Worksheet with SetForeColor runs out of memory

[B]

Hi,

When I generate a Worksheet with foreground colors for every row this is slow for small Excel files (say 1000 rows and 25 columns) and runs out memory for a 4000 row by 25 column Excel (the .xls file without the colors is about 400k). When I comment out the color code below it is significantly faster (about 5 times) and doesnt run out of memory.

Regards,

Bayram

private static Workbook CreateWorkbook(RadGridView grid, IEnumerable<string> skipColumns)
{
    Workbook book = new Workbook();
    book.Sheets.Add(SheetType.Worksheet);
    Worksheet sheet = book.ActiveWorksheet;

    Color accentBorder = Office2013Palette.Palette.AccentColor;
    Color transparent = Colors.Transparent;
    Color black = Colors.Black;
    Color white = Colors.White;

    ThemableColor themeBlack = new ThemableColor(Color.FromArgb(0,0,0,0));
    ThemableColor themeWhite = new ThemableColor(Color.FromArgb(0, 255, 255, 255));

    CellBorder border = new CellBorder(CellBorderStyle.Thin, themeBlack);
    CellBorder noBorder = new CellBorder(CellBorderStyle.None, themeBlack);
    CellBorders borders = new CellBorders(border, border, border, border, border, border, noBorder, noBorder);

    int rowNumber = 0;
    int colNumber = 0;
    foreach (var column in grid.Columns)
    {
        if (column.Header is string)
        {
            string columnHeader = (string)column.Header;
            if (skipColumns == null || !skipColumns.Contains(columnHeader))
            {
                sheet.Cells[rowNumber, colNumber].SetValue(columnHeader);
                colNumber++;
            }
        }
    }
    CellSelection selection = sheet.Cells[0,0,0,colNumber-1];
    selection.SetIsBold(true);
    PatternFill solidPatternFill = new PatternFill(PatternType.Solid, accentBorder, transparent);
    selection.SetFill(solidPatternFill);
    selection.SetForeColor(themeWhite);

    HashSet<int> textColumns = new HashSet<int>();
    rowNumber++;
    foreach (var item in grid.Items)
    {
        if (item is DynRecord)
        {
            DynRecord record = (DynRecord)item;
            colNumber = 0;

            foreach (var column in grid.Columns)
            {
                if (column.Header is string)
                {
                    string columnHeader = (string)column.Header;
                    if (skipColumns == null || !skipColumns.Contains(columnHeader))
                    {
                        if (column is GridViewDataColumn)
                        {
                            GridViewDataColumn dataColumn = (GridViewDataColumn)column;
                            string propName = dataColumn.DataMemberBinding.Path.Path;

                            object value = record.Value(propName);
                            if (value != null)
                            {
                                if (value is string)
                                {
                                    textColumns.Add(colNumber);
                                    sheet.Cells[rowNumber, colNumber].SetValue((string)value);
                                }
                                else if (value is DateTime)
                                    sheet.Cells[rowNumber, colNumber].SetValue((DateTime)value);
                                else if (value is Decimal)
                                    sheet.Cells[rowNumber, colNumber].SetValue(Convert.ToDouble((decimal)value));
                                else
                                {
                                    textColumns.Add(colNumber);
                                    sheet.Cells[rowNumber, colNumber].SetValue(value.ToString());
                                }
                            }
                        }
                        colNumber++;
                    }
                }
            }
            // This part is slow and runs out of memory
            //Color color = record.GetRecordColor().Color;
            //if (color != Colors.Black)
            //{
            //    selection = sheet.Cells[rowNumber, 0, rowNumber, colNumber - 1];
            //    selection.SetForeColor(new ThemableColor(color));
            //}

            rowNumber++;
        }
    }
    selection = sheet.Cells[0, 0, rowNumber-1, colNumber - 1];
    selection.SetBorders(borders);


    // Set Text columns formatting to @ to prevent Excel autoformatting
    foreach (int i in textColumns)
    {
        selection = sheet.Cells[1, i, rowNumber - 1, i];
        selection.SetFormat(new CellValueFormat("@"));
    }

    // AutoFitWidth
    for (int i = 0; i < colNumber - 1; i++)
    {
        ColumnSelection columnSelection = sheet.Columns[i];
        columnSelection.AutoFitWidth();
        double width = sheet.Columns[i].GetWidth().Value.Value * 1.1;
        columnSelection.SetWidth(new ColumnWidth(width, false));
    }

    return book;
}

[Anna]

Hello,

Thank you for the sample code. Unfortunately, I am having trouble reproducing the performance problem. I had to make a few adjustments to the code to make it work on my side and I would like to make sure I've not omitted anything relevant. Could you please open a support ticket and attach a sample project?

Regards,
Anna
Telerik