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
// run autofit on all the columns
// Save time by just measuring the first 75 rows
// Freeze the top row and the first five columns
// Mark the first row as BOLD
sheet.FirstRow().Style.Font.Bold = true;
SetRowColorsForInventory(sheet, 14, 18);
// All done
MemoryStream ms = new MemoryStream();
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?