一、简介

二、案例

2.1 POM依赖

        <!-- 阿里开源EXCEL -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>1.1.2-beta4</version>
        </dependency>

2.2 POJO对象

package com.authorization.privilege.excel;

import java.util.Date;

/**
 * @author qjwyss
 * @date 2019/3/15
 * @description
 */
public class User {

    private String uid;
    private String name;
    private Integer age;
    private Date birthday;

    public User() {
    }

    public User(String uid, String name, Integer age, Date birthday) {
        this.uid = uid;
        this.name = name;
        this.age = age;
        this.birthday = birthday;
    }

    public String getUid() {
        return uid;
    }

    public void setUid(String uid) {
        this.uid = uid;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    @Override
    public String toString() {
        return "User{" +
                "uid='" + uid + '\'' +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", birthday=" + birthday +
                '}';
    }
}

2.3 测试环境

2.3.1 一个SHEET容量

/**
 * @author qjwyss
 * @date 2019/5/31
 * @description
 */
public class ExcelReadTest {

    private Logger logger = LoggerFactory.getLogger(ExcelReadTest.class);


    @Test
    public void readExcelTest() throws Exception {

        InputStream inputStream = new FileInputStream(new File("E:\\temp\\7.xlsx"));

        System.out.println("开始读取");

        List<User> dataList = new ArrayList<User>();

        EasyExcelFactory.readBySax(inputStream, new Sheet(1,1), new AnalysisEventListener() {

            @Override
            public void invoke(Object object, AnalysisContext analysisContext) {

                logger.info("当前行：{} 对应的对象信息为：{}", analysisContext.getCurrentRowNum(), object);

                ArrayList userObj = (ArrayList) object;
                User user = new User();
                user.setUid(userObj.get(0) == null ? null : userObj.get(0).toString());
                user.setName(userObj.get(1) == null ? null : userObj.get(1).toString());
                user.setAge(userObj.get(2) == null ? null : Integer.valueOf(userObj.get(2).toString()));
                dataList.add(user);

                // 每批插入的数量
                int perReadCount = 5;
                Integer currentRowNum = analysisContext.getCurrentRowNum();
                if (currentRowNum % perReadCount == 0) {
                    logger.info("存储dataList的大小为：{}",dataList.size());
                    dataList.clear();
                }
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                logger.info("最后一批存储dataList的大小为：{}",dataList.size());
                dataList.clear();//解析结束销毁不用的资源
            }
        });

        System.out.println("读取完毕");
    }

}

测试输出结果：此时测试了一个有7条记录的excel,输出结果如下

开始读取
18:19:12.049 [main] INFO com.authorization.privilege.excel.ExcelReadTest - 当前行：1 对应的对象信息为：[ID_0, null, 0, Tue Apr 02 20:05:37 CST 2019]
18:19:12.056 [main] INFO com.authorization.privilege.excel.ExcelReadTest - 当前行：2 对应的对象信息为：[ID_1, 小明1, 1, Tue Apr 02 20:05:37 CST 2019]
18:19:12.057 [main] INFO com.authorization.privilege.excel.ExcelReadTest - 当前行：3 对应的对象信息为：[ID_2, 小明2, 2, Tue Apr 02 20:05:37 CST 2019]
18:19:12.058 [main] INFO com.authorization.privilege.excel.ExcelReadTest - 当前行：4 对应的对象信息为：[ID_3, 小明3, 3, Tue Apr 02 20:05:37 CST 2019]
18:19:12.059 [main] INFO com.authorization.privilege.excel.ExcelReadTest - 当前行：5 对应的对象信息为：[ID_4, 小明4, 4, Tue Apr 02 20:05:37 CST 2019]
18:19:12.059 [main] INFO com.authorization.privilege.excel.ExcelReadTest - 存储dataList的大小为：5
18:19:12.060 [main] INFO com.authorization.privilege.excel.ExcelReadTest - 当前行：6 对应的对象信息为：[ID_5, 小明5, null, Tue Apr 02 20:05:37 CST 2019]
18:19:12.061 [main] INFO com.authorization.privilege.excel.ExcelReadTest - 当前行：7 对应的对象信息为：[ID_6, 小明6, 6, Tue Apr 02 20:05:37 CST 2019]
18:19:12.061 [main] INFO com.authorization.privilege.excel.ExcelReadTest - 最后一批存储dataList的大小为：2
读取完毕

2.4 生产环境

2.4.1 一个SHEET容量

TODO  就是把上面的分批处理改为数据库批量插入  TODO