Auto column width EPPlus slow

Question

I use this code to load data into a worksheet (C#, EPPlus 4.04) and set the column width automatically:

workSheet.Cells["A1"].LoadFromCollection(itemsToExport, PrintHeaders: true);
workSheet.Cells[workSheet.Dimension.Address].AutoFitColumns();

There is a significant performance penalty that seems to be lineair with the number of records in the worksheet. E.g. a 2 second difference on 15K records, and I have to create multiple files.

Is there a way to speed this up without resorting to async processing and the likes?

Ps, setting AutoFitColumns before filling the data does not work, columns will remain in a width that's too small for the data.

Its going to be slow, it has to go through each row/column and measure the size of the text that it contains to determine the column width. If this involves formulas or different fonts, its even slower. I think Excel does some kind of trick that only measures the visible columns/rows, but EPP doesn't have a "visualizer" so it has to go through them all. — Ron Beyer, Aug 18 '15 at 14:22

score 3 · Answer 1 · answered Aug 19 '15 at 06:45

Ron Beyer is right with his comment. I downloaded the source code from epplus.codeplex.com and took a look at the function:

/// <summary>
/// Set the column width from the content of the range.
/// Note: Cells containing formulas are ignored if no calculation is made.
///       Wrapped and merged cells are also ignored.
/// </summary>
/// <param name="MinimumWidth">Minimum column width</param>
/// <param name="MaximumWidth">Maximum column width</param>
public void AutoFitColumns(double MinimumWidth, double MaximumWidth)

All cells will be processed, in a loop, unfortunately. Maybe I should use a evenly spaced font like Courier and calculate the widths myself.

The AutoFitColumns in the EPPlus library could use some extra attention as its code complexity is 35 atm (but keep up the good work!).

Auto column width EPPlus slow

1 Answers1