Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Loading Excel sheet to DataTable using Ole DB jet engine in C#
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();
}
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment