全网最复杂easyExcel-Demo

pom文件中主要依赖为

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

 excel合并单元格工具类：这个工具类是网上找了好久的，因为网上太多N列合并成一行的

我还做了横向合并单元格后导出图片的功能，本文章的源码和新功能的git地址为：

GitHub - centosroot123123/EasyExcel-sc: 包含一行中连续的单元格合并，将图片导入合并后的单元格并将其填满，但导出excel后会出现部分内容有问题，属于小瑕疵，但功能完美实现了

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

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

//列合并工具类
public class ExcelFillCellMergePrevColUtils implements CellWriteHandler {
    private static final String KEY ="%s-%s";
    //所有的合并信息都存在了这个map里面，vx=19192164261
    Map<String, Integer> mergeInfo = new HashMap<>();

    public ExcelFillCellMergePrevColUtils() {
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        //当前行
        int curRowIndex = cell.getRowIndex();
        //当前列
        int curColIndex = cell.getColumnIndex();

        Integer num = mergeInfo.get(String.format(KEY, curRowIndex, curColIndex));
        if(null != num){
            // 合并最后一行 ,列
            mergeWithPrevCol(writeSheetHolder, cell, curRowIndex, curColIndex,num);
        }
    }
    public void mergeWithPrevCol(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex, int num) {
        Sheet sheet = writeSheetHolder.getSheet();
        CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex, curRowIndex, curColIndex, curColIndex + num);
        sheet.addMergedRegion(cellRangeAddress);
    }
    //num从第几列开始增加多少列,(6,2,7)代表的意思就是第6行的第2列至第2+7也就是9列开始合并
    public void add (int curRowIndex,  int curColIndex , int num){
        mergeInfo.put(String.format(KEY, curRowIndex, curColIndex),num);
    }

}

VO类：

public class AutoExportLogVo {//getter,setter,构造都自己生成，或者用lombok
    private String name;
    private String company;
    private String logTime;
    private String remark;
    private String isUpdate;
}

public class AutoExportInfoVo {//getter,setter,构造都自己生成，或者用lombok
    private String name;
    private String empty;//由于easyexcel无法合并空白的单元格，需要填充""到单元格中，然后合并
    this.name = name;
    }

public class AutoExport {//getter,setter,构造都自己生成，或者用lombok
    private String id;
    private String name;
    private String company;
    private String phone;
    private String stName;
    private String createUser;
    private String createTime;
    private String content;
}

 使用范例：

//id, getAutoExportLogVos(id), getSignatorys(id), getAutoExport(id)这四个参数根据业务需求来
InputStream resourceAsStream = this.getClass().getClassLoader().
                        getResourceAsStream("template/资料审核会签单样式.xlsx");
//模板放在resources下面
                excel(resourceAsStream, id, getAutoExportLogVos(id), getSignatorys(id), getAutoExport(id));

public void excel(InputStream resourceAsStream, String fileName, List<AutoExportLogVo> data,
                      List<AutoExportInfoVo> infos, AutoExport exportExcelBase/*, HttpServletResponse response*/) {

        //ServletOutputStream out = response.getOutputStream();
        //BufferedOutputStream bos = new BufferedOutputStream(out);
        //设置文件类型
        //response.setContentType("application/vnd.ms-excel");
        //设置编码格式
        //response.setCharacterEncoding("utf-8");
        //response.setHeader("Content-disposition", "attachment;filename=" +
        //URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
        //创建excel,下面一行filePath换成bos，就是返回流给浏览器进行下载

        ExcelFillCellMergePrevColUtils excelFillCellMergePrevColUtils = new ExcelFillCellMergePrevColUtils();

        for (int i = 0; i < data.size(); i++) {
            excelFillCellMergePrevColUtils.add(7+i, 0, 1);//合并审批人
            excelFillCellMergePrevColUtils.add(7+i, 2, 2);//合并审批人单位
            excelFillCellMergePrevColUtils.add(7+i, 5, 4);//合并审批时间
            excelFillCellMergePrevColUtils.add(7+i, 10, 1);//合并审批意见
        }

        for (int i = 0; i < infos.size(); i++) {//合并打印签章
            excelFillCellMergePrevColUtils.add(8+data.size()+i, 1, 11);
        }

        String filePath = "D:/" + fileName + ".xlsx";
        ExcelWriterBuilder builder = EasyExcel.write(filePath);
        builder.registerWriteHandler(excelFillCellMergePrevColUtils);

        ExcelWriter excelWriter = builder.withTemplate(resourceAsStream).build();
        //创建sheet
        WriteSheet writeSheet = EasyExcel.writerSheet(0).build();
        FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();

        excelWriter.fill(new FillWrapper("log", data), fillConfig, writeSheet);
        excelWriter.fill(new FillWrapper("info", infos), fillConfig, writeSheet);
        excelWriter.fill(exportExcelBase, writeSheet);

        //填充完成
        excelWriter.finish();
        //bos.flush();
    }

模板：

用法：{字段名}对应exportExcelBase

excelWriter.fill(exportExcelBase, writeSheet);

{数组名.字段名}对应数组遍历：

excelWriter.fill(new FillWrapper("log", data), fillConfig, writeSheet);

至于你要问简单模板只有一个数组作为数据源，很简单：

excelWriter.fill(data, fillConfig, writeSheet);这是单数组写法可以和excelWriter.fill(exportExcelBase, writeSheet);一起用

最终效果展示：

调出打印页面是因为导出之后有打印纸质表格的需求，所以要注意页边距和能否完整打印 

最后如果有其他疑问可以留言或者联系我索要源码，VX号码在上面代码注释中