Menu
I am analyzing the syles.xml in .XLSX file and find for the same format ID = 14, in document https://xlsxwriter.readthedocs.org/en/latest/format.html#format-set-num-format , it is "m/d/yy", but in OOXML Specification ECMA-376 1-st Edition Part 3, 147 page. , it becomes "mm-dd-yy" and when I try to set a cell to numberformat ID = 14 in Excel 2003 Chinese version, then actually the displayed string is in "yyyy-mm-dd”. Since FormatCode string varies when the locale and version of Excel varies, how can I obtain the correct FormatCode string based on the given numFmtID for the Excel installed on the local version? Are there any way to call Automation to obtain this?
Thanks
Alan
932
Number formatting in Excel is a very powerful and essential feature required to display data in an understandable and meaningful way to the viewers, not affecting the calculations. 1. Use of the Excel Format Code to Display Text with Number 2. Add Decimal Places, Spaces, Colors, and Conditions in Excel by Using the Number Format Code 3.
The problem: Not the number format code is returned but rather the “Text value corresponding to the number format of the cell.” And – as you can see from our example above – can be the same for different custom number format codes because it doesn’t include the custom text in the format code.
In the Type list, select the number format that you want to customize. The number format that you select appears in the Type box at the top of the list. In the Type box, make the necessary changes to the selected number format.
Create a custom format code 1 On the Home tab, click Number Format , and then click More Number Formats. 2 In the Format Cells dialog box, in the Category box, click Custom. 3 In the Type list, select the number format that you want to customize. ... 4 In the Type box, make the necessary changes to the selected number format.
Your observation is right. There is incompatibility between XML and Excel specifically for number 14. I am referring to 2 threads below wherein the efforts put by their team elaborates on this matter. Number formats are defined up-to 49 and custom format workout has been suggested to use a number more than 50. Please go through these links, if not solution of your problem they at-least vindicates your stand. Please note I have recently joined Stackoverflow and my knowledge is at beginner level. Thanks
(Info)Setting different date formats in Excel
XLSX numFmtId predefined ID = 14 doesn't match
172
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
