springboot实现excel导入转换成bean存入数据库

柔情只为你懂 2024-03-23 16:44 65阅读 0赞

先做一个excel对应的bean

@Data
    @EqualsAndHashCode
    public class QualityDrawingJointReviewProblemExcelBean {
    
    
        @ExcelProperty("图纸编号")
        private String drawingNum;
    
    
        @ExcelProperty("图纸名称")
        private String drawingName;
    
        /**
         * 页码
         */
    
        @ExcelProperty("页码")
    
        private Integer pageCode;
    
        /**
         * 问题描述
         */
    
        @ExcelProperty("问题描述")
    
        private String problemDesc;
    
    
    }

在方法中用easyExcel方法：

@Override
        @Transactional(rollbackFor = Exception.class)
        public boolean excelImport(Long id, MultipartFile file) {
    
            QualityDrawingJointReview qualityDrawingJointReview = qualityDrawingJointReviewMapper.selectOne(new QueryWrapper<QualityDrawingJointReview>().lambda().eq(QualityDrawingJointReview::getId, id));
    
            try {
                List<QualityDrawingJointReviewProblem> list = ListUtil.list(Boolean.TRUE);
                EasyExcel.read(file.getInputStream(), QualityDrawingJointReviewProblemExcelBean.class, new AnalysisEventListener<QualityDrawingJointReviewProblemExcelBean>() {
                    @Override
                    public void invoke(QualityDrawingJointReviewProblemExcelBean data, AnalysisContext context) {
                        //将QualityDrawingJointReviewProblemExcelBean转换成QualityDrawingJointReviewProblem对象
                        QualityDrawingJointReviewProblem qualityDrawingJointReviewProblem = new QualityDrawingJointReviewProblem();
                        qualityDrawingJointReviewProblem.setJointReviewId(id);
                       if (qualityDrawingJointReview!= null&&StringUtils.isNotBlank(qualityDrawingJointReview.getReceiptsNum())) {
                           qualityDrawingJointReviewProblem.setDrawingNum(qualityDrawingJointReview.getReceiptsNum());
                        }
    
                        try {
                            BeanUtils.copyProperties(qualityDrawingJointReviewProblem,data);
                        } catch (IllegalAccessException e) {
                            throw new RuntimeException(e);
                        } catch (InvocationTargetException e) {
                            throw new RuntimeException(e);
                        }
                        //todo
                        list.add(qualityDrawingJointReviewProblem);
    
                    }
    
                    @Override
                    public void doAfterAllAnalysed(AnalysisContext context) {
                        log.info("质量检查库内容EXCEL导入完成");
                    }
                }).sheet().doRead();
    
                //批量插入list
    
                    this.qualityDrawingJointReviewProblemService.saveBatch(list);
    
                return true;
    
            } catch (IOException e) {
                log.error("质量检查库内容EXCEL导入IO流异常", e);
                throw new CommonBussinessException(CommonErrorCodeEnum.EXCEL_READER_ERROR);
            }
        }

其他的就是写接口了

@ApiOperation(value = "图纸问题EXCEL导入", notes = "图纸问题EXCEL导入")
        @PostMapping("/import")
        public R excelImport(@RequestPart("file") MultipartFile file, Long id) {
            return R.ok(this.drawingOutsideJointReviewService.excelImport(id, file));
        }

service接口

boolean excelImport(Long categoryId, MultipartFile file);

引入的包：

import com.alibaba.excel.EasyExcel;