c# - OpenXML - 将日期写入 Excel 电子表格会导致内容不可读

Question

我正在使用以下代码将 DateTime 添加到我的电子表格中的列:

var dt = DateTime.Now;
r.AppendChild<Cell>(new Cell()
    { 
        CellValue = new CellValue(dt.ToOADate().ToString()),
        DataType = new EnumValue<CellValues>(CellValues.Date), 
        StyleIndex = 1,
        CellReference = header[6] + index
    });

当我尝试在 Excel 2010 中打开文件时，出现错误

Excel found unreadable content in file.xlsx

如果我注释掉该行，一切都很好。

我已经提到了 similar questions在 StackOverflow 上，但他们基本上和我有相同的代码。

Answer 1

像往常一样迟到了，但我必须发布一个答案，因为之前的所有答案都是完全错误的，除了 Oleh 被否决的答案很遗憾是不完整的。

由于问题与 Excel 有关，最简单的方法是使用所需的数据和样式创建一个 Excel 电子表格，然后将其作为部分打开并查看原始 XML。

将日期 01/01/2015 添加到单元格 A1 中会产生以下结果:

<row r="1">
  <c r="A1" s="0">
    <v>42005</v>
  </c>
</row>

请注意，type 属性不在那里。但是， 有一个引用以下样式的样式属性:

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

这是您必须添加的最基本的样式。

所以生成上面的代码:

1. 您需要创建一个样式如下:

var CellFormats = new CellFormats();
CellFormats.Append(new CellFormat()
{
    BorderId = 0,
    FillId = 0,
    FontId = 0,
    NumberFormatId = 14,
    FormatId = 0,
    ApplyNumberFormat = true
});
CellFormats.Count = (uint)CellFormats.ChildElements.Count;
var StyleSheet = new Stylesheet();
StyleSheet.Append(CellFormats);

NumberFormatId = 14 指的是内置格式mm-dd-yy，这里是list of some other formats .

不幸的是，添加只是 上面的样式似乎还不够，如果你这样做实际上会导致 Excel 崩溃。请注意，BorderId、FillId、FontId 需要对应样式表中的项目，这意味着您需要提供它们。完整代码 list 中的 GetStyleSheet() 方法提供了 Excel 正常工作所需的最小默认样式表。

1. 并添加一个单元格如下:

SheetData.AppendChild(new Row(
    new Cell() 
    { 
        // CellValue is set to OADate because that's what Excel expects.
        CellValue = new CellValue(date.ToOADate().ToString(CultureInfo.InvariantCulture)), 
        // Style index set to style (0 based).
        StyleIndex = 0
    }));

注意:Office 2010 和 2013 可以以不同方式处理日期，但默认情况下它们似乎没有。

它们支持 ISO 8601 格式的日期，即 yyyy-MM-ddTHH:mm:ss 碰巧这也是可排序的标准格式(“s”)，因此您可以:

SheetData.AppendChild(new Row(
    new Cell() 
    { 
        CellValue = new CellValue(date.ToString("s")), 
        // This time we do add the DataType attribute but ONLY for Office 2010+.
        DataType = CellValues.Date
        StyleIndex = 1
    }));

结果:

<row>
  <c s="0" t="d">
    <v>2015-08-05T11:13:57</v>
  </c>
</row>

完整代码 list

下面是添加具有日期格式的单元格所需的最少代码示例。

private static void TestExcel()
{
    using (var Spreadsheet = SpreadsheetDocument.Create("C:\\Example.xlsx", SpreadsheetDocumentType.Workbook))
    {
        // Create workbook.
        var WorkbookPart = Spreadsheet.AddWorkbookPart();
        var Workbook = WorkbookPart.Workbook = new Workbook();

        // Add Stylesheet.
        var WorkbookStylesPart = WorkbookPart.AddNewPart<WorkbookStylesPart>();
        WorkbookStylesPart.Stylesheet = GetStylesheet();
        WorkbookStylesPart.Stylesheet.Save();

        // Create worksheet.
        var WorksheetPart = Spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();
        var Worksheet = WorksheetPart.Worksheet = new Worksheet();

        // Add data to worksheet.
        var SheetData = Worksheet.AppendChild(new SheetData());
        SheetData.AppendChild(new Row(
            new Cell() { CellValue = new CellValue(DateTime.Today.ToOADate().ToString(CultureInfo.InvariantCulture)), StyleIndex = 1 },
            // Only works for Office 2010+.
            new Cell() { CellValue = new CellValue(DateTime.Today.ToString("s")), DataType = CellValues.Date, StyleIndex = 1 }));

        // Link worksheet to workbook.
        var Sheets = Workbook.AppendChild(new Sheets());
        Sheets.AppendChild(new Sheet()
        {
            Id = WorkbookPart.GetIdOfPart(WorksheetPart),
            SheetId = (uint)(Sheets.Count() + 1),
            Name = "Example"
        });

        Workbook.Save();
    }
}

private static Stylesheet GetStylesheet()
{
    var StyleSheet = new Stylesheet();

     // Create "fonts" node.
    var Fonts = new Fonts();
    Fonts.Append(new Font()
    {
        FontName = new FontName() { Val = "Calibri" },
        FontSize = new FontSize() { Val = 11 },
        FontFamilyNumbering = new FontFamilyNumbering() { Val = 2 },
    });

    Fonts.Count = (uint)Fonts.ChildElements.Count;

    // Create "fills" node.
    var 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;

    // Create "borders" node.
    var Borders = new Borders();
    Borders.Append(new Border()
    {
        LeftBorder = new LeftBorder(),
        RightBorder = new RightBorder(),
        TopBorder = new TopBorder(),
        BottomBorder = new BottomBorder(),
        DiagonalBorder = new DiagonalBorder()
    });

    Borders.Count = (uint)Borders.ChildElements.Count;

    // Create "cellStyleXfs" node.
    var CellStyleFormats = new CellStyleFormats();
    CellStyleFormats.Append(new CellFormat()
    {
        NumberFormatId = 0,
        FontId = 0,
        FillId = 0,
        BorderId = 0
    });

    CellStyleFormats.Count = (uint)CellStyleFormats.ChildElements.Count;

    // Create "cellXfs" node.
    var CellFormats = new CellFormats();

    // A default style that works for everything but DateTime
    CellFormats.Append(new CellFormat()
    {
        BorderId = 0,
        FillId = 0,
        FontId = 0,
        NumberFormatId = 0,
        FormatId = 0,
        ApplyNumberFormat = true
    });

   // A style that works for DateTime (just the date)
   CellFormats.Append(new CellFormat()
    {
        BorderId = 0,
        FillId = 0,
        FontId = 0,
        NumberFormatId = 14, // or 22 to include the time
        FormatId = 0,
        ApplyNumberFormat = true
    });

    CellFormats.Count = (uint)CellFormats.ChildElements.Count;

    // Create "cellStyles" node.
    var CellStyles = new CellStyles();
    CellStyles.Append(new CellStyle()
    {
        Name = "Normal",
        FormatId = 0,
        BuiltinId = 0
    });
    CellStyles.Count = (uint)CellStyles.ChildElements.Count;

    // Append all nodes in order.
    StyleSheet.Append(Fonts);
    StyleSheet.Append(Fills);
    StyleSheet.Append(Borders);
    StyleSheet.Append(CellStyleFormats);
    StyleSheet.Append(CellFormats);
    StyleSheet.Append(CellStyles);

    return StyleSheet;
}