CODEWITHSUNDEEP
c#asp.netexcel
GayanSanjeewa
GayanSanjeewa

Reputation: 366

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.

Upvotes: 3

Views: 3389

Answers (4)

Decker97
Decker97

Reputation: 1653

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.

Upvotes: 0

Juliusz
Juliusz

Reputation: 2105

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

Upvotes: 1

Doc Brown
Doc Brown

Reputation: 20054

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.

Upvotes: 2

Justin Niessner
Justin Niessner

Reputation: 245509

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.

Upvotes: 3

Related Questions