I've used code like this: e.Layout.Bands(0).Columns(7).Format = "$#,##0.00"
to format various columns in my grid. All looks great in the grid. However, when I export to Excel, the formatting appears to be lost. How do I preserve it, or recreate it in the exported sheet when I export it.
While on the subject, I have one more question: I expect some of these sheets to have 100 columns and over 400,000 rows. Will the grid be able to handle this much data? Will the exporter?
Thanks!
Hello,
What you could do in your case is to use the InitializeColumn event of the UltraGridExcelExporter. The event arguments of this event expose the excel format string, the grid format string and the column itself. So you could check if the column has format string and apply it to the excel format string, please see the attached sample. Please note that some of the format strings in C# are different than those in excel. For example the standard numeric strings in C# (http://msdn.microsoft.com/en-us/library/dwhawy9k(v=vs.110).aspx ) are not the same as in Excel.
As for whether the grid and the excel exporter will handle this amount of data, the grid is designed to handle very large amounts of rows so it shouldn’t have any issues. You should read the Performance guide, if you haven’t already. You can find it at the following link:
http://ko.infragistics.com/community/forums/t/15306.aspx
The excel exporter will need some time to export a grid with so many columns and rows. I strongly recommend with such amount of data to use the ExportAsync() method of the exporter instead of the Export method, because it will provide an activity indicator while exporting the asynchronously, and your UI is not going to “freeze”:
http://help.infragistics.com/Doc/WinForms/2013.2/CLR4.0/?page=Infragistics4.Win.UltraWinGrid.ExcelExport.v13.2~Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter~ExportAsync.html
Please let me know if you have any additional questions.
Thanks for the reply. Was dragged into another project and didn't sett this until today. Are there any VB.NET code samples for using intializecolumn?
As to Export vs. ExportAsync, this is good to know! I probably won't need it for this project, since this will be an automated scheduled task that runs every night, but it will surely come in handy in the future.
Thanks.
To be a little more specific, I am looking for some VB.NET code that explicitly shows how to use the initializecolumn event to format columns I'm exporting to Excel as currency, number, text, percentage etc.
And are other properties of the sheet exposed to the exporter. For example, using the exporter, can I sort the sheet? Can I add subtotals? And can I freeze panes, the first row, for example?
I am just checking about the progress of this issue. Let me know if you need my further assistance on this issue.
Thank you for using Infragistics Components.
Hi,
The reason the grid doesn't automatically copy the format into Excel is that the Excel formats and DotNet formats are not the same. But they are very similar. So a lot of the time, you can get away with simply copying one to the other, like you re doing in your Else clause.
As far as documentation goes, I would think the best place to do that would be Excel's help and documentation. It's certainly not something that is including in the Infragistics documentation.
OK, I found this code:
Select Case e.FrameworkFormatStr Case "##.##" e.ExcelFormatStr = "[<>0]##.##;[=0]\" \ ";" Case "hh:mm:ss" e.ExcelFormatStr = "m.d.yy h:mm;@" Case Else e.ExcelFormatStr = e.FrameworkFormatStr End Select
My only question is, where can I find documentation on these cryptic Excel Formatting strings?