Hi,
i an facing an unexpected behavior while migrating from aspose.cells 18.4 to 24.6 (java)
this is a piece of code to reproduce my issue
// create a workbook from my .xlsx file
// test.xlsx should have this formula: =“Amount : “&TEXT(B1;”# ### ###,00 €”) in cell B2
Workbook wb = new Workbook(“test.xlsx”);
// get sheet to update
Worksheet sheet = wb.getWorksheets().get(0);
// set value on
inal BigDecimal amount = BigDecimal.valueOf(12345678,2);
// set value of B1 which is used in my formula
sheet.getCells().get(“B1”).putValue(amount); // cell B1 is an Accounting or Currency
// calculate formula
wb.calculateFormula(true);
// save as PDF
wb.save(“result.pdf”, new PdfSaveOptions());
Expected value in my PDF file (as previously with aspose Cells 18.4) : Amount : 123 456,78 €
This looks to me due to your locale/regional settings of the system (OS) where you are processing sample code with Excel file via Aspose.Cells APIs. Please note, the numbers formatting would be changed in accordance with the locale/regional settings of the system. For confirmation, you may open the template Excel file (with updated value) into MS Excel manually on the system (where you are processing the code segment) and you will also notice this behavior. In case, you still think it is an issue with Aspose.Cells API, kindly do zip your Excel file and output PDF and provide us, we will check it soon. Also, give us your environment details (OS, locale/regional settings, etc.).
By the way, you may change the locale of the workbook (for example, by using Workbook.Settings.Region/Locale) to match your environment (where comma is taken as decimal place) and then render it to PDF if it makes a difference?
@amjad.sahi ,
Thanks for your answer. indeed it was guessing that it depend on Region/Local , but i’m wondering why the same case was working fine with aspose.cells 18.4 and not with 24.6 with the same default system Locale (en-US) . you can try the sample code with version 18.4 and 24.6 and you can see the difference.
Regards
I am not sure why it was working in older versions. But I still suspect that it may have been working the other way around because even older versions used to evaluate number formatting based on the locale/regional settings. It’s possible that you processed it using an older version on some other machine where the relevant locale/regional settings were already applied and you got your expected results. Or you may have specified “Custom” number formatting for the cell(s). Please note, when you set custom number formatting to cells, it will render to PDF irrespective of the underlying locale/regional settings. The behavior shown by the newer Aspose.Cells is logical and expected and is the same as with MS Excel. Moreover, we are sorry, but we cannot evaluate issues using older versions.
@fournierl
By further test with locale such as fr, we can reproduce the issue. It is a bug of formatting numeric values which was introduced into new versions when we support to recognize some special group separators for some regions. We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSJAVA-46051
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
By our test we only found the issue with french language related regions where white space is the decimal group separator. We have fixed the issue for those regions.
Newer versions of Aspose.Cells behaves more similar to ms excel for formatting numbers, including the strict limit of regional settings. For your specified format pattern ”# ### ###,00 €”, it seems for your region white space is the group separator and ‘,’ is the decimal separator. So you need to use the proper locale/region which has the same separators to make the function work correctly(such as by setting wb.getSettings().setRegion(CountryCode.FRANCE) before calculating formulas).
Currently we only know the french language has the same separators so we fixed this issue for them. If the language used in your environment is other than french but can give the same result, please let us know so we can check and fix the issue for your language too.
Thank you for your feedback. It’s good to hear that the newer version works for you. Please feel free to reach out to us if you have any further queries or comments, and we will be happy to assist you soon.