C# Open XML 2.0 数字格式编号范围

Question

C# Open XML 2.0 数字格式编号范围

22

使用C#处理大型Excel文件的Open XML 2.0，我遇到了一个问题。我要解析的单元格没有 DataType，然后检查 NumberFormatId 来确定它是小数、数字还是日期。我正在寻找数字/小数与日期的确切 NumberFormatId 范围。它们似乎随处可见，一些数字/小数具有 189、212、214、305 的格式，而日期具有 185、194、278 等值。是否有人知道规范是否定义了这些范围？

编辑-更多信息

以下是来自xl文件夹内style.xml文件中194号数字格式的示例。

Excel表格来自世界上不同的地区，因此我认为数字格式不同，但它们是否重叠？在不同的文化设置中，numFmtId 194 是否会变成除了日期之外的其他东西？

以下是我如何将类似于“40574”这样的 c.CellValues 转换为日期，但问题是如何知道“40574”是一个日期而不是一个数字？

 DateTime.FromOADate(Convert.ToDouble(c.CellValue.Text));

目前我是通过检查是否存在 DataType，然后检查 CellFormat 来完成这个操作，但是当某些 NumberFormatId 不在我的检查范围内时就会出现问题。

    private Object FormatCellValue(Cell c, SharedStringTable ssTable, CellFormats cellFormats)
            {
                if (c.CellValue != null)
                {
                    // If there is no data type, this must be a string that has been formatted as a number
                    if (c.DataType == null)
                    {
                        CellFormat cf;
                        if (c.StyleIndex == null)
                        {
                            cf = cellFormats.Descendants<CellFormat>().ElementAt<CellFormat>(0);
                        }
                        else
                        {
                            cf = cellFormats.Descendants<CellFormat>().ElementAt<CellFormat>(Convert.ToInt32(c.StyleIndex.Value));
                        }


                        if ((cf.NumberFormatId >= 14 && cf.NumberFormatId <= 22) ||
                            (cf.NumberFormatId >= 165 && cf.NumberFormatId <= 180) || 
                                cf.NumberFormatId == 278 || cf.NumberFormatId == 185 || cf.NumberFormatId == 196 || 
                                cf.NumberFormatId == 217 || cf.NumberFormatId == 326) // Dates
                        {

                            try
                            {

                                DateTime dt;
                                dt = DateTime.FromOADate(Convert.ToDouble(c.CellValue.Text));

...CODE CONTINUES

编辑

在我更新的帖子中，我忘记发布我在style.xml文件中找到的值：

<numFmt numFmtId="323" formatCode="mmm/yy;@"/>

所以我的问题是如何获取formatCode并解析它以确定它是否为日期？以下是数字格式化323的即时调试窗口输出：

{DocumentFormat.OpenXml.Spreadsheet.CellFormat}
    base {DocumentFormat.OpenXml.OpenXmlCompositeElement}: {DocumentFormat.OpenXml.Spreadsheet.CellFormat}
    Alignment: {DocumentFormat.OpenXml.Spreadsheet.Alignment}
    ApplyAlignment: "1"
    ApplyBorder: "1"
    ApplyFill: "1"
    ApplyFont: "1"
    ApplyNumberFormat: "1"
    ApplyProtection: "1"
    BorderId: "64"
    ExtensionList: null
    FillId: "0"
    FontId: "83"
    FormatId: "37992"
    LocalName: "xf"
    NumberFormatId: "323"
    PivotButton: null
    Protection: {DocumentFormat.OpenXml.Spreadsheet.Protection}
    QuotePrefix: "1"

- maguy

1

请查看以下包含整数值和相应格式的表格页面：http://closedxml.codeplex.com/wikipage?title=NumberFormatId%20Lookup%20Table 或 http://lateral8.com/articles/2010/6/11/openxml-sdk-20-formatting-excel-values.aspx。 - Lukasz M

你需要更多关于此问题的帮助吗？如果是的话，我可以将其发布为解决方案：）。如果您需要更多信息或正在寻找其他内容，请告诉我，我会尽力帮助。 - Lukasz M

我已经添加了更多信息，将之前提到的内容整合成了一个回答。我已经描述了如何检查xlsx文件中定义的格式。如果这个回答解决了你的问题，请告诉我。 - Lukasz M

看起来我从 style.xml 中提取的 xml 格式没有通过，所以我又试了一次。如果我查看 FormatId “37993”，在哪里可以查找到实际的“mm/yy;@”格式？然后我需要想出一种方法来检查所有日期格式的情况。 - maguy

我参考了 zip 文件手动进行检查。你可以通过 OpenXML SDK 获取 formatCode 的值。请查看我更新后的答案，其中包含一个执行此任务的方法代码。 - Lukasz M

显示剩余5条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Lukasz M · Accepted Answer

格式 ID 值的列表

以下是格式选项列表（来源）

ID  Format Code
0   General
1   0
2   0.00
3   #,##0
4   #,##0.00
9   0%
10  0.00%
11  0.00E+00
12  # ?/?
13  # ??/??
14  d/m/yyyy
15  d-mmm-yy
16  d-mmm
17  mmm-yy
18  h:mm tt
19  h:mm:ss tt
20  H:mm
21  H:mm:ss
22  m/d/yyyy H:mm
37  #,##0 ;(#,##0)
38  #,##0 ;[Red](#,##0)
39  #,##0.00;(#,##0.00)
40  #,##0.00;[Red](#,##0.00)
45  mm:ss
46  [h]:mm:ss
47  mmss.0
48  ##0.0E+0
49  @

然而，这些列表只列出了几种格式。根据这篇文章: Reading dates from OpenXml Excel files，ID值小于164的格式是内置的。在那里您还可以找到更长的格式列表。

检查xlsx文件中的格式ID值

对于ID值较大的格式，您可以在文件本身中找到它们的定义。为了看到它们，您应该使用zip归档浏览器打开它，并在xl目录中找到styles.xml文件。或者使用Open XML SDK 2.0 Productivity Tools打开此xlsx文件，然后导航到该文件的/xl/styles.xml/x:StyleSheet节点。

在该部分，您应该能够看到定义在您的文档中以及分配给它们的ID值的格式。格式部分应类似于以下内容：

...
<x:numFmts count="1">
    <x:numFmt numFmtId="166" formatCode="yy/mm/dd;@" />
</x:numFmts>
...

从这里存储的格式来看，id值可能是特定于 xlsx 文件的，因此两个不同的 xlsx 文件中可以使用相同的 ID 值来定义不同的格式。但是，对于内置格式，它们是预定义的，在所有文件中应该相同。

如果您需要在文件中查找这些格式或需要其他信息，请告诉我。

编辑

您还可以在此文档中找到有关数字格式的更多信息：http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspx。

第二次编辑

您可以使用此代码获取包含 xlsx 文件中定义的所有格式的字典：

private Dictionary<uint, String> BuildFormatMappingsFromXlsx(String fileName)
{
    Dictionary<uint, String> formatMappings = new Dictionary<uint, String>();

    using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
    {
        var stylePart = document.WorkbookPart.WorkbookStylesPart;

        var numFormatsParentNodes = stylePart.Stylesheet.ChildElements.OfType<NumberingFormats>();

        foreach (var numFormatParentNode in numFormatsParentNodes)
        {
            var formatNodes = numFormatParentNode.ChildElements.OfType<NumberingFormat>();
            foreach (var formatNode in formatNodes)
            {
                formatMappings.Add(formatNode.NumberFormatId.Value, formatNode.FormatCode);
            }
        }
    }

    return formatMappings;
}

如果您想检查其中任意一个是否是日期，我想一个简单的方法是验证格式代码（由我发布的方法创建的字典中的值）是否包含mm和yy子字符串。