CODEWITHSUNDEEP
c#performanceepplus-4
Michel van Engelen
Michel van Engelen

Reputation: 2869

Auto column width EPPlus slow

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.

Upvotes: 5

Views: 1970

Answers (1)

Michel van Engelen
Michel van Engelen

Reputation: 2869

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!).

Upvotes: 3

Related Questions