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.