的Microsoft.Office.Interop.Excel很慢 [英] Microsoft.Office.Interop.Excel really slow

问题描述

我使用的是标准的Microsoft.Office.Interop.Excel导出1200 x 800的矩阵（indexMatrix）到一个Excel文件。该应用程序的工作原理，只是它是真的真的真的慢（即使是100×100矩阵）。我还通过出口的TextWriter在一个文本文件中的它的工作原理几乎瞬间。有什么办法导出到Excel文件快？

下面是我的code：

  Excel.Application xlApp =新Excel.Application（）;
        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWorkSheet;
        反对misValue = System.Reflection.Missing.Value;        // xlApp =新Excel.ApplicationClass（）;
        xlWorkBook = xlApp.Workbooks.Add（misValue）;        xlWorkSheet =（Excel.Worksheet）xlWorkBook.Worksheets.get_Item（1）;
        的for（int i = 0; I＆LT; 800;我++）// H
            对于（INT J = 0; J＆LT; 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（真，misValue，misValue）;
        xlApp.Quit（）;        releaseObject（xlWorkSheet）;
        releaseObject（xlWorkBook）;
        releaseObject（xlApp）;        MessageBox.Show（创建Excel文件，你可以找到文件c：\\\\ csharp的-Excel.xls）;
 

解决方案

您正在更新单个细胞。这将是非常缓慢的。如果你仔细想想，你每次更新单元，RPC调用将被整理到Excel进程。

这将是的多更快，如果你值的二维数组分配给单个语句相同尺寸的Excel范围（一个跨进程调用），而不是当前的1200 * 800 = 960000跨进程调用。

是这样的：

  //获取二维数组的维
INT rowCount等= indexMatrix.GetLength（0）;
INT列数= indexMatrix.GetLength（1）;
//获取相同尺寸的Excel范围
Excel.Range范围=（Excel.Range）xlWorkSheet.Cells [1,1];
范围= range.get_Resize（rowCount时，列数）;
//将2-D阵列到Excel范围
range.set_Value（Excel.XlRangeValueDataType.xlRangeValueDefault，indexMatrix）;
 

其实，是迂腐，有三个跨进程调用在上面code（.Cells，.get_Resize和.set_Value），并且每个迭代两个调用在code（.Cells获得和隐式.set_Value），共1200×800×2 = 1,920,000。

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");

解决方案

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.

这篇关于的Microsoft.Office.Interop.Excel很慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！