使用EasyExcel导出百万条数据

使用EasyExcel导出百万条数据

应用是基于100W条数据进行的测试
首先：导入相关需要的依赖：

		<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.16</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.16</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.10</version>
            <exclusions>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi-ooxml</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi-ooxml-schemas</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.16.20</version>
        </dependency>

        <!-- junit -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-simple</artifactId>
            <version>1.7.25</version>
            <scope>compile</scope>
        </dependency>
         

创建所需要的实体类，如下：

@Accessors(chain = true)
@Data
public class ExcelBean {

    @ExcelProperty("主键id")
    private String id;

    @ExcelProperty("姓名")
    private String name;

    @ExcelProperty("地址")
    private String address;

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

    @ExcelProperty("数量")
    private Integer number;

    @NumberFormat("#.##")
    @ExcelProperty("身高")
    private Double high;

    @ExcelProperty("距离")
    private Double distance;

    @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
    @ExcelProperty("开始时间")
    private Date startTime;

    @ExcelProperty("结束时间")
    private Date endTime;
}

创建所用到的测试类，如下：

@Slf4j
public class writeExcelByApi {

    public static final String FILE_NAME = "C:\\Users\\861123001\\Desktop\\mqtt压测软件\\自造数据\\test_04.xlsx";
    // 每个 sheet 写入的数据
    public static final int NUM_PER_SHEET = 300000;
    // 每次向 sheet 中写入的数据（分页写入）
    public static final int NUM_BY_TIMES = 50000;

    @Test
    public void writeExcelByApi(){
        String fileName = FILE_NAME;
        log.info("导出excel名称={}",fileName);
        long startTime = System.currentTimeMillis();
        //调用api
        List<ExcelBean> date = getDate();
        EasyExcel.write(fileName,ExcelBean.class).sheet().doWrite(date);
        log.info("导出excel结束,数据量={}，耗时={}ms", date.size(), System.currentTimeMillis() - startTime);
    }

    /**
     * 获取excel 导出的数据
     *
     * @return list 集合
     */
    public static List<ExcelBean> getDate(){
        log.info("开始生成数据");
        //创建返回数据集合
        List<ExcelBean> list = new ArrayList<>();
        Date date = new Date();
        long startTime = System.currentTimeMillis();
        for (int i = 0; i < 2000000; i++) {
            //创建数据对象
            ExcelBean excelBean = new ExcelBean();
            ExcelBean excel = excelBean.setId(UUID.randomUUID().toString())
                    .setName("小明" + (10000 + i))
                    .setAddress("浙江省杭州市西湖")
                    .setAge(i)
                    .setNumber(i + 10000)
                    .setHigh(1.234 * i)
                    .setDistance(3.14 * i)
                    .setStartTime(date)
                    .setEndTime(date);

            list.add(excel);
        }
        log.info("数据生成结束，数据量={}，耗时={}ms", list.size(), System.currentTimeMillis() - startTime);
        return list;
    }
}

EasyExcel 导出 excel 应用优化一：可以通过分sheet来解决超出100万的数据

	@Test
    public void writeExcelByMulSheet() {
        String fileName = FILE_NAME;
        log.info("导出excel名称={}",fileName);
        long startTime = System.currentTimeMillis();

        //获取数据
        List<ExcelBean> date = getDate();

        //获取sheet的个数
        int sheetNum = date.size() % NUM_PER_SHEET == 0 ? date.size() / NUM_PER_SHEET : date.size() / NUM_PER_SHEET + 1;

        //指定写入的文件
        ExcelWriter excelWriter = EasyExcel.write(fileName, ExcelBean.class).build();
        for (int i = 0; i < sheetNum; i++) {
            long l = System.currentTimeMillis();

            //设置sheet的名字，每个sheet名称不能相同
            String sheetName = "sheet" + i;
            WriteSheet writeSheet = EasyExcel.writerSheet(i, sheetName).build();

            //开始根结束行数
            int startNum = i * NUM_PER_SHEET;
            int endNum = i == sheetNum - 1 ? date.size() : (i + 1) *  NUM_PER_SHEET;

            excelWriter.write(date.subList(startNum, endNum), writeSheet);
            log.info("写入sheet={}，数据量{}-{}={}，耗时={}ms", sheetName, endNum, startNum, endNum - startNum, System.currentTimeMillis() - l);
        }
        //最好放在finally中
        excelWriter.finish();
        log.info("导出excel结束,总数据量={}，耗时={}ms", date.size(), System.currentTimeMillis() - startTime);
    }

EasyExcel 导出 excel 应用优化二：数据源 list 太大，直接读取全部的 list 数据导致 OOM
将 list 数据进行分页读取，并进行分页写入到 excel。这样还有个好处，每次每页读取部分数据，然后写入到 excel 中（相当于该批数据已经从内存刷到了磁盘），也增加了写入的效率；poi 中的导出excel，为此专门提供了一个刷新磁盘的 api，具体代码如下

	@Test
    public void writeExcelByMulWrite() {
        String fileName = FILE_NAME;
        log.info("导出excel名称={}",fileName);
        long startTime = System.currentTimeMillis();

        //获取数据
        List<ExcelBean> date = getDate();
        ExcelWriter excelWriter = EasyExcel.write(fileName, ExcelBean.class).build();

        //适用于针对100万数据以下的写法
        //WriteSheet writeSheet = EasyExcel.writerSheet("testSheet" ).build();

        //计算需要写的次数
        int times = date.size() % NUM_BY_TIMES == 0 ? date.size() / NUM_BY_TIMES : date.size() / NUM_BY_TIMES + 1;
        for (int i = 0; i < times; i++) {
            long l = System.currentTimeMillis();
            WriteSheet writeSheet = EasyExcel.writerSheet("testSheet" + i).build();
            //开始跟结束行数
            int startNum = i * NUM_BY_TIMES;
            int endNum = i == times - 1 ? date.size() : (i + 1) * NUM_BY_TIMES;

            excelWriter.write(date.subList(startNum, endNum), writeSheet);
            log.info("写入数量{}-{}={}，耗时={}ms", endNum, startNum, endNum - startNum, startTime - l);
        }
        //最好写在finally里
        if (excelWriter != null) {
            excelWriter.finish();
        }
    }

EasyExcel 导出 excel 应用优化三：结合前面两种方案
将 list 数据进行分页读取，并且每个 sheet 分多次写入，且写入到多个 sheet 中

	@Test
    public void writeExcelByMulSheetAndMulWrite() {
        String fileName = FILE_NAME;
        log.info("导出excel名称={}", fileName);
        long startTime = System.currentTimeMillis();

        //获取数据
        List<ExcelBean> date = getDate();

        //获取sheet表数
        int sheetNum = date.size() % NUM_PER_SHEET == 0 ? date.size() / NUM_PER_SHEET : date.size() / NUM_PER_SHEET + 1;

        //获取每个sheet导入的次数
        int writeNumPerSheet = NUM_PER_SHEET % NUM_BY_TIMES == 0 ? NUM_PER_SHEET / NUM_BY_TIMES : NUM_PER_SHEET / NUM_BY_TIMES + 1;

        // 最后一个 sheet 写入的数量
        int writeNumLastSheet = date.size() - (sheetNum - 1) * NUM_PER_SHEET;

        // 最后一个 sheet 写入的次数
        int writeNumPerLastSheet = writeNumLastSheet % NUM_BY_TIMES == 0 ? writeNumLastSheet / NUM_BY_TIMES : writeNumLastSheet / NUM_BY_TIMES + 1;

        // 指定写入的文件
        ExcelWriter excelWriter = EasyExcel.write(fileName, ExcelBean.class).build();

        for (int i = 0; i < sheetNum; i++) {
            String sheetName = "sheet" + i;
            WriteSheet writeSheet = EasyExcel.writerSheet(i, sheetName).build();
            int writeNum = i == sheetNum - 1 ? writeNumPerLastSheet : writeNumPerSheet; // 每个sheet 写入的次数
            int endEndNum = i == sheetNum - 1 ? date.size() : (i + 1) * NUM_PER_SHEET; // 每个sheet 最后一次写入的最后行数

            for (int j = 0; j < writeNum; j++) {
                long l = System.currentTimeMillis();
                int startNum = i * NUM_PER_SHEET + j * NUM_BY_TIMES;
                int endNum = j == writeNum - 1 ? endEndNum : i * NUM_PER_SHEET + (j + 1) * NUM_BY_TIMES;
                excelWriter.write(date.subList(startNum, endNum), writeSheet);
                log.info("写入sheet={}，数据量={}-{}={}，耗时={}", sheetName, endNum, startNum, endNum - startNum, startTime - l);
            }
        }
        // 需要放入 finally 中
        if (excelWriter != null) {
            excelWriter.finish();
        }
        log.info("导出excel结束,总数据量={}，耗时={}ms", date.size(), System.currentTimeMillis() - startTime);

    }