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;
}
}