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服务*/@ExcelIgnoreprivate 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;}@Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {}@Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {}@Overridepublic 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