Having trouble applying a number format with OpenXML in C#

Question

I am trying to get a column in an excel document I am generating, using OpenXML, to be formatted in a friendly manner for 12 digit UPCs.

In order to accomplish this I am using the following code (based upon this question):

var sp = workbookpart.AddNewPart<WorkbookStylesPart>();
sp.Stylesheet = new Stylesheet {NumberingFormats = new NumberingFormats(), CellFormats = new CellFormats()};

var upcFormatting = new NumberingFormat {NumberFormatId = 164, FormatCode = "000000000000"};

var upcCellFormat = new CellFormat
                                {
                                        NumberFormatId = upcFormatting.NumberFormatId,
                                        FontId = 0U,
                                        FillId = 0U,
                                        BorderId = 0U,
                                        FormatId = 0U,
                                        ApplyNumberFormat = BooleanValue.FromBoolean(true)
                                 };
sp.Stylesheet.NumberingFormats.AppendChild(upcFormatting);
sp.Stylesheet.CellFormats.AppendChild(upcCellFormat);

sp.Stylesheet.NumberingFormats.Count++;
sp.Stylesheet.CellFormats.Count++;

var styleIndex = sp.Stylesheet.CellFormats.Count;

workbookpart.Workbook.Save();

The above code unfortunately generates a style sheet which excel deems as corrupt, the sheet looks like so:

<?xml version="1.0" encoding="UTF-8"?>
<x:styleSheet xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
   <x:numFmts count="1">
     <x:numFmt formatCode="000000000000" numFmtId="164"/>
   </x:numFmts>
   <x:cellXfs count="1">
   <x:xf numFmtId="164" applyNumberFormat="1" xfId="0" borderId="0" fillId="0" fontId="0"/> 
   </x:cellXfs>
</x:styleSheet>

If anyone could provide insight as to how I can get a valid style sheet generated with a numbering format that allows for 12 digits to be displayed only using OpenXML (can't use any frameworks built around it, such as ClosedXML) I would greatly appreciate it.

Thanks,

So I've figured out my issue by using the OpenXML Productivity Tool. After using it, I discovered that it is necessary to also add Font, Border, Fill, and Cell Style sections as well. By creating a blank excel with the formats I want, I was able to use the tool to see the code needed. --Note: I figured this out before seeing Manuel's answer, so I'm not sure if performing his suggestion alone is enough but in any case using the Productivity Tool should help anyone else who runs into issues similar to mine. — David Gutierrez

Manuel Manuel · Accepted Answer · 2012-03-29T19:15:06

You're missing the default style in your stylesheet. Add the following line (must be first) and change the cellXfs count to 2. Don't forget to update your sheet to use style "2" instead of "1".

<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>

Having trouble applying a number format with OpenXML in C#

1 Answers