European number formatting

Aspose.Cells Product Family
1

Hi

I've created a solution which adds summary information to spreadsheets. However, as the data is for European clients the number formatting used is different. I need to be able to change the default formatting in a workbook so that a decimal point is used as the thousands seperator.

i.e.

I need the number two thousand to change from "2,000" to "2.000".

This is also creating problems when using formulas because the decimal point is being recognised as a decimal point and not a thousands seperator.

i.e.

For two thousand plus five hundred...
...I'm getting 2.000 + 500 = 502.000
...but I need 2.000 + 500 = 2.500

What do I need to do?

2

1. For number format, you can try:

cells["A1"].PutValue(2000);

cells["A1"].Style.Number = 3;

2. When using Aspose.Cells to set formula, the decimal point is always "." .

So to set the formula, please use the following code:

cell.Formula = "=2000+500";

3

Number format 3 still uses a comma as a thousands seperator. I need to use the comma as a decimal separator and the decimal point as a thousands seperatr. This is standard formatting for many European countries (France, Sweden, Denmark).

Eg.

Normal formatting: 1,659.95
European formating: 1.659,95

As you can see the comma and decimal point have been reversed. Is this possible in Excel using Aspose?

4

In MS Excel, decimal and thousand separators depend on language and local settings. If your local settings is us-en and number format is 3,

1659.95 will show 1659.95 in formula bar and 1,660 in cell A1.

If you change decimal separator to "," and thousand separator to ".",

1659.95 will show 1659,95 in formula bar and 1.660 in cell A1.

Following is my sample code and attached is my output file.

Workbook workbook = new Workbook();
Cells cells = workbook.Worksheets[0].Cells;
cells["A1"].PutValue(1659.95);
cells["A1"].Style.Number = 3;
workbook.Save("d:\\test\\abc.xls");

5

Hi.


There is exists a way to override system decimal and thousands separators (like in Excel advanced settings)?

Best regards. Alexey
6

Hi Alexey,


Well, I think you need to use Style.Custom attribute to specify your desired formatting strings accordingly and independently, see the sample code below.
e.g
Sample code:

var workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;
Aspose.Cells.Cell cell = cells[“A1”];
cell.PutValue(1234567);
Style style = cell.GetStyle();
style.Custom = “[$-1010409]”$"#’##0;("$"#’##0)";
cell.SetStyle(style);
workbook.Save(“e:\test2\out1.xlsx”);


Thank you.
7

Hi,


I am having problems using this method. I am using Aspses Cells to create a chart from a column of data.

The problem is getting the value axis to have the correct formatting with custom thousand separators.

This method works fine when the thousand separator is “,”. However, when I try and use any other character, eg apostrophe (#’##0), it only adds the thousand separator in front of the final 3 numbers, like this:

1000’000

I have tried to format each cell in the column with a format created according to the cell’s content. Eg for a cell with 1000000 I applied the format #’###’##0. This works fine for the cell but the graph only seems to take the format for the first cell so only certain numbers on the axis have the right format.

I have also tried to format the cell values using different locales, this gives the correct format for the cells but does not seem to feed through to the chart axis, presumably as I have only formatted the cell’s value and not set a style on the cell?

Is there a way I can just set the style on the value axis based on a locale? Or better still set the Thousand and Decimal separators that can be accessed through advanced options in Excel?

Thanks.

8

Hi Shruti,


Well, Aspose.Cells follows MS Excel standards and specifications for certain objects (including chart object). If you could accomplish your task in Ms Excel manually, you can do it via Aspose.Cells. We are not sure about your issue. Could you paste your sample code (runnable) here or preferably create a sample console demo application (you may zip it prior attaching here) with v8.2.1.x (latest version) and post us here to reproduce the issue on our end. Please also provide your output Excel file via Aspose.Cells APIs. Also provide a sample file containing your expected chart (formatted), you may create the chart with numeric formattings for its objects in Ms Excel manually, it will help us to know what do you want to achieve. We will check it soon.

Thank you.
9

Hi Amjad,


Thank you for your swift reply. Attached is an example project (minus license file) that creates a chart with formatted axis labels.

Also in the zip are the .xlsx that is produced from my Aspose project (Aspose_support_example.xlsx) and the .xlsx that I have manually formatted in Excel to have the correct axis labels (Aspose_support_example_manual.xlsx).

Many Thanks.


10

Hi Shruti,


Thanks for the template files and sample code.

I have evaluated your scenario/ case a bit. Well, if you need to accomplish the task as per your “Aspose_support_example_manual.xlsx” file, you will change the line of code:
i.e.,
style.setCustom(“#'##0”);
with:
style.setCustom(“#,##0”);

Please see the screen shot of your expected file when it is opened into the Ms Excel here:
http://prntscr.com/4p9jm7

I have compared my output file (after updating the above line of code) to your expected file regarding data labels and cell styles, both are same as you my test it.

Thank you.



11

Hi Amjad,


Thank you for looking into this matter further. I know that it works OK using a comma as the thousand separator. The problem we are having is using any character other than comma e.g. dot, space, apostrophe.

Our software allows the user to choose from a range of thousand and decimal separators to appear in their reports, without the need for them to change their OS locale (something which many of them will not have permission to do). They need to do this as they will be producing documents for clients in many different countries. As you may be aware the comma thousand separator is one of the the less commonly used thousand separator characters globally.

We have experienced no problems formatting numbers with the user assigned character anywhere else throughout our .doc and .ppt output files (produced using Aspose libs). It’s only been a problem on chart axis.

I’m sure if we had access to the Decimal and Thousand separator properties in Excel’s advanced options then this could easily be made to work. As I understand it, this is not available, is that correct?

Alternatively, perhaps if we could gain access to the individual axis tick label’s text then we could simply reformat each label’s text ourselves to suit our needs. Is that something that might be possible?

Thank again.





12

Hi Shruti,


Well, you may try to use:
e.g
chart.ValueAxis/CategoryAxis.TickLabels.NumberFormat attribute to specify your desired numbers formatting for the ticket labels but it would work as per MS Excel does. I am not sure how could you do this in Ms Excel for the chart’s axis labels, so could you create the chart with your desired custom numeric formattings for its tick labels objects in Ms Excel manually and post us the file here, so we will open it in MS Excel to see how it is displayed to know your requirements on our end.

Thank you.
13

Amjad,


I feel we are going round in circles at the moment. What you are requesting in this post we have already done in my second post - see the file attached to that post (Aspose_support_example_manual.xlsx). That file contains a graph with our required formatting.

Many thanks
14

Hi,


As I said earlier, when I change the line of code:
i.e.,
style.setCustom(“#'##0”);
with:
style.setCustom(“#,##0”);

in your code segment and generated the output file. Now when I compare both files(I open yours “Aspose_support_example_manual.xlsx” file and my output file into MS Excel), both are same, please see the screen shot for the comparison of both files here:

Hope, I have clarified now that there is no issue and Aspose.Cells works as per your file that you created manually.

Thank you.

15

Hi,


Sorry, it seems I missed out a step to reproduce the graph with the required formatting:-

You have to go to File>Options>Advanced and change your Thousand separator to '

That should result in you getting the formatting we require - see attached image.

Many thanks
16

Hi Shruti,


Thanks for providing us further details.

We will check if we could accomplish your requirements as per your needs via Aspose.Cells APIs. We need to evaluate if we could set number formatting to place a character (e.g " ’ ") as the
thousands separator in chart’s axis labels etc. I have logged a ticket with an id “CELLSJAVA-41023” for your issue/ requirements. Our concerned developer will look into it.

Once we have any update on it, we will let you know here.

Thank you.
17

Hi,

Thanks for using Aspose.Cells.

We cannot support user specified separators for number formatting if no
locale use those special characters as number separator. It is the limit
of Java’s built in number formatting. However, we do support to format
numbers with user specified region/Locale settings and it does not need
user to change the system’s default locale.

For example, for the number
formatting in “Aspose_support_example_manual.xlsx”, you can easily
change the thousand separator to ‘.’ by changing the region of workbook
to one region whose thousand separator is ‘.’:


Java


Workbook wb = new Workbook(“Aspose_support_example_manual.xlsx”);

wb.getSettings().setLocale(Locale.GERMAN);

// or wb.getSettings().setRegion(CountryCode.GERMANY);

wb.save(“res.pdf”);


thus in the generated pdf the number such as "1500000" will be formatted as "1.500.000".
18

Hi Shakeel,


Thanks for looking into this. It seems like I am very close to being able to produce the exact formatting that we require.

I only need to format the labels in actual real world locale formats (The apostrophe one is for Switzerland). So no problem there.

I tried previoisly to use setLocale on the worksheet but ran into the same problem I am having now - the problem is how do I turn on Excel’s “Use 1000 Separator” for a cell or the whole worksheet using Apose?

I have tried using setNumber on the cell’s style but that seems to always revert the thousand separator to “,” no matter what locale I have set.

How can I turn on thousand separator for cell or worksheet?

Many Thanks.


19

Hi,

Thanks for using Aspose.Cells.

Please try the following code. It sets the 1000 separator for cell A1. I have attached the output xlxsx file and the output pdf file generated by the code for your reference.

Java


Workbook workbook = new Workbook();


Worksheet worksheet = workbook.getWorksheets().get(0);


Cell cell = worksheet.getCells().get(“A1”);


cell.putValue(1000000);


//Use 1000 separator

Style style = cell.getStyle();

style.setCustom("");

style.setCultureCustom("#,##0.00");

cell.setStyle(style);


worksheet.autoFitColumns();


workbook.save(“output.xlsx”);

workbook.save(“output.pdf”);