阿里开源（EasyExcel）---导入EXCEL

「爱情、让人受尽委屈。」 2022-01-22 15:39 538阅读 0赞

# 一、简介 #

# 二、案例 #

## 2.1 POM依赖 ##

<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