To get DataSet from Excel file.

By calling following functions returns dataset

string strErrorMessage = “”;
DataSet ds = GetExcel(path,ref strErrorMessage);

using System.Data.OleDb;
public DataSet GetExcel(string strFileName, ref string strErrorMessage)
{
string strConnection = GetExcelConnectionString(strFileName);
DataTable dtNew = null;
OleDbConnection cnExcelConnection = new OleDbConnection(strConnection);
OleDbCommand cmd = new OleDbCommand();

DataSet ds = new DataSet();
try
{
cnExcelConnection.Open();
dtNew = cnExcelConnection.GetSchema("Tables");

if (dtNew.Rows.Count > 0)
{
foreach(DataRow dr in dtNew.Rows)
{
ds.Tables.Add(ReadExcelData(dr["TABLE_NAME"].ToString(), strConnection, ref strErrorMessage));
}
}
else
{
return null;
}
}
catch (Exception ex)
{
strErrorMessage = ex.Message.ToString();
return null;
}
finally
{
if (!(cnExcelConnection.State == ConnectionState.Closed))
{
cnExcelConnection.Close();
}
cnExcelConnection.Dispose();
}
return ds;
}

public string GetExcelConnectionString(string strFileName)
{
string strConnection = "";
string strFileType = GetFileExtention(strFileName, true);

if (strFileType == ".xlsx")
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + (strFileName) + ";" + "Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";

if (strFileType == ".xls")
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + (strFileName) + ";" + "Extended Properties='Excel 8.0;HDR=NO;IMEX=1'";
return strConnection;
}

public string GetFileExtention(string strFileName, bool blExtensionRequired)
{
if (strFileName == "")
return strFileName;
string[] slots = strFileName.Split('.');
if (blExtensionRequired)
{
return "." + slots[slots.Length - 1];
}
else
{
return slots[slots.Length - 1];
}
}

public DataTable ReadExcelData(string strSheetName, string strConnection, ref string strErrorMessage)

{

OleDbConnection cnExcelConnection = new OleDbConnection(strConnection);

try

{

cnExcelConnection.Open();

DataTable dtImportedData = new DataTable();

dtImportedData.TableName = strSheetName;

OleDbDataAdapter myData = new OleDbDataAdapter("SELECT * FROM [" + strSheetName + "]", cnExcelConnection);

myData.Fill(dtImportedData);

return dtImportedData;

}

catch (Exception ex)

{

strErrorMessage = ex.Message.ToString();

return null;

}

finally

{

if (!(cnExcelConnection.State == ConnectionState.Closed))

{

cnExcelConnection.Close();

}

cnExcelConnection.Dispose();

}

}