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,

I hope you had a good weekend.  Thanks for persevering with this.

I’ve tried your code but it still doesn’t format it correctly.

I tried again bypassing the Infragistics libraries using the following code and it formatted both cells with the £.

Dim ex As New Microsoft.Office.Interop.Excel.Application
        ex.Visible = True
        Dim ws As Excel.Worksheet
        ex.Workbooks.Add()
        ws = ex.ActiveSheet
        Dim r As Excel.Range
        r = ws.Range("A1")
        r.NumberFormat = "$#,##0.00"
        r.FormulaR1C1 = 100
        r = ws.Range("A2")
        r.NumberFormat = "£#,##0.00"
        r.FormulaR1C1 = 200

I ran a macro on Excel and set the cell to format style currency.  It displayed as £.  The macro code showed setting the number format to $#,##0.00 will set format it with £.

If I change the Example project and format the cell as “"\£#,##0.00"” then it will work.

Something is different between using the Infragistics libraries and communicating to Excel direct.  Whatever it is seems to be getting wrapped up in a deployment as the client gets the same issue.  I’ve tried installing the test app on a clean test machine and it had the same issue.  I’ve added the installer files for you to try if you can. It adds a shortcut to the desktop.

Have you tried testing this with your settings set to UK?

Again, thanks for your assistance.  It's much appreciated.

Nathan

Hi Nathan,

I am starting to wonder if maybe there is a misunderstanding here in terms of what the expected Excel file should look like.

redox said:

I tried again bypassing the Infragistics libraries using the following code and it formatted both cells with the £.

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?

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.

Just to further clarify, when I run my sample (under American English settings), the resulting Excel file displays as follows:

£123.45
$123.45

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.

So are are you saying you are getting the same results, but you believe those results are wrong?

Or are you getting different results?

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.

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

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