Interop Excel is slow

c#, excel, excel-interop

Solution

This answer is only about the second part of your question. Your are using lots of ranges there which is not as intended and indeed very slow.

First read the complete range and then iterate over the result like so:

var xx[,] = (MySheet.Cells["A1", "XX100"] as Excel.Range).Value2;
for (int i=0;i<xx.getLength(0);i++)
{
    for (int j=0;j<xx.getLength(1);j++)
    {
         Console.WriteLine(xx[i,j].toString());
    }
}

This will be much faster!

Problem

I am writing an application to open an Excel sheet and read it ``` MyApp = new Excel.Application(); MyBook = MyApp.Workbooks.Open(filename); MySheet = (Excel.Worksheet)MyBook.Sheets[1]; // Explict cast is not required here lastRow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row; MyApp.Visible = false; ``` It takes about 6-7 seconds for this to take place, is this normal with interop Excel? Also is there a quicker way to Read an Excel than this? ``` string[] xx = new string[lastRow]; for (int index = 1; index <= lastRow; index++) { int maxCol = endCol - startCol; for (int j = 1; j <= maxCol; j++) { try { xx[index - 1] += (MySheet.Cells[index, j] as Excel.Range).Value2.ToString(); } catch { } if (j != maxCol) xx[index - 1] += "|"; } } MyApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(MySheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(MyBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(MyApp); ```

Original source