Nathan,

I ran your sample application and it worked fine for me.  It looks like you are doing everything you need to do to get the currency symbol that you want.  You are handling the InitializeColumn event of the ExcelExporter and then setting the ExcelFormatStr property of the Column.  I have attached the output file that I generated with your application.  Is there some setting on your machine that is causing the Excel file to default to United States currency?

Hi Mike,

Mike Saltzman said:

I'm confused by this. You are saying that when you run this code, which uses Interop, the results you get are that both cells are formatted using the Pound ("£") and not the Dollar ("$") sign. Is that correct?

Yes.  The interop code will produce “£” for both values.  However Excel only sees the first one as a Currency format, the second it sees as a custom format.

 If I want to format it with a “$” the formatting would need to use "[$$-409]#,##0.00".  Excel would see this as a currency format too.  I mean that when you select a cell in Excel the ribbon shows the current format of that cell i.e.Currency.

When you record a macro in Excel these are the formatting values it uses to give the results.  The interop results and the Excel macro results are identical.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 27/01/2014 by VMOFFICE2003
'
'
 Range("B2").Select
 ActiveCell.FormulaR1C1 = "100"
 Range("B2").Select
 Selection.NumberFormat = "$#,##0.00"
 Range("B3").Select
 ActiveCell.FormulaR1C1 = "200"
 Range("B3").Select
 Selection.NumberFormat = "£#,##0.00"
 Range("B4").Select
 ActiveCell.FormulaR1C1 = "300"
 Range("B4").Select
 Selection.NumberFormat = "[$$-409]#,##0.00"
 
End Sub

I’ve tried this on 5 different machines, two of them being client machines (configured by different IT providers).   All UK configured.  The excel versions were 2003, 2010, 2013.  All gave the same result.

Interop Results:

Mike Saltzman said:

And it further implies that those are not the results you are getting when you use the Infragistics Excel engine. But that would be correct. If you explicitly set a format (as I have done in my sample), then the format should be honored. If Interop is not honoring it, then that seems like a problem with the Interop behavior.

When I use the Infragistics Engine I get the following:

I was expecting the formatting to be honoured too, but that’s not what I’m experiencing :(

Mike Saltzman said:

The first cell display with a Pound ("£") and the second with a Dollar ("$") sign. That's the behavior I have been getting all along, and it is what I expected. I had someone in Developer Support try it out while running under English (UK) settings and they get exactly the same results.

When you Developer has the UK settings, when he chooses the currency format in the Excel UI (the application) does it default to £.  If he records a macro in Excel setting a cell to the format of Currency and it does use the £, what does the macro say the format is?

None of this makes much sense I must admit.

Kind regards

Nathan

Hi Nathan,

I have investigated your issue and it seems that is caused by the UK settings on your machine. Specifically when the CurrentCulture of the application uses CurrencySymbol different than “$”, when you export to excel that CurrencySymbol is always replaced with “$” - in your case the CurrecncySymbol is "£" and that is why it is beeing replaced with "$". To be able to track this issue I have created a support ticket for you in our internal tracking system with ID: CAS-130259-G3G5B6. So I will update you for the progress of this matter trough the mentioned case.

Excellent stuff, thank you.

Just to support what redox is experiencing, I have found that since upgrading to 13.1.20131.2095 Infragistics4.Documents.Excel.v13.1 when exporting to Excel on any platform that is configured for UK settings, I am now experiencing the same result.

My code (VB.NET VS2010) is using the excelExporter_CellExported event and applying cell format as follows:-

Dim tmCF As IWorksheetCellFormat = e.CurrentWorksheet.Rows(e.CurrentRowIndex).Cells(e.CurrentColumnIndex).CellFormat

tmCF.FormatString = "£#,##0.00;-£#,##0.00;£0.00"

The resultant cells in Excel display as $ Currency amounts and have a "Custom" format as follows: $#,##0.00;-$#,##0.00;$0.00

This code was working fine and exporting as £ prior to the upgrade to the service pack. I have now deployed this version of the assemblies and confirm that the deployed platforms are also experiencing this effect.

A workaround or fix is urgently required.

Many thanks

Paul 

Fantastic, it works a treat.  Thanks you.

Mike,

Great news and the workaround works as well! Thanks for the prompt response, another Happy customer.

Paul