In my code I use (v11.2)
worksheet.Rows(rowIndex).Cells(columnIndex).CellFormat.FormatString = "# ##0,00"
But when open the resulting file in Excel the cell is not formatted properly and when I look at the cell properties in Excel in the Format Cells dialog I see the following : "#\ ## 000"
What should I use as the format string to get the correct "# ##0,00" also in Excel?
This looks like it might be a bug. You should be able to use this format string. I have forwarded this post to the Developer Support Manager and a DS engineer will be contacting you about this issue.
Jiri,
Are you sure that your keyboard is set to a Finnish (or European) keyboard when you type in that format string? I tried this myself and I was able to get the formatting the way you want it in the Excel file. However, I did not use the Worksheet. I handled the InitializeColumn event of the UltraGridExcelExporter and set the format string like so:
if (e.Column.Index == 1) { e.ExcelFormatStr = "### ### ##0.00"; }
But I found that the format string must be entered using a European keyboard, otherwise I did not get the decimal values.
So the numbers appear like this: 5 280,00
which I think is what you want.
I should also mention that it is also necessary to make sure that the Regional and Language settings on the machine where you are producing the Excel file are set to Finland or whatever country whose formats you want to use.
When I set my locale to US English I can get the rounding right using the format string "# ##0.00", but I loose the thousands separator. It also turns out that the decimal separator (dot or comma) in the Excel format string depends on the locale, i.e. if the locale says comma and the format string has a dot, then the format string doesn't work.
The behavior I would expect is that the string I set as the format string for the cell becomes 1:1 the format string for the cell in Excel. After that it would be my responsibility to use the right thousands and decimal separators when I create the format string by looking at the current locale of the users computer. I would have to accept that if the resulting Excel file is opened on a computer with a different locale, then the format string might not work, but I can live with that.
Currently I really can't use the format string because the results on the client's computer are too unpredictable because I have no control over that setup.
I have subbmitted this issue to our development team for further review. You will receive more information regarding this item through support case CAS-86377-GMNJ4Y.