Hi!

Thanks for your reply, but formatString cannon decide this problem. When I use string "#,##0.00" to format number on a cell and Windows has Locale settings with using comma as separator of whole and fractional parts of number then I recieve result like 123456789,010,00 from number 123456789,01 (see attachment). Also Excel replaces a point with a comma in this format string.

In older time previous developer had used string '#,##0.00_-;-#,##0.00;_-"-"' to format numbers on cells. I knew that empty cells must be formatted as "-" (crossed out section). Some time no one was complaining. But once we has recieved result with two commas and without any other separators. In place of comma in this string I had tried to use space. But Excel set space as escaped like '#\ ##0.00_-;-#\ ##0.00;_-"-"' and I don't knew how this change can influence. Also Excel does not separate millions from thousands etc. As it's possible to use billions and more in my case I has used very long format string '### ### ### ### ### ### ### ##0,00_-;-### ### ### ### ### ### ### ##0,00;_-"-"' (Excel shows it as '###\ ###\ ###\ ###\ ###\ ###\ ###\ ##0,00_-;-###\ ###\ ###\ ###\ ###\ ###\ ###\ ##0,00;_-"-"'). In case with empty cells I does not have any problems. But with numbers... Excel adds all spaces to cell before number. For example number '-123' turns to '-       123'. Actually any cell stores number without spaces, but in cases of preview or print any number contains all spaces used in format string. Only the checkbox "Use separator" does not create any from these problems((((

Hello Gornak,

Thank you for your feedback.

When you set the format string you will need to set the correct decimal and group separator depending on the regional settings of the user machine. To get these values you can use code like this:

// get the number group separator and decimal separator

// for the current culture

var numericFormatInfo = Thread.CurrentThread.CurrentCulture.NumberFormat;

var groupSeparator = numericFormatInfo.NumberGroupSeparator;

var decimalSeparator = numericFormatInfo.NumberDecimalSeparator;

Then you can set the format string like this:

// set the format string of the column containing the cells and make it wider

var customFormatString = "#{0}##0{1}00_-;-#{0}##0{1}00_-;_-\"-\"_-";

ws.Columns[firstCell.ColumnIndex].CellFormat.FormatString = string.Format(customFormatString, groupSeparator, decimalSeparator);

Please check the attached sample project where I have implement this approach and let me know if you need any additional information.

Thank you for using Infragistics Controls.