Hi,
I'm having a problem with the formatting of currency during the export to excel. I'm using version 13.2 of the controls.
I want the column values to be formatted as "£#,##0.00" however it formats it as "$#,##0.00" when it gets to excel.
I created a simple class
Public Class Data Public Property Price As Double End Class
Public Class Data
Public Property Price As Double
End Class
Then create a list of about 10 numbers using this class, bound the list to the grid and then exported the grid to excel.
On the initializeColumn event I was setting the column format as below.
Private Sub UltraGridExcelExporter1_InitializeColumn(sender As Object, e As Infragistics.Win.UltraWinGrid.ExcelExport.InitializeColumnEventArgs) Handles UltraGridExcelExporter1.InitializeColumn e.ExcelFormatStr = "£#,##0.00" End Sub
Private Sub UltraGridExcelExporter1_InitializeColumn(sender As Object, e As Infragistics.Win.UltraWinGrid.ExcelExport.InitializeColumnEventArgs) Handles UltraGridExcelExporter1.InitializeColumn
e.ExcelFormatStr = "£#,##0.00"
End Sub
When I view the exported spreadsheet the numbers are formatted "$#,##0.00"
If I try it with "€#,##0.00" it will format it using the €. It seems to not like £ any more.
Your help will be greatly appreciated.
kind regards,
Nathan
Excellent stuff, thank you.
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.
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
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:
Format
Result
What Excel thinks the formatting is.
"$#,##0.00"
£
Currency
"£#,##0.00"
Custom
"[$$-409]#,##0.00"
$
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
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?
Okay, I think we are narrowing it down. I'm going to ask Infragistics Developer Support to try running my sample on a machine running English (UK) settings and see if they can duplicate the problem. If they can, they will write it up for developer review and we will look into getting it fixed.
But it seems like you have a workaround for now.