CODEWITHSUNDEEP
c#exceldatatableexport
Uni Le
Uni Le

Reputation: 793

Fastest way to read Excel using Interop and save in DataTable

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;

Upvotes: 0

Views: 6349

Answers (2)

ipavlic
ipavlic

Reputation: 4966

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).

Upvotes: 1

John Bustos
John Bustos

Reputation: 19544

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.

Upvotes: 0

Related Questions