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?

Ns

@Am_I_Helpful An .xslx is just a zip file. The XML contents are inside. – Ns

Wasn't aware of that. Thanks, it helped! And, +1. – Marylinmarylinda

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.

Ns

By the way, if anyone comes back to this at a later date, I highly recommend checking out the ClosedXML lib. It's so much easier to work with than OpenXML directly. – Ns

I'd give you more than just +1 if I could for pointing towards ClosedXML. That is a seriously easy to use library... Thanks! – Unconquerable

How do I modify this code such that it does a modification of an existing spreadsheet? – Walli

@NatePet It's been so long since I looked into it, I can't remember. To be fair, you should really look at ClosedXML. It makes editing and creation of sheets ridiculously easy. OpenXML is comparatively complex and difficult to master. – Ns

@Ns is it possible to resize only Column Header in Width in case of exporting to excel – Nonu

@RahulChowdhury Again, been ages since I looked at this. No idea, sorry. You should really try the ClosedXML library and ask on their help section if you have questions. – Ns

@Ns It is indeed good for small spreadsheets but be careful if your working with large spreadsheets, you could run into memory issues. – Changeless

+1 Thanks for explaining this: "It turns out that excel reserves styles 0 and 1 for normal cells and "Gray125" pattern fill respectively". I came across this same situation and did not know why my styles for index 1 were not working! – Chibcha

You, sir, deserve my sincerest gratitude for providing a working code example. I don’t think setting the list counts is necessary, though—actually, I don’t see why the Count property has a setter. At least in my case, it works without doing the assignment, and it spares one the hassle of keeping count of the list elements. – Dawes

Thanks a lot. Your answer did help me, but it gave a inspiration to figure out the solution for my issue. – Hypsometer

@Okuma.Scott Good luck trying to create your gradient fill in OpenXML. I could not even create a simple HyperLink. I tried to reverse engineer the extra code for a hyperlink using the Productivity Tool (sic), only to discover that adding a HyperLink also added a new Style, i.e. for a blue underlined text. Also, all existing styles got renumbered, and a diff between the generated code without and with the hyperlink showed over 100 code changes. This is what Polynomial means with Insanity, only should he use an even bigger font size. With ClosedXML, adding a hyperlink was a snap. – Roup

@PhilCooper If resources are an issue for your large spreadsheet, why not use simple CSV? – Roup

@Roup Thanks. I did figure it out actually. Gradient Fill using ClosedXml + OpenXml – Georgiegeorgina

Thanks for this! I can confirm what @Dawes says: you don't need to set the Count properties manually. – Sorensen

Thanks so much for the ClosedXML pointer! They are a lifesaver! One caveat: they cannot handle checkboxes (to be fair, I did not know of checkboxes in Excel before this project, so not much of a caveat) Fr checkboxes I still needed to use OpenXML separately to edit them, using code I found there (github.com/ClosedXML/ClosedXML/issues/89#issuecomment-577078398) – Bergeron

@Ns out of all the SO code I've seen to add styles, yours is the only one that worked as-is. Thank you! – Mussolini

This is a life-saver. Thanks for sharing and digging through this. – Halfmoon

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.

Bribery

Burned over an hour on this 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.. – Marked

Thank you, I can confirm this as I just independently stumbled upon this madness. Couldn't find a word about it in the documentation, too. – Deodorant

Oh wow, this was what I am missing. Excel will ignore first CellFormat, so just add an empty one.. Rejected on my pull request so I had to comment a summary and add the link to this answer. Thank you! – Enervated

"First Font is used as default for whole workbook". How to override this? I want to use multiple fonts. – Deina

Answer 3

ClosedXML really makes this a lot easier. Here is some ClosedXML styling code:

//workbook styling
workbook.Style.Border.BottomBorder = XLBorderStyleValues.Thick;

//worksheet styling
worksheet.Style.Font.Strikethrough = true;

//range styling
ws.Range("A1:C3").Style.Font.Bold = true;

//cell styling
worksheet.Cell("A1").Style.Fill.BackgroundColor = XLColor.Gray;

SlapKit is an upgrade of ClosedXML that supports more advanced cell styling scenarios out of the box. Since it is based on ClosedXML it has exactly the same API on functionality that is common. It can do some cool extra stuff, though:

//------You can use predefined styles (not exists in ClosedXML)--------

//workbook styling
workbook.Style = workbook.PredefinedStyles[XLBuiltInStyleType.Accent1];

//worksheet styling
worksheet.Style = workbook.PredefinedStyles[XLBuiltInStyleType.Accent2];

//range styling
ws.Range("A1:C3").Style = workbook.PredefinedStyles[XLBuiltInStyleType.Good];

//cell styling
worksheet.Cell("B1").Style = workbook.PredefinedStyles[XLBuiltInStyleType.Bad];

//You can customize the custom styles according to your preferences.
workbook.PredefinedStyles[XLBuiltInStyleType.Bad]
    .Fill.SetBackgroundColor(XLColor.Red)
    .Font.SetFontColor(XLColor.White)
    .Font.SetBold();
 
//You can create your custom styles.
IXLCustomPredefinedStyle styleGood = workbook.PredefinedStyles["my style"];
styleGood
    .Fill.SetBackgroundColor(XLColor.Green)
    .Font.SetFontColor(XLColor.White)
    .Font.SetBold();

//use the custom-named style
worksheet.Cell("B1").Style = styleGood;

There is more of it in their documentation (on a side note, being based on ClosedXML they are documenting all ClosedXML features, too, which seems to augment ClosedXML documentation really nicely)

Rosenberger