<div class="modal-content"> <div class="modal-header"> <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">×</span></button> <h4 class="modal-title title">导入</h4> </div> <div class="modal-body"> <div class="row"> <div class="col-lg-12"> <form id="defaultForm" method="" class="form-horizontal recoveryNodeForm" action=""> <div class="col-lg-12"> <div class="form-group"> <label class="col-lg-3 control-label">导入文件</label> <div class="col-lg-6"> <input type="file" class="form-control" style="height:36px;" name="uploadFile" id="uploadFile"/> </div> <button type="button" class="btn btn-primary" id="uploadExcel">上传</button> </div> </div> <input type="hidden" name="pkId" value="" /> </form> </div> </div> <div> <span><b>导入结果反馈</b></span> <ul id="exportResult"> </ul> </div> </div>
uploadExcel : function () { $("#uploadExcel").on("click","",function () { $(".recoveryNodeForm").data("bootstrapValidator").validate(); var flag = $(".recoveryNodeForm").data("bootstrapValidator").isValid(); if(!flag){ //未通过验证 return false; } var fileObj = document.getElementById("uploadFile").files[0]; var formFile = new FormData(); formFile.append("file", fileObj); var data = formFile; $.ajax({ url: ctx +'/recovery/netStorage/uploadFile.mvc', data: data, type: "Post", dataType: "json", cache: false,//上传文件无需缓存 processData: false,//用于对data参数进行序列化处理 这里必须false contentType: false, //必须 success: function (result) { var htmlstr = ''; if(result.result==false){ for(var i=0;i<result.data.length;i++){ htmlstr += '<li>'+result.data[i]+'</li>'; } } else { htmlstr = '<li>上传成功</li>'; } $('#exportResult').html(htmlstr); }, error: function(XMLHttpRequest, textStatus, errorThrown){ DialogUtil.error("系统错误"); } }); }); }
validatorForm :function () {
$(".recoveryNodeForm").bootstrapValidator({
message: 'This value is not valid',
live: 'submitted',
fields: {/*验证*/
uploadFile: {
message: '导入文件无效',
validators: {
notEmpty: {/*非空提示*/
message: '导入文件不能为空'
},
regexp: {
regexp: /.xlsx$/,
message: '导入文件类型必须是excel'
}
}
}
}
});
}
@RequestMapping("/uploadFile") @ResponseBody public StandardResult uploadFile(@RequestParam("file") MultipartFile file) throws IOException{ SysUser sysUser = BaseUtil.getCurrentUser(); XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream()); StandardResult result = customerBatteryService.insertDataByExcel(workbook,sysUser); return result; }
public StandardResult insertDataByExcel(XSSFWorkbook workbook, SysUser sysUser){ StandardResult result = checkExcel(workbook); if(result.getResult() == false){ return result; } //A-1订单信息sheet 5列 XSSFSheet sheet0 = workbook.getSheetAt(0); CustomerBattery customerBattery; String excelId = BaseUtil.getUUID(); String departmentId = sysUser.getDepartmentId(); for(int i=3;i<=sheet0.getLastRowNum();i++){//从第4行读起,读到最后一行 customerBattery = new CustomerBattery(); //读取指定索引行的值 XSSFRow row = sheet0.getRow(i); if(row == null) continue; if(checkAllCellEmpty(row,0,4)) continue; customerBattery.setPkId(BaseUtil.getUUID()); customerBattery.setExcelId(excelId); customerBattery.setDepartmentId(departmentId); customerBattery.setOrderNumber(row.getCell(0).toString());//订单号 customerBattery.setKsName(row.getCell(1).toString());//客户名称 customerBattery.setSupplyName(row.getCell(2).toString());//供货电池厂名称 customerBattery.setPackNumber((int)row.getCell(3).getNumericCellValue());//所含电池包数 customerBattery.setModelNumber((int)row.getCell(4).getNumericCellValue());//所含电池模块数 customerBattery.setDelFlag("0"); customerBattery.setStatus("0"); customerBattery.setCreateUser(sysUser.getTruename()); customerBattery.setCreateDate(new Date()); if("上汽通用五菱".equals(row.getCell(1).toString().toString())){ customerBattery.setInterfaceName("五菱"); }else if("江铃汽车".equals(row.getCell(1).toString().toString())){ customerBattery.setInterfaceName("江铃"); } customerBatteryDao.insert(customerBattery); } //A-2售后电池信息sheet 3列 XSSFSheet sheet1 = workbook.getSheetAt(1); CustomerBatteryDetail customerBatteryDetail; for(int i=3;i<=sheet1.getLastRowNum();i++){//从第4行读起,读到最后一行 customerBatteryDetail = new CustomerBatteryDetail(); //读取指定索引行的值 XSSFRow row = sheet1.getRow(i); if(row == null) continue; if(checkAllCellEmpty(row,0,2)) continue; customerBatteryDetail.setPkId(BaseUtil.getUUID()); customerBatteryDetail.setExcelId(excelId); customerBatteryDetail.setCustomerType(row.getCell(0).toString());//售后产品类型(电池包/电池模块) customerBatteryDetail.setBatteryModel(row.getCell(1).toString());//电池型号 customerBatteryDetail.setBatteryCode(row.getCell(2).toString());//电池编码 customerBatteryDetail.setDelFlag("0"); customerBatteryDetail.setCreateUser(sysUser.getTruename()); customerBatteryDetail.setCreateDate(new Date()); customerBatteryDetailDao.insert(customerBatteryDetail); } //A-3 电池模块编码信息sheet 3列 XSSFSheet sheet2 = workbook.getSheetAt(2); ModulePackRelation modulePackRelation; for(int i=3;i<sheet2.getLastRowNum();i++){//从第4行读起,读到最后一行 XSSFRow row = sheet2.getRow(i); if(row == null) continue; if(checkAllCellEmpty(row,0,2)) continue; modulePackRelation = new ModulePackRelation(); modulePackRelation.setPkId(BaseUtil.getUUID()); modulePackRelation.setExcelId(excelId); modulePackRelation.setModuleCode(row.getCell(0).toString());//电池模块编码 modulePackRelation.setModuleModel(row.getCell(1).toString());//电池模块型号 modulePackRelation.setPackCode(row.getCell(2).toString());//所属电池包编码 modulePackRelation.setDelFlag("0"); modulePackRelation.setCreateDate(new Date()); modulePackRelation.setCreateUser(sysUser.getTruename()); modulePackRelation.setType("2"); modulePackRelationDao.insert(modulePackRelation); } //A-4 单体电池编码信息 XSSFSheet sheet3 = workbook.getSheetAt(3); MonomerModuleRelation monomerModuleRelation; for(int i=3;i<=sheet3.getLastRowNum();i++) {//从第4行读起,读到最后一行 XSSFRow row = sheet3.getRow(i); if (row == null) continue; if (checkAllCellEmpty(row, 0, 2)) continue; monomerModuleRelation = new MonomerModuleRelation(); monomerModuleRelation.setPkId(BaseUtil.getUUID()); monomerModuleRelation.setExcelId(excelId); monomerModuleRelation.setMonomerCode(row.getCell(0).toString());//单体电池编码 monomerModuleRelation.setModuleCode(row.getCell(1).toString());//所属电池模块编码 monomerModuleRelation.setDelFlag("0"); monomerModuleRelation.setCreateDate(new Date()); monomerModuleRelation.setCreateUser(sysUser.getTruename()); monomerModuleRelation.setType("2"); monomerModuleRelationDao.insert(monomerModuleRelation); } return result; }
private StandardResult checkExcel(XSSFWorkbook workbook) { StandardResult result = new StandardResult(); //错误信息汇总 LinkedHashSet hs1 = new LinkedHashSet(); //找不到型号汇总 LinkedHashSet hs2 = new LinkedHashSet(); //客户名称输入错误 LinkedHashSet hsProductor = new LinkedHashSet(); //模块对应包不存在 LinkedHashSet hs3 = new LinkedHashSet(); //所含电池包数 int packCountAll = 0; //所含模块数 int moduleCountAll= 0; boolean errorFlag = true; //电池包编码对应的电池包型号 Map<String, String> packMap = new HashMap<>(); List<Map<String, String>> listPack = new ArrayList<Map<String, String>>(); //电池编码错误 LinkedHashSet hasPack = new LinkedHashSet(); //电池模块编码错误 LinkedHashSet hasModule = new LinkedHashSet(); //单体电池编码错误 LinkedHashSet hasMonomer = new LinkedHashSet(); //A-1订单信息sheet 5列 EntityWrapper<PackRecord> ewpr; List<PackRecord> packRecords; EntityWrapper<CustomerBattery> ewppi; List<CustomerBattery> customerBatteryInfos ; if(workbook.getNumberOfSheets() < 4) { result.setResult(false); LinkedHashSet sheetHs = new LinkedHashSet(); sheetHs.add("数据sheet缺失,请检查"); result.setData(sheetHs); return result; } XSSFSheet sheet0 = workbook.getSheetAt(0); for(int i=3;i<=sheet0.getLastRowNum();i++){//从第4行读起,读到最后一行 //读取指定索引行的值 XSSFRow row = sheet0.getRow(i); if(row == null) continue; if(checkAllCellEmpty(row,0,4)) continue; if(i == 4){ if(checkAllCellEmpty(row,0,4) == false){ result.setResult(false); LinkedHashSet sheetHs = new LinkedHashSet(); sheetHs.add("只能填写一个订单,请检查"); result.setData(sheetHs); return result; } } if(checkCellEmpty(row.getCell(0))) hs1.add("订单号不能为空"); if(checkCellEmpty(row.getCell(1))) { hs1.add("客户名称不能为空"); }else if(!"上汽通用五菱".equals(row.getCell(1).toString()) && !"江铃汽车".equals(row.getCell(1).toString())){ hs1.add("客户名称错误"); }else{ hsProductor.add(row.getCell(1).toString()); if(!hsProductor.contains(row.getCell(1).toString())){ hs1.add("客户名称必须相同"); }else{ //如果同一订单号,客户名称必须相同 EntityWrapper<CustomerBattery> ewprcb; ewprcb = new EntityWrapper<>(); List<CustomerBattery> customerBatteryList =customerBatteryDao.selectPage(new Page<CustomerBattery>(1,1),ewprcb.eq("ORDER_NUMBER",row.getCell(0).toString()).eq("del_flag", CommonConstants.DEL_FLAG)); if(customerBatteryList.size() >0 && ! customerBatteryList.isEmpty()){ String kaName = customerBatteryList.get(0).getKsName(); if(! kaName.equals(row.getCell(1).toString())){ hs1.add("同一订单号客户名称必须相同"); } } } }; if(checkCellEmpty(row.getCell(2))){ hs1.add("供货电池厂名称不能为空"); }else{ EntityWrapper<BatteryProductor> ewprbp; ewprbp = new EntityWrapper<>(); List<BatteryProductor> batteryProductorList =batteryProductorDao.selectPage(new Page<BatteryProductor>(1, 1),ewprbp.eq("del_flag", CommonConstants.DEL_FLAG)); boolean flag = false; for(BatteryProductor batteryProductor :batteryProductorList){ if(row.getCell(2).toString().equals(batteryProductor.getProductorName())){ flag = true; break; } } if(flag == false){ hs1.add("供货电池厂名称在电池厂商备案中不存在"); }else{ //如果同一订单号,客户名称必须相同 EntityWrapper<CustomerBattery> ewprcb; ewprcb = new EntityWrapper<>(); List<CustomerBattery> customerBatteryList =customerBatteryDao.selectPage(new Page<CustomerBattery>(1,1),ewprcb.eq("ORDER_NUMBER",row.getCell(0).toString()).eq("del_flag", CommonConstants.DEL_FLAG)); if(customerBatteryList.size() >0 && ! customerBatteryList.isEmpty()){ String supplyName = customerBatteryList.get(0).getSupplyName(); if(! supplyName.equals(row.getCell(2).toString())){ hs1.add("同一订单号供货电池厂名称必须相同"); } } } } if(checkCellEmpty(row.getCell(3))){ hs1.add("所含电池包数不能为空"); }else if(row.getCell(3).getCellType() != 0){ errorFlag = false; hs1.add("所含电池包数必须为数字"); }else{ packCountAll = (int)row.getCell(3).getNumericCellValue(); } System.out.print(row.getCell(4).getCellType()); if(checkCellEmpty(row.getCell(4))) { hs1.add("所含电池模块数不能为空"); }else if(row.getCell(4).getCellType() != 0){ errorFlag = false; hs1.add("所含电池模块数必须为数字"); }else{ moduleCountAll = (int)row.getCell(4).getNumericCellValue(); } } hs2 = new LinkedHashSet(); //A-2售后电池信息3列 XSSFSheet sheet1 = workbook.getSheetAt(1); //电池包计数 int packCounts = 0; //模块计数 int moduleCounts = 0; for(int i=3;i<=sheet1.getLastRowNum();i++){//从第4行读起,读到最后一行 //读取指定索引行的值 XSSFRow row = sheet1.getRow(i); if(row == null) continue; if(checkAllCellEmpty(row,0,2)) continue; if(checkCellEmpty(row.getCell(0))){ hs1.add("售后产品类型(电池包/电池模块)不能为空"); }else if(! "电池包".equals(row.getCell(0).toString()) && ! "电池模块".equals(row.getCell(0).toString())){ hs1.add("售后产品类型(电池包/电池模块)填写错误"); }else{ if("电池包".equals(row.getCell(0).toString())){ if(packCountAll == 0 && errorFlag != false){ hs1.add("订单中不存在电池包"); }else{ packCounts++; } }else{ if(moduleCountAll == 0 && errorFlag != false){ hs1.add("订单中不存在电池模块"); }else{ moduleCounts++; } } } if(checkCellEmpty(row.getCell(1))) { hs1.add("电池型号不能为空"); }else { if("电池包".equals(row.getCell(0).toString())){ EntityWrapper<PackRecord> prewmpi = new EntityWrapper<PackRecord>(); List<PackRecord> packRecordList= packDao.selectPage(new Page<PackRecord>(1,1),prewmpi.eq("PACK_MODEL",row.getCell(1).toString()).eq("del_flag", CommonConstants.DEL_FLAG)); if(packRecordList.isEmpty()){ hs2.add(row.getCell(1).toString()); } }else { EntityWrapper<ModuleRecord> mrewmpi = new EntityWrapper<ModuleRecord>(); List<ModuleRecord> moduleRecordList= moduleDao.selectPage(new Page<ModuleRecord>(1,1),mrewmpi.eq("MODULE_MODEL",row.getCell(1).toString()).eq("del_flag", CommonConstants.DEL_FLAG)); if(moduleRecordList.isEmpty()){ hs2.add(row.getCell(1).toString()); } } }; if(checkCellEmpty(row.getCell(2))) { hs1.add("电池编码不能为空"); }else { EntityWrapper<CustomerBatteryDetail> ewmpcb; ewmpcb = new EntityWrapper<>(); List<CustomerBatteryDetail> customerBatteryDetailList = customerBatteryDetailDao.selectPage(new Page<CustomerBatteryDetail>(1,1),ewmpcb.eq("BATTERY_CODE",row.getCell(2).toString()).eq("del_flag", CommonConstants.DEL_FLAG)); if(hasPack.contains(row.getCell(2).toString())){ hs1.add("电池编码重复"); }else if(! customerBatteryDetailList.isEmpty()){ hs1.add("电池编码已存在"); } hasPack.add(row.getCell(2).toString()); if(!checkCellEmpty(row.getCell(0)) && "电池模块".equals(row.getCell(0).toString())){ hasModule.add(row.getCell(2).toString()); } } //电池型号和对应电池编码 if(! checkCellEmpty(row.getCell(1)) && ! checkCellEmpty(row.getCell(2)) && !checkCellEmpty(row.getCell(0))){ if("电池包".equals(row.getCell(0).toString())){ packMap.put(row.getCell(2).toString(),row.getCell(1).toString()); } listPack.add(packMap); } } if(packCounts != packCountAll){ hs1.add("电池包个数与订单信息中所含电池包数不对应"); } if(moduleCounts != moduleCountAll){ hs1.add("电池模块数与订单信息中所含电池模块数不对应"); } if(hs2.size() > 0){ hs1.add("电池型号"+hs2.toString()+"在备案中不存在"); } hs2 = new LinkedHashSet(); //A-3电池模块编码信息 3列 XSSFSheet sheet2 = workbook.getSheetAt(2); for(int i=3;i<=sheet2.getLastRowNum();i++) {//从第4行读起,读到最后一行 XSSFRow row = sheet2.getRow(i); if(row == null) continue; if(checkAllCellEmpty(row,0,2)) continue; if(packCountAll==0 && i > 2 && errorFlag != false){ if(checkAllCellEmpty(row,0,4) == false){ result.setResult(false); LinkedHashSet sheetHs = new LinkedHashSet(); sheetHs.add("电池模块编码信息不该有数据,请检查"); result.setData(sheetHs); return result; } } if(checkCellEmpty(row.getCell(0))) { hs1.add("电池模块编码不能为空"); }else{ EntityWrapper<ModulePackRelation> ewmpmp; ewmpmp = new EntityWrapper<>(); List<ModulePackRelation> modulePackRelationList = modulePackRelationDao.selectPage(new Page<ModulePackRelation>(1,1),ewmpmp.eq("MODULE_CODE",row.getCell(0).toString()).eq("del_flag", CommonConstants.DEL_FLAG)); if(hasModule.contains(row.getCell(0).toString())){ hs1.add("电池模块编码重复"); }else if(! modulePackRelationList.isEmpty()){ hs1.add("电池模块编码已存在"); } hasModule.add(row.getCell(0).toString()); } if(checkCellEmpty(row.getCell(1))) { hs1.add("电池模块型号不能为空"); }else{ EntityWrapper<ModuleRecord> mrewmpi = new EntityWrapper<ModuleRecord>(); List<ModuleRecord> moduleRecordList= moduleDao.selectPage(new Page<ModuleRecord>(1,1),mrewmpi.eq("MODULE_MODEL",row.getCell(1).toString()).eq("del_flag", CommonConstants.DEL_FLAG)); if(moduleRecordList.isEmpty()){ hs2.add(row.getCell(1).toString()); } } if(checkCellEmpty(row.getCell(2))) { hs1.add("所属电池包编码不能为空"); }else{ //是否找到对应的电池包编码 if(! hasPack.contains(row.getCell(2).toString())){ hs1.add("模块所属电池包编码不存在"); }else{ //判断该模块型号对应的excel包是否在备案信息里 String packCode = null; if(!checkCellEmpty(row.getCell(1))){ String moduleExcel = row.getCell(1).toString(); for(int m=0;m<listPack.size();m++){ packCode = listPack.get(m).get(row.getCell(2).toString()); } //查询该包下的所有模块 List<String> moduleRecordList = moduleDao.selectModuleByPackCode(packCode); boolean flag = false; for(String moduleCode: moduleRecordList){ if(moduleCode.equals(moduleExcel)){ flag = true; break; } } if(flag == false){ hs3.add(row.getCell(1).toString()); hs1.add("模块"+hs3.toString()+"与包型号不对应"); } } } } } if(hs2.size() > 0){ hs1.add("电池模块型号"+hs2.toString()+"在备案中不存在"); } //A-4单体电池编码信息 2列 XSSFSheet sheet3 = workbook.getSheetAt(3); for(int i=3;i<=sheet3.getLastRowNum();i++) {//从第4行读起,读到最后一行 XSSFRow row = sheet3.getRow(i); if (row == null) continue; if (checkAllCellEmpty(row, 0, 1)) continue; if (checkCellEmpty(row.getCell(0))) { hs1.add("单体电池编码不能为空"); }else{ EntityWrapper<MonomerModuleRelation> ewmpmp; ewmpmp = new EntityWrapper<>(); List<MonomerModuleRelation> monomerModuleRelationList = monomerModuleRelationDao.selectPage(new Page<MonomerModuleRelation>(1,1),ewmpmp.eq("MONOMER_CODE",row.getCell(0).toString()).eq("del_flag", CommonConstants.DEL_FLAG)); if(hasMonomer.contains(row.getCell(0).toString())){ hs1.add("单体电池编码重复"); }else if(! monomerModuleRelationList.isEmpty()){ hs1.add("单体电池编码已存在"); } hasMonomer.add(row.getCell(0).toString()); } if (checkCellEmpty(row.getCell(1))) { hs1.add("所属电池模块编码不能为空"); }else{ if(! hasModule.contains(row.getCell(1).toString())){ hs1.add("单体所属电池模块编码不存在"); } } } result.setData(hs1); if(hs1.size() > 0) result.setResult(false); else result.setResult(true); return result; }
/** * @param row 一行数据 * @param begin 开始位置 * @param end 结束位置 * @return 全为空返回true */ public boolean checkAllCellEmpty(XSSFRow row,int begin,int end){ boolean flag = true; for (int i = begin; i < end; i++) { if(row.getCell(i)==null||"".equals(row.getCell(i))||row.getCell(i).getCellType() == XSSFCell.CELL_TYPE_BLANK){ }else{ flag = false; break; } } return flag; }
/** * @param cell 单个cell校验 * @return 为空返回true */ public boolean checkCellEmpty(XSSFCell cell){ if(cell==null||"".equals(cell)||cell.getCellType() == XSSFCell.CELL_TYPE_BLANK){ return true; } return false; }