Note :This post is first published on Apr-2013 in my previous blog Techkindle. Moving the content here.

Here we will try to load Excel sheet data into the datatable using Jet engine.
First, We need to Download and install the AccessDatabaseEngine exe from the microsoft website.


using System;
using System.Data;
using System.Data.OleDb;
namespace ExcelToDataTable
{
class LoadExcel
{
static void Main(string[] args)
{
LoadExcel objExcel = new LoadExcel();
DataTable dtExcelRecords = objExcel.GetDataTableFromExcel(@"D:\StudentRecords.xls");
if (dtExcelRecords != null && dtExcelRecords.Rows.Count > 0)
{
foreach (DataColumn dc in dtExcelRecords.Columns)
{
Console.Write(dc.Caption + " ");
}
Console.WriteLine("\n———————————————–");
foreach (DataRow dr in dtExcelRecords.Rows)
{
foreach (var item in dr.ItemArray)
{
Console.Write(item.ToString() + " ");
}
Console.Write("\n");
}
Console.ReadKey();
}
}
public DataTable GetDataTableFromExcel(string filePath)
{
string connectionString = string.Empty;
string[] arFile = filePath.Split('.');
string fileExtension = arFile[1];
if (fileExtension.ToLower() == "xls")
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
else if (fileExtension.ToLower() == "xlsx")
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
OleDbConnection objOleDbConnection = new OleDbConnection(connectionString);
OleDbCommand objOleDbCommand = new OleDbCommand();
try
{
objOleDbCommand.CommandType = System.Data.CommandType.Text;
objOleDbCommand.Connection = objOleDbConnection;
OleDbDataAdapter dAdapter = new OleDbDataAdapter(objOleDbCommand);
DataTable dtExcelRecords = new DataTable();
objOleDbConnection.Open();
DataTable dtExcelSheetName = objOleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
objOleDbCommand.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
dAdapter.SelectCommand = objOleDbCommand;
dAdapter.Fill(dtExcelRecords);
return dtExcelRecords;
}
catch (Exception ex)
{
throw ex;
}
finally
{
objOleDbConnection.Close();
objOleDbConnection.Dispose();
}
}
}
}

view raw

ExcelToDataTable

hosted with ❤ by GitHub

Output

Happy Learning 🙂