excel export Memory exception

Question

Hi, I need to export huge amount of data from ado.net datatable(which i get by db query) to excel.

I tried the following way : 1. Create excel object with workbook/worksheet @ server side...and use memory stream to write whole document to client side.

But this gave me "out of Memory exception". bcoz my memory stream was was so huge.

So I replaced this with a new way - as follows :

Writing each row from datatable as a coma seperated string to client side. So, as and when we get each row ...we can write to client side ..no memory is used.

But by this way we can write to csv file...not to excel...

Does anybody know how to handle this situation. Can I use silverlight to get data row by row from server, pass it to client side...build excel at client side.?

Answer 1

You should create it on the server, then copy it to the client in chunks.

For an example, see this answer.

Answer 2

If this is for XL 2007, then the workbook is basically in an OPEN XML file format.

If you can format the data in your datatable to conform to the OPEN XML, you can save the file and then just download the entire file.

Read up on OPEN XML at http://msdn.microsoft.com/en-us/library/aa338205.aspx