logo标签列表

一个Office Open XML单元格如何表示包含日期/时间值？

excelopenxmlopenxml-sdk
47

我正在使用Office Open XML SDK读取一个 .xlsx 文件，但在读取日期/时间值时感到困惑。我的其中一个电子表格具有以下标记（由Excel 2010生成）。

<x:row r="2" spans="1:22" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <x:c r="A2" t="s">
    <x:v>56</x:v>
  </x:c>
  <x:c r="B2" t="s">
    <x:v>64</x:v>
  </x:c>
  .
  .
  .
  <x:c r="J2" s="9">
    <x:v>17145</x:v>
  </x:c>

J2单元格中有一个日期序列值和一个样式属性s="9"。但是，Office Open XML规范说明9对应于后跟的超链接。这是来自 ECMA-376，第二版，第1部分 - 基础知识和标记语言参考.pdf 的第4999页的屏幕截图。

alt text

与规范一起提供的presetCellStyles.xml文件也将builtinId 9称为后跟的超链接。

<followedHyperlink builtinId="9">

规范中的所有样式都是视觉格式样式，而不是数字样式。数字样式在哪里定义，如何区分样式引用 s="9" 表示单元格格式（视觉）样式还是数字样式？

显然我正在错误的地方查找将单元格与其数字格式匹配的样式。在哪里可以找到这些信息呢？

7个回答
61
属性s引用了styles.xml中的一个样式xf条目。样式xf又引用了一个数字格式掩码。要识别包含日期的单元格，需要执行样式xf -> numberformat查找，然后确定该数字格式掩码是否为日期/时间数字格式掩码（而不是百分比或会计数字格式掩码等）。 styles.xml文件具有以下元素:
<xf numFmtId="14" ... applyNumberFormat="1" />
<xf numFmtId="1" ... applyNumberFormat="1" />

这些是xf条目，它们将给出一个numFmtId来引用数字格式掩码。

你应该在style.xml顶部附近的styleSheet元素中找到numFmts部分。

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

数字格式 id 可能在这里，也可能是内置格式之一。小于 164 的数字格式代码（numFmtId）是“内置”的。

我拥有的列表不完整：

0 = 'General';
1 = '0';
2 = '0.00';
3 = '#,##0';
4 = '#,##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# ??/??';

缺失的值主要与东亚变体格式有关。

3
内置数字格式的完整列表可以在Ecma Office Open XML文件格式标准文档的第4部分中找到（http://www.ecma-international.org/news/TC45_current_work/TC45_available_docs.htm），用于OpenXML的3.8.30和3.8.31节（第2127至2143页）。 - Mark Baker
2
再次感谢您。我在_ECMA-376，第二版，第1部分 - 基础知识和标记语言参考_的第18.8.30节第1964页中找到了列表。 - Samuel Neff
1
当前版本可从以下链接下载：http://www.ecma-international.org/publications/standards/Ecma-376.htm - Matt Connolly
仍然是第18.8.30节，第四版，2012年12月，第1767页。 - Ludovic Kuty
5
有关NumFmtId="14"的评论，它并不完全是“mm-dd-yy”格式：这会因用户在Windows级别上的区域和语言设置而在Windows Excel 2010软件中被解释为不同的日期格式。例如，在法语环境下它将是“短日期”格式即dd/mm/yyyy，而在英语环境下则为mm/dd/yyyy（通过从英语（美国）切换到法语（法国）等方式进行验证）。 - Sandra Rossi
显示剩余4条评论
7
我想分享我的解决方案，用于确定双精度值FromOADate是否是日期。原因是我的Excel文件中还有一个邮政编码。如果它是文本，则numberingFormat将为空。
或者，您可以使用numberingFormatId，并检查是否在Excel用于日期的Ids列表中。
在我的情况下，我已明确确定了客户端所有字段的格式。
    /// <summary>
    /// Creates the datatable and parses the file into a datatable
    /// </summary>
    /// <param name="fileName">the file upload's filename</param>
    private void ReadAsDataTable(string fileName)
    {
        try
        {
            DataTable dt = new DataTable();
            using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(string.Format("{0}/{1}", UploadPath, fileName), false))
            {
                WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
                IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
                string relationshipId = sheets.First().Id.Value;
                WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
                Worksheet workSheet = worksheetPart.Worksheet;
                SheetData sheetData = workSheet.GetFirstChild<SheetData>();
                IEnumerable<Row> rows = sheetData.Descendants<Row>();

                var cellFormats = workbookPart.WorkbookStylesPart.Stylesheet.CellFormats;
                var numberingFormats = workbookPart.WorkbookStylesPart.Stylesheet.NumberingFormats;

                // columns omitted for brevity

                // skip first row as this row is column header names
                foreach (Row row in rows.Skip(1))
                {
                    DataRow dataRow = dt.NewRow();

                    for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
                    {
                        bool isDate = false;
                        var styleIndex = (int)row.Descendants<Cell>().ElementAt(i).StyleIndex.Value;
                        var cellFormat = (CellFormat)cellFormats.ElementAt(styleIndex);

                        if (cellFormat.NumberFormatId != null)
                        {
                            var numberFormatId = cellFormat.NumberFormatId.Value;
                            var numberingFormat = numberingFormats.Cast<NumberingFormat>()
                                .SingleOrDefault(f => f.NumberFormatId.Value == numberFormatId);

                            // Here's yer string! Example: $#,##0.00_);[Red]($#,##0.00)
                            if (numberingFormat != null && numberingFormat.FormatCode.Value.Contains("mm/dd/yy"))
                            {
                                string formatString = numberingFormat.FormatCode.Value;
                                isDate = true;
                            }
                        }

                        // replace '-' with empty string
                        string value = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i), isDate);
                        dataRow[i] = value.Equals("-") ? string.Empty : value;
                    }

                    dt.Rows.Add(dataRow);
                }
            }

            this.InsertMembers(dt);
            dt.Clear();
        }
        catch (Exception ex)
        {
            LogHelper.Error(typeof(MemberUploadApiController), ex.Message, ex);
        }
    }

    /// <summary>
    /// Reads the cell's value
    /// </summary>
    /// <param name="document">current document</param>
    /// <param name="cell">the cell to read</param>
    /// <returns>cell's value</returns>
    private string GetCellValue(SpreadsheetDocument document, Cell cell, bool isDate)
    {
        string value = string.Empty;

        try
        {
            SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
            value = cell.CellValue.InnerXml;

            if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
            {
                return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
            }
            else
            {
                // check if this is a date or zip.
                // integers will be passed into this else statement as well. 
                if (isDate)
                {
                    value = DateTime.FromOADate(double.Parse(value)).ToString();
                }

                return value;
            }
        }
        catch (Exception ex)
        {
            LogHelper.Error(typeof(MemberUploadApiController), ex.Message, ex);
        }

        return value;
    }
我得到了NumberFormatId = 14，但是在列表中没有任何项的NumberingFormat.Id == 14。 - Philipp Munin
7
选定的答案很准确，但请注意，Excel定义的一些数字格式（numFmt）代码与OpenXML规范不同。根据《Open XML SDK 2.5生产力工具》文档（NumberingFormat类的“Implementer Notes”选卡），标准定义了内置格式ID 14："mm-dd-yy"、22："m/d/yy h:mm"、37："#,##0 ;(#,##0)"、38："#,##0 ;[Red]"、39："#,##0.00;(#,##0.00)"、40："#,##0.00;[Red]"、47："mmss.0"、KOR fmt 55："yyyy-mm-dd"。 Excel定义了内置格式ID
14："m/d/yyyy"
22："m/d/yyyy h:mm"
37："#,##0_);(#,##0)"
38："#,##0_);[Red]"
39："#,##0.00_);(#,##0.00)"
40："#,##0.00_);[Red]"
47："mm:ss.0"
55："yyyy/mm/dd" 大多数都是微小变化，但#14非常重要。我浪费了几个小时排除问题：为什么单位数月份和日期没有前导零（例如01/05/14 vs. 1/5/14）。
1
在styles.xml中查看是否有numFmt节点。我认为它将包含一个numFmtId为“9”，这将与使用的日期格式相关联。
我不知道ECMA中的位置，但如果您搜索numFmt，您可能会找到它。
s="9" 指的是 xfId，而不是 numFmtId。 - Mark Baker
0
如果有人遇到了困难，以下是我的解决方法：
1）创建一个新的Excel文件并在单元格A1中输入日期时间字符串
2）更改单元格的格式，然后保存文件。
3）运行以下Powershell脚本从.xlxs中提取样式表。
[Reflection.Assembly]::LoadWithPartialName("DocumentFormat.OpenXml")

$xlsx = (ls C:\PATH\TO\FILE.xlsx).FullName
$package = [DocumentFormat.OpenXml.Packaging.SpreadsheetDocument]::Open($xlsx, $true)

[xml]$style = $package.WorkbookPart.WorkbookStylesPart.Stylesheet.OuterXml
Out-File -InputObject $style.OuterXml -FilePath "style.xml"

style.xml现在包含了您可以注入到DocumentFormat.OpenXml.Spreadsheet.Stylesheet(string outerXml)中的信息，从而实现：

4）使用提取的文件构建Excel对象模型。

var style = File.ReadAllText(@"c:\PATH\TO\EXTRACTED\Style.xml");
var stylesheetPart = WorkbookPart_REFERENCE.AddNewPart<WorkbookStylesPart>();
stylesheetPart.Stylesheet = new Stylesheet(style);
stylesheetPart.Stylesheet.Save();
0

@RobScott 参考你的代码片段，我发现在特定单元格的样式索引中始终为 null。

              var styleIndex = (int)row.Descendants<Cell>().ElementAt(i).StyleIndex.Value;

我的需求是读取下面提到的Excel，并将行和列数据转换为JSON格式。

Excel参考

库存发票号 库存发票日期 名称 描述
DC3320012989 2021年01月23日 00:00:00:00 物品1 描述
DC3320012989 2021年01月24日 00:00:00:00 物品2 描述
DC3320012989 2021年01月25日 00:00:00:00 物品3 描述
0

我曾经不清楚如何可靠地确定单元格是否具有日期/时间值。在花费了一些时间进行实验后，我想出了代码(请参见帖子), 它将查找内置和自定义的日期/时间格式。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接