Excel to PDF - Set number formatting for grouping and decimal separator by setting workbook region and locale

Aspose.Cells Product Family
1

Hi,

How it is possible to set grouping separator and decimal separator used in number formatting methods when XLS converted to PDF?

The only method is working: java.util.Locale.setDefault(myLocale) but this mechanizm is unsafe for the application at whole.

Aspose methods workbook.setLocale(), workbook.setLanguge() do not change format symbols.

Tip. Both versions Aspose.Cells 2.1.0 and 2.4.0 have the same problems.

Best regards, Evgeniy

2

Hi Evgeniy,

Please forward us your MS Excel file and the code, in order to let us know what you are trying to do. This will help us to understand and investigate the issue.

Thanks,

3

Hi Salman,

String FILE_NAME = "NumberFormatter";

Workbook workbook = new Workbook ();

try {
workbook.open(FILE_NAME + ".xls");

workbook.setLocale(Locale.GERMANY);
workbook.setLanguage(CountryCode.GERMANY);

workbook.getWorksheets().getSheet(0).getCells().getCell(0, 1).setValue(1234567.89d);

workbook.save(FILE_NAME + "_out.xls");
workbook.save(FILE_NAME + "_out.xml", FileFormatType.ASPOSE_PDF);
} catch (Exception ex) {
ex.printStackTrace();
}

The code above inserts number 1234567.89 into B1 (cell [0, 0]) and saves file into XLS and PDF format. Output value is "1,234,567.89" - grouping separator is a comma (please see the attached XML file). My task is to set correct grouping and decimal separator for number to have it as "1 234 567.89" or as "1'234'567'.89".

I tried to use setting locale and/or language with methods

workbook.setLocale(Locale.GERMANY);
workbook.setLanguage(CountryCode.GERMANY);

but the output files are not changed.

Best regards, Evgeniy

4

Hi,


Workbook.setLocale() and setLanguage() are just to set the "COUNTRY" properties of Workbook which will be saved when saving a workbook in MS Excel. But the actual formattings of the workbook's data still depends on the locale in effect when viewing the data in MS Excel. For data formattings, the output of Aspose.Cells is just same with what you can get in MS Excel. That is, current locale in effect will determine the formattings.



Thank you.




5

Hi,

And what can you suggest to do? How to set grouping separator?

Best regards, Evgeniy

6

Hi,


Well, as a workaround I think you can change the default locale before calling Workbook.save() method and recover the default locale after Workbook.save(),

e.g

Locale localeDefault = Locale.getDefault();

Locale localeNew = Locale....;

Locale.setDefault(localeNew);

workbook.save(...);

Locale.setDefault(localeDefault);

Thank you.

7

Thank you, but your advice is not helpful as far we cannot change current locale in multi-user application on server side.

Is it possible for you to add new parameter in Aspose.Cells to manage this properly?

Best regards, Evgeniy

8

Hi,


Well I am afraid, it requires us to change the internal model of formatting numbers to support custom Locales. We have recorded your requirement into our issue tracking system with id CELLSJAVA-19910. We will looking into this feature later when we finish some other important tasks (on hand).


Thanks for your understanding!

9

The issues you have found earlier (filed as 19910) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier
10

Can you please explain the solution? I’m also trying to create PDFs for many different locales on a single server, so we can’t change the default locale.

One of the earlier messages in this thread suggests workbook.setLocale, but that method no longer exists (I’m using aspose-cell-7.1). setRegion and setLanguage are both marked as deprecated, but I can’t find any information on what should be used instead.

Thanks.
Martin

11
Hi Martin,

Could you try to set locale for your workbook in your code accordingly, See the below code segment for your reference.
e.g

WorkbookSettings setting = workbook.getWorkbookSettings();
setting.setLocale(.............);


Also, Please download and use the latest version/fix: Aspose.Cells for Java.

Let us know if it works fine.
12

Sorry - that doesn’t work for me, even with 7.1.2.2. If I change the Java default then the PDF changes, but as others have said that isn’t suitable for a multi-user system.

If I set the locale in the workbook settings the PDF still uses the default locale.

13

Hi,

We are working on this feature now and we will try to provide a fix/version in about one month that supports to format numbers according with the Locale specified by user(settings of Workbook.Settings.Locale/Region).

14

Thanks - obviously the sooner the better as far as we’re concerned.

15

The previous post by the ASPOSE team suggested that a fix for the locale setting would be ready in approximately one month (posted - 03-23-2012). We are now nearly three months on. Is there an update on when this functionality will be included (or if it has been included) and more importantly when will it be released?

16

Hi,



Could you try our latest fix: Aspose.Cells for Java

and let us know your feedback.

Thank you.

17

Sorry - that doesn’t seem to fix it.
My code is:

WorkbookSettings setting = this.workbook.getSettings();
setting.setLocale(newLocale);

and log statements confirm it is setting the correct locale (I’ve been using nl_BE in my tests). But the PDF still uses English number formats (123,456.89) instead of 123.456,89 as I’d expect.

18

Hi,

Thanks for your posting and using Aspose.Cells for Java.

I was able to replicate the problem using the source file (attachment). When I changed the locale to Germany, the output pdf is still showing in English locale format.

We will look into it and fix this issue asap.

This issue has been logged as CELLSJAVA-40213.

Java


String filePath = “F:\Shak-Data-RW\Downloads\source.xlsx”;


Workbook workbook = new Workbook(filePath);


WorkbookSettings setting = workbook.getSettings();

setting.setLocale(Locale.GERMANY);


workbook.save(filePath + “.out.pdf”);

19
Hi,

Please download and try the latest fix: Aspose.Cells for Java

From this fix, we format cell values by the setting: WorkbookSettings.Region/Locale. Users can specify the locale for formatting values by Workbook.getSettings().setRegion()/setLocale().

A notable change for formatting existing excel97-2003 files: For existing excel97-2003 template files, the region value may has been saved in it and different from the default locale of application. We will format values by the saved region value if user did not change it explicitly after loading the template file. So the output for this template file will be different from what created by old versions because in old versions we always format value by the default locale of java application.

To make sure that existing template files be formatted with system default locale, user should add following code before get any formatted values:
Workbook.getSettings().setRegion(CountryCode.DEFAULT); // or setLocale(Locale.getDefault());
20

The issues you have found earlier (filed as CELLSJAVA-40213) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.