AutoFit method takes too long

GemBox.Spreadsheet
1

Hi,

I have a problem with Columns[i].AutoFit() option, it takes like 90% of overall request time.
I am exporting invoice items, fill excel with ~160k rows (~25 columns), and before creating a pivot table, am calling the AutoFit option for each column.

Here is my code:

AddInvoiceDataSheet(locations, model, db);

if (model.ExportInvoiceItems)
{
    int columnCount = _excelInvoiceItemsDataSheet.CalculateMaxUsedColumns();
    for (int i = 0; i < columnCount; i++)
    {
        _excelInvoiceItemsDataSheet.Columns[i].AutoFit(1,
            _excelInvoiceItemsDataSheet.Rows[0],
            _excelInvoiceItemsDataSheet.Rows[_excelInvoiceItemsDataSheet.Rows.Count - 1]);
    }
}
else
{
    int columnCount = _excelInvoiceDataSheet.CalculateMaxUsedColumns();
    for (int i = 0; i < columnCount; i++)
    {
        _excelInvoiceDataSheet.Columns[i].AutoFit(1,
            _excelInvoiceDataSheet.Rows[0],
            _excelInvoiceDataSheet.Rows[_excelInvoiceDataSheet.Rows.Count - 1]);
    }
}

While testing with timestamps, the longest operation was the AutoFit option.

Logging:

14:45:42 GetInvoiceExcel started …
14:45:42 sveStavkeRacunaQuery started …
14:45:47 sveStavkeRacunaQuery ToList() …
14:45:47 AddInvoiceItemsExcelToSheet started with 159659 items …
14:45:47 AddInvoiceItemsExcelToSheet foreach no. 1 …
…
14:45:50 AddInvoiceItemsExcelToSheet foreach no. 159001 …
14:45:50 AddInvoiceItemsExcelToSheet foreach ended …
14:45:53 AddInvoiceItemsExcelToSheet table created …
14:45:53 FillInvoiceExcelFileData AutoFit start …
14:45:53 FillInvoiceExcelFileData AutoFit column: 0 …
14:46:11 FillInvoiceExcelFileData AutoFit column: 1 …
14:46:27 FillInvoiceExcelFileData AutoFit column: 2 …
14:46:43 FillInvoiceExcelFileData AutoFit column: 3 …
14:47:01 FillInvoiceExcelFileData AutoFit column: 4 …
14:47:16 FillInvoiceExcelFileData AutoFit column: 5 …
14:47:30 FillInvoiceExcelFileData AutoFit column: 6 …
14:47:47 FillInvoiceExcelFileData AutoFit column: 7 …
14:47:47 FillInvoiceExcelFileData AutoFit column: 8 …
14:47:47 FillInvoiceExcelFileData AutoFit column: 9 …
14:48:00 FillInvoiceExcelFileData AutoFit column: 10 …
14:48:12 FillInvoiceExcelFileData AutoFit column: 11 …
14:48:25 FillInvoiceExcelFileData AutoFit column: 12 …
14:48:43 FillInvoiceExcelFileData AutoFit column: 13 …
14:49:01 FillInvoiceExcelFileData AutoFit column: 14 …
14:49:26 FillInvoiceExcelFileData AutoFit column: 15 …
14:49:52 FillInvoiceExcelFileData AutoFit column: 16 …
14:50:08 FillInvoiceExcelFileData AutoFit column: 17 …
14:50:20 FillInvoiceExcelFileData AutoFit column: 18 …
14:50:38 FillInvoiceExcelFileData AutoFit column: 19 …
14:50:51 FillInvoiceExcelFileData AutoFit column: 20 …
14:51:04 FillInvoiceExcelFileData AutoFit column: 21 …
14:51:16 FillInvoiceExcelFileData AutoFit column: 22 …
14:51:29 FillInvoiceExcelFileData AutoFit column: 23 …
14:51:44 FillInvoiceExcelFileData AutoFit column: 24 …
14:51:57 FillInvoiceExcelFileData AutoFit ended …
14:51:57 FillInvoiceExcelFileData Pivot create started …
14:52:00 FillInvoiceExcelFileData Pivot create ended …
14:52:00 FillInvoiceExcelFileData ended …
14:52:00 Data generated …
14:52:00 excelFile saving to response …

Are there anyway AutoFit option can be faster (call it for all columns at once) because I had to remove it for taking too much time and is not worth that time.

Thank You in advance!
Branko,
Remaris

2

Hi,

We will profile ExcelColumn.AutoFit method to see if we can optimize it, but for 160 000 rows for which the method must resolve cell style font, resolve cell value based on style’s number format and call GDI/GDI+ to measure the string for each of those 160 000 rows, I am not sure how much we will be able to speed it up.

Regards,
Stipo

3

Hi,

We profiled the AutoFit methods and half of the time is spent on System.Windows.Forms.TextRenderer.MeasureText method which we use to achieve maximum compatibility with MS Excel’s AutoFit and most of the other half is used on creating System.Drawing.Font instances.

We might be able to speed-up a method by caching the System.Drawing.Font instances, but then AutoFit in your code would drop from 6 minutes to maybe 3.5 minutes.

Let us know if this would be satisfactory for you and we will implement this change.

Regards,
Stipo

4

Hi,

I wanted to add that if you can, you could think about reducing the number of rows that are measured when performing the AutoFit method.

For instance, in the following example the AutoFit method will evaluate only cells within the first 100 rows:

_excelInvoiceItemsDataSheet.Columns[i].AutoFit(1,
    _excelInvoiceItemsDataSheet.Rows[0],
    _excelInvoiceItemsDataSheet.Rows[Math.Min(100, _excelInvoiceItemsDataSheet.Rows.Count - 1)]);

Also, if needed you could increase the scaling factor for the safety measure, from 1 to let’s say 1.5.

I hope this helps.

Regards,
Mario

1 Like
5

Hi, sry for late reply (I was on vacation).

I will try possible alternatives to speed up column fit as soon as I catch some time.

Thank You for suggestions.

Greetings
Branko, Remaris