What is the fastest way to export a DataTable in C# to MS Excel?

Question

As the title says I have massive DataTables in C# that I need to export to Excel as fast as possible whilst having a reasonable memory consumption.

I've tried using the following:

EPPlus (currently the fastest)
OpenXML (slower than EPPlus - not sure this makes sense as EPPlus probably uses OpenXML?)
SpreadsheetLight (slow)
ClosedXML (OOM exception with large tables)

Assuming massive data sets (up to 1,000,000 rows, 50 columns) and an infinite development time, what is THE fastest way I can export?

EDIT: I need more than just a basic table starting in A1. I need the table to start in a cell of my choosing, I need to be able to format the cells with data in, and have multiple tabs all of which contain their own data set.

Thanks.

Have a look at the code in my [answer here](https://stackoverflow.com/questions/32690851/export-big-amount-of-data-from-xlsx-outofmemoryexception/32787219#32787219). It shows how to use OpenXml using the SAX approach which should be the fastest way to do it. It will also be more memory efficient for large data sets. — petelids, Aug 03 '17 at 08:38
*Assuming massive data sets (up to 1,000,000 rows, 50 columns)* Excel is the wrong tool for the job... I'd recommend EPPlus, which you've already tried and said is "fastest", but you don't describe any sort of performance requirement other than "more fasterer than 'currently the fastest' I've benchmarked" — ta.speot.is, Aug 03 '17 at 09:09

Ognjen Babic · Answer 1 · 2017-08-03T09:08:30.867

You did not specified any requirements on how the data should look like in excel file. I guess, you don't need any complicated logic, just setting up correct data in correct columns. In that case, you can put your data in CSV (comma separated values) file. Excel can read this file just fine.

Example of CSV file:

Column 1,Column 2,Column 3
value1,value1,value1
value2,value2,value2
...

As requested, here is the code sample for creation csv file.

var csvFile = new StringBuilder();  
csvFile.AppendLine("Column 1,Column 2,Column 3");

foreach (var row in data)
{
    csvFile.AppendLine($"{row.Column1Value},{row.Column2Value}, row.Column3Value}");
}

File.WriteAllText(filePath, csvFile.ToString());

You can use some texternal libraries for parsing csv files, but this is the most basic way i can think of atm.

Have edited to give more detail about what I need to achieve, thanks for the reply — IDoCode, Aug 03 '17 at 08:55

score 0 · Answer 2 · answered Aug 03 '17 at 08:25

Excel is just an XML file format. If you strip away all the helper libraries, and think you can do a better job at coding than the people at EPPPlus or OpenXML then you can just use an XML Stream Writer and write the properly tagged Excel XML to a file.

You can make use of all kinds of standard file buffering and caching to make the writing as fast as possible but none of that will be specific to an Excel file - just standard buffered writes.

score 0 · Answer 3 · answered Aug 03 '17 at 09:11

Assuming ... an infinite development time, what is THE fastest way I can export?

Hand-roll your own XLSX export. It's basically compressed XML. So stream your XML to a ZipArchive and it will be more-or-less as fast as it can go. If you stream it rather than buffer it then memory usage should be fixed for any size export.

What is the fastest way to export a DataTable in C# to MS Excel?

3 Answers3