Excel Number format not same as in styles.xml when workbook is opened.

Question

Excel Number format not same as in styles.xml when workbook is opened.

R D 6

Excel workbook is generated using aspose cells. Generated workbook's styles.xml appears correct. However when the excel workbook is opened, the number format is no longer what it shows in styles.xml.

For example - in this case I would like to have comma as the decimal separator and dot as thousand separator, with one digit after decimal.

The workbook has following specified in the styles.xml :
<numFmt numFmtId="182" formatCode="#.##0,0;[Color3]-#.##0,0"/>
</numFmts>

When this workbook is opened, and when I look at the corresponding number format for the cell, it has been replaced with incorrect format string:
"#,##00;[Color3]-#,##00" which makes the numbers appear incorrect.
225511-workbook.xml
Number -40845,3 appears incorrectly as "-40845,300" - with no thousand separator and incorrect number of decimal places.

Excel options-->advanced options has been set to correctly match the format options.

Any idea what could be wrong here and why Excel is changing the format string?

225521-styles.xml

Hung-Chun Yu 981 Reputation points Microsoft Employee Moderator

2022-07-28T14:45:54.83+00:00
HI @R D

Thank you for contacting Microsoft Open Specification Support.
Questions for you

How was the .xlsx been created?

Could you send the before and after files in question to dochelp at microsoft dot com with subject line "Excel Number format not same as in styles.xml when workbook is opened" and body contains my name: Hung-Chun Yu.

Assuming you are opening Excel in question with Windows 10, could you go to Control Panel\Clock and Region, under Format Tab, click on Additional Settings, and include the screenshot of that page for Number and Currency?

See if possible, with Aspose cells, make sure do not enable use system separator, recreate the sheet and repeat the process to see if the issue still repro.

Hung-Chun Yu
Microsoft Open Specifications Support

Hung-Chun Yu 981 Microsoft Employee Moderator

HI @R D

Thank you very much for the files.

 It appeared you might have uncovered an issue with Excel, where it can't handle if period is used as thousand separator and comma is used as decimal point.  
 I updated the style.xml accordingly where I used comma as thousand separator and period as decimal and sheet would open and display the format properly. I am tagging [office-excel-itpro] to see if engineer there can confirm my finding and see if that is a known issue.

Hung-Chun Yu 981 Reputation points Microsoft Employee Moderator

2022-08-01T18:48:30.767+00:00

Hi @R D

I added [office-excel-itpro] tag, lets see if engineering there can charm in on this and confirm indeed is an issue with Excel, where it won't able to load and re-ender the format properly if period is used as thousand separator and comma is used as a decimal point.

Hung-Chun Yu
Microsoft Open Specifications Support
R D 6 Reputation points

2022-08-02T17:20:37.887+00:00

Thanks Hung-Chun Yu.
Similar issue is observed when space is used as thousand separator.
Is there any ETA for resolution? We are seeing multiple of our customers facing this issue.
Hung-Chun Yu 981 Reputation points Microsoft Employee Moderator

2022-08-02T17:23:35.48+00:00

Hi @R D

Currently I am working with our Engineers trying to sort out the issue. No ETA yet.

Hung-Chun Yu
Microsoft Open Specifications Support

3 answers

Your answer

Hung-Chun Yu 981 Reputation points Microsoft Employee Moderator

2022-07-28T14:45:54.83+00:00

HI @R D

Thank you for contacting Microsoft Open Specification Support.
Questions for you

How was the .xlsx been created?

Could you send the before and after files in question to dochelp at microsoft dot com with subject line "Excel Number format not same as in styles.xml when workbook is opened" and body contains my name: Hung-Chun Yu.

Assuming you are opening Excel in question with Windows 10, could you go to Control Panel\Clock and Region, under Format Tab, click on Additional Settings, and include the screenshot of that page for Number and Currency?

See if possible, with Aspose cells, make sure do not enable use system separator, recreate the sheet and repeat the process to see if the issue still repro.

Hung-Chun Yu
Microsoft Open Specifications Support
Hung-Chun Yu 981 Reputation points Microsoft Employee Moderator

2022-07-28T21:50:51.237+00:00

HI @R D

Thank you very much for the files.

It appeared you might have uncovered an issue with Excel, where it can't handle if period is used as thousand separator and comma is used as decimal point. I updated the style.xml accordingly where I used comma as thousand separator and period as decimal and sheet would open and display the format properly. I am tagging [office-excel-itpro] to see if engineer there can confirm my finding and see if that is a known issue.

<numFmt numFmtId="165" formatCode="#,##0.0;-#,##0.0"/>
Hung-Chun Yu 981 Reputation points Microsoft Employee Moderator

2022-08-01T18:48:30.767+00:00

Hi @R D

I added [office-excel-itpro] tag, lets see if engineering there can charm in on this and confirm indeed is an issue with Excel, where it won't able to load and re-ender the format properly if period is used as thousand separator and comma is used as a decimal point.

Hung-Chun Yu
Microsoft Open Specifications Support
R D 6 Reputation points

2022-08-02T17:20:37.887+00:00

Thanks Hung-Chun Yu.
Similar issue is observed when space is used as thousand separator.
Is there any ETA for resolution? We are seeing multiple of our customers facing this issue.
Hung-Chun Yu 981 Reputation points Microsoft Employee Moderator

2022-08-02T17:23:35.48+00:00

Hi @R D

Currently I am working with our Engineers trying to sort out the issue. No ETA yet.

Hung-Chun Yu
Microsoft Open Specifications Support

Answer 1

Hung-Chun Yu 981 Microsoft Employee Moderator

Hi @R D

It turned out it's by design.

The option customer is toggling is an app setting and it has impact only in how user interacts with the application, it has no impact on how file load/save would operate. File content must be written in a way that's invariant across different locales and different app settings. As a result, files are always written using comma and period as the thousands/decimal separator respectively.

Hung-Chun Yu
Microsoft Open Specifications Support

Answer 2

R D 6

If this is by design, please explain how one can set number formatting to custom format string, which is different than the standard English one.

Hung-Chun Yu 981 Reputation points Microsoft Employee Moderator

2022-08-04T18:32:36.847+00:00

Hi @R D

How it is shown depends on system's locale settings or on the Excel's settings for separator (File/Excel Options/Advanced).
I did find a solution not via OpenXML, but use VBA Marco.

Private Sub Workbook_Open()

Application.UseSystemSeparators = False
Application.DecimalSeparator = "."

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.UseSystemSeparators = True

End Sub

Answer 3

R D 6

VB cannot work for us. Excel workbook is generated on the server using a java library. It is not possible to set client-side locale settings, or preferences. Workbook generated needs to set custom format using specified decimal and thousand separator in the cell itself.

Also, this used to work before, and appears to be broken recently.

Hung-Chun Yu 981 Reputation points Microsoft Employee Moderator

2022-08-04T19:18:40.653+00:00

Hi @R D

I will check with our engineers.

Hung-Chun Yu
Microsoft Open Specifications Support
Hung-Chun Yu 981 Reputation points Microsoft Employee Moderator

2022-08-04T20:12:44.917+00:00

Hi @R D

From our Engineer -

"Within the file the period and comma characters are symbolic of the decimal and thousands separator. They don't represent literal characters that would be used. If users with different settings open the file they would see their individual settings respected (Windows Locale settings).

If the customer is asking for a way to (one a per-format basis) override those user settings then to my knowledge that is not possible, and it would be rather confusing if it where since users would need to manually inspect values and individual number formats to know if 1,000 is one-thousand or one with three decimal places.

If the customer is asking for how to user non-english separators within the format string stored in the file then this too is not possible. File content is always written in English to ensure we have a locale-invariant format from which we can do load/save from and machine and maintain proper fidelity."

I did found an older post from MSDN with a solution, however formatted cell no longer stored as a numeric value, instead it get converted to a string. Advantage of this it shows correct format, however disadvantage you won't able to do computation with the cell easily. Here is the link https://social.msdn.microsoft.com/Forums/office/en-US/85ff2122-6996-4a84-b618-7b9c4bd7439f/how-to-apply-a-custom-formatting-with-openxml-?forum=oxmlsdk

Hung-Chun Yu
Microsoft Open Specifications Support
Hung-Chun Yu 981 Reputation points Microsoft Employee Moderator

2022-08-04T21:43:19.663+00:00
To Summarized:

The file is interpreted using English separators, and then users interact with it in the runtime using separators based on each user's settings (via Windows Locale Settings, or Excel Options).

The number format string in the file can't force specific separators to be used.

Hung-Chun Yu
Microsoft Open Specifications Support

Share via

Excel Number format not same as in styles.xml when workbook is opened.

3 answers

Your answer