Cell styles in OpenXML spreadsheet (SpreadsheetML)

Question

I've generated a .xlsx spreadsheet in C# using the OpenXML SDK, but can't figure out how to get cell styles working. I've been studying files produced by Excel, and can't quite figure out how it's done.

Right now, I'm creating a fill, creating a CellStyleFormat that points at the fill, creating a CellFormat that points at the index of the CellStyleFormat, then creating a CellStyle that points to the CellFormat.

Here's the code I'm using to generate the document:

Console.WriteLine("Creating document"); using (var spreadsheet = SpreadsheetDocument.Create("output.xlsx", SpreadsheetDocumentType.Workbook)) {     Console.WriteLine("Creating workbook");     spreadsheet.AddWorkbookPart();     spreadsheet.WorkbookPart.Workbook = new Workbook();     Console.WriteLine("Creating worksheet");     var wsPart = spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();     wsPart.Worksheet = new Worksheet();      var stylesPart = spreadsheet.WorkbookPart.AddNewPart<WorkbookStylesPart>();     stylesPart.Stylesheet = new Stylesheet();     stylesPart.Stylesheet.Fills = new Fills();      // create a solid red fill     var solidRed = new PatternFill() { PatternType = PatternValues.Solid };     solidRed.AppendChild(new BackgroundColor { Rgb = HexBinaryValue.FromString("FF00FF00") });      stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = new PatternFill() { PatternType = PatternValues.None } });     stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = solidRed });     stylesPart.Stylesheet.CellStyleFormats = new CellStyleFormats();     stylesPart.Stylesheet.CellStyleFormats.AppendChild(new CellFormat { FillId = 0, ApplyFill = false });     stylesPart.Stylesheet.CellStyleFormats.AppendChild(new CellFormat { FillId = 1, ApplyFill = true });     stylesPart.Stylesheet.CellFormats = new CellFormats();     stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat { FormatId = 0 });     stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat { FormatId = 1 });     stylesPart.Stylesheet.CellStyles = new CellStyles();     stylesPart.Stylesheet.CellStyles.AppendChild(new CellStyle { Name = "None", FormatId = 0 });     stylesPart.Stylesheet.CellStyles.AppendChild(new CellStyle { Name = "Solid Red", FormatId = 1 });      stylesPart.Stylesheet.Save();      Console.WriteLine("Creating sheet data");     var sheetData = wsPart.Worksheet.AppendChild(new SheetData());      Console.WriteLine("Adding rows / cells...");      var row = sheetData.AppendChild(new Row());     row.AppendChild(new Cell() { CellValue = new CellValue("This"),  DataType = CellValues.String });     row.AppendChild(new Cell() { CellValue = new CellValue("is"),    DataType = CellValues.String });     row.AppendChild(new Cell() { CellValue = new CellValue("a"),     DataType = CellValues.String });     row.AppendChild(new Cell() { CellValue = new CellValue("test."), DataType = CellValues.String });      sheetData.AppendChild(new Row());      row = sheetData.AppendChild(new Row());     row.AppendChild(new Cell() { CellValue = new CellValue("Value:"),   DataType = CellValues.String });     row.AppendChild(new Cell() { CellValue = new CellValue("123"),      DataType = CellValues.Number });     row.AppendChild(new Cell() { CellValue = new CellValue("Formula:"), DataType = CellValues.String });     row.AppendChild(new Cell() { CellFormula = new CellFormula("B3"),   StyleIndex = 1 }); //       Console.WriteLine("Saving worksheet");     wsPart.Worksheet.Save();      Console.WriteLine("Creating sheet list");     var sheets = spreadsheet.WorkbookPart.Workbook.AppendChild(new Sheets());     sheets.AppendChild(new Sheet() { Id = spreadsheet.WorkbookPart.GetIdOfPart(wsPart), SheetId = 1, Name = "Test" });      Console.WriteLine("Saving workbook");     spreadsheet.WorkbookPart.Workbook.Save();      Console.WriteLine("Done."); } 

Here's the generated XML:

workbook.xml

<?xml version="1.0" encoding="utf-8"?> <x:workbook xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">   <x:sheets>     <x:sheet name="Test" sheetId="1" r:id="Rbad86b8c80844a16" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" />   </x:sheets> </x:workbook> 

styles.xml

<?xml version="1.0" encoding="utf-8"?> <x:styleSheet xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">   <x:fills>     <x:fill>       <x:patternFill patternType="none" />     </x:fill>     <x:fill>       <x:patternFill patternType="solid">         <x:bgColor rgb="FF00FF00" />       </x:patternFill>     </x:fill>   </x:fills>   <x:cellStyleXfs>     <x:xf fillId="0" applyFill="0" />     <x:xf fillId="1" applyFill="1" />   </x:cellStyleXfs>   <x:cellXfs>     <x:xf xfId="0" />     <x:xf xfId="1" />   </x:cellXfs>   <x:cellStyles>     <x:cellStyle name="None" xfId="0" />     <x:cellStyle name="Solid Red" xfId="1" />   </x:cellStyles> </x:styleSheet> 

worksheets/sheet.xml

<?xml version="1.0" encoding="utf-8"?> <x:worksheet xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">   <x:sheetData>     <x:row>       <x:c t="str"><x:v>This</x:v></x:c>       <x:c t="str"><x:v>is</x:v></x:c>       <x:c t="str"><x:v>a</x:v></x:c>       <x:c t="str"><x:v>test.</x:v></x:c>     </x:row>     <x:row />     <x:row>       <x:c t="str"><x:v>Value:</x:v></x:c>       <x:c t="n"><x:v>123</x:v></x:c>       <x:c t="str"><x:v>Formula:</x:v></x:c>       <x:c s="1"><x:f>B3</x:f></x:c>     </x:row>   </x:sheetData> </x:worksheet> 

The last cell of the last row is where I'm trying to add the style.

This all validates properly when I run it through the OpenXML SDK Productivity Tool, but, when I attempt to open the file in Excel, I get the following error:

Repaired Records: Format from /xl/styles.xml part (Styles)

The spreadsheet then shows, but the fill isn't applied.

Any idea how to go about fixing this?

Answer 1

Right, I managed to figure this out, after a lot of experimentation.

It turns out that excel reserves styles 0 and 1 for normal cells and "Gray125" pattern fill respectively. Most of the above code can be removed, as we only need a CellFormat really.

Working code:

Console.WriteLine("Creating document"); using (var spreadsheet = SpreadsheetDocument.Create("output.xlsx", SpreadsheetDocumentType.Workbook)) {     Console.WriteLine("Creating workbook");     spreadsheet.AddWorkbookPart();     spreadsheet.WorkbookPart.Workbook = new Workbook();     Console.WriteLine("Creating worksheet");     var wsPart = spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();     wsPart.Worksheet = new Worksheet();      var stylesPart = spreadsheet.WorkbookPart.AddNewPart<WorkbookStylesPart>();     stylesPart.Stylesheet = new Stylesheet();      Console.WriteLine("Creating styles");      // blank font list     stylesPart.Stylesheet.Fonts = new Fonts();     stylesPart.Stylesheet.Fonts.Count = 1;     stylesPart.Stylesheet.Fonts.AppendChild(new Font());      // create fills     stylesPart.Stylesheet.Fills = new Fills();      // create a solid red fill     var solidRed = new PatternFill() { PatternType = PatternValues.Solid };     solidRed.ForegroundColor = new ForegroundColor { Rgb = HexBinaryValue.FromString("FFFF0000") }; // red fill     solidRed.BackgroundColor = new BackgroundColor { Indexed = 64 };      stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = new PatternFill { PatternType = PatternValues.None } }); // required, reserved by Excel     stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = new PatternFill { PatternType = PatternValues.Gray125 } }); // required, reserved by Excel     stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = solidRed });     stylesPart.Stylesheet.Fills.Count = 3;      // blank border list     stylesPart.Stylesheet.Borders = new Borders();     stylesPart.Stylesheet.Borders.Count = 1;     stylesPart.Stylesheet.Borders.AppendChild(new Border());      // blank cell format list     stylesPart.Stylesheet.CellStyleFormats = new CellStyleFormats();     stylesPart.Stylesheet.CellStyleFormats.Count = 1;     stylesPart.Stylesheet.CellStyleFormats.AppendChild(new CellFormat());      // cell format list     stylesPart.Stylesheet.CellFormats = new CellFormats();     // empty one for index 0, seems to be required     stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat());     // cell format references style format 0, font 0, border 0, fill 2 and applies the fill     stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat { FormatId = 0, FontId = 0, BorderId = 0, FillId = 2, ApplyFill = true }).AppendChild(new Alignment { Horizontal = HorizontalAlignmentValues.Center });     stylesPart.Stylesheet.CellFormats.Count = 2;      stylesPart.Stylesheet.Save();      Console.WriteLine("Creating sheet data");     var sheetData = wsPart.Worksheet.AppendChild(new SheetData());      Console.WriteLine("Adding rows / cells...");      var row = sheetData.AppendChild(new Row());     row.AppendChild(new Cell() { CellValue = new CellValue("This"),  DataType = CellValues.String });     row.AppendChild(new Cell() { CellValue = new CellValue("is"),    DataType = CellValues.String });     row.AppendChild(new Cell() { CellValue = new CellValue("a"),     DataType = CellValues.String });     row.AppendChild(new Cell() { CellValue = new CellValue("test."), DataType = CellValues.String });      sheetData.AppendChild(new Row());      row = sheetData.AppendChild(new Row());     row.AppendChild(new Cell() { CellValue = new CellValue("Value:"),   DataType = CellValues.String });     row.AppendChild(new Cell() { CellValue = new CellValue("123"),      DataType = CellValues.Number });     row.AppendChild(new Cell() { CellValue = new CellValue("Formula:"), DataType = CellValues.String });     // style index = 1, i.e. point at our fill format     row.AppendChild(new Cell() { CellFormula = new CellFormula("B3"),   DataType = CellValues.Number, StyleIndex = 1 });      Console.WriteLine("Saving worksheet");     wsPart.Worksheet.Save();      Console.WriteLine("Creating sheet list");     var sheets = spreadsheet.WorkbookPart.Workbook.AppendChild(new Sheets());     sheets.AppendChild(new Sheet() { Id = spreadsheet.WorkbookPart.GetIdOfPart(wsPart), SheetId = 1, Name = "Test" });      Console.WriteLine("Saving workbook");     spreadsheet.WorkbookPart.Workbook.Save();      Console.WriteLine("Done."); } 

---

Some advice:

Use ClosedXML if you want to avoid this insanity.

I cannot recommend ClosedXML highly enough if you're doing this kind of work. The OpenXML API and format is horribly tedious to work with on its own, with all sorts of undocumented cases. ClosedXML does so much of the leg work for you. They're also really great at getting bugs fixed quickly.

Answer 2

A more generic answer, all this I found after testing, so no documentation to point to.

Once you set a CellFormats collection in the stylesheet Excel runs a deeper validation on it.

CellFormats cannot be empty, it must have at least one CellFormat there.

Once you add a CellFormat, Excel will complain if Fills, Fonts or Borders collections are empty.

First Font is used as default for whole workbook and also Column/Row headers in Excel.

Excel will ignore first CellFormat, so just add an empty one.

If you need a Border or Fill in your format, Excel will also ignore first Border and Fill, so also add empty ones as first child in Borders and Fills.

Finally, starting in the second CellFormat (s = "1") you're good to go.

Tested in Excel 2010.