Closed Malvineous closed 8 years ago
And of course now I find test.php which has most of the formats in it, including percent. Only one I can't see is the text format, where you can have normal text and variable-width numbers with leading zeroes, and the leading zeroes don't get stripped out. Any chance you could add that? Excel calls the format 'Text'.
You can try "string" or "GENERAL" as the text format, or even the formula: "='mytext" If you have a known/fixed number of leading zeros in that column, use the format code "000000" to turn 55 into 000055
I use libreoffice for spreadsheets, and it has a window to customize cell formatting, I just use that to design difficult/custom cell formats. Its true some good documentation on this would be in order.
Thanks for the response! I have tried GENERAL
and string
but these are equivalent to the Excel "General" format, which strips leading zeroes. I want to avoid ='0123
as then I will have to parse the formula when extracting data again. Unfortunately format codes like 0000
won't work as the values in this case are variable-length.
I tried setting the format in Excel and saving it, and it behaves the way I want - values like 0123
have their leading zeroes preserved. However I cannot see from the XML what value I should pass to PHP_XLSXWriter as the column type! Are you able to decipher anything from this?
<worksheet>
<cols>
...
<col min="4" max="4" width="11.42578125" style="3"/>
</cols>
</worksheet>
<styleSheet>
<numFmts count="1">
<numFmt numFmtId="164" formatCode="0000000000"/>
</numFmts>
...
<cellStyleXfs count="1">
<xf numFmtId="0" fontId="0" fillId="0" borderId="0"/>
</cellStyleXfs>
<cellXfs count="4">
<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>
<xf numFmtId="164" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
<xf numFmtId="9" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
<xf numFmtId="49" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
</cellXfs>
<cellStyles count="1">
<cellStyle name="Normal" xfId="0" builtinId="0"/>
</cellStyles>
</styleSheet>
As far as I can tell, the column I want (4) has a style of 3
set. But what does that mean? <xf>
index 3 has numFmtId
of 49
(or 9
, not sure if it's zero-based), but I don't think I can put numbers into the PHP_XLSXWriter headers.
Any ideas?
Oh wow well it was a long shot, but I tried searching the ECMA-376 specs for "49" and believe it or not, I actually found the answer. On page 1768 (section 18.8.30 numFmt) it has a table of built in format IDs. 49 translates to @
, so I put an @
in as the PHP_XLSXWriter column type and it worked! The column type has changed to "Text" and leading zeroes are preserved.
Is there anywhere where the cell formats are documented? Looking at the code I can see a few types listed, but nothing for things like number-as-text to preserve leading zeroes. I also can't see how to specify percentages either.
I've downloaded the ECMA-376 specs but can't see anything in there that lists these formats either - I'm sure they're there I just don't know what to search for.
Would it be possible to include a reference list of available format specifiers somewhere, either in the code or on the project's GitHub wiki page?