Exporting data to Excel: Interop, Open XML SDK or something else?

**topshot** · Apr 17th, 2015, 09:46 AM

On to my next VB6 conversion which requires the use of Excel spreadsheets. Basically, I'm after which road will give me the best functionality and performance for creating basic spreadsheets for reports that have maybe thousands of rows (but not millions), some minor formatting (font type), titles and charts. I want to make sure I can set a 2D array to a range so I don't have to loop (and maybe all options allow for that).

I'm not worrying about anything prior to 2007 so everything will be XLSX format. Normally I don't need to read any spreadsheets though for my first project that is all I'll be doing.

From what I've been reading here and elsewhere, I get the sense that the Interop is a pain in general (perhaps because of server side issues) so I've seen Open XML SDK or EPPlus, which seems to be a "wrapper" for the SDK to make it much easier to use, highly recommended. Currently, everything will be done at client level but it's certainly possible that I'll have server side in the future.

As much as EPPlus seemed to be regarded elsewhere, I was surprised there were very few posts on it at this site when I Googled so that got me wondering why if it was so good.

Thanks for your advice and direction.

**Frabulator** · Apr 17th, 2015, 09:53 AM

I have created a program that uses Excel as a calculator and data dumb, not so much as a formatting tool. Over my months of researching, trial and error, I finally found someone on this forum that really patched things up for me.

From what I have found JET and ACE are easier to use but have a ton of bugs. Interop is much more reliable but takes a bit longer to set up properly.

Also, when using Excel you will need to release the workbook, worksheet and application when you get done with it or it will keep it open and running the background until you end the process or restart your computer.

If I am incorrect about any of that I'm sure some one will correct me, but that is what I have gathered from my experience. Hope it helped

Frab

**jayinthe813** · Apr 17th, 2015, 10:00 AM

I use EPPLUS at the client level, we dont support servers yet. Note that it is 100% complete in regards to everything it supports (such as advanced charting styles), but it is VERY fast and should support 99% of what you need. I prefer it wildly over Interop, you dont even need to have Excel installed. I used to generate reports with 500,000 row items that took Interop over 20 minutes to create. The same report in EPPLUS took less than 5.

The main problem with Interop is it is so slow. EPPLUS is only one of many solutions though and its all a matter of taste. The thing I liked most about EPPLUS is that the coding was almost the same as writing macros, so there isnt much of a learning curve to it.

**topshot** · Apr 17th, 2015, 10:19 AM

Originally Posted by Frabulator

I finally found someone on this forum that really patched things up for me.

Who was that if you don't mind me asking (you can PM if you don't want to state publicly).

Also, when using Excel you will need to release the workbook, worksheet and application when you get done with it or it will keep it open and running the background until you end the process or restart your computer.

In VB6 I did leave the Excel window open so it was up to the operator to save it if desired and/or close it. I assumed I'd do the same here.

Originally Posted by jayinthe813

I use EPPLUS at the client level, we dont support servers yet. Note that it is 100% complete in regards to everything it supports (such as advanced charting styles), but it is VERY fast and should support 99% of what you need. I prefer it wildly over Interop, you dont even need to have Excel installed.

Indeed, you were the one proponent of EPPlus that I found here, Jay. That made me wonder why nobody else was on the bandwagon.

**topshot** · Apr 17th, 2015, 10:27 AM

One more thing to add is that I WILL be needing DATE values sometimes. I guess that's one difference of using Value vs Value2 when assigning the Range.

**Frabulator** · Apr 17th, 2015, 10:50 AM

Originally Posted by topshot

Who was that if you don't mind me asking (you can PM if you don't want to state publicly).

TnTinMN is his name, and he is pretty active on here around this time of the day. If this thread stays up for a while he will probably glance at it anyway. However it would be best not to message people regarding forum questions. Main reason is other people may be able to help you.

If you would like to look at the thread he helped me with it is in the link below:

LINK

Originally Posted by topshot

In VB6 I did leave the Excel window open so it was up to the operator to save it if desired and/or close it. I assumed I'd do the same here.

If you create a reference, like "Label1.Text = xlApp.xlWorkbook.ActiveWorkbook.Worksheet(0).Range("A2").Value" that reference stays active until you completely end Excel in the windows task manager or you restart your computer. Even if the user closes excel themselves, it will still run in the background. It also works the other way around. If you say " xlApp.xlWorkbook.ActiveWorkbook.Worksheet(0).Range("A2").Value = Label1.Text" that also creates a reference that will not end.

How you end it is pretty simple though. First you need to put these lines of code into your project

vb.net Code:

Public Shared Sub ReleaseCOM(ByVal COMObj As Object, Optional ByVal GCCollect As Boolean = False)
        Try
            If COMObj IsNot Nothing Then
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(COMObj)
            End If
        Finally
            COMObj = Nothing
            If GCCollect Then
                GC.WaitForPendingFinalizers()
                GC.Collect()
            End If
        End Try
    End Sub
 
 
 
 
 
    Private Sub releaseObject(ByVal obj As Object)
        Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

What the above code does is sets up a release function so that once you make your connection you can release it, severing connections to the Excel Doc. This will make the program run smoother and not bog down.

After you have those in the code you need to have a reference to the Excel Workbook and Application to run it smoothly.

vb.net Code:

'when referencing excel you will need these, which go inside the class
    Dim xlApp As Microsoft.Office.Interop.Excel.Application
    Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
    Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
    Dim NewFileName As String
    Dim TempLimitSet As VariantType
    Dim ActiveWB As String
    Dim ActiveSheetName As String

Once you have these you can set the excel book to open like so:

vb.net Code:

xlApp = New Microsoft.Office.Interop.Excel.Application
            xlApp.Application.DisplayAlerts = False
            xlWorkBook = xlApp.Workbooks.Open("LOCATION", Notify:=False) 'notify false will not bring up any warning messages about excel
 
            xlWorkSheet = CType(xlWorkBook.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet)
 
'set below to true to show excel, set to false to hide excel
            xlApp.Visible = True

As mentioned before you will need to set up a proper link into excel. What I mentioned before ( - Label1.Text = xlApp.xlWorkbook.ActiveWorkbook.Worksheet(0).Range("A2").Value - ) is actually incorrect, but if you have worked with VB in Excel it looks right. The reason why is because if you do that you will create a link that can not be terminated, therefore you will have to restart your computer or completely kill excel to stop the reference. How you correctly link is listed below:

vb.net Code:

'this will create a link from excel into your program. Example would be something like "LimitPG = Worksheet(1).Range("B7").Value"
 
 
            Dim LimitPG As String  'this sets up a string, int32, double or whatever the value of the excel doc will be
Dim ws as Microsoft.Office.Interop.Excel.Worksheet
Dim rng1 As Microsoft.Office.Interop.Excel.Range
 
            ws = CType(xlWorkBook.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet)
            rngN1 = (xlWorkBook.Worksheets(1).Range("B7"))
            LimitPG = CStr(rngN1.Text)
 
'the line below will release the reference
            ReleaseCOM(rngN1) ' your done with it so release it before you forget
 
 
 
 
'
'
'
 
 
'this will create a link from your program into excel. Example would be something like "Worksheets("Data1").Range("AI18").Vlaue = 1"
 
                    Dim ws2 As Microsoft.Office.Interop.Excel.Worksheet = CType(xlWorkBook.Worksheets("Data1"), Microsoft.Office.Interop.Excel.Worksheet)
                    Dim rng2 As Microsoft.Office.Interop.Excel.Range = xlWorkBook.Worksheets("Data1").Range("AI18")
                    rng2.Value = 1
                    ReleaseCOM(rng2)
                    ReleaseCOM(ws2)

And when you get completely done with the program, workbook or whatever you are referencing you can completely kill it with this code.

vb.net Code:

xlWorkBook.Close()
                releaseObject(xlApp)
                ReleaseCOM(xlWorkBook) 'droping previously referenced WorkBook
                releaseObject(xlWorkBook)
                releaseObject(xlWorkSheet)
                ReleaseCOM(xlApp, True) 'saves and exits

Hope that helps

Frab

**Frabulator** · Apr 17th, 2015, 11:02 AM

I incorporated the current date and last registered date (date last opened) into my program which spat it into excel. Getting the current date isnt too difficult:

vb.net Code:

'gets current date
        Dim CurrentDate As String
        CurrentDate = Date.Now.ToString("MM/dd/yyyy")
 
'this puts the string as EXAMPLE "04/17/2015"

this is an example of putting strings together into a date

vb.net Code:

Dim inM As String
            inM = "04"
 
  Dim ind As String
            ind = "17"
 
  Dim iny As String
            iny = "2015"
 
 
 
            Dim indates As String = inM & "/" & ind & "/" & iny
            Dim InDate As Date = Date.Parse(indates)
            InDate.ToString("MM/dd/yyyy")
 
'this puts the string as EXAMPLE "04/17/2015"

You can put those into excel by either having each section in a separate cell or putting the whole date in the cell. VB.Net and Excel will read it both ways

**topshot** · Apr 17th, 2015, 11:07 AM

I've had help from TnTinMN on other questions before.

I can now see another reason why the Interop is a pain.

I wonder if I need to deal with all that using the XML SDK or one of its wrappers?

**Frabulator** · Apr 17th, 2015, 11:14 AM

Originally Posted by topshot

I can now see another reason why the Interop is a pain.

I wonder if I need to deal with all that using the XML SDK or one of its wrappers?

It is a paint to get set up, and a little overwhelming. But once you get it set up right it works great.

I personally have not tried the others you mentioned, so I cant vogue for them but perhaps some one else can

**jayinthe813** · Apr 17th, 2015, 01:16 PM

Ok so lets clarify a few things (other members are open to correct me if they feel I am incorrect).

The tool you choose to use depends on your needs. You have decided that you have to generate an excel report and present it to the user. Choosing between Interop and EPPlus (or other similar third party library) is all down to your needs and/or preferences. The code to write either library will be very similar. The bottom line you have to ask yourself (mainly):

1. Will the end-user always have Excel (and the version you have) installed? You cannot use Interop without having Excel installed.

2. Is performance an issue? If you are going to be creating large reports do you require it done more faster/efficiently?

I still use both actually, it just depends on the needs. Some things are missing or are not well explained in the EPPlus documentation. This is usually more advanced stuff though, not typical scenarios you would deal with. EPPlus also doesnt suffer from "EXCEL" instances staying open (which you see a work-around for above). But you shouldn't worry about those issues, rather, learning the syntax first.

No matter what library you decide to go with, once you understand how to write in one, you will see how transparent it is to move to another one (There is such a short learning curve). I imagine the reason why not a lot of members here talk about using a third party library is because they use databases where possible or they don't have a real need for exporting excel data quickly. Once you start writing huge files though, and the requirements are put in front of you, you use what makes the most sense for your needs.

**topshot** · Apr 17th, 2015, 02:36 PM

As I said, I won't be writing huge spreadsheets at least for this client. I restrict how much they can pull (eg, 3 months) to make sure they don't get out of hand.

Today it takes maybe 10 seconds on average from the time they hit the report button to the spreadsheet being fully formatted and open for them to use. I certainly would not want it any slower than that.

The users will have Excel (unless it does happen to be a web-based program), however, I doubt they will have the version I have (2013) - more likely 2010 or maybe 2007.

It is also true that if I use some version of the SDK route that I will still need to shell Excel to open the file I created for them to see it and manipulate the data however they want (eg, resort, filter, add charts, etc)