3

I'm am working in c# with Excel files, using Microsoft.Office.Interop.Excel. My problem is that my program runs very slow. Basically, what it does is to iterate through the cells of an Excel sheet, and read the data in each one of the cells.

I use the following command:

value = (range.Cells[row, col] as Excel.Range).Value2;

where value is my variable, and range is an Interop.Excel object of the Range class.

Is there a beter way in Interop to access an access file , or another library which I should use?

Soner Gönül
  • 97,193
  • 102
  • 206
  • 364
proton
  • 393
  • 6
  • 31
  • 2
    Don't use interop. Use OLEDB or a library like EPPlus to read the file without using a spreadsheet – Panagiotis Kanavos Aug 05 '15 at 11:01
  • 1
    Use `sheet.get_Range()` to load into memory as a batch, then loop – Alex K. Aug 05 '15 at 11:06
  • I ran into this earlier this year - please see both attempts and note how much faster the first is: [how-to-open-and-parse-excel-data](http://stackoverflow.com/questions/28830743/how-to-open-and-parse-excel-data) – jacoblambert Aug 05 '15 at 11:09

2 Answers2

1

If at any possible, use a library like EPPLUS, which is MUCH more faster than interop and a lot easier to program.

If not possible due to external constraints, try to reduce the number of interop calls as much as possible. Doing the looping in excel is asking for trouble - since each cell has to acessed via interop and the marshalling of data from excel to your app eats a lot of time. It is much better to use get_Range() to get the whole array in one go. The single request will take a long time, but looping in C# is extremely fast afterwards. In general, it is beneficial to do anything you can on the C# side - even if excel would offer the same functionality.

Christian Sauer
  • 10,351
  • 10
  • 53
  • 85
0

Excel Data Reader

Lightweight and very fast if reading is your only use case.

Juergen
  • 151
  • 1
  • 5