c# Microsoft.Office.Interop.Excel export

Question

I'm writing a program in which I'm using C# language, DataSet, etc. I have about 200 000 values what I want to export to an .xlsx document.

My code:

using Excel = Microsoft.Office.Interop.Excel;
...
Excel.Application excelApp = new Excel.Application();
Excel.Workbook excelworkbook = excelApp.Workbooks.Open(/location/);
Excel._Worksheet excelworkSheet = (Excel.Worksheet)excelApp.ActiveSheet;
...
excelApp.visible = true;
...
for (int i = 0; i < /value/; i++)
 for (int j = 0; j < /value/; j++) 
  excelworkSheet.Cells[i, j] = /value/;

It works well, but it is too slow (at least 5-10 minutes).

Have you got any advice?

Answer 1

For Excel, I only programmed VBA so I cannot give you the exact syntax on how to do it in C#.

What I notice though is that you are doing something that I have noticed many people are tempted to:
Writing code to each cell in Excel separately. Read / Write operations are rather slow in comparison to operations performed in memory.

It would be more interesting to pass an array of data to a function that writes all of these data to a defined range in one step. Before doing so, of course you need to set the dimensions of the range correctly (equal to the size of the array).

However, when doing so, performance should be increased.

Answer 2

I just took the same performance hit, wrote this to benchmark:

[Test]
public void WriteSpeedTest()
{
    var excelApp = new Application();
    var workbook = excelApp.Workbooks.Add();
    var sheet = (Worksheet)workbook.Worksheets[1];
    int n = 1000;
    var stopwatch = Stopwatch.StartNew();
    SeparateWrites(sheet, n);
    Console.WriteLine("SeparateWrites(sheet, " + n + "); took: " + stopwatch.ElapsedMilliseconds + " ms");

    stopwatch.Restart();
    BatchWrite(sheet, n);
    Console.WriteLine("BatchWrite(sheet, " + n + "); took: " + stopwatch.ElapsedMilliseconds + " ms");

    workbook.SaveAs(Path.Combine(@"C:\TEMP", "Test"));
    workbook.Close(false);
    Marshal.FinalReleaseComObject(excelApp);
}

private static void BatchWrite(Worksheet sheet, int n)
{
    string[,] strings = new string[n, 1];
    var array = Enumerable.Range(1, n).ToArray();
    for (var index = 0; index < array.Length; index++)
    {
        strings[index, 0] = array[index].ToString();
    }

    sheet.Range["B1", "B" + n].set_Value(null, strings);
}

private static void SeparateWrites(Worksheet sheet, int n)
{
    for (int i = 1; i <= n; i++)
    {
        sheet.Cells[i, 1].Value = i.ToString();
    }
}

Results:

                            n = 100   n = 1 000   n = 10 000    
SeparateWrites(sheet, n);   180 ms    1125 ms     10972 ms
BatchWrite(sheet, n);       3 ms      4 ms        14 ms