【EasyExecl】Excel文件上传

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。

 github地址: https://www.yuque.com/easyexcel/doc/easyexcel

中文网址：https://www.yuque.com/easyexcel/doc/read

常用Api:常见api · 语雀常见api · 语雀title: API详细参数介绍关于常见类解析Easy...https://www.yuque.com/easyexcel/doc/api

excel示例

导入相关依赖：

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.itheima</groupId>
    <artifactId>easy-excel-demo</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.20</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.0</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.76</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.5.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

对象
指定列的下标或者列名

package com.****.domain;

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

import java.util.Date;

//实体类

@Data
@EqualsAndHashCode
public class ExcelData {

    //@ExcelProperty("字符串标题") 声明当前字段对应的列
    @ExcelProperty("字符串标题")
    private String title;

    @ExcelProperty("日期标题")
    private Date date;

    @ExcelProperty("数字标题")
    private Double number;
}

构建监听器

package com.***.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.***.domain.ExcelData;

import java.util.List;

public class ExcelDataReadListener implements ReadListener<ExcelData> {
 
    //有个很重要的点ExcelDataReadListener不能被spring管理，要每次读取excel都要new, 然后里面用到spring可以构造方法传进去
    //private ExcelDataDao excelDataDao;
    //public ExcelDataReadListener(ExcelDataDao excelDataDao){
    //    this.excelDataDao = excelDataDao;
    //}
    
    
    //每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
    private static final int BATCH_COUNT = 100;

    //缓存的数据
    private List<ExcelData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

    //每一条数据解析都会来调用此方法
    @Override
    public void invoke(ExcelData data, AnalysisContext context) {
        cachedDataList.add(data);//读取到一条记录添加到集合
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    //所有数据解析完成了, 会来调用此方法
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        System.out.println("所有数据解析完成！");
    }

    //数据保存到数据库
    private void saveData() {
        System.out.println("缓存集合已满,开始一次保存！");
        for (ExcelData excelData : cachedDataList) {
            System.out.println(excelData);
            
            //excelDataDao.save();
        }
    }
}

测试读取文件

package com.***.test;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.listener.PageReadListener;
import com.***.domain.ExcelData;
import com.***.listener.ExcelDataReadListener;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;

@Slf4j
public class ReadExcelTest {

    //方式一: 直接读取
    @Test
    public void test1() {
        String fileName = "D:/upload/excel读取测试.xlsx";

        // 这里 需要指定读用哪个class去读，然后读取第一个sheet文件流会自动关闭
        EasyExcel.read(fileName, ExcelData.class, new PageReadListener<ExcelData>(dataList -> {
            for (ExcelData excelData : dataList) {
                System.out.println("读取到一条数据:" + excelData);
            }
        })).sheet().doRead();
    }

    //方式二: 缓冲读取
    @Test
    public void test2() {
        String fileName = "D:/upload/excel读取测试.xlsx";

        // 这里 需要指定读用哪个class去读，然后读取第一个sheet文件流会自动关闭
        EasyExcel.read(fileName, ExcelData.class, new ExcelDataReadListener()).sheet().doRead();
    }
}

写excel

对象：

package com.****.domain;

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

import java.util.Date;

@Data
public class User {
    @ExcelProperty("姓名")
    private String name;

    @ExcelProperty("年龄")
    private Integer age;

    @ExcelProperty("创建时间")
    private Date createTime;

    @ExcelIgnore//忽略这个字段
    private String ignore;
}

测试：

package com.***.test;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.***.domain.ExcelData;
import com.***.domain.User;
import org.junit.Test;

import java.util.Date;
import java.util.List;

public class WriteExcelTest {

    private static List<User> list = ListUtils.newArrayList();

    //准备输出的数据
    static{
        for (int i = 0; i < 10; i++) {
            User data = new User();
            data.setName("字符串" + i);
            data.setAge(10 + i);
            data.setCreateTime(new Date());
            list.add(data);
        }
    }

    @Test
    public void test1() {
        String fileName = "D:/upload/excel输出测试.xlsx";
        EasyExcel.write(fileName, User.class).sheet("模板").doWrite(list);
    }

    @Test
    public void simpleWrite2() {
        String fileName = "D:/upload/excel输出测试.xlsx";
        // 这里 需要指定写用哪个class去写
        ExcelWriter excelWriter = null;
        try {
            excelWriter = EasyExcel.write(fileName, ExcelData.class).build();
            WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
            excelWriter.write(list, writeSheet);
        } finally {
            // 千万别忘记finish 会帮忙关闭流
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }
}