Java基于EasyExcel根据模板生成Excel（涉及合并单元格）

模板文件：

controller：

 public void export(HttpServletResponse response) {
        MyExcelUtil myExcelUtil = new MyExcelUtil();
        myExcelUtil.listFill(response);
    }

MyExcelUtil（基于EasyExcel官方文档）：

    

import org.springframework.core.io.ClassPathResource;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import org.jeecg.modules.product.VO.IissueList;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.*;

public class MyExcelUtil {
    //根据模板导出excel
    public void listFill(HttpServletResponse response) {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + "问题清单" + ".xlsx");
        // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
        // 填充list 的时候还要注意 模板中{.} 多了个点 表示list
        //获取文件路径
        ClassPathResource resource= new ClassPathResource("file/excel/IissueListPro.xlsx");
        String templateFileName = null;
        try {
            templateFileName = resource.getFile().getPath();
        } catch (IOException e) {
            e.printStackTrace();
        }

        //模板中需要批量添加的，{.**}中的数据，
        //IissueList是根据模板需要批量添加的实体类，字段名和模板中的字段名相同
        ArrayList<IissueList> list=new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            IissueList iissueList = new IissueList();
            iissueList.setNum(String.valueOf(i));
            iissueList.setCompanyName("2");
            iissueList.setAddress("qwnm");
            iissueList.setQuestion("6666");
            list.add(iissueList);
        }

        ExcelWriter excelWriter = null;
        try {
            //合并前6列
            int[] mergeColumeIndex = {0,1,2,3,4,5};
            //合并行，两组数字为一次合并，比如3、4为一组，合并3到4行，5、6同理
            int[] mergeRowIndex = {3,4,5,6};
            excelWriter = EasyExcel.write(response.getOutputStream())
                                    .withTemplate(templateFileName)
                                    //调用合并策略
                                    .registerWriteHandler(new ExcelFillCellMergeStrategy(mergeRowIndex, mergeColumeIndex))
                                    .build();
        } catch (IOException e) {
            e.printStackTrace();
        }
        WriteSheet writeSheet = EasyExcel.writerSheet().build();
        // 这里注意 入参用了forceNewRow 代表在写入list的时候不管list下面有没有空行 都会创建一行，然后下面的数据往后移动。默认 是false，会直接使用下一行，如果没有则创建。
        // forceNewRow 如果设置了true,有个缺点 就是他会把所有的数据都放到内存了，所以慎用
        // 简单的说 如果你的模板有list,且list不是最后一行，下面还有数据需要填充 就必须设置 forceNewRow=true 但是这个就会把所有数据放到内存 会很耗内存
        // 如果数据量大 list不是最后一行 参照下一个
        FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
        excelWriter.fill(list, fillConfig, writeSheet);
        Map<String, Object> map = new HashMap<>();
        map.clear();
        map.put("city", "成都市");
        excelWriter.fill(map, writeSheet);
        excelWriter.finish();
    }
}

合并策略工具（借鉴别处，地址忘记。。）：

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.CellType;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.List;

public class ExcelFillCellMergeStrategy implements CellWriteHandler {
    /** 需要进行单元格合并的列数组 **/
    private int[] mergeColumnIndex;
    /** 单元格合并从第几行开始 **/
    private int[] mergeRowIndex;
    public ExcelFillCellMergeStrategy() {}
    public ExcelFillCellMergeStrategy(int[] mergeRowIndex, int[] mergeColumnIndex) {
        this.mergeRowIndex = mergeRowIndex;
        this.mergeColumnIndex = mergeColumnIndex;
    }
    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                 List<WriteCellData<?>> list, Cell cell, Head head, Integer integer, Boolean isHead) {
        int curRowIndex = cell.getRowIndex();
        int curColIndex = cell.getColumnIndex();
        int length = mergeRowIndex.length;
        //判断是否合并行，
        for (int k = 0; k < length; k++) {
            //2个数为一组，一组做一次合并。
            if(k!=0 && k%2!=0){
                continue;
            }
            //每组数开始合并
            if (curRowIndex >= mergeRowIndex[k] && curRowIndex <mergeRowIndex[k+1]) {
                for (int i = 0; i < mergeColumnIndex.length; i++) {
                    if (curColIndex == mergeColumnIndex[i]) {
                        mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                        break;
                    }
                }
            }
        }
    }

    /**
     * 当前单元格向上合并
     *
     * @param writeSheetHolder
     * @param cell
     *            当前单元格
     * @param curRowIndex
     *            当前行
     * @param curColIndex
     *            当前列
     */
    private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        Object curData =
                cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
        Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
        Object preData =
                preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
        // 将当前单元格数据与上一个单元格数据比较
        Boolean dataBool = preData.equals(curData);
        if (true) {
            Sheet sheet = writeSheetHolder.getSheet();
            List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
            boolean isMerged = false;
            for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
                CellRangeAddress cellRangeAddr = mergeRegions.get(i);
                // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
                if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
                    sheet.removeMergedRegion(i);
                    cellRangeAddr.setLastRow(curRowIndex);
                    sheet.addMergedRegion(cellRangeAddr);
                    isMerged = true;
                }
            }
            // 若上一个单元格未被合并，则新增合并单元
            if (!isMerged) {
                CellRangeAddress cellRangeAddress =
                        new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
                sheet.addMergedRegion(cellRangeAddress);
            }
        }
    }
}