CODEWITHSUNDEEP
c#spreadsheetgear
Sean Forman
Sean Forman

Reputation: 400

Colour applying to all cells

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?

Upvotes: 2

Views: 3486

Answers (2)

Tim Andersen
Tim Andersen

Reputation: 3184

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);

Upvotes: 4

Sean Forman
Sean Forman

Reputation: 400

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

Upvotes: 2

Related Questions