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.
