/// <summary>
/// 将Excel导入到DataTable （用ODBC方法连接）(LiPu)
/// </summary>
/// <param name="excelPath">excel 路径</param>
/// <param name="sheetName"></param>
/// <returns></returns>
public DataTable ExcelToDataTableODBC(string excelPath, string sql)
{
//检验excelPath是否真实存在
if (File.Exists(excelPath) == false)
{
throw new Exception("Can't found the file :" + excelPath);
}
//Excel数据导入
String strExcelConn = "DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);" +
"UID=admin;" +
"UserCommitSync=Yes;" +
"Threads=3;" +
"SafeTransactions=0;" +
"ReadOnly=1;" +
"PageTimeout=5;" +
"MaxScanRows=8;" +
"MaxBufferSize=2048;" +
"FIL=excel 12.0;" +
"DriverId=1046;" +
// @"DefaultDir=D:\vswork\ExecelConn;" +
"DBQ=" + excelPath;
string strExcelSelect = "";
strExcelSelect = sql;
//需在sheetName前后加 ”[” 与 ”]” 符号，并于sheetName后加入”$”符号，否则会发生Microsoft Jet 数据库引擎无法找到 'sheetName' 对象的错误
DataSet ds = new DataSet();
OdbcConnection myExcelConn = null;
myExcelConn = new OdbcConnection(strExcelConn);
//OdbcDataReader reader = null;
OdbcCommand myExcelCommand = new OdbcCommand(strExcelSelect, myExcelConn);
OdbcDataAdapter myDataAdapter = new OdbcDataAdapter(myExcelCommand);
try
{
myExcelConn.Open();
myDataAdapter.Fill(ds, "ReadData");
}
catch (Exception e)
{
}
finally
{
myExcelConn.Close();
}
DataTable result = ds.Tables["ReadData"];
return result;
}
/// <summary>
/// 将Excel导入到DataTable
/// </summary>
/// <param name="excelPath">excel 路径</param>
/// <param name="sheetName"></param>
/// <returns></returns>
public DataTable ExcelToDataTable(string excelPath, string sql)
{
//检验excelPath是否真实存在
if (File.Exists(excelPath) == false)
{
throw new Exception("Can't found the file :" + excelPath);
}
//Excel数据导入
String strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + excelPath + ";" +
//"Extended Properties=Excel 8.0;";
"Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
string strExcelSelect = "";
strExcelSelect = sql;
//需在sheetName前后加 ”[” 与 ”]” 符号，并于sheetName后加入”$”符号，否则会发生Microsoft Jet 数据库引擎无法找到 'sheetName' 对象的错误
DataSet ds = new DataSet();
OleDbConnection myExcelConn = null;
myExcelConn = new OleDbConnection(strExcelConn);
OleDbCommand myExcelCommand = new OleDbCommand(strExcelSelect, myExcelConn);
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myExcelCommand);
try
{
myExcelConn.Open();
myDataAdapter.Fill(ds, "ReadData");
}
catch(Exception ex)
{
}
finally
{
myExcelConn.Close();
}
DataTable result = new DataTable();
if (ds.Tables.Count != 0)
{
result = ds.Tables["ReadData"];
}
else
{
result = null;
}
return result;
}