CODEWITHSUNDEEP
c#performanceexcelcom-interop
weismat
weismat

Reputation: 7411

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

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.

Upvotes: 4

Views: 8675

Answers (5)

LeftyX
LeftyX

Reputation: 35587

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.

Upvotes: 1

bpgergo
bpgergo

Reputation: 16037

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.

Upvotes: 0

gjvdkamp
gjvdkamp

Reputation: 10516

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

Upvotes: 0

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174309

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

Upvotes: 4

Ioannis Karadimas
Ioannis Karadimas

Reputation: 7896

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.

Upvotes: 5

Related Questions