EasyExcel自定义复杂的表头并在同sheet中实现分页

---

前言

提示：以下是本篇文章正文内容，下面案例可供参考
自定义表头导出excel，并在同sheet中进行分页

一、EasyExcel导出

1.关键代码

代码如下（示例）：

EasyExcelUtils

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.merge.LoopMergeStrategy;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
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.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import com.alibaba.excel.write.style.row.SimpleRowHeightStyleStrategy;
import org.apache.poi.ss.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * @description: easyexcel 实现自定义表头并在同一个 sheet 中分页
 * easyexcel 引用的是 3.1.1 版本
 */
public class EasyExcelUtils {

    /**
     * 下载 excel
     *
     * @param response
     * @param list 主体表格数据
     * @throws Exception
     */
    public static void download(HttpServletResponse response, List<?> list) throws Exception {

        // 总共 8 列
        int columnTotal = 8;

        // 首行标题
        List<List<String>> titleData = new ArrayList<>();
        List<String> title = Arrays.asList("这是标题");
        titleData.add(title);

        // 中间抬头部分
        List<List<String>> secondData = new ArrayList<>();
        // 合并前3列, 合并后5列
        List<String> second1 = Arrays.asList("这是抬头1", "", "", "抬头1的内容");
        List<String> second2 = Arrays.asList("这是抬头2", "", "", "抬头2的内容");
        secondData.add(second1);
        secondData.add(second2);

        // 末尾合计
        List<List<String>> totalData = new ArrayList<>();
        List<String> total = new ArrayList<>();
        for (int i = 1; i <= columnTotal; i++) {
            if (i == 1) {
                total.add("合计");
                continue;
            }
            if (i == columnTotal) {
                total.add("这是合计总数");
                continue;
            }
            total.add("");
        }
        totalData.add(total);

        excelCreate(response, list, columnTotal, titleData, secondData, totalData);
    }

    /**
     * excel 表格生成
     *
     * @param response
     * @param list 主体表格数据
     * @param columnTotal 总列数
     * @param titleData 表头数据
     * @param secondData 第二部分抬头数据
     * @param totalData 合计数据
     * @throws Exception
     */
    private static void excelCreate(HttpServletResponse response, List<?> list, int columnTotal, List<List<String>> titleData,
                                    List<List<String>> secondData, List<List<String>> totalData) throws Exception {
        response.setContentType("application/octet-stream");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("Excel文件名称" + DateTimeFormatter.BASIC_ISO_DATE.format(LocalDate.now()), "UTF-8")
                .replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build()) {
            WriteSheet sheet = EasyExcel.writerSheet("sheetName").build();

            // 按每5行数据进行分页
            int pageNum = (int) Math.ceil(list.size() / 5d);
            if (pageNum == 0) {
                pageNum = 1;
            }
            for (int i = 1; i <= pageNum; i++) {
                WriteTable titleTable = titleTableCreate(columnTotal);
                WriteTable secondTable = secondTableCreate(columnTotal);
                WriteTable headTable = headTableCreate(i + 1);
                WriteTable totalTable = totalTableCreate(pageNum + 2);

                excelWriter.write(titleData, sheet, titleTable);
                excelWriter.write(secondData, sheet, secondTable);

                int startIndex = (i -1) * 5;
                int endIndex = Math.min(startIndex + 5, list.size());
                excelWriter.write(list.subList(startIndex, endIndex), sheet, headTable);
                excelWriter.write(totalData, sheet, totalTable);
            }
        }
    }

    /**
     * 生成标题表格
     *
     * @param columnTotal
     * @return
     */
    private static WriteTable titleTableCreate(int columnTotal) {

        // 合并单元格
        LoopMergeStrategy loopMergeStrategy = new LoopMergeStrategy(1, columnTotal, 0);

        // 样式
        WriteCellStyle titleStyle = new WriteCellStyle();
        // 水平居中
        titleStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置白色背景
        titleStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());

        // 设置字体格式
        WriteFont font = new WriteFont();
        font.setBold(Boolean.TRUE);
        font.setFontHeightInPoints((short)14);
        font.setFontName("宋体");
        titleStyle.setWriteFont(font);

        HorizontalCellStyleStrategy titleStyleStrategy = new HorizontalCellStyleStrategy(titleStyle, titleStyle);

        // 生成表格
        WriteTable titleTable = EasyExcel.writerTable(0)
                .registerWriteHandler(new SimpleRowHeightStyleStrategy((short)25, (short)25))
                .registerWriteHandler(loopMergeStrategy)
                .registerWriteHandler(titleStyleStrategy)
                .needHead(Boolean.FALSE)
                .build();
        return titleTable;
    }

    /**
     * 生成抬头表格
     *
     * @param columnTotal
     * @return
     */
    private static WriteTable secondTableCreate(int columnTotal) {

        // 合并单元格
        LoopMergeStrategy loopMergeStrategy1 = new LoopMergeStrategy(1, 3, 0);
        LoopMergeStrategy loopMergeStrategy2 = new LoopMergeStrategy(1, columnTotal - 3, 3);

        // 样式
        WriteCellStyle secondStyle = new WriteCellStyle();
        // 水平居中
        secondStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        secondStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置白色背景
        secondStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());

        // 设置字体格式
        WriteFont font = new WriteFont();
        font.setBold(Boolean.FALSE);
        font.setFontHeightInPoints((short)12);
        font.setFontName("宋体");
        secondStyle.setWriteFont(font);

        HorizontalCellStyleStrategy secondStyleStrategy = new HorizontalCellStyleStrategy(secondStyle, secondStyle);

        // 生成表格
        WriteTable secondTable = EasyExcel.writerTable(1)
                .registerWriteHandler(new SimpleRowHeightStyleStrategy((short)25, (short)25))
                .registerWriteHandler(loopMergeStrategy1)
                .registerWriteHandler(loopMergeStrategy2)
                .registerWriteHandler(secondStyleStrategy)
                .needHead(Boolean.FALSE)
                .build();
        return secondTable;
    }

    /**
     * 生成主体数据表格
     *
     * @param tableNum
     * @return
     */
    private static WriteTable headTableCreate(int tableNum) {

        List<List<String>> head = new ArrayList<>();
        List<String> headList = Arrays.asList("姓名", "性别", "年龄", "身份证号", "地址", "工作单位");
        List<String> subList = Arrays.asList("名称", "工号", "地址");
        headList.forEach(title -> {
            if ("工作单位".equals(title)) {
                subList.forEach(sub -> head.add(Arrays.asList(title, sub)));
                return;
            }
            head.add(Arrays.asList(title, title));
        });

        HorizontalCellStyleStrategy headStyleStrategy = headStyleStrategy();

        // 生成表格
        WriteTable titleTable = EasyExcel.writerTable(tableNum)
                .registerWriteHandler(new AbstractColumnWidthStyleStrategy() {
                    // 设置指定列的宽度
                    @Override
                    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
                        Sheet sheet = writeSheetHolder.getSheet();
                        int columnIndex = cell.getColumnIndex();
                        if (columnIndex == 3 || columnIndex == 4 || columnIndex == 7) {
                            sheet.setColumnWidth(columnIndex, 7000);
                        } else {
                            sheet.setColumnWidth(columnIndex, 3600);
                        }
                    }
                })
                .registerWriteHandler(new SimpleRowHeightStyleStrategy((short)25, (short)25))
                .registerWriteHandler(headStyleStrategy)
                .head(head)
                .needHead(Boolean.TRUE)
                .build();
        return titleTable;
    }

    /**
     * 主体表格样式
     *
     * @return
     */
    private static HorizontalCellStyleStrategy headStyleStrategy() {

        // 主体表头样式
        WriteCellStyle headStyle = new WriteCellStyle();
        // 水平居中
        headStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置灰色背景
        headStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());

        // 设置字体格式
        WriteFont font = new WriteFont();
        font.setBold(Boolean.FALSE);
        font.setFontHeightInPoints((short)12);
        font.setFontName("宋体");
        headStyle.setWriteFont(font);

        // 设置边框
        headStyle.setBorderLeft(BorderStyle.THIN);
        headStyle.setBorderRight(BorderStyle.THIN);
        headStyle.setBorderTop(BorderStyle.THIN);
        headStyle.setBorderBottom(BorderStyle.THIN);

        // 主题内容样式
        WriteCellStyle contentStyle = new WriteCellStyle();
        // 水平居中
        contentStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置灰色背景
        contentStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());
        // 自动换行
        contentStyle.setWrapped(true);

        // 设置字体格式
        font = new WriteFont();
        font.setBold(Boolean.FALSE);
        font.setFontHeightInPoints((short)10);
        font.setFontName("宋体");
        contentStyle.setWriteFont(font);

        // 设置边框
        contentStyle.setBorderLeft(BorderStyle.THIN);
        contentStyle.setBorderRight(BorderStyle.THIN);
        contentStyle.setBorderTop(BorderStyle.THIN);
        contentStyle.setBorderBottom(BorderStyle.THIN);


        HorizontalCellStyleStrategy headStyleStrategy = new HorizontalCellStyleStrategy(headStyle, contentStyle);
        return headStyleStrategy;
    }

    /**
     * 生成合计表格
     *
     * @param tableNum
     * @return
     */
    private static WriteTable totalTableCreate(int tableNum) {
        // 样式
        WriteCellStyle totalStyle = new WriteCellStyle();
        // 水平靠左
        totalStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        // 垂直居中
        totalStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置灰色背景
        totalStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        totalStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        // 自动换行
        totalStyle.setWrapped(true);

        // 设置字体格式
        WriteFont font = new WriteFont();
        font.setBold(Boolean.FALSE);
        font.setFontHeightInPoints((short)10);
        font.setFontName("宋体");
        totalStyle.setWriteFont(font);

        HorizontalCellStyleStrategy totalStrategy = new HorizontalCellStyleStrategy(null, totalStyle);

        // 生成表格
        WriteTable titleTable = EasyExcel.writerTable(tableNum)
                .registerWriteHandler(new SimpleRowHeightStyleStrategy((short)25, (short)25))
                .registerWriteHandler(totalStrategy)
                .needHead(Boolean.FALSE)
                .build();
        return titleTable;
    }
}

UserInfo:

/**
 * @description: 用户信息
 */
public class UserInfo {

    private String name;

    private String sex;

    private Integer age;

    private String cardNo;

    private String address;

    private String companyName;

    private String workNo;

    private String companyAddress;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getCardNo() {
        return cardNo;
    }

    public void setCardNo(String cardNo) {
        this.cardNo = cardNo;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getCompanyName() {
        return companyName;
    }

    public void setCompanyName(String companyName) {
        this.companyName = companyName;
    }

    public String getWorkNo() {
        return workNo;
    }

    public void setWorkNo(String workNo) {
        this.workNo = workNo;
    }

    public String getCompanyAddress() {
        return companyAddress;
    }

    public void setCompanyAddress(String companyAddress) {
        this.companyAddress = companyAddress;
    }
}

2.测试

代码如下（示例）：

    @GetMapping("/export")
    public void downExcel(HttpServletResponse response){

        try {
            List<UserInfo> userInfos = buildUserInfos();
            EasyExcelUtils.download(response, userInfos);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private List<UserInfo> buildUserInfos() {
        List<UserInfo> userInfos = new ArrayList<>();
        for (int i = 0; i < 20; i++) {
            UserInfo userInfo = new UserInfo();
            userInfo.setName("姓名" + i);
            userInfo.setSex("男");
            userInfo.setAge(17 + i);
            userInfo.setCardNo("身份证号" + i);
            userInfo.setAddress("住址" + i);
            userInfo.setCompanyName("公司名称" + i);
            userInfo.setWorkNo("工号" + i);
            userInfo.setCompanyAddress("公司地址" + i);
            userInfos.add(userInfo);
        }
        return userInfos;
    }

3.导出结果展示

---

总结

因为业务场景的不同，需要不同样式的导出模板，自定义表头的灵活性更高，可以根据这个进行扩展，不同格式按不同 table 划分，然后组装，以满足更多场景下的导出