Parse text file and create an excel report

Question

Hello,

My application is supposed to parse a text file (relatively easy) and create an excel spreadsheet report.

Should I write a stand alone VB.NET application that saves the excel file, or should I use VSTO? I am unsure if there are any differences in terms of ease of development, usability issues, API functions available, etc.

Are there any other programming languages/interfaces/libraries that will allow me to rapidly develop an involved excel spreadsheet? I am talking about things like functions, graphs, etc.

Thank you

Answer 1

As far as in what form you develop this application, it's really up to you. I would base it on what kind of deployment you would like to use.

If you choose to do it outside of excel, then I would look into EPPlus.

Answer 2

For commercial libraries, you should look at Aspose Cells and SpreadsheetGear.

It's been a while since I've used either, but I recall that the Aspose approach is focused on calling into their libraries from code, and does not require Excel, while SpreadsheetGear is somewhat more template-driven.

Answer 3

You can do this very easily by taking advantage of excel 2007 format (.xlsx) Here is what I did, you can modify it pretty easily. Im basically taking advantage that the xlsx file is really just a zip file containing xml files.

I created an empty excel file called Empty.xlsx and added it to my application as a resource. (build action embedded resource)

I'm also using a library for standard zip and unzip since that is how you get at the parts of the excel file.

Here is how i take a datatable and create the excel file.. notice that Excel is not actually needed.

   Private Function CreateExcelReport(ByVal FilePath As String, ByVal tbl As DataTable) As FileInfo
    'Just loading the excel file from the assembly, you could do it from a file also
    Dim _assembly As Assembly = Assembly.GetExecutingAssembly
    Dim xlStream As New StreamReader(_assembly.GetManifestResourceStream("YourAssembly.Empty.xlsx"))
'Create a new fileinfo that will hold the outputed excel file with the data.
Dim fiRet As New FileInfo(FilePath)

'Im using Ionic Zip Reduced free library to break the slsx file into its subparts 
Using z As ZipFile = ZipFile.Read(xlStream.BaseStream)
    'Grab Sheet 1 out of the file parts and read it into a string.
    Dim myEntry As ZipEntry = z("xl/worksheets/sheet1.xml")
    Dim msSheet1 As New MemoryStream
    myEntry.Extract(msSheet1)
    msSheet1.Position = 0
    Dim sr As New StreamReader(msSheet1)
    Dim strXMLData As String = sr.ReadToEnd

    'Grab the data in the empty sheet and swap out the data that I want 
    Dim str2 As XElement = CreateSheetData(tbl)
    Dim strReplace As String = strXMLData.Replace("<sheetData/>", str2.ToString)
    z.UpdateEntry("xl/worksheets/sheet1.xml", strReplace)
    'This just rezips the file with the new data it doesnt save to disk
    z.Save(fiRet.FullName)
End Using

'Return a Fileinfo class to be saved to disk or DB or streamed to browser
    Return fiRet
End Function




Private Function CreateSheetData(ByVal dt As DataTable) As XElement

Dim sheedata As XElement = <sheetData></sheetData>

'Create Header Rows
Dim HeaderRow As New XElement(<row></row>)
For j = 0 To dt.Columns.Count - 1
    Dim c As New XElement(<c t="inlineStr"></c>)
    Dim _is As New XElement(<is></is>)
    Dim v As New XElement(<t></t>)
    v.Add(dt.Columns(j).ColumnName)
    _is.Add(v)
    c.Add(_is)
    HeaderRow.Add(c)
Next
sheedata.Add(HeaderRow)

'Create row for each datarow
For Each dr As DataRow In dt.Rows
    Dim newrow As New XElement(<row></row>)
    For j = 0 To dt.Columns.Count - 1
        Dim c As New XElement(<c t="inlineStr"></c>)
        Dim _is As New XElement(<is></is>)
        Dim v As New XElement(<t></t>)
        v.Add(dr(j).ToString)
        _is.Add(v)
        c.Add(_is)
        newrow.Add(c)
    Next
    sheedata.Add(newrow)

Next


    Return sheedata

End Function