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
FrameworkFormatStr is the Format applied to the grid column on-screen.
I've created an Excel spreadsheet manually and set the formatting and it works so there's something effecting the ExcelExporter.
The following code produced a spreadsheet that had the 100 formatted as currency and the 200 formatted as custom. Both appeared with the £ sign.
Dim ex As New Microsoft.Office.Interop.Excel.Application ex.Visible = True Dim ws As Worksheet ex.Workbooks.Add() ws = ex.ActiveSheet Dim r As Range r = ws.Range("A1") r.NumberFormat = "$#,##0.00" r.FormulaR1C1 = 100 r = ws.Range("A2") r.NumberFormat = "£#,##0.00" r.FormulaR1C1 = 200
Dim ex As New Microsoft.Office.Interop.Excel.Application ex.Visible = True
Dim ws As Worksheet
ex.Workbooks.Add()
ws = ex.ActiveSheet
Dim r As Range r = ws.Range("A1") r.NumberFormat = "$#,##0.00" r.FormulaR1C1 = 100
r = ws.Range("A2") r.NumberFormat = "£#,##0.00" r.FormulaR1C1 = 200
Out of curiosity where does the FrameworkFormatStr get it's value from? Mine returns "$#,##0.00".
Hi Michael, I was afraid of that.
We used the same method as used in the example application in an application that has just gone live with a client. We were getting the £ currency as was the client but now our dev machines and our client have the same issue.
I can't think of any setting on my dev machine that would effect the deployment of the application. The only thing that's changed since it was working was upgrading the Infragistics controls from 2011.1 and installing VS 2013.
Your excel file shows the formatting as Currency, yet when I run the example application it creates a custom format.
Excel functions normally when it's being used through the UI, i.e. selecting currency format will display the £.
I'm at a loss to be honest :(
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?
I've attached an example project.