2

I am trying to apply colours to certain cells in a spreadsheet generated by Spreadsheet Gear.

My current code

        var workbook = Factory.GetWorkbook();
        var worksheet = workbook.Worksheets["Sheet1"];
        // Set the new worksheet name
        worksheet.Name = "Group export";

        var cells = worksheet.Cells;

        cells[0, 0].Style.Font.Size = 20;

        cells[5, 0].Value = "Default Values";
        cells["A6"].Style.Interior.Color = System.Drawing.Color.FromArgb(166,166,166); //Line 86

However, when opening the spreadsheet, I find that the font size and cell colour is applied to every single cell in the spreadsheet. The "Default Values" cell is only in the correct cell, however any background or font styling I apply anywhere in sheet applies to all cells.

I set up a watch for cells["A6"].Style.Interior.Color and cells["A5"].Style.Interior.Color, and a breakpoint directly after line 86 to confirm that this is where the styling happens.

Why is the styling applying to all cells in the spreadsheet?

Sean Forman
  • 400
  • 4
  • 16

2 Answers2

4

The problem is that you are setting the Interior definition for the style (see IRange.Style.Interior and the IStyle interface) that the cell is using and not directly setting the interior for the cell itself (IRange.Interior). When you set any property at the style level, it will affect all other cells that use that same style.

Think "Normal", "Bad", "Good", etc., available from Excel's Ribbon > Home > Styles). By default, all cells utilize the "Normal" style, so by setting IRange.Style.Interior you are setting the color for all cells that utilize the "Normal" style--namely all cells in the workbook.

To set individual cell colors that don't affect any other cells using that style you would need to set the Interior directly under IRange, such as:

cells[0, 0].Font.Size = 20;
cells["A6"].Interior.Color = System.Drawing.Color.FromArgb(166,166,166);
Tim Andersen
  • 3,014
  • 1
  • 15
  • 11
  • `SpreadsheetGear.Color.FromArgb` instead of `System.Drawing.Color.FromArgb`? – Csaba Toth Jun 23 '16 at 05:52
  • 1
    `System.Drawing.Color` was used in SpreadsheetGear 2010 and earlier. SpreadsheetGear 2012 changed this to use its own `SpreadsheetGear.Color` struct. Read more about this change in the [SpreadsheetGear 2012 Breaking Changes](http://www.spreadsheetgear.com/support/help/spreadsheetgear.net.7.0/#SpreadsheetGear2012_Breaking_Changes.html) help page. The OP indicated System.Drawing so I matched my answer accordingly. – Tim Andersen Jun 23 '16 at 14:10
2

For some reason, accessing the Style part causes it to apply a style to the entire sheet, instead of just the range. Removing "Style" fixed the issue

Instead of using

cells["A6"].Style.Interior.Color = System.Drawing.Color.FromArgb(166,166,166);

Using

cells["A6"].Interior.Color = System.Drawing.Color.FromArgb(166,166,166);

Formatted the cells correctly

Sean Forman
  • 400
  • 4
  • 16