MVC3 Excel导入数据到sql Server数据库
MVC Excel导入到数据库,我们客户有一个固定的Excel,每个月都让我手动录入到库内(一开始不会SQL的导入)一两次我还好,蛮乐意的后来多了,我就使用了SQL自带的导入,可是每个月都这样,就太恶心了,为了凸显我是一个程序员,我跟项目经理提出,做一个页面上传Excel文件的页面,然后保存到数据库,让客户上传,天天上传都可以,不要每个月找我一次,而且客户还是个女的,最让我不开心的是她还那么土,然后项目经理说既然是你想出来的,那么你就自己写,我化石了,则个则.............ge。我是一个有骨气的人,所以只好试试啦。
视图完整代码
@using (Html.BeginForm("StationImport", "Home", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
<h2>基站信息导入</h2>
<div style="margin-top: 20px;">
<fieldset id="myfieldset1">
<legend>基站批量信息导入</legend>
<p>
选择文件:<input id="FileUpload" type="file" name="files" style="width: 250px; height: 24px;
background: White" class="easyui-validatebox" /></p>
<p>
<input id="btnImport" type="submit" value="导入" style="width: 60px; height: 28px;" /></p>
<p style="color: Red; text-align: center;">@ViewBag.error</p>
</fieldset>
</div>
}
后台完整代码
private MySchoolEntities db = new MySchoolEntities();
public ActionResult Index()
{
return View();
}
[HttpPost]
public ActionResult StationImport(HttpPostedFileBase filebase)
{
HttpPostedFileBase file=Request.Files["files"];
string FileName = string.Empty;
string strConn = string.Empty;
string savePath;
if (file == null||file.ContentLength<=0)
{
ViewBag.error = "文件不能为空";
return View();
}
else
{
string filename= Path.GetFileName(file.FileName);
int filesize = file.ContentLength; //获取上传文件的大小单位为字节byte
string fileEx = Path.GetExtension(filename); //获取上传文件的扩展名
string noFileName =Path.GetFileNameWithoutExtension(filename);//获取无扩展名的文件名
int Maxsize = 4000 * 1024;//定义上传文件的最大空间大小为4M
string FileType = ".xls,.xlsx";//定义上传文件的类型字符串
FileName = noFileName + DateTime.Now.ToString("yyyyMMddhhmmss") + fileEx;
if (!FileType.Contains(fileEx))
{
ViewBag.error = "文件类型不对,只能导入xls和xlsx格式的文件";
return View();
}
if (filesize >= Maxsize)
{
ViewBag.error = "上传文件超过4M,不能上传";
return View();
}
string path = AppDomain.CurrentDomain.BaseDirectory + "uploads/excel/";
savePath = Path.Combine(path, FileName);
file.SaveAs(savePath);
if (fileEx.Equals(".xlsx"))
strConn = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + savePath + ";Extended Properties='Excel 12.0 Xml; HDR=YES; IMEX=1'";
else if (fileEx.Equals(".xls"))
strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + savePath + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
}
var conn = new OleDbConnection(strConn);
conn.Open();
var myCommand = new OleDbDataAdapter("select * from [Sheet1$]", strConn);
var myDataSet = new DataSet();
try
{
myCommand.Fill(myDataSet, "ExcelInfo");
}
catch (Exception ex)
{
ViewBag.error = ex.Message;
return View();
}
DataTable table = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable();
for (int i = 0; i < table.Rows.Count; i++)
{
//获取地区名称
var station = new tab_Student
{
Name = table.Rows[i][0].ToString(),
Value = table.Rows[i][1].ToString(),
};
db.tab_Student.Add(station);
}
db.SaveChanges();
ViewBag.error = "导入成功";
System.Threading.Thread.Sleep(2000);
return RedirectToAction("Index");
}