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

问题描述

我正在使用标准的Microsoft.Office.Interop.Excel将1200 X 800矩阵（indexMatrix）导出到excel文件。应用程序的工作原理就是真的很慢（即使是100×100矩阵）。我也通过一个TextWriter导出一个文本文件，几乎可以立即工作。有没有办法更快地导出到excel文件？

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?

这是我的代码：

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

推荐答案

您正在更新单个单元格。那会很慢如果您考虑到这一点，每次更新一个单元格时，RPC调用将被编组到Excel进程。

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.

这将是很多如果您将二维数组的值分配到单个语句（一个跨进程调用）中相同维度的Excel范围，而不是当前的1200 x 800 = 960,000跨进程调用，则速度将更快。

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.

如下所示：

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

实际上，在上面的代码中有三个跨进程调用（.Cells， .get_Resize和.set_Value），并且您的代码中每次迭代有两个调用（.Cells get和一个隐式的.set_Value），总共为1200 x 800 x 2 = 1,920,000。

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.

注意
range.get_Resize 和 range.set_Value 需要一个旧版本的Excel互操作库我正在使用这个帖子首次被创作。这些天，您可以使用@ The @ nk的评论中指出的 range.Resize 和 range.Value

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.

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