轻松掌握 EasyExcel 复杂动态表头导出技巧，实现高效数据处理与精准报告生成

[Jonny]

EasyExcel导出

由于简单的注解已经无法满足复杂的业务需求，本文简单带你了解如何实现动态easyexcel表数据导出，这里的动态指的是表头不固定，需要动态生成。

样例

• 简单固定表头
  • http://localhost:8080/file/simple

• 动态表头
  • http://localhost:8080/file/dynamic

• 固定+动态表头（自定义样式）
  • http://localhost:8080/file/fix_dynamic

项目结构

pom依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.5</version>
</dependency>

domain

package com.example.domain.model;

import com.alibaba.excel.write.handler.CellWriteHandler;
import lombok.*;

import java.util.Collection;
import java.util.List;

/**
 * @author Jonny
 */
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class ExportData {

    /**
     * 自定义处理器：单元格合并、背景色等
     */
    private List<CellWriteHandler> cellWriteHandlers;

    /**
     * 动态表头
     */
    @NonNull
    private List<List<String>> head;

    /**
     * 数据行 List<List<Object>>
     */
    @NonNull
    private Collection<?> data;

    /**
     * excel名称
     */
    @NonNull
    private String excelName;

    /**
     * 工作表名称
     */
    @Builder.Default
    private String sheetName = "sheet1";
}

package com.example.domain.model;

import com.alibaba.excel.write.handler.CellWriteHandler;
import lombok.*;

import java.util.Collection;
import java.util.List;

/**
 * @author Jonny
 * @description 简单导出
 */
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class ExportSimpleData {

    /**
     * 自定义处理器：单元格合并、背景色等
     */
    private List<CellWriteHandler> cellWriteHandlers;

    /**
     * 类型
     */
    @NonNull
    private Class<?> clazz;

    /**
     * 数据行 List<List<Object>>
     */
    @NonNull
    private Collection<?> data;

    /**
     * excel名称
     */
    @NonNull
    private String excelName;

    /**
     * 工作表名称
     */
    @Builder.Default
    private String sheetName = "sheet1";

}

数据模型

package com.example.domain.vo;

import cn.hutool.core.util.NumberUtil;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.math.BigDecimal;
import java.math.MathContext;
import java.math.RoundingMode;

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class DataDemo {

    @ExcelProperty("编号")
    private String no;

    @ExcelProperty("学生")
    private String stuName;

    @ExcelProperty(value = {"科目", "语文", "分数"})
    private BigDecimal ywScore;

    @ExcelProperty(value = {"科目", "语文", "占比"})
    private BigDecimal ywRatio;

    @ExcelProperty(value = {"科目", "数学", "分数"})
    private BigDecimal sxScore;

    @ExcelProperty(value = {"科目", "数学", "占比"})
    private BigDecimal sxRatio;

    @ExcelProperty("总成绩")
    private BigDecimal totalScore;

    @ExcelProperty("平均成绩")
    private BigDecimal avgScore;

    public void calc() {
        MathContext mathContext = new MathContext(2, RoundingMode.HALF_UP);
        // 计算总成绩
        BigDecimal total = NumberUtil.add(ywScore, sxScore);
        totalScore = total;
        avgScore = NumberUtil.div(total, 2).round(mathContext);
        ywRatio = NumberUtil.div(ywScore, total).round(mathContext);
        sxRatio = NumberUtil.div(sxScore, total).round(mathContext);
    }
}

通用服务类

package com.example.service;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.example.domain.model.ExportData;
import com.example.domain.model.ExportSimpleData;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.http.ContentDisposition;
import org.springframework.http.HttpHeaders;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.Objects;
import java.util.Optional;

import static org.apache.poi.hpsf.ClassIDPredefined.EXCEL_V11;

/**
 * @author Jonny
 * @description 动态excel导出
 */
@Service
public class DynamicExcelService {

    private static final HorizontalCellStyleStrategy DEFAULT_STYLE;

    static {
        DEFAULT_STYLE = buildCellStyleStrategy();
    }

    /**
     * 导出复杂表头数据
     *
     * @param data 数据信息
     */
    public void export(ExportData data) {
        ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        Optional.ofNullable(attributes).ifPresent(attr -> {
            HttpServletResponse response = attr.getResponse();
            Optional.ofNullable(response).ifPresent(resp -> {
                resp.setContentType(EXCEL_V11.getContentType());
                resp.setCharacterEncoding(StandardCharsets.UTF_8.name());

                ExcelWriter writer = null;
                try {
                    // 防止中文乱码
                    String fileName = URLEncoder.encode(data.getExcelName() + ".xlsx", StandardCharsets.UTF_8.name()).replaceAll("\\+", "%20");
                    resp.setHeader(HttpHeaders.CONTENT_DISPOSITION, ContentDisposition.attachment().filename(fileName).build().toString());
                    resp.setHeader(HttpHeaders.ACCESS_CONTROL_EXPOSE_HEADERS, HttpHeaders.CONTENT_DISPOSITION);
                    writer = EasyExcelFactory.write(resp.getOutputStream()).registerWriteHandler(DEFAULT_STYLE).build();
                    // writer = EasyExcelFactory.write(response.getOutputStream()).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();

                    // 动态添加表头
                    WriteSheet sheet1 = new WriteSheet();
                    sheet1.setSheetName(data.getSheetName());
                    sheet1.setSheetNo(0);

                    // 创建工作表
                    WriteTable table = new WriteTable();
                    table.setTableNo(1);
                    // 设置表头
                    table.setHead(data.getHead());
                    // 写数据
                    writer.write(data.getData(), sheet1, table);
                } catch (IOException e) {
                    throw new RuntimeException(e);
                } finally {
                    if (Objects.nonNull(writer)) {
                        writer.finish();
                    }
                }
            });
        });
    }

    /**
     * 导出固定表头(依赖实体类注解)
     *
     * @param data 数据信息
     */
    public void exportSimple(ExportSimpleData data) {
        ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        Optional.ofNullable(attributes).ifPresent(attr -> {
            HttpServletResponse response = attr.getResponse();
            Optional.ofNullable(response).ifPresent(resp -> {
                resp.setContentType(EXCEL_V11.getContentType());
                resp.setCharacterEncoding(StandardCharsets.UTF_8.name());

                try {
                    // 防止中文乱码
                    String fileName = URLEncoder.encode(data.getExcelName() + ".xlsx", StandardCharsets.UTF_8.name()).replaceAll("\\+", "%20");
                    resp.setHeader(HttpHeaders.CONTENT_DISPOSITION, ContentDisposition.attachment().filename(fileName).build().toString());
                    resp.setHeader(HttpHeaders.ACCESS_CONTROL_EXPOSE_HEADERS, HttpHeaders.CONTENT_DISPOSITION);
                    EasyExcel.write(resp.getOutputStream(), data.getClazz()).registerWriteHandler(DEFAULT_STYLE)
                            .sheet(data.getSheetName())
                            .doWrite(data.getData());
                } catch (Exception e) {
                    throw new RuntimeException(e);
                }
            });
        });
    }

    /**
     * 导出固定行和动态表头数据
     *
     * @param data 参数
     */
    public void exportFixedAndDynamicHead(ExportData data) {
        ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        Optional.ofNullable(attributes).ifPresent(attr -> {
            HttpServletResponse response = attr.getResponse();
            Optional.ofNullable(response).ifPresent(resp -> {
                resp.setContentType(EXCEL_V11.getContentType());
                resp.setCharacterEncoding(StandardCharsets.UTF_8.name());

                ExcelWriter writer = null;
                try {
                    // 防止中文乱码
                    String fileName = URLEncoder.encode(data.getExcelName() + ".xlsx", StandardCharsets.UTF_8.name()).replaceAll("\\+", "%20");
                    resp.setHeader(HttpHeaders.CONTENT_DISPOSITION, ContentDisposition.attachment().filename(fileName).build().toString());
                    resp.setHeader(HttpHeaders.ACCESS_CONTROL_EXPOSE_HEADERS, HttpHeaders.CONTENT_DISPOSITION);

                    // 初始化 writer
                    ExcelWriterBuilder write = EasyExcelFactory.write(resp.getOutputStream());
                    if (!CollectionUtils.isEmpty(data.getCellWriteHandlers())) {
                        data.getCellWriteHandlers().forEach(write::registerWriteHandler);
                    }
                    writer = write.registerWriteHandler(DEFAULT_STYLE).build();

                    // 创建 WriteSheet
                    WriteSheet sheet1 = new WriteSheet();
                    sheet1.setSheetName(data.getSheetName());
                    sheet1.setSheetNo(0);

                    // 创建 WriteTable
                    WriteTable table = new WriteTable();
                    table.setTableNo(1);

                    // 设置动态表头
                    table.setHead(data.getHead());

                    // 写入动态表头和数据
                    writer.write(data.getData(), sheet1, table);

                } catch (IOException e) {
                    throw new RuntimeException(e);
                } finally {
                    if (Objects.nonNull(writer)) {
                        writer.finish();
                    }
                }
            });
        });
    }


    /**
     * 配置字体，表头背景等
     *
     * @return 样式
     */
    public static HorizontalCellStyleStrategy buildCellStyleStrategy() {

        // 表头
        WriteCellStyle headCellStyle = new WriteCellStyle();
        // 背景色
        headCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());

        WriteFont headWriteFont = new WriteFont();
        // 加粗
        headWriteFont.setBold(true);
        headCellStyle.setWriteFont(headWriteFont);

        // 单元格样式
        WriteCellStyle cellStyle = new WriteCellStyle();
        // 字体策略
        WriteFont font = new WriteFont();
        cellStyle.setWriteFont(font);
        // 设置边框+数据垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 数据行水平居中
        cellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        cellStyle.setBorderLeft(BorderStyle.NONE);
        cellStyle.setBorderTop(BorderStyle.NONE);
        cellStyle.setBorderRight(BorderStyle.NONE);
        cellStyle.setBorderBottom(BorderStyle.NONE);

        // 自动换行
        cellStyle.setWrapped(true);
        // 表头样式
        return new HorizontalCellStyleStrategy(headCellStyle, cellStyle);
    }
}

接口

package com.example.controller;

import com.example.domain.model.ExportData;
import com.example.domain.model.ExportSimpleData;
import com.example.domain.model.merge.CellBackgroundWriteHandler;
import com.example.domain.model.merge.MergeCellWriteHandler;
import com.example.domain.model.merge.RowBackgroundWriteHandler;
import com.example.domain.vo.DataDemo;
import com.example.service.DynamicExcelService;
import com.google.common.collect.Lists;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.util.List;

/**
 * @author Jonny
 */
@Controller
@RequestMapping("/file")
public class DownloadController {

    @Resource
    private DynamicExcelService excelService;

    /**
     * 复杂动态表头导出
     */
    @GetMapping("/dynamic")
    public void dynamic() {
        ExportData data = ExportData.builder()
                .excelName("动态表头数据")
                .head(builderHeader())
                .data(getDataList()).build();
        excelService.export(data);
    }

    /**
     * 固定表头+复杂动态表头导出
     */
    @GetMapping("/fix_dynamic")
    public void download() {
        List<List<String>> dynamicHead = builderHeader1();
        ExportData data = ExportData.builder()
                .excelName("固定表头+动态表头数据")
                .cellWriteHandlers(Lists.newArrayList(new MergeCellWriteHandler()))
                .head(dynamicHead)
                .data(getDataList()).build();
        excelService.exportFixedAndDynamicHead(data);
    }

    /**
     * 简单固定表头导出(依赖实体类注解)
     */
    @GetMapping("/simple")
    public void exportSimple() {
        Class<DataDemo> dataDemoClass = DataDemo.class;
        ExportSimpleData data = ExportSimpleData.builder()
                .excelName("简单固定表头")
                .clazz(dataDemoClass)
                .sheetName("学生成绩")
                .data(builderData()).build();
        excelService.exportSimple(data);
    }

    /**
     * 单元格样式
     */
    @GetMapping("/cellStyle")
    public void cellStyle() {
        List<List<String>> head = Lists.newArrayList();
        head.add(Lists.newArrayList("学号"));
        head.add(Lists.newArrayList("姓名"));
        head.add(Lists.newArrayList("平均成绩"));

        List<List<Object>> dataList = Lists.newArrayList();
        dataList.add(Lists.newArrayList("A001", "张三", null));
        dataList.add(Lists.newArrayList("A002", "小李", new BigDecimal("59")));
        dataList.add(Lists.newArrayList("A003", "小王", new BigDecimal("72")));
        dataList.add(Lists.newArrayList("A004", "小周", new BigDecimal("66")));
        dataList.add(Lists.newArrayList("A005", "小邓", new BigDecimal("44")));

        ExportData data = ExportData.builder()
                .excelName("单元格背景色")
                .cellWriteHandlers(Lists.newArrayList(new CellBackgroundWriteHandler()))
                .head(head)
                .data(dataList).build();
        excelService.exportFixedAndDynamicHead(data);
    }

    /**
     * 设置行样式
     */
    @GetMapping("/rowBg")
    public void rowBg() {
        List<List<String>> head = Lists.newArrayList();
        head.add(Lists.newArrayList("学号"));
        head.add(Lists.newArrayList("姓名"));
        head.add(Lists.newArrayList("平均成绩"));

        List<List<Object>> dataList = Lists.newArrayList();
        dataList.add(Lists.newArrayList("A001", "张三", new BigDecimal("88")));
        dataList.add(Lists.newArrayList("A002", "小李", new BigDecimal("59")));
        dataList.add(Lists.newArrayList("A003", "小王", new BigDecimal("72")));
        dataList.add(Lists.newArrayList("A004", "小周", new BigDecimal("66")));
        dataList.add(Lists.newArrayList("A005", "小郑", new BigDecimal("44")));
        dataList.add(Lists.newArrayList("A006", "小涛", new BigDecimal("44")));
        dataList.add(Lists.newArrayList("A007", "小强", new BigDecimal("44")));

        ExportData data = ExportData.builder()
                .excelName("单元格背景色")
                .cellWriteHandlers(Lists.newArrayList(new RowBackgroundWriteHandler()))
                .head(head)
                .data(dataList).build();
        excelService.exportFixedAndDynamicHead(data);
    }

    private List<DataDemo> builderData() {
        List<DataDemo> dataDemos = Lists.newArrayList(
                DataDemo.builder().no("S001").stuName("小明").ywScore(new BigDecimal("85.5")).sxScore(new BigDecimal("88.9")).build(),
                DataDemo.builder().no("S010").stuName("小丽").ywScore(new BigDecimal("99")).sxScore(new BigDecimal("77.2")).build(),
                DataDemo.builder().no("S011").stuName("小王").ywScore(new BigDecimal("76")).sxScore(new BigDecimal("83")).build()
        );
        dataDemos.forEach(DataDemo::calc);
        return dataDemos;
    }

    /**
     * 构建表头
     *
     * @return 表头数据
     */
    private static List<List<String>> builderHeader() {

        // 主表头
        List<List<String>> topTitle = Lists.newArrayList();
        // 第1列
        topTitle.add(Lists.newArrayList("编码"));
        // 第2列
        topTitle.add(Lists.newArrayList("名称"));
        // 动态列
        List<String> secondTitles = Lists.newArrayList("2024", "2025", "2026", "2027");
        // 动态子列
        List<String> threeTitles = Lists.newArrayList("数量", "金额");

        // 根据实际需要，决定要渲染多少列
        secondTitles.forEach(second -> threeTitles.forEach(three -> topTitle.add(Lists.newArrayList(second, three))));

        // 后面的列
        topTitle.add(Lists.newArrayList("地址"));
        return topTitle;
    }

    private static List<List<String>> builderHeader1() {

        // 主表头
        List<List<String>> topTitle = Lists.newArrayList();
        topTitle.add(Lists.newArrayList("总览", "编码"));
        topTitle.add(Lists.newArrayList("总览", "名称"));
        // 动态列
        List<String> secondTitles = Lists.newArrayList("2024", "2025", "2026", "2027");
        // 动态子列
        List<String> threeTitles = Lists.newArrayList("数量", "金额");

        // 根据实际需要，决定要渲染多少列
        secondTitles.forEach(second -> threeTitles.forEach(three -> topTitle.add(Lists.newArrayList("总览", second, three))));

        // 后面的列
        topTitle.add(Lists.newArrayList("总览", "地址"));
        return topTitle;
    }

    /**
     * 构建数据
     *
     * @return 行数据
     */
    private static List<List<Object>> getDataList() {
        List<List<Object>> contentList = Lists.newArrayList();
        contentList.add(Lists.newArrayList("A001", "零件A", 10, 20.1, 20, 200.01, 20, 200.01, 20, 200.01, "重庆"));
        contentList.add(Lists.newArrayList("BC01", "零件B", 20, 300, 40, 500.8, 40, 500.8, 40, 500.8, "四川"));
        return contentList;
    }
}

自定义handler单元格样式

• CellBackgroundWriteHandler

package com.example.domain.model.merge;

import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;

import java.math.BigDecimal;

/**
 * @author Jonny
 * @description 自定义单元格样式 由于CellStyle超过64000会报错，所以需要将样式对象缓存起来，避免重复样式
 */
public class CellBackgroundWriteHandler implements CellWriteHandler {

    /**
     * 根据条件设置单元格样式： 小于60分单元格标红
     * <p>
     * 方式1
     * 可以设置RGB样色，缺点就是会丢失单元格原本样式：比如字体样式，如需保留样式需要新建字体拷贝原有样式
     * <p>
     * 方式2
     * 设置预置样式，灵活度不够高，也会丢失单元格字体样式
     * <p>
     * 方式3
     * 设置预置背景色简单，是在原有样式上修改，优点是不会丢失字体样式，缺点是只能使用预置颜色
     *
     * @param context 上下文
     */
    @Override
    public void afterCellDispose(CellWriteHandlerContext context) {
        Cell cell = context.getCell();
        // 第二行第3列
        if (context.getRowIndex() > 0 && cell.getColumnIndex() == 2) {
            if (CellType.NUMERIC == cell.getCellType()) {
                double numericCellValue = cell.getNumericCellValue();
                BigDecimal score = BigDecimal.valueOf(numericCellValue);
                BigDecimal passingScore = BigDecimal.valueOf(60);
                if (score.compareTo(passingScore) < 0) {
                    WriteCellData<?> cellData = context.getFirstCellData();

                    // Workbook workbook = context.getWriteWorkbookHolder().getWorkbook();
                    // CellStyle cellStyle = workbook.createCellStyle();

                    // ====================================方式1======================================
                    // 设置RGB样色
                    // byte[] rgb = new byte[]{(byte) 250, (byte) 0, (byte) 0};
                    // XSSFCellStyle xssfCellColorStyle = (XSSFCellStyle) cellStyle;
                    // xssfCellColorStyle.setFillForegroundColor(new XSSFColor(rgb, null));
                    // 指定固体填充 FillPatternType 为FillPatternType.SOLID_FOREGROUND
                    // cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    // cellStyle.setAlignment(HorizontalAlignment.CENTER);
                    // cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    // cell.setCellStyle(xssfCellColorStyle);

                    // ====================================方式2======================================
                    // 枚举类内置IndexedColors.RED.getIndex()
                    // // 原有样式
                    // CellStyle originalStyle = cell.getCellStyle();
                    // // 设置填充模式
                    // cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    // // 设置背景色
                    // cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
                    // // 原有字体对齐方式 水平居中 垂直居中
                    // // 设置字体样式（可选）
                    // Font originalFont = workbook.getFontAt(originalStyle.getFontIndexAsInt());
                    // Font newFont = workbook.createFont();
                    // newFont.setFontHeightInPoints(originalFont.getFontHeightInPoints());
                    // newFont.setFontName(originalFont.getFontName());
                    // newFont.setColor(IndexedColors.RED.getIndex());
                    // cellStyle.setFont(newFont);
                    // cell.setCellStyle(cellStyle);

                    // 清除默认样式，不设置则无效(适用于方式1，方式2)
                    // cellData.setWriteCellStyle(null);

                    // ====================================方式3======================================
                    // 这种方式不会丢失字体样式，仅修改背景色
                    WriteCellStyle originalStyle = cellData.getOrCreateStyle();
                    originalStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
                    originalStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
                }
            }
        }
    }

}

• MergeCellWriteHandler

package com.example.domain.model.merge;

import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;

/**
 * @author Jonny
 * @description 自定义单元格样式
 */
public class MergeCellWriteHandler implements CellWriteHandler {

    /**
     * 由于CellStyle超过64000会报错，所以需要将样式对象缓存起来，避免重复样式
     */
    private static final WriteCellStyle DEFAULT_STYLE;

    static {
        DEFAULT_STYLE = getWriteCellStyle();
    }

    /**
     * 去除边框+内容居右
     *
     * @param context 上下文
     */
    @Override
    public void afterCellDispose(CellWriteHandlerContext context) {
        if (context.getRowIndex() == 0) {
            // 设置样式
            context.getCellDataList().get(0).setWriteCellStyle(DEFAULT_STYLE);
        }
    }

    /**
     * 构建样式
     *
     * @return 样式
     */
    private static WriteCellStyle getWriteCellStyle() {
        // 创建样式
        WriteCellStyle writeCellStyle = new WriteCellStyle();
        // 设置内容居右
        writeCellStyle.setHorizontalAlignment(HorizontalAlignment.RIGHT);
        // 设置垂直居中
        writeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // 设置边框为无
        writeCellStyle.setBorderLeft(BorderStyle.NONE);
        writeCellStyle.setBorderTop(BorderStyle.NONE);
        writeCellStyle.setBorderRight(BorderStyle.NONE);
        writeCellStyle.setBorderBottom(BorderStyle.NONE);
        return writeCellStyle;
    }

}

• RowBackgroundWriteHandler

package com.example.domain.model.merge;

import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;

/**
 * @author Jonny
 * @description 自定义行样式 由于CellStyle超过64000会报错，所以需要将样式对象缓存起来，避免重复样式
 */
public class RowBackgroundWriteHandler implements CellWriteHandler {

    /**
     * 根据条件设置单元格样式： 小于60分单元格标红
     *
     * @param context 上下文
     */
    @Override
    public void afterCellDispose(CellWriteHandlerContext context) {
        Cell cell = context.getCell();
        // 数据行奇数行设置背景
        if (context.getRowIndex() > 0 && (cell.getRowIndex() & 1) == 0) {

            Workbook workbook = context.getWriteWorkbookHolder().getWorkbook();
            CellStyle cellStyle = workbook.createCellStyle();

            // WriteCellData<?> cellData = context.getFirstCellData();
            // WriteCellStyle originalStyle  = cellData.getOrCreateStyle();
            // WriteFont writeFont = originalStyle.getWriteFont();

            // 原有样式
            CellStyle originalStyle = cell.getCellStyle();

            // 设置rgb颜色
            byte[] rgb = new byte[]{(byte) 255, (byte) 230, (byte) 153};
            XSSFCellStyle xssfCellColorStyle = (XSSFCellStyle) cellStyle;
            xssfCellColorStyle.setFillForegroundColor(new XSSFColor(rgb, null));
            // 指定固体填充 FillPatternType 为FillPatternType.SOLID_FOREGROUND
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

            // // 设置字体样式（可选）
            Font originalFont = workbook.getFontAt(originalStyle.getFontIndexAsInt());
            Font newFont = workbook.createFont();
            newFont.setFontHeightInPoints(originalFont.getFontHeightInPoints());
            newFont.setFontName(originalFont.getFontName());
            newFont.setColor(IndexedColors.RED.getIndex());
            cellStyle.setFont(newFont);

            cell.setCellStyle(xssfCellColorStyle);


            // 清除默认样式，不设置则无效
            context.getFirstCellData().setWriteCellStyle(null);
        }
    }

}

浏览器异步下载

可以在浏览器控制台直接运行

fetch('http://localhost:8080/file/simple', {
    method: 'GET',
    headers: {
        'Content-Type': 'application/json',
    }
})
    .then(response => {
        if (response.ok) {
            const contentDisposition = response.headers.get('Content-Disposition');
            let fileName = 'downloaded_file.xlsx';  // 默认文件名

            // 如果响应头中包含 'Content-Disposition'，解析文件名
            if (contentDisposition && contentDisposition.includes('attachment')) {
                // 处理 filename*= 格式（通常是 URL 编码的文件名）
                const filenameStarMatches = contentDisposition.match(/filename\*=UTF-8''([^;]*)/);
                if (filenameStarMatches && filenameStarMatches[1]) {
                    // 解码 URL 编码的文件名，进行两次解码以确保字符还原
                    fileName = decodeURIComponent(decodeURIComponent(filenameStarMatches[1]));
                }
                // 如果没有匹配到 filename*=，尝试处理普通的 filename（URL 编码的文件名）
                else {
                    const filenameMatches = contentDisposition.match(/filename="([^"]*)"/);
                    if (filenameMatches && filenameMatches[1]) {
                        // 解码 URL 编码的文件名
                        fileName = decodeURIComponent(filenameMatches[1]);
                    }
                }
            }

            // 获取文件内容 (Blob)
            return response.blob().then(blob => ({blob, fileName}));
        } else {
            throw new Error('文件下载失败');
        }
    })
    .then(({blob, fileName}) => {
        // 创建一个虚拟链接并触发下载
        const url = window.URL.createObjectURL(blob);
        const a = document.createElement('a');
        a.href = url;
        a.download = fileName;  // 使用解码后的文件名
        document.body.appendChild(a);
        a.click();  // 模拟点击下载
        document.body.removeChild(a);  // 下载后移除链接
    })
    .catch(error => {
        console.error('下载文件时发生错误:', error);
    });