Setting HorizontalAlignment on a column results in a OutOfMemoryException

[Paul Meems]

Using v17.4460.0.46

I create a new workbook with 1 worksheet.

I have a list returned from my database.

I use `ImportData()` to add this list to my sheet.

This works fine.

Next, I need to change some styles of some columns.

With a large number of rows (156k+) and 51 columns, this almost always results in a OutOfMemoryException.

What is the best workflow to set the horizontal alignment or number format of a column?

I now use this piece of code:

worksheet.UsedRangeIncludesFormatting = false;

var usedRange = worksheet.UsedRange;

var lastColumn = usedRange.LastColumn;

var firstRow = usedRange.Row;

var migrantRange = worksheet.MigrantRange;

for (var i = 0; i <= lastColumn; i++)

{

    var index = i + 1;

    migrantRange.ResetRowColumn(firstRow, index);

    // columns is an list of ColumnStyle.

    // ColumnStyle is a struct: ColumnStyle(string name, double columnWidth = 0, string headerStyleName = null, string numberFormat = null, ExcelHAlign horizontalAlignment = ExcelHAlign.HAlignGeneral)

    var matches = columns.Where(p => string.Equals(p.Name, migrantRange.Text, StringComparison.CurrentCulture)).ToList();

    if (!matches.Any()) continue;

    // The column was found

    var foundColumn = matches[0];

    if (!string.IsNullOrEmpty(foundColumn.HeaderStyleName))

    {

       // Set header style

        migrantRange.CellStyleName = foundColumn.HeaderStyleName;

        if (migrantRange.Text.Contains('-') || migrantRange.Text.Contains(' '))

        {

            try

            {

                migrantRange.WrapText = true;

                migrantRange.AutofitRows();

            }

            catch (Exception e)

            {

                Log.Warn("Warning with AutofitRows: " + e.Message);

                // throw; swallow

            }

        }

    }

    if (foundColumn.ColumnWidth > 0)

    {

        // Change column width

        if (foundColumn.ColumnWidth > worksheet.Columns[i].ColumnWidth)

        {

            try

            {

                worksheet.Columns[i].ColumnWidth = foundColumn.ColumnWidth;

            }

            catch (Exception e)

            {

                Log.Warn("Warning with ColumnWidth: " + e.Message);

                // throw; swallow

            }

        }

    }

    var range = worksheet.Range[firstRow, index, usedRange.LastRow, index];

    if (!string.IsNullOrEmpty(foundColumn.NumberFormat))

    {

        // Set number format

        try

        {

            range.NumberFormat = foundColumn.NumberFormat;

        }

        catch (Exception e)

        {

            Log.Warn("Warning with setting NumberFormat: " + e.Message);

            // throw; swallow

        }

    }

    try

    {

        // Set HorizontalAlignment, always results in a OutOfMemoryException  <----------

        range.CellStyle.HorizontalAlignment = foundColumn.HorizontalAlignment;

    }

    catch (Exception e)

    {

        Log.Warn("Warning with setting HorizontalAlignment: " + e.Message);

        // throw; swallow

    }

}

[Paul Meems]

Also worksheet.UsedRange.AutofitColumns(); takes a long time (6 min.) and worksheet.UsedRange.BorderAround(ExcelLineStyle.Thin, Color.FromArgb(128, 128, 128)); is already running for 1 hour, 3 min.

I'm already using worksheet.UsedRangeIncludesFormatting = false; 

Do I have more possibilities to speed this up.

[Shamini Kiruba Sobers]

Hi Paul, 

Greetings from Syncfusion. 

Please find the response for your queries from the below table. 

Query	Response
Next, I need to change some styles of some columns.  With a large number of rows (156k+) and 51 columns, this almost always results in a OutOfMemoryException.    What is the best workflow to set the horizontal alignment or number format of a column?	Kindly look into the following link and try applying global styles instead of looping each cell and apply the same style to large range.  https://help.syncfusion.com/file-formats/xlsio/improving-performance#styles
Also worksheet.UsedRange.AutofitColumns(); takes a long time (6 min.)	Unfortunately, using AutoFit for the entire used range will reduce the performance. Please refer the following link.  https://help.syncfusion.com/file-formats/xlsio/improving-performance#autofit
worksheet.UsedRange.BorderAround(ExcelLineStyle.Thin, Color.FromArgb(128, 128, 128)); is already running for 1 hour, 3 min.	Similarly, accessing each cell in the used range (with 156k+ rows and 51 columns) and setting borders will affect the performance. However, we are checking on it currently to give you the better solution. We will update further details on 20th March 2020.

Kindly let us know if the provided solution helps or share us the input document through email: [email protected] so that we can assist you better. 

Regards, 

Shamini. 

[Shamini Kiruba Sobers]

Hi Paul, 

Please find the response for your query in the below table. 

Query

Response

worksheet.UsedRange.BorderAround(ExcelLineStyle.Thin, Color.FromArgb(128, 128, 128)); is already running for 1 hour, 3 min.

We have tried this code with sample input data (worksheet having sample data in 156k+ rows and 51 columns) and we got it run within 35 seconds. Please find the tried sample below.  https://www.syncfusion.com/downloads/support/directtrac/general/ze/F152527785311594  The issue could be file specific, we guess. Kindly share us the input file or the data imported in the worksheet, so that we can provide you the prompt solution at the earliest.

Thanks, 

Shamini. 

[Paul Meems]

Thanks for the sample project and the global style suggestions.

I will have a closer look at the global styles. I'm already using them for font and background color.

I will also use them for data formatting and alignments.

I tried your sample project.

On my pc BorderAround takes 16s. 

I also added BorderInside, which takes an additional 41s

Saving this file takes another 14s.

When I use the same code with one of my data files (38.5MB, last cell 'EA87762'):

BorderAround: 23s

BorderInside: 56s

Save: 18s

Which is reasonable.

With my code, which is much more complicated than your sample:

I can't get BorderAround and BorderInside to work when the resulting file as more than 90k rows. 

So I skip those commands when my dataset has more than 90k rows.

My largest file (59.6MB, last cell DY152732) I use with your code and get these results:

BorderAround: OutOfMemoryException

BorderInside: 1m 48s

Save: 33s

The resulting file with the borders is 73MB.

I think you want this file but it is full of personal data. I'll try to remove that and send it to you soon.

Another solution might be to be able to set a default styling on an empty workbook (or worksheet), which will be used when adding data.

That might speed things up as well.

[Shamini Kiruba Sobers]

Hi Paul, 

  

Thanks for the update. 

  

It looks like we have a performance issue with BorderAround() method. We will make efforts to solve it soon. However, we have prepared a workaround of BorderAround() method to overcome the OutOfMemoryException as below. 

Code snippet: 

 

            ExcelEngine excelEngine = new ExcelEngine(); 

            IApplication application = excelEngine.Excel; 

            IWorkbook workbook = application.Workbooks.Open("SampleData.xlsx"); 

            IWorksheet worksheet = workbook.Worksheets[0]; 

                                            

            Stopwatch stopwatch = new Stopwatch(); 

            stopwatch.Start(); 

            //worksheet.UsedRange.BorderAround(ExcelLineStyle.Thin, Color.FromArgb(128, 128, 128)); 

            // Below method can be called instead of the above commented method, where exception is thrown. 

            BorderAround(worksheet.UsedRange, ExcelLineStyle.Thick, Color.FromArgb(128, 128, 128)); 

            stopwatch.Stop(); 

            TimeSpan timeSpan = stopwatch.Elapsed; 

            MessageBox.Show(timeSpan.Seconds.ToString() + " Seconds"); 

                                            

            workbook.SaveAs("../../Data/output.xlsx"); 

            workbook.Close(); 

            excelEngine.Dispose(); 

 

        private void BorderAround(IRange range, ExcelLineStyle lineStyle, Color color)
        {            
            int firstRow = range.Row;
            int lastRow = range.LastRow;
            int firstColumn = range.Column;
            int lastColumn = range.LastColumn; 

  

            IRange range1 = range[firstRow, firstColumn, lastRow, firstColumn];
            IRange range2 = range[firstRow, lastColumn, lastRow, lastColumn];
            IRange range3 = range[firstRow, firstColumn, firstRow, lastColumn];
            IRange range4 = range[lastRow, firstColumn, lastRow, lastColumn]; 

  

            range1.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = lineStyle;
            range1.Borders[ExcelBordersIndex.EdgeLeft].ColorRGB = color;
            range2.Borders[ExcelBordersIndex.EdgeRight].LineStyle = lineStyle;
            range2.Borders[ExcelBordersIndex.EdgeRight].ColorRGB = color;
            range3.Borders[ExcelBordersIndex.EdgeTop].LineStyle = lineStyle;
            range3.Borders[ExcelBordersIndex.EdgeTop].ColorRGB = color;
            range4.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = lineStyle;
            range4.Borders[ExcelBordersIndex.EdgeBottom].ColorRGB = color;
        } 

We have also prepared a working sample with the same. The sample contains the BorderAround() method which takes 12 seconds to apply BorderAround for a large range with last cell EA156104.
https://www.syncfusion.com/downloads/support/directtrac/general/ze/F1525271627833031 

  

Also, kindly look into the following link to set default styles on an entire row or an entire column. 

https://help.syncfusion.com/file-formats/xlsio/working-with-cell-or-range-formatting?cs-save-lang=1&cs-lang=csharp#set-default-style-for-row-or-column 

  

  

Regards, 

Shamini.