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

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.

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

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.

Quote:

---

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).

Quote:

---

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.

Quote:

---

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. :)

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.

Quote:

---

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

Quote:

---

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

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

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?

Quote:

---

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 :)

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.

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)

Quote:

---

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

---

something else !

Quote:

---

which requires the use of Excel spreadsheets

---

No other options ?

Have tried to blend vb.net and Excel (using Interop) for some database front-end, and I can assure you is hard and messy.
excel stays open if you don't do something about it, and when you do, say, by killing all Excel processes, you could annoy other users working in parallel with you. Culture locales, dates and/or different versions of Excel could be a problem too affecting portability.
Along with Excel comes use of Datagridview, more headaches etc.
If you want to use Excel for basic database work, i can tell you is the wrong tool. Access, no better, again Interop.

The right tool for this work is an RDBMS (Sql, mySql etc) and you can build a vb.net front-end to manage the DB.
just my two cents...

Quote:

---

Originally Posted by vangos

something else !



No other options ?

Have tried to blend vb.net and Excel (using Interop) for some database front-end, and I can assure you is hard and messy.
excel stays open if you don't do something about it, and when you do, say, by killing all Excel processes, you could annoy other users working in parallel with you. Culture locales, dates and/or different versions of Excel could be a problem too affecting portability.
Along with Excel comes use of Datagridview, more headaches etc.
If you want to use Excel for basic database work, i can tell you is the wrong tool. Access, no better, again Interop.

The right tool for this work is an RDBMS (Sql, mySql etc) and you can build a vb.net front-end to manage the DB.
just my two cents...

---

You can use Access databases the same way you use mySql and Sql server databases in VB.net (ADO.NET).

Quote:

---

Originally Posted by vangos

The right tool for this work is an RDBMS (Sql, mySql etc) and you can build a vb.net front-end to manage the DB.

---

The data is being pulled from Oracle. Excel is just so the end users (usually quality or product engineering) have something familiar that they can easily manipulate the report data however they wish. So they select some parameters from the VB front end, I pull the data, and then they do whatever they need to with the data using Excel. Once the data is copied to Excel, I don't care what if anything they do with it.

Access would not give me any benefit here since most users don't know how to use it and I suspect most users don't even have it. Something like Crystal Reports or the ReportViewer component wouldn't either AFAIK since they provide just canned reports so the user can't resort how they want, add formulas or charts, etc.

If there's a tool other than Excel that would work well for my need, and is free and/or comes with Win 7, then I'd be open to that, but that is not what I asked for in the OP. I know Excel works well for this requirement, and I'm after the best way to display the data in Excel using .Net since I'm pretty new to it.

Quote:

---

Originally Posted by topshot

The data is being pulled from Oracle. Excel is just so the end users (usually quality or product engineering) have something familiar that they can easily manipulate the report data however they wish. So they select some parameters from the VB front end, I pull the data, and then they do whatever they need to with the data using Excel. Once the data is copied to Excel, I don't care what if anything they do with it.

---

I'm not trying to be a S_A_, but this sounds like it this could all be done much easier from with in Excel itself with a Userform and bit of VBA.

Query Oracle database


For your potential Server side needs, it is not recommended to use Office Automation (Interop) as several issues can arise.
See: Considerations for server-side Automation of Office

Using the Open XML SDK directly is an option, but it will be a lot learning and coding on your part. Considering the relatively simple spreadsheet design that you described, I would think that any of the helper libraries (EPPLus, ClosedXML, SpreadsheetLight, etc.) would handle your needs.

You specifically mentioned assigning an array to a range as a desired feature to avoid looping through each cell. This is the correct philosophy for Interop, but it may not apply to the OpenXML solution as you are dealing with a totally different critter there (many files packaged into a zip file masquerading as a single entity); my point with this statement is that your implementation constraint may not match reality.

Good Luck.

Quote:

---

Originally Posted by TnTinMN

I'm not trying to be a S_A_, but this sounds like it this could all be done much easier from with in Excel itself with a Userform and bit of VBA.

Query Oracle database

---

If it can work with the newest Oracle connection methods that don't require just 2 DLLs instead of the client install, this may be an option for one of my programs that only does reporting. Are you implying that other programs that may also have a report that could be done with it that I shell out to an Excel file/form I'd create for it?

I'll investigate how complex UserForms can be (interface-wise - combo boxes, radio buttons, tabs, etc) since I've never had to create one. I assume the VBA can handle creating the dynamic SQL (in some cases I can't use stored procedures with parameters).

Quote:

---

Originally Posted by topshot

If it can work with the newest Oracle connection methods that don't require just 2 DLLs instead of the client install, this may be an option for one of my programs that only does reporting.

---

I don't work with Oracle so I can not easily test it, but try following the instructions in the link I provided to see if you can create a connection.

If that fails, it should be possible to create a ActiveX component in VB.Net to do the DB work and return the data to Excel. This may be more than you want to get into, but you can use the Microsoft InteropForms Toolkit 2.1 to create ActiveX controls in VB.Net. This route is still Interop, but in the other direction with the Excel application using a .Net application to do the stuff it can do and retrieving the results. This route eliminates many of the pains associated with going the other direction (worrying PIA versions, tracking COM objects to be released).

[QUOTE=topshot;4867763Are you implying that other programs that may also have a report that could be done with it that I shell out to an Excel file/form I'd create for it?[/QUOTE]
I don't know how I gave you that impression. All I was suggesting is that if you can get a DB connection from within Excel, you probably could do everything you need to do with out worrying about other tools to create an Excel file.

Quote:

---

Originally Posted by TnTinMN

I don't know how I gave you that impression. All I was suggesting is that if you can get a DB connection from within Excel, you probably could do everything you need to do with out worrying about other tools to create an Excel file.

---

Most of my programs do a lot more than just use Excel to get or display data so I don't see how it would be practical to use Excel for everything else (ie, using serial ports and DIO, etc). Thus, the Excel file I'd create for the reporting portion would need to be called from the main program or am I missing something?

Quote:

---

Originally Posted by topshot

Most of my programs do a lot more than just use Excel to get or display data so I don't see how it would be practical to use Excel for everything else (ie, using serial ports and DIO, etc). Thus, the Excel file I'd create for the reporting portion would need to be called from the main program or am I missing something?

---

You are probably not missing anything. I'm not advocating this as a better route to go nor am I implying that it is as feature rich as the .Net framework. I am just trying to give you information so you can make an informed decision.

Since you porting from VB6, I thought that this may be a less painful route for simpler cases. VBA used to be referred to as a subset of VB6, but over the years they have filled in a lot of the missing functionality and even added pointers for doing API calls. People do do serial port control from Excel. Here is link to a way of setting it up to use the Windows API. Does it make sense to do so?, that is something you have to decide given the constraints you are operating under.

Serial port programming on Visual Basic with Windows API

So if I'm getting this correctly, if I use the Interop method, users either need to have the same version of Excel as I do or I have to include all versions of the Excel PIA for the versions they may use? And it can be a PITA to cleanup if you're not careful though I'm far more worried about version compatibility.

If I use XML SDK method, I have to loop through the data, but there isn't a ton of it and I don't have the hassle of the Interop. I can then use the Shell function to open Excel with the filename on the command line though I may run into security exceptions since they won't be admins. Is there a way to open a file as if you were double clicking it from Explorer so the associated program opens?

Quote:

---

Originally Posted by topshot

So if I'm getting this correctly, if I use the Interop method, users either need to have the same version of Excel as I do or I have to include all versions of the Excel PIA for the versions they may use? And it can be a PITA to cleanup if you're not careful though I'm far more worried about version compatibility.

---

No, the users do not need to have the same Excel version that you developed against.

The Excel object model is all based on interfaces. They may add a new interfaces over time, but the older ones are still there. An issue can arise if you develop against the latest version and use the newest features. These naturally do not exist in the prior versions. I know that charts have undergone improvements over time, but it sounds like most if not all of what you are using has existed for a long time. You can go to the documentation when in doubt to check when a particular feature has been added.

You need to either have local copies of the PIA you developed against included with your application, or you can use the the "Embed Interop Types" feature that was introduced with VS2010. With the embedded option, the compiler determines which parts of the various interfaces you used in the application and adds the to your code. You never see this unless you use a decompiler.

Quote:

---

Originally Posted by topshot

If I use XML SDK method, I have to loop through the data, but there isn't a ton of it and I don't have the hassle of the Interop.

---

AFAIK, this is correct. However, one of the helper libraries may have a feature that isolates you from that.

Quote:

---

Originally Posted by topshot

I can then use the Shell function to open Excel with the filename on the command line though I may run into security exceptions since they won't be admins. Is there a way to open a file as if you were double clicking it from Explorer so the associated program opens?

---

I am not aware of any need to be an Administrator to launch a file and have its default program open it. I would advise against using the VB Shell function and instead use the System.Diagnostics.Process class to launch the file. This can be as simple as:

Code:

---

System.Diagnostics.Process.Start("Book1.xlsx")

---

Quote:

---

Originally Posted by TnTinMN

You need to either have local copies of the PIA you developed against included with your application, or you can use the the "Embed Interop Types" feature that was introduced with VS2010. With the embedded option, the compiler determines which parts of the various interfaces you used in the application and adds the to your code. You never see this unless you use a decompiler.

---

I could live with that.

Quote:

---

I would advise against using the VB Shell function and instead use the System.Diagnostics.Process class to launch the file. This can be as simple as:

Code:

---

System.Diagnostics.Process.Start("Book1.xlsx")

---

---

Excellent. I'll play around some with both of those options then. Thanks for the help.