Microsoft.Office.Interop.Excel really slow

Question

I am exporting a 1200 X 800 matrix (indexMatrix) to a excel file using the standard Microsoft.Office.Interop.Excel. The app works, just that it is really really really slow( even for the 100 x 100 matrix) . I also export in a text file through a TextWriter an it works almost instantly . Is there any way to export to the excel file faster?

Here is my code :

        Excel.Application xlApp=new Excel.Application();         Excel.Workbook xlWorkBook;         Excel.Worksheet xlWorkSheet;         object misValue = System.Reflection.Missing.Value;          //xlApp = new Excel.ApplicationClass();         xlWorkBook = xlApp.Workbooks.Add(misValue);          xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);         for (int i = 0; i < 800; i++)   //h             for (int j = 0; j < 1200; j++)                 xlWorkSheet.Cells[i+1,j+1] =indexMatrix[i][j];           xlWorkBook.SaveAs("C:\\a.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);         xlWorkBook.Close(true, misValue, misValue);         xlApp.Quit();          releaseObject(xlWorkSheet);         releaseObject(xlWorkBook);         releaseObject(xlApp);          MessageBox.Show("Excel file created , you can find the file c:\\csharp-Excel.xls"); 

Answer 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.

Answer 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