easyexcel 填写模板指定行合并单元格

1.easyexcel（官网）添加版本依赖

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

2.编写一个类继承合并单元策略，重写merge()方法

package com.lxj.exam.handler;

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import java.util.List;

/**
 * 合并单元格处理类
 */
public class MyHandler extends AbstractMergeStrategy {

    /**
     * 合并开始行
     */
    private Integer startRow = 0;
    /**
     * list表格所有的合并列集合
     */
    private List<CellRangeAddress> cellRangeAddressList = null;

    public MyHandler() {
    }

    public MyHandler(int startRow, List<CellRangeAddress> cellRangeAddressList) {
        this.startRow = startRow;
        this.cellRangeAddressList = cellRangeAddressList;
    }

    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
        // 设置样式
        CellStyle cellStyle = cell.getCellStyle();
        //水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //自动换行
        cellStyle.setWrapText(true);
        //在这里判断从哪一行开始调用合并的方法
        if (cell.getRowIndex() > this.startRow) {
            if (relativeRowIndex == null || relativeRowIndex == 0) {
                return;
            }
            mergeColumn(sheet, cell, head, relativeRowIndex);
        }
    }

    /**
     * 合并单元格
     *
     * @param sheet
     * @param cell
     * @param head
     * @param relativeRowIndex
     */
    protected void mergeColumn(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
        int rowIndex = cell.getRowIndex();
        int colIndex = cell.getColumnIndex();
        sheet = cell.getSheet();
        // 获取前一行
        Row preRow = sheet.getRow(rowIndex - 1);
        //获取前一列
        Cell preCell = preRow.getCell(colIndex);
        List<CellRangeAddress> list = this.cellRangeAddressList;
        for (int i = 0; i < list.size(); i++) {
            CellRangeAddress cellRangeAddress = list.get(i);
            if (cellRangeAddress.containsColumn(preCell.getColumnIndex())) {
                int lastColIndex = cellRangeAddress.getLastColumn();
                int firstColIndex = cellRangeAddress.getFirstColumn();
                CellRangeAddress cra = new CellRangeAddress(cell.getRowIndex(), cell.getRowIndex(), firstColIndex, lastColIndex);
                sheet.addMergedRegion(cra);
                // 加边框
                RegionUtil.setBorderBottom(BorderStyle.THIN, cra, sheet);
                RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheet);
                RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheet);
                RegionUtil.setBorderTop(BorderStyle.THIN, cra, sheet);
                return;
            }
        }

    }



}

3.测试方法

/**
*@param templateFilePath 模板文件路径
*@param filePath 导出文件路径
*/
 public static void test3(String templateFilePath, String filePath) {
        Map<String, List<ExcelDto>> map = new HashMap<>();
        map.put("a", getListDtos());
        Map<String, Object> testMap = new HashMap<>();
        testMap.put("date", LocalDateTime.now());
        testMap.put("title", "测试");
        // 添加合并单元格地址
        List<CellRangeAddress> list = new ArrayList<>();
        //new CellRangeAddress(开始行，结束行，开始列，结束列) 
        list.add(new CellRangeAddress(1, 1, 2, 3));
        list.add(new CellRangeAddress(1, 1, 5, 6));
        // 创建ExcelWriterBuilder 
        ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(filePath)
                .withTemplate(templateFilePath);
        ExcelWriter excelWriter = excelWriterBuilder.build();
        // 创建writeSheet，并注册合并单元格handler
        WriteSheet writeSheet = EasyExcel.writerSheet()
                .registerWriteHandler(new MyHandler(0,list))
                .build();
        WriteSheet writeSheet1 = EasyExcel.writerSheet().build();
        // 填写配置，forceNewRow true表示自动创建一行，后面的数据后移
        FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
        //填写数据
        excelWriter.fill(new FillWrapper("a", getListDtos()), fillConfig, writeSheet);
        excelWriter.fill(testMap,writeSheet1);
        // 关闭填写
        excelWriter.finish();
    }

    public static List<ExcelDto> getListDtos() {
        List<ExcelDto> list = new ArrayList<>();
        int i = 0;
        while (i < 1) {
            list.add(new ExcelDto("张三", 2, 2, "打工仔", "打工仔"));
            list.add(new ExcelDto("李四1", 2, 2, "打工仔", "打工仔"));
            list.add(new ExcelDto("李四2", 2, 2, "打工仔", "打工仔"));
            list.add(new ExcelDto("李四3", 2, 2, "打工仔", "打工仔"));
            list.add(new ExcelDto("李四4", 2, 2, "打工仔", "打工仔"));
            list.add(new ExcelDto("李四5", 2, 2, "打工仔", "打工仔"));
            i++;
        }
        return list;
    }

模板文件

导出的文件内容

 注：有两个需要注意的地方是，1.代码里面用了forceNewRow为ture，在写excel模板的时候，

表达式需要合并的地方一定要合并。

 没有合并，结果会像下面看到一样。

 2.第二个要注意的地方是，这个两个填充数据的顺序一定是list数据在上，kay-value数据在下，否则导出来的数据没有合并。像下面的这样就是对的。