Interop Excel Slow

Question

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, is this normal with interop Excel?

Also is there a faster way to read 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);

Answer 1

This answer only applies to the second part of your question. You use a lot of ranges that are not so designed and really very slow.

Read the full range first, and then repeat it like this:

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());
    }
}

It will be much faster!

Answer 2

Short answer: right, the intervention is slow. (had the same problem, taking a couple of seconds to read 300 lines ...

Use the library for this:

• http://epplus.codeplex.com/
• http://npoi.codeplex.com/

Answer 3

@RvdK - COM- .

?

, . , .NET, COM-, ( ) COM- (Excel) ( IPC Windows), () - , OLE Automation , . .

, , . , COM - (- Visual Basic 6).

: http://www.codeproject.com/Articles/990/Understanding-Classic-COM-Interoperability-With-NE

?

• ClosedXML Excel xlsx ( , ) Microsoft OpenXml SDK, . : https://closedxml.codeplex.com/wikipage?title=Finding%20and%20extracting%20the%20data&referringTitle=Documentation

• Excel , , Excel, , : https://exceldatareader.codeplex.com/

• - Excel , (, XML, CSV). ,

Answer 4

, xls xlsx,

Workbook wb = new Workbook();
wb.LoadFromFile(ofd.FileName);

https://freenetexcel.codeplex.com/