easyExcel 导出 合并表头 合并相同数据单元格

• 实体类设计自定义表头(建议重新建一个POI class，不要使用原本实体类)

@ExcelIgnore 不需要展示在excel中的列

@ExcelProperty({"需求部门需求情况", "需求部门"}) 需求部门为需求部门需求情况的一个子

如：

/**
 * 需求响应导出映射的实体
 * since:2019.11.14
 */
@Data
@ContentRowHeight(20)
@HeadRowHeight(20)
@ColumnWidth(20)
public class MatterResponsePOI implements Serializable {

    private static final long serialVersionUID = 6862907353794257432L;

    /**
     * 为service服务
     */
   @ExcelIgnore
    private Long id;

   /**
    * 序号
    */
   @ExcelProperty("序号")
   @ColumnWidth(7)
   @ApiModelProperty(value = "序号", name = "orderNumber", dataType = "Integer")
   private Integer orderNumber = 1;

   /**
    * 需求部门
    */
   @ExcelProperty({"需求部门需求情况", "需求部门"})
   @ApiModelProperty(value = "需求部门", name = "deptIdName", dataType = "String")
   private String deptIdName;

   /**
    * 基本编码
    */
   @ExcelProperty({"需求部门需求情况", "事项编码"})
   @ApiModelProperty(value = "基本编码", name = "matterCode", dataType = "String")
   private String matterCode;

   /**
    * 需求部门事项名称
    */
   @ExcelProperty({"需求部门需求情况", "需求部门事项名称"})
   @ColumnWidth(23)
   @ApiModelProperty(value = "需求部门事项名称，必填，长度不超过255", name = "matterName", dataType = "String")
   private String matterName;

   /**
    * 需求部门材料类别
    */
   @ExcelProperty({"需求部门需求情况", "需求部门材料类别"})
   @ColumnWidth(23)
   @ApiModelProperty(value = "材料类别（1-表格类,2-证明类,3-批文类,4-证照类,5-其他类）", name = "materialKind", dataType = "String")
   private String materialKind;

   /**
    * 需求部门材料名称
    */
   @ExcelProperty({"需求部门需求情况", "需求部门材料名称"})
   @ColumnWidth(23)
   @ApiModelProperty(value = "需求部门材料名称", name = "materialName", dataType = "String")
   @TableField(condition = SqlCondition.LIKE)
   private String materialName;

   /**
    * 需求部门数据项
    */
   @ExcelProperty({"数源部门确认情况", "需求部门数据项"})
   @ApiModelProperty(value = "需求部门数据项", name = "itemName", dataType = "String")
   @TableField(condition = SqlCondition.LIKE)
   private String itemName;

   /**
    * 共享需求
    */
   @ExcelProperty({"数源部门确认情况", "共享需求"})
   @ApiModelProperty(value = "标记数源部门（共享需求）", name = "markOrgName", dataType = "String")
   private String markOrgName;

   /**
    * 备注内容
    */
   @ExcelIgnore
   @ApiModelProperty(value = "需求梳理备注", name = "desc", dataType = "String")
   private String desc;

   /**
    * 是否共享（1：是，0：否）
    */
   @ExcelIgnore
   @ApiModelProperty(value = "是否共享（1：是，0：否）", name = "sourceState", dataType = "String")
   private String sourceState;

   /**
    * 不予共享理由
    */
   @ExcelIgnore
   @ApiModelProperty(value = "不予共享理由", name = "refuseReason", dataType = "String")
   private String refuseReason;

   /**
    * 数源部门名称
    */
   @ExcelProperty({"数源部门确认情况", "数源部门"})
   @ApiModelProperty(value = "数源部门名称", name = "sourceOrgName", dataType = "String")
   private String sourceOrgName;

   /**
    * 数源部门确认材料类别
    */
   @ExcelProperty({"数源部门确认情况", "数源部门确认材料类别"})
   @ColumnWidth(28)
   @ApiModelProperty(value = "数源部门确认材料类别", name = "cmaterialKind", dataType = "String")
   @TableField(condition = SqlCondition.LIKE)
   private String cmaterialKind;

   /**
    * 数源部门确认材料名称
    */
   @ExcelProperty({"数源部门确认情况", "数源部门确认材料名称"})
   @ColumnWidth(28)
   @ApiModelProperty(value = "数源部门确认材料名称", name = "cmaterialName", dataType = "String")
   @TableField(condition = SqlCondition.LIKE)
   private String cmaterialName;



   /**
    * 数源部门确认数据项
    */
   @ExcelProperty({"数源部门确认情况", "数源部门确认数据项"})
   @ColumnWidth(28)
   @ApiModelProperty(value = "数源部门确认数据项", name = "citemName", dataType = "String")
   @TableField(condition = SqlCondition.LIKE)
   private String citemName;

   /**
    * 数源部门备注
    */
   @ExcelProperty({"", "数源部门备注"})
   @ApiModelProperty(value = "数源部门备注", name = "cdesc", dataType = "String")
   private String cdesc;

}

• 访问接口设计

@PostMapping({"/export", "/excel/export"})
   @ApiOperation(value = "供需对接-三清单导出", notes = "供需对接-三清单导出", httpMethod = "POST")
   public void exportExcel(Page<SupDetailVO> page, SupDetailDTO supDetailDTO, HttpServletRequest request, HttpServletResponse response) throws Exception {
      //文件名称
      String fileName = getNameWithRequest(request,   "事项数据.xlsx");
      //导出不允许超过1000条
      page.setSize(1000L);
      //初始化字典项
      initDictMap();
      //新增pageWithInput方法，直接获取本身加关联,并按照解析的方式赋值
      IPage<SupDetailVO> iPage = supDetailListService.getMatterDataPageList(page,supDetailDTO);
      //需要把不导出的字段过滤掉
      List<SupDetailVO> list = iPage.getRecords();
      List<SupDetailPOI> supDetails = Lists.newArrayList();
      SupDetailPOI supDetail;
      int i = 1;
      //数据转换
      for (SupDetailVO one : list) {
         supDetail = getSupDetailVO(one, i);
         supDetail.setSupMatterName(one.getSupMatterName()+"("+one.getMatterCode()+")");
         if(one.getChildNames() != null && one.getChildNames() != ""){
            supDetail.setChildCode(one.getMatterCode());
         }
         supDetails.add(supDetail);
         i++;
      }

      //设置返回头
      setResponse(response, fileName);
      //需要合并的列
      int[] mergeColumeIndex = {1, 2, 3,4,5,6};
      //需要从第一行开始，列头第一行
      int mergeRowIndex = 1;
      EasyExcel//将数据映射到DownloadDTO实体类并响应到浏览器
            .write(new BufferedOutputStream(response.getOutputStream()),SupDetailPOI.class)
            //07的excel版本,节省内存
            .excelType(ExcelTypeEnum.XLSX)
            //是否自动关闭输入流
            .autoCloseStream(Boolean.TRUE)
    	 //设置内容合并单元格
            .registerWriteHandler(new ExcelFillCellMergeStrategy(mergeRowIndex, mergeColumeIndex))
            //设置拦截器或自定义样式
            .registerWriteHandler(getStyleStrategy())
//               // 自定义列宽度，有数字会
//                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
            //设置excel保护密码
//                .password("123456")
            .sheet("事项数据").doWrite(supDetails);
   }

/**
 * 初始化字典
 */
private void initDictMap() {
   List<String> typeList = Lists.newArrayList();
   typeList.add("element_material_type");
   typeList.add("requirementDetails_Confirmation");
   Map<String, Map<String, String>> dictMap = remoteDictService.getDictGetKey(typeList);
   // 材料类别
   elementMaterialTypeMap = getDictMap("element_material_type", dictMap);
   //事项状态
   confirmationTypeMap = getDictMap("requirementDetails_Confirmation", dictMap);
   // 是否
   whetherOrNotMap.put(SupMatterConstants.PARENT,"是");
   whetherOrNotMap.put(SupMatterConstants.UNPARENT,"否");
}

@Override
public Map<String, Map<String, String>> getDictMap(List<String> typeList) {
   Map<String, Map<String, String>> map = new HashMap<>();
   typeList.forEach(x -> {
      QueryWrapper<SysDictItem> wrapper = new QueryWrapper<>();
      wrapper.eq("type", x).eq("del_flag", "0");
      List<SysDictItem> itemList = sysDictItemService.list(wrapper);
      Map<String, String> valueLabelMap = new HashMap<>();
      itemList.forEach(y -> {
         valueLabelMap.put(y.getValue(), y.getLabel());
      });
      map.put(x, valueLabelMap);
   });
   return map;
}

//设置返回头
private void setResponse(HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
   //response为HttpServletResponse对象
   response.setContentType("application/vnd.ms-excel;charset=utf-8");
   //fileName是弹出下载对话框的文件名，不能为中文，中文请自行编码
   response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
}

//设置样式 去除默认表头及内容居中
public static HorizontalCellStyleStrategy getStyleStrategy(){
   //内容样式策略
   WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
   //垂直居中,水平居中
   contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
   contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
   contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
   contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
   contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
   contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
   //设置 自动换行
   contentWriteCellStyle.setWrapped(true);
   // 字体策略
   WriteFont contentWriteFont = new WriteFont();
   // 字体大小
   contentWriteFont.setFontHeightInPoints((short) 12);
   contentWriteCellStyle.setWriteFont(contentWriteFont);
   //头策略使用默认
   WriteCellStyle headWriteCellStyle = new WriteCellStyle();
   headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
   return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}

@Data
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 beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {

   }

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

   }

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

      if (curRowIndex > mergeRowIndex) {
         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) {
      //获取当前行的第一列的数据和上一行的第一列数据，通过第一行数据是否相同进行合并
//        Cell preCell_now = cell.getSheet().getRow(curRowIndex ).getCell(curColIndex);
//        Object curData = preCell_now.getCellTypeEnum() == CellType.STRING ? preCell_now.getStringCellValue() : preCell_now.getNumericCellValue();
//        Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex - 1);
//          Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
      //获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并
      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();

      // 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行
      //
      if (curData.equals(preData)) {
         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);
         }
      }
   }
}

• 引入依赖

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

easyExcel官网:https://alibaba-easyexcel.github.io/quickstart/write.html

借鉴文章：https://blog.csdn.net/qq_41514643/article/details/106993760?utm_medium=distribute.pc_aggpage_search_result.none-task-blog-2~all~first_rank_v2~rank_v25-1-106993760.nonecase&utm_term=easyexcel%E8%AE%BE%E7%BD%AE%E8%A1%A8%E5%A4%B4%E6%A0%B7%E5%BC%8F&spm=1000.2123.3001.4430