A while back, Duane wrote an article in this forum detailing how to do an ajax postback that resulted in the Excel document downloading. It was quite good and pretty deep, but I never got a chance to implement it.
Now I am back to try it, and I can't find the article. It looks like somebody cleaned out the forums, deleting some good content in the process.
Can somebody please retrieve it?
And can somebody please give your developers someplace they can post this kind of stuff without worrying about a moderator deleting it?
While the article doesn't appear to be publicly visible (can't find it through search or by browsing the forum), it is available by direct link.
Here is the link, if it's helpful to anyone else.
http://forums.infragistics.com/forums/t/55904.aspx
Thanks Duane! Keep it coming!
Wow, this is doubly cool... it solves another issue I have been having as well, trying to export large datasets with the WebexcelExporter.
Right now, there is no such statement as WebExcelExporter1.Export(myworksheet).... or WebExcelExporter1.Export(myDataSet)... or WebExcelExporter1.Export(mySqlDataReader)... (feature requests there, hint hint).
This article shows how to take the Worksheet object directly to the response stream without the WebExcelExporter.
A person could modify this code to accept a simple sqldatareader, minimizing memory requirements for exporting large datasets. Speaking of large datasets, I bet this is faster than WebExcelExporter too.
Hello Rob Hudson,
Thanks for the comments on the thread in regards to the asynchronous excel export. Do you have any questions on the sample provided or need any assistance in regards to WebExcelExporter?
When using the WebExcelExporter can be found for the list of key features here: http://samples.infragistics.com/aspnet/ComponentOverview.aspx?cn=excel-exporter. For example currently the features would be to use the WebDataGrid and WebHierarchicalDataGrid controls.
Let me know if you have any questions with this matter. Thank you.
Hey Duane,
I have clients who want to pull exports of up to 1 million records with about a dozen columns, and the WebExcelExporter chokes on it. Even the method in your article is not going smoothly. The export takes forever, resulting in the page sometimes timing out, hence the need for async... and maintaining a small memory footprint is a real challenge.
I just had a thought worth exploring... The process of building the spreadsheet from a datasource using for-next loops seems like a terrific opportunity for parallel processing. It could improve the speed of the exporter substantially.
I submitted several feature requests this morning that I hope make it into the WebExcelExporter that would make it more suitable for large resultsets.
I am sure that I will eventually come up with a solution that will work, but I doubt it will have the elegance that you guys are capable of. I would really love to see a sample that exports an enormous resultset efficiently in an ASP.Net environment.
If you guys want to take a crack at it, I would sure love to see what you come up with. If not, I understand. In either case, I'll post my final code for the benefit of all.
Thanks, - Rob
Thanks for the update.
As far as exporting 1 million records may be more than what is expected for the machine that is hosting the web server to handle when saving to excel. Also what you are wanting to accomplish would simply be more involved in the actual web server processing than the WebExcelExporter control. In this case if you have a sample that shows efficient exporting for 1 million records using .NET code then I can submit a feature request on this in particular. This will help as far as a proof of concept and automation on the NetAdvantage product.
I don't agree that exporting large datasets is outside the ability of a web server.
I put together a simple little app that generates large recordsets, and then tests the time it takes to export that recordset using various methods. Have a look at this:
For a recordset of 50,000 rows, the WebExcelExporter took over 4 minutes! That is really too long, regardless of what environment you are in. All of the other methods returned a memory stream in a matter of seconds.
The result that was really stunning though was the BIFF format. This is probably the fastest possible method to export data to Excel, being that it is direct-to-binary. It shows just how "within the scope of a web server" the export of these files can be. I was startled to click the button and see the worksheet pop up almost instantly. Unfortunately, the BIFF format is limited to 65k records per spreadsheet.
It would be interesting to do a solution where you could output large recordsets to BIFF, and have it wrap onto new spreadsheets when it reaches the row limit of one spreadsheet. There's your proof of concept that it is possible to export very large numbers of records at crazy-fast speeds. By my calculation, BIFF could export 1 million rows in 15 seconds, with a super-small memory footprint to boot.
Regarding Infragistics Excel, I was also pleased to see that with larger resultsets, parallel processing dramatically reduces the time it takes to populate the Worksheet object. This is on my single-CPU workstation. Time was cut from 59 seconds to 35 seconds.
However, SpreadsheetGear has you beat soundly (and also proves the concept). Note that the SpreadsheetGear solution was able to generate a worksheet with 1 million rows and also stream it in under 1 minute. That is acceptable for an async operation in my environment.
Some good news for you guys... With parallel loading, I was able to populate your Worksheet object in a time very close to theirs. However, I was unable to execute Workbook.Save() because of other issues... which brings me to the next topic.
There seems something very wrong with Workbook.Save(). It is extraordinarily expensive, both in terms of memory, and processing time. I can populate 600,000 rows into an IG Worksheet object in 11 seconds, but it takes 74 seconds (and a lot of memory) for Workbook.Save to write to the memorystream. I can't process row counts larger than that without Workbook.Save crashing.
Workbook.Save() is the bottleneck. With that improved, you have a path to a high performance Excel exporter that would compete with SpreadsheetGear.
I tried attaching my project as a .zip file to this post. If it didn't take, let me know, and I will be glad to send it by other means.
The fix for development issue 89817 is now in the latest service release. You can download within My Keys & Downloads page.
Just pinging you guys to see if there has been an update to this issue for the next release.
I am experiencing more pressure from the client to have a solution, and I need to know whether I should wait for Infragistics.
Thanks for the update on the thread.
For the performance improvements on using the Excel library I have logged bug id 89817 on this using the sample code that was provided in this thread to have our engineers look into this. Also private case CAS-72672-JNFCD2 has been created and you can find this case number in the support activity page here: https://es.infragistics.com/Membership/MySupport.aspx.
Understood... Duane, the reason I didn't have handling for the try/catch block is that my intent was to just ignore the error... I was just trying to get a build time.
I understand (while being surprised) about it not being thread safe. I will submit a feature request.
The objects in the Excel library are not thread safe, so you should not be using Parallel.ForEach to populate a Worksheet with data. You can submit a feature request to make these objects thread safe. Until that is implemented, you should be populating the objects synchronously.