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

问题描述

我正在使用标准 Microsoft.Office.Interop.Excel 将 1200 X 800 矩阵 (indexMatrix) 导出到 Excel 文件.该应用程序有效，只是它真的真的很慢(即使对于 100 x 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 互操作库所需要的.这些天你可以使用 range.Resize 和 range.Value 如@The1nk 的评论中所述.

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屋！