Performance issue when using AutoFitColumns Method

[ahmedtolba1984]

trafficstars

Dear, I got degradation in Performance when using AutoFitColumns especially with Large files (more than 500000 rows) sheet.Cells.AutoFitColumns(); Is there any workaround or hotfix for this issue?

[swmal]

Hello,

As AutofitColumns() loops through all cells in the range and measures the length of the text in each cell this will include millions of measurements depending on how many columns you have in the range. For more details see our wiki page about this function: https://github.com/EPPlusSoftware/EPPlus/wiki/Autofit-columns

If you are running EPPlus on Windows you can try to change to the fallback text measurer to see if that improves the performance:

package.Settings.TextSettings.PrimaryTextMeasurer = package.Settings.TextSettings.FallbackTextMeasurer

The fallback measurer does not use System.Drawing/GDI, so it might be a little faster.

If you still have bad performance, my advice would be to do AutofitColumns() on the first 1000 rows (or similar) and see if that gives you a good enough result.

[ahmedtolba1984]

@swmal Thanks for your feedback, I tried to apply your solution by autofitting the first 1000 rows but it didn't give me accurate result

            for (var i = 0; i < maxRowsToFit; i++)
            {
                for (var j = 0; j < columns.Length; j++)
                {
                    sheet.Cells[i + 1, j + 1].AutoFitColumns();
                }
            }`
Is the above code OK?

[swmal]

Hello @ahmedtolba1984

You should only call autofitcolumns once for the entire range. Like sheet.Cells["A1:D1000"].AutofitColumns() (or with numeric indexers).