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,
Thank you for your post. I have been looking into it and I have managed to achieve the functionality that you would like by handling the ‘CellExporting’ event of the DataPresenterExcelExporter and in its body I set the format of the currently exporting cell to the format you want if it belongs to the decimal field. Please let me know if this helps you or you have further questions on this matter.
Looking forward for your reply.
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);
Thank you for your feedback. I am glad that you resolved your issue and I believe that other community members may benefit from this as well.
Thanks again.
Your article is usually rich in really useful details about Rule Tag 3 along with mini. I am going to you should definitely revisit take a look at your own articles with regard to our next report.