EasyExcel工具使用三板斧

我不是女神ヾ 2023-02-19 13:29 8阅读 0赞

1.  **引入依赖**：
    
        <dependency>
                    <groupId>com.alibaba</groupId>
                    <artifactId>easyexcel</artifactId>
                    <version>2.1.7</version>
                </dependency>
        
                <dependency>
                    <groupId>org.slf4j</groupId>
                    <artifactId>slf4j-simple</artifactId>
                    <version>1.7.5</version>
                </dependency>
        
                <dependency>
                    <groupId>org.apache.xmlbeans</groupId>
                    <artifactId>xmlbeans</artifactId>
                    <version>3.1.0</version>
                </dependency>
        
                <dependency>
                    <groupId>org.projectlombok</groupId>
                    <artifactId>lombok</artifactId>
                    <version>1.18.10</version>
                </dependency>
2.  ** 创建工具类：**
    
        @Slf4j
        @NoArgsConstructor //无参构造函数
        @AllArgsConstructor //全参构造函数
        public class ExcelSubjectDataListener extends AnalysisEventListener<ExcelSubjectData> {
        
            private SubjectMapper subjectMapper;
        
            /**
             *遍历每一行的记录
             * @param data
             * @param context
             */
            @Override
            public void invoke(ExcelSubjectData data, AnalysisContext context) {
                log.info("解析到一条记录: {}", data);
                //处理读取出来的数据
                String levelOneTitle = data.getLevelOneTitle();//一级标题
                String levelTwoTitle = data.getLevelTwoTitle();//二级标题
                log.info("levelOneTitle: {}", levelOneTitle);
                log.info("levelTwoTitle: {}", levelTwoTitle);
        
                //判断数据是否存在
                Subject subjectLevelOne = this.getByTitle(levelOneTitle);
                String parentId = null;
                if(subjectLevelOne == null){
                    //组装一级类别
                    Subject subject = new Subject();
                    subject.setParentId("0");
                    subject.setTitle(levelOneTitle);
                    // 存入数据库
                    subjectMapper.insert(subject);
                    parentId = subject.getId();
                }else{
                    parentId = subjectLevelOne.getId();
                }
        
                //判断数据是否存在
                Subject subjectLevelTwo = this.getSubByTitle(levelTwoTitle, parentId);
                if(subjectLevelTwo == null){
                    //组装二级类别
                    Subject subject = new Subject();
                    subject.setTitle(levelTwoTitle);
                    subject.setParentId(parentId);
                    // 存入数据库
                    subjectMapper.insert(subject);
                }
            }
        
            /**
             * 所有数据读取之后的收尾工作
             * @param context
             */
            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                log.info("全部数据解析完成");
            }
        
            /**
             * 根据一级分类的名称查询数据是否存在
             * @param title
             * @return
             */
            private Subject getByTitle(String title){
                QueryWrapper<Subject> queryWrapper = new QueryWrapper<>();
                queryWrapper.eq("title", title);
                queryWrapper.eq("parent_id", "0"); //一级分类
                return subjectMapper.selectOne(queryWrapper);
            }
        
        
            /**
             * 根据分类的名称和父id查询数据是否存在
             * @param title
             * @param parentId
             * @return
             */
            private Subject getSubByTitle(String title, String parentId){
                QueryWrapper<Subject> queryWrapper = new QueryWrapper<>();
                queryWrapper.eq("title", title);
                queryWrapper.eq("parent_id", parentId); //二级分类
                return subjectMapper.selectOne(queryWrapper);
            }
        }
3.  **官方文档供参考：  **[https://www.yuque.com/easyexcel/doc/easyexcel][https_www.yuque.com_easyexcel_doc_easyexcel]

[https_www.yuque.com_easyexcel_doc_easyexcel]: https://www.yuque.com/easyexcel/doc/easyexcel