Automate Excel Operations -1 - Read Excel Data into DataTable

Disclaimer - Neither Microsoft nor I advise to automate MS Office operations using Interop DLL

But then we don't have much of a choice when it comes to automating such operations. At least, I am not aware of any such tool at the time of writing this post.

Note - Before we proceed any further, let me tell you, from here on, whenever I say "Data", I am referring to tabular data in excel sheet until stated otherwise.

I am planning to write multiple posts and will try to cover one topic in each post. So let's start with the very first point - How to read data from excel file.

Let's start by creating an empty solution "FokatRnd" and adding an empty class project "CustomInteropHelper" to it. Now add a new class "ExcelHelper.cs" to this. To read data from excel file, we don't need any special tool. "System.Data.OleDb"  namespace will solve our purpose easily.

Declare a connection string that will be passed to OleDbConnection class to create a connection.

public const string EXCEL_CONNECTIONSTRING = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties=\"Excel 12.0 Xml;HDR={1};IMEX=1\";";

As you might have noticed, we are using 2 placeholders in the above connection string.
1. Data Source={0} ; - Fully qualified path of the excel file.
2. HDR={1} ; - This boolean value(Yes/No) will inform OleDbDataAdapter whether it should treat first row as table header or not.

Next, we will use OleDbDataAdapter to fill the DataTable object with results. DataTable is part of "System.Data" namespace so we don't need to include any other namespace for now. We need to pass the select query into this OleDbDataAdapter object.

string selectQuery = "select * from [SheetNameHere$]";

We need to pass the sheet name of the excel file from which we want to read the data. "*" is used to read all the columns. The query syntax is pretty much the same as SQL query. Take a look at the final piece of code that will read tabular data from specified excel sheet.

public static DataTable LoadDataFromExcel(string fileName, string sheetName,bool considerFirstRowHeader = true)
{
    string connection = string.Format(EXCEL_CONNECTIONSTRING, fileName, considerFirstRowHeader ? "Yes" : "No");

    DataTable objDataTable = new DataTable(sheetName);

    using (OleDbConnection objOleDbConnection = new OleDbConnection(connection))
    {
        OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
        objOleDbConnection.Open();
        string selectQuery = "select * from [SheetName$]";
        dataAdapter = new OleDbDataAdapter(selectQuery, objOleDbConnection);
        dataAdapter.Fill(objDataTable);
        dataAdapter.Dispose();
    }

    return objDataTable;
}

And, that's it. This function returns a DataTable which contains all the data in excelsheet. We can use this tabular data as per our requirement. Go ahead, Give it a try and as always, feel free to improve this article by providing your valuable suggestions and comments.