Excel的最小样式表用日期打开XML? [英] Minimal style sheet for Excel Open XML with dates?

问题描述

我正在尝试使用Open XML SDK创建Excel文件，并使用所需的最小样式表来允许将单元格格式化为日期.以下是我尝试的样式表:

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>

即使没有从单元格引用此样式，Excel也会告诉我我的文件已损坏.如果删除cellXfs元素，该文件将打开正常.有人可以解释我还需要补充什么吗?创建样式表的C#代码如下.

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.

推荐答案

经过大量的反复试验，我发现样式表需要以下内容:

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

• 默认字体
• 默认填充
• 默认边框
• 默认单元格格式

任何一个遗漏都将导致Excel错误，除非所有这些遗漏.另外，我为日期添加了另一种单元格格式.

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.

我希望这对其他人有用.以下代码创建一个工作样式表，该工作表允许使用日期格式(数字格式ID为22):

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;

生成的XML看起来像:

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>

这篇关于Excel的最小样式表用日期打开XML?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！