Format Cell Number Issue

[Evelyn]

I am trying very hard to format a cell number, but it is not working as expected.

 

In my country we use the numbers like this: 1.000,00. The 'comma' to represent the decimal and the 'dot' to represent 'thousand' point.

What I want:  The number value to look like this: "0,824".

What I get: the number value looking like this: "823.723.228.995.058,00". (PS: I get this value from a database).

That's really weird.

 

I am setting the format like this: 

worksheet.Cells[row, column].SetFormat(new CellValueFormat("#.##0,00"));

 

I have already tried a lot of formats, but none of them seems to work as I expect. I aways get the same result.

 

Is there any other CellValueFormat that I am missing here?

 

Thank you

[Evelyn]

@Edit

Here's how exactly I am doing:

worksheet.Cells[0, 0].SetValue("=100*A2/A3");
worksheet.Cells[0, 0].SetFormat(new CellValueFormat("#.##0,000"));

worksheet.Cells[1, 0].SetValue(1000);
worksheet.Cells[1, 0].SetFormat(new CellValueFormat("#.##0,00"));

worksheet.Cells[2, 0].SetValue(121400.00);
worksheet.Cells[2, 0].SetFormat(new CellValueFormat("#.##0,00"));

Doing like this, that weird number appears, when I Set the cell value to 0.824, like this: 

worksheet.Cells[0, 0].SetValue(0.824);

 

it seems to work just fine.
I have already tried a lot of formats, but none of them seems to work as I expect. I aways get the same result.

I also tried to create a NumberCellValue and I did the same thing, but again, nothing changed.

[Tanya]

Hello Evelyn,

As far I understand your scenario, you are dividing the number that you have from the database and need to format it with three digits after the comma. Am I right? If so, you could use the "0,00#" format string.

Please, have in mind that the separators in a number are different for the different cultures and the result you are getting could be affected by the current culture of the application/machine.

Hope this helps.

Regards,
Tanya
Telerik

[Evelyn]

Hello Tanya,

Thank you for your reply. I tried to use the "0,00#" format stringm but I got the result: "823723228995058,00".
The most important thing now is to make it formatted like this "0.824" or "10.589" or "8.548"... I will worry about the separators later!

Without setting a format, I got the result like this: 8,23723E+14.

Let me know if you find anything. Thank you!

[Tanya]

Hi Evelyn,

The "823723228995058,00" number is too big and in order to visualize it in the desired way, it should be divided. The number format could change the way a number is visualized, but not its actual value. I was able to show the number like "0.824" after dividing the original value by 1000000000000000 and applying the "0.00#" format.

Please, note that in order to build the proper format string, you will need to take into account the current culture.

Regards,
Tanya
Telerik