在OpenXML中应用数字格式 [英] Applying number formatting in OpenXML

问题描述

我正在尝试使用OpenXML从头开始创建Excel电子表格，并且一切正常(将实际值转储到实际单元格中)，但是现在我尝试将数字格式应用于列，并且正在运行陷入问题.我的styles.xml看起来像这样:

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>

但是Excel似乎不喜欢它，并在修复"文件后将其删除.我在这里想念什么?这些文档对确切地显得有些杂乱无章，这些都是使Excel保持快乐的必要条件.

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.

我手动分配了numFmtId，我认为这可能是一个适当的高数字.这是正确的方法吗?

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

此外，我知道formatCode是重复的，但是我认为Excel不会因此而被绊倒，如果有必要，我可以将其合并.

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.

我的列定义如下(在sheet.xml中):

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>

为了进行比较-这是Excel本身创建的有效styles.xml文件的摘录:

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>

推荐答案

回答我自己的问题，但我遇到了这篇文章:

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

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

建议最小的样式表需要的内容比numFmts和cellXfs多得多.因此，我修改了他们的代码，以生成一个最小的样式表，准备插入单元格格式和数字格式(我正在循环执行):

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.

我猜单元格格式必须除NumberFormatId外还具有FontId，FillId，BorderId和FormatId，为了具有这些ID，您需要为它们每个创建至少一个条目.尽管XML Schema将它们标记为可选".

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

这篇关于在OpenXML中应用数字格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！