Cell format documentation about php_xlsxwriter HOT 4 CLOSED

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

from php_xlsxwriter.

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.

from php_xlsxwriter.

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?

from php_xlsxwriter.

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.

from php_xlsxwriter.