Reading excel file with C#

Excel files can easily be read from C# using OleDbDataAdapter. You just need the correct connection string - and that's where it gets a little complicated. You have to specify the version of Excel in the connection string.

Now there are pretty many connection strings that look like this one floating around the Internet

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
    "Data Source=" + fileName + ";" +
    "Extended Properties=Excel 8.0;";

Don't use this one, use this one instead:

string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
    "Data Source=" + fileName + ";" +
    "Extended Properties=Excel 12.0;";

The first connection string provides access only to older (Excel 97) Excel files - which nowadays isn't really of any use anymore. The second one uses THE ACE OLEDB Driver in version 12.0, which is able to handle the new Excel format (files with the extension .xlsx).

The other thing we need is an OleDbDataAdapter, which can be found in the following namespace

using System.Data.OleDb;

Now we got all this settled, let's take a look at the actual code

using System.Data.OleDb;

namespace Examples.Data
{

class ExcelAdapter
    {
        private OleDbDataAdapter dataAdapter;
        private DataSet dataSet;
        private DataTable dataTable;

        private String fileName;

        public ExcelAdapter(String fileName)
        {
            this.Load(fileName);
            this.fileName = fileName;
        }

        public void Reload()
        {
            this.Load(this.fileName);
        }

        private List Load(String fileName)
        {
            string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
                "Data Source=" + fileName + ";" +
                "Extended Properties=Excel 12.0;";

            this.dataAdapter = new OleDbDataAdapter("SELECT * FROM [Blonskij$]", connectionString);

            this.dataSet = new DataSet();

            this.dataAdapter.Fill(this.dataSet, "ExcelInfo");
            this.dataTable = this.dataSet.Tables["ExcelInfo"];
            foreach (DataRow row in this.dataTable.AsEnumerable())
            {
                
            }

            return new List();
        }
    }}

Now mind the following line

this.dataAdapter = new OleDbDataAdapter("SELECT * FROM [MySheet$]", connectionString);

Here you have to specify the Sheet in the chosen Excel file.

That's that. I hope it helps.