MVC excel导入

MVC excel导入

view部分

代码

@{ Layout = "~/Views/Shared/_LayoutPage1.cshtml"; ViewBag.Title = "Index"; } <script src="~/scripts/jquery-1.10.2.js"></script> <link rel="stylesheet" href="~/Content/kd.css" /> @using (Html.BeginForm("StationImport", "daoru", FormMethod.Post, new { enctype = "multipart/form-data" })) { <h2 style="color:#fff;"> 信息导入 </h2> <div class="daoru"> <fieldset id="myfieldset1"> <p> 选择文件：<input id="FileUpload" type="file" name="files" style="color:#fff;" /> </p> <p> <input id="btnImport" type="submit" value="导入" /> </p> <span >@ViewBag.error</span> </fieldset> </div> }

controller部分

代码 using System; using System.Collections.Generic; using System.Data; using System.Data.OleDb; using System.IO; using System.Linq; using System.Web; using System.Web.Mvc; using Rjb.Models;

namespace Rjb.Controllers { public class daoruController : Controller { // GET: daoru public rjbEntities ddb= new rjbEntities(); public ActionResult Index() { return View(); }

[HttpPost] public ActionResult StationImport(HttpPostedFileBase filebase) { HttpPostedFileBase file = Request.Files["files"];//获取excel文件 if (file == null || file.ContentLength <= 0)//判断是否为空 { ViewBag.error = "文件不能为空"; return View(); } string fileExtenSion; //获取上传文件的扩展名 fileExtenSion = Path.GetExtension(file.FileName); if (fileExtenSion.ToLower() != ".xls" && fileExtenSion.ToLower() != ".xlsx") { ViewBag.error = "文件类型不对，只能导入xls和xlsx格式的文件"; return View(); } string FileName = "../Content/excel" + Path.GetFileName(file.FileName); //删除服务器里上传的文件 if (System.IO.File.Exists(Server.MapPath(FileName))) { System.IO.File.Delete(Server.MapPath(FileName)); } file.SaveAs(Server.MapPath(FileName)); //HDR=Yes，这代表第一行是标题，不做为数据使用 ，如果用HDR=NO，则表示第一行不是标题，做为数据来使用。系统默认的是YES string connstr2003 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(FileName) + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'"; string connstr2007 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(FileName) + ";Extended Properties=\"Excel 12.0;HDR=YES\""; OleDbConnection conn; if (fileExtenSion.ToLower() == ".xls") { conn = new OleDbConnection(connstr2003); } else { conn = new OleDbConnection(connstr2007); } conn.Open(); string sql = "select * from [Sheet1$]"; OleDbCommand cmd = new OleDbCommand(sql, conn); DataTable dt = new DataTable(); OleDbDataReader sdr = cmd.ExecuteReader(); dt.Load(sdr); sdr.Close(); conn.Close(); //删除服务器里上传的文件 if (System.IO.File.Exists(Server.MapPath(FileName))) { System.IO.File.Delete(Server.MapPath(FileName)); } try { int insertcount = 0;//记录插入成功条数 for (int i = 0; i < dt.Rows.Count; i++) //列数 { string e_name = dt.Rows[i][0].ToString();//获取编号 t_shop shop = new t_shop(); shop.s_name = e_name; ddb.t_shop.Add(shop); ddb.SaveChanges(); insertcount++; } Response.Write(insertcount + "条数据导入成功！"); } catch (Exception ex) { } return RedirectToAction("Index"); } }

}