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"?

Epact

The class SpreadsheetReader does not exists in OpenXML 2.5 – Cariotta

@Cariotta In order to use SpreadsheetReader, you need to also install DocumentFormat.OpenXML.Extensions. – Adowa

Answer 1

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

Lafountain

Thank you for your great answer. Just one point to add: If I don't change the style of a cell, the line ` int cellStyleIndex = (int)theCell.StyleIndex.Value;` causes a null-exception. How can I know for sure, that it is actually the default-style and "what" style (color etc.) this is? Thx in advance! – Epact

@chiffre: I think (I did some testing) if StyleIndex is null you have to use cell style index 0. However I did not found a note about it in the open xml specification. – Lafountain

Thanks for that information. There is really the need for a good and complete wrapper - where you can simply call "cell.GetColor" etc.. (and that actually works ;) ) – Epact

Thanks for this thorough explanation, but I find that you can't reliably get colors from ThemeElements.ColorScheme using ColorType.Theme as an index: Indexes 0 and 1 (usually black/white) have swapped places, and also indexes 2 and 3. These are the theme's "Dark1"/"Light1"/"Dark2"/"Light2" colors. Can we count on these indexes always being swapped, or do we need to find the theme colors some other way? – Yogi

The same code works for the font attributes as well. Thanks for helping. For the record: Extract the font id and get the font (type Font) from the Fonts property. – Punke

That's why I don't like OpenXML. It is too awkward! How is it possible that Microsoft did not implement a function for such a basic task as getting the color of a cell? – Cariotta

In order to use SpreadsheetReader, you need to also install DocumentFormat.OpenXML.Extensions. I found this out from this other question: #31585370 I would suggest an edit, but the edit queue is full. – Adowa

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.

Unnamed

Answer 3

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

Crofoot