利用oledb读取的excel数据快速插入的sqlserver中
1、控制器
/// <summary>
/// 批量导入Excel
/// </summary>
/// <returns></returns>
public ActionResult ImportExcel(HttpPostedFileBase file)
{
try
{
//HttpPostedFileBase file = Request.Files["file"];//接收客户端传递过来的数据.
if (file == null)
{
return Content("请选择上传的Excel文件");
}
else
{
string filename = DateTime.Now.ToString("yyyymmddhhMMss") + file.FileName;
string filepath = Server.MapPath("~/Uploads/"+filename);
// / 如果已经存在就清空
file.SaveAs(filepath);
Excel excel = new Excel();
DataSet ds= excel.GetExcelTabel(filepath);
foreach (DataRow dr in ds.Tables[0].Rows)
{
StuInfo s = new StuInfo();
s.StuId = Guid.NewGuid();
s.StuName = dr[0].ToString();
string classname = dr[1].ToString();
ClassInfo c = db.ClassInfo.AsNoTracking().Where(u => u.ClassName == classname).FirstOrDefault();
s.ClassInfo_ClassId = c.ClassId;
// s.ClassInfo =c;
s.IsDelete = false;
db.StuInfo.Add(s);
}
int i= db.SaveChanges();
//查询数据
var list = db.StuInfo.AsNoTracking().Where(u => u.IsDelete == false).Select(u=> new {u.StuId,u.StuName,u.ClassInfo.ClassName });
return Json(list,JsonRequestBehavior.AllowGet);
}
}
catch (Exception e)
{
return Content("导入失败");
}
}
2、读取Excel的函数
public DataSet GetExcelTabel(string filesavepath)
{
//连接字符串
string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + filesavepath + ";Extended Properties='Excel 12.0; HDR=YES; IMEX=1'";
try
{
//获取所有的工作表名
OleDbConnection objConn = new OleDbConnection(strConn);
objConn.Open();
DataTable dtSheetName = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
objConn.Close();
String[] excelSheets = new String[dtSheetName.Rows.Count];//创建字符数组存sheet名
int i = 0; // 添加工作表名称到字符串数组
foreach (DataRow row in dtSheetName.Rows)
{
string strSheetTableName = row["TABLE_NAME"].ToString();
//过滤无效SheetName
if (strSheetTableName.Contains("$") && strSheetTableName.Replace("'", "").EndsWith("$"))
{
excelSheets[i] = strSheetTableName.Substring(0, strSheetTableName.Length - 1);
}
i++;
}
DataSet ds = new DataSet();
//遍历工作表
foreach (var item in excelSheets)
{
//建立adapter获取Excel中的数据
OleDbDataAdapter da = new OleDbDataAdapter("select * from ["+item+"$]", strConn);
//填充读取的Excel数据到DataSet
da.Fill(ds);
}
return ds;
}
catch (Exception e)
{
throw;
}
}
3、注意事项:
“未在本地计算机上注册“microsoft.ACE.oledb.12.0””这个异常
解决办法 : https://www.cnblogs.com/jinianjun/archive/2011/12/05/2276679.html