互操作Excel是缓慢的 [英] Interop Excel is slow

问题描述

我写一个程序打开一个Excel工作表，并阅读

  =的MyApp新Excel.Application（）; 
 MyBook = MyApp.Workbooks.Open（文件名）; 
 MySheet的工作=（Excel.Worksheet）MyBook.Sheets [1]; //显式铸铁这里不需要
 LASTROW = MySheet.Cells.SpecialCells（Excel.XlCellType.xlCellTypeLastCell）.Row; 
 MyApp.Visible = FALSE; 
  

大约需要6-7秒，此发生，这是正常的与互操作的Excel <？ / p>

也有一个更快的方法来读取一个Excel比这个？

 的String [] XX =新的字符串[LASTROW] 
为（INT指数= 1;指数< = LASTROW;指数++）
 {
 INT MAXCOL = endCol  -  startCol; 
为（INT J = 1; J< = MAXCOL; J ++）
 {
试
 {
 XX [指数 -  1] + =（MySheet.Cells [指数，J]为Excel.Range）.Value2.ToString（）; 
} 
抓
 {
} 
 
如果XX [指数 -  1] + =|（J = MAXCOL！）; 
} 
} 
 MyApp.Quit（）; 
 System.Runtime.InteropServices.Marshal.ReleaseComObject（MySheet的工作）; 
 System.Runtime.InteropServices.Marshal.ReleaseComObject（MyBook）; 
 System.Runtime.InteropServices.Marshal.ReleaseComObject（MyApp的）; 
  

解决方案

这答案只有你问题的第二部分。
你正在使用大量的范围有哪些不如预期确实很慢。

首先阅读完整的范围，然后遍历像这样的结果：

  VAR XX [，] =（MySheet.Cells [A1，XX100]作为Excel.Range）.Value2 ; 
的for（int i = 0; I< xx.getLength（0）;我++）
 {
为（INT J = 0; J< xx.getLength（1）; J ++）
 {
 Console.WriteLine（XX [I，J]的ToString（））; 
} 
} 
  

这会快很多！

I am writing an application to open an Excel sheet and read it

MyApp = new Excel.Application();
MyBook = MyApp.Workbooks.Open(filename);
MySheet = (Excel.Worksheet)MyBook.Sheets[1]; // Explict cast is not required here
lastRow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
MyApp.Visible = false;

It takes about 6-7 seconds for this to take place, is this normal with interop Excel?

Also is there a quicker way to Read an Excel than this?

string[] xx = new string[lastRow];
for (int index = 1; index <= lastRow; index++)
{
   int maxCol = endCol - startCol;
   for (int j = 1; j <= maxCol; j++)
   {
      try
      {
         xx[index - 1] += (MySheet.Cells[index, j] as Excel.Range).Value2.ToString();
      }
      catch
      {    
      }

      if (j != maxCol) xx[index - 1] += "|";
   }
}
MyApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(MySheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(MyBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(MyApp);

解决方案

This answer is only about the second part of your question. Your are using lots of ranges there which is not as intended and indeed very slow.

First read the complete range and then iterate over the result like so:

var xx[,] = (MySheet.Cells["A1", "XX100"] as Excel.Range).Value2;
for (int i=0;i<xx.getLength(0);i++)
{
    for (int j=0;j<xx.getLength(1);j++)
    {
         Console.WriteLine(xx[i,j].toString());
    }
}

This will be much faster!

这篇关于互操作Excel是缓慢的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！