OpenXML having trouble coloring cells

Question

I am attempting to style cells and I cant get the colors to work correctly, I am using the following Fill:

// <Fills>
Fill fill0 = new Fill();        // Default fill
Fill fill1 = new Fill(
    new PatternFill(
        new ForegroundColor() { Rgb = new HexBinaryValue() { Value = "DCDCDC" } }
    )
    { PatternType = PatternValues.Solid });

Fills fills = new Fills();      // appending fills
fills.Append(fill0);
fills.Append(fill1);

CellFormat _0_default = new CellFormat() { FontId = 0, FillId = 0, BorderId = 0 }; // Default style : Mandatory | Style ID =0
CellFormat _1_header = new CellFormat() { FontId = 1, FillId = 1, ApplyFill = true }; //HEADER

CellFormats cellformats = new CellFormats();
cellformats.Append(_0_default);
cellformats.Append(_1_header);

These are my only styles, and that is my only fill - I set the first row to StyleIndex = 1

Also, it doesn't seem to matter what I make the BackgroundColor or if I omit it completely.

From this link: https://blogs.msdn.microsoft.com/chrisquon/2009/11/30/stylizing-your-excel-worksheets-with-open-xml-2-0/

But the problem is that my cells now look like this:

Which you can see is not the gray that it should be - any idea what I am missing? Thank you.

I think that in your value **FFFFFF00** is the bug, RGB color uses only six characters and your input seems that it's for a RGBA value. — ganchito55, Mar 13 '17 at 23:46
Just to cover the obvious: are you using that fill with the cell? — Ray Fischer, Mar 14 '17 at 01:06
I think @RayFischer is right, the fill you are seeing look suspiciously like `PatternValues.Gray125`. Could you show the code where you use the `Fill`? If I had to guess I'd say you're out by one on your `FillId`. — petelids, Mar 14 '17 at 12:22
ok, updated - it does apply the bold, so it has to be the right styleindex right?! — naspinski, Mar 14 '17 at 14:30
Ok... so fillId 0 is ALWAYS transparent, and fillId 1 is ALWAYS Gray125, so you need to add those in manually, and then add your custom fills. Does not make a lot of sense to me either. — naspinski, Mar 14 '17 at 14:51
Possible duplicate of [Xlsx styles getting wrong fill pattern](https://stackoverflow.com/questions/19731176/xlsx-styles-getting-wrong-fill-pattern) — Lucius, Oct 03 '19 at 08:47

naspinski · Accepted Answer · 2018-06-01T13:55:36.883

For some reason I cannot seem to find documented, Fill Id 0 will always be None, and Fill Id 1 will always be Gray125. If you want a custom fill, you will need to get to at least Fill Id 2. Any further explanation to this would be greatly appreciated!

            // <Fills>
        Fill fill1 = new Fill(
            new PatternFill(
                new ForegroundColor() { Rgb = new HexBinaryValue() { Value = "DCDCDC" } }
            )
            { PatternType = PatternValues.Solid });

        Fills fills = new Fills(
            new Fill(new PatternFill() { PatternType = PatternValues.None }), //this is what it will be REGARDLESS of what you set it to
            new Fill(new PatternFill() { PatternType = PatternValues.Gray125 }), //this is what it will be REGARDLESS of what you set it to
            fill1);

        // <Borders>
        Border border0 = new Border();     // Default border

        Borders borders = new Borders();    // <APPENDING Borders>
        borders.Append(border0);

        CellFormat _0_default = new CellFormat() { FontId = 0, FillId = 0, BorderId = 0 }; // Default style : Mandatory | Style ID =0
        CellFormat _1_header = new CellFormat() { FontId = 1, FillId = 2, ApplyFill = true }; //HEADER

For anyone who interested in this, this is documented in ISO/IEC 29500 2.1.704 Part 1 Section 18.8.21, fills: "b. The standard allows for arbitrary definitions of these elements: In Excel, the first two Fill ("[ISO/IEC-29500-1] §18.8.20; fill") values are reserved and deviations from their predefined values are not respected" — grafgenerator, Feb 16 '20 at 14:55

OpenXML having trouble coloring cells

1 Answers1