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
– Steve B, Commented Nov 12, 2012 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
– Uni Le, Commented Nov 12, 2012 at 13:31

Community · Accepted Answer · 2017-05-23 11:58:22Z

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, 2017 at 11:58

CommunityBot

11 silver badge

answered Nov 12, 2012 at 13:24

ipavlic

4,95610 gold badges43 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Uni Le Over a year ago

i tried with NPOI, but i'm not use to it. Which are better NPOI or EPPlus?

ipavlic Over a year ago

@UniLe I'm sorry, but I can't help you with that.

Uni Le Over a year ago

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

Steve B Over a year ago

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?

ipavlic Over a year ago

@SteveB NPOI supports both xlsx and xls files.

|

John Bustos · Accepted Answer · 2012-11-12 14:21:07Z

0

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.

answered Nov 12, 2012 at 14:21

John Bustos

19.7k18 gold badges102 silver badges162 bronze badges

Collectives™ on Stack Overflow

Fastest way to read Excel using Interop and save in DataTable

2 Answers 2

12 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related