Applying number formatting in OpenXML

Question

I'm trying to create an Excel spreadsheet from scratch using OpenXML and I've got everything working okay (dumping actual values into actual cells), but now I'm trying to apply number formatting to columns and I'm running into a problem. I have styles.xml that looks like this:

<?xml version="1.0" encoding="utf-8"?>
<x:styleSheet xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<x:numFmts count="12">
    <x:numFmt numFmtId="216" formatCode="#,###" />
    <x:numFmt numFmtId="217" formatCode="$#,###" />
    <x:numFmt numFmtId="218" formatCode="#0.00" />
    <x:numFmt numFmtId="219" formatCode="#,###" />
    <x:numFmt numFmtId="220" formatCode="#0.0%" />
    <x:numFmt numFmtId="221" formatCode="#,###" />
    <x:numFmt numFmtId="222" formatCode="#0.0%" />
    <x:numFmt numFmtId="223" formatCode="#0.0%" />
    <x:numFmt numFmtId="224" formatCode="#0.0%" />
    <x:numFmt numFmtId="225" formatCode="#,###" />
    <x:numFmt numFmtId="226" formatCode="#,###" />
    <x:numFmt numFmtId="227" formatCode="#0.0%" />
</x:numFmts>
<x:cellXfs count="12">
    <x:xf numFmtId="216" applyNumberFormat="1" />
    <x:xf numFmtId="217" applyNumberFormat="1" />
    <x:xf numFmtId="218" applyNumberFormat="1" />
    <x:xf numFmtId="219" applyNumberFormat="1" />
    <x:xf numFmtId="220" applyNumberFormat="1" />
    <x:xf numFmtId="221" applyNumberFormat="1" />
    <x:xf numFmtId="222" applyNumberFormat="1" />
    <x:xf numFmtId="223" applyNumberFormat="1" />
    <x:xf numFmtId="224" applyNumberFormat="1" />
    <x:xf numFmtId="225" applyNumberFormat="1" />
    <x:xf numFmtId="226" applyNumberFormat="1" />
    <x:xf numFmtId="227" applyNumberFormat="1" />
</x:cellXfs>
</x:styleSheet>

But Excel doesn't seem to like it and removes it after "repairing" the file. What am I missing here? The docs are a little spotty on exactly what is needed to keep Excel happy.

I manually assigned the numFmtId starting at what I thought might be a suitably high number. Is that the right way to do it?

Also, I'm aware that the formatCode are duplicated, but I'd assumed Excel wouldn't get tripped up by that, I could consolidate them if necessary.

My column definitions look like this (in sheet.xml):

<x:cols>
    <x:col min="1" max="1" width="7" />
    <x:col min="2" max="2" width="58" />
    <x:col min="3" max="3" width="16" style="0" />
    <x:col min="4" max="4" width="6" style="1" />
    <x:col min="5" max="5" width="17" style="2" />
    <x:col min="6" max="6" width="16" style="3" />
    <x:col min="7" max="7" width="18" style="4" />
    <x:col min="8" max="8" width="17" style="5" />
    <x:col min="9" max="9" width="20" style="6" />
    <x:col min="10" max="10" width="21" style="7" />
    <x:col min="11" max="11" width="21" style="8" />
    <x:col min="12" max="12" width="16" style="9" />
    <x:col min="13" max="13" width="16" style="10" />
    <x:col min="14" max="14" width="19" style="11" />
</x:cols>

For comparison - here's a snippet from a working styles.xml file created by Excel itself:

<numFmts count="1">
    <numFmt numFmtId="164" formatCode="#,##0\p"/>   // where does 164 come from?
</numFmts>

<cellXfs count="3">
    <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>  // do you always need a 0 xf entry? It isn't referenced in the sheet.xml file
    <xf numFmtId="3" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>        // assuming numFmtId = 3 is a built in format??
    <xf numFmtId="164" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
</cellXfs>

Solution

Answering my own question, but I came across this post:

http://polymathprogrammer.com/2009/11/09/how-to-create-stylesheet-in-excel-open-xml/

Which suggested that the minimum stylesheet requires quite a few more things than just the numFmts and the cellXfs. So I adapted their code to produce a minimal stylesheet ready for me to insert my cell formats and number formats (which I was doing in a loop):

    private Stylesheet CreateStylesheet()
    {
        Stylesheet ss = new Stylesheet();

        Fonts fts = new Fonts();
        DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font()
        {
            FontName = new FontName()
            {
                Val = "Calibri"
            },
            FontSize = new FontSize()
            {
                Val = 11
            }
        };
        fts.Append(ft);
        fts.Count = (uint)fts.ChildElements.Count;

        Fills fills = new Fills();
        fills.Append(new Fill()
        {
            PatternFill = new PatternFill()
            {
                PatternType = PatternValues.None
            }
        });
        fills.Append(new Fill()
        {
            PatternFill = new PatternFill()
            {
                PatternType = PatternValues.Gray125
            }
        });
        fills.Count = (uint)fills.ChildElements.Count;

        Borders borders = new Borders();
        Border border = new Border()
        {
            LeftBorder = new LeftBorder(),
            RightBorder = new RightBorder(),
            TopBorder = new TopBorder(),
            BottomBorder = new BottomBorder(),
            DiagonalBorder = new DiagonalBorder()
        };
        borders.Append(border);
        borders.Count = (uint)borders.ChildElements.Count;

        CellStyleFormats csfs = new CellStyleFormats();
        CellFormat cf = new CellFormat() { 
            NumberFormatId = 0,
            FontId = 0,
            FillId = 0,
            BorderId = 0
        };

        csfs.Append(cf);
        csfs.Count = (uint)csfs.ChildElements.Count;

        NumberingFormats nfs = new NumberingFormats();
        CellFormats cfs = new CellFormats();
        cf = new CellFormat() 
        { 
            NumberFormatId = 0,
            FontId = 0,
            FillId = 0,
            BorderId = 0,
            FormatId = 0
        };
        cfs.Append(cf);

        ss.Append(nfs);
        ss.Append(fts);
        ss.Append(fills);
        ss.Append(borders);
        ss.Append(csfs);
        ss.Append(cfs);

        CellStyles css = new CellStyles();
        CellStyle cs = new CellStyle() 
        {
            Name = "Normal",
            FormatId = 0,
            BuiltinId = 0
        };
        css.Append(cs);
        css.Count = (uint)css.ChildElements.Count;
        ss.Append(css);

        DifferentialFormats dfs = new DifferentialFormats();
        dfs.Count = 0;
        ss.Append(dfs);

        TableStyles tss = new TableStyles() 
        {
            Count = 0,
            DefaultTableStyle = "TableStyleMedium9",
            DefaultPivotStyle = "PivotStyleLight16"
        };
        ss.Append(tss);

        return ss;
    }

Not positive that there aren't things there that could be dropped, but I don't have the patience to go through it by trial-and-error to see if it can be made any slimmer.

I guess a cell format must have a FontId, FillId, BorderId and FormatId in addition to the NumberFormatId and in order to have those ids, you need to create at least one entry for each of them. This is despite the XML Schema labeling them as "optional".