Hi,
I am using excel 2003.I want to export ultrawin grid records to multi[ple tab sheets in the same excel if the maximum limit in a single tab is reached i.e if the records count is more than 65536 rows then export rest of the records to another tab sheet in the same excel.
Please suggest a solution.
There's no easy way to do this, but if you have the latest version of the grid, then it should be possible with a bit of clever coding.
There's a property on the UltraGridExcepExporter called FileLimitBehavior. The default option is to throw an exception, which you don't want. So you would set this to TruncateData. This means that the export will export as many rows as it can to fill up a single worksheet.
So you could handle the RowExported event and keep track of the last row that was actually exporter. Then... if the data was truncated, you could export the grid to a second worksheet. This time you would have to handle InitializeRow on the ExcelExport and skip each row until you get to the last one that was exported the first time through.
How do we know when the data is truncated? so that we can call export one more time.
Is there any event which we can register for indicating that data is truncated and export is stopped or are there more rows to be exported.
Thanks & Regards,
Prasad
Hi Prasad,
There's no event, but Excel has a fixed limit to the number of rows in a single worksheet sheet. I think it's something like 65,000 - but I think the error message in the exporter will give you the exact number if you want it.
Frankly, I can't see the point in exporting such a huge amount of data to Excel, anyway. No human user could possible deal with such a large set of data either in the grid or in Excel.
Also, I would think that if you are planning to break it up into multiple sheets, you would probably do it based on some sort of logical grouping, rather than just the number of rows. But I guess you still need to know the maximum.
There's no easy way to call the export method and tell it to export the grid starting at a particular grid row. It always starts from the top of the grid. Theoretically, you could hide the rows, but I don't know if this would be very efficient, and if your data is in a hierarchy, it could be very tricky.
What I would do is use an event like HeaderExporter or RowExporting and examine the CurrentRowIndex property on the event. This will tell you want row you are on in the excel sheet. If you get somewhere close to the limit, say 60,000, you could add a new Worksheet and set the CurrentRowIndex to 0. The next item exported will then be placed at the top of the new sheet.
Yes, My plan is to break it up into multiple sheets. But we do have that much huge set of data to be exported.
So, If there is no event to identify when the export is stopped or when the limit has reached, can we set the file limit behavior to throw an exception?
so that on exception I can call export method once again (by passing already exported rows). Will it retain the data in the sheet till the point of exception or will it be lost ? If not retained, the only possibility is to logically break the data into chunks of 65K rows and export as you suggested.
Regards,