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
I've attached an example project.
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?
FrameworkFormatStr is the Format applied to the grid column on-screen.
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
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?
Hi Mike,
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,
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.
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
Paul said: A workaround or fix is urgently required.
A workaround or fix is urgently required.
Hi Paul, try using "\£#,##0.00" as a temp fix. Excel will see it as custom but at least it has the correct currency.
Mike Saltzman said: Hi, I have been informed that the bug is fixed internally and the fix will be available in the next service release, which is due at the end of February. Infragistics Product Service Releases Schedule - www.infragistics.com
I have been informed that the bug is fixed internally and the fix will be available in the next service release, which is due at the end of February.
Infragistics Product Service Releases Schedule - www.infragistics.com
Thanks Mike for the update.
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.
Many thanks