3

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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
GayanSanjeewa
  • 366
  • 3
  • 13

4 Answers4

3

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.

Justin Niessner
  • 242,243
  • 40
  • 408
  • 536
2

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.

Community
  • 1
  • 1
Doc Brown
  • 19,739
  • 7
  • 52
  • 88
1

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

Juliusz
  • 2,096
  • 2
  • 27
  • 32
  • yep, I really really need to do it. We use SSRS too but can't use for this unless SSRS has a functionality to do what I want (Like export to excel as the way I want). but I doubt. – GayanSanjeewa Aug 30 '11 at 23:31
  • Maybe it is worh of checking? Definitely you can break the report into spreadsheet. Is there anything else you need? [Maybe it is a time to update your question with the details what exactly you try to achieve?] – Juliusz Aug 31 '11 at 07:51
0

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.

Decker97
  • 1,643
  • 10
  • 11
  • But I dont think we can break in to sheets while doing the export from SSRS. – GayanSanjeewa Aug 30 '11 at 23:32
  • This has been discussed here on SO many times: since this is ASP.NET, the program is probably running on a server, where the use of Interop should be really, really avoided (!!!) Read this http://support.microsoft.com/kb/257757 for more information. – Doc Brown Aug 31 '11 at 06:06