EsayExcel使用

EsayExcel简单入门

1 maven依赖

    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>2.2.6</version>
    </dependency>
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-simple</artifactId>
        <version>1.7.26</version>
    </dependency>
    第一个是esayExcel的依赖，第二个是因为esayExcel中的slf4j-api排除了<ignore>org.slf4j.impl.StaticLoggerBinder</ignore>，所以加上了后面的依赖

2 excel导出

@Data
public class Dog {
    @ExcelProperty(value = "狗龄", index = 0)
    private int age;
    @ExcelProperty(value = "昵称", index = 1)
    private String name;
    @ExcelProperty(value = "品种", index = 2)
    private String type;
    @ExcelIgnore
    private BigDecimal weight;
}

定义了一个Gog类使用@ExcelProperty注解进行字段导出的标注，@ExcelIgnore是忽略该字段

    String filePath = "D:\\Dog.xlsx";
    @org.junit.Test
    public void testExcel(){
        EasyExcel.write(filePath,Dog.class).sheet("狗舍").doWrite(buildDog());
    }
    
    public List<Dog> buildDog(){
        List<Dog> list = new ArrayList<>();
        for (int i = 0; i < 20; i++) {
            Dog dog = new Dog();
            dog.setAge(i);
            dog.setName("旺财");
            dog.setType("田园犬");
            list.add(dog);
        }
        return list;
    }

进行执行后，dog.xlsx文件便输出到了d盘
![这个是生成的文件截图](https://img-blog.csdnimg.cn/2021031918091195.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NwcTE4NDM1MTMyNTE3,size_16,color_FFFFFF,t_70#pic_center)

3 excel的读取

excel的读取建议继承AnalysisEventListener类进行操作
1.声明一个函数式接口

@FunctionalInterface
public interface ExcelConsumer<E> {
    void excute(List<E> list);
}

2. 继承AnalysisEventListener

public class ExcelListener<T> extends AnalysisEventListener<T> {

    private static final int MAX_COUNT = 1000;
    List<T> list = new ArrayList<T>();

    //函数式接口,进行自定义
    private ExcelConsumer<T> consumer;

    public ExcelListener() {
    }

    public ExcelListener(ExcelConsumer<T> consumer) {
        this.consumer = consumer;
    }
    @Override
    public void invoke(T t, AnalysisContext analysisContext) {
        list.add(t);
        if (list.size() > MAX_COUNT){
            //自定义逻辑执行
            consumer.excute(list);
            list.clear();

        }
    }
    //读取之后的逻辑
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        consumer.excute(list);
    }
}

3.读取上次的文件

    @org.junit.Test
    public void testExcelReadOne()  {
        File file = new File(filePath);
        EasyExcel.read(file, Dog.class, new ExcelListener<Dog>(p ->
                p.forEach(System.out::println))).sheet().doRead();
    }

输出：![第一个参数是file对象，第二个是对应的VO，不然会转成LinkedHashMap,第三个是自己定义的Listener,中间使用自定义函数式接口对中间的值进行操作](https://img-blog.csdnimg.cn/2021031918260366.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NwcTE4NDM1MTMyNTE3,size_16,color_FFFFFF,t_70#pic_center)