場(chǎng)景

SpringBoot中使用POI實(shí)現(xiàn)Excel導(dǎo)入到數(shù)據(jù)庫(kù)（圖文教程已實(shí)踐）：

https://blog.csdn.net/BADAO_LIUMANG_QIZHI/article/details/88660466

在Excel導(dǎo)入到數(shù)據(jù)庫(kù)后進(jìn)行格式驗(yàn)證，如果有錯(cuò)誤則將錯(cuò)誤的Cell標(biāo)紅然后驗(yàn)證完成后

將標(biāo)紅的Excel下載到客戶端。

實(shí)現(xiàn)

js中發(fā)送請(qǐng)求

//解析Excel操作 function parseExcel(){var url = "/wmsReceiveOrder/parseExcel";$.ajax({type: 'POST',url: url,cache: false,? //禁用緩存contentType: false,dataType: "json",processData:false,success: function (result) {//250則為驗(yàn)證不通過則下載不正確的excelif(result.statusCode=="250"){alert(result.message)window.location.href="/wmsReceiveOrder/downloadMistakeExcel";}else{alert(result.message)if(result.statusCode=="200"){window.location.reload()}}}})return false; }

后臺(tái)解析Excel的方法

?@Description("解析Excel")@ResponseBody@RequestMapping("/parseExcel")@Transactionalpublic Map<String, Object> parseExcel(HttpServletRequest request) {return receiveOrderService.parseExcel(request);}

serviceImpl

/**** 解析excel* @param request* @return*/@Override@Transactionalpublic Map<String, Object> parseExcel(HttpServletRequest request) {Map<String, Object> result = new HashMap<String, Object>();Workbook workbook = null;//驗(yàn)證標(biāo)識(shí)Boolean isValidatePass = true;//獲取文件路徑String path = (String)request.getSession().getAttribute("currFilePath");if(path==null||path==""){result.put("statusCode", "300");result.put("message", "請(qǐng)先上傳excel文件再導(dǎo)入");}else{//獲取文件格式String fileType = path.substring(path.lastIndexOf(".") + 1, path.length());try {InputStream stream = new FileInputStream(path);//如果后綴名為xls，使用HSSFif (fileType.equals("xls")) {workbook = new HSSFWorkbook(stream);//如果后綴名是xlsx，使用XSSF}else if (fileType.equals("xlsx")){workbook = new XSSFWorkbook(stream);}Sheet sheet= workbook.getSheet("sheet1");//獲取行數(shù)int rows=sheet.getPhysicalNumberOfRows();WmsReceiveOrder receiveOrder =new WmsReceiveOrder();//獲取第一行數(shù)據(jù)Row row1 =sheet.getRow(0);if(row1!=null){//獲取采購(gòu)訂單號(hào)row1.getCell(1).setCellType(Cell.CELL_TYPE_STRING);String purchaseCode =row1.getCell(1).getStringCellValue();receiveOrder.setPurchaseCode(purchaseCode);}//獲取第二行數(shù)據(jù)Row row2 =sheet.getRow(1);if(row2!=null){//獲取供應(yīng)商送貨單號(hào)row2.getCell(1).setCellType(Cell.CELL_TYPE_STRING);String deliveryCode =row2.getCell(1).getStringCellValue();receiveOrder.setDeliveryCode(deliveryCode);//日期格式加校驗(yàn)Cell deliveryTimeCell = row2.getCell(3);if(deliveryTimeCell!=null){//如果是數(shù)值類型if(deliveryTimeCell.getCellType()==0){if(HSSFDateUtil.isCellDateFormatted(deliveryTimeCell)){//獲取送貨日期Date deliveryTime =deliveryTimeCell.getDateCellValue();receiveOrder.setDeliveryTime(deliveryTime);}else{//設(shè)置送貨時(shí)間為紅色CellStyle style =? workbook.createCellStyle();style.setFillForegroundColor(IndexedColors.RED.getIndex());style.setFillPattern(CellStyle.SOLID_FOREGROUND);deliveryTimeCell.setCellStyle(style);isValidatePass=false;}}else{//設(shè)置送貨時(shí)間為紅色CellStyle style =? workbook.createCellStyle();style.setFillForegroundColor(IndexedColors.RED.getIndex());style.setFillPattern(CellStyle.SOLID_FOREGROUND);deliveryTimeCell.setCellStyle(style);isValidatePass=false;}}//獲取供應(yīng)商編號(hào)row2.getCell(5).setCellType(Cell.CELL_TYPE_STRING);String supplierCode =row2.getCell(5).getStringCellValue();receiveOrder.setSupplierCode(supplierCode);//獲取供應(yīng)商名稱row2.getCell(6).setCellType(Cell.CELL_TYPE_STRING);String supplierName =row2.getCell(6).getStringCellValue();receiveOrder.setSupplierName(supplierName);}//獲取第三行數(shù)據(jù)Row row3 =sheet.getRow(2);if(row3!=null){//獲取ERP入貨單號(hào)row3.getCell(1).setCellType(Cell.CELL_TYPE_STRING);String erpInCode =row3.getCell(1).getStringCellValue();receiveOrder.setErpInCod(erpInCode);Cell inTimeCell= row3.getCell(3);//如果是數(shù)值類型if(inTimeCell!=null&&inTimeCell.getCellType()==0){if(HSSFDateUtil.isCellDateFormatted(inTimeCell)){//獲取入庫(kù)日期Date inTime =row3.getCell(3).getDateCellValue();receiveOrder.setInTime(inTime);}else{CellStyle style =? workbook.createCellStyle();style.setFillForegroundColor(IndexedColors.RED.getIndex());style.setFillPattern(CellStyle.SOLID_FOREGROUND);inTimeCell.setCellStyle(style);isValidatePass=false;}}else{CellStyle style =? workbook.createCellStyle();style.setFillForegroundColor(IndexedColors.RED.getIndex());style.setFillPattern(CellStyle.SOLID_FOREGROUND);inTimeCell.setCellStyle(style);isValidatePass=false;}}receiveOrder.setType(1);receiveOrder.setStatus("1");//校驗(yàn)子表各條的數(shù)量以及生產(chǎn)日期以及物料編號(hào)是否存在for (int currentRow=4;currentRow<rows;currentRow++) {WmsReceiveOrderDetails wmsReceiveOrderDetails =new WmsReceiveOrderDetails();//獲取物料編號(hào)sheet.getRow(currentRow).getCell(1).setCellType(Cell.CELL_TYPE_STRING);String materielNumber = sheet.getRow(currentRow).getCell(1).getStringCellValue();//獲取生產(chǎn)日期Cell productDateCell= sheet.getRow(currentRow).getCell(4);//如果是數(shù)值類型if(productDateCell!=null&&productDateCell.getCellType()==0){if(!HSSFDateUtil.isCellDateFormatted(productDateCell)) {CellStyle style = workbook.createCellStyle();style.setFillForegroundColor(IndexedColors.RED.getIndex());style.setFillPattern(CellStyle.SOLID_FOREGROUND);productDateCell.setCellStyle(style);isValidatePass = false;}}else{//設(shè)置生產(chǎn)日期為紅色CellStyle style =? workbook.createCellStyle();style.setFillForegroundColor(IndexedColors.RED.getIndex());style.setFillPattern(CellStyle.SOLID_FOREGROUND);productDateCell.setCellStyle(style);isValidatePass=false;}//對(duì)數(shù)量進(jìn)行驗(yàn)證Cell numCell =sheet.getRow(currentRow).getCell(5);//如果是數(shù)值類型if(numCell!=null&&numCell.getCellType()==0){if(HSSFDateUtil.isCellDateFormatted(numCell)){//設(shè)置數(shù)量單元格為紅色CellStyle style =? workbook.createCellStyle();style.setFillForegroundColor(IndexedColors.RED.getIndex());style.setFillPattern(CellStyle.SOLID_FOREGROUND);numCell.setCellStyle(style);isValidatePass=false;}else{if(!String.valueOf(numCell.getNumericCellValue()).contains(".0")){//設(shè)置數(shù)量單元格為紅色CellStyle style =? workbook.createCellStyle();style.setFillForegroundColor(IndexedColors.RED.getIndex());style.setFillPattern(CellStyle.SOLID_FOREGROUND);numCell.setCellStyle(style);isValidatePass=false;}}}else{//設(shè)置為紅色CellStyle style =? workbook.createCellStyle();style.setFillForegroundColor(IndexedColors.RED.getIndex());style.setFillPattern(CellStyle.SOLID_FOREGROUND);numCell.setCellStyle(style);isValidatePass=false;}//對(duì)物料編號(hào)進(jìn)行驗(yàn)證QueryWrapper<BusMaterielInfo> busMaterielInfoQueryWrapper =new QueryWrapper<BusMaterielInfo>();busMaterielInfoQueryWrapper.eq("materiel_number",materielNumber);BusMaterielInfo busMaterielInfo = busMaterielInfoMapper.selectOne(busMaterielInfoQueryWrapper);Cell materialNumberCell=sheet.getRow(currentRow).getCell(1);if(busMaterielInfo==null){CellStyle style =? workbook.createCellStyle();style.setFillForegroundColor(IndexedColors.RED.getIndex());style.setFillPattern(CellStyle.SOLID_FOREGROUND);materialNumberCell.setCellStyle(style);isValidatePass=false;}}//驗(yàn)證完成if(!isValidatePass){//將錯(cuò)誤文件下載result.put("statusCode", "250");result.put("message", "Excel存在錯(cuò)誤(紅色部分)!");FileOutputStream fileOut = new FileOutputStream(path);workbook.write(fileOut);fileOut.close();}else {//查詢條件Map<String, Object> map = new HashMap<String, Object>();map.put("delivery_code", receiveOrder.getDeliveryCode());map.put("supplier_code", receiveOrder.getSupplierCode());List<WmsReceiveOrder> wmsReceicveOrderList = wmsReceiveOrderMapper.selectByMap(map);if (wmsReceicveOrderList.size() == 0 || wmsReceicveOrderList == null) {boolean isSaveReceiveOrder = false;//如果前面主表驗(yàn)證通過則插入主表if (isValidatePass) {//插入receiveOrder表數(shù)據(jù)isSaveReceiveOrder = this.save(receiveOrder);}List<WmsReceiveOrderDetails> receiveOrderDetailsList = new ArrayList<WmsReceiveOrderDetails>();if (isSaveReceiveOrder) {//如果插入表頭成功，獲取插入數(shù)據(jù)的ID并插入詳情表Long receiveId = receiveOrder.getId();for (int currentRow = 4; currentRow < rows; currentRow++) {WmsReceiveOrderDetails wmsReceiveOrderDetails = new WmsReceiveOrderDetails();wmsReceiveOrderDetails.setReceiveId(receiveId);//獲取物料編號(hào)sheet.getRow(currentRow).getCell(1).setCellType(Cell.CELL_TYPE_STRING);String materielId = sheet.getRow(currentRow).getCell(1).getStringCellValue();wmsReceiveOrderDetails.setMaterielNumber(materielId);//獲取物料名稱sheet.getRow(currentRow).getCell(2).setCellType(Cell.CELL_TYPE_STRING);String materielName = sheet.getRow(currentRow).getCell(2).getStringCellValue();wmsReceiveOrderDetails.setMaterielName(materielName);//獲取供應(yīng)商批次sheet.getRow(currentRow).getCell(3).setCellType(Cell.CELL_TYPE_STRING);String supplierBatch = sheet.getRow(currentRow).getCell(3).getStringCellValue();wmsReceiveOrderDetails.setSupplierBatch(supplierBatch);//獲取生產(chǎn)日期Date productDate = sheet.getRow(currentRow).getCell(4).getDateCellValue();wmsReceiveOrderDetails.setProductDate(productDate);//獲取數(shù)量sheet.getRow(currentRow).getCell(5).setCellType(Cell.CELL_TYPE_STRING);Long num = Long.parseLong(sheet.getRow(currentRow).getCell(5).getStringCellValue());wmsReceiveOrderDetails.setNum(num);//獲取托盤編號(hào)String salverCode = sheet.getRow(currentRow).getCell(6).getStringCellValue();wmsReceiveOrderDetails.setSalverCode(salverCode);wmsReceiveOrderDetails.setStatus("0");receiveOrderDetailsList.add(wmsReceiveOrderDetails);}if (receiveOrderDetailsList != null) {for (WmsReceiveOrderDetails wmsReceiveOrderDetails : receiveOrderDetailsList) {boolean isSaveReceiveOrderDetails = wmsReceiveOrderDetailsService.save(wmsReceiveOrderDetails);if (!isSaveReceiveOrderDetails) {result.put("statusCode", "300");result.put("message", "導(dǎo)入物料編號(hào)為：" + wmsReceiveOrderDetails.getMaterielNumber() + "出錯(cuò)了!");} else {if (isValidatePass == true) {result.put("statusCode", "200");result.put("message", "導(dǎo)入收貨單成功!");} else {result.put("statusCode", "300");result.put("message", "導(dǎo)入收貨單失敗!");}}}}} else {result.put("statusCode", "300");result.put("message", "導(dǎo)入收貨單失敗!");}} else {//是否已經(jīng)導(dǎo)入過判斷結(jié)束result.put("statusCode", "300");result.put("message", "此收貨單已經(jīng)導(dǎo)入，請(qǐng)勿重復(fù)導(dǎo)入！！");}}//驗(yàn)證通過后的插入數(shù)據(jù)庫(kù)} catch (FileNotFoundException e) {e.printStackTrace();result.put("statusCode", "300");result.put("message", e.toString());} catch (IOException e) {e.printStackTrace();result.put("statusCode", "300");result.put("message", e.toString());}}//判斷是否已經(jīng)上傳文件return result;}

錯(cuò)誤excel文件下載方法

?@Description("錯(cuò)誤Excel下載")@RequestMapping("/downloadMistakeExcel")public String downloadMistakeExcel(HttpServletRequest request, HttpServletResponse response) {String path = (String)request.getSession().getAttribute("currFilePath");if (path != null) {//設(shè)置文件路徑File file = new File(path);if (file.exists()) {//時(shí)間格式化格式SimpleDateFormat simpleDateFormat =new SimpleDateFormat("yyyyMMddHHmmss");//獲取當(dāng)前時(shí)間并作為時(shí)間戳String timeStamp=simpleDateFormat.format(new Date());response.setContentType("application/force-download");// 設(shè)置強(qiáng)制下載不打開response.addHeader("Content-Disposition", "attachment;fileName="+timeStamp+"mistake.xlsx" );// 設(shè)置文件名byte[] buffer = new byte[1024];FileInputStream fis = null;BufferedInputStream bis = null;try {fis = new FileInputStream(file);bis = new BufferedInputStream(fis);OutputStream os = response.getOutputStream();int i = bis.read(buffer);while (i != -1) {os.write(buffer, 0, i);i = bis.read(buffer);}System.out.println("success");} catch (Exception e) {e.printStackTrace();} finally {if (bis != null) {try {bis.close();} catch (IOException e) {e.printStackTrace();}}if (fis != null) {try {fis.close();} catch (IOException e) {e.printStackTrace();}}}}}return null;}

流程圖

總結(jié)

以上是生活随笔為你收集整理的SpringBoot+POI实现导入Excel时验证并返回错误Cell标红的文件的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。