Efficient way to write excel from C#

Question

I'm trying to generate excel using c# following is the code snippet for it

Microsoft.Office.Interop.Excel;
.
.
.
foreach (string[] rowContents in lstOutputFileContent)
{
    for(int i = 0; i < rowContents.Length; i++)
    {
        sheet.Cells[currRow, i + 1] = rowContents[i];
    }
}

but the problem is when lstOutputFileContent contains say more than 50K line then its taking too long to write (4-5 mins). Is there a better/faster way to write excel in this scenario i.e. I've list of array of string and I want to write this to excel.

I tried using OleDb but in case where first few lines contain less cells then when I try to insert row with extra cell it was giving error.

Any help will be greatly appreciated.

This question was dealt with in detail here: stackoverflow.com/questions/3840270/… — Govert
– Govert, Commented Nov 10, 2011 at 7:18
You are writing 50k rows to a file. It ain't going to ever be that fast. — Wyatt Barnett
– Wyatt Barnett, Commented Nov 10, 2011 at 11:56

John Fisher · Accepted Answer · 2011-11-10 06:17:04Z

8

If you're using Excel 2007 or higher, the best option is Open XML SDK 2.0

You can also modify the current method. Instead of writing each value individually, create a two-dimensional array to hold all the values. Then get a range of the same size and set the value for the range to be the two-dimensional array. That way you only suffer the cost of one marshalled COM call instead of the many, many you were dealing with.

answered Nov 10, 2011 at 6:17

John Fisher

22.8k2 gold badges43 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

pabdulin Over a year ago

Indeed, using 2-dimentional array with Range is the fastest solution.

techExplorer Over a year ago

But when you insert using range then numbers are also inserted as string in excel, which is changing the expected behaviour of output file

John Fisher Over a year ago

aashutoshsingh: That's not quite true. If you make an array of object, then put strings or numbers into it, the spreadsheet will type the cell to match the object's type.

Community · Accepted Answer · 2017-05-23 12:11:33Z

1

You can insert a row at a time instead of a cell at a time using the code in my answer at Excel C# inputting into specific cell

I got to that when I encountered performance problems I believe similar to yours.

edited May 23, 2017 at 12:11

CommunityBot

11 silver badge

answered Nov 10, 2011 at 6:18

Aaron Anodide

17.2k15 gold badges67 silver badges129 bronze badges

1 Comment

to StackOverflow Over a year ago

+1, This will reduce the number of round trips to Excel from (RowCount * RowSize) to (RowCount), a significant improvement. But if RowCount > 50K, you still have a lot of round trips. You can go further and insert a 2-dimensional array into a Range on one call.

Efran Cobisi · Accepted Answer · 2011-11-10 06:19:11Z

1

For Excel 2002 (?) or higher, you may simply serve your spreadsheet content in HTML format, using a simple table: please see here for a simple example. This will be the fastest option.

answered Nov 10, 2011 at 6:19

Efran Cobisi

6,5141 gold badge24 silver badges23 bronze badges

Collectives™ on Stack Overflow

Efficient way to write excel from C#

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related