Get Excel cell ForegroundColor

[hehehewei]

When using OpenXML to retrieve the ForegroundColor of cells in Excel, you may encounter situations where some colors can be obtained while others cannot. What could be the reason for this?

       public static string GetCellBackgroundColor(WorkbookPart workbookPart, Cell cell)
        {
            CellFormat cellFormat = (CellFormat)workbookPart.WorkbookStylesPart.Stylesheet.CellFormats.ElementAt((int)cell.StyleIndex.Value);

            Fill fill = (Fill)workbookPart.WorkbookStylesPart.Stylesheet.Fills.ElementAt((int)cellFormat.FillId.Value);
            PatternFill patternFill = (PatternFill)fill.PatternFill;
            if (patternFill != null && patternFill.PatternType != null && patternFill.PatternType.Value == PatternValues.Solid)
            {
                if (patternFill.ForegroundColor != null && patternFill.ForegroundColor.Rgb != null)
                {
                    string rgbValue = patternFill.ForegroundColor.Rgb.Value;
                    string hexValue = ConvertRgbToHex(rgbValue);
                    return hexValue;
                }
            }
            return null;
        }

I’m unable to retrieve the foreground color when the ForegroundColor code of the Excel cell is '#9BC2E6'.This cell's patternFill.ForegroundColor.Rgb is null.
What is the actual reason behind this? Thanks a lot!

[tomjebo]

Hi @hehehewei,

If the cell color you're setting in Excel is really just the cell's foreground color, that's not going to affect the fill color. Fill color is set in the UI by the Fill tab of the Font properties dialog and would cause the FillId value to be set to something other than the default "no pattern" id which is usually 0.

If you only set the font's foreground color, you get:

  <cellXfs count="2">
    <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>
    <xf numFmtId="0" fontId="1" fillId="0" borderId="0" xfId="0" applyFont="1"/>
  </cellXfs>

fillId is 0 here and when you look up that fill, the pattern is "none":

    <fill>
      <patternFill patternType="none"/>
    </fill>

If you set the Fill color (or pattern) in the UI, then the fillId gets some other fill index:

  <cellXfs count="2">
    <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>
    <xf numFmtId="0" fontId="1" fillId="2" borderId="0" xfId="0" applyFont="1" applyFill="1"/>
  </cellXfs>

This fillId is set to 2 which through the UI, I set to your fill color of rgb #9BC2E6:

    <fill>
      <patternFill patternType="solid">
        <fgColor rgb="FF9BC2E6"/>
        <bgColor indexed="64"/>
      </patternFill>
    </fill>

So maybe you are just chasing the wrong style property to get the cell's fill RGB? If you want the font's foreground color, you should be looking at the Stylesheet.Fonts collection and checking the correct Font's color rgb attribute.
Not sure if that's what's going on but if you provide the document or XML excerpts from the styles.xml and sheet1.xml parts in question, then it might confirm what's happening.

[robso86]

This breakdown has saved me so much frustration in getting the background colour of a cell through OpenXML.
I have been stepping through my code completely at a loss as to why the PatternFill.BackgroundColor was always Nothing, swapped over to Foreground and everything works as expected.
Thank you for sharing this.

[AlfredHellstern]

closing