3

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,

Community
  • 1
  • 1
David Gutierrez
  • 31
  • 1
  • 2
  • So I've figured out my issue by using the [OpenXML Productivity Tool](http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=5124). 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 Mar 29 '12 at 19:20

1 Answers1

1

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"/>
Manuel
  • 10,869
  • 14
  • 55
  • 86
  • Manuel, if you can confirm that you do not also need to define font, fill, border, and cell style sections for this to work I will accept your answer – David Gutierrez Mar 29 '12 at 19:21
  • Nope. I should have mentioned it. You have to provide the default style for fonts, fills, and borders. For some reason number format has a tacit default. – Manuel Mar 29 '12 at 19:33