Hi,
we need some help with the excel export function of the infragistics ultragrid version 11.2.
Situation:
Our application handles a high amount of data (currently, over 35,000 rows. each row with 20 sub rows) and provides a functionality to export filtered (and non-filtered) data to an excelsheet.
Problem:
The infragistics excel export function needs round-a-bout an hour to export the 700,000 rows (35,000 * 20) to an excelsheet. Even if we try to export only a small amount of rows (place a row filter)!
Technical details:
For each row we intercept the InitializeRow event and set some object properties:
If e.Row.IsFilteredOut Then e.SkipRow = True e.SkipDescendants = True e.Row.Band.ColHeadersVisible = False e.Row.Band.HeaderVisible = False End If
If e.Row.IsFilteredOut Then
e.SkipRow = True
e.SkipDescendants = True
e.Row.Band.ColHeadersVisible = False
e.Row.Band.HeaderVisible = False
End If
We tried to export the rows with and without handling the InitializeRow event, but the result is the same.
Sample:
We add a sample application to this post, which generates a xml file with 10000 main and 20 sub rows. This example shows the simplified way of our export procedure. All other parts of our export functionality have been checked for performance issues and we come to the conclusion that only the excel export function of the ultragrid can be the bottle neck of our application.
Our question is:
Is there an easier/faster way to export filtered grids with the excel exporter? Or we doing something basicly wrong?
Sincerely,
Kai
Hi Kai,
I don't think you are doing anything wrong, I think this is just a normal limitation of what you are trying to do.
When the grid displays on the screen, it's optimized to create only the objects it needs. So in this sample you attached here, the grid will end up creating an UltraGridRow object for every root-level row, but the child rows are not loaded until you expand a parent row.
When you export the grid, it has to export everything, so that means it has to load all of the data into memory. Not only that, but the grid is designed to clone the DisplayLayout. This allows you to alter the layout used for exporting without altering the grid on-screen. But it also means a performance hit up front while the grid creates what is essentially a whole new grid with all of the rows - most of which have not been loaded into the on-screen grid.
This is why InitializeRow fires for every row. It's firing because the rows are being cloned. The filtering is then applied to the cloned rows so they don't export. But they have to be created first before they can be filtered out.
Ultimately, exporting a grid with 700,000 rows is just not practical. Of course, displaying 700,000 rows in a grid on the screen isn't really practical, either. No human user could possible deal with such a large set of data in any meaningful way, even with filtering. So my recommendation would be to reduce the amount of data you are binding to the grid at any one time.
Oh, one other thing I forgot to mention...
I'm pretty sure that the slowest part of this process is the loading of the data. The DataSet class is pretty slow when loading child rows for a Relationship because regardless of how many rows are actually related to any given parent row, the DataSet has to loop through all of the child rows in the entire table every time it needs to determine the child rows for any given parent.
So in your sample your DataSet has a parent table with 10,000 rows in it and a child table with 200,000 rows in it. That means that DataSet is going to have to loop through all 200,000 child rows ten thousand times.
This is what's taking up most of the time, not the exporting process. You would have a similar problem any time you did anything in this application that forced the loading of all of the data. For example, if you called:
Me.UltraGrid1.Rows.ExpandAll(True)
This is one of the disadvantages of using a relational data source like the DataSet class where the parent/child relationship are calculated dynamically.
If you used a data source where the parent/child relationship are not dynamic and the parent always has a direct link to it's children, it would be a lot faster. So, for example, you could use UltraDataSource.
I have attached a sample project here which is a modified version of yours that uses an UltraDataSource instead of a DataSet.
My sample blows up, of course, because Excel simply cannot handle this many rows. That's a limitation of Excel, of course, and there's nothing we can do about that. It's another reason why exporting 700,000 rows isn't practical. But the point of the sample is to demonstrate that it blows up very quickly - it only takes a few seconds instead of the hours it takes to blow up when using the DataSet.
If you chance the filename to an xlsx, instead of xls, so that the the exporter uses Office2010 format, it exports correctly without blowing up. Excel2010 can handle more rows. On my machine, the export worked in just under 60 seconds.
Hi Mike,
thanks for your quick and helpful response! :)
We have discussed your proposed solution and one of our team member came up with the idea to test the excel export with an older Infragistics version (10.2) we have previously used.
The 10.2 example uses the same way as our first example to fill the datagrid (via xml), but as you can see, the filtered export works as fast as with your solution. Only the versions above 10.2 can not handle the large amount of data when we try to export a filtered grid. Thus, we think larger changes have occurred between the versions, which massively slow down the export of a filtered grid.
A switch to an UltraDataSource requires a lot of changes to our sourcecode, therefore we are currently looking for the simplest solution :)
Additional informations:
Currently we run with .net framework 2.0 and infragistics 10.2, but we try to switch over to .net framework 3.5 and the new infragistics excel exporter of version 11.2.In our program we use a dataset which is bounded to the ultragrid via a data- and bindingsource. The generated xml file from above is just an easy example to fill the grid with data.
Sincerely,Kai
I tested the new sample you posted, which uses v10.2 of the Infragistics assemblies and I am not getting any different results with these assemblies. It's still taking far longer than a minute to export (or even to blow up, which is what it should be doing since it's still using xls and xlsx format).
I'm using the latest service release, of course, so just to be thorough, in case you are using an older version without the service releases that I have, I tried going back all the way to v8.1 and I still get the same results.
So if you are getting different results, I don't think it's the version of the Infragistics Assemblies that is causing it.
Like I said... it's not the export process that is taking the time. If it were, then the application would be blowing up as soon as it hit 65,000 rows, which is the limit in Excel.
If this application worked at all, without raising an exception, then it must have been exporting far less than the 2 million plus rows you are exporting here. It could not possibly have worked in any version, because Excel simply doesn't support that many rows in a single worksheet.
I think my previous description was not clear enough. We don't want to export the whole grid. We only want to export a bunch of filtered rows. In our second example (WindowsApplication1, v10.2) the export of filtered rows works really fast (12 rows in 1,xx seconds), but in our first example (ExcelExportTestApplication, v11.2) it tooks hours to export the 12 filtered rows. The export process is the same, only the .net framework and infragistics versions are different.
To reproduce the different processing times between the two versions do the following steps:
1. Open the application2. Filter "Node1_ID" Column with the value "100" (12 rows remain)3. Click the export button
An overfilling of the excelsheet is not possible, because our application already got a method to raise an error when the excel limits are reached. But the whole functionality is worthless until the export under infragistics 11.2 is that slow.
Select Case .FilterIndex
Case 1 'xlsx Select Case Me.IsExportableToExcel(Grid, ExcelVersion.Excel2007, Me.ExportWithHierarchicalIndentation) Case GridExportableToExcel.MaxBandDepthOverflow 'Throw Error Message Return False Case GridExportableToExcel.MaxColumnCountOverflow 'Throw Error Message Return False Case GridExportableToExcel.MaxRowCountOverflow 'Throw Error Message Return False Case GridExportableToExcel.UnspecifiedCheckError 'Throw Error Message Return False Case GridExportableToExcel.Yes If .FileName.EndsWith(".xlsx") = False Then .FileName = .FileName & ".xlsx" End If End Select Case 2 'xls Select Case Me.IsExportableToExcel(Grid, ExcelVersion.Excel2003, Me.ExportWithHierarchicalIndentation) Case GridExportableToExcel.MaxBandDepthOverflow 'Throw Error Message Return False Case GridExportableToExcel.MaxColumnCountOverflow 'Throw Error Message Return False Case GridExportableToExcel.MaxRowCountOverflow 'Throw Error Message Return False Case GridExportableToExcel.UnspecifiedCheckError 'Throw Error Message Return False Case GridExportableToExcel.Yes If .FileName.EndsWith(".xls") = False Then .FileName = .FileName & ".xls" End If End Select End Select
Case 1 'xlsx Select Case Me.IsExportableToExcel(Grid, ExcelVersion.Excel2007, Me.ExportWithHierarchicalIndentation)
Case GridExportableToExcel.MaxBandDepthOverflow
'Throw Error Message
Return False
Case GridExportableToExcel.MaxColumnCountOverflow
Case GridExportableToExcel.MaxRowCountOverflow
Case GridExportableToExcel.UnspecifiedCheckError
Case GridExportableToExcel.Yes
If .FileName.EndsWith(".xlsx") = False Then
.FileName = .FileName & ".xlsx"
End Select
Case 2 'xls
Select Case Me.IsExportableToExcel(Grid, ExcelVersion.Excel2003, Me.ExportWithHierarchicalIndentation)
If .FileName.EndsWith(".xls") = False Then
.FileName = .FileName & ".xls"
The issue has been resolved. I suggest that you upgrade your application to the latest version of NetAdvantage and download and install the latest Service Release. Let me know if that resolves your issues.
Thomas,
This issue has been submitted to development for further review. The reference number for this development item is TFS115020. A support case has also been opened for you and the reference number for the support case is CAS-93962-576WBW.
I will leave this case open and update you with any new information after the review. You can also continue to send updates to this case at any time.
You can view the status of the development issue connected to this case by logging into your account on our website, hovering to the "My Support Activity" dropdown and selecting the "Development Issues" tab.
Sorry, it's a long thread, and I forgot about your mention of filtering in your initial post. I tried your sample again, and I still get the same results in 10.2 - but this is probably because I am using a service release that you are not.
I went back and tried it again in 9.2 and it does work as you say - it exports the grid in under 4 seconds.
I have forwarded this thread over to Infragistics Developer Support and asked them to create a case for you and write this issue up for further investigation. My guess is that whatever changed here was changed due to a necessary bug fix, but we will see what we can do.