EasyExcel使用-1

环境搭建

1. 导入依赖

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

2. 实体类

作用：将excel中每行数据封装到实体类中

package com.itheima.excel;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

@Data
public class User {

    @ExcelProperty(index = 0)
    private int id;
    @ExcelProperty(index = 1)
    private String name;
    @ExcelProperty("pass")
    private String password;

}

@ExcelProperty

D:\\Desktop\\easy-excel.xlsx

1. 基础使用

package com.itheima.excel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;

import java.util.ArrayList;

public class Main {

    public static void main(String[] args) {
        EasyExcel.read("D:\\Desktop\\easy-excel.xlsx", User.class, new ReadListener<User>() {
                    // 缓存大小
                    private final static int CACHE_SIZE = 3;
                    ArrayList<User> list = new ArrayList<>(CACHE_SIZE);
                    // 每读一行数据，就调用invoke方法
                    @Override
                    public void invoke(User user, AnalysisContext analysisContext) {
                        list.add(user);
                        if (list.size() >= CACHE_SIZE) {
                            System.out.println("写入数据库：" + list);
                            list.clear();
                        }
                    }
                    // 读取完成之后调用
                    @Override
                    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                        System.out.println("写入数据库：" + list);
                        list.clear();
                    }

                    /**
                     * 抛出异常处理
                     * 
                     * @param exception
                     * @param context
                     * @throws Exception
                     */
                    @Override
                    public void onException(Exception exception, AnalysisContext context) throws Exception {
                        System.out.println(exception);
                    }
                })
                .doReadAll();
    }

}

结果：

写入数据库：[User(id=1, name=张三, password=123), User(id=2, name=李四, password=456), User(id=3, name=王五, password=www)]
写入数据库：[User(id=4, name=zhaoliu, password=aaa), User(id=5, name=tianqi, password=444), User(id=6, name=null, password=null)]
写入数据库：[User(id=7, name=null, password=null), User(id=8, name=null, password=null), User(id=9, name=null, password=null)]
写入数据库：[User(id=10, name=null, password=null), User(id=11, name=null, password=null)]

2. Lambda表达式改进

package com.itheima.excel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.listener.PageReadListener;

import java.util.List;
import java.util.function.Consumer;

public class Main {

    public static void main(String[] args) {

        // 读取一个sheet页
        // EasyExcel.read("D:\\Desktop\\easy-excel.xlsx",
        //                 User.class,
        //                 new PageReadListener((Consumer<List<User>>) list -> {
        //                     System.out.println("读取到数据：" + list);
        //                 }))
        //         .sheet()
        //         .doRead();

        // 读取所有sheet页_方式一
        EasyExcel.read("D:\\Desktop\\easy-excel.xlsx",
                        User.class,
                        new PageReadListener((Consumer<List<User>>) list -> {
                            System.out.println("读取到数据：" + list);
                        }))
                .doReadAll();

    }

}

结果：

读取到数据：[User(id=1, name=张三, password=123), User(id=2, name=李四, password=456), User(id=3, name=王五, password=www), User(id=4, name=zhaoliu, password=aaa), User(id=5, name=tianqi, password=444), User(id=6, name=null, password=null), User(id=7, name=null, password=null), User(id=8, name=null, password=null), User(id=9, name=null, password=null), User(id=10, name=null, password=null), User(id=11, name=null, password=null)]