asp.net mvc 批量导入excel数据

1、引入插件<link href="~/Content/bootstrap.min.css" rel="stylesheet" /><link href="~/Content/bootstrap-fileinput/css/fileinput.css" rel="stylesheet" /><script src="~/Content/js/jquery/jquery-2.1.1.min.js"></script><script src="~/Content/js/fileinput.js"></script><script src="~/Content/js/bootstrap/bootstrap.js"></script>

2、前端代码编写<div class="container kv-main" id="" style="height:450px;margin:20px;"> <form enctype="multipart/form-data"> <div class="form-group"> <input id="file-1" type="file" class="file" data-overwrite-initial="false" data-min-file-count="1" name="file-1"> </div> </form></div>

3、前端js代码编写<script> var method = $.request猾诮沓靥("method"); if (!method) { method = "UpLoadFile"; } console.log(method); $("#file-1").fileinput({ uploadAsync: true, //language: 'zh', uploadUrl: '/CommomManage/UnloadData/' + method, //you must set a valid URL here else you will get an error allowedFileExtensions: ['xls'], overwriteInitial: false, maxFileSize: 600000000, maxFilesNum: 1, initialCaption: '请选择上传文件', allowedFileTypes: ['xls'], //allowedFileTypes: ['image', 'video', 'flash'], slugCallback: function (filename) { return filename; } }).on("filebatchselected", function (event, files) { $(this).fileinput("upload"); //自动上传功能 $.modalMsg("导入中，请稍等...", "success"); }).on("fileuploaded", function (event, data) { if (data.response) { var result = data.response; if (result.state == "success") { top.frames[iframeId].submitForm($.activeWindow()); $.modalMsg(result.message, "success"); if (result.data) { alert(result.data); } $.modalClose(); } else { //$.modalClose(); $.modalAlert(result.message, "error"); } } });; $(function () { $(".btn-file span.hidden-xs").html("浏览..."); $(".fileinput-upload span.hidden-xs").html(" 上传 "); $(".fileinput-remove span.hidden-xs").html(" 清空 "); $("div.file-drop-zone-title").html("拖放文件在这里..."); //$("#test-upload").fileinput({ // 'showPreview': false, // 'allowedFileExtensions': ['pdf', 'dox'], // 'elErrorContainer': '#errorBlock' //}); /* $("#test-upload").on('fileloaded', function(event, file, previewId, index) { alert('i = ' + index + ', id = ' + previewId + ', file = ' + file.name); }); */ $(".fileinput-remove").click(function () { $("div.file-drop-zone-title").html("拖放文件在这里..."); }); })</script>

4、后端业务处理代码编写，分为两个憧钏荭拜部分第一部分接受的方法public ActionResult UpLoadFile(HttpPostedFileBase filebase) { HttpPostedFileBase file = Request.Files["file-1"]; if (file == null) return Error("请导入文件！"); if (!file.FileName.Contains("会员信息表")) return Error("请导入正确的excel表！（模板可从主页右上角按钮下载）"); return SubmitFormOfBegin(file.InputStream); }第二部分，处理的方法#region 会员信息导入 private ActionResult SubmitFormOfBegin(Stream file) { string useId = OperatorProvider.Provider.GetCurrent().UserId; //var organizeid = HttpContext.Request.Cookies["SCid"].Value; var organizeid = OperatorProvider.Provider.GetCurrent().CompanyId; DateTime now = DateTime.Now; //if (db.WZGL_GoodsInfoOfBeginning.Count() > 0) // return Error("不可二次期初导入！"); IWorkbook workbookDown; try { workbookDown = new HSSFWorkbook(file); //HSSF适用2007以前的版本,XSSF适用2007版本及其以上的。 } catch { workbookDown = new XSSFWorkbook(file); } //基本信息 string id = null, address = null, code = null, fullName = null, briefName = null, manager = null, managerPhone = null, bz = null; bool status = false; string membershiplevel = null, telPhone = null, postalCode = null, fax = null, president = null, mobilePhone = null, mailBox = null, contacts = null, cellPhone = null, legalPerson = null; string legalPhone = null, levelTelephone = null, levelmailBox = null, qualificationlevel = null, enterprisesType = null; DateTime admissionMonth = DateTime.Now, admissionTime = DateTime.Now; try { IRow row; //会员list List<m_member> m_memberInfoList = new List<m_member>(); // int sheetCount = workbookDown.NumberOfSheets; int rowCount; ISheet sheet; ICell cell; for (int j = 0; j < sheetCount; j++) { if (workbookDown.IsSheetHidden(j)) continue; sheet = workbookDown.GetSheetAt(j); rowCount = sheet.LastRowNum; for (int i = 2; i <= rowCount; i++) { row = sheet.GetRow(i); if (row == null) continue; if (row.GetCell(0).StringCellValue == "结束") break; code = row.GetCell(0).StringCellValue.ToLower(); if (db.m_member.Any(s => s.code.ToLower() == code && s.deleteMark != true)) { return Error("第" + (j + 1) + "个sheet表中第" + i + "行的编号“" + code + "”已存在，请检查！"); } fullName = row.GetCell(1).StringCellValue.ToLower(); if (db.m_member.Any(s => s.fullName == fullName && s.organizeid == organizeid && s.deleteMark != true)) { return Error("第" + (j + 1) + "个sheet表中第" + i + "行的单位名称“" + fullName + "”已存在，请检查！"); } id = Guid.NewGuid().ToString(); code = row.GetCell(0).StringCellValue;//编号 fullName = row.GetCell(1).StringCellValue;//单位名称 briefName = row.GetCell(2).StringCellValue;//简称 membershiplevel = row.GetCell(3).StringCellValue;//会员级别 string membershiplevelId = (from a in db.Sys_ItemsDetail where a.F_ItemName == membershiplevel select a.F_Id).FirstOrDefault(); if (membershiplevelId.IsEmpty()) { return Error("该会员级别“" + membershiplevel + "”没定义"); } address = row.GetCell(4).StringCellValue;//地址 row.GetCell(5).SetCellType(CellType.String); telPhone = row.GetCell(5).StringCellValue;//联系电话 postalCode = row.GetCell(6).StringCellValue;//邮政编码 fax = row.GetCell(7).StringCellValue;//传真 president = row.GetCell(8).StringCellValue;//总经理 row.GetCell(9).SetCellType(CellType.String); mobilePhone = row.GetCell(9).StringCellValue;//总经理手机 mailBox = row.GetCell(10).StringCellValue;//邮箱 contacts = row.GetCell(11).StringCellValue;//联系人 row.GetCell(12).SetCellType(CellType.String); cellPhone = row.GetCell(12).StringCellValue;//联系人手机 admissionTime = sheet.GetRow(i).GetCell(13).DateCellValue; //入会时间 //admissionMonth = admissionTime.Year + "-"+ admissionTime.Day; admissionMonth = row.GetCell(14).DateCellValue;//入会月份 qualificationlevel = row.GetCell(15).StringCellValue;//资质等级 string qualificationlevelId = (from a in db.Sys_ItemsDetail where a.F_ItemName == qualificationlevel select a.F_Id).FirstOrDefault(); if (qualificationlevelId.IsEmpty()) { return Error("该资质等级“" + qualificationlevel + "”没定义"); } enterprisesType = row.GetCell(16).StringCellValue;//企业类型 string enterprisesTypeId = (from a in db.Sys_ItemsDetail where a.F_ItemName == enterprisesType select a.F_Id).FirstOrDefault(); if (enterprisesTypeId.IsEmpty()) { return Error("该企业类型“" + enterprisesType + "”没定义"); } legalPerson = row.GetCell(17).StringCellValue;//法人代表 row.GetCell(18).SetCellType(CellType.String); legalPhone = row.GetCell(18).StringCellValue;//法人手机 levelmailBox = row.GetCell(19).StringCellValue;//法人邮箱 //启用状态 if (row.GetCell(20).StringCellValue.Equals("是")) { status = true; } //负责人 bz = row.GetCell(21).StringCellValue; m_memberInfoList.Add(new m_member { id = id, organizeid = organizeid, code = code, fullName = fullName, briefName = briefName, membershiplevel = membershiplevelId, address = address, telPhone = telPhone, postalCode = postalCode, fax = fax, president = president, mobilePhone = mobilePhone, mailBox = mailBox, contacts = contacts, cellPhone = cellPhone, admissionMonth = admissionMonth, admissionTime = admissionTime, legalPerson = legalPerson, legalPhone = legalPhone, levelTelephone = levelTelephone, levelmailBox = levelmailBox, qualificationlevel = qualificationlevelId, enterprisesType = enterprisesTypeId, status = status, bz = bz, creatorTime = now, creatorUserId = useId }); } } db.m_member.AddRange(m_memberInfoList); db.SaveChanges(); return Success("导入成功！"); } catch { return Error("导入失败,请检查下格式！"); } } #endregion

5、上传效果