Fastest way to read Excel using Interop and save in DataTable

Question

After searching in Internet and trying some codes i exported data from Excel to Datatable with Interop. The Problem is, it's very slow. Can someone give me a key how can i make it quicker with Interop, not OLEDB or anything else?

My code:

object misValue = System.Reflection.Missing.Value;

Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(userSelectedFilePath2);
Excel._Worksheet xlWorksheet1 = xlWorkbook.Sheets[1];
Excel.Range xlRange1 = xlWorksheet1.UsedRange;

DataTable excelTb1 = new DataTable();

for (int j = 1; j <= xlRange1.Columns.Count; j++) // Header Names
{
    excelTb1.Columns.Add(xlRange1.Cells[1, j].Value2.ToString());
}

DataRow dataRow = null;

for (int row = 2; row < xlRange1.Rows.Count + 1; row++)
{
    dataRow = excelTb1.NewRow();

    for (int col = 1; col <= xlRange1.Columns.Count; col++)
    {
        dataRow[col - 1] = (xlRange1.Cells[row, col] as Excel.Range).Value2;
    }
    excelTb1.Rows.Add(dataRow);
}

xlWorkbook.Close(true, misValue, misValue);
xlApp.Quit();
dataGridView1.DataSource = excelTb1;

Why do you need to use *interop* instead of any other technique? — Steve B, Nov 12 '12 at 13:23
@SteveB: i tried wiht many other techniques, OLEDB and NPOI but got many problems. OLEDB read only 255chars and NPOI has to add dll file and update POI, which is a little complicated — Uni Le, Nov 12 '12 at 13:31

score 1 · Answer 1 · edited May 23 '17 at 11:58

1

I'll give you an answer to a question you didn't ask. Use NPOI library.

it will be faster
you won't have problems with forgetting to close your resources
Excel will not be required or used in the background

Here's the relevant code for that: NPOI : How To Read File using NPOI . For xlsx formats, use XSSFWorkbook instead (it is available starting from version 2.0).

edited May 23 '17 at 11:58

Community

1
1

answered Nov 12 '12 at 13:24

ipavlic

4,906
10
40
77

i tried with NPOI, but i'm not use to it. Which are better NPOI or EPPlus? – Uni Le Nov 12 '12 at 13:29
@UniLe I'm sorry, but I can't help you with that. – ipavlic Nov 12 '12 at 13:30
when i use NPOI, i got FileXmlOfficeException coz i want to read xlsx file instead of xls. So i changed to XSSF but now got problem that i have to update POI. It's complicated – Uni Le Nov 12 '12 at 13:33
don't know the exact schema, but xlsx are simply zip files with xml content inside. Maybe you can use the OpenXML SDK to get the xml content, and explore it? – Steve B Nov 12 '12 at 13:37
@SteveB NPOI supports both `xlsx` and `xls` files. – ipavlic Nov 12 '12 at 13:39
so how can i read using NPOI xlsx file without Apache's POI? Apache's POI is a java lib so i need IKVM, it's complicated, isn't it? – Uni Le Nov 12 '12 at 13:50
@UniLe There are no "NPOI xlsx" files, only Excel xlsx files. It seems that you are using C#. In that case, you head to the link in my post, click on it, and download the library. It comes with binaries (`NPOI.dll`) and examples. NPOI is a .NET port of Apache POI which is a Java library. Sadly, you must make a minimal effort on your part. There's even an answer to your question here on SO: http://stackoverflow.com/questions/5855813/npoi-how-to-read-file-using-npoi – ipavlic Nov 12 '12 at 14:03
i tried it many times, but it gived me ERROR FileXmlOfficeException, use XSSF instead of SXXF. I'm using Excel 2010 – Uni Le Nov 12 '12 at 14:17
@UniLe Then why don't you use `XSSFWorkbook`? It's available starting from NPOI 2.0 (http://code.google.com/p/npoi/downloads/list). – ipavlic Nov 12 '12 at 14:43
i tried the link you gave me 2.0 but still got the same error: `The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)` so i changed from HSSFWorkbook to XSSFWorkbook but my compiler didn't recognize it – Uni Le Nov 12 '12 at 15:00
@UniLe I don't have such problems when testing. Try on a separate project - I'm quite certain the error is on your side. – ipavlic Nov 13 '12 at 07:47
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/19466/discussion-between-ipavlic-and-uni-le) – ipavlic Nov 13 '12 at 07:47

score 0 · Answer 2 · answered Nov 12 '12 at 14:21

My First thoughts on this are that you are looping through the Excel sheet and converting values one-by-one int your array to populate your structure.

Try something more along the lines of (Forgive my VB, but i'm sure oyu'll understand what I'm recommending):

Dim SpreadsheetVals(,) as object
SpreadhseetVals = xlWorksheet1.UsedRange

Then do your looping through your array instead.

That should improve speed considerably.

Fastest way to read Excel using Interop and save in DataTable

2 Answers2