public static System.Data.DataSet ExcelConnection(string filepath, string exName, string tableName, out string errmsg)
{
errmsg = "";
string strCon = "";
if (exName == ".xls")
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
if (exName == ".xlsx")
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
System.Data.OleDb.OleDbConnection ExcelConn = new System.Data.OleDb.OleDbConnection(strCon);
try
{
ExcelConn.Open();
DataTable dt = ExcelConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
if (dt == null)
return null;
string excelSheet = dt.Rows[0]["TABLE_NAME"].ToString();
string strCom = string.Format("SELECT * FROM [" + excelSheet + "]");
System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, ExcelConn);
DataSet ds = new DataSet();
myCommand.Fill(ds, tableName);
ExcelConn.Close();
return ds;
}
catch(Exception ex)
{
errmsg = ex.Message;
ExcelConn.Close();
return null;
}
}