/// <summary>
/// excel 匯入
/// </summary>
/// <param name="filePath">路徑</param>
/// <param name="paramType">類型</param>
public int ImportFromExcel(String filePath)
{
OleDbDataReader result = null;
OleDbConnection conn = null;
try
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1\"";
conn = new OleDbConnection(strConn);
conn.Open();
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
//迭代所有標籤頁
foreach (DataRow dr in dt.Rows)
{
//標籤頁 名稱
String sheetName = dr["TABLE_NAME"].ToString();
log.Error("tableName :" + sheetName);//********************************
OleDbCommand myCommand = new OleDbCommand("SELECT * FROM [" + sheetName + "]", conn);
result = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
//逐行讀取
for (int line = 0; result.Read(); line++)
{
Hashtable lineData = new Hashtable();
//逐列讀取
for (int row = 0; row < result.FieldCount; row++)
{
lineData.Add("F" + row, result.GetValue(row).ToString());
log.Error("result[" + line + "," + row + "] :" + result.GetValue(row).ToString());//********************************
}
}
}
return(1);
}
//例外
catch (Exception e)
{
HibernateHelper.RollbackTransaction();
log.Error("從 excel中導入失敗 :" + e.Message);
HttpContext.Current.Items["Message"] = e.Message;
return(-1);
}
//關閉
finally
{
HibernateHelper.CloseSession();
if (result != null)
{
result.Close();
}
//關閉
if (conn != null)
{
conn.Close();
}
}
}