PHPExcel number formats for dates

Question

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

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

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';

to NumberFormat.php.

Is this supposed to be the case

`PHP Excel Reader` or `PHPExcel`? – Mark Baker Jan 06 '16 at 01:16 — Mark Baker, Jan 06 '16 at 01:16

Mark Baker · Accepted Answer · 2016-01-06T16:53:37.010

0

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.

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.

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>

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.

edited Jan 06 '16 at 16:53

answered Jan 06 '16 at 01:31

Mark Baker

209,507
32
346
385

So a different spreadsheet could have 102, 104, etc. with a format of #.#### or something different than mm-dd-yyyy. Is this correct? – Joseph Batson Jan 06 '16 at 16:45
No spreadsheet should be using those values anyway, precisely because they are reserved..... but as they're unused, some xlsx generators do use them regardless of that, and could define them as whatever format they wanted – Mark Baker Jan 06 '16 at 16:47
Ok, Thanks for the help. – Joseph Batson Jan 06 '16 at 16:58
A full list of all built-in codes can be found in section 18.8.30 of c061750_ISO_IEC_29500-1 (2012) – Mark Baker Jan 06 '16 at 16:59
Like I say, check the styles file, though the xlsx generator should hopefully have populated that correctly, and if so then PHPExcel 1.8 branch should read it correctly – Mark Baker Jan 06 '16 at 17:04

PHPExcel number formats for dates

1 Answers1

Linked