【java】基于hutool实现.Excel导出任意多级自定义表头数据和支持多sheet导出

原创 已于 2026-05-13 17:24:25 修改 · 92 阅读 · 1 ·
本内容遵循CC 4.0 BY-SA版权协议
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
GEO检测
标签
#java #excel #开发语言
于 2026-03-27 16:52:43 首次发布
该文章已生成可运行项目，

基于hutool实现.Excel导出多级表头数据

import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import org.apache.poi.ss.usermodel.*;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;

public class ExcelUtils {

/**
     * 导出指定多级表头的 Excel
     * @param topLine  顶部插入数据，可以为null
     * @param headers  表头，支持无限级
     * @param dataList  要导出的数据
     * @param response
     * @throws IOException
     */
    public static void downloadExcelMoreHeaders(List<String> topLine,List<ExcelHeader> headers,
                                                List<HashMap<String, Object>> dataList,
                                                HttpServletResponse response,
                                                String fileName) throws IOException {
        ExcelWriter writer = null;
        try {
            writer = ExcelUtil.getWriter(true);
            Sheet sheet = writer.getSheet();


           int startRow = 0;
            if (topLine != null && !topLine.isEmpty()) {
                startRow = 1;
                Row topRow = sheet.createRow(0);
                for (int i = 0; i < topLine.size(); i++) {
                    Cell cell = topRow.createCell(i);
                    cell.setCellValue(topLine.get(i));
                    // 居中样式
                    CellStyle style = writer.getWorkbook().createCellStyle();
                    style.setAlignment(HorizontalAlignment.CENTER);
                    cell.setCellStyle(style);
                }
            }

            // 最大表头层级
            int maxLevel = getMaxHeaderLevel(headers);

            // 递归构建表头（按叶子节点列数自动合并）
            java.util.concurrent.atomic.AtomicInteger colIndex = new java.util.concurrent.atomic.AtomicInteger(0);
            buildHeaderRecursive(writer, sheet, headers, startRow, colIndex, startRow + maxLevel);

            // 收集所有最终字段
            List<String> fieldOrder = collectAllLeafFields(headers);

            // 写入数据
            List<List<Object>> dataRows = new ArrayList<>();
            for (HashMap<String, Object> data : dataList) {
                dataRows.add(fieldOrder.stream()
                        .map(field -> data.getOrDefault(field, ""))
                        .collect(Collectors.toList()));
            }

            writer.passRows(startRow + maxLevel);
            writer.write(dataRows, false);

            // 数据样式
            CellStyle dataStyle = createDataCellStyle(writer.getWorkbook());
            for (int rowNum = startRow + maxLevel; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (row != null) {
                    for (Cell cell : row) {
                        if (cell != null) {
                            cell.setCellStyle(dataStyle);
                        }
                    }
                }
            }

            // ==================== 列宽计算（修复不越界） ====================
            int colCount = 0;
            for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (row != null) {
                    colCount = Math.max(colCount, row.getLastCellNum());
                }
            }
            colCount = colCount == 0 ? 100 : colCount;

            float chineseRatio = 2.0f;
            float bufferWidth = 3.0f;
            int maxWidth = 50;
            int[] maxColWidths = new int[colCount];

            int checkRows = Math.min(sheet.getLastRowNum(), 1000);
            for (int rowNum = 0; rowNum <= checkRows; rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (row == null) continue;

                int cellNum = row.getLastCellNum();
                for (int j = 0; j < cellNum; j++) {
                    Cell cell = row.getCell(j);
                    if (cell == null) continue;

                    String text = getCellStringValue(cell);
                    int width = getMaxLineWidth(text, chineseRatio);
                    if (width > maxColWidths[j]) {
                        maxColWidths[j] = width;
                    }
                }
            }

            for (int j = 0; j < colCount; j++) {
                sheet.setColumnWidth(j, Math.min((int) (maxColWidths[j] + bufferWidth) * 256, maxWidth * 256));
            }

            // 输出
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            response.setHeader("Content-Disposition",
                    "attachment;filename*=UTF-8''" +
                            URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20") +
                            ".xlsx");
            writer.flush(response.getOutputStream(), true);

        } finally {
            if (writer != null) writer.close();
            try {
                if (response.getOutputStream() != null) response.getOutputStream().close();
            } catch (Exception ignored) {}
        }
    }

    // ==================== 核心递归：构建表头（自动合并正确列数） ====================
    private static void buildHeaderRecursive(ExcelWriter writer, Sheet sheet,
                                            List<ExcelHeader> headers,
                                            int row, AtomicInteger colIndex, int maxLevel) {
        for (ExcelHeader header : headers) {
            int currentCol = colIndex.get();
            List<ExcelHeader> children = header.getChildren();

            if (children == null || children.isEmpty()) {
                // 叶子节点
                int rowSpan = maxLevel - row;
                if (rowSpan > 1) {
                    writer.merge(row, row + rowSpan - 1, currentCol, currentCol, header.getTitle(), true);
                } else {
                    writer.writeCellValue(currentCol, row, header.getTitle());
                }
                applyHeaderStyle(writer, currentCol, row);
                colIndex.incrementAndGet();
            } else {
                // 父节点：合并列数 = 底层真实叶子列数
                int span = countLeafColumns(children);
                writer.merge(row, row, currentCol, currentCol + span - 1, header.getTitle(), true);
                applyHeaderStyle(writer, currentCol, row);

                // 递归子节点
                buildHeaderRecursive(writer, sheet, children, row + 1, colIndex, maxLevel);
            }
        }
    }

    // ==================== 工具方法 ====================
    private static int countLeafColumns(List<ExcelHeader> headers) {
        int count = 0;
        for (ExcelHeader h : headers) {
            if (h.getChildren() == null || h.getChildren().isEmpty()) count++;
            else count += countLeafColumns(h.getChildren());
        }
        return count;
    }

    private static int getMaxHeaderLevel(List<ExcelHeader> headers) {
        int max = 1;
        for (ExcelHeader h : headers) {
            if (h.getChildren() != null && !h.getChildren().isEmpty()) {
                int level = getMaxHeaderLevel(h.getChildren()) + 1;
                max = Math.max(max, level);
            }
        }
        return max;
    }

    private static List<String> collectAllLeafFields(List<ExcelHeader> headers) {
        List<String> list = new ArrayList<>();
        for (ExcelHeader h : headers) {
            if (h.getChildren() == null || h.getChildren().isEmpty()) list.add(h.getFieldName());
            else list.addAll(collectAllLeafFields(h.getChildren()));
        }
        return list;
    }

    private static int getRealColumnCount(Sheet sheet) {
        int count = 0;
        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row != null) count = Math.max(count, row.getLastCellNum());
        }
        return Math.max(count, 100);
    }

    private static int[] calculateColumnWidths(Sheet sheet, int colCount, float chineseRatio) {
        int[] widths = new int[colCount];
        int checkRows = Math.min(sheet.getLastRowNum(), 1000);
        for (int i = 0; i <= checkRows; i++) {
            Row row = sheet.getRow(i);
            if (row == null) continue;
            for (int j = 0; j < colCount; j++) {
                Cell cell = row.getCell(j);
                if (cell == null) continue;
                String text = getCellStringValue(cell);
                int w = getMaxLineWidth(text, chineseRatio);
                if (w > widths[j]) widths[j] = w;
            }
        }
        return widths;
    }

    private static CellStyle createDataCellStyle(Workbook wb) {
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setWrapText(true);
        return style;
    }

    private static void applyHeaderStyle(ExcelWriter writer, int col, int row) {
        CellStyle style = writer.getWorkbook().createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        writer.setStyle(style, col, row);
    }

    private static String getCellStringValue(Cell cell) {
        if (cell == null) return "";
        switch (cell.getCellType()) {
            case STRING: return cell.getStringCellValue();
            case NUMERIC: return String.valueOf(cell.getNumericCellValue());
            case BOOLEAN: return String.valueOf(cell.getBooleanCellValue());
            default: return "";
        }
    }

    private static int getMaxLineWidth(String text, float chineseRatio) {
        if (text == null || text.isEmpty()) return 0;
        int max = 0;
        for (String line : text.split("\n")) {
            int w = 0;
            for (char c : line.toCharArray()) {
                w += (Character.UnicodeBlock.of(c) == Character.UnicodeBlock.CJK_UNIFIED_IDEOGRAPHS) ? chineseRatio : 1;
            }
            max = Math.max(max, w);
        }
        return max;
    }
}

配套 ExcelHeader.java

import lombok.Data;
import java.util.List;

@Data
public class ExcelHeader {
    private String title;
    private String fieldName;
    private List<ExcelHeader> children;
}

必须依赖（pom.xml）

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.8.10</version>
</dependency>

调用案例

  public static void export(HttpServletResponse response) throws Exception {
        // ===================== 1. 构建 3级表头 =====================
        List<ExcelHeader> headers = new ArrayList<>();

        // 第1行：大标题
        ExcelHeader title = new ExcelHeader();
        title.setTitle("学生成绩统计表");
        title.setChildren(new ArrayList<>());
        headers.add(title);

        // 第2行：分组
        ExcelHeader group1 = new ExcelHeader();
        group1.setTitle("学生信息");
        group1.setChildren(new ArrayList<>());
        title.getChildren().add(group1);

        ExcelHeader group2 = new ExcelHeader();
        group2.setTitle("成绩信息");
        group2.setChildren(new ArrayList<>());
        title.getChildren().add(group2);

        // 第3行：真实列（子集）
        group1.getChildren().add(createHeader("姓名", "name"));
        group1.getChildren().add(createHeader("班级", "className"));

        group2.getChildren().add(createHeader("语文", "chinese"));
        group2.getChildren().add(createHeader("数学", "math"));
        group2.getChildren().add(createHeader("英语", "english"));

        // ===================== 2. 测试数据 =====================
        List<HashMap<String, Object>> dataList = new ArrayList<>();

        HashMap<String, Object> row1 = new HashMap<>();
        row1.put("name", "张三");
        row1.put("className", "高三1班");
        row1.put("chinese", 125);
        row1.put("math", 143);
        row1.put("english", 130);
        dataList.add(row1);

        HashMap<String, Object> row2 = new HashMap<>();
        row2.put("name", "李四");
        row2.put("className", "高三1班");
        row2.put("chinese", 118);
        row2.put("math", 136);
        row2.put("english", 127);
        dataList.add(row2);

        // ===================== 3. 导出 =====================
        ExcelUtils.downloadExcelMoreHeaders(null,headers, dataList, response, "学生成绩表");
    }

    // 快速创建列
    private static ExcelHeader createHeader(String title, String fieldName) {
        ExcelHeader h = new ExcelHeader();
        h.setTitle(title);
        h.setFieldName(fieldName);
        return h;
    }

实现导出多sheet 合并的数据文档

一、基于downloadExcelMoreHeaders方法，创建一个包含单个Sheet的Workbook的方法

    /**
     * 创建一个包含单个Sheet的Workbook（不下载，只返回对象）
     */
    public static Workbook createSingleSheetBook(
            List<String> topLine,
            List<ExcelHeader> headers,
            List<HashMap> dataList,
            String sheetName
    ) {
        // 直接用POI创建，完美支持设置sheet名称
        Workbook workbook = new XSSFWorkbook();
        // 第二个参数直接传字符串 sheetName
        ExcelWriter writer = new ExcelWriter(workbook, sheetName);
        Sheet sheet = writer.getSheet();
        // ========== 你的原有逻辑 完全不动 ==========
        int startRow = 0;
        if (topLine != null && !topLine.isEmpty()) {
            startRow = 1;
            Row topRow = sheet.createRow(0);
            for (int i = 0; i < topLine.size(); i++) {
                Cell cell = topRow.createCell(i);
                cell.setCellValue(topLine.get(i));
                CellStyle style = writer.getWorkbook().createCellStyle();
                style.setAlignment(HorizontalAlignment.CENTER);
                cell.setCellStyle(style);
            }
        }

        int maxLevel = getMaxHeaderLevel(headers);
        java.util.concurrent.atomic.AtomicInteger colIndex = new java.util.concurrent.atomic.AtomicInteger(0);
        buildHeaderRecursive(writer, sheet, headers, startRow, colIndex, startRow + maxLevel);

        List<String> fieldOrder = collectAllLeafFields(headers);
        List<List<Object>> dataRows = new ArrayList<>();
        for (HashMap<String, Object> data : dataList) {
            dataRows.add(fieldOrder.stream()
                    .map(field -> data.getOrDefault(field, ""))
                    .collect(Collectors.toList()));
        }

        writer.passRows(startRow + maxLevel);
        writer.write(dataRows, false);

        CellStyle dataStyle = createDataCellStyle(writer.getWorkbook());
        for (int rowNum = startRow + maxLevel; rowNum <= sheet.getLastRowNum(); rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row != null) {
                for (Cell cell : row) {
                    if (cell != null) {
                        cell.setCellStyle(dataStyle);
                    }
                }
            }
        }

        // 自动列宽（你的逻辑）
        int colCount = 0;
        for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row != null) {
                colCount = Math.max(colCount, row.getLastCellNum());
            }
        }
        colCount = colCount == 0 ? 100 : colCount;

        float chineseRatio = 2.0f;
        float bufferWidth = 3.0f;
        int maxWidth = 50;
        int[] maxColWidths = new int[colCount];

        int checkRows = Math.min(sheet.getLastRowNum(), 1000);
        for (int rowNum = 0; rowNum <= checkRows; rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row == null) continue;

            int cellNum = row.getLastCellNum();
            for (int j = 0; j < cellNum; j++) {
                Cell cell = row.getCell(j);
                if (cell == null) continue;

                String text = getCellStringValue(cell);
                int width = getMaxLineWidth(text, chineseRatio);
                if (width > maxColWidths[j]) {
                    maxColWidths[j] = width;
                }
            }
        }

        for (int j = 0; j < colCount; j++) {
            sheet.setColumnWidth(j, Math.min((int) (maxColWidths[j] + bufferWidth) * 256, maxWidth * 256));
        }

        // 关键：只返回 workbook，不关闭、不下载
        return writer.getWorkbook();
    }

二、合并多个workbook并下载

    /**
     * 合并多个 workbook 并输出下载（每个 workbook 取第一个sheet）
     */
    public static void mergeAndDownload(
            HttpServletResponse response,
            String fileName,
            Workbook... workbooks
    ) throws IOException {

        Workbook finalWb = new XSSFWorkbook();

        for (Workbook wb : workbooks) {
            if (wb == null) continue;
            Sheet sourceSheet = wb.getSheetAt(0);
            String sheetName = sourceSheet.getSheetName();

            Sheet targetSheet = finalWb.createSheet(sheetName);

            // 复制行、单元格、样式
            for (int rowNum = 0; rowNum <= sourceSheet.getLastRowNum(); rowNum++) {
                Row sourceRow = sourceSheet.getRow(rowNum);
                Row targetRow = targetSheet.createRow(rowNum);
                if (sourceRow == null) continue;

                for (int cellNum = 0; cellNum < sourceRow.getLastCellNum(); cellNum++) {
                    Cell sourceCell = sourceRow.getCell(cellNum);
                    Cell targetCell = targetRow.createCell(cellNum);
                    if (sourceCell == null) continue;

                    // 复制值
                    switch (sourceCell.getCellType()) {
                        case STRING: targetCell.setCellValue(sourceCell.getStringCellValue()); break;
                        case NUMERIC: targetCell.setCellValue(sourceCell.getNumericCellValue()); break;
                        case BOOLEAN: targetCell.setCellValue(sourceCell.getBooleanCellValue()); break;
                        default: targetCell.setCellValue(sourceCell.toString());
                    }

                    // 复制样式
                    CellStyle newStyle = finalWb.createCellStyle();
                    newStyle.cloneStyleFrom(sourceCell.getCellStyle());
                    targetCell.setCellStyle(newStyle);
                }
            }

            // 复制列宽
            for (int i = 0; i < sourceSheet.getRow(0).getLastCellNum(); i++) {
                targetSheet.setColumnWidth(i, sourceSheet.getColumnWidth(i));
            }
            // ========== 3.复制【合并单元格】 ==========
            for (int i = 0; i < sourceSheet.getNumMergedRegions(); i++) {
                CellRangeAddress region = sourceSheet.getMergedRegion(i);
                targetSheet.addMergedRegion(region);
            }
        }

        // 下载
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition",
                "attachment;filename*=UTF-8''" +
                        URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20") + ".xlsx");

        finalWb.write(response.getOutputStream());
        finalWb.close();
        response.getOutputStream().close();
    }

三、调用方式

Workbook wb1 = ExcelUtils.createSingleSheetBook(
         null, headers1,
        data1,
         "表1"
 );

 Workbook wb2 = ExcelUtils.createSingleSheetBook(
         null, headers2,
         data2,
         "表2"
 );

 // ====================== 合并导出 ======================
 ExcelUtils.mergeAndDownload(
         response,
         "多表合并",
         wb1, wb2
 );
本文章已经生成可运行项目
ew45218
关注
hutool工具编写导出Excel的使用
dfBeautifulLive的博客
04-26 5903
最近写了一个要导出excel的，要是比较简单一些就使用原始的方式了，遍历循环rows，并且遍历循环单元格填充值，就OK了，但是我的表头不是固定的，还需要一个行里填充4个单元格，有点像合并单元格之类的，填充数据呢，也是稍微复杂点，如果用原生的一是时间长，二是也比较困难，通过一些方式找到了hutool工具的ExcelWriter方式。 这个导出的好处就是你就关注数据本身，专注于数据本身能够怎么存储到List集合里，再把集合传给writer方法里就好了，就直接可以是一个想要的excel，咱们先来说一下使用示例
基于Hutool，Excel文件导出工具，支持表头合并，CSV文件大量导出
早晨阳光一般暖的博客
05-03 8038
基于Hutool，Excel文件导出工具，支持表头合并，CSV文件大量导出
使用htool工具导出和导入Excel表
最新发布
YUELEI118的博客
03-17 2135
【代码】使用htool工具导出和导入Excel表。
使用hutool工具类，快速导出Excel表格
q192101462的博客
04-07 3633
使用hutoo工具类 导出数据到excel表
hutool导出导出excel中文自适应列宽+反射+自定义注解获取表头
dgzhang_reg的博客
11-22 6842
hutool
Hutool 自定义excel导出
zl761117410的专栏
11-14 3239
Hutool 自定义excel导出 方法概览
使用hutool-excel导入或导出excel
qq_41884677的博客
05-09 1271
1. 输出自定义excel模板 @RequestMapping("getTemplate") public void getTemplate(HttpServletResponse response) throws UnsupportedEncodingException { // 设置模板第一行信息 List<String> row = CollUtil.newArrayList("姓名", "年龄", "性别", "地址"); List<List<Str
根据 hutool 自定义 导入导出 xlsx 的操作
qiwunongqingyin的博客
08-23 5514
hutool 导出文件,链式结构 简单易用 导入
Java实现excel动态表头及动态sheet
weixin_43241706的博客
06-11 6507
Java 动态表头 多sheet
java hutool工具实现excel的上传 支持office03和07
scorpio_meng的博客
06-28 1893
感谢hutool工具类，让java上传excel 变得超级简便！！！ 一行核心代码获取所有信息。。。
EasyExcel 导入导出封装工具
zhaoyang10的专栏
08-17 3135
EasyExcel 导入导出工具。
excel 浏览器 多sheet页下载
weixin_38193704的博客
02-07 250
excel 浏览器 多sheet页下载
hutool ExcelUtil导出复杂表头excel
喜欢分享,善于记录工作中遇到的问题,和自己的思考
01-28 4418
hutool ExcelUtil导出复杂表头excel
使用hutool导出Excel循环表头和数据、自动合并单元格、样式修改
忆江南的博客
02-23 9777
引入hutool工具包 <dependency> <groupId>cn.hutool</groupId> <artifactId>hutool-all</artifactId> <version>4.5.1</version> </dependency> <dependency> .
hutool ExcelUtil导出excel二级表头
我有故人折剑去，斩尽春风不曾归
11-11 2006
【代码】hutool ExcelUtil导出excel二级表头。
hutool 导出复杂表头excel
qq_31683775的博客
08-11 2473
hutool 导出复杂表头excel
hutool工具多sheet自定义导出excel
qq_32532841的博客
02-20 5778
CellStyle , .cloneStyleFrom
SpringBoot + hutool工具 实现动态表头导出Excel
热门推荐
qq_44136551的博客
07-08 1万+
动态表头定位：前端导出Excel时，选择需要导出的字段。 实现思路：使用 自定义注解 ，映射 实体类字段和表头名称，再通过反射获取到实体类的自定义注解，对比 前端带过来的 表头集合，通过 hutool导出功能实现。 导入 相关依赖 <!-- hutool 工具依赖 --> <dependency> <groupId>cn.hutool</groupId> <
java导出excel_程序员：超级简单，使用Hutool Java工具类库导出Excel
weixin_39637614的博客
11-14 474
前言在开发应用系统的时候，导出文件是必不可放的功能。以前用过POI、easyexcel等工具的导入导出功能，但总感觉太麻烦了，代码特别多，感觉并不是很好用。今天给大家介绍一款新工具，java工具类库Hutool。Hutool简介Hutool是一个小而全的Java工具类库，通过静态方法封装，降低相关API的学习成本，提高工作效率，使Java拥有函数式语言般的优雅，让使用者更轻松。Hutool中的工具...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值