///
<summary>
http://www.microsoft.com/en-us/download/details.aspx?id=13255
///
Gets the DataTable
///
</summary>
///
<param
name="excelExtension">file
extension. either xls or xlsx</param>
///
<param
name="excelFilePath">Excel
file path</param>
///
<param
name="sheetName">Name
of the sheet</param>
///
<returns>DataTable</returns>
private
static
DataTable
GetExcelDataTable(string
excelExtension, string
excelFilePath, string
sheetName)
{
DataTable
dt = new
DataTable();
try
{
string
query = String.Format("select
* from [{0}$]",
sheetName);
string
connectionString = string.Empty;
if
(excelExtension.ToLower().Trim() == "xls")
connectionString
= String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data
Source={0};Extended Properties=""Excel 8.0""",
excelFilePath);
if
(excelExtension.ToLower().Trim() == "xlsx")
connectionString
= String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data
Source={0};Extended Properties=""Excel 12.0 Xml""",
excelFilePath);
using
(OleDbDataAdapter
dataAdapter = new
OleDbDataAdapter(query,
connectionString))
{
dataAdapter.Fill(dt);
}
}
catch
(Exception
ex)
{
throw
new
Exception(ex.Message);
}
return
dt;
}
If you get the following error :
The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.
Install 'AccessDatabaseEngine.exe for 32-bit or '_x64' for 64-bit OS fromhttp://www.microsoft.com/en-us/download/details.aspx?id=13255
