EasyExcel报表 复杂格式导出、动态合并行，列

文章内容

需求说明

使用版本

easyexcel版本：2.2.4;

需求格式

表头包含所有检测方，还有根据检测方、部门合并单元格，以及每个检测方后面还增加了一行合计费用，合计数量。此处有个前提，虽然检测方没有合并，但是整体还是根据检测方来分类数据，可以理解为这个表的数据按照检测方–>部门的二级分组，部门小计的合并样式是与设备部门相同

参考文章

easyexcel复杂格式导出、自定义合并: link.
easyexcel的下载和自定义单元格合并: link.

代码

3.1 pom

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

3.2 Controller

	@ResponseBody
	@GetMapping("/excelExport")
	public void excelExport(@RequestParam Map paramMap) throws IOException {
		// 所选日期
		String startDate = getParameter("startDate");
		String endDate = getParameter("endDate");
		
		//格式化日期样式：XXXX年XX月XX日~XXXX年XX月XX日
		String dataName = DateUtils.parseData(startDate) + "~" + DateUtils.parseData(endDate);
		// 设置文件名
		String fileName = dataName + " 统计表";
		String sheetName = "统计表";
		//DetectionManagement 表对象
		List<DetectionManagement> data = service.findByParam(paramMap).getData();
		// 处理数据
		List<CheckDetectionManagementExcel> dataList = detectionDataStrategy.optionData(data);
		// 行合并策略map
		Map<String, List<RowRangeDto>> strategyMap = detectionDataStrategy.addMerStrategy(dataList);
		// 列合并策略
		int[] totalLength= detectionDataStrategy.totalLength(data);
		
		DetectionMergeStrategy mergeStrategy = new DetectionMergeStrategy(totalLength,strategyMap);

		writeExcel(response, dataList, fileName, sheetName, dataName, mergeStrategy);

	}

	/**
	 * 写出excel
	 * @param response
	 * @param writeList 数据源
	 * @param fileName  文件名
	 * @param sheetName sheet名
	 * @param dataName  第二行的时间范围内容
	 * @param mergeStrategy 合并策略
	 * @throws IOException
	 */
	
	public static void writeExcel(HttpServletResponse response, List<CheckDetectionManagementExcel> writeList,
			String fileName, String sheetName, String dataName, DetectionMergeStrategy mergeStrategy)
			throws IOException {
		try (OutputStream out = EasyExcelUtil.getOutputStream(fileName, response)) {
			EasyExcel.write(out, CheckDetectionManagementExcel.class).needHead(true)//
					.registerWriteHandler(new DetectionSheetWriteHandler(dataName))
					.registerWriteHandler(DetectionCellStyleStrategy.getStyleStrategy((short) 15, (short) 9))
					.registerWriteHandler(mergeStrategy).useDefaultStyle(true).relativeHeadRowIndex(3)
					.autoCloseStream(Boolean.FALSE).excelType(ExcelTypeEnum.XLSX).sheet(0, sheetName)
					.doWrite(writeList);
		} catch (Exception e) {
			// 重置response
			// response.reset();
			response.setContentType("application/json");
			response.setCharacterEncoding("utf-8");
			Map<String, String> map = new HashMap<String, String>();
			map.put("status", "failure");
			map.put("message", "下载文件失败" + e.getMessage());
			response.getWriter().println(JSON.toJSONString(map));
		}
	}

3.3.1 DetectionDataStrategy 将数据处理成导出的数据格式

	private BigDecimal totalCost;// 总计费用
	private BigDecimal cost;// 费用小计
	private int totalAmount;// 总计台数

	/**
	 * 处理导出的数据，加上序号，将存字典主键的字段值，改成对应的名称
	 * 
	 * @param dataList
	 * @return
	 */
	public List<CheckDetectionManagementExcel> optionData(List<DetectionManagement> dataList) {

		// 简单地按检测方排个序，再分组，方便组装数据
		Map<Long, List<DetectionManagement>> clazzMap = dataList.stream()
				.sorted(Comparator.comparing(DetectionManagement::getCheckedBy))
				.collect(Collectors.groupingBy(DetectionManagement::getCheckedBy, TreeMap::new, Collectors.toList()));

		// 将基础数据整合成excel要写的数据，包含总分、合计这些数据
		List<CheckDetectionManagementExcel> demoList = new ArrayList<>();
		totalCost = new BigDecimal("0.00");
		totalAmount = 0;
		clazzMap.forEach((clazz, clazzes) -> {// 检测方
			String checkedByName = getTitle(clazz);// 检测方名称
			// 按设备部门排个序，再分组，方便组装数据
			Map<String, List<DetectionManagement>> byOrgCode = clazzes.stream()
					.collect(Collectors.groupingBy(DetectionManagement::getOrgCode));
			cost = new BigDecimal("0.00");
			byOrgCode.forEach((code, subjects) -> {
				int orgSum = subjects.size();
				subjects.forEach(detection -> {
					CheckDetectionManagementExcel heardTemplate = new CheckDetectionManagementExcel();
					// 设置检测方
					heardTemplate.setCheckedBy(getTitle(detection.getCheckedBy()));
					// 检测名称
					heardTemplate.setDetectionName(detection.getDetectionName());
					// 检测费用
					heardTemplate.setDetectionAmount(detection.getDetectionAmount() == null ? ""
							: NumberUtils.formatString(detection.getDetectionAmount()));
					// 设备部门
					SysOrganization organization = webService.getByCode(detection.getOrgCode()).getData();
					heardTemplate.setOrgName(organization == null ? "" : organization.getName());
					heardTemplate.setOrgCode(detection.getOrgCode());
					// 检测设备类型
					heardTemplate.setEquipmentTypeName(getTitle(detection.getEquipmentType()));
					heardTemplate.setSubtotal(orgSum + "");// 因为数据处理后要合并单元格，所以该处存的是部门合计数量
					demoList.add(heardTemplate);

					cost = cost.add(new BigDecimal(Double.toString(detection.getDetectionAmount())));
				});
			});
			totalAmount = totalAmount + clazzes.size();
			totalCost = totalCost.add(cost);
			demoList.add(new CheckDetectionManagementExcel(checkedByName + "费用-小计:", "", cost + "",
					checkedByName + "数量-小计:", "", clazzes.size() + ""));
		});
		demoList.add(
				new CheckDetectionManagementExcel("总计费用（元）:", "", totalCost + "", "总计检测（台数）:", "", totalAmount + ""));
		return demoList;
	}

3.3.2 合并策略计算

	private int[] totalLength;
	private int i;

	/**
	 * 算出每个检测方检测的条数
	 * 
	 * @param dataList
	 * @return
	 */
	public int[] totalLength(List<DetectionManagement> dataList) {
		i = 0;
		// 简单地按检测方排个序，再分组，方便组装数据
		Map<Long, List<DetectionManagement>> clazzMap = dataList.stream()
				.sorted(Comparator.comparing(DetectionManagement::getCheckedBy))
				.collect(Collectors.groupingBy(DetectionManagement::getCheckedBy, TreeMap::new, Collectors.toList()));
		// clazzMap的长度就是检测方的个数。数组长度加1是因为最后一行是总计，总计也作为一个检测方
		totalLength = new int[clazzMap.size() + 1];

		clazzMap.forEach((clazz, clazzes) -> {// 检测方
			totalLength[i] = clazzes.size();
			i = i + 1;// 每个检测方后都跟一条小计行
		});
		return totalLength;
	}

	/**
	 * 计算需要合并的行坐标
	 * 
	 * @param excelDtoList
	 * @return
	 */
	public Map<String, List<RowRangeDto>> addMerStrategy(List<CheckDetectionManagementExcel> excelDtoList) {
		Map<String, List<RowRangeDto>> strategyMap = new HashMap<>();
		CheckDetectionManagementExcel preExcelDto = null;
		for (int i = 0; i < excelDtoList.size(); i++) {
			CheckDetectionManagementExcel currDto = excelDtoList.get(i);
			if (preExcelDto != null) {
				// 从第二行开始判断是否需要合并
				if (currDto.getCheckedBy().equals(preExcelDto.getCheckedBy())) {
					// //如果检测方一样，并且所属机构一样，则可合并所属机构一列
					if (currDto.getOrgCode().equals(preExcelDto.getOrgCode())) {
						fillStrategyMap(strategyMap, "3", i);//设备部门
						fillStrategyMap(strategyMap, "5", i);//部门小计
						// //如果坐席、所属机构一样，并且合作机构也一样，则可合并合作机构一列
						// if (currDto.getCoopOrg().equals(preExcelDto.getCoopOrg())) {
						// fillStrategyMap(strategyMap, "2", i);
						// }
					}
				}
			}
			preExcelDto = currDto;
		}
		return strategyMap;
	}

	/**
	 * @Author: TheBigBlue
	 * @Description: 新增或修改合并策略map
	 * @Date: 2020/3/16
	 * @Param:
	 * @return:
	 **/
	private void fillStrategyMap(Map<String, List<RowRangeDto>> strategyMap, String key, int index) {
		List<RowRangeDto> rowRangeDtoList = strategyMap.get(key) == null ? new ArrayList<>() : strategyMap.get(key);
		boolean flag = false;
		for (RowRangeDto dto : rowRangeDtoList) {
			// 分段list中是否有end索引是上一行索引的，如果有，则索引+1
			if (dto.getEnd() == index) {
				dto.setEnd(index + 1);
				flag = true;
			}
		}
		// 如果没有，则新增分段
		if (!flag) {
			rowRangeDtoList.add(new RowRangeDto(index, index + 1));
		}
		strategyMap.put(key, rowRangeDtoList);
	}

3.4 DetectionMergeStrategy

这个类继承AbstractMergeStrategy抽象类，实现merge方法，进行自定义合并策略，传入自定义的合并策略map，解析此map，添加合并请求。
这个类的关键是那个if (cell.getRowIndex() == 1 && cell.getColumnIndex() == 0) {}，注释中也写了，因为merge这个方法是对每个cell操作的，这个merge方法是会重复执行的，如果要合并A2:A3，当当前操作Cell=A2时，要求合并A2,A3，没有问题，当当前操作Cell=A3时，又要求合并A2,A3，但这时已经合并了，所以最后导出的文件在打开时会有问题，需要修复。所以这里要求，如果指定了合并哪些单元格，那就执行一次merge方法，我这里是因为我已经有了合并策略的map，知道需要合并哪些单元格，所以让merge方法只执行一次，那就让rowIndex=1，columnIndex=1，执行这一次的时候，就告诉excel对象合并上面map那样的要求，所以所有的单元格都被操作了一次，最后的结果也是正确的合并了。再次记录一下。

public class DetectionMergeStrategy extends AbstractMergeStrategy {
	
	    private final int[] totalLength;//检测方数据条数小计行数
	    private Map<String, List<RowRangeDto>> strategyMap; //需要合并行的坐标

	    public DetectionMergeStrategy(int[] totalLength, Map<String, List<RowRangeDto>> strategyMap) {
	        this.totalLength = totalLength;
	        this.strategyMap = strategyMap;
	    }

	@Override
	protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
		// 当前行
		int curRowIndex = cell.getRowIndex();
		// 当前列
		int curColIndex = cell.getColumnIndex();

		if (curRowIndex == 4 && curColIndex == 0) {
			/**
			 * 保证每个cell被合并一次，如果不加上面的判断，因为是一个cell一个cell操作的，
			 * 例如合并A2:A3,当cell为A2时，合并A2,A3，但是当cell为A3时，又是合并A2,A3， 但此时A2,A3已经是合并的单元格了
			 * ，curRowIndex == 4是因为表头占了前四行
			 */
			for (Map.Entry<String, List<RowRangeDto>> entry : strategyMap.entrySet()) {
				Integer columnIndex = Integer.valueOf(entry.getKey());
				entry.getValue().forEach(rowRange -> {
					// 添加一个合并请求
					sheet.addMergedRegionUnsafe(new CellRangeAddress(curRowIndex + rowRange.getStart() - 1,
							curRowIndex + rowRange.getEnd() - 1, columnIndex, columnIndex));
				});
			}
		}

		// 由于每个单元格单元格都会调用该方法，为了避免重复合并异常，只在应合并的行、列执行即可
		if (curColIndex == 0 || curColIndex == 3) {// 列合并
			int index = -1;
			for (int i=0; i< totalLength.length; i++) {
				int entry = totalLength[i];
				index = index + entry+1;//加1是因为合并的费用小计行
				if (relativeRowIndex == index) {// || (relativeRowIndex - first) % TFByLength == 0
					sheet.addMergedRegionUnsafe(
							new CellRangeAddress(curRowIndex, curRowIndex, curColIndex, curColIndex + 1));
				}
			}
			
		}

	}	    		   
}

3.5 DetectionSheetWriteHandler 复杂表头样式整理

这个类继承SheetWriteHandler 抽象类，实现afterSheetCreate方法，进行自定义表头策略，传入自定义的表头信息，及自定义样式。

public class DetectionSheetWriteHandler implements SheetWriteHandler {
	
	private String dataTime;
    public DetectionSheetWriteHandler(){}
    public DetectionSheetWriteHandler(String dataTime){
        this.dataTime = dataTime;
    }
    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        Sheet sheet = workbook.getSheetAt(0);
        //设置第一行标题
        Row row1 = sheet.createRow(1);
        row1.setHeight((short) 800);
        Cell row1Cell1 = row1.createCell(0);
        row1Cell1.setCellValue(" 统 计 表");
        CellStyle row1CellStyle = workbook.createCellStyle();
        row1CellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        row1CellStyle.setAlignment(HorizontalAlignment.CENTER);
        Font row1Font = workbook.createFont();
        row1Font.setBold(true);
        row1Font.setFontName("宋体");
        row1Font.setFontHeightInPoints((short) 18);
        row1CellStyle.setFont(row1Font);
        row1Cell1.setCellStyle(row1CellStyle);
        //合并单元格，起始行,结束行,起始列,结束列
        sheet.addMergedRegionUnsafe(new CellRangeAddress(1, 1, 0, 5));
//        sheet.addMergedRegionUnsafe(new CellRangeAddress(1, 1, 22, 23));

//        设置第二行标题
        Row row2 = sheet.createRow(2);
        row2.setHeight((short) 400);
        Cell row2Cell1 = row2.createCell(0);
        row2Cell1.setCellValue("时间范围："+ dataTime);
        CellStyle row2CellStyle = workbook.createCellStyle();
        row2CellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        row2CellStyle.setAlignment(HorizontalAlignment.RIGHT);
        Font row2Font = workbook.createFont();
        row2Font.setFontName("宋体");
        row2Font.setFontHeightInPoints((short) 10);
        row2CellStyle.setFont(row2Font);
        row2Cell1.setCellStyle(row2CellStyle);
        sheet.addMergedRegionUnsafe(new CellRangeAddress(2, 2, 0, 5));

    }
}

3.6 DetectionCellStyleStrategy 类 自定义excel内容的样式

public class DetectionCellStyleStrategy {

	/**
	 * 导出excel时的样式配置
	 * 
	 * @param headFont
	 *            contentFont字体大小
	 * @return
	 */
	public static HorizontalCellStyleStrategy getStyleStrategy(short headFont, short contentFont) {
		// 头的策略
		WriteCellStyle headWriteCellStyle = new WriteCellStyle();
		// 背景设置为灰色
		// headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
		WriteFont headWriteFont = new WriteFont();
		headWriteFont.setFontHeightInPoints(headFont);
		// 字体样式
		headWriteFont.setFontName("宋体");
		headWriteCellStyle.setWriteFont(headWriteFont);
		// 自动换行
		headWriteCellStyle.setWrapped(true);
		// 水平对齐方式
		headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
		// 垂直对齐方式
		headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		headWriteCellStyle.setBorderLeft(BorderStyle.THIN);// 左边框
		headWriteCellStyle.setBorderTop(BorderStyle.THIN);// 上边框
		headWriteCellStyle.setBorderRight(BorderStyle.THIN);// 右边框
		headWriteCellStyle.setBorderBottom(BorderStyle.THIN);// 下边框

		// 内容的策略
		WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
		// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了
		// FillPatternType所以可以不指定
		// contentWriteCellStyle.setFillPatternType(FillPatternType.SQUARES);
		// 背景白色
		contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());

		// 字体策略
		WriteFont contentWriteFont = new WriteFont();
		// 字体大小
		contentWriteFont.setFontHeightInPoints(contentFont);
		// 字体样式
		contentWriteFont.setFontName("宋体");
		contentWriteCellStyle.setWriteFont(contentWriteFont);
		// 自动换行
		contentWriteCellStyle.setWrapped(true);
		// 水平对齐方式
		contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
		// 垂直对齐方式
		contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
		contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
		contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
		contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
		// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
		return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
	}
}

3.7.1 CheckDetectionManagementExcel 这个实体类是映射最后文档的head信息的。

@Data
@ColumnWidth(13)
@ContentRowHeight(30)
//@ContentFontStyle(fontHeightInPoints = 11)
public class CheckDetectionManagementExcel {
    /* *
     * 检测方
     */
    @ColumnWidth(15)
    @ExcelProperty({"检测方"})
    private String checkedBy;
    
    /**
     * 检测名称
     */
    @ColumnWidth(25)
    @ExcelProperty({"检测名称"})
    private String detectionName;

    /* *
     * 检测费用
     */
    @ColumnWidth(25)
    @ExcelProperty({"检测费用"})
    private String detectionAmount;
    
    /**
     * 设备部门
     */
    @ColumnWidth(25)
    @ExcelProperty({"设备部门"})
    private String orgName;
    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    private String orgCode;
    

    /**
     * 检测设备类型
     */
    @ColumnWidth(25)
    @ExcelProperty({"检测设备类型"})
    private String equipmentTypeName;

    /**
     * 部门小计
     */
    @ColumnWidth(25)
    @ExcelProperty({"部门小计(台)"})
    private String subtotal;

    public CheckDetectionManagementExcel() {
    
    } 
	public CheckDetectionManagementExcel(String checkedBy, String detectionName, String detectionAmount, String orgName,
			String equipmentTypeName, String subtotal) {
		super();
		this.checkedBy = checkedBy;
		this.detectionName = detectionName;
		this.detectionAmount = detectionAmount;
		this.orgName = orgName;
		this.equipmentTypeName = equipmentTypeName;
		this.subtotal = subtotal;
	}
}

3.7.2 RowRangeDto代码

这个实体类是分段的起始位置DTO。

/**
 * 这个实体类是分段的起始位置DTO
 * 
 * @author ctvit
 *
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class RowRangeDto implements Serializable {

	//合并起始坐标
	private int start;
	//合并结束坐标
	private int end;
}

3.8 效果图

还差一点样式，继续调试，数据动态合并已成立