初识EasyExcel

逃离我推掉我的手 2022-10-13 02:48 424阅读 0赞

### 1、为什么使用easyexcel ###

>   
> java解析excel的框架有很多 poi jxl 存在问题：非常的消耗内存  
> easyexcel 我们遇到再大的excel都不会出现内存溢出的问题 能够将一个原本3M excel文件，poi来操作将会占用内存100M，  
> 使用easyexcel降低到几KB，使用起来更加简单
> 
> poi读 1、创建xsshworkbook/hssfworkbook (inputstream in)  
>       2、读取sheet  
>       3、拿到当前sheet所有行row  
>       4、通过当前行去拿到对应的单元格的值

###         2、easyexcel拟解决的问题 ###

> 1、excel读写时内存溢出  
> 2、使用简单  
> 3、excel格式解析

###    3、工作原理 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzcxNzY2_size_16_color_FFFFFF_t_70][]

### 4.springboot集成EasyExcel，使用jpa查询数据 ###

引入依赖：

>     <dependency>
>         <groupId>com.alibaba</groupId>
>         <artifactId>easyexcel</artifactId>
>         <version>2.2.6</version>
>     </dependency>

package com.cxb.excel.entity;
    
    import com.alibaba.excel.annotation.ExcelIgnore;
    import com.alibaba.excel.annotation.ExcelProperty;
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    
    import javax.persistence.Entity;
    import javax.persistence.GeneratedValue;
    import javax.persistence.Id;
    import javax.persistence.Table;
    
    /**
     * @author 小石潭记
     * @date 2021/7/5 10:09
     * @Description: ${todo}
     */
    @Entity
    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    @Table(name = "t_area")
    public class TArea {
    
        @Id
        @GeneratedValue
        @ExcelProperty(index = 0, value = "城市ID")
        private int id;
    
        @ExcelProperty(index = 1, value = "城市名称")
        private String areaName;
    
        @ExcelProperty(index = 2, value = "父级城市ID")
        private int parentId;
    
        @ExcelProperty(index = 3, value = "父级城市名称")
        private String parentName;
        @ExcelIgnore
        private String level;
    }

package com.cxb.excel.respository;
    
    import com.cxb.excel.entity.TArea;
    import org.springframework.data.repository.PagingAndSortingRepository;
    import org.springframework.stereotype.Repository;
    
    /**
     * @author 小石潭记
     * @date 2021/7/5 10:18
     * @Description: ${todo}
     */
    @Repository
    public interface TAreaRepository extends PagingAndSortingRepository<TArea, Integer> {
    }

package com.cxb.excel.listener;
    
    import com.alibaba.excel.context.AnalysisContext;
    import com.alibaba.excel.event.AnalysisEventListener;
    import com.alibaba.excel.metadata.CellData;
    import com.cxb.excel.entity.TArea;
    
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Map;
    
    /**
     * @author 小石潭记
     * @date 2021/7/6 14:18
     * @Description: ${todo} 该listener里面不能依赖注入，可以通过构造器传值，因为该listener不是spring管理的
     */
    public class EasyExcelListener extends AnalysisEventListener<TArea> {
    
        private List<TArea> tAreaList = new ArrayList<>();
    
        /**
         * 校验excel文件的头部是否被修改
         * @param headMap
         * @param context
         */
        @Override
        public void invokeHead(Map<Integer, CellData> headMap, AnalysisContext context) {
            System.out.println("校验表格头部是否修改");
        }
    
        /**
         * 每一行数据
         * @param tArea
         * @param analysisContext
         */
        @Override
        public void invoke(TArea tArea, AnalysisContext analysisContext) {
            System.out.println("读取每一行数据");
            tAreaList.add(tArea);
        }
    
        /**
         * 读取完成执行
         * @param analysisContext
         */
        @Override
        public void doAfterAllAnalysed(AnalysisContext analysisContext) {
            System.out.println("数据读取完成" + tAreaList.size());
        }
    
        /**
         * 读取异常时的处理
         * @param exception
         * @param context
         * @throws Exception
         */
        @Override
        public void onException(Exception exception, AnalysisContext context) throws Exception {
            System.out.println("数据读取异常");
        }
    }

package com.cxb.excel.controller;
    
    import com.alibaba.excel.EasyExcel;
    import com.alibaba.excel.ExcelReader;
    import com.alibaba.excel.ExcelWriter;
    import com.alibaba.excel.read.metadata.ReadSheet;
    import com.alibaba.excel.write.metadata.WriteSheet;
    import com.cxb.excel.entity.TArea;
    import com.cxb.excel.listener.EasyExcelListener;
    import com.cxb.excel.respository.TAreaRepository;
    import lombok.extern.slf4j.Slf4j;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.data.domain.Page;
    import org.springframework.data.domain.PageRequest;
    import org.springframework.util.ClassUtils;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    /**
     * @author 小石潭记
     * @date 2021/7/5 14:10
     * @Description: ${todo}
     */
    @Slf4j
    @RestController
    @RequestMapping("/area")
    public class ExportController {
        @Autowired
        private TAreaRepository tAreaRepository;
    
        @GetMapping("/export")
        private void export() {
            try {
                String filePath = ClassUtils.getDefaultClassLoader().getResource("").getPath() + "export.xlsx";
                ExcelWriter excelWriter = EasyExcel.write(filePath, TArea.class).build();
                int count = (int) tAreaRepository.count();
                // 总页数
                int pageSize = count % 500 == 0 ? count / 500 : count / 500 + 1;
                for (int i = 0; i < pageSize; i++) {
                    PageRequest pageRequest = PageRequest.of(i, 500);
                    Page<TArea> allPage = tAreaRepository.findAll(pageRequest);
                    WriteSheet sheet = EasyExcel.writerSheet(i).build();
                    excelWriter.write(allPage.getContent(), sheet);
                }
                excelWriter.finish();
            } catch (Exception e) {
                log.error("导出异常{}", e);
            }
            System.out.println("导出成功");
        }
    
        @GetMapping("/import")
        private void importExcel() {
            String filePath = ClassUtils.getDefaultClassLoader().getResource("").getPath() + "export.xlsx";
            ExcelReader build = EasyExcel.read(filePath, TArea.class, new EasyExcelListener()).build();
            ReadSheet readSheet = EasyExcel.readSheet(0).build();
            build.read(readSheet);
            build.finish();
            System.out.println("导入成功");
        }
    }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzcxNzY2_size_16_color_FFFFFF_t_70]: /images/20221005/2b18a21216f54aec8412a8dda25e5d09.png