需求:导出排班明细表
现状:目前只能导出单行排班明细
目标:合并相同院区及辖区单元格
easyExcel导出字段定义类
package com.bozhong.schedule.dto.export.response; import com.alibaba.excel.annotation.ExcelProperty; import lombok.Getter; import lombok.Setter; import java.io.Serializable; /** * 导出科室排班数据明细respDTO **/ @Getter @Setter public class ExportDeptScheduleDataDetailRespDTO implements Serializable { @ExcelProperty(value = "院区", index = 0) private String hospitalName; @ExcelProperty(value = "辖区", index = 1) private String parentDeptName; @ExcelProperty(value = "护理单元", index = 2) private String deptName; @ExcelProperty(value = "白班(个)", index = 3) private Double dayShiftNum; @ExcelProperty(value = "夜班(个)", index = 4) private Double nightShiftNum; @ExcelProperty(value = "请假(个)", index = 5) private Double leaveNum; @ExcelProperty(value = "休息(个)", index = 6) private Double vacationNum; @ExcelProperty(value = "其他(个)", index = 7) private Double otherNum; @ExcelProperty(value = "白班工时(h)", index = 8) private Double dayShiftWorkHours; @ExcelProperty(value = "夜班工时(h)", index = 9) private Double nightShiftWorkHours; @ExcelProperty(value = "加扣班时长(h)", index = 10) private Double overWorkHours; @ExcelProperty(value = "总工时(h)", index = 11) private Double totalWorkHours; @ExcelProperty(value = "人均日工时(h)", index = 12) private Double perCapitaWorkHours; @ExcelProperty(value = "请假天数(天)", index = 13) private Double leaveDays; @ExcelProperty(value = "护理时数(h)", index = 14) private Double nursingHours; @ExcelProperty(value = "剩余年假(天)", index = 15) private Double annualLeave; }
easyExcel行合并策略类
package com.bozhong.schedule.common.utils.excel; import com.alibaba.excel.metadata.Head; import com.alibaba.excel.util.CollectionUtils; import com.alibaba.excel.write.merge.AbstractMergeStrategy; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.util.CellRangeAddress; import java.util.ArrayList; import java.util.List; // 连续多行重复数据合并策略 该类继承了AbstractMergeStrategy抽象合并策略,需要重写merge()方法 public class RowsGroupMergeStrategy extends AbstractMergeStrategy { /** * 分组,每几行合并一次 */ private List<Integer> exportFieldGroupCountList; /** * 目标合并列index */ private Integer targetColumnIndex; // 需要开始合并单元格的首行index private Integer rowIndex; // exportDataList 为待合并目标列的值 // targetColumnIndex 为合并目标列下标 0 表示合并第一列 1 表示合并第二列 public RowsGroupMergeStrategy(List<String> exportDataList, Integer targetColumnIndex) { this.exportFieldGroupCountList = getGroupCountList(exportDataList); this.targetColumnIndex = targetColumnIndex; } @Override protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) { if (null == rowIndex) { rowIndex = cell.getRowIndex(); } // 仅从首行以及目标列的单元格开始合并,忽略其他 if (cell.getRowIndex() == rowIndex && cell.getColumnIndex() == targetColumnIndex) { mergeGroupColumn(sheet); } } private void mergeGroupColumn(Sheet sheet) { int rowCount = rowIndex; for (Integer count : exportFieldGroupCountList) { if(count == 1) { rowCount += count; continue ; } // 合并单元格 CellRangeAddress cellRangeAddress = new CellRangeAddress(rowCount, rowCount + count - 1, targetColumnIndex, targetColumnIndex); sheet.addMergedRegionUnsafe(cellRangeAddress); rowCount += count; } } // 该方法将目标列根据值是否相同连续可合并,存储可合并的行数 private List<Integer> getGroupCountList(List<String> exportDataList){ if (CollectionUtils.isEmpty(exportDataList)) { return new ArrayList<>(); } List<Integer> groupCountList = new ArrayList<>(); int count = 1; for (int i = 1; i < exportDataList.size(); i++) { if (exportDataList.get(i).equals(exportDataList.get(i - 1))) { count++; } else { groupCountList.add(count); count = 1; } } // 处理完最后一条后 groupCountList.add(count); return groupCountList; } }
ExcelUtils工具类封装:
/**
* 返回文件流
*
* @param list 数据 list,每个元素为一个 BaseRowModel
* @param sheetName 导入文件的 sheet 名
* @param clazz 映射实体类,Excel 模型
* @param writeHandlers 处理策略类
*/
public static <T> byte[] writeExcelReturnBytesWithHandler(List<T> list, String sheetName, Class<T> clazz, WriteHandler... writeHandlers) {
ByteArrayOutputStream out = new ByteArrayOutputStream();
ExcelWriter excelWriter = null;
try {
ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(out, clazz)
.registerWriteHandler(ExcelUtils.getStyleStrategy());
for (WriteHandler writeHandler : writeHandlers) {
excelWriterBuilder.registerWriteHandler(writeHandler);
}
excelWriter = excelWriterBuilder.build();
WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
excelWriter.write(list,writeSheet);
} finally {
// 千万别忘记finish 会帮忙关闭流
if (excelWriter != null) {
excelWriter.finish();
}
}
return out.toByteArray();
}
其中,excelWriterBuilder.registerWriteHandler(writeHandler);为关键注册策略处理代码
使用方式:导出为流
byte[] buffer = ExcelUtils.writeExcelReturnBytesWithHandler(result, name, ExportDeptScheduleDataDetailRespDTO.class,
new RowsGroupMergeStrategy(result.stream().map(ExportDeptScheduleDataDetailRespDTO::getHospitalName).collect(Collectors.toList()), 0),
new RowsGroupMergeStrategy(result.stream().map(ExportDeptScheduleDataDetailRespDTO::getParentDeptName).collect(Collectors.toList()), 1));
其中,
new RowsGroupMergeStrategy(result.stream().map(ExportDeptScheduleDataDetailRespDTO::getHospitalName).collect(Collectors.toList()), 0)
为关键策略类代码,构造方法第一个参数为需要分组合并的列,第二个参数为该列的下标以上代表院区第一列相同列进行合并