Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Applying number formatting in OpenXML

Tags:

excel

openxml

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>
like image 908
Matt Burland Avatar asked May 06 '14 18:05

Matt Burland


People also ask

How do I make text bold in OpenXml?

Bold fbld = new Bold();

How do I add DocumentFormat OpenXml reference?

Go to your Solution Explorer > right click on references and then click Manage NuGet Packages . Then search in tab "Online" for DocumentFormat. OpenXml and install it. After you can use DocumentFormat.

What is DocumentFormat OpenXml?

The Open XML SDK provides tools for working with Office Word, Excel, and PowerPoint documents. It supports scenarios such as: - High-performance generation of word-processing documents, spreadsheets, and presentations. - Populating content in Word files from an XML data source.

Where is DocumentFormat OpenXml DLL?

0 on c:\program files\open xml sdk\v2\lib\DocumentFormat. OpenXml. dll.


1 Answers

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".

like image 110
Matt Burland Avatar answered Sep 28 '22 07:09

Matt Burland