Custom number format

[wubble_u]

Greetings Aspose community & Support,

I’m having difficulty with number formats. What I would like is to see a number format of 123,3456,123.1234567.

What I’m getting is something along the lines of 123,3456,123.00. (As in the it doesn’t have a clue what do with the decimal spaces).

All I’d really like is an option to “include the thousand separator”, but that option doesn’t appear to exist in the Aspose library.* (* I could be wrong?)

I’ve tried setting a number style to 3 or 4, I’ve also tried using a custom format which it appears to ignore completely. Something like below

cellStyle.Custom = “#,##0.####”;

Some resources I have read to get a handle on what to do:

European number formatting

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 sep…

Setting Display Format of Numbers & Dates for Rows & Columns
Any help would be greatly appreciated, as always.

Thanks

[amjad.sahi]

Hi,

Aspose.Cells follows MS Excel standards, if MS Excel allows your desired number formatting, Aspose.Cells will also allow this. Could you please provide us your expected Excel file with your desired formatting (you may create the Excel file manually in MS Excel to specify your desired formatting for your data), save the file and post it here, we will check and help you on how to do this with Aspose.Cells.

Thank you.

[wubble_u]

Hello, thanks for the quick reply. I've attached an excel document detailing the format required. Let me know if you require further information.

[amjad.sahi]

Hi,

I tested your scenario/ case with our latest version/fix, please try the latest version/fix: Aspose.Cells for Java (Latest Version) (aspose-cells-7.6.1.4-java.zip). It works fine. Please specify the custom number format as “#,##0.0000”. I used the following sample code which gives me desired output. I have attached the output file here.

e.g

Sample code:

Workbook workbook = new Workbook();

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

Cells cells = worksheet.getCells();

Cell cell = cells.get(“A1”);

cell.putValue(123456789.1234);

Style style = cell.getStyle();

style.setCustom(“#,##0.0000”);

cell.setStyle(style);

worksheet.autoFitColumn(0);

workbook.save(“outCustomnumberstyle2.xlsx”);

If you still find the issue with our latest version/fix, kindly do provide your sample code (runnable) and output file, we will check your issue soon.

Thank you.

[wubble_u]

I should of mentioned that I'm writing it in C#. I don't think setCustom exists in the .net library?

Is this the C# equivalent?

Style s = col.GetStyle();

s.Custom = (

"#,##0.0000");

col.SetStyle(s);

[wubble_u]

Hi Support,

After some more investigation, it looks like it might be a limitation of excel (only going to the 15th significant number) which is why it was looking wrong for me.

Thanks for your help.

[amjad.sahi]

Hi,

Please see the following parallel sample code in C# using Aspose.Cells for .NET for your reference:

e.g

Sample code:

[C#]

Workbook workbook = new Workbook();

Worksheet worksheet = workbook.Worksheets[0];

Cells cells = worksheet.Cells;

Cell cell = cells[“A1”];

cell.PutValue(123456789.1234);

Style style = cell.GetStyle();

style.Custom = “#,##0.0000”;

cell.SetStyle(style);

worksheet.AutoFitColumn(0);

workbook.Save(“e:\test2\outCustomnumberstyle2.xlsx”);

And for "After some more investigation, it looks like it might be a limitation of excel (only going to the 15th significant number) which is why it was looking wrong for me."

Yes, your understanding is correct.

Thank you.