d-mmm-yy
?I'm struggling to format date from ISO-8601 to custom numFmtId=15
cell.
From my searching I found:
OpenXml and Date format in Excel cell, What do excel xml cell attribute values mean?, What's the difference between <c t="str"> and <c><is> in Office Open XML? give me headstart on understanding excel xml.
Microsoft docs 1 which suggest <x:xf>
and <cellStyleXfs>
, Microsoft docs2 explaining about numFmts (Number Formats)
which I haven't found how it's supposed to works in cell format.
https://social.msdn.microsoft.com/Forums/office/en-US/c02fc9f9-0a7d-40b5-ac16-48199df474ba/spreadsheet-format-number?forum=oxmlsdk which suggest to make two different excel with the second being what you expected and compare the .xml
the only difference I found was only in styles.xml
which suggest numFmtId="15"
http://www.ericwhite.com/blog/dates-in-strict-spreadsheetml-files/
What indicates an Office Open XML Cell contains a Date/Time value?
I was able to detect whether or not the column header that contains date
keyword and whatever column value inside, but I was unable to format it properly.
The best I was able to found on jsreport related thread was: https://jsreport.net/learn/html-to-xlsx which wasn't helping me that much since I was using xlsx
recipe.
You can see playground here which contains my progress so far.
To format cell you can add on style.xml
:
`<xf numFmtId="15" fontId="0" fillId="0" borderId="1" xfId="0" applyNumberFormat="1" applyFill="1" applyBorder="1" applyAlignment="1">
<alignment horizontal="center" />
</xf>`
And update which part which using that format.
example:
<c t="d" s="4">
You can see result on Playground here.
The most helpful resources from which I've linked in the question:
https://social.msdn.microsoft.com/Forums/office/en-US/c02fc9f9-0a7d-40b5-ac16-48199df474ba/spreadsheet-format-number?forum=oxmlsdk help me a lot comparing the .xml
to find the difference to add.
What do excel xml cell attribute values mean? explaining what attributes do.