We're exporting a grid of data to Excel as xls using the Infragistics Excel controls. We've got code that converts the grid display format to the equivalent Excel format. In the case of currency values we're setting the format as a custom string like this:
"£#,##0.00;-£#,##0.00"
via code like this:
worksheetCell.CellFormat.FormatString = ConvertToExcelFormat(columnInfo);
Where the method "ConvertToExcelFormat" just looks at the column information and returns the appropriate format string. However, when we open the spreadsheet in Excel the value is now showing with $ as the currency symbol and while the format is still custom, the string is now:
"$#,##0.00;-$#,##0.00"
What have we missed when setting the CellFormat?
I thought that it might have something to do with the culture of the workbook itself so I've tried setting that to en-GB, but that had no effect on the problem.
If I change it to use another currency symbol (the €, for example):
"€#,##0.00;-€#,##0.00"
Then this displays correctly.
It's definitely something to do with the "£" symbol being treated differently somewhere down the line.
Hello Chris,
I am just checking the progress of this issue and was wondering if you managed to achieve your goal or if you need any further assistance on the matter.
I managed to get the £ to appear by escaping the symbol in the string:
string decimalPlaceHolder = decimalSeparator + FunctionToGetZeros(numberOfDecimalPlaces); string result = string.Format("\\{0}#{1}##0{2};-\\{0}#{1}##0{2}", currencySymbol, groupSeparator, decimalPlaceHolder);