Loading Excel sheet to DataTable using Ole DB jet engine in C#
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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