c# - 使用 OpenXML 在 Excel 单元格中添加日期

Question

这就是我正在做的:

CellFormat cellFormat = 
                new CellFormat() 
                { NumberFormatId = (UInt32Value)14U, 
                    FontId = (UInt32Value)0U, 
                    FillId = (UInt32Value)0U, 
                    BorderId = (UInt32Value)0U, 
                    FormatId = (UInt32Value)0U, 
                    ApplyNumberFormat = true };

sd.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.AppendChild<CellFormat>(cellFormat);

_dateStyleIndex = sd.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.Count() - 1;

然后在我的代码后面的某个地方

else if (type == DataTypes.DateTime)
{                
    DateTime dateTime = DateTime.Parse(text);
    double oaValue = dateTime.ToOADate();
    cell.CellValue = new CellValue(oaValue.ToString(CultureInfo.InvariantCulture));
    cell.DataType = new EnumValue<CellValues>(CellValues.Date);
    cell.StyleIndex = Convert.ToUInt32(_dateStyleIndex);               
}

但是，当我使用 Open XML SDK Tool 验证生成的 excel 文件时，出现以下验证错误:属性“t”具有无效值“d”。枚举约束失败。

我在这里错过了什么？提前感谢您的帮助。

PS:补充一下，x:sheetData 是这样的。它给了我验证错误:

<x:sheetData xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <x:row r="2">
    <x:c r="B2" t="s">
      <x:v>0</x:v>
    </x:c>
    <x:c r="C2" t="s">
      <x:v>1</x:v>
    </x:c>
    <x:c r="D2" t="s">
      <x:v>2</x:v>
    </x:c>
  </x:row>
  <x:row r="3">
    <x:c r="B3" t="s">
      <x:v>3</x:v>
    </x:c>
    <x:c r="C3" t="s">
      <x:v>6</x:v>
    </x:c>
    <x:c r="D3" s="1" t="d">
      <x:v>42634.906087963</x:v>
    </x:c>
  </x:row>
  <x:row r="4">
    <x:c r="B4" t="s">
      <x:v>4</x:v>
    </x:c>
    <x:c r="C4" t="s">
      <x:v>7</x:v>
    </x:c>
    <x:c r="D4" s="1" t="d">
      <x:v>42634.9062037037</x:v>
    </x:c>
  </x:row>
  <x:row r="5">
    <x:c r="B5" t="s">
      <x:v>5</x:v>
    </x:c>
    <x:c r="C5" t="s">
      <x:v>8</x:v>
    </x:c>
    <x:c r="D5" s="1" t="d">
      <x:v>42634.9062847222</x:v>
    </x:c>
  </x:row>
</x:sheetData>

Answer 1

为了获得最广泛的兼容性，请使用 CellValues.Number 作为单元格数据类型。

根据docs , CellValues.Date 适用于 Excel 2010，因此为了与 Excel 2007(以及可能的其他应用程序)完全向后兼容，您可能希望避免使用它。

//broadly supported - earliest Excel numeric date 01/01/1900
DateTime dateTime = DateTime.Parse(text);
double oaValue = dateTime.ToOADate();
cell.CellValue = new CellValue(oaValue.ToString(CultureInfo.InvariantCulture));
cell.DataType = new EnumValue<CellValues>(CellValues.Number);
cell.StyleIndex = Convert.ToUInt32(_numericDateCellFormatIndex); 


//supported in excel 2010 - not XLSX Transitional compliant 
DateTime dateTime = DateTime.Parse(text);
cell.CellValue = new CellValue(dateTime.ToString("s"));
cell.DataType = new EnumValue<CellValues>(CellValues.Date);
cell.StyleIndex = Convert.ToUInt32(_sortableDateCellFormatIndex);

这earlier more complete answer建议 Excel 2010 默认不使用“可排序”CellValues.Date 数据类型本身。

据推测，CellValues.Date 类型的原因是为了克服数字日期的限制，例如最早的 Excel 数字日期是 01/01/1900。

digitalpreservation.gov explains some of the historical intention behind the date cell type , 此页面解释了 XLSX Transitional is the version used by mainstream real world applications (2014 年测试)。

XLSX Strict has a value type for cells of date, using the Complete,Extended Format Calendar representations in ISO 8601. For reasons ofbackwards compatibility, this typed use of ISO 8601 dates is notpermitted in XLSX Transitional.

Late in the ISO standardizationprocess for OOXML, a proposal was made to adopt the ISO 8601 formatfor dates and times in spreadsheets.

The experts present at theISO 29500 Ballot Resolution Meeting where votes were held on theoutstanding proposals for the OOXML format were primarily experts inXML and in textual documents rather than with spreadsheets

Sincethe intent of the Transitional variant of ISO 29500 was to becompatible with the existing corpus of .xlsx documents and theapplications designed to handle them, an amendment to Part 4 todisallow ISO 8601 dates in the Transitional variant was introduced.Secondly, ISO 8601 is a very flexible format, and any use in a contextthat aims at interoperability needs to be specific about whichparticular textual string patterns are expected for dates and times.

... Tests in November 2014 indicated that Google Sheets and Libre Officeboth created new documents in the Transitional variant