Write large amount of data to Excel using C#, Asp.net

I need to write (using C#, Asp.net 2010) large amount of data (300,000 rows) from DB to Excel开发者_运维技巧 (2010). And it should also create around 20 sheets dynamically. So the final Excel workbook will have 15,000 rows per sheet and 20 sheets. I tried using Open XML (DOM) but it took heaps of time and I gave it up after waiting around 40mins but got no sign of output. Also I tried to follow Brian Jone's post about how to use OpenXmlReader and OpenXmlWriter to copy a worksheet and add rows to it. But I do not know how to add sheets using that methodology.

Any help would greatly be appreciated.

---

I would suggest checking out SpreadsheetGear.

When working with smaller files, the ad-hoc methods of creating spreadsheets is fine but performance and quality suffer when you start using them to create larger files.

I've used SpreadsheetGear in the past and, although you do have to pay for it, found it to be well worth the investment.

---

According to this former post

Library to write an Excel file

EPPlus seems to be an option for you, if the license (GPL) is ok for you.

---

Do you really, really need to write it? You have SSRS which will happily do it for you. (As long as your DB is MS SQL Server).

---

Using interop, I know that we had to turn off the AutoCalculate spreadsheet option - this improved performance of the spreadsheet and putting in values.

Like Juliusz says (+1), SSRS might be the way to go with this since it is so much data. There is a way to get them to go on separate sheets during the export to excel but I forget how we did it.