SpringBoot+POI实现导入Excel时验证并返回错误Cell标红的文件

红太狼 2023-10-18 17:51 7阅读 0赞

## 场景 ##

SpringBoot中使用POI实现Excel导入到数据库（图文教程已实践）：

[https://blog.csdn.net/BADAO\_LIUMANG\_QIZHI/article/details/88660466][https_blog.csdn.net_BADAO_LIUMANG_QIZHI_article_details_88660466]

在Excel导入到数据库后进行格式验证，如果有错误则将错误的Cell标红然后验证完成后

将标红的Excel下载到客户端。

## 实现 ##

### js中发送请求 ###

//解析Excel操作
    function parseExcel(){
        var url = "/wmsReceiveOrder/parseExcel";
        $.ajax({
            type: 'POST',
            url: url,
            cache: false,  //禁用缓存
            contentType: false,
            dataType: "json",
            processData:false,
            success: function (result) {
                //250则为验证不通过则下载不正确的excel
                if(result.statusCode=="250"){
                    alert(result.message)
                    window.location.href="/wmsReceiveOrder/downloadMistakeExcel";
                }else{
                alert(result.message)
                if(result.statusCode=="200"){
                    window.location.reload()
                }
                }
            }
        })
        return false;
    }

### 后台解析Excel的方法 ###

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

### serviceImpl ###

/***
         * 解析excel
         * @param request
         * @return
         */
        @Override
        @Transactional
        public Map<String, Object> parseExcel(HttpServletRequest request) {
            Map<String, Object> result = new HashMap<String, Object>();
            Workbook workbook = null;
            //验证标识
            Boolean isValidatePass = true;
            //获取文件路径
            String path = (String)request.getSession().getAttribute("currFilePath");
           if(path==null||path==""){
                result.put("statusCode", "300");
                result.put("message", "请先上传excel文件再导入");
            }else{
                //获取文件格式
                String fileType = path.substring(path.lastIndexOf(".") + 1, path.length());
                try {
                    InputStream stream = new FileInputStream(path);
                    //如果后缀名为xls，使用HSSF
                    if (fileType.equals("xls")) {
                        workbook = new HSSFWorkbook(stream);
                        //如果后缀名是xlsx，使用XSSF
                    }else if (fileType.equals("xlsx")){
                        workbook = new XSSFWorkbook(stream);
                    }
                    Sheet sheet= workbook.getSheet("sheet1");
                    //获取行数
                    int rows=sheet.getPhysicalNumberOfRows();
                    WmsReceiveOrder receiveOrder =new WmsReceiveOrder();
                    //获取第一行数据
                    Row row1 =sheet.getRow(0);
                    if(row1!=null){
                        //获取采购订单号
                        row1.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                        String purchaseCode =row1.getCell(1).getStringCellValue();
                        receiveOrder.setPurchaseCode(purchaseCode);
                    }
                    //获取第二行数据
                    Row row2 =sheet.getRow(1);
                    if(row2!=null){
                        //获取供应商送货单号
                        row2.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                        String deliveryCode =row2.getCell(1).getStringCellValue();
                        receiveOrder.setDeliveryCode(deliveryCode);
                        //日期格式加校验
                        Cell deliveryTimeCell = row2.getCell(3);
                        if(deliveryTimeCell!=null){
                            //如果是数值类型
                            if(deliveryTimeCell.getCellType()==0){
                                    if(HSSFDateUtil.isCellDateFormatted(deliveryTimeCell)){
                                        //获取送货日期
                                        Date deliveryTime =deliveryTimeCell.getDateCellValue();
                                        receiveOrder.setDeliveryTime(deliveryTime);
                                    }else{
                                        //设置送货时间为红色
                                        CellStyle style =  workbook.createCellStyle();
                                        style.setFillForegroundColor(IndexedColors.RED.getIndex());
                                        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
                                        deliveryTimeCell.setCellStyle(style);
                                        isValidatePass=false;
                                    }
                            }else{
                                //设置送货时间为红色
                                CellStyle style =  workbook.createCellStyle();
                                style.setFillForegroundColor(IndexedColors.RED.getIndex());
                                style.setFillPattern(CellStyle.SOLID_FOREGROUND);
                                deliveryTimeCell.setCellStyle(style);
                                isValidatePass=false;
                            }
                        }
                        //获取供应商编号
                        row2.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
                        String supplierCode =row2.getCell(5).getStringCellValue();
                        receiveOrder.setSupplierCode(supplierCode);
                        //获取供应商名称
                        row2.getCell(6).setCellType(Cell.CELL_TYPE_STRING);
                        String supplierName =row2.getCell(6).getStringCellValue();
                        receiveOrder.setSupplierName(supplierName);
                    }
                    //获取第三行数据
                    Row row3 =sheet.getRow(2);
                    if(row3!=null){
                        //获取ERP入货单号
                        row3.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                        String erpInCode =row3.getCell(1).getStringCellValue();
                        receiveOrder.setErpInCod(erpInCode);
                        Cell inTimeCell= row3.getCell(3);
                        //如果是数值类型
                       if(inTimeCell!=null&&inTimeCell.getCellType()==0){
                            if(HSSFDateUtil.isCellDateFormatted(inTimeCell)){
                                //获取入库日期
                                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");
                    //校验子表各条的数量以及生产日期以及物料编号是否存在
                    for (int currentRow=4;currentRow<rows;currentRow++) {
                        WmsReceiveOrderDetails wmsReceiveOrderDetails =new WmsReceiveOrderDetails();
                        //获取物料编号
                        sheet.getRow(currentRow).getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                        String materielNumber = sheet.getRow(currentRow).getCell(1).getStringCellValue();
                        //获取生产日期
                        Cell productDateCell= sheet.getRow(currentRow).getCell(4);
                        //如果是数值类型
                       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{
                            //设置生产日期为红色
                            CellStyle style =  workbook.createCellStyle();
                            style.setFillForegroundColor(IndexedColors.RED.getIndex());
                            style.setFillPattern(CellStyle.SOLID_FOREGROUND);
                            productDateCell.setCellStyle(style);
                            isValidatePass=false;
                        }
                        //对数量进行验证
                        Cell numCell =sheet.getRow(currentRow).getCell(5);
                        //如果是数值类型
                       if(numCell!=null&&numCell.getCellType()==0){
                            if(HSSFDateUtil.isCellDateFormatted(numCell)){
                                //设置数量单元格为红色
                                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")){
                                    //设置数量单元格为红色
                                    CellStyle style =  workbook.createCellStyle();
                                    style.setFillForegroundColor(IndexedColors.RED.getIndex());
                                    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
                                    numCell.setCellStyle(style);
                                    isValidatePass=false;
                                }
                            }
                        }else{
                            //设置为红色
                            CellStyle style =  workbook.createCellStyle();
                            style.setFillForegroundColor(IndexedColors.RED.getIndex());
                            style.setFillPattern(CellStyle.SOLID_FOREGROUND);
                            numCell.setCellStyle(style);
                            isValidatePass=false;
                        }
                        //对物料编号进行验证
                        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;
                        }
                    }
                    //验证完成
                    if(!isValidatePass){
                        //将错误文件下载
                        result.put("statusCode", "250");
                        result.put("message", "Excel存在错误(红色部分)!");
                        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;
                            //如果前面主表验证通过则插入主表
                            if (isValidatePass) {
                                //插入receiveOrder表数据
                                isSaveReceiveOrder = this.save(receiveOrder);
                            }
                            List<WmsReceiveOrderDetails> receiveOrderDetailsList = new ArrayList<WmsReceiveOrderDetails>();
                            if (isSaveReceiveOrder) {
                                //如果插入表头成功，获取插入数据的ID并插入详情表
                                Long receiveId = receiveOrder.getId();
                                for (int currentRow = 4; currentRow < rows; currentRow++) {
                                    WmsReceiveOrderDetails wmsReceiveOrderDetails = new WmsReceiveOrderDetails();
                                    wmsReceiveOrderDetails.setReceiveId(receiveId);
                                    //获取物料编号
                                    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);
                                    //获取供应商批次
                                    sheet.getRow(currentRow).getCell(3).setCellType(Cell.CELL_TYPE_STRING);
                                    String supplierBatch = sheet.getRow(currentRow).getCell(3).getStringCellValue();
                                    wmsReceiveOrderDetails.setSupplierBatch(supplierBatch);
                                    //获取生产日期
                                    Date productDate = sheet.getRow(currentRow).getCell(4).getDateCellValue();
                                    wmsReceiveOrderDetails.setProductDate(productDate);
                                    //获取数量
                                    sheet.getRow(currentRow).getCell(5).setCellType(Cell.CELL_TYPE_STRING);
                                    Long num = Long.parseLong(sheet.getRow(currentRow).getCell(5).getStringCellValue());
                                    wmsReceiveOrderDetails.setNum(num);
                                    //获取托盘编号
                                    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", "导入物料编号为：" + wmsReceiveOrderDetails.getMaterielNumber() + "出错了!");
                                        } else {
                                            if (isValidatePass == true) {
                                                result.put("statusCode", "200");
                                                result.put("message", "导入收货单成功!");
                                            } else {
                                                result.put("statusCode", "300");
                                                result.put("message", "导入收货单失败!");
                                            }
    
                                        }
                                    }
                                }
                            } else {
                                result.put("statusCode", "300");
                                result.put("message", "导入收货单失败!");
                            }
                        } else {//是否已经导入过判断结束
                            result.put("statusCode", "300");
                            result.put("message", "此收货单已经导入，请勿重复导入！！");
                        }
                    }//验证通过后的插入数据库
                } 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());
                }
            }//判断是否已经上传文件
            return result;
        }

### 错误excel文件下载方法 ###

@Description("错误Excel下载")
        @RequestMapping("/downloadMistakeExcel")
        public String downloadMistakeExcel(HttpServletRequest request, HttpServletResponse response) {
            String path = (String)request.getSession().getAttribute("currFilePath");
            if (path != null) {
                //设置文件路径
                File file = new File(path);
                if (file.exists()) {
                    //时间格式化格式
                    SimpleDateFormat simpleDateFormat =new SimpleDateFormat("yyyyMMddHHmmss");
                    //获取当前时间并作为时间戳
                    String timeStamp=simpleDateFormat.format(new Date());
                    response.setContentType("application/force-download");// 设置强制下载不打开
                    response.addHeader("Content-Disposition", "attachment;fileName="+timeStamp+"mistake.xlsx" );// 设置文件名
                    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;
        }

### 流程图 ###

![Image 1][]![20190429170629286.jpg][]

[https_blog.csdn.net_BADAO_LIUMANG_QIZHI_article_details_88660466]: https://blog.csdn.net/BADAO_LIUMANG_QIZHI/article/details/88660466
[Image 1]: 
[20190429170629286.jpg]: https://img-blog.csdnimg.cn/20190429170629286.jpg