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 在 file.xlsx 中发现不可读的内容

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

我在 StackOverflow 上提到了类似问题 ，但他们的代码基本上和我一样。

Answer 1

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

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

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

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

请注意，类型属性不存在。但是，有一个样式属性引用以下样式：

<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，这里是 < href="https://stackoverflow.com/questions/4730152/what-indicates-an-office-open-xml-cell-contains-a-date-time-value#4730266">其他一些列表格式。

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

2. 并添加一个单元格，如下所示：

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>

完成代码列表

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

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;
}

Answer 2

尝试指示它是 CellValues.String 类型，而不是 CellValues.Date 类型。

使用

DataType = new EnumValue<CellValues>(CellValues.String)   // good

而不是

DataType = new EnumValue<CellValues>(CellValues.Date)     // bad

Now，将其添加为 date 是有意义的，无需 ToString() 转换，并使用 CellValues.Date DataType -- 但是 CellValue() 仅采用字符串 作为参数。

[为什么，OpenXmlSDK，为什么？？？你是一个包装纸。把东西包裹得很好。让它们隐形，让我的生活更轻松。 :::sigh:::]

此外，如果目标单元格期望格式化日期，我们应该指示它是一个日期。

但我发现，虽然 CellValues.String 和 CellValues.Date 都按预期格式化（相同），但只有 CellValues.Date 抛出加载时“不可读的内容”。

我对 dt.ToOADate().ToString(new CultureInfo("en-US")); 方法的任何变化都完全没有运气——我最终得到了一个五位数字在电子表格中显示为五位数字，而它应该是格式化日期。

添加字符串值但使用 CellValues.Number 数据类型时，我收到了相同的错误消息。

Answer 3

尝试使用 dt.ToOADate().ToString().Replace (",", ".") 而不是 dt.ToOADate().ToString()

对于某些工作代码示例请参见http://www.codeproject.com/KB/office/ExcelOpenXMLSDK.aspx

编辑：

请将您的代码更改为：

dt.ToOADate().ToString(new CultureInfo("en-US"));

Answer 4

例如，您可以创建自己的带有日期列的 Excel 文件。然后，如果您使用 Open XML SDK 中的生产力工具打开它，您会发现没有为具有日期值的单元格指定 DataType。这意味着您在创建日期单元格时应省略DataType。在这种情况下，还需要将 dt.ToOADate().ToString() 作为单元格值传递。

Answer 5

以下代码可用于在电子表格中设置日期时间值：

Cell cell = GetRequiredCell(); // It returns the required Cell

DateTime dtValue = new DateTime(2012, 12, 8);

string strValue = dtValue.ToOADate().ToString().Replace(",", ".");
// decimal separator change it to "."

cell.DataType = new EnumValue<CellValues>(CellValues.Number);
cell.CellValue = new CellValue(strValue);
cell.StyleIndex = 1; 

Answer 6

private Cell CreateCellWithValue(DateTime columnValue, uint? styleIndex, string cellReference)
{
    Cell c = new Cell();
    c.DataType = CellValues.Number;
    c.CellValue = new CellValue(columnValue.ToOADate().ToString(new CultureInfo("en-US")));
    c.CellReference = cellReference;
    c.StyleIndex = styleIndex;

    return c;
}

Answer 7

以下内容对我们有用：

c.CellValue = new CellValue(datetimeValue).ToOADate().ToString());
c.DataType = CellValues.Number;
c.StyleIndex = StyleDate;

将 DataType 设置为 CellValues.Number，然后确保使用 CellFormats 中的适当样式索引格式化单元格。在我们的例子中，我们在工作表中构建一个样式表，StyleDate 是样式表中 CellFormats 的索引。