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?

@Am_I_Helpful An .xslx is just a zip file. The XML contents are inside. — Polynomial, Oct 04 '16 at 09:07

Polynomial · Accepted Answer · 2016-11-02T17:55:30.953

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.

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. — Polynomial, Jul 04 '12 at 14:18
I'd give you more than just +1 if I could for pointing towards ClosedXML. That is a seriously easy to use library... Thanks! — pennyrave, Jan 09 '13 at 15:56
How do I modify this code such that it does a modification of an existing spreadsheet? — Nate Pet, Apr 08 '13 at 18:47
@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. — Polynomial, Apr 08 '13 at 20:23
@Polynomial is it possible to resize only Column Header in Width in case of exporting to excel — Rahul Chowdhury, Aug 09 '13 at 14:50
@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. — Polynomial, Aug 09 '13 at 14:59
@Polynomial It is indeed good for small spreadsheets but be careful if your working with large spreadsheets, you could run into memory issues. — Phil Cooper, Aug 22 '13 at 15:59
+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! — Jeff, Sep 24 '14 at 13:15
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. — Informagic, Nov 02 '16 at 13:29
Thanks a lot. Your answer did help me, but it gave a inspiration to figure out the solution for my issue. — Almett, May 25 '17 at 07:49
@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. — Roland, Jan 18 '19 at 11:35
@PhilCooper If resources are an issue for your large spreadsheet, why not use simple CSV? — Roland, Jan 18 '19 at 11:44
@Roland Thanks. I did figure it out actually. [Gradient Fill using ClosedXml + OpenXml](https://gist.github.com/OkumaScott/d8b2d2b3d27012f26ccb516ebf999fcf) — Scott Solmer, Jan 18 '19 at 13:51
Thanks for this! I can confirm what @Informagic says: you don't need to set the `Count` properties manually. — huysentruitw, May 21 '20 at 16:13
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 (https://github.com/ClosedXML/ClosedXML/issues/89#issuecomment-577078398) — Mike, Dec 15 '20 at 17:43
@Polynomial out of all the SO code I've seen to add styles, yours is the only one that worked as-is. Thank you! — Ketchup201, Apr 13 '23 at 17:56
This is a life-saver. Thanks for sharing and digging through this. — Ritwik Sen, Jul 11 '23 at 20:44

Luizgrs · Answer 2 · 2017-03-30T14:55:43.220

9

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.

edited Mar 30 '17 at 14:55

answered Mar 30 '17 at 14:47

Luizgrs

4,765
1
22
28

3

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.`. – Craig Apr 24 '19 at 13:24
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. – Geier Jun 27 '19 at 12:56
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! – Daniel Hernandez Apr 13 '22 at 21:55

Cell styles in OpenXML spreadsheet (SpreadsheetML)

2 Answers2

Use ClosedXML if you want to avoid this insanity.

Linked