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: http://stackoverflow.com/questions/3840270/fastest-way-to-interface-between-live-unsaved-excel-data-and-c-sharp-objects — Govert, Nov 10 '11 at 07:18
You are writing 50k rows to a file. It ain't going to ever be that fast. — Wyatt Barnett, Nov 10 '11 at 11:56

score 8 · Accepted Answer · answered Nov 10 '11 at 06:17

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 '11 at 06:17

John Fisher

22,355
2
39
64

2

Indeed, using 2-dimentional array with Range is the fastest solution. – Petr Abdulin Nov 10 '11 at 06:26
But when you insert using range then numbers are also inserted as string in excel, which is changing the expected behaviour of output file – techExplorer Nov 10 '11 at 13:40
2

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. – John Fisher Nov 10 '11 at 15:15

score 1 · Answer 2 · edited May 23 '17 at 12:11

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 '17 at 12:11

Community

1
1

answered Nov 10 '11 at 06:18

Aaron Anodide

16,906
15
62
121

+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. – Joe Nov 10 '11 at 06:34

score 1 · Answer 3 · answered Nov 10 '11 at 06:19

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 '11 at 06:19

Efran Cobisi

6,138
22
22

Efficient way to write excel from C#

3 Answers3