Bulk export performance tip

Jun 2, 2013 at 12:12 AM
In EPPlus v3.1.3, I was exporting 320K rows, 12 columns and it was very slow, about 6000 rows per minute ... using the standard Cells collection. On pace to take 54 minutes for all the rows.

While trying out some changes, I ended up with a notable breakthrough, getting all 320K rows to export in 20 seconds!

It required just one change. I had set a NumberFormat on a range of cells on just one of the columns before outputting the data. It looked roughly like this:
ws.Cells[3, 1, 1048576, 1].Style.Numberformat.Format = "[$-409]mmm d yyyy h:mm AM/PM;@";
for (int r = 0; r < items.Count; r++)
{
    ...
}
The change was moving the Numberformat below the FOR loop. Apparently the Numberformat must have been getting re-applied or re-evaluated each time data was being output into a cell in the column #1 where the Numberformat was set to. Basically the lesson learned is to apply any type of formatting after all the data has been output.

Just wanted to share this in case it helps anyone.
May 13, 2015 at 4:42 PM
Thanks! This made my day.