Microsoft.Office.Interop.Excel really slow

Solution 1:

You are updating individual cells. That's going to be very slow. If you think about it, each time you update a cell, an RPC call will be marshalled to the Excel process.

It will be much faster if you assign your two dimensional array of values to an Excel Range of the same dimensions in a single statement (one cross-process call) instead of your current 1200 x 800 = 960,000 cross-process calls.

Something like:

// Get dimensions of the 2-d array
int rowCount = indexMatrix.GetLength(0);
int columnCount = indexMatrix.GetLength(1);
// Get an Excel Range of the same dimensions
Excel.Range range = (Excel.Range) xlWorkSheet.Cells[1,1];
range = range.get_Resize(rowCount, columnCount);
// Assign the 2-d array to the Excel Range
range.set_Value(Excel.XlRangeValueDataType.xlRangeValueDefault, indexMatrix);

Actually, to be pedantic, there are three cross-process calls in the above code (.Cells, .get_Resize and .set_Value), and there are two calls per iteration in your code (.Cells get and an implicit .set_Value) for a total of 1200 x 800 x 2 = 1,920,000.

Note range.get_Resize and range.set_Value were needed for an old version of the Excel interop library I was using when this post was first authored. These days you can use range.Resize and range.Value as noted in the comment by @The1nk.

Solution 2:

Excel interop is never going to be fast. You're basically remote-controlling an instance of the Excel application. You might have more success by creating a CSV file and then using Excel interop to convert this to a .xls or .xlsx file