从OpenXml Excel文件读取日期 [英] Reading dates from OpenXml Excel files

问题描述

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

I'm trying to read data from the .xlsx files using SharpZipLib to unpack it (in memory) and reading the inner xml files. Everything is fine but recognizing the dates - they're stored in julean format and I need to somehow recognize if a number is a date or only a number. In another topic (unfortunately it died and I need quick answer) I got to know some things from Mark Baker, but it's still not enough...

Excel将日期存储为浮点值...整数部分是自1/1/1900之后的天数（或根据使用哪个日历的1/1/1904），小数部分是比例一天（即时间部分）...由于1900被认为是一个闰年，这使得稍微更尴尬。

"Excel stores dates as a float value... the integer part being the number of days since 1/1/1900 (or 1/1/1904 depending on which calendar is being used), the fractional part being the proportion of a day (ie the time part)... made slightly more awkward by the fact that 1900 is considered a leap year.

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

The only thing that differentiates a data from a number is the number format mask. If you can read the format mask, you can use that to identify the value as a date rather than a number... then calculate the date value/formatting from the base date."

但是，日期的属性s是否始终为1？我知道它定义样式，但也许？

"But doesn't the attribute "s" for dates has always the value of "1"? I know it defines style, but maybe? ;)"

s属性引用了styles.xml中的一个样式xf条目，它将不会总是为日期的条目1 ...这一切都取决于工作簿中使用了多少不同的样式。样式xf依次引用数字格式掩码。要识别包含日期的单元格，您需要执行样式xf - >数字格式查找，然后识别该数字格式掩码是否为日期/时间数字格式掩码（而不是例如百分比或记帐号格式掩码）

The s attribute references a style xf entry in styles.xml, and it won't always be entry 1 for dates... it all depends how many different styles are being used in the workbook. The style xf in turn references a number format mask. To identify a cell that contains a date, you need to perform the style xf -> numberformat lookup, then identify whether that numberformat mask is a date/time numberformat mask (rather than, for example, a percentage or an accounting numberformat mask)

另外还有一个问题 - 我正在看一下style.xml的内容，在我看到的元素中，我看到了这样的元素：< xf numFmtId =14... applyNumberFormat =1/>，< xf numFmtId =1... applyNumberFormat =1/>等，但是没有< numFmts& ...有没有任何标准格式？或者我只是想丢一些东西？

"One more question - I'm now looking at the style.xml's content and in the section I see elements like: "<xf numFmtId="14" ... applyNumberFormat="1" />", "<xf numFmtId="1" ... applyNumberFormat="1" />", etc. but there is no <numFmts> section... Are there any "standard" formats? Or am I just missing something?"

有人可以帮我吗感谢提前。

Can someone please help me out? Thanks in advance.

推荐答案

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

You should find the numFmts section somewhere near the top of style.xml, as part of the styleSheet element

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

编辑

我一直在仔细检查我的xlsx读者代码（这是一段很长时间，因为我深入图书馆的那一部分）;并有内置格式。数字格式代码（numFmtId）小于164是内置。

I've been double-checking my xlsx reader code (it's been a long while since I delved into that part of the library); and there are built-in formats. Number format codes (numFmtId) less than 164 are "built-in".

我的列表不完整：

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# ??/??';

这篇关于从OpenXml Excel文件读取日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！