/// <summary>
/// 导入Excel成DataTable
/// </summary>
/// <param name="filePath">文件路径 </param>
/// <param name="version">Exl的版本(2003,2007)</param>
/// <returns> </returns>
public DataTable ExcelImportToDataTable(string filePath, string version)
{
DataTable dt = null;
OleDbConnection Excelconn = null;
LogHelper.WriteLog("初始化");
if (version == "2003")
{
Excelconn =
new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + filePath + @"';Extended Properties='Excel 8.0;HDR=NO;IMEX=1'" + @";");
}
else if (version == "2007")
{
LogHelper.WriteLog("判断为OleDbConnection");
Excelconn =
new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + filePath + @"';Extended Properties='Excel 12.0;HDR=NO;IMEX=1'" + @";");
//当 IMEX=0 时为“汇出模式”,这个模式开启的 Excel 档案只能用来做“写入”用途。
//当 IMEX=1 时为“汇入模式”,这个模式开启的 Excel 档案只能用来做“读取”用途。
//当 IMEX=2 时为“连結模式”,这个模式开启的 Excel 档案可同时支援“读取”与“写入”用途。
LogHelper.WriteLog("判断为OleDbConnection实例化成功");
}
else
{
throw new Exception("当前文件正在编辑,请关闭重试!");
}
//OleDbCommand cmd = null;
//OleDbDataReader rdr = null;
LogHelper.WriteLog("连接字符串初始化成功字符串的值:" + Excelconn.ConnectionString);
try
{
Excelconn.Open();
//cmd = Excelconn.CreateCommand();
LogHelper.WriteLog("打开成功");
dt = Excelconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
//string sheetName = "[" + dt.Rows[0]["TABLE_NAME"] + "]"; // TABLE_NAME 按照字母顺序排列 Rows[0]["TABLE_NAME"] 并不总是第一个sheet
string sheetName = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["TABLE_NAME"].ToString() == "Sheet1$")
{
sheetName = "[Sheet1$]";
isDefaultSheetNameExist = true;
break;
}
}
if (!isDefaultSheetNameExist) throw new Exception("默认的Sheet1不存在,请勿修改默认的Sheet名!");
//cmd.CommandText = "SELECT * FROM " + sheetName + "";
//rdr = cmd.ExecuteReader();
string strSql = "SELECT * FROM " + sheetName + "";
LogHelper.WriteLog("sql语句:" + strSql + "\n");
OleDbDataAdapter da = new OleDbDataAdapter(strSql, Excelconn);
LogHelper.WriteLog("初始化适配器");
dt = new DataTable();
LogHelper.WriteLog("初始化表格");
da.Fill(dt);
LogHelper.WriteLog("成功返回dt!");
return dt;
}
catch (Exception etc)
{
LogHelper.WriteLog("ADO.NET连接EXCEL获取其中的数据异常:", etc);
LogHelper.WriteLog(etc.Message + "\n");
if (!(etc.InnerException == null))
LogHelper.WriteLog("内部异常", etc.InnerException);
throw etc;
}
finally
{
//rdr.Close();
Excelconn.Close();
LogHelper.WriteLog("关闭ADO.NET数据连接,释放连接资源");
}
}
/// <summary>
/// 根据 MIME type 来判断文件的类型
/// </summary>
/// <returns></returns>
private static string getExcelType()
{
if (_filemimeType == "application/vnd.ms-excel")
{
return "2003";
}
else if (_filemimeType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" ||
_filemimeType == "application/x-zip-compressed") // IE6 下Excel2007 为 application/x-zip-compressed
{
return "2007";
}
else
{
return "";
}
}