Minimal style sheet for Excel Open XML with dates?

Question

I'm trying to create an Excel file using the Open XML SDK with the minimal style sheet needed that will allow me to format a cell as a date. Below is my attempt at a style sheet:

<?xml version="1.0" encoding="utf-8"?>
<x:styleSheet xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <x:cellXfs count="1">
        <x:xf numFmtId="14" xfId="0" applyNumberFormat="1" />
    </x:cellXfs>
</x:styleSheet>

Even without referencing this style from a cell, Excel tells me that my file is corrupt. If I remove the cellXfs element, the file opens fine. Can someone explain what else I need to add to this? The C# code that creates the style sheet is below.

var stylesheet = new Stylesheet();
var cellFormats = new CellFormats() { Count = 1 };
var cellFormat = new CellFormat();
cellFormat.NumberFormatId = 14;
cellFormat.FormatId = 0;
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cellFormats.Append(cellFormat);
stylesheet.Append(cellFormats);

I've tried adding in cell style formats and cell styles, but just having the above seems to break the document and I'm unsure why.

Answer 1

Through a lot of trial and error, I've found that a style sheet needs the following:

• Default font
• Default fill
• Default border
• Default cell format

Leaving any of those out will cause Excel to error, unless all of them are left out. In addition, I added another cell format for the date.

I hope this is useful to someone else. The following code creates a working style sheet that allows for a date format (number format id 22):

var stylesheet = new Stylesheet();

// Default Font
var fonts = new Fonts() { Count = 1, KnownFonts = BooleanValue.FromBoolean(true) };
var font = new Font
                {
                    FontSize = new FontSize() { Val = 11 },
                    FontName = new FontName() { Val = "Calibri" },
                    FontFamilyNumbering = new FontFamilyNumbering() { Val = 2 },
                    FontScheme = new FontScheme() { Val = new EnumValue<FontSchemeValues>(FontSchemeValues.Minor) }
                };
fonts.Append(font);
stylesheet.Append(fonts);

// Default Fill
var fills = new Fills() { Count = 1 };
var fill = new Fill();
fill.PatternFill = new PatternFill() { PatternType = new EnumValue<PatternValues>(PatternValues.None) };
fills.Append(fill);
stylesheet.Append(fills);

// Default Border
var borders = new Borders() { Count = 1 };
var border = new Border
                    {
                        LeftBorder = new LeftBorder(),
                        RightBorder = new RightBorder(),
                        TopBorder = new TopBorder(),
                        BottomBorder = new BottomBorder(),
                        DiagonalBorder = new DiagonalBorder()
                    };
borders.Append(border);
stylesheet.Append(borders);

// Default cell format and a date cell format
var cellFormats = new CellFormats() { Count = 2 };

var cellFormatDefault = new CellFormat { NumberFormatId = 0, FormatId = 0, FontId = 0, BorderId = 0, FillId = 0 };
cellFormats.Append(cellFormatDefault);

var cellFormatDate = new CellFormat { NumberFormatId = 22, FormatId = 0, FontId = 0, BorderId = 0, FillId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true) };
cellFormats.Append(cellFormatDate);

stylesheet.Append(cellFormats);

return stylesheet;

The resulting XML looks like:

<?xml version="1.0" encoding="utf-8"?>
<x:styleSheet xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <x:fonts count="1" x14ac:knownFonts="1" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">
        <x:font>
            <x:sz val="11" />
            <x:name val="Calibri" />
            <x:family val="2" />
            <x:scheme val="minor" />
        </x:font>
    </x:fonts>
    <x:fills count="1">
        <x:fill>
            <x:patternFill patternType="none" />
        </x:fill>
    </x:fills>
    <x:borders count="1">
        <x:border>
            <x:left />
            <x:right />
            <x:top />
            <x:bottom />
            <x:diagonal />
        </x:border>
    </x:borders>
    <x:cellXfs count="2">
        <x:xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" />
        <x:xf numFmtId="22" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1" />
    </x:cellXfs>
</x:styleSheet>