使用OpenXML在Excel单元格中添加日期 [英] Adding a date in an Excel cell using OpenXML

问题描述

这就是我在做什么:

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;

，然后在我的代码中的某处

and then somewhere later in my code

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".枚举约束失败.

However, when I validate the generated excel file with Open XML SDK Tool, I get the following validation error: The attribute 't' has invalid value 'd'. The Enumeration constraint failed.

我在这里想念什么?谢谢您的提前帮助.

What am I missing here? Thank you for your help in advance.

PS:添加，这就是x:sheetData的样子.它给了我验证错误:

PS: Add, this is how the x:sheetData looks like. It gives me the validation error:

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

推荐答案

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

For broadest compatability use CellValues.Number as the cell data type.

根据文档，CellValues.Date适用于Excel 2010，因此您可能希望避免与Excel 2007(以及其他潜在应用程序)完全向后兼容.

According to the docs, CellValues.Date is for Excel 2010, so you may wish to avoid it for complete backwards compatability with Excel 2007 (and potentially other applications).

//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);

此更早的完整答案表明Excel 2010本身未使用可排序" CellValues.Date数据类型默认情况下.

This earlier more complete answer suggests that Excel 2010 doesn't use the 'sortable' CellValues.Date data type itself by default.

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

Presumably the reason for the CellValues.Date type is to overcome the limitations of the numeric date such as the earliest Excel numeric date being 01/01/1900.

digitalpreservation.gov 解释了日期单元格类型背后的一些历史意图 ，并且此页面说明 XLSX Transitional是主流现实应用程序使用的版本(于2014年测试).

digitalpreservation.gov explains some of the historical intention behind the date cell type, and this page explains that XLSX Transitional is the version used by mainstream real world applications (tested in 2014).

XLSX Strict具有日期单元格的值类型，使用完整"， ISO 8601中的扩展格式日历表示形式.出于以下原因: 向后兼容，不是ISO 8601日期的这种类型的使用 在XLSX Transitional中允许.

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

ISO标准化后期 OOXML的过程中，提出了采用ISO 8601格式的建议 用于电子表格中的日期和时间.

Late in the ISO standardization process for OOXML, a proposal was made to adopt the ISO 8601 format for dates and times in spreadsheets.

出席会议的专家 ISO 29500投票决议会议，对 OOXML格式的杰出建议主要是 XML和文本文档中，而不是电子表格中

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

以来 ISO 29500过渡版本的意图是 与现有的.xlsx文档语料库和 旨在处理它们的应用程序，对第4部分的修订 禁止在过渡版本中引入ISO 8601日期. 其次，ISO 8601是一种非常灵活的格式，可以在上下文中使用 旨在实现互操作性的目标需要具体说明 日期和时间应该使用特定的文本字符串模式.

Since the intent of the Transitional variant of ISO 29500 was to be compatible with the existing corpus of .xlsx documents and the applications designed to handle them, an amendment to Part 4 to disallow ISO 8601 dates in the Transitional variant was introduced. Secondly, ISO 8601 is a very flexible format, and any use in a context that aims at interoperability needs to be specific about which particular textual string patterns are expected for dates and times.

... 2014年11月的测试表明，Google表格和Libre Office 都以过渡版本创建了新文档

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

这篇关于使用OpenXML在Excel单元格中添加日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！