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.

Trestle

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. – Abey

Updated to reflect that - doesn't seem to change anything. – Trestle

Just to cover the obvious: are you using that fill with the cell? – Cutlery

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. – Iloilo

ok, updated - it does apply the bold, so it has to be the right styleindex right?! – Trestle

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. – Trestle

Possible duplicate of Xlsx styles getting wrong fill pattern – Aleuromancy

Answer 1

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

Trestle

I have exactly the same problem! Thanks for your post. – Geiss

Same issue! Thanks. – Anaplastic

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" – Foreandaft

Does it autoincrement starting at 2 (0 and 1 are taken) when you Append a fill? Or do you need the code to Append 2 fills so that our custom fills start at index 2? – Burberry

To elaborate, because this confused the crap out of me: if you instantiate the Stylesheet with less than two placeholder Fills at the beginning, you won't be able to use your custom Fills because their FillId will be invalid and the excel file will need repair. – Woke

FML - I've been trying to find out where Gray125 is coming from for hours 💣 – Oulu