0

Here's the scenario:

  • I have around 400k records in a SQL Server 2008 R2 database that I want to export to an XLSX spreadsheet.
  • The application is an ASP.NET 4.0 web application

I tried using a DataTable with ReportViewer but the w3wp process memory usage skyrockets due to the entire DataTable being read into memory.

I thought that Gembox Spreadsheet would handle that scenario a little better, guessing that I could use a DataReader instead of the DataTable and just write a new row to the Excel workbook sheet and flush it over the HTTP stream. But I can't find that function anywhere in Gembox Spreadsheet. Has anyone achieved anything similar, either with Gembox or any other 3rd party component?

Valerio Santinelli
  • 1,592
  • 2
  • 27
  • 45
  • Would it make sense to spit out 400k records in smaller chunks, lets say 10k. That way you wont run into memory problem. – TheTechGuy Aug 01 '13 at 18:45
  • It makes sense but that's not doable with ReportViewer as I'd have to push everything into a DataTable in the end, which would defeat the implementation you suggested. The same applies to Gembox. – Valerio Santinelli Aug 01 '13 at 19:02

1 Answers1

0

I am only guessing it here but you should be able to do it using SQLDataReader. SQL DataReader is different than SQL DataSet. The former retrieves one record at a time and it required live connection to DB. It should not create memory problem, I think. The later retrieves the whole table at once which can sky rocket the memory usage. Here is a good article from Microsoft, difference between datareader and dataset

Also note that Excel has limitation as well. Excel 2007 is pretty good by the way, can handle 1000k x 16k records. Older versions of Excel are definitely more limited. Excel 2007 Limitation

I am also thinking if Excel can handle such a large file , your program should handle 400k records. I know that is a lot of data but Operating System usually takes care of memory management unless you are doing something that is just plain wrong.

TheTechGuy
  • 16,560
  • 16
  • 115
  • 136