how to bulk export data to excel

Question

I have table having around 80 to 90 columns and it has data around 800000 to 900000, i need to export data around 400000 to excel using asp.net and c#.

I have tried to export data from dataset using Open-XML DOM method, but it will hang out the system and got Memory Out of Bound error at some point.

can any other method which will export data from sqlserver to Microsoft excel 2007/2010.

Answer 1

You could use standart and quickest export tool bcp.exe:
http://www.excel-sql-server.com/sql-server-export-to-excel-using-bcp-sqlcmd-csv.htm

Answer 2

The method suggested by TomTom would be the best when ur dealing with such bulky data.. but in case if u want the exported file to be an xls or xlsx, then u can trying considering the Excel file as a DataSource and use OleDb to connect to the data source(Excel file in this case). And use the insert queries to insert rows in to the excel file.

You can also try using Microsoft.Office.Interop.Excel to write to excel files.. But am still not sure how much time it would consume to deal with such heavy data..

Answer 3

The DOM makes it easy because everything is strongly typed. Unfortunately, the DOM approach requires loading entire Open XML parts in memory, which can result in Out of Memory exceptions.

You can also use the SAX-like writing capability as explained in the following article by Brian Jones:

Writing Large Excel Files with the Open XML SDK

Answer 4

You are aware that excel can openm CSV (Comma Separated Value) files without problems?

That is about the best - and thinnest - way I Can see that handled.

And do not put them into a dataset. Use a reader and write them out to the response stream as you read them. No need to materialize them in memory.