页面代码：
<form id="form1" enctype="multipart/form-data"> <div style="float:right"> <button type="button" class="btn btn-primary" onclick="$('#fileUpload').click()" id="reviewFile">浏览</button> <button class="btn btn-primary" type="button" style="margin-left:5px;height:30px;" id="dataExport">批量导入</button> <input type="button" class="btn btn-primary" style="margin-left:5px;height:30px;" id="downLoad" value="下载模板"> </div> <div style="float:right;margin-top:5px"> <input id="fileUpload" name="fileUpload" type="file" style="display:none" /> <input id="fileText" type="text" class="form-control" disabled /> </div> <script> $("#fileUpload").change(function () { $("#fileText").val($(this).val()); }) </script> </form>
js代码：
//导入excel数据 $("#dataExport").click(function () { var formData = new FormData($('form')[0]); $.ajax({ url: '/BaseInfoPage/GetAllDataFromExcel', type: 'POST', xhr: function () { return $.ajaxSettings.xhr(); }, data: formData, cache: false, contentType: false, processData: false, success: function (data) { if (data == "导入成功!") { layer.msg(data, { icon: 1, time: 5000 }, function () { location.reload(); //刷新父页面 第二个参数设置msg显示的时间长短 }); } else { layer.msg(data, { icon: 0, time: 5000 }, function () { return; }); } }, error: function (e) { layer.msg(e, { icon: 0, time: 5000 }, function () { return; }); } }); })
c#后台代码：
public string GetAllDataFromExcel(HttpPostedFileBase fileUpload) { if (fileUpload == null) { return "文件为空!"; } try { List<LargeTransportLicenseParam> dataList = new List<LargeTransportLicenseParam>(); List<string> rNameLists = new List<string>(); List<string> ltlLists = new List<string>(); List<string> strLId = large_util.QueryLargeIds();//获取所有大件运输许可编号 List<string> strRname = large_util.QueryReName();//获取所有省份 int result = 0; int result2 = 0; string fileExt = Path.GetExtension(fileUpload.FileName).ToLower(); string fileName = fileUpload.FileName; string filePath = CSysCfg.exFilePath; if (!Directory.Exists(filePath)) { Directory.CreateDirectory(filePath); } //保存模板到服务器 fileUpload.SaveAs(filePath + "\" + fileName); if (fileExt == ".xls" || fileExt == ".xlsx") { //1.创建IWorkbook IWorkbook Workbook; using (FileStream fileStream = new FileStream(filePath + "\" + fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式 if (fileExt == ".xls") { Workbook = new HSSFWorkbook(fileStream); } else if (fileExt == ".xlsx") { Workbook = new XSSFWorkbook(fileStream); } else { Workbook = null; } } Dictionary<List<string>, List<string>> dataDic = new Dictionary<List<string>, List<string>>(); //遍历每个Sheet for (int i = 0; i < Workbook.NumberOfSheets; i++) { //获取每个Sheet对象 ISheet sheet = Workbook.GetSheetAt(i); //获取每个工作表中的行 //第一，二行是列名舍去，从第三行开始读取 LastRowNum 是当前表的总行数-1（注意） for (int j = 2; j <= sheet.LastRowNum; j++) { IRow row = sheet.GetRow(j); #region 数据赋值 判断 if (row != null) { LargeTransportLicenseParam param = new LargeTransportLicenseParam(); param.loginId = Cookie.Value.Split(',')[0].ToString(); if (strLId.Contains(row.GetCell(0).ToString()))//判断如果数据库包含此值 { return "第" + (row.RowNum + 1) + "行第" + 1 + "列数据库具有相同的值!"; } else if (row.GetCell(0) == null) { return "第" + (row.RowNum + 1) + "行第" + 1 + "值为空!"; } else { param.ltlId = row.GetCell(0).ToString();//申请编号 必填 } //车牌号码 if (row.GetCell(1) == null) { return "第" + (row.RowNum + 1) + "行第" + 2 + "值为空!"; } else { param.ltlPlateId = row.GetCell(1).ToString();//车牌号码 必填 } //开始时间 if (row.GetCell(2) == null) { return "第" + (row.RowNum + 1) + "行第" + 3 + "列值为空!"; } else if (row.GetCell(2).CellType == CellType.String) { return "第" + (row.RowNum + 1) + "行第" + 3 + "列请输入正确的日期格式!"; } else { param.ltlStarteTime = row.GetCell(2).DateCellValue.ToString();//当输入文本日期时，通过DateCellValue得到他的日期格式 } //结束时间 if (row.GetCell(3) == null) { return "第" + (row.RowNum + 1) + "行第" + 4 + "值为空!"; } else if (row.GetCell(3).CellType == CellType.String) { return "第" + (row.RowNum + 1) + "行第" + 4 + "列请输入正确的日期格式!"; } else { param.ltlEndTime = row.GetCell(3).DateCellValue.ToString();//结束时间 必填 } //途径省份 if (row.GetCell(4) == null) { return "第" + (row.RowNum + 1) + "行第" + 5 + "值为空!"; } string strs = row.GetCell(4).ToString(); string[] arr = strs.Split(','); if (arr[0] != null) { for (int k = 0; k < arr.Length; k++) { rNameLists.Add(arr[k]); ltlLists.Add(row.GetCell(0).ToString()); } } param.ltlPassageRoute = row.GetCell(5) == null ? "" : row.GetCell(5).ToString();//通行路线 param.ltlRoadTransportNum = row.GetCell(6) == null ? "" : row.GetCell(6).ToString();//道路运输证号 param.ltlBrandModel = row.GetCell(7) == null ? "" : row.GetCell(7).ToString();//厂牌型号 ICell cell = row.GetCell(8);//车长(M) if (cell == null) { param.ltlCarLength = 0; } else if (row.GetCell(8).CellType == CellType.String) { return "第" + (row.RowNum + 1) + "行第" + 9 + "列数据类型错误，必须为数值"; } else { param.ltlCarLength = Convert.ToInt32(row.GetCell(8).ToString());//车长(M) } ICell cell9 = row.GetCell(9);//车宽 if (cell9 == null) { param.ltlCarWidth = 0; } else if (row.GetCell(9).CellType == CellType.String) { return "第" + (row.RowNum + 1) + "行第" + 10 + "列数据类型错误，必须为数值"; } else { param.ltlCarWidth = Convert.ToInt32(row.GetCell(9).ToString());//车长(M) } ICell cell10 = row.GetCell(10);//车高 if (cell10 == null) { param.ltlCarHeight = 0; } else if (row.GetCell(10).CellType == CellType.String) { return "第" + (row.RowNum + 1) + "行第" + 11 + "列数据类型错误，必须为数值"; } else { param.ltlCarHeight = Convert.ToInt32(row.GetCell(10).ToString()); } ICell cell11 = row.GetCell(11);//整备质量 if (cell11 == null) { param.ltlCurbQuality = 0; } else if (row.GetCell(11).CellType == CellType.String) { return "第" + (row.RowNum + 1) + "行第" + 12 + "列数据类型错误，必须为数值"; } else { param.ltlCurbQuality = Convert.ToInt32(row.GetCell(11).ToString()); } ICell cell12 = row.GetCell(12);//荷载质量 if (cell12 == null) { param.ltlLoadQuality = 0; } else if (row.GetCell(12).CellType == CellType.String) { return "第" + (row.RowNum + 1) + "行第" + 13 + "列数据类型错误，必须为数值"; } else { param.ltlLoadQuality = Convert.ToInt32(row.GetCell(12).ToString()); } ICell cell13 = row.GetCell(13);//牵引质量 if (cell13 == null) { param.ltlTractionQuality = 0; } else if (row.GetCell(13).CellType == CellType.String) { return "第" + (row.RowNum + 1) + "行第" + 14 + "列数据类型错误，必须为数值"; } else { param.ltlTractionQuality = Convert.ToInt32(row.GetCell(13).ToString()); } param.ltlRoadTransportImg = row.GetCell(14) == null ? "" : row.GetCell(14).ToString();//道路运输证照片 param.ltlDrivingLicenseImg = row.GetCell(15) == null ? "" : row.GetCell(15).ToString();//机动车行驶证照片 ICell cell16 = row.GetCell(16);//车货总重量(吨) if (cell16 == null) { return "第" + (row.RowNum + 1) + "行第" + 17 + "列值为空!"; } else if (row.GetCell(16).CellType == CellType.String) { return "第" + (row.RowNum + 1) + "行第" + 17 + "列数据类型错误，必须为数值"; } else { param.ltlTotalWeigth = Convert.ToInt32(row.GetCell(16).ToString()); } ICell cell17 = row.GetCell(17);//轴距 if (cell17 == null) { param.ltlWheelbase = 0; } else if (row.GetCell(17).CellType == CellType.String) { return "第" + (row.RowNum + 1) + "行第" + 18 + "列数据类型错误，必须为数值"; } else { param.ltlWheelbase = Convert.ToInt32(row.GetCell(17).ToString()); } ICell cell18 = row.GetCell(18);//车货最大长(M) if (cell16 == null) { return "第" + (row.RowNum + 1) + "行第" + 19 + "列值为空!"; } else if (row.GetCell(18).CellType == CellType.String) { return "第" + (row.RowNum + 1) + "行第" + 19 + "列数据类型错误，必须为数值"; } else { param.ltlMaxLength = Convert.ToInt32(row.GetCell(18).ToString()); } ICell cell19 = row.GetCell(19);//轴载 if (cell19 == null) { param.ltlAxleLoad = 0; } else if (row.GetCell(19).CellType == CellType.String) { return "第" + (row.RowNum + 1) + "行第" + 20 + "列数据类型错误，必须为数值"; } else { param.ltlAxleLoad = Convert.ToInt32(row.GetCell(19).ToString()); } ICell cell20 = row.GetCell(20);//车货最大宽(M) if (cell20 == null) { return "第" + (row.RowNum + 1) + "行第" + 21 + "列值为空!"; } else if (row.GetCell(20).CellType == CellType.String) { return "第" + (row.RowNum + 1) + "行第" + 21 + "列数据类型错误，必须为数值"; } else { param.ltlMaxWidth = Convert.ToInt32(row.GetCell(20).ToString()); } param.ltlGoodsInfo = row.GetCell(21) == null ? "" : row.GetCell(21).ToString();//货物信息 ICell cell22 = row.GetCell(22);//车货最大高(M) if (cell22 == null) { return "第" + (row.RowNum + 1) + "行第" + 23 + "列值为空!"; } else if (row.GetCell(22).CellType == CellType.String) { return "第" + (row.RowNum + 1) + "行第" + 23 + "列数据类型错误，必须为数值"; } else { param.ltlMaxHeight = Convert.ToInt32(row.GetCell(22).ToString()); } ICell cell23 = row.GetCell(23);//货物重量(吨) if (cell23 == null) { param.ltlGoodsWeight = 0; } else if (row.GetCell(23).CellType == CellType.String) { return "第" + (row.RowNum + 1) + "行第" + 24 + "列数据类型错误，必须为数值"; } else { param.ltlGoodsWeight = Convert.ToInt32(row.GetCell(23).ToString()); } ICell cell24 = row.GetCell(24);//货物最大长(M) if (cell24 == null) { param.ltlGoodsMaxLenght = 0; } else if (row.GetCell(24).CellType == CellType.String) { return "第" + (row.RowNum + 1) + "行第" + 25 + "列数据类型错误，必须为数值"; } else { param.ltlGoodsMaxLenght = Convert.ToInt32(row.GetCell(24).ToString()); } ICell cell25 = row.GetCell(25);//货物最大宽(M) if (cell25 == null) { param.ltlGoodsMaxWidth = 0; } else if (row.GetCell(25).CellType == CellType.String) { return "第" + (row.RowNum + 1) + "行第" + 26 + "列数据类型错误，必须为数值"; } else { param.ltlGoodsMaxWidth = Convert.ToInt32(row.GetCell(25).ToString()); } ICell cell26 = row.GetCell(26);//货物最大高(M) if (cell26 == null) { param.ltlGoodsMaxHeight = 0; } else if (row.GetCell(26).CellType == CellType.String) { return "第" + (row.RowNum + 1) + "行第" + 27 + "列数据类型错误，必须为数值"; } else { param.ltlGoodsMaxHeight = Convert.ToInt32(row.GetCell(26).ToString()); } ICell cell27 = row.GetCell(27);//轮胎数 if (cell27 == null) { param.ltlTireNumber = 0; } else if (row.GetCell(27).CellType == CellType.String) { return "第" + (row.RowNum + 1) + "行第" + 28 + "列数据类型错误，必须为数值"; } else { param.ltlTireNumber = Convert.ToInt32(row.GetCell(27).ToString()); } ICell cell28 = row.GetCell(28);//轴数 if (cell28 == null) { param.ltlAxle = 0; } else if (row.GetCell(28).CellType == CellType.String) { return "第" + (row.RowNum + 1) + "行第" + 29 + "列数据类型错误，必须为数值"; } else { param.ltlAxle = Convert.ToInt32(row.GetCell(28).ToString()); } param.ltlGoodsImg = row.GetCell(29) == null ? "" : row.GetCell(29).ToString();//车货照片 param.ltlBusinessLicenseNum = row.GetCell(30) == null ? "" : row.GetCell(30).ToString();//经营许可证编号 param.ltlManagerName = row.GetCell(31) == null ? "" : row.GetCell(31).ToString();//经办人姓名 param.ltlManagerIdCard = row.GetCell(32) == null ? "" : row.GetCell(32).ToString();//经办人身份证 param.ltlManagerPhone = row.GetCell(33) == null ? "" : row.GetCell(33).ToString();//联系电话 param.ltlOpenImg = row.GetCell(34) == null ? "" : row.GetCell(34).ToString();//营业执照照片 param.ltlPowerImg = row.GetCell(35) == null ? "" : row.GetCell(35).ToString();//授权书照片 param.ltlManagerIdCardImg = row.GetCell(36) == null ? "" : row.GetCell(36).ToString();//授权书照片 param.ltlBusinessLicenseImg = row.GetCell(37) == null ? "" : row.GetCell(37).ToString();//经营许可证照片 dataList.Add(param); } else { return "暂无数据!"; } #endregion } } result = large_util.AddLargeInfo2(dataList); if (result == 1) { if (rNameLists != null && ltlLists != null) { result2 = large_util.Add2(ltlLists, rNameLists); } else { return "暂无数据!"; } } return "导入成功!"; } else { return "只可以选择Excel文件!"; } } catch (Exception ex) { throw ex; } }
//批量新增 public int AddLargeInfo2(List<LargeTransportLicenseParam> LargeData) { int result = 0; var lgId = ""; try { foreach (var item in LargeData) { lgId = item.ltlId; if (item != null) { string oldName = System.IO.Path.GetFileName(item.ltlOpenImg); string expendName = System.IO.Path.GetExtension(oldName); string filePath = ""; if (oldName != "" || oldName == null) { filePath = CSysCfg.lFilePath + "\" + "ltlOpenImg" + item.ltlId + expendName; } string oldName1 = System.IO.Path.GetFileName(item.ltlPowerImg); string expendName1 = System.IO.Path.GetExtension(oldName1); string filePath1 = ""; if (oldName1 != "" || oldName1 == null) { filePath1 = CSysCfg.lFilePath + "\" + "ltlPowerImg" + item.ltlId + expendName1; } string oldName2 = System.IO.Path.GetFileName(item.ltlManagerIdCardImg); string expendName2 = System.IO.Path.GetExtension(oldName2); string filePath2 = ""; if (oldName2 != "" || oldName2 == null) { filePath2 = CSysCfg.lFilePath + "\" + "ltlManagerIdCardImg" + item.ltlId + expendName2; } string oldName3 = System.IO.Path.GetFileName(item.ltlBusinessLicenseImg); string expendName3 = System.IO.Path.GetExtension(oldName3); string filePath3 = ""; if (oldName3 != "" || oldName3 == null) { filePath3 = CSysCfg.lFilePath + "\" + "ltlBusinessLicenseImg" + item.ltlId + expendName3; } string oldName4 = System.IO.Path.GetFileName(item.ltlRoadTransportImg); string expendName4 = System.IO.Path.GetExtension(oldName4); string filePath4 = ""; if (oldName4 != "" || oldName4 == null) { filePath4 = CSysCfg.lFilePath + "\" + "ltlRoadTransportImg" + item.ltlId + expendName4; } string oldName5 = System.IO.Path.GetFileName(item.ltlDrivingLicenseImg); string expendName5 = System.IO.Path.GetExtension(oldName5); string filePath5 = ""; if (oldName5 != "" || oldName5 == null) { filePath5 = CSysCfg.lFilePath + "\" + "ltlDrivingLicenseImg" + item.ltlId + expendName5; } string oldName7 = System.IO.Path.GetFileName(item.ltlGoodsImg); string expendName7 = System.IO.Path.GetExtension(oldName7); string filePath7 = ""; if (oldName7 != "" || oldName7 == null) { filePath7 = CSysCfg.lFilePath + "\" + "ltlGoodsImg" + item.ltlId + expendName7; } LargeTransportLicense large = new LargeTransportLicense { ltl_Id = item.ltlId, ltl_PlateId = item.ltlPlateId, ltl_StarteTime = DateTime.Parse(item.ltlStarteTime), ltl_EndTime = DateTime.Parse(item.ltlEndTime), ltl_PassageRoute = item.ltlPassageRoute, ltl_BusinessLicenseNum = item.ltlBusinessLicenseNum, ltl_ManagerName = item.ltlManagerName, ltl_ManagerIdCard = item.ltlManagerIdCard, ltl_ManagerPhone = item.ltlManagerPhone, ltl_OpenImg = filePath, ltl_PowerImg = filePath1, ltl_ManagerIdCardImg = filePath2, ltl_BusinessLicenseImg = filePath3, ltl_RoadTransportNum = item.ltlRoadTransportNum, ltl_BrandModel = item.ltlBrandModel, ltl_CarLength = item.ltlCarLength * 1000, ltl_CarWidth = item.ltlCarWidth * 1000, ltl_CarHeight = item.ltlCarHeight * 1000, ltl_CurbQuality = item.ltlCurbQuality * 1000, ltl_LoadQuality = item.ltlLoadQuality * 1000, ltl_TractionQuality = item.ltlTractionQuality * 1000, ltl_RoadTransportImg = filePath4, ltl_DrivingLicenseImg = filePath5, ltl_TotalWeigth = item.ltlTotalWeigth * 1000, ltl_Wheelbase = item.ltlWheelbase, ltl_MaxLength = item.ltlMaxLength * 1000, ltl_AxleLoad = item.ltlAxleLoad, ltl_MaxWidth = item.ltlMaxWidth * 1000, ltl_GoodsInfo = item.ltlGoodsInfo, ltl_MaxHeight = item.ltlMaxHeight * 1000, ltl_GoodsWeight = item.ltlGoodsWeight * 1000, ltl_GoodsMaxLenght = item.ltlGoodsMaxLenght * 1000, ltl_GoodsMaxWidth = item.ltlGoodsMaxWidth * 1000, ltl_GoodsMaxHeight = item.ltlGoodsMaxHeight * 1000, ltl_TireNumber = item.ltlTireNumber, ltl_Axle = item.ltlAxle, ltl_GoodsImg = filePath7 }; using (Entities db = new Entities()) { db.LargeTransportLicense.Add(large); db.SaveChanges(); LogUtil.AddLogs("大件运输许可", "添加", "添加大件运输许可:" + item.ltlId); result = 1; } } else { result = 0; } } } catch (Exception ex) { LogUtil.AddLogs("大件运输许可", "添加", "添加大件运输许可:" + lgId + "失败！"); LogUtil.AddErrorLogs("大件运输许可", "添加", "添加大件运输许可:" + lgId + "异常：" + ex.Message); throw; } return result; } //批量新增省份 public int Add2(List<string> lId, List<string> rNames) { int result = 0; string ltlId = "";//大件运输id var reIds = "";//行政区划id try { LargeRegionRelation datas = null; using (Entities db = new Entities()) { if (lId != null && rNames != null) { for (int i = 0; i < rNames.Count(); i++) { ltlId = lId[i]; string str = rNames[i]; string regStr = "省"; Regex r = new Regex(regStr); Match m = r.Match(str);//str是否包含省 string str1; int index = rNames[i].LastIndexOf("省");//获取最后一个字符 if (index >= 0)//判断省份最后一个字符是否是省 { if (m.Success)//如是则截取，再进行新增 { //绿色部分与紫色部分取一种即可。 str = str.Replace(regStr, ""); str1 = str.Substring(0, m.Index); reIds = (from p in db.Region where p.region_Name == str1 select p.region_id).FirstOrDefault(); datas = new LargeRegionRelation { lr_ltlId = ltlId, lr_RegionId = reIds }; db.LargeRegionRelation.Add(datas); db.SaveChanges(); result = 1; } } else { reIds = (from p in db.Region where p.region_Name == rNames[i] select p.region_id).FirstOrDefault(); datas = new LargeRegionRelation { lr_ltlId = ltlId, lr_RegionId = reIds }; db.LargeRegionRelation.Add(datas); db.SaveChanges(); result = 1; } } } else { result = 0; } } return result; } catch (Exception) { throw; } }