Number (amount) values are not converted properly

[UshaThavasiappan]

Hello,
Im converting excel file into pdf using spire.xls-12.9.1

When I try to convert, I got 2 issues on number (amount) values.
Screenshot of issue, sample program, sample source and converted file are attached for your reference.

1. Amount values are not properly separated with comma symbol
In "CashFlowStatement" sheet, under Operations table, the amount value for "Interest" is not properly separated with comma symbol. It should display 3,500 instead of 350,0
This issue occurs only for 4 digit numbers. I could not see any issue for more than 4 digits.

2. Negative amount values are surrounded with () and no negative sign
In "CashFlowStatement" sheet, under Operations table, the negative amount value for "Inventory purchases" is not shown as per the data. It should display -2,64,000 instead (2,64,000)

I really appreciate your help on this.

NumberFormatIssue.zip

(392.61 KiB) Downloaded 482 times

[Abel.He]

Hello,

Thanks for your inquiry.
After investigation, I didn’t reproduce your issue and attached the result Pdf document for your reference. I guess this issue is related to the system environment. My OS is Win10-64bit and the region setting of OS is China-Chinese, and the JDK version is Oracle JDK1.8. To help us do further investigation and work out a solution for you, please offer the following message. Thanks for your assistance in advance.

1) The test environment that converting the Xlsx file to pdf file, such as OS info (E.g. Windows 7, 64-bit) and region setting (E.g. China, Chinese).
2) The Jdk version, such as Oracle Jdk 1.8.
3) The WPS Office version, such as 11.1.0.12598.

Sincerely
Abel
E-iceblue support team

[UshaThavasiappan]

Hello Abel,

My test environment is Windows 11 - 64bit and JDK 17.0.2. Even I tried with JDK 1.8, but im getting the same issue

I also tried with Windows 10 - 64 bit and JDK 15.0.2, but no luck
Even I tried with CentOS Linux version 7 with Open JDK 1.8.0_222.
I'm still facing the same issue.

Update : WPS version 11.2.0.11341

[Abel.He]

Hello,

Thanks for your feedback.
I did reproduce this issue on Centos Linux. After further investigation, the Culture-name of WPS Office is different with the local system, the the Culture-name of WPS Office is “zh-CN”, therefore, you need add the following code to set Culture-name.

Code: Select all

Locale locale = new Locale("zh", "CN");
Locale.setDefault(locale);

In addition, I put the complete code below for your reference.
If you have any issue, just feel free to contact us.

Code: Select all

//Set Culture-name
Locale locale = new Locale("zh", "CN");
Locale.setDefault(locale);

//Create a Workbook instance and load an Excel file
Workbook workbook = new Workbook();
workbook.loadFromFile("data/cash-flow-statement.xlsx");

//Set worksheets to fit to page when converting
workbook.getConverterSetting().setSheetFitToWidth(true);

//Save the resulting document to a specified path
workbook.saveToFile("output/cash-flow-statement_fitToWidth_auto_12.8.4.pdf", FileFormat.PDF);

Sincerely
Abel
E-iceblue support team

[UshaThavasiappan]

Hello Abel,
I tried your suggestion. It was working good for "CN".
But for "US", I'm getting the same issue.

Code: Select all

        Locale locale = new Locale("en", "US");
        Locale.setDefault(locale);

Help me out !

[Abel.He]

Hello,

Thanks for your feedback.
Different Culture-name will affect the display effect of characters. The Culture-name of WPS Office is “zh-CN”, and if the Culture-name of your OS is en-US, when converting excel file to pdf file, there may happen that -123,000 become (123,000), 3,500 become 350,0 etc. Therefore, to avoid this, you need to set Culture-name to “zh-CN” in your code to match the Culture-name of WPS Office.
If you have any issue, just feel free to contact us.

Sincerely
Abel
E-iceblue support team

[UshaThavasiappan]

Hello,
I hope this issue is not about the WPS Office Culture settings. In my machine, the language is defaulted with English USA.

I do not know how you have figured the WPS culture is CH.
I have a point to clarify that, the Spire.Office or Spire.XLS API will look for the default office tool in the operating system?

Kindly refer to the screenshot of my WPS office default language settings.

NumberFormatIssue-WPSLocale.png (159.54 KiB) Viewed 2883 times

Note: Even I tried with Windows 10 with MS-Office. I got similar issue for number formats

[Abel.He]

Hello,

Thanks for your feedback.
The Spire.Office or Spire.XLS API don’t look for the default office tool in the operating system.
In addition, you use MS Excel to make the same excel file, then using our product to convert it to pdf file to see whether there is number format issue in result pdf document.
And please tell me your version information of MS Office. Thanks for your assistance in advance.

Sincerely
Abel
E-iceblue support team

[UshaThavasiappan]

Hello,

I got a file from one of my clients and tried to convert it using Spire.Office and Spire.Xls.

I tried the same using Microsoft 365 MSO version 2209 Build 16.0.15629.20200 on Windows 10 (64 bit) with JDK 15.0.2
I could reproduce the same issue on this server too.

Please help to resolve this as early as possible.

[Abel.He]

Hello,

Thanks for your feedback.
I have some information to confirm with you:
1） Is the computer on which you opened the excel document (the effect is as shown in the screenshot below) the same computer on which you converted excel to pdf?
2） Please offer the Excel file generated by Microsoft 365 MSO version 2209 Build 16.0.15629.20200 on Windows 10 (64 bit).


Sincerely
Abel
E-iceblue support team

[UshaThavasiappan]

Hello Abel,

1） Is the computer on which you opened the excel document (the effect is as shown in the screenshot below) the same computer on which you converted excel to pdf?
Yes, the file is converted and opened in the same computer
2） Please offer the Excel file generated by Microsoft 365 MSO version 2209 Build 16.0.15629.20200 on Windows 10 (64 bit).
Please visit the attachment

cash-flow-statement-NumberFormat.zip

(124 KiB) Downloaded 486 times

[Abel.He]

Hello,

Thanks for your feedback.
I reported your scenario to our development team and logged it into our issue tracking system with the ticket number SPIREXLS-4237. Our development team will investigate it. Once there are any updates, I will inform you in time. Sorry for the inconvenience caused.

Sincerely
Abel
E-iceblue support team

[UshaThavasiappan]

Hello,
Thanks for your response. Please do keep post me.

[Abel.He]

Hello,

Once there are any updates, I'll inform you in time.

Sincerely
Abel
E-iceblue support team

[Abel.He]

Hello,

Greeting from E-iceblue.
For this issue with the number SPIREXLS-4237, I have some updates to inform you according to the feedback from our development team:
This issue is caused by locale. In Java language environment, there no way to get the locale of OS directly. And if you don’t use code to set the locale, the locale defaults to US. When the locale is US, the minus sign will become parentheses in excel document, and when I set my computer’s local to en(US) and then opened the excel document (cash-flow-statement.xlsx) to confirm this, as shown in the screenshot below. Therefore, if you want the number format to be unchanged, you need to use code to set the same locale as your OS. The code snippet as following:

Code: Select all

//Set Culture-name, you need to change the "zh-CN" to the locale of your computer's OS
Locale locale = new Locale("zh", "CN");
Locale.setDefault(locale);

If you have any issue, just feel free to contact us.

Sincerely
Abel
E-iceblue support team