Getting background cell in Excel using Open XML 2.0

Question

I am trying to get the backgroundcolor of a cell in an excel-spreadsheet. I use the Open XML 2.0 SDK, and I can open the * .xlsx file and get the cell values, for example. My background color code is as follows:

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 is that PatternFill.BackgroundColor returns only a natural number, I think it is a style id. 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 because I used the "built-in" color in excel - not a self-defined color ?!

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

Answer 1

Image of filling a cell in an Excel table consisting of two colors: background color and foreground color. The term foreground color is a little misleading. This is not the font color, but the foreground color of the fill pattern.

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

So, to get the background color of your cell (solid fill), you need to analyze the ForegroundColor property of the selected PatternFill . You must define the "color type" that the instance represents:

• Auto color and system color
• Indexed color.
• ARGB color (alpha, red, green, and blue)
• Color based themes.
• The hue value applied to the color.

For more information about the various "types of colors" see the following link .

Note that the value of the InnerText property for the ForegroundColor and BackgroundColor class depends on the color type. For example, in the case of theme-based color, the InnerText property InnerText set to an index in the ColorScheme collection.

The following example prints all the 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(); }