CODEWITHSUNDEEP
c#performanceexcel
techExplorer
techExplorer

Reputation: 908

Efficient way to write excel from C#

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.

Upvotes: 1

Views: 6750

Answers (3)

Efran Cobisi
Efran Cobisi

Reputation: 6454

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.

Upvotes: 1

Aaron Anodide
Aaron Anodide

Reputation: 17180

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.

Upvotes: 1

John Fisher
John Fisher

Reputation: 22721

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.

Upvotes: 8

Related Questions