EasyExcel多次写入数据&多个EasyExcel文件导出到zip压缩文件

笔者最近需要导出一百多万条数据到Excel，已经超出单张工作表的最大容量（2^20=1048576），需要导出到多个工作表或多个Excel文件。

海量数据导出面临的问题有以下几个：

1. 如果一次性查出所有数据，很可能内存溢出，所以需要分页导出，分页导出就必须解决大分页查询的性能问题。
   该问题网上有很多解决办法，本文不涉及。

2. 如果使用POI进行数据导出，内存、CPU占用都很高，而且速度很慢，所以采用EasyExcel进行数据导出。在上一篇使用EasyExcel读写Excel文件中，数据是一次性写入的，本文介绍EasyExcel多次写入数据的方法。

3. 将多个EasyExcel的数据写入到zip文件中。通过将EasyExcel写入到ByteArrayOutputStream，再将ByteArrayOutputStream转成byte数组，写入到zip中。

1.在pom.xml中添加POI相关依赖

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

2.实体类

package com.example.study.entity;


import com.alibaba.excel.annotation.ExcelProperty;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Getter;
import lombok.Setter;

import java.util.Date;

@Getter
@Setter
public class StudentEntity {
    @ExcelProperty(value = "id", order = 1)
    private Integer id;

    @ExcelProperty(value = "生日", order = 4)
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date birthday;

    @ExcelProperty(value = "名字", order = 2)
    private String name;

    @ExcelProperty(value = "性别", order = 3)
    private String sex;

    private String desc;

    private String extra;
}

3.写入数据

package com.example.study.common;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.example.study.entity.StudentEntity;

import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

public class EasyExcelWriteToZipDemo {
    public static void main(String[] args) {
        String writeExcel = "F:\\tmp\\batch_export.zip";
        write(writeExcel);
    }

    private static void write(String writeExcel) {
        // 设置不需要导出的字段
        Set<String> excludeColumnFieldNames = new HashSet<>();
        excludeColumnFieldNames.add("desc");
        excludeColumnFieldNames.add("extra");
        WriteSheet sheet = EasyExcel.writerSheet("花名册")
                .excludeColumnFieldNames(excludeColumnFieldNames)
                .needHead(Boolean.TRUE)
                .build();
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        ExcelWriter workbook = EasyExcel.write(baos, StudentEntity.class).build();
        int sheetMaxSize = 1001;
        int currentExcelSize = 0;
        int count = 0;
        int total = 3111;
        int fileIndex = 1;
        try (FileOutputStream fos = new FileOutputStream(writeExcel);
             ZipOutputStream zos = new ZipOutputStream(fos)) {
            while (count < total) {
                List<StudentEntity> page = getPage(count + 1, total);
                if (currentExcelSize + page.size() > sheetMaxSize) {
                    // 超过单个文件最大数据条数后，写入到zip文件，并将当前页面数据写入新的excel文件
                    workbook.close();
                    zos.putNextEntry(new ZipEntry(String.format("数据导出文件_%s.xlsx", fileIndex++)));
                    zos.write(baos.toByteArray());
                    baos = new ByteArrayOutputStream();
                    workbook = EasyExcel.write(baos, StudentEntity.class).build();
                    currentExcelSize = page.size();
                } else {
                    currentExcelSize += page.size();
                }
                workbook.write(page, sheet);
                count += page.size();
            }
            if (currentExcelSize > 0) {
                workbook.close();
                zos.putNextEntry(new ZipEntry(String.format("数据导出文件_%s.xlsx", fileIndex++)));
                zos.write(baos.toByteArray());
            }
            zos.flush();
            fos.flush();
        } catch (IOException exception) {
            exception.printStackTrace();
        }
    }

    private static List<StudentEntity> getPage(int lastMaxId, int total) {
        List<StudentEntity> page = new ArrayList<>();
        for (int index = lastMaxId; index < Math.min(lastMaxId + 100, total + 1); index++) {
            StudentEntity student = new StudentEntity();
            student.setId(index);
            student.setName("名字_" + index);
            student.setSex(index % 2 == 0 ? "女" : "男");
            student.setBirthday(new Date());
            student.setDesc("test desc");
            student.setExtra("test extra");
            page.add(student);
        }
        return page;
    }
}