Excel Load To DataTable using OleDb in C#

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 web site.

Complete Source Code.

	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