前言：

有一种场景，在写入结构化数据之前，增加一些整体描述的数据，此类数据一般无固定结构，或者不好被编程描述。于是就需要手动指定单元格的写入文本。但是实际操作起来非常麻烦，尤其是某些单元格，还要一些样式，样式又没有规律。

例如：

|--------|-------|--------|----|
| 班级 | 12班 |
| 班主任 | 张三 | 性别 | 女 |
| 入学时间 | 2010 |

| 姓名 | 年龄 | 性别 | ... |
| 李四 | 14岁 | 女生 | ... |
| 王五 | 15岁 | 男生 | ... |
| 赵六 | 15岁 | 女生 | ... |

学生表格前面增加了对于班级的整体描述，并且需要一些label样式，以清晰展示

代码：

CustomCellStyleStrategy.java 样式策略，用来记录单元格样式

import org.apache.poi.ss.usermodel.CellStyle;

import java.util.HashMap;
import java.util.Map;

public class CustomCellStyleStrategy {

    private final Map<String, CellStyle> map = new HashMap<>();

    public CustomCellStyleStrategy add(int rowIndex, int colIndex, CellStyle cellStyle) {
        String key = rowIndex + "," + colIndex;
        map.put(key, cellStyle);
        return this;
    }

    public boolean hasStyle(int rowIndex, int colIndex) {
        String key = rowIndex + "," + colIndex;
        return map.containsKey(key);
    }

    public CellStyle getStyle(int rowIndex, int colIndex) {
        String key = rowIndex + "," + colIndex;
        return map.get(key);
    }
}

CustomCellStyleHandler.java 样式单元格处理器，用来处理单元格样式，此处理器只会注册在table上，非全局

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;

import java.util.List;

public class CustomCellStyleHandler implements CellWriteHandler {

    private final CustomCellStyleStrategy customCellStyleStrategy;

    public CustomCellStyleHandler(CustomCellStyleStrategy strategy) {
        this.customCellStyleStrategy = strategy;
    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        if (!customCellStyleStrategy.hasStyle(cell.getRowIndex(), cell.getColumnIndex())) {
            return;
        }
        CellStyle cellStyle = customCellStyleStrategy.getStyle(cell.getRowIndex(), cell.getColumnIndex());
        cell.setCellStyle(cellStyle);
    }
}

CustomCellWriter.java 主要写入者类，使用建造者模式，可以链式调用

import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import org.apache.poi.ss.usermodel.CellStyle;

import java.util.ArrayList;
import java.util.List;

public class CustomCellWriter {

    private final List<Row> rows = new ArrayList<>();
    private int cursor = 0;

    public CustomCellWriter cursor(int rowIndex) {
        this.cursor = rowIndex;
        return this;
    }

    public Row row() {
        return new Row(this);
    }

    public void doWrite(ExcelWriter excelWriter, WriteSheet writeSheet, WriteTable writeTable) {
        CustomCellStyleStrategy strategy = new CustomCellStyleStrategy();
        List<List<Object>> data = new ArrayList<>();
        for (int i = 0; i < rows.size(); i ++) {
            List<Cell> cells = rows.get(i).cells;
            List<Object> targetRows = new ArrayList<>();
            for (int j = 0; j < cells.size(); j ++) {
                Cell cell = cells.get(j);
                if (cell.cellStyle != null) {
                    strategy.add(i + cursor, j, cell.cellStyle);
                }
                targetRows.add(cell.content);
            }
            data.add(targetRows);
        }
        CustomCellStyleHandler customCellStyleHandler = new CustomCellStyleHandler(strategy);
        writeTable.getCustomWriteHandlerList().add(customCellStyleHandler);
        excelWriter.write(data, writeSheet, writeTable);
    }

    public static class Row {

        private final CustomCellWriter parent;
        private final List<Cell> cells = new ArrayList<>();

        private Row(CustomCellWriter customCellWriter) {
            this.parent = customCellWriter;
        }

        public CustomCellWriter done() {
            this.parent.rows.add(this);
            return this.parent;
        }

        public Row cell(Object content, CellStyle cellStyle) {
            cells.add(new Cell(content, cellStyle));
            return this;
        }

        public Row cell(Object content) {
            cells.add(new Cell(content, null));
            return this;
        }
    }

    public static class Cell {

        private Object content;
        private CellStyle cellStyle;

        private Cell(Object content, CellStyle cellStyle) {
            this.content = content;
            this.cellStyle = cellStyle;
        }
    }

使用：

StudentExporter.java

public class StudentExporter {

        // 不会关闭输出流，需调用者自行关闭
        public void export(ClassInfo classInfo, List<Student> students, OutputStream os) {
            try (ExcelWriter excelWriter = EasyExcel.write(os).build()) {
                WriteSheet writeSheet = EasyExcel.writerSheet().sheetNo(0).build();

                CellStyle labelStyle = createLabelStyle(excelWriter); // 创建label样式
                CellStyle numberStyle = createNumberStyle(excelWriter); // 创建数字样式（蓝色字体）

                // 使用CustomCellWriter自定义写入
                WriteTable classTable = EasyExcel.writerTable(0).build();
                new CustomCellWriter().row()
                        .cell("班级", labelStyle).cell(classInfo.getName())
                        .done()
                        .row()
                        .cell("班主任", labelStyle).cell(classInfo.getManager()).cell("性别", labelStyle).cell(classInfo.getManagerGender())
                        .done()
                        .row()
                        .cell("入学时间", labelStyle).cell(classInfo.getYear(), numberStyle)
                        .done()
                        .row()
                        .cell("").cell("") // 空一行
                        .done()
                        .doWrite(excelWriter, writeSheet, classTable);

                /*
                // 继续写入其他非结构数据
                WriteTable xxxTable = EasyExcel.writerTable(x).build();
                new CustomCellWriter().cursor(4) // 需要指定从第几行开始，因为第一次写入占了4行
                        .row().cell("xxx").done()
                        .doWrite(excelWriter, writeSheet, xxxTable);
                 */

                // 正常写入学生列表（结构化数据）
                WriteTable studentsTable = EasyExcel.writerTable(1).head(Student.class).build(); // 学生列表样式，由Student.class指定(注解)
                excelWriter.write(students, writeSheet, studentsTable);
                excelWriter.finish();
            }
        }
    }