Storing a big Excel table into array with C#

user_10716897 0
Viewed 732

I have an Excel table with about 50 columns and over 6000 rows.

I found the following solution to read the data: https://coderwall.com/p/app3ya/read-excel-file-in-c

It uses Microsoft.Office.Interop.Excel to read the file.

Sadly, it is really slow. Reading a file with only 50 rows allready take about a minute. I never finished loading the 6000 row file.

I then thought about using csv, but the table contains , and ; so this won't be an option.

Can anyone suggest another method?

excel c#
2 Answers

Apart from my comment-

Here is the method I use in order to read from an Excel file and into a table. You will need to have:

using Microsoft.Office.Interop; using statement, along with adding the correct Microsoft.Office.Interop.Excel reference to your project.

Method:

public DataTable ReadExcel(string fileName, string TableName)
{
        DataTable dt = new DataTable();

        OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 8.0\"");
        OleDbCommand cmd = new OleDbCommand("SELECT * FROM " + TableName, conn);

        try
        {
            conn.Open();
            OleDbDataReader reader = cmd.ExecuteReader();

            while (!reader.IsClosed)
            {
                dt.Load(reader);
            }
        }
        finally
        {
            conn.Close();
        }

        return dt;
}

Explanation:

fileName will be the file path to the Excel file you are wanting to read the data form.

TableName will be the Excel Sheet name you are wanting to read data from.

The reason it is written this way, is because C# will read it and treat the Excel file like a database, where instead of sheets, there are tables.

You may need to alter the OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 8.0\"");

You can find the proper/correct Provider here: https://www.connectionstrings.com/excel/

user_84475620

If you're only going to read the Excel file, I suggest ExcelDataReader instead of the interop.

user_7935580
Related