Excel轉datatable

如果想支持 .xls,.xlsx 兩種格式 則必須安裝一個exe文件，下載路徑https://www.microsoft.com/zh-CN/download/details.aspx?id=13255   下載好安裝就支持.xls,.xlsx 兩種格式轉換成datatable了,

附代碼   

public ActionResult file(string inputName)
{
try
{

HttpFileCollectionBase ss = Request.Files;
HttpPostedFileBase file = ss.Get(inputName);
if (file == null)
{
return Json(new
{
success = "沒有選擇文件"
});
}
string IsXls = System.IO.Path.GetExtension(file.FileName).ToString().ToLower();//System.IO.Path.GetExtension獲得文件的擴展名 
if (IsXls != ".xls" && IsXls != ".xlsx")
{

return Json(new
{
success = "只可以選擇.xls,.xlsx的文件"
});
}
if (!Directory.Exists(Server.MapPath("~/excel/")))
{
Directory.CreateDirectory(Server.MapPath("~/excel/"));
}
FileInfo fil = new FileInfo(file.FileName);
string filename = fil.Name;
/* string filename = file.FileName; */ //獲取Execle文件名 DateTime日期函數 
string savePath = Server.MapPath("~/excel/" + filename);//Server.MapPath 獲得虛擬伺服器相對路徑 
file.SaveAs(savePath); //SaveAs 將上傳的文件內容保存在伺服器上 
DataTable dt12 = fnexcle(savePath);
//DataSet ds = ExcelSqlConnection(savePath, filename); 
//DataTable dt = ds.Tables[0]; //定義一個DataRow數組 
int rowsnum = dt12.Rows.Count;
if (rowsnum == 0)
{
return Json(new
{
success = "表格為空！！！"
});
}
else
{

//存入資料庫
ceshiclass.fnexcle(Session[Miscells.userID].ToString(), Session[Miscells.userName].ToString(), dt12);
}
return Json(new { success = "" });
}
catch (Exception ex)
{
return Json(new { success = ex.Message });
}

}

public DataTable fnexcle(string savePath)
{
string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + savePath + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
DataSet myDataSet = new DataSet();
OleDbConnection conn = new OleDbConnection(strConn);
try
{
conn.Open();
//返回Excel的架構，包括各個sheet表的名稱,類型，創建時間和修改時間等　 
DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
//包含excel中表名的字元串數組 
string[] strTableNames = new string[dtSheetName.Rows.Count];
for (int k = 0; k < dtSheetName.Rows.Count; k++)
{
strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
}
OleDbDataAdapter myCommand = null;
DataTable dt = new DataTable();
//從指定的表明查詢數據,可先把所有表明列出來供用戶選擇 
string strExcel = "select * from[" + strTableNames[0] + "]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
//myCommand.Fill(dt); 
myCommand.Fill(myDataSet, "ExcelInfo");
//Data.Deleted();
DataTable table = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable();
conn.Close();
return table;
}
catch (Exception ex)
{
conn.Close();
throw ex;

//return null;
}

}