What's the best way to export DataTable to an Excel file?

Question

As far as I know, there're 3 ways to do this:

• The first way is using COM Interop to create a Excel file, then iterate over all cells to read and write data. The created file appears to be valid (true .XLS file), but it seems that Excel must installed to use this way
• The second way is writing a file header (CSS style), and then iterate over all cells, too. This way doesn't requires Excel installed, but the file created may not be valid (it's a HTML file, actually)
• The third way is fill DataTable into a DataGrid, and then render it into a HTML file, then rename it into .xls extension.

The third way seems to be simplest, but I don't know if there is a better way to archive this? If not, which is the best in these three ways?

Thank you so much.

Answer 1

A fourth way would be to use an OleDbConnection and connect your .NET application to Excel using standard ADO.NET.

I had to do something similar a few years back and this article was very helpful.

http://davidhayden.com/blog/dave/archive/2006/05/26/2973.aspx

There's a lot of quirky things with this approach (mainly referencing tables and ranges), but it ends up being just an ADO.NET implementation and ends up looking very much like a normal ADO.NET project.

Hope this helps!

Answer 2

Simplest way: CSV. Just ToString() on each value of each DataRow and drop it to a file. You can also drop the column names as the first row in the file. CSV files are imported easily into excel.

My alternate would be to export to xslx using one of the many projects on Codeplex. I've played with ExcelPackage and Simple OOXML, both of which (IIRC) work fine.

You can find more by searching for Excel or OOXML on Codeplex.

Answer 3

Be wary of writing to CSVs as Excel will automatically convert types of data. For example 0001 will be converted to a numerical 1.

Another option is to use DataTable.WriteXML(filename) and set the filename to something.xls

This is not binary XLS file, but should open with Excel just as well.

BTW: The HTML table approach is better if you need formatting, and both of these approaches can be very slow for large tables.