building excel file gives memory out of exception

[anishmohan]

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.?

[Ramesh S]

Convert the DataTable into a two dimensional array and the pass the array to the Excel COM object.

The Excel object modal accepts arrays and writes the content into Excel worksheet.

Try the following link.

DataTable to Excel With Bulk Insert

[anishmohan]

Convert the DataTable into a two dimensional array and the pass the array to the Excel COM object.

The Excel object modal accepts arrays and writes the content into Excel worksheet.

Try the following link.

DataTable to Excel With Bulk Insert

-----------------------------------------------
The issue here is data is soo huge to build an excel @ server side.

[Ramesh S]

The solution given in the article is to insert bulk/huge data into Excel using Array List.

[anishmohan]

Even if am using array list....it is @ srever side and this will consume same amnt of huge memory...and will give me memory out exception....

I need something which pass back each row to client side...and in client side we can build an excel sheet

[Ramesh S]

You cannot build Excel document at client side using C#.

You can use ActiveX object in JavaScript to build Excel document which will work in IE only.

Using ActiveX object in JavaScript is not a good solution and the code is complex and debugging is not possible.

If you try to insert a huge DataTable directyly to Excel using a for loop, it will take more time and even throw memory exception. But if you convert huge DataTable into ArrayList and then write the ArrayList to Excel, it will reduce a lot of time. I have tried this approach and succeeded.