ExcelWorksheet.Columns.AutoFit() is very slow

[rdhasse]

trafficstars

With Excel, auto file of column widths takes a second or two. With the same worksheet, in EPPlus it takes ~10 minutes. Any idea why it takes so much longer in EPPlus since my understanding is both are doing the same operation?

[JanKallman]

If you run under Windows, EPPlus will measure the width of all the cells in the columns you autofit using System.Drawing.Commons MeasureText method. That is a quite expensive operation, so it will take time if you autofit columns with many cells. One option is to switch to our build-in text measurer : GenericFontMetricsTextMeasurer You can do that by setting the PrimaryTextMeasurer : package.Settings.TextSettings.PrimaryTextMeasurer = package.Settings.TextSettings.GenericTextMeasurer

If you have columns with many cell, a better approach might be to autofit on a subset of the columns cells. Also see this article on our wiki: https://github.com/EPPlusSoftware/EPPlus/wiki/Autofit-columns

[JanKallman]

We will have another look at the performance in this method and see if we can improve it.

[ksowin]

I actually can't get the column autofit to work at all with any of the options listed here. I'm using 7.0.10.0. The column widths remain unchanged. The only way I can get it to work is by using the overload that takes a minimum column width and setting it to something small. Unfortunately, that also results in every unused column shrinking down to that minimum width even though they aren't in the specified range.

Sample: ws.Cells["A2:G" + endRow].AutoFitColumns(1); results in something like this:

[ksowin]

After further investigation, I can see how this is happening. After creating the worksheet, I'm globally setting the style: ws.Cells.Style.Font.Name = "Calibri Light"; ws.Cells.Style.Font.Size = 8; ws.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; ws.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;

This action expands what EPPlus plus sees as the worksheet dimensions. To work around this, I changed my order of operations to:

1. Populate the data
2. Set the style settings for only the populated range.
3. Autofit the columns
4. Set the default style settings for the remainder of the worksheet.

Ideally, EPPlus would only AutoFit on columns that contain data, or set the worksheet dimensions to match the range that contains data.

[swmal]

@ksowin - thanks for this input. We are planning to have another look at this function and see if we can improve the performance.