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.
Hello Mike,Is this problem already solved because i created a ticket yesterday with the same issue but with the newest version of infragistics:http://es.infragistics.com/community/forums/p/91506/452251.aspx#452251The format string is still changed even if i change local culture settings.Thanks in advantage.
I am not sure what the problem is with the format string. I will ask Michael to look into this and see if it is the same issue reported above. As for parsing the formula, the argument separator is different depending on what the decimal separator is for the current culture. If you would like to always use a comma as the argument separator, specify the invariant culture when parsing the formula:
Formula formula = Formula.Parse( "=HYPERLINK(\"" + url + "\", \"" + item.FoundString + "\")", CellReferenceMode.A1, CultureInfo.InvariantCulture);
formula.ApplyTo(cell);
Hello,
i have similar issue
when i set StringFormat as "#,##0" then when i open excel file in the format dialog I see "# ##0".
another similar issue,
when i create a hyperlink this way: (using "," separator)
.ApplyFormula("=HYPERLINK(\"" + url + "\", \"" + item.FoundString + "\")");
it work on one machine, but generates exception on the other machine with different local settings
but when i create it using ";" separator it works fine o the another machine (using ";" separator):
.ApplyFormula("=HYPERLINK(\"" + url + "\"; \"" + parsedItem.FoundString + "\")");
i see Excel generating depends on local settings, how can i get it working correctly?
regards
Wojtek
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.
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.