日期的PHPExcel数字格式 [英] PHPExcel number formats for dates

问题描述

我有一个电子表格，当我在excel中打开时，相关单元格的格式显示为日期mm-dd-yyyy.

I have a spreadsheet that when i open in excel the cells in question show formatting as date mm-dd-yyyy.

当我通过php Excel Reader(xlsx文件)运行此文件时，它无法识别出它是一个日期.

When I run this through php excel reader (xlsx file) it does not recognize that it is a date.

我在ms open xml sdk中打开了文件，并以numFmts的样式显示了

I opened the file in ms open xml sdk and it shows in the styles for numFmts

numFmtId="102" formatCode="mm-dd-yyyy"
numFmtId="104" formatCode="mm-dd-yyyy"
numFmtId="106" formatCode="mm-dd-yyyy"
numFmtId="108" formatCode="mm-dd-yyyy"
numFmtId="110" formatCode="mm-dd-yyyy"
numFmtId="112" formatCode="mm-dd-yyyy"
numFmtId="114" formatCode="mm-dd-yyyy"
numFmtId="116" formatCode="mm-dd-yyyy"
numFmtId="118" formatCode="mm-dd-yyyy"

它只会在我添加后转换为日期

It would only convert to date after I added

self::$_builtInFormats[102]             = 'mm-dd-yyyy';
self::$_builtInFormats[104]             = 'mm-dd-yyyy';
self::$_builtInFormats[106]             = 'mm-dd-yyyy';
self::$_builtInFormats[108]             = 'mm-dd-yyyy';
self::$_builtInFormats[110]             = 'mm-dd-yyyy';
self::$_builtInFormats[112]             = 'mm-dd-yyyy';
self::$_builtInFormats[114]             = 'mm-dd-yyyy';
self::$_builtInFormats[116]             = 'mm-dd-yyyy';
self::$_builtInFormats[118]             = 'mm-dd-yyyy';

到NumberFormat.php.

to NumberFormat.php.

应该是这样吗

推荐答案

MS Excel将格式代码0到163用于内置"格式，但是此集中有许多未使用的条目，并且没有正式定义任何内容格式代码102到118.在PHPExcel PHPExcel_Style_NumberFormat类中列出为内置代码是所有主要内置代码，只忽略了一些针对中文/韩文/泰文/日文本地化的特殊本地化格式.

MS Excel uses format codes 0 to 163 for "built-in" formats, but there are a lot of unused entries in this set, and nothing is officially defined for format codes 102 to 118. The codes listed as built-in in the PHPExcel PHPExcel_Style_NumberFormat class are all the main built-ins, only ignoring a few special localised formats for Chinese/Korean/Thai/Japanese localised.

但是，此限制并不能阻止许多顽皮的自制xlsx编写器使用ISO规范中未实际定义的保留的" id值.

However, this restriction doesn't prevent a lot of naughty homebrew xlsx writers from using "reserved" id values that aren't actually defined in the ISO specification.

通常，这些值应在/xl/styles.xml文件中的如下所示的块中定义:

Typically, these values should be defined in the /xl/styles.xml file in a block looking like:

<numFmts count="2">
    <numFmt formatCode="mm-dd-yyyy" numFmtId="102"/>
    <numFmt formatCode="mm-dd-yyyy" numFmtId="104"/>
    ...
</numFmts>

尽管最新版本的PHPExcel符合ISO标准，并且会忽略164以下的任何值，除非在正式规范中明确定义(即仅使用内置列表中的值)，但在1.8分支可以容忍"这种对标准的滥用，并且代码将读取用户定义的数字格式值，该值低于164，除非它们覆盖了标准中定义的值.

While the latest production release of PHPExcel adheres to the ISO standard, and ignores any values below 164 unless they are explicitly defined in the formal specification (ie uses only values in the built-in list), a change has been made in the 1.8 branch to "tolerate" this misuse of the standard, and the code will read user-defined number format values below 164 unless they over-ride a value defined in the standard.

这篇关于日期的PHPExcel数字格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！