workbook.SaveAs() takes a long time

May 13, 2011 at 4:15 PM

I am reading a table from an SQL database and creating an Excel file to download as a MemoryStream. There are 21 columns and 9761 rows being created.

Here are the sequence of events:

// Create an Excel Workbook
XLWorkbook workbook = new XLWorkbook();
IXLWorksheet sheet = workbook.Worksheets.Add("NewSheet");
// Retrieve the data from database
var table = ReadDataFromDatabase();
// Add the inventory table to the Excel sheet
sheet.Cell(1, 1).InsertTable(table);
// run autofit on all the columns
// Save time by just measuring the first 75 rows 
sheet.Columns().AdjustToContents(1,75);
// Freeze the top row and the first five columns
sheet.SheetView.Freeze(1, 5);
// Mark the first row as BOLD
sheet.FirstRow().Style.Font.Bold = true;
SetRowColorsForInventory(sheet, 14, 18);
// All done
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
return ms;
Reading the data takes just a second.
Doing the InsertTable() takes 5 seconds.
Doing the formatting stuff including examining each row and setting a row color based on the data takes 4 seconds.
Doing the workbook.SaveAs() to the MemoryStream takes 20 seconds.
It takes a total of 30 seconds with 20 seconds happening in the SaveAs() function. That seems excessive to me. What do you think?
Coordinator
May 13, 2011 at 4:41 PM

I know... I'm always improving the performance but until I get off the OpenXML SDK there's only so much I can do regarding the SaveAs performance. I wish I had better news =/

May 13, 2011 at 6:32 PM

Thanks for responding. By the way, I am using ClosedXML_v0.47.1_Net3.5 from March 23, 2011 and OpenXml SDK Version 2.0.5022.0.

Please let me know if any more recent releases have improved performance.

It still seems very odd that copying the structures from one memory location to another should take longer than it took to create the structures in the first place. That is unless the SDK structures are so radically different from the way the .xlsx is built.

Thanks again for a truly great product!

Coordinator
May 13, 2011 at 7:09 PM

v48.2 included a performance enhancement when saving formulas. If your sheets don't have formulas then you won't notice a difference though.

May 13, 2011 at 7:56 PM

No, they don't. Thanks again.