Worksheet with SetForeColor runs out of memory

2 posts, 0 answers
  1. B
    B avatar
    74 posts
    Member since:
    Jul 2011

    Posted 16 Oct 2014 Link to this post

    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;
    }
  2. Anna
    Admin
    Anna avatar
    99 posts

    Posted 17 Oct 2014 Link to this post

    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
     

    Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

     
Back to Top