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

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/BADAO_LIUMANG_QIZHI/article/details/89678706

场景

SpringBoot中使用POI实现Excel导入到数据库(图文教程已实践):

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;
    }

流程图

展开阅读全文

没有更多推荐了,返回首页