Fast DataTable Export with Formatting

Jul 14, 2011 at 5:46 PM

I have recently inherited some code which was built against Simple OOXml to Export DataTables to a spreadsheet stored in a MemoryStream. When this code was put into production, it was far too slow for our uses. To get around the problem, I built a DataTable exporter manually using the OpenXmlWriter class. Using this approach, I was unable to reproduce some of the formatting that was done using Simple OOXml. 

Next, I attempted to open the stream I had already constructed, using the following code:


//Exports a DataTable to a MemoryStream using OpenXmlWriter
MemoryStream myStream = ExportDataTableToStream(myTable);

SpreadsheetDocument doc = SpreadsheetDocument.Open(myStream, true);
WorksheetPart SheetOutput = SpreadsheetWriter.InsertWorksheet(doc, "Output");

After doing this, I try to pull a SpreadsheetStyle object out of the opened MemoryStream:

SpreadsheetStyle footerStyle = SpreadsheetReader.GetDefaultStyle(doc);

This doesn;t work. I either get an exception, or null is returned. What confuses me is that in the code I inherited, a blank SpreadsheetDocument was created using SpreadsheetDocument.Open, and the SpreadsheetStyle 
object existed. I'm not sure what I am doing wrong, but is there an easy fix? Any help would be appreciated.