基于Hutool工具类定制化Excel开发-CSDN博客

基于 Hutool 工具类实现定制化 Excel 报表导出实践

一、引言

在企业级应用开发中，Excel 报表导出是一个常见的功能需求。虽然市面上存在多种 Java Excel 操作工具（如 Apache POI、EasyExcel、JXLS 等），但在实际开发中我们经常发现这些工具要么过于底层（如 POI），要么灵活性不足（如 EasyExcel）。本文介绍如何基于 Hutool 工具类进行二次开发，实现高度定制化的 Excel 报表导出功能
选择 Hutool 的理由：
- 轻量级封装 Apache POI，使用简单
- 代码简洁，适合快速导出场景
- 支持样式定制和数据列表直接导出

二、定制化开发

1. 注解体系设计

为增强 Excel 导出的灵活性需要自定义一些注解使用

1.1. `@Excel` 注解

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Excel {
    String title() default "";      // 列标题
    boolean needSum() default false; // 是否需要汇总
    int index() default 0;          // 列顺序
}

1.2. `@MergeColumn` 注解

@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
@Repeatable(MergeColumns.class)
public @interface MergeColumn {
    int startColumn();             // 起始列
    int endColumn();               // 结束列
    String value() default "";     // 合并区域显示值
    IndexedColors backgroundColor() default IndexedColors.WHITE; // 背景色
}

1.3. `@MergeColumns` 注解

搭配MergeColumn 使用用于指定列合并

@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface MergeColumns {
    MergeColumn[] value();
}

2. 核心组件实现

2.1 `CustomBigExcelWriter`

继承 Hutool 的 ExcelWriter，增强以下功能：

package com.jerry.excel;

import cn.hutool.core.collection.ListUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.io.IORuntimeException;
import cn.hutool.poi.excel.ExcelWriter;
import cn.hutool.poi.excel.WorkbookUtil;
import cn.hutool.poi.excel.cell.CellSetter;
import cn.hutool.poi.excel.cell.CellUtil;
import com.jerry.anno.Excel;
import com.jerry.anno.MergeColumn;
import com.jerry.anno.MergeColumns;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ObjectUtils;
import org.springframework.util.StringUtils;
import java.io.File;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.DecimalFormat;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;

/**
 * @author jerry
 * @ClassName CustomBigExcelWriter
 * @Description 继承hutool的ExcelWriter实现定制化开发 https://plus.hutool.cn/apidocs/cn/hutool/poi/excel/ExcelWriter.html
 * @Date 2025/2/8 17:12
 * @Version 1.0
 */
public class CustomBigExcelWriter extends ExcelWriter {

    public static final int DEFAULT_WINDOW_SIZE = 100;
    private boolean isFlushed;

    public CustomBigExcelWriter() {
        this(100);
    }

    public CustomBigExcelWriter(int rowAccessWindowSize) {
        this(WorkbookUtil.createSXSSFBook(rowAccessWindowSize), null);
    }

    public CustomBigExcelWriter(int rowAccessWindowSize, boolean compressTmpFiles, boolean useSharedStringsTable, String sheetName) {
        this(WorkbookUtil.createSXSSFBook(rowAccessWindowSize, compressTmpFiles, useSharedStringsTable), sheetName);
    }

    public CustomBigExcelWriter(int rowAccessWindowSize, String sheetName) {
        this(WorkbookUtil.createSXSSFBook(rowAccessWindowSize), sheetName);
    }

    public CustomBigExcelWriter(String destFilePath, String sheetName) {
        this(FileUtil.file(destFilePath), sheetName);
    }

    public CustomBigExcelWriter(File destFile) {
        this(destFile, null);
    }

    public CustomBigExcelWriter(File destFile, String sheetName) {
        this(destFile.exists() ? WorkbookUtil.createSXSSFBook(destFile) : WorkbookUtil.createSXSSFBook(), sheetName);
        this.destFile = destFile;
    }

    public CustomBigExcelWriter(SXSSFWorkbook workbook, String sheetName) {
        this(WorkbookUtil.getOrCreateSheet(workbook, sheetName));
    }

    public CustomBigExcelWriter(Sheet sheet) {
        super(sheet);
    }

    public CustomBigExcelWriter setSheet(String sheetName) {
        super.setSheet(sheetName);

        // 重新初始化
        this.initRowCel();
        this.customHeadCellStyle = CustomCellStyle.createDefaultHeadCellStyle(this.workbook);
        this.customCellStyle = CustomCellStyle.createDefaultCellStyle(this.workbook);

        return this;
    }


    public CustomBigExcelWriter autoSizeColumn(int columnIndex) {
        SXSSFSheet sheet = (SXSSFSheet) this.sheet;
        sheet.trackColumnForAutoSizing(columnIndex);
        super.autoSizeColumn(columnIndex);
        sheet.untrackColumnForAutoSizing(columnIndex);
        return this;
    }

    public CustomBigExcelWriter autoSizeColumnAll() {
        SXSSFSheet sheet = (SXSSFSheet) this.sheet;
        sheet.trackAllColumnsForAutoSizing();
        super.autoSizeColumnAll();
        sheet.untrackAllColumnsForAutoSizing();
        return this;
    }

    public ExcelWriter flush(OutputStream out, boolean isCloseOut) throws IORuntimeException {
        if (!this.isFlushed) {
            this.isFlushed = true;
            return super.flush(out, isCloseOut);
        } else {
            return this;
        }
    }

    public void close() {
        if (null != this.destFile && !this.isFlushed) {
            this.flush();
        }

        ((SXSSFWorkbook) this.workbook).dispose();
        super.closeWithoutFlush();
    }
    // ==================================================================以上为继承ExcelWriter必备的一些代码 尽量不要修改以免出bug========================================================================================================================================================================================


    // ==================================================================以下为定制化业务开发========================================================================================================================================================================================
    private CellStyle customHeadCellStyle;
    private CellStyle customCellStyle;

    private boolean needSum;

    public CustomBigExcelWriter(String destFilePath) {
        this(destFilePath, null);
        initRowCel();
        this.customHeadCellStyle = CustomCellStyle.createDefaultHeadCellStyle(this.workbook); // 初始化默认表头样式
        this.customCellStyle = CustomCellStyle.createDefaultCellStyle(this.workbook); // 初始化单元格样式
    }

    private void initRowCel() {
        setDefaultRowHeight(20);
        setColumnWidth(-1, 25);
    }

    /**
     * 跳过两行
     *
     * @return
     */
    public CustomBigExcelWriter passTwoRows() {
        this.passCurrentRow();
        this.passCurrentRow();
        return this;
    }


    /**
     * 通过反射获取父类的currentRow
     *
     * @return
     */
    public int getParentAndIncrement() {
        try {
            Field field = ExcelWriter.class.getDeclaredField("currentRow");
            field.setAccessible(true);

            AtomicInteger currentRow = (AtomicInteger) field.get(this);
            return currentRow.getAndIncrement();
        } catch (NoSuchFieldException | IllegalAccessException e) {
            throw new RuntimeException("无法获取ExcelWriter中的currentRow", e);
        }
    }

    public <T> void write(List<T> data, String defaultNullValue, CellStyle headerCellStyle, boolean includeHeader) {
        if (CollectionUtils.isEmpty(data)) return;

        Class<?> clazz = data.get(0).getClass();
        List<Field> allFields = getAllFields(clazz);

        // 过滤不被@Excel注解标识的字段 并按下标进行排序、设置这些字段可以被访问 因为后面会频繁访问这些字段
        allFields = allFields.stream().filter(field -> field.isAnnotationPresent(Excel.class)).peek(field -> field.setAccessible(true)).sorted(Comparator.comparingInt(field -> field.getAnnotation(Excel.class).index())).collect(Collectors.toList());

        // 标题前的合并区域
        if (clazz.isAnnotationPresent(MergeColumns.class)) {
            MergeColumns mergeColumns = clazz.getAnnotation(MergeColumns.class);

            for (MergeColumn mergeColumn : mergeColumns.value()) {
                CellStyle mergeColumnCellStyle = workbook.createCellStyle();
                mergeColumnCellStyle.setAlignment(HorizontalAlignment.LEFT);  // 设置文字居左
                mergeColumnCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);  // 垂直居中
                mergeColumnCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 应用背景色
                mergeColumnCellStyle.setFillForegroundColor(mergeColumn.backgroundColor().index);

                CellStyle finalCellStyle = CustomCellStyle.mergeCellStyles(mergeColumnCellStyle, (SXSSFWorkbook) workbook);
                if (mergeColumn.startColumn() != mergeColumn.endColumn()) {
                    merge(getCurrentRow(), getCurrentRow(), mergeColumn.startColumn(), mergeColumn.endColumn(), mergeColumn.value(), finalCellStyle);
                } else {
                    // 如果开始跟结束都是一样的列 那么不合并直接写出 满足定制化的报表
                    writeCellValue(mergeColumn.startColumn(), getCurrentRow(), mergeColumn.value(), finalCellStyle);
                }
            }
            getParentAndIncrement(); // 行号+1
        }

        // 标题
        writeHeader(headerCellStyle, includeHeader, allFields);

        // 数据
        writeBasicData(data, defaultNullValue, allFields);
    }

    /**
     * 获取所有字段 包括父类
     *
     * @param clazz
     * @return
     */
    public List<Field> getAllFields(Class<?> clazz) {
        List<Field> allFields = new ArrayList<>();
        while (clazz != null) {
            Field[] declaredFields = clazz.getDeclaredFields();
            allFields.addAll(Arrays.asList(declaredFields));
            clazz = clazz.getSuperclass();
        }
        return allFields;
    }

    /**
     * 写入标题数据
     *
     * @param headerCellStyle
     * @param includeHeader
     * @param allFields
     */
    private void writeHeader(CellStyle headerCellStyle, boolean includeHeader, List<Field> allFields) {
        if (includeHeader) {
            Row headerRow = this.sheet.createRow(getParentAndIncrement());

            // 合并默认样式 默认样式提供一些基础的样式
            CellStyle finalStyle = (headerCellStyle != null) ? CustomCellStyle.mergeCellStyles(headerCellStyle, (SXSSFWorkbook) workbook) : CustomCellStyle.mergeCellStyles(customHeadCellStyle, (SXSSFWorkbook) workbook);

            for (int i = 0; i < allFields.size(); i++) {
                Field field = allFields.get(i);
                Excel excelAnnotation = field.getAnnotation(Excel.class);

//                // title如果为null使用字段名
//                String title = StringUtils.hasText(excelAnnotation.title()) ? excelAnnotation.title() : field.getName();
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(excelAnnotation.title());
                cell.setCellStyle(finalStyle);
            }
        }
    }

    /**
     * 写入基础数据
     *
     * @param data
     * @param defaultNullValue
     * @param allFields
     * @param <T>
     */
    private <T> void writeBasicData(List<T> data, String defaultNullValue, List<Field> allFields) {
        Map<String, BigDecimal> sumMap = new HashMap<>();

        // 单元格样式
        CellStyle cellStyle = CustomCellStyle.mergeCellStyles(null, (SXSSFWorkbook) workbook);
        for (T item : data) {
            Row row = this.sheet.createRow(getParentAndIncrement());
            for (int i = 0; i < allFields.size(); i++) {
                Field field = allFields.get(i);
                Excel excelAnnotation = field.getAnnotation(Excel.class);

                Object value = null;
                try {
                    value = field.get(item);
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }

                String fieldType = field.getType().getSimpleName();
                if ("Date".equals(fieldType) && !ObjectUtils.isEmpty(value)) {
                    value = convertToDDMMYYYY((Date) value);
                } else if ("double".equalsIgnoreCase(fieldType) && !ObjectUtils.isEmpty(value)) {
                    DecimalFormat df = new DecimalFormat("#.00");
                    value = df.format(value);
                }

                // 判断是否需要计算统计汇总
                if (excelAnnotation.needSum()) {
                    needSum = true;
                    String title = excelAnnotation.title();
                    BigDecimal sumValue = sumMap.getOrDefault(title, BigDecimal.ZERO);
                    BigDecimal numericValue;

                    if (value == null) {
                        numericValue = BigDecimal.ZERO;
                    } else if (value instanceof Integer) {
                        // Integer类型直接转换为BigDecimal 使用value.toString() 使用这种字符串创建BigDecimal不会带有小数位
                        numericValue = new BigDecimal(value.toString());
                    } else {
                        try {
                            numericValue = new BigDecimal(value.toString());
                            // 四舍五入保留两位小数
                            numericValue = numericValue.setScale(2, RoundingMode.HALF_UP);
                        } catch (NumberFormatException ex) {
                            numericValue = BigDecimal.ZERO;
                        }
                    }

                    sumValue = sumValue.add(numericValue);
                    sumMap.put(title, sumValue);
                }

                // 写入单元格
                Cell cell = handleCellValue(defaultNullValue, row, i, value);
                cell.setCellStyle(cellStyle);
            }
        }

        // 写入汇总行
        if (needSum) {
            writeSummaryRow(allFields, sumMap);
        }
    }

    public static String convertToDDMMYYYY(Date date) {
        DateTimeFormatter ddMMyyyy = DateTimeFormatter.ofPattern("dd-MM-yyyy");
        return date == null ? "" : ddMMyyyy.format(date.toInstant().atZone(ZoneId.systemDefault()));
    }

    public <T> void write(List<T> data, String defaultNullValue, boolean includeHeader) {
        // 空两行
        passTwoRows();
        write(data, defaultNullValue, null, includeHeader);
    }

    /**
     * 写入单元格值
     *
     * @param x
     * @param y
     * @param value
     * @param cellStyle
     * @return
     */
    public CustomBigExcelWriter writeCellValue(int x, int y, Object value, CellStyle cellStyle) {
        Cell cell = this.getOrCreateCell(x, y);
        CellUtil.setCellValue(cell, value, this.getStyleSet(), false);
        if (cellStyle != null) {
            cell.setCellStyle(cellStyle); // 使用自定义样式
        }
        return this;
    }

    /**
     * 写入带样式的行
     * 如果需要写入map 且是需要输出标题的 使用 ExcelWriter writeRow(Map<?, ?> rowMap, boolean isWriteKeyAsHead)
     *
     * @param rowData
     * @param cellStyle
     * @return
     */
    public CustomBigExcelWriter writeStyledRow(Object rowData, CellStyle cellStyle) {
        Iterable<?> iterableRowData;

        if (rowData instanceof Iterable<?>) {
            iterableRowData = (Iterable<?>) rowData;
        } else if (rowData instanceof Map<?, ?>) {
            iterableRowData = ((Map<?, ?>) rowData).entrySet();
        } else {
            iterableRowData = Collections.singleton(rowData);
        }

        List<CellSetter> row = new ArrayList<>();
        for (Object data : iterableRowData) {
            row.add(cell -> {
                cell.setCellValue(data.toString());
                if (cellStyle != null) {
                    cell.setCellStyle(cellStyle);
                }
            });
        }
        this.writeRow(row);

        return this;
    }

    /**
     * 写入带样式的列
     *
     * @param rowData
     * @param cellStyle
     * @return
     */
    public CustomBigExcelWriter writeStyledCol(Object rowData, CellStyle cellStyle) {
        Iterable<?> iterableRowData;

        if (rowData instanceof Iterable<?>) {
            iterableRowData = (Iterable<?>) rowData;
        } else if (rowData instanceof Map<?, ?>) {
            iterableRowData = ((Map<?, ?>) rowData).entrySet();
        } else {
            iterableRowData = Collections.singleton(rowData);
        }

        List<Object> cols = new ArrayList<>();
        for (Object data : iterableRowData) {
            cols.add(ListUtil.of((CellSetter) cell -> {
                cell.setCellValue(data.toString());
                if (cellStyle != null) {
                    cell.setCellStyle(cellStyle);
                }
            }));
        }
        this.writeRow(cols);

        return this;
    }

    /**
     * 写入上一行
     *
     * @param values
     */
    public void writeToPreviousRow(Object... values) {
        // 上一行
        int previousRow = getCurrentRow() + 1;
        for (int i = 0; i < values.length; i++) {
            writeCellValue(i, previousRow, values[i], customCellStyle);
        }
    }

    /**
     * 写入当前行
     *
     * @param values
     */
    public void writeToCurrentRow(Object... values) {
        // 上一行
        for (int i = 0; i < values.length; i++) {
            writeCellValue(i, getCurrentRow(), values[i], customCellStyle);
        }
    }

    /**
     * 处理单元格值
     *
     * @param defaultNullValue
     * @param row
     * @param i
     * @param value
     * @return
     */
    private static Cell handleCellValue(String defaultNullValue, Row row, int i, Object value) {
        Cell cell = row.createCell(i);
        if (Objects.isNull(value)) {
            // 处理空值
            cell.setCellValue(StringUtils.hasText(defaultNullValue) ? defaultNullValue : "--");
        } else {

            if (value instanceof BigDecimal) {
                // 四舍五入保留两位小数
                BigDecimal roundedValue = new BigDecimal(((Number) value).doubleValue()).setScale(2, RoundingMode.HALF_UP);
                cell.setCellValue(roundedValue.toString());
            } else if (value instanceof Boolean) {
                cell.setCellValue(String.valueOf(value));
            } else if (value instanceof Date) {
                cell.setCellValue(DateUtil.formatDateTime((Date) value));
            } else if (value instanceof Long) {
                cell.setCellValue(value.toString());
            } else if (value instanceof Integer) {
                cell.setCellValue(value.toString());
            } else {
                cell.setCellValue(value.toString());
            }
        }
        return cell;
    }

    /**
     * 写入汇总行
     *
     * @param fields
     * @param sumMap
     */
    private void writeSummaryRow(List<Field> fields, Map<String, BigDecimal> sumMap) {
        Row summaryRow = this.sheet.createRow(getParentAndIncrement());

        // 创建单元格样式
        CellStyle summaryRowStyle = CustomCellStyle.handleSummaryRowStyle(this.workbook);

        for (int i = 0; i < fields.size(); i++) {
            Excel excelAnnotation = fields.get(i).getAnnotation(Excel.class);
            String title = excelAnnotation.title();
            if (sumMap.containsKey(title)) {
                Cell cell = summaryRow.createCell(i);
                cell.setCellValue(sumMap.get(title).toString());
                cell.setCellStyle(summaryRowStyle);
            }
        }
    }
}

2.2 `CustomCellStyle`

提供样式工厂方法：

package com.jerry.excel;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

/**
 * @author jerry
 * @ClassName CustomCellStyle
 * @Description
 * @Date 2025/2/12 9:42
 * @Version 1.0
 */
public class CustomCellStyle {

    /**
     * 创建默认的表头样式
     *
     * @return
     */
    public static CellStyle createDefaultHeadCellStyle(Workbook workbook) {
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 12);

        CellStyle headCellStyle = workbook.createCellStyle();
        headCellStyle.setFont(font);
        headCellStyle.setAlignment(HorizontalAlignment.LEFT);  // 设置文字居左
        headCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
        // 设置填充模式为实心前景色 不填充无法显示背景色
        headCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        // 设置边框样式
        headCellStyle.setBorderBottom(BorderStyle.THIN);
        headCellStyle.setBorderTop(BorderStyle.THIN);
        headCellStyle.setBorderLeft(BorderStyle.THIN);
        headCellStyle.setBorderRight(BorderStyle.THIN);
        return headCellStyle;
    }

    /**
     * 创建默认的单元格样式
     *
     * @return
     */
    public static CellStyle createDefaultCellStyle(Workbook workbook) {
        Font font = workbook.createFont();
        font.setBold(false);
        font.setFontHeightInPoints((short) 11);

        CellStyle style = workbook.createCellStyle();
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.LEFT);  // 设置文字居左
        style.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
        return style;
    }

    /**
     * 创建标题行样式
     *
     * @param writer
     * @return
     */
    public static CellStyle createTitleStyle(CustomBigExcelWriter writer) {
        // 获取标题样式
        CellStyle headCellStyle = writer.getWorkbook().createCellStyle();
        Font font = writer.createFont();
        font.setBold(true); // 设置字体加粗
        font.setFontHeightInPoints((short) 12); // 12号字体

        headCellStyle.setFont(font);
        headCellStyle.setAlignment(HorizontalAlignment.LEFT);  // 设置文字居左
        headCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中

        // 设置填充模式为实心前景色 不填充无法显示背景色
        headCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 设置背景颜色为浅蓝色
        headCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());

        return headCellStyle;
    }

    /**
     * 处理汇总行的样式
     *
     * @return
     */
    public static CellStyle handleSummaryRowStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        // 字体加粗
        Font font = workbook.createFont();
        font.setBold(true);

        style.setFont(font);
        style.setAlignment(HorizontalAlignment.LEFT);  // 设置文字居左
        style.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中

        // 设置填充模式为实心前景色
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 设置前景色为浅灰色
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());

        cloneBorderedStyle(style); // 复制边框样式
        return style;
    }


    /**
     * 创建单元格样式
     *
     * @param writer
     * @param isBold   是否加粗
     * @param fontSize 字体大小
     * @return
     */
    public static CellStyle createCellStyle(CustomBigExcelWriter writer, boolean isBold, short fontSize) {
        Font font = writer.createFont();
        font.setBold(isBold);
        font.setFontHeightInPoints(fontSize);

        CellStyle style = writer.getWorkbook().createCellStyle();
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.LEFT);  // 设置文字居左
        style.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
        return style;
    }

    /**
     * 克隆边框样式
     *
     * @param style
     * @return CellStyle
     */
    public static CellStyle cloneBorderedStyle(CellStyle style) {
        // 设置边框实线
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);

        // 确保边框颜色可见
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());

        return style;
    }

    /**
     * 合并自定义样式和边框样式
     *
     * @param customStyle
     * @param workbook
     * @return
     */
    public static CellStyle mergeCellStyles(CellStyle customStyle, SXSSFWorkbook workbook) {
        if (customStyle == null) {
            return cloneBorderedStyle(workbook.createCellStyle());
        }

        return cloneBorderedStyle(customStyle); // 自定义样式叠加边框样式
    }
}

2.3 ExcelUtils

package com.jerry.util;

import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.LocalDateTimeUtil;
import cn.hutool.core.exceptions.DependencyException;
import cn.hutool.core.util.ObjectUtil;
import com.jerry.excel.CustomBigExcelWriter;
import com.jerry.excel.CustomCellStyle;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.CellStyle;

import java.io.File;
import java.time.LocalDate;

/**
 * @author jerry
 * @ClassName ExcelUtils
 * @Description
 * @Date 2025/2/6 16:07
 * @Version 1.0
 */
@Slf4j
public class ExcelUtils {

    public static CustomBigExcelWriter getBigWriter(String destFilePath) {
        try {
            // 如果文件已存在就删除 BigExcelWriter不会替换
            File file = new File(destFilePath);
            if (file.exists()) {
                file.delete();  // 删除已有的文件
            }

            return new CustomBigExcelWriter(destFilePath);
        } catch (NoClassDefFoundError var2) {
            throw new DependencyException((Throwable) ObjectUtil.defaultIfNull(var2.getCause(), var2), "You need to add dependency of 'poi-ooxml' to your project, and version >= 4.1.2", new Object[0]);
        }
    }

    /**
     * 写入固定标题
     *
     * @param writer
     * @param title
     */
    public static void writeReportTitle(CustomBigExcelWriter writer, String title) {
        CellStyle bold14Style = CustomCellStyle.createCellStyle(writer, true, (short) 14);
        // 写入第一行
        writer.writeStyledRow("REHABUS, THE HONG KONG SOCIETY FOR REHABILITATION", bold14Style);
        // 写入第二行
        writer.writeStyledRow(title, bold14Style);
        // 跳过两行进行下一步
        writer.passTwoRows();
    }

    /**
     * 写入固定格式的查询日期行
     *
     * @param writer
     * @param dateName  日期名称
     * @param startDate 开始日期
     * @param endDate   结束日期
     */
    public static void writeSelectDateRow(CustomBigExcelWriter writer, String dateName, LocalDate startDate, LocalDate endDate) {
        // 定义加粗且字体大小为12的样式
        CellStyle bold12Style = CustomCellStyle.createCellStyle(writer, true, (short) 12);

        writer.writeCellValue(0, writer.getCurrentRow(), dateName, bold12Style);
        writer.writeCellValue(1, writer.getCurrentRow(), LocalDateTimeUtil.format(startDate, DatePattern.NORM_DATE_PATTERN), bold12Style);
        writer.writeCellValue(2, writer.getCurrentRow(), "TO", CustomCellStyle.createCellStyle(writer, false, (short) 11));
        writer.writeCellValue(3, writer.getCurrentRow(), LocalDateTimeUtil.format(endDate, DatePattern.NORM_DATE_PATTERN), bold12Style);
        writer.passCurrentRow(); // 空一行 writeCellValue指定写行 所以不会新增行数
    }

    /**
     * 写入固定格式的查询字段行列
     *
     * @param writer
     * @param fieldSize
     * @param y
     * @param dateName
     * @param data
     */
    public static void writeSelectLastRowCell(CustomBigExcelWriter writer, int fieldSize, int y, String dateName, String data) {
        // 定义加粗且字体大小为12的样式
        CellStyle bold12Style = CustomCellStyle.createCellStyle(writer, false, (short) 12);

        writer.writeCellValue(fieldSize - 1, y, dateName, bold12Style);
        writer.writeCellValue(fieldSize, y, data, bold12Style);
    }
}

3. 使用示例

实体类定义：

@Data
@MergeColumns(
        value = {
                @MergeColumn(startColumn = 1, endColumn = 1, value = "A"),
                @MergeColumn(startColumn = 2, endColumn = 2, value = "B"),
                @MergeColumn(startColumn = 3, endColumn = 3, value = "C = A - B"),
        }
)
public class TestReport {
    @Excel(title = "", index = 0)
    private String title;

    @Excel(title = "Overdue Amount", index = 1, needSum = true)
    private BigDecimal overdueAmount;

    @Excel(title = "Disease", index = 2, needSum = true)
    private BigDecimal disease;

    @Excel(title = "Net", index = 3, needSum = true)
    private BigDecimal net;
}

测试用例：

@SpringBootTest
public class CustomBigExcelWriterTests {

    @Test
    void download() {
        // 创建 BigExcelWriter 对象，指定输出文件路径
        String fullPath = "d:/CustomBigExcelWriterTests.csv";

        CustomBigExcelWriter writer = ExcelUtils.getBigWriter(fullPath);

        // 写入第一二行标题
        ExcelUtils.writeReportTitle(writer, "FARE INCOME REPORT DETAIL REPORT");

        List<String> data = Stream.generate(() -> Instancio.create(String.class)).limit(10).collect(Collectors.toList());
        writer.writeStyledRow(data, null);
        // 关闭 writer，释放内存
        writer.close();
    }
}

四、结合实际业务整合策略

为支持多种报表类型，引入策略：

1. 策略接口 `ReportStrategy`

public interface ReportStrategy<T> {
    String write(T params);  // 执行导出
    String getFullPath();    // 获取文件路径
    void writeContent(CustomBigExcelWriter writer, T params); // 内容写入逻辑
}

2. 策略工厂 `ReportStrategyFactory`

@Component
public class ReportStrategyFactory {
    private final Map<Class<?>, ReportStrategy> strategyMap = new HashMap<>();

    @Autowired
    public ReportStrategyFactory(List<ReportStrategy> strategies) {
        for (ReportStrategy strategy : strategies) {
            Class<?> genericType = GenericTypeResolver.resolveTypeArgument(strategy.getClass(), ReportStrategy.class);
            if (genericType != null) {
                strategyMap.put(genericType, strategy);
            }
        }
    }

    public <T> ReportStrategy<T> getStrategy(Class<T> clazz) {
        ReportStrategy strategy = strategyMap.get(clazz);
        if (strategy == null) {
            throw new RuntimeException("No strategy found for class: " + clazz.getName());
        }
        return strategy;
    }
}

3. 抽象公共策略实现`AbstractReportProcessor`

@Slf4j
public abstract class AbstractReportProcessor<T> implements ReportStrategy<T> {
    @Autowired
    protected ReportConfig reportConfig;

    public String writeReport(T params) {
        String fullPath = getFullPath();
        log.info("Generating report: " + fullPath);
        try (CustomBigExcelWriter writer = ExcelUtils.getBigWriter(fullPath)) {
            writeContent(writer, params);
            return fullPath;
        } catch (Exception e) {
            log.error("Failed to generate report: " + fullPath, e);
            throw new RuntimeException("Failed to generate report: " + fullPath, e);
        }
    }
}

4. 具体策略实现 `TestReprotProcessor`

@Component
public class TestReportProcessor extends ReportProcessor<TestReport> {
    @Override
    public String write(TestReport params) {
        return writeReport(params);
    }

    @Override
    public String getFullPath() {
        return "d://TestReport.xlsx";
    }

    @Override
    public void writeContent(CustomBigExcelWriter writer, TestReport params) {
        // excel内容
    }
}

5. Controller 调用

@RestController
@RequestMapping("/reports")
public class ReportController {
    
    @Autowired
    private ReportStrategyFactory strategyFactory;
    
    @GetMapping("/sales")
    public void exportSalesReport(SalesReportVO vo, HttpServletResponse response) {
        ReportStrategy<TestReportVO> strategy = strategyFactory.getStrategy(TestReportVO.class);
        String filePath = strategy.write(vo);
        // 返回文件下载
    }
}