Slow Performance When Reading Excel

Question

I want to read excel file but in this way is too slow. What pattern should I use to read excel file faster. Should I try csv ?

I am using the following code:

ApplicationClass excelApp = excelApp = new ApplicationClass();
Workbook myWorkBook = excelApp.Workbooks.Open(@"C:\Users\OWNER\Desktop\Employees.xlsx");
Worksheet mySheet = (Worksheet)myWorkBook.Sheets["Sheet1"];

for (int row = 1; row <= mySheet.UsedRange.Rows.Count; row++)
{
    for (int col = 1; col <= mySheet.UsedRange.Columns.Count; col++)
    {
        Range dataRange = (Range)mySheet.Cells[row, col];
        Console.Write(String.Format(dataRange.Value2.ToString() + " "));
    }
    Console.WriteLine();
}

excelApp.Quit();

You seem to be using Excel Interop for reading Excel files. This is not the recommended (nor supported) way to do so. Please use OpenXML for direct file access instead. There are plenty of wrapper libraries for working with it. http://stackoverflow.com/a/23104151/1808494 — Aron, Aug 16 '15 at 14:14

score 3 · Accepted Answer · edited May 23 '17 at 11:43

The reason your program is slow is because you are using Excel to open your Excel files. Whenever you are doing anything with the file you have to do a COM+ interop, which is extremely slow, as you have to pass memory across two different processes.

Microsoft has dropped support for reading .xlsx files using Excel interop. They released the OpenXML library specifically for this reason.

I suggest you use a wrapper library for using OpenXML, since the API is pretty hairy. You can check out this SO for how to use it correctly.

open xml reading from excel file

Daniel Luberda · Answer 2 · 2015-08-16T14:26:24.673

0

You're accessing Excel file through excel interop. By doing reads cell by cell you're doing a lot of P/Invoke's which is not very performant.

You can read data in ranges, not cell by cell. This loads the data into memory and you could iterate it much faster. (Eg. try to load column by column.)

BTW: You could use some library instead like http://epplus.codeplex.com which reads excel files directly.

edited Aug 16 '15 at 14:26

answered Aug 16 '15 at 14:12

Daniel Luberda

7,374
1
32
40

This does not explain why reading a cell at a time is slow (Invoke/P and marshaling large amounts of memory across processes). – Aron Aug 16 '15 at 14:18
Your EDIT does not relate to your answer at all. EPPlus solves the problem by not interoping Excel at all, and reading the file directly using OpenXML. – Aron Aug 16 '15 at 14:19

score 0 · Answer 3 · answered Aug 16 '15 at 19:36

0

Excel Data Reader

Lightweight and very fast if reading is your only concern.

answered Aug 16 '15 at 19:36

Juergen

151
1
5

Slow Performance When Reading Excel

3 Answers3