从 OpenXml Excel 文件中读取日期

Question

我正在尝试使用 SharpZipLib 从 .xlsx 文件中读取数据以将其解压（在内存中）并读取内部 xml 文件。一切都很好，但要识别日期 - 它们以 julean 格式存储，我需要以某种方式识别数字是日期还是只是数字。在另一个主题中（不幸的是它死了，我需要快速回答）我从马克·贝克那里了解了一些事情，但这仍然不够......

“Excel 将日期存储为浮点值...整数部分是自 1900 年 1 月 1 日（或 1904 年 1 月 1 日，具体取决于所使用的日历）以来的天数，小数部分是比例一天的时间（即时间部分）...由于 1900 年被认为是闰年，这一事实变得更加尴尬。

唯一区分数据和数字的是数字格式掩码。如果您可以读取格式掩码，则可以使用它将该值识别为日期而不是数字...然后根据基准日期计算日期值/格式。”

“但是日期属性“s”的值不是始终为“1”吗？我知道它定义了样式，但也许吧？;)”

s 属性引用 styles.xml 中的样式 xf 条目，并且它并不总是日期的条目 1...这完全取决于工作簿中使用了多少种不同的样式。样式 xf 又引用数字格式掩码。要识别包含日期的单元格，您需要执行样式 xf ->数字格式查找，然后确定该数字格式掩码是否是日期/时间数字格式掩码（而不是百分比或会计数字格式掩码）

“还有一个问题 - 我现在正在查看 style.xml 的内容，在该部分中我看到如下元素：“”等，但没有 部分...是否有任何“标准”格式？或者我只是错过了什么？”

有人可以帮我吗 ？ ？提前致谢。

Answer 1

您应该在 style.xml 顶部附近找到 numFmts 部分，作为 styleSheet 元素的一部分

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?> 
    <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
        <numFmts count="3">
            <numFmt numFmtId="164" formatCode="[$-414]mmmm\ yyyy;@" /> 
            <numFmt numFmtId="165" formatCode="0.000" /> 
            <numFmt numFmtId="166" formatCode="#,##0.000" /> 
        </numFmts>

EDIT

我一直在仔细检查我的 xlsx 阅读器代码（自从我深入研究以来已经很长一段时间了）图书馆的那一部分）；并且有内置格式。小于 164 的数字格式代码 (numFmtId) 是“内置”的。

我的清单不完整：

0 = 'General';
1 = '0';
2 = '0.00';
3 = '#,##0';
4 = '#,##0.00';
5 = '$#,##0;\-$#,##0';
6 = '$#,##0;[Red]\-$#,##0';
7 = '$#,##0.00;\-$#,##0.00';
8 = '$#,##0.00;[Red]\-$#,##0.00';
9 = '0%';
10 = '0.00%';
11 = '0.00E+00';
12 = '# ?/?';
13 = '# ??/??';
14 = 'mm-dd-yy';
15 = 'd-mmm-yy';
16 = 'd-mmm';
17 = 'mmm-yy';
18 = 'h:mm AM/PM';
19 = 'h:mm:ss AM/PM';
20 = 'h:mm';
21 = 'h:mm:ss';
22 = 'm/d/yy h:mm';

37 = '#,##0 ;(#,##0)';
38 = '#,##0 ;[Red](#,##0)';
39 = '#,##0.00;(#,##0.00)';
40 = '#,##0.00;[Red](#,##0.00)';

44 = '_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)';
45 = 'mm:ss';
46 = '[h]:mm:ss';
47 = 'mmss.0';
48 = '##0.0E+0';
49 = '@';

27 = '[$-404]e/m/d';
30 = 'm/d/yy';
36 = '[$-404]e/m/d';
50 = '[$-404]e/m/d';
57 = '[$-404]e/m/d';

59 = 't0';
60 = 't0.00';
61 = 't#,##0';
62 = 't#,##0.00';
67 = 't0%';
68 = 't0.00%';
69 = 't# ?/?';
70 = 't# ??/??';

Answer 2

单元格可能有样式。这些是在 styleSheet 中索引 cellXfs 的 uint。每个 cellXfs 项都包含一组属性。最重要的是 NumberFormatID。如果其值在 14-22 范围内，则它是“标准”日期。如果它落在 165 - 180 范围内，则它是“格式化”日期，并且将具有相应的 NumberingFormat 属性。

标准日期

[x:cr="A2" s="2"][x:v]38046[/x:v][/x:c]

[x:xf numFmtId="14" fontId="0" fillId= "0" borderId="0" xfId="0" applyNumberFormat="1" /] （序数位置 2）

格式化日期

[x:cr="A4" s="4"][x:v]38048[/x :v][/x:c]

[x:xf numFmtId="166" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1" /](序号位置 4)

[x:numFmt numFmtId="166" formatCode="m/d;@" /]

此代码提取与这些日期格式相对应的样式 ID 列表。

  private void GetDateStyles()
  {
     //
     // The only way to tell dates from numbers is by looking at the style index. 
     // This indexes cellXfs, which contains NumberFormatIds, which index NumberingFormats.
     // This method creates a list of the style indexes that pertain to dates.
     WorkbookStylesPart workbookStylesPart = (WorkbookStylesPart) UriPartDictionary["/xl/styles.xml"];
     Stylesheet styleSheet = workbookStylesPart.Stylesheet;
     CellFormats  cellFormats = styleSheet.CellFormats;

     int i = 0;
     foreach (CellFormat cellFormat in cellFormats)
     {
        uint numberFormatId = cellFormat.NumberFormatId;
        if ((numberFormatId >= 14 && numberFormatId <= 22) 
        || (numberFormatId >= 165u && numberFormatId <= 180u))
        {
           _DateStyles.Add(i.ToString());
        }
        i++;
     }

Answer 3

我建议 numFmtId="14" 应被视为“Windows 短日期格式”，因为在澳大利亚，此格式将日期显示为“dd/mm/yy”，而不是“mm/dd/yy”。

Answer 4

有两种方法可以获取单元格的日期格式。

首先获取“s”或 StyleIndex。请注意下面的数字原始格式的日期 (40667)：

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

cells 节点中的“s”属性指向从 0 开始的 styles.xml 节点的从零开始的数组。这是定位日期格式（如果有）的关键，映射到原始数字日期数据。您会看到 s=1，它指向 Excel 工作簿的以下单元格格式 styles.xml 部分中的第二个 xf 节点：

   <cellXfs count="2">
     <xf numFmtId="0" ... />
     <xf numFmtId="14" ... />
   </cellXfs>

在第二个节点中，您会看到 numFmtId="14" 值。这就是 numberFormatID。它告诉您，这是确定日期数字应以什么形式显示所需的 id。但是该数字指向日期格式的两个可能位置。如果其数字在 14-22 范围内，则它是日期的内置样式。如果超出该范围，则（可能）是 Excel 文件所有者添加的自定义日期格式。在检查这两个地方之前你不会知道。

在第一种情况下，如果其值是 14-22，则需要将其映射到每个 Excel 文件具有的预构建日期格式之一（mm-dd-yy 等）。您可以在 OpenXML SDK 中找到该表。以下是 numFmtId 映射到内置日期格式的示例...

14  mm-dd-yy
15  d-mmm-yy
16  d-mmm
17  mmm-yy
18  h:mm AM/PM

此时您知道它是一个日期以及它以什么格式呈现。如果它不是这些值之一，则它可能是自定义的数字。现在，您必须再次搜索 styles.xml 文件以查找具有匹配 numFmtId 值的样式节点。这些节点将包含自定义日期格式，如下所示：

    <numFmts count="2">
        <numFmt numFmtId="164" formatCode="mm/yyyy;@" /> 
        <numFmt numFmtId="165" formatCode="0.000" /> 
        <numFmt numFmtId="166" formatCode="#,##0.000" /> 
    </numFmts>

请注意，如果您的 numFmtId 为 164，则您会找到其自定义日期格式。因此，要捕获所有这些疯狂的日期格式（自定义和内置），最好的选择是维护一系列可接受的“格式”作为字符串，找到您的 formatCode，然后查看它是否与代码中可接受的格式之一匹配。

祝你好运！