百万数据导出Excel工具类,欢迎指正

依赖项

工具类需要一些常用的依赖,dependency如下

    <dependencies>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.0</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.0</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.6</version>
        </dependency>
    </dependencies>

工具类和使用

具体的工具类如下所示,使用方法参照main方法即可,需要的可以扩展param参数,使得工具更为通用

package xyz.yq56;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;
import java.util.function.Function;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import com.sun.istack.internal.NotNull;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.experimental.SuperBuilder;

/**
 * @author yi qiang
 * @date 2022/7/5 23:12
 */
public class BigExcelUtil {

    public static void main(String[] args) throws IOException, InterruptedException {
        long t = System.currentTimeMillis();
        WbParam<User> param = WbParam.<User>builder()
                .func(page -> {
                	//一般来说,这里应该是个servie.pageList方法.这里是模拟这个数据库获取数据的过程
                    List<User> list = new ArrayList<>();
                    for (int i = 0; i < 10000; i++) {
                        list.add(new User("id_" + page + "_" + i, i, i + "", i + "", i + ""));
                    }
                    return list;
                })
                //属性映射,数据需要和表头一一对应
                .mapper(item -> new String[]{
                        item.getName(), Integer.toString(item.getAge()), item.getS1(), item.getS2(), item.getS3()
                })
                //这里一般需要使用service.count方法
                .totalPage(100)
                //表头
                .headerArr(new String[]{"name", "age", "s1", "s2", "s3"})
                .build();

        Workbook wb = createWb(param);
        generateTempWorkbook("G:\\tmp\\test.xlsx", wb);
        System.out.println("耗时: " + (System.currentTimeMillis() - t));
    }


    @Data
    @AllArgsConstructor
    public static class User {
        private String name;
        private Integer age;
        private String s1;
        private String s2;
        private String s3;
    }


    /**
     * 指定目录下生成Workbook
     *
     * @param filePath 目录
     * @param workbook 工作簿
     * @throws IOException 异常抛出
     */
    public static void generateTempWorkbook(String filePath, Workbook workbook) throws IOException {
        if (workbook == null) {
            throw new IllegalArgumentException("Workbook doesn't exist");
        }

        org.apache.commons.io.FileUtils.touch(new File(filePath));
        try (FileOutputStream out = new FileOutputStream(filePath);) {
            //写入文件输出流
            workbook.write(out);
        } finally {
            workbook.close();
            if (workbook instanceof SXSSFWorkbook) {
                ((SXSSFWorkbook) workbook).dispose();
            }
        }
    }

    /**
     * 1048576行数据一下使用此工具类即可实现wb的创建
     * <br> 记得对Workbook进行清理工作
     *
     * @param wbParam 参数,里面属性不可以为空
     * @param <T>     待转化属性
     * @return wb
     */
    @SuppressWarnings("all")
    public static <T> Workbook createWb(WbParam wbParam) {
        Workbook workbook = new SXSSFWorkbook(wbParam.getRowAccessWindowSize());
        Sheet sheet = createSheetWithHeader(workbook, wbParam.getSheetTitle(), wbParam.getHeaderArr());
        int row = 1;
        //映射关系获取一次就行,不用放到循环里面
        Function<T, String[]> mapper = wbParam.getMapper();
        Function<Integer, List<T>> func = wbParam.getFunc();

        for (int i = 1; i <= wbParam.getTotalPage(); i++) {
            for (T t : func.apply(i)) {
                appendRow(sheet, row, t, mapper);
                row++;
            }
        }
        return workbook;
    }

    /**
     * 创建一个带有表头的sheet
     *
     * @param workbook   工作簿,需要提前创建且不能为null
     * @param sheetTitle sheet标题
     * @param headerArr  表头
     * @return 带有表头的sheet
     */
    public static Sheet createSheetWithHeader(Workbook workbook, String sheetTitle, String[] headerArr) {
        Sheet sheet = workbook.createSheet(sheetTitle);

        //创建表头
        Row header = sheet.createRow(0);
        for (int i = 0; i < headerArr.length; i++) {
            Cell cell = header.createCell(i);
            cell.setCellValue(headerArr[i]);
        }
        return sheet;
    }

    /**
     * 追加行数据
     *
     * @param sheet  表单
     * @param row    行数据
     * @param t      对象
     * @param mapper 对象属性与行数据的映射关系
     * @param <T>    待转化的对象
     */
    public static <T> void appendRow(Sheet sheet, int row, T t, Function<T, String[]> mapper) {
        if (sheet == null) {
            throw new IllegalArgumentException("Sheet doesn't exist");
        }
        if (row < 0) {
            throw new IllegalArgumentException("Illegal row: " + row);
        }
        Row contentRow = sheet.createRow(row);
        String[] rowArr = mapper.apply(t);
        if (rowArr != null) {
            for (int i = 0; i < rowArr.length; i++) {
                Cell cell = contentRow.createCell(i);
                cell.setCellValue(rowArr[i]);
            }
        }
    }

    /**
     * 所有参数都必填
     *
     * @param <T> 待转化的对象
     */
    @Data
    @SuperBuilder
    public static class WbParam<T> {
        /**
         * 总页数
         */
        @NotNull
        int totalPage;
        /**
         * 分页获取数据
         */
        @NotNull
        Function<Integer, List<T>> func;
        /**
         * sheet标题
         */
        String sheetTitle;
        /**
         * 头数组
         */
        @NotNull
        String[] headerArr;
        /**
         * 类与String的映射关系
         */
        @NotNull
        Function<T, String[]> mapper;

        /**
         * 多少条之后就刷新到磁盘中(异常值会重置为10000)
         */
        @NotNull
        int rowAccessWindowSize;

        public int getRowAccessWindowSize() {
            return rowAccessWindowSize <= 0 ? 10000 : rowAccessWindowSize;
        }

        public String getSheetTitle() {
            //工具类还是需要减少一点依赖,这里直接写判空
            return (sheetTitle == null || sheetTitle.isEmpty()) ? "sheet_" + UUID.randomUUID() : sheetTitle;
        }
    }
}

效果

生成100w行数据的Excel,大约耗时9.3s(包含生成100w个对象的时间).

测试发现,列数越多耗时越长.2列大概是4s,换成5列就需要9s.故,导出时应尽量只导出需要的列