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
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
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.
Hi Nathan,
redox said:I only posted the two excel files to demonstrate the different results using different versions of the Infragistics controls.
Yes, I get that. :)
I looked at the files and I can see that in one there is no Format applied to the cells and in one there is. So clearly the format is not being written to the file (or not being written correctly). But without a sample, that doesn't tell us why it's happening.
redox said:I've tried your project and it still provides the incorrect format. :( I've also installed the latest Infragistics service pack and no change.
Okay, so that seems to indicate that it's some other factor, like something on the machine.
redox said:It's not a regional setting as our client machines would have to have changed at the same time, plus why would it work fine with an earlier version of the control set on the same machine? I did change my regional settings to the US as a test but it made no difference.
The version issue doesn't necessarily rule out a problem with regional settings. There could be an issue where the new version is comparing the format to the current machine settings and then not exporting the format if it's the same. Of course, since you are saying that the problem occurs with two different regional settings, that's probably not related.
redox said:Your project was with VS2010, have you ran the same in VS2013?
I don't have VS2013 to test with, but I seriously doubt that's the issue. The version of Visual Studio should not make a difference, only the framework matters. My samples is using CLR4, and it will use CLR4 no matter what version of VS you are running. But I will ask around and see if someone in Developer Support has a machine with VS2013 they can test on, just to be sure.
In the mean time, let's try another test. I am attaching another sample that just creates a Workbook and exports to Excel without using the WinGrid or the ExcelExporter. If this one works, then we know something in the Exporter is the problem if it does not, then we know for sure the problem is specific to the machine.
I only posted the two excel files to demonstrate the different results using different versions of the Infragistics controls.
I've tried your project and it still provides the incorrect format. :( I've also installed the latest Infragistics service pack and no change.
It's not a regional setting as our client machines would have to have changed at the same time, plus why would it work fine with an earlier version of the control set on the same machine? I did change my regional settings to the US as a test but it made no difference.
Your project was with VS2010, have you ran the same in VS2013?
I've attached my VS2013 example project that is giving me the issue. Perhaps running the exe file in the debug bin directory before you load the project may simulate the issue (clutching at straws!).
Regards,
The attachment you posted here just contains the two Excel files. I can see that one has a format applied to the cells and the other does not. But that doesn't help us determine the cause of the issue, since there's no way to tell what went wrong during the creation of the files.
I tried this out with a small sample project of my own and it seems to be working fine for me. I have attached my sample here so you can run it and see if you get the same results. If my sample doesn't work on your machine (the "double 1" column does not show the correct format, then it means there's something wrong with the assemblies on your machine. Maybe you just need to the latest service release.
If my sample does work on your machine, then it means there is something in your project that is causing this.
I'm wondering if maybe this has something to do with the regional settings on the machine itself. Are you running this test on a machine where the currency format is already the same as the one you are setting in code?
I've just reverted my example project to Infragistics v2011.1 and this time the exporter formatted the currency correctly.
I've attached the samples:
test.xls was created with Visual Studio 2010 and Infragistics v2011.1
text2.xls was created with Visual Studio 2013 and Infragistics v2013.2