Getting cell-backgroundcolor in Excel with Open XML 2.0

Question

I am trying to get the backgroundcolor of a cell in a excel-spreadsheet. I am using Open XML 2.0 SDK and I am able to open the *.xlsx-file and to get cell-values for example. My code for getting the Background-Color is the following:

   public BackgroundColor GetCellBackColor(Cell theCell, SpreadsheetDocument document)
    {
        BackgroundColor backGroundColor = null;
        WorkbookStylesPart styles = SpreadsheetReader.GetWorkbookStyles(document);
        int cellStyleIndex = (int)theCell.StyleIndex.Value;
        CellFormat cellFormat = (CellFormat)styles.Stylesheet.CellFormats.ChildElements[cellStyleIndex];
        Fill fill = (Fill)styles.Stylesheet.Fills.ChildElements[(int)cellFormat.FillId.Value];
        backGroundColor = fill.PatternFill.BackgroundColor;

        return backGroundColor;
    }

My problem here is, that PatternFill.BackgroundColor returns just a natural number, I think it's the id of the style. My problem is, that the line of code

DocumentFormat.OpenXml.Spreadsheet.Color c = (DocumentFormat.OpenXml.Spreadsheet.Color)styles.Stylesheet.Colors.ChildElements[Int32.Parse(backGroundColor.InnerText)];

returns with an error, because Stylesheet.Colors is null... ...maybe it's because I used a "built in" color in excel - not an self-defined color?!

Any ideas how I could "calculate" the real color-number from the "backGroundColor-Value"?

Answer 1

Hans's answer is great! I wanted to add to it since I had to parse a gradient fill for some of the columns. Here is my solution:

I just wanted to get the rgb string, if your requirement is different you will have to change the return type accordingly.

public static IEnumerable<string> GetCellFillColors(string sheetName,
     string addressName, WorkbookPart wbPart)
    {
        WorkbookStylesPart styles = wbPart.WorkbookStylesPart;

        Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().
          Where(s => s.Name == sheetName).FirstOrDefault();

        // Throw an exception if there is no sheet.
        if (theSheet == null)
        {
            throw new ArgumentException("sheetName");
        }

        // Retrieve a reference to the worksheet part.
        WorksheetPart wsPart =
            (WorksheetPart)(wbPart.GetPartById(theSheet.Id));

        // Use its Worksheet property to get a reference to the cell 
        // whose address matches the address you supplied.
        Cell theCell = wsPart.Worksheet.Descendants<Cell>().
          Where(c => c.CellReference == addressName).FirstOrDefault();

        int cellStyleIndex;
        if (theCell.StyleIndex == null) // I think (from testing) if the StyleIndex is null
        {                               // then this means use cell style index 0.
            cellStyleIndex = 0;           // However I did not found it in the open xml 
        }                               // specification.
        else
        {
            cellStyleIndex = (int)theCell.StyleIndex.Value;
        }

        CellFormat cellFormat = (CellFormat)styles.Stylesheet.CellFormats.ChildElements[cellStyleIndex];

        DocumentFormat.OpenXml.Spreadsheet.Fill fill = (DocumentFormat.OpenXml.Spreadsheet.Fill)styles.Stylesheet.Fills.ChildElements[(int)cellFormat.FillId.Value];

        if (fill.PatternFill != null)
        {
            return fill.PatternFill?.BackgroundColor?.Rgb?.ToString() != null ?
            new List<string>()
            {
                fill.PatternFill?.BackgroundColor?.Rgb?.ToString()
            } : new List<string>();
        }

        if (fill.GradientFill != null)
        {
            var colors = fill.GradientFill.ChildElements.Select(e => (DocumentFormat.OpenXml.Spreadsheet.GradientStop)e);
            return colors?.Select(c => c?.Color.Rgb?.ToString());

        }

        return null;
    }

Answer 2

Well I had a similiar use case in which I need to test which RGB Color is applied as background Color to Cell. Just Appending to code from your function,

backGroundColor = fill.PatternFill.BackgroundColor.Rgb.Value;
return backgroundColor;

This would return the Rgb Color Value used in the background of cell.

Answer 3

The fill pattern of a cell in an excel spreadsheet is composed of two colors: The background color and the foreground color. The term foreground color is a little bit misleading here. It is not the color of the font but the foreground color of the pattern fill.

For example if you fill the background of a cell with a solid color the ForegroundColor property of the releated PatternFill object of the cell is set to the choosen solid color value where as The BackgroundColor object is set to the system foreground color. The PatternType property of the PatternFill object is set to PatternValues.Solid.

So, to get the color value of your cell background (solid fill), you have to analyze the the ForegroundColor property of the releated PatternFill object. You have to determine the "type of color" the instance represents:

1. An automatic color and system dependent color
2. An indexed color.
3. An ARGB color (alpha, red, green and blue)
4. A theme based color.
5. A tint value applied to the color.

For more information about the different "color types" see the following link.

Please note that the meaning of the InnerText property of the ForegroundColor and BackgroundColor class depends on the color type. For example in case of a theme based color the InnerText property is set to the index into the ColorScheme collection.

The following example prints all background color information for all cells in a spreadsheet document:

public static PatternFill GetCellPatternFill(Cell theCell, SpreadsheetDocument document)
{ 
  WorkbookStylesPart styles = SpreadsheetReader.GetWorkbookStyles(document);

  int cellStyleIndex;
  if (theCell.StyleIndex == null) // I think (from testing) if the StyleIndex is null
  {                               // then this means use cell style index 0.
    cellStyleIndex = 0;           // However I did not found it in the open xml 
  }                               // specification.
  else
  {
    cellStyleIndex = (int)theCell.StyleIndex.Value;
  }      

  CellFormat cellFormat = (CellFormat)styles.Stylesheet.CellFormats.ChildElements[cellStyleIndex];

  Fill fill = (Fill)styles.Stylesheet.Fills.ChildElements[(int)cellFormat.FillId.Value];
  return fill.PatternFill;  
}

private static void PrintColorType(SpreadsheetDocument sd, DocumentFormat.OpenXml.Spreadsheet.ColorType ct)
{
  if (ct.Auto != null)
  {
    Console.Out.WriteLine("System auto color");
  }

  if (ct.Rgb != null)
  {
    Console.Out.WriteLine("RGB value -> {0}", ct.Rgb.Value);
  }

  if (ct.Indexed != null)
  {
    Console.Out.WriteLine("Indexed color -> {0}", ct.Indexed.Value);

    //IndexedColors ic = (IndexedColors)styles.Stylesheet.Colors.IndexedColors.ChildElements[(int)bgc.Indexed.Value];         
  }

  if (ct.Theme != null)
  {
    Console.Out.WriteLine("Theme -> {0}", ct.Theme.Value);

    Color2Type c2t = (Color2Type)sd.WorkbookPart.ThemePart.Theme.ThemeElements.ColorScheme.ChildElements[(int)ct.Theme.Value];

    Console.Out.WriteLine("RGB color model hex -> {0}", c2t.RgbColorModelHex.Val);
  }

  if (ct.Tint != null)
  {
    Console.Out.WriteLine("Tint value -> {0}", ct.Tint.Value);
  }
}

static void ReadAllBackgroundColors()
{
  using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open("c:\\temp\\bgcolor.xlsx", false))
  {
    WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
    foreach(WorksheetPart worksheetPart in workbookPart.WorksheetParts)
    {
      SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();

      foreach (Row r in sheetData.Elements<Row>())
      {
        foreach (Cell c in r.Elements<Cell>())
        {            
          Console.Out.WriteLine("----------------");
          PatternFill pf = GetCellPatternFill(c, spreadsheetDocument);        

          Console.Out.WriteLine("Pattern fill type -> {0}", pf.PatternType.Value);

          if (pf.PatternType == PatternValues.None)
          {
            Console.Out.WriteLine("No fill color specified");
            continue;
          }

          Console.Out.WriteLine("Summary foreground color:");
          PrintColorType(spreadsheetDocument, pf.ForegroundColor);
          Console.Out.WriteLine("Summary background color:");
          PrintColorType(spreadsheetDocument, pf.BackgroundColor);                          
        }
      }     
    }
  }
}

static void Main(string[] args)
{ 
  ReadAllBackgroundColors();
}