How can I speed-up creating excel Excel spreadsheets from C#?

Question

I am currently writing around 200 Excel spreadsheets using Excel 2007 with C# COM Interop.

Unfortunately I need about 4 minutes to write these 200 sheets - each sheet has around 1000 rows with 8- 10 columns.

How can I speed up things?

My code looks like this basically:

var xlApp = new Excel.Application();
xlApp.DisplayAlerts=false;
foreach (x in z) {
   var wb = xlApp.Workbooks.add(XLWBATemplae.xlWBATWorksheet);
   var ws = (Worksheet)wb.Worksheets[1];
   //fill data into sheet
   wb.SaveAs(fileName);
   wbClose();
}

clarification:I am filling every cell individually right now

I will try out some of the suggestions from you and will then accept the best solution.

Without seeing your "fill in data sheet" code (or at least a reasonable facsimile thereof,) it's tough to give a recommendation. — dlev, Jul 05 '11 at 13:11
You are using out-of-process COM, that adds lots of overhead to each individual call. A simple property getter can be as much as 10,000 times slower. You speed that up by minimizing the number of calls or running your code inside Excel as an add-in. — Hans Passant, Jul 05 '11 at 13:17
For manipulating Excel, I withdrew myself from .NET and went back to writing regular COM objects. Overall, there is less "user visible" overhead (UI is more responsive), since calling an in process COM object's method is just a virtual function call. In process .NET uses some marshalling (and you have the .NET runtime overhead), and out of process .NET is just insanely slow. Especially when you use a lot of method calls. If you're writing Excel worksheets, why don't you write them directly ? They are only XML files and it should be fairly easy to look at one by hand. — Alexandre C., Jul 15 '11 at 00:27
This is essentially what EPPlus is doing - thus it is very quick. Looking at the XMLs to handmade them would take more than using the library. — weismat, Jul 15 '11 at 06:34

score 5 · Accepted Answer · answered Jul 05 '11 at 13:10

5

You can get rid of COM Interop altogether, by using EPPlus (here). This library creates Excel files by using the 2007 XML format, not the Excel COM interface, and is a lot quicker as a result.

answered Jul 05 '11 at 13:10

Ioannis Karadimas

7,746
3
35
45

@weismat, EPPlus is fantastic. Don't reinvent the wheel! – bzlm Jul 05 '11 at 13:12
I tried EPPlus and liked the performance and the API. Needed less than an hour to change my program as the API is very similar. I will add exact performance figures tomorrow, but it was small fraction of the old time (something between 8-10 times faster than before). – weismat Jul 05 '11 at 17:48
1

I did the maths now and it was actually 23 times faster. – weismat Jul 06 '11 at 05:30
1

@weismat, you should test [@Daniel's solution](http://stackoverflow.com/questions/6583136/how-can-i-speed-up-creating-excel-excel-spreadsheets-from-c/6583209#6583209) as well and see how many times faster *that* is. :) – bzlm Jul 06 '11 at 08:12
Note that EPPlus is GPL. He claims it is LGPL, but since it is still a derivative work of ExcelPackage, it remains GPL like is predecessor. If you use this in a commercial package, technically your product must be released as open source as well. – Beep beep Jan 28 '15 at 20:06

score 4 · Answer 2 · edited May 23 '17 at 11:53

4

You can speed this up by treating the 1000 rows with 8 to 10 columns as an array and copy your data into this array in one go instead of filling each one of those 8000 to 10000 cells on its own. See here for more info: Write Array to Excel Range

edited May 23 '17 at 11:53

Community

1
1

answered Jul 05 '11 at 13:13

Daniel Hilgarth

171,043
40
335
443

LeftyX · Answer 3 · 2011-07-05T14:33:13.480

1

Have you considered using C# tools such NPOI.
It works very well and it doesn't require you to use COM Interop.
Scott Mitchell wrote about it recently.

edited Jul 05 '11 at 14:33

answered Jul 05 '11 at 13:10

LeftyX

35,328
21
132
193

score 0 · Answer 4 · edited May 23 '17 at 11:45

0

I wouldn't use an Excel app to do this, instead write it directly from c# using a library: Create Excel (.XLS and .XLSX) file from C#

MUCH faster.

Rgds GJ

edited May 23 '17 at 11:45

Community

1
1

answered Jul 05 '11 at 13:15

gjvdkamp

9,929
3
38
46

score 0 · Answer 5 · answered Jul 05 '11 at 13:15

0

Sometimes it's sufficient to save your files as a text file in csv format. It can be opened by Excel like a regular Excel sheet.

answered Jul 05 '11 at 13:15

bpgergo

15,669
5
44
68

How can I speed-up creating excel Excel spreadsheets from C#?

5 Answers5

Linked