Exporting data to an excel spreadsheet

Question

I am going to be working on a general purpose toolkit for writing excel files based off a database data. I've worked with a couple of methods before:

1) Create a CSV file
Excel will open it and format the data with each value going into a single cell, each line is one row.
Drawbacks: file extension is not always going to be opened by excel by default.
It seems to 'cut off' on very large data sources. From memory it hit around 65,000 rows before the rest of the file was 'ignored' by excel.

2) Generate an XML spreadsheet
http://msdn.microsoft.com/en-us/library/bb226687(v=office.11).aspx
Drawbacks: Error's when opening 'the file you are trying to open ** is in a different format than specified by the file extension'. It does open fine, but it doesn't inspire confidence.
Makes writing SQL-to-XML awkward.

I briefly looked at using the excel Interop methods a while ago. And I am sure I had issues when trying to run the code from a server which doesn't have excel/office installed. This will almost always be the case.

Generally the code will be run either through a webpage or a silverlight WCF service (if that makes a difference).
And either emailed to a user, or would also be nice to have the option to just open the file straight away and allow them to save.
Including headers/colours/formula's would be a bonus

Mostly at the moment they would just copy/paste the data.
But with a large data set it is prone to failure, or is just really really slow.

Anyone give me the best ideas how to go about it

Answer 1

I recommend using EPPlus, it is very easy to convert DataTables to Excel files and allows for tons of styling options and even data validation. You don't even need Excel installed on the server.

Answer 2

Since CSV has the problem of cutting off after big number of rows, that approach is out.

Using Interop is tricky. In addition to ensuring that you have Excel and PIA installed on the server, you need to run the Excel code in a single thread (using some kind of queuing mechanism). Otherwise, you will start running into issues on that front.

The XML actually seems to be the most reliable solution, save for the confusing message. If you can answer my question in the comments, we might be able to assist you with that, as well.

PS. If you can determine the number of rows before generating the Excel sheet (which you should), you could deliver the results in one of the 2 formats: CSV for small files, XML for large, the trade-off being the confusing message (provided that it won't go away). Depending on the percentage of files being over the CSV threshold, that might be an acceptable compromise.

Answer 3

You could use a commerical product, such as Aspose.Cells to generate the Excel spreadsheet in C# on the server without having Office installed. They have some sample code that generates a spreadsheet in an aspx.cs page, if you want to see what the object model looks like.

Unfortunately, their pricing scheme is really awkward if you have more than one developer who gets the code from source control and builds it; they officially consider you to be part of the same team and require licenses for each developer (even if you're the only programmer who directly works on that code).