大数据量Excel模板导出工具(流式写入)

序

自己写了一个支持大批量数据Excel导出的工具类，理论上支持无限大的数据导出（如果你愿意等待漫长的下载时间的话）

提前总结：

• 支持大批量数据导出
• 支持自定义sheet最大行数，并进行多sheet页数据生成
• 支持自适应表头宽度（默认最长列宽，也可自定义列宽）
• 支持自定义表头、表数据样式处理

自定义Excel注解

@Excel注解

import java.lang.annotation.ElementType;  
import java.lang.annotation.Retention;  
import java.lang.annotation.RetentionPolicy;  
import java.lang.annotation.Target;  
import java.math.BigDecimal;  
  
/**  
 * 自定义导出Excel数据注解  
 *   
* @author Cheems  
 */@Retention(RetentionPolicy.RUNTIME)  
@Target(ElementType.FIELD)  
public @interface Excel  
{  
    /**  
     * 导出时在excel中排序  
     */  
    public int sort() default Integer.MAX_VALUE;  
  
    /**  
     * 导出到Excel中的名字.  
     */    public String name() default "";  
  
    /**  
     * 日期格式, 如: yyyy-MM-dd  
     */    public String dateFormat() default "";  
  
    /**  
     * 如果是字典类型，请设置字典的type值 (如: sys_user_sex)  
     */    public String dictType() default "";  
  
    /**  
     * 读取内容转表达式 (如: 0=男,1=女,2=未知)  
     */    public String readConverterExp() default "";  
  
    /**  
     * 分隔符，读取字符串组内容  
     */  
    public String separator() default ",";  
  
    /**  
     * BigDecimal 精度 默认:-1(默认不开启BigDecimal格式化)  
     */    public int scale() default -1;  
  
    /**  
     * BigDecimal 舍入规则 默认:BigDecimal.ROUND_HALF_EVEN  
     */    public int roundingMode() default BigDecimal.ROUND_HALF_EVEN;  
  
    /**  
     * 导出类型（0数字 1字符串）  
     */  
    public ColumnType cellType() default ColumnType.STRING;  
  
    /**  
     * 导出时在excel中每个列的高度 单位为字符  
     */  
    public double height() default 14;  
  
    /**  
     * 导出时在excel中每个列的宽 单位为字符  
     */  
    public double width() default 16;  
  
    /**  
     * 文字后缀,如% 90 变成90%  
     */    public String suffix() default "";  
  
    /**  
     * 当值为空时,字段的默认值  
     */  
    public String defaultValue() default "";  
  
    /**  
     * 提示信息  
     */  
    public String prompt() default "";  
  
    /**  
     * 设置只能选择不能输入的列内容.  
     */    public String[] combo() default {};  
  
    /**  
     * 是否导出数据,应对需求:有时我们需要导出一份模板,这是标题需要但内容需要用户手工填写.  
     */    public boolean isExport() default true;  
  
    /**  
     * 另一个类中的属性名称,支持多级获取,以小数点隔开  
     */  
    public String targetAttr() default "";  
  
    /**  
     * 是否自动统计数据,在最后追加一行统计数据总和  
     */  
    public boolean isStatistics() default false;  
  
    /**  
     * 导出字段对齐方式（0：默认；1：靠左；2：居中；3：靠右）  
     */  
    Align align() default Align.AUTO;  
  
    public enum Align  
    {  
        AUTO(0), LEFT(1), CENTER(2), RIGHT(3);  
        private final int value;  
  
        Align(int value)  
        {  
            this.value = value;  
        }  
  
        public int value()  
        {  
            return this.value;  
        }  
    }  
  
    /**  
     * 字段类型（0：导出导入；1：仅导出；2：仅导入）  
     */  
    Type type() default Type.ALL;  
  
    public enum Type  
    {  
        ALL(0), EXPORT(1), IMPORT(2);  
        private final int value;  
  
        Type(int value)  
        {  
            this.value = value;  
        }  
  
        public int value()  
        {  
            return this.value;  
        }  
    }  
  
    public enum ColumnType  
    {  
        NUMERIC(0), STRING(1);  
        private final int value;  
  
        ColumnType(int value)  
        {  
            this.value = value;  
        }  
  
        public int value()  
        {  
            return this.value;  
        }  
    }  
}

@Excel注解的使用

需要导出的属性需要设置@Excel注解； 不需要导出的属性需要设置@ExcelIgnore注解； @Excel的sort字段可不填，但如果需要自定义某列的宽度时，本对象内的所有@Excel注解的sort字段必填（用于定位列位置）

import com.alibaba.excel.annotation.ExcelIgnore;   
import io.swagger.annotations.ApiModelProperty;  
import lombok.Data;  
  
@Data  
public class BizWorkLineMachineDTO {  
  
    @ApiModelProperty(value = "作业票号")  
    @Excel(name = "作业票号", sort = 1)  
    private String  workTicketNo;  
  
    @ApiModelProperty(value = "作业线名称")  
    @Excel(name = "作业线名称", sort = 2)  
    private String workLineName;  
  
    @ApiModelProperty(value = "班次")  
    @Excel(name = "班次", sort = 3)  
    private String classNoName;  
  
    @ApiModelProperty(value = "作业状态")  
    @Excel(name = "作业状态", sort = 4)  
    private String workStatus;  
  
    @ApiModelProperty(value = "作业类型名称")  
    @Excel(name = "作业类型", width=10, sort = 5)  
    private String workTypeName;  
  
    @ApiModelProperty(value = "作业类型")  
    @ExcelIgnore  
    private Integer workType;  
}

@Excel注解的处理

声明一个注解处理类，处理目标对象，提取@Excel注解相关信息

  
import cn.hutool.core.util.ReflectUtil;  
  
import java.lang.reflect.Field;  
import java.util.*;  
import java.util.stream.Collectors;  
  
/**  
 * @author Cheems  
 * @since 2025-02-25 14:52  
 */public class ExcelFieldFilter {  
    public static List<Field> getAnnotatedFields(Class<?> clazz) {  
        List<Field> fields = new ArrayList<>();  
        Class<?> current = clazz;  
        while (current != null) {  
            for (Field field : current.getDeclaredFields()) {  
                if (field.isAnnotationPresent(Excel.class)) {  
                    fields.add(field);  
                }  
            }  
            current = current.getSuperclass();  
        }  
        return fields.stream()  
                .sorted(Comparator.comparing(f ->  
                        f.getAnnotation(Excel.class).sort()))  
                .collect(Collectors.toList());  
    }  
  
  
    public static Map<Integer, Field> getFieldMap(Class<?> clazz) {  
        Map<Integer, Field> map = new LinkedHashMap<>();  
        Field[] fields = ReflectUtil.getFields(clazz);  
        List<Field> fieldList = Arrays.stream(fields).filter(e -> e.isAnnotationPresent(Excel.class))  
                .sorted(Comparator.comparingInt(f -> {  
                    return f.getAnnotation(Excel.class).sort();  
                })).collect(Collectors.toList());  
  
  
        for (int i = 0; i < fieldList.size(); i++) {  
            Field field = fieldList.get(i);  
            field.setAccessible(true);  
            map.put(i, field);  
        }  
        return map;  
    }  
}

流式导出excel工具

声明函数式接口

用于传入分页查询函数

import java.util.List;  
  
@FunctionalInterface  
public interface PageQueryFunction<Q, R> {  
    List<R> query(int pageNum, int pageSize, Q queryParams);  
}

导出工具

import com.alibaba.excel.EasyExcel;  
import com.alibaba.excel.ExcelWriter;  
import com.alibaba.excel.write.metadata.WriteSheet;  
import org.slf4j.Logger;  
import org.slf4j.LoggerFactory;  
  
import javax.servlet.http.HttpServletResponse;  
import java.io.IOException;  
import java.util.List;  
  
/**  
 * @author Cheems  
 * @since 2025-02-24 15:37  
 */public class StreamingExcelUtil<T> {  
  
    private static final Logger log = LoggerFactory.getLogger(StreamingExcelUtil.class);  
  
    /**  
     * Excel sheet最大行数，默认65536  
     */    public static final int sheetSize = 59999;  
  
    /**  
     * 工作表名称  
     */  
    private String sheetName;  
  
    /**  
     * 实体对象  
     */  
    public Class<T> clazz;  
  
    public StreamingExcelUtil(Class<T> clazz) {  
        this.clazz = clazz;  
    }  
  
    public void init(String sheetName) {  
        this.sheetName = sheetName;  
    }  
  
    /**  
     * 通用流式导出方法  
     *  
     * @param response      HTTP响应对象  
     * @param queryParams   查询参数（任意类型）  
     * @param pageSize      分页大小  
     * @param queryFunction 查询方法（函数式接口）  
     * @param clazz         Excel映射类  
     * @param <Q>           查询参数类型  
     * @param <R>           返回结果类型  
     */  
    public <Q, R> void exportStreaming(  
            HttpServletResponse response,  
            String fileName,  
            Q queryParams,  
            int pageSize,  
            PageQueryFunction<Q, R> queryFunction,  
            Class<R> clazz) throws IOException {  
        // 初始化响应流  
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");  
        this.init(fileName);  
        ExcelWriter excelWriter = null;  
        try {  
            // 构建ExcelWriter（根据是否自定义表头）  
            // 注册自定义处理器  
            excelWriter = EasyExcel.write(response.getOutputStream()).build();  
  
  
            // Sheet控制变量  
            int currentSheetIndex = 1;  
            int currentSheetRowCount = 0;  
            WriteSheet currentSheet = createNewSheet(clazz, currentSheetIndex);  
  
            // 分页查询处理  
            int pageNum = 1;  
            while (true) {  
                List<R> dataChunk = queryFunction.query(pageNum, pageSize, queryParams);  
                if (dataChunk == null || dataChunk.isEmpty()) break;  
  
                int remainingRows = sheetSize - currentSheetRowCount;  
  
                if (remainingRows <= 0) {  
                    // 创建新Sheet  
                    currentSheetIndex++;  
                    currentSheet = createNewSheet(clazz, currentSheetIndex);  
                    currentSheetRowCount = 0;  
                    remainingRows = sheetSize;  
                }  
  
                if (dataChunk.size() <= remainingRows) {  
                    // 整块写入当前Sheet  
                    excelWriter.write(dataChunk, currentSheet);  
                    currentSheetRowCount += dataChunk.size();  
                } else {  
                    // 分割数据块  
                    List<R> firstPart = dataChunk.subList(0, remainingRows);  
                    List<R> secondPart = dataChunk.subList(remainingRows, dataChunk.size());  
  
                    excelWriter.write(firstPart, currentSheet);  
                    currentSheetRowCount += remainingRows;  
  
                    // 创建新Sheet写入剩余数据  
                    currentSheetIndex++;  
                    currentSheet = createNewSheet(clazz, currentSheetIndex);  
                    excelWriter.write(secondPart, currentSheet);  
                    currentSheetRowCount = secondPart.size();  
                }  
  
                dataChunk.clear();  
                pageNum++;  
            }  
        } finally {  
            if (excelWriter != null) {  
                excelWriter.finish();  
            }  
        }  
    }  
  
  
    private WriteSheet createNewSheet(Class<?> clazz, int sheetIndex) {  
        String sheetName;  
        // 设置工作表的名称.  
        if (sheetIndex == 0) {  
            sheetName = this.sheetName;  
        } else {  
            sheetName = this.sheetName + sheetIndex;  
        }  
        return EasyExcel.writerSheet(sheetName)  
                .relativeHeadRowIndex(1)  
                .registerWriteHandler(new CustomColumnWidthStyleStrategy(clazz))  
                .registerWriteHandler(new CustomDataWriteHandler(clazz))  
                .registerWriteHandler(new CustomExcelHeaderHandler(clazz))  
                .build();  
    }  
}

自定义Excel处理器

表头处理器

  
import com.alibaba.excel.write.handler.SheetWriteHandler;  
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;  
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;  
import org.apache.poi.ss.usermodel.*;  
  
import java.lang.reflect.Field;  
import java.util.List;  
  
/**  
 * @author Cheems  
 * @since 2025-02-24 15:42  
 */public class CustomExcelHeaderHandler implements SheetWriteHandler {  
    private final Class<?> dataClazz;  
  
    public CustomExcelHeaderHandler(Class<?> dataClazz) {  
        this.dataClazz = dataClazz;  
    }  
  
    @Override  
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {  
  
    }  
  
    @Override  
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {  
        // 获取Sheet对象  
        Sheet sheet = writeSheetHolder.getSheet();  
        Workbook workbook = sheet.getWorkbook();  
  
        // 创建表头行  
        Row headerRow = sheet.createRow(0);  
  
        // 获取所有字段的Excel注解  
        List<Field> fields = ExcelFieldFilter.getAnnotatedFields(dataClazz);  
  
        // 创建表头样式（对应原ExcelUtil的header样式）  
        CellStyle headerStyle = createHeaderStyle(workbook);  
  
        // 创建表头单元格  
        int columnIndex = 0;  
        for (Field field : fields) {  
            Excel excelAnnotation = field.getAnnotation(Excel.class);  
            if (excelAnnotation != null) {  
                Cell cell = headerRow.createCell(columnIndex++);  
                cell.setCellValue(excelAnnotation.name());  
                cell.setCellStyle(headerStyle);  
            }  
        }  
    }  
  
    private CellStyle createHeaderStyle(Workbook workbook) {  
        CellStyle style = workbook.createCellStyle();  
        style.setAlignment(HorizontalAlignment.CENTER);  
        style.setVerticalAlignment(VerticalAlignment.CENTER);  
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());  
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);  
        style.setBorderRight(BorderStyle.THIN);  
        style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());  
        style.setBorderLeft(BorderStyle.THIN);  
        style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());  
        style.setBorderTop(BorderStyle.THIN);  
        style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());  
        style.setBorderBottom(BorderStyle.THIN);  
        style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());  
  
        Font font = workbook.createFont();  
        font.setFontHeightInPoints((short) 10);  
        font.setBold(true);  
        font.setColor(IndexedColors.WHITE.getIndex());  
        style.setFont(font);  
        return style;  
    }  
}

表数据处理器

import com.alibaba.excel.metadata.CellData;  
import com.alibaba.excel.metadata.Head;  
import com.alibaba.excel.write.handler.CellWriteHandler;  
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;  
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;  
import org.apache.poi.ss.usermodel.*;  
  
import java.lang.reflect.Field;  
import java.math.BigDecimal;  
import java.util.Date;  
import java.util.List;  
import java.util.Map;  
import java.util.concurrent.ConcurrentHashMap;  
  
/**  
 * @author Cheems  
 * @since 2025-02-24 15:47  
 */public class CustomDataWriteHandler implements CellWriteHandler {  
    private final Class<?> dataClazz;  
    private final Map<String, CellStyle> styleCache = new ConcurrentHashMap<>();  
  
    public CustomDataWriteHandler(Class<?> dataClazz) {  
        this.dataClazz = dataClazz;  
    }  
  
    @Override  
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder,  
                                 WriteTableHolder writeTableHolder,  
                                 Row row,  
                                 Head head,  
                                 Integer columnIndex,  
                                 Integer relativeRowIndex,  
                                 Boolean isHead) {  
        // 用于提前准备数据（如果需要）  
    }  
  
    @Override  
    public void afterCellCreate(WriteSheetHolder writeSheetHolder,  
                                WriteTableHolder writeTableHolder,  
                                Cell cell,  
                                Head head,  
                                Integer relativeRowIndex,  
                                Boolean isHead) {  
        if (isHead) return; // 跳过表头  
  
        Workbook workbook = writeSheetHolder.getSheet().getWorkbook();  
  
        // 获取字段元数据  
        List<Field> fields = ExcelFieldFilter.getAnnotatedFields(dataClazz);  
        if (cell.getColumnIndex() + 1 > fields.size()) return;  
        Field field = fields.get(cell.getColumnIndex());  
        Excel excel = field.getAnnotation(Excel.class);  
  
        // 创建或获取样式  
        String styleKey = buildStyleKey(excel);  
        CellStyle style = styleCache.computeIfAbsent(styleKey, k ->  
                createDataStyle(workbook, excel));  
  
        // 应用样式  
        cell.setCellStyle(style);  
    }  
  
    @Override  
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder,  
                                       WriteTableHolder writeTableHolder,  
                                       CellData cellData,  
                                       Cell cell,  
                                       Head head,  
                                       Integer relativeRowIndex,  
                                       Boolean isHead) {  
        if (isHead) return;  
  
        // 获取字段和注解  
        List<Field> fields = ExcelFieldFilter.getAnnotatedFields(dataClazz);  
        if (cell.getColumnIndex() + 1 > fields.size()) return;  
        Field field = fields.get(cell.getColumnIndex());  
        Excel excel = field.getAnnotation(Excel.class);  
  
        // 处理特殊数据类型  
        Object value = cellData.getData();  
        if (value instanceof Date && StringUtils.isNotEmpty(excel.dateFormat())) {  
            cell.setCellValue(DateUtils.formatDate((Date) value, excel.dateFormat()));  
        } else if (value instanceof Number && excel.scale() >= 0) {  
            BigDecimal bd = new BigDecimal(value.toString())  
                    .setScale(excel.scale(), excel.roundingMode());  
            cell.setCellValue(bd.toString());  
        }  
    }  
  
    @Override  
    public void afterCellDispose(WriteSheetHolder writeSheetHolder,  
                                 WriteTableHolder writeTableHolder,  
                                 List<CellData> cellDataList,  
                                 Cell cell,  
                                 Head head,  
                                 Integer relativeRowIndex,  
                                 Boolean isHead) {  
        // 清理资源（如有需要）  
    }  
  
    private String buildStyleKey(Excel excel) {  
        return excel.align().name() + "_" + excel.scale();  
    }  
  
    private CellStyle createDataStyle(Workbook workbook, Excel excel) {  
        CellStyle style = workbook.createCellStyle();  
  
        // 对齐方式  
        style.setAlignment(HorizontalAlignment.CENTER);  
        style.setVerticalAlignment(VerticalAlignment.CENTER);  
  
        // 边框  
        style.setBorderTop(BorderStyle.THIN);  
        style.setBorderBottom(BorderStyle.THIN);  
        style.setBorderLeft(BorderStyle.THIN);  
        style.setBorderRight(BorderStyle.THIN);  
        style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());  
        style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());  
        style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());  
        style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());  
  
        // 字体  
        Font font = workbook.createFont();  
        font.setFontHeightInPoints((short) 10);  
        style.setFont(font);  
  
        // 数字格式  
        if (excel.scale() > 0) {  
            String format = "0." + StringUtils.repeat("0", excel.scale());  
            style.setDataFormat(workbook.createDataFormat().getFormat(format));  
        }  
  
        return style;  
    }  
}

自定义表宽度处理策略

import com.alibaba.excel.enums.CellDataTypeEnum;  
import com.alibaba.excel.metadata.CellData;  
import com.alibaba.excel.metadata.Head;  
import com.alibaba.excel.util.CollectionUtils;  
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;  
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;  
import org.apache.poi.ss.usermodel.Cell;  
  
import java.lang.reflect.Field;  
import java.util.HashMap;  
import java.util.List;  
import java.util.Map;  
  
public class CustomColumnWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {  
    private static final int MAX_COLUMN_WIDTH = 255;  
    private final Class<?> dataClazz;  
    private Map<String, Map<Integer, Integer>> cache = new HashMap<>(8);  
  
    public CustomColumnWidthStyleStrategy(Class<?> clazz) {  
        dataClazz = clazz;  
    }  
  
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {  
        // 使用该自定义行宽策略时，必须声明@Excel的sort字段，用以定位类属性位置  
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);  
        if (needSetWidth) {  
            int columnIndex = cell.getColumnIndex();  
            Map<Integer, Field> fieldMap = ExcelFieldFilter.getFieldMap(dataClazz);  
            if (fieldMap.containsKey(columnIndex)) {  
                Map<Integer, Integer> maxColumnWidthMap = (Map) this.cache.get(writeSheetHolder.getSheetName());  
                if (maxColumnWidthMap == null) {  
                    maxColumnWidthMap = new HashMap<>(16);  
                    this.cache.put(writeSheetHolder.getSheetName(), maxColumnWidthMap);  
                }  
                Field field = fieldMap.get(columnIndex);  
                int columnWidth = 0;  
                if (field.isAnnotationPresent(Excel.class)) {  
                    Excel annotation = field.getAnnotation(Excel.class);  
                    int width = (int)annotation.width();  
                    if (width != 16) {  
                        // 16为默认值  
                        // 设置了width属性  
                        columnWidth = width;  
                    } else {  
                        columnWidth = this.dataLength(cellDataList, cell, isHead);  
                    }  
                }else{  
                    columnWidth = this.dataLength(cellDataList, cell, isHead);  
                }  
                if (columnWidth >= 0) {  
                    if (columnWidth > 255) {  
                        columnWidth = 255;  
                    }  
                    Integer maxColumnWidth = (Integer) maxColumnWidthMap.get(columnIndex);  
                    if (maxColumnWidth == null || columnWidth > maxColumnWidth) {  
                        maxColumnWidthMap.put(columnIndex, columnWidth);  
                        writeSheetHolder.getSheet().setColumnWidth(columnIndex, columnWidth * 256);  
                    }  
  
                }  
            }  
  
        }  
    }  
  
    private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {  
        if (isHead) {  
            return cell.getStringCellValue().getBytes().length;  
        } else {  
            CellData cellData = (CellData) cellDataList.get(0);  
            CellDataTypeEnum type = cellData.getType();  
            if (type == null) {  
                return -1;  
            } else {  
                switch (type) {  
                    case STRING:  
                        return cellData.getStringValue().getBytes().length;  
                    case BOOLEAN:  
                        return cellData.getBooleanValue().toString().getBytes().length;  
                    case NUMBER:  
                        return cellData.getNumberValue().toString().getBytes().length;  
                    default:  
                        return -1;  
                }  
            }  
        }  
    }  
}

流式导出工具的使用

StreamingExcelUtil<BizWorkLineMachineDTO> util = new StreamingExcelUtil<BizWorkLineMachineDTO>(BizWorkLineMachineDTO.class);  
util.exportStreaming(  
        response,  
        "作业机械汇总",  
        bizWorkLineMachine, // 查询参数  
        5000,  // 分页大小  
        (pageNum, pageSize, q) -> {  
            // 使用PageHelper分页（或自定义分页逻辑）  
            Page<?> page = new Page<>(pageNum, pageSize);  
            page.setMaxLimit(5000L);  
            return bizWorkLineMachineMapper.selectMachineListSummary(page, q);  
        },  
        BizWorkLineMachineDTO.class  
);