业务背景:要求同列的不同行的相同数据进行合并,部分列需要依据code进行比对然后进行合并,主要的技术栈使用EasyExcel进行合并,先把最小维度的每条数据查询出来,然后对这些数据按着合并策略进行合并。
1、先查询出所有符合要求的数据,需要把输出list放到最小单元那一层,保证每一条数据都要有数据。
public List<RiskIdentifyDatoExportToDataExcelDto> getRiskIdentifyList(UserInfo userInfo, Long id) {log.info("辨识风险导出数据,查询id:{}",id);List<RiskIdentifyDatoExportToDataExcelDto> resultList=new ArrayList<>();CtuRiskIdentify ctuRiskIdentify = ctuRiskIdentifyReadMapper.selectById(id);CtuDangerOrigin dangerOrigin = ctuDangerOriginReadMapper.selectByRiskKey(ctuRiskIdentify.getRiskKey());String dangerOriKey = dangerOrigin.getDangerOriKey();//分析对象和业态AnalysisObject analysisObject = analysisObjectReadMapper.selectById(ctuRiskIdentify.getAnalysisObjId());Business business = businessReadMapper.selectById(ctuRiskIdentify.getBusinessId());CtuAnalysisCategory analysisCategory = ctuAnalysisCategoryReadMapper.selectById(ctuRiskIdentify.getAnalysisCategoryId());//找辨识最小单元管控措施List<CtuDangerCase> ctuDangerCases = ctuDangerCaseReadMapper.selectByDangerKey(dangerOriKey);//风险等级 需评价完成后List<String> riskKeys=new ArrayList<>();riskKeys.add(ctuRiskIdentify.getRiskKey());List<AssessRiskLevelDTO> riskLevelDTOS = assessServiceHandler.queryDangerRiskLevel(riskKeys);//风险单元String riskUnitCode = ctuRiskIdentify.getRiskUnitCode();List<String> riskUnitCodes=new ArrayList<>(1);riskUnitCodes.add(riskUnitCode);List<RiskUnitLevelDto> diffLevelRiskUnitList = diffLevelRiskUnitService.getDiffLevelRiskUnit(riskUnitCodes).getData();for(CtuDangerCase dangerCase:ctuDangerCases){//查询危险致因管控措施List<CtuDangerCaseMethod> dangerCaseMethods=ctuDangerCaseMethodReadMapper.selectByCaseCode(dangerCase.getDangerCaseCode());if(!CollectionUtils.isEmpty(dangerCaseMethods)){for(CtuDangerCaseMethod dangerCaseMethod:dangerCaseMethods){RiskIdentifyDatoExportToDataExcelDto item=new RiskIdentifyDatoExportToDataExcelDto();item.setAnalysisObjId(ctuRiskIdentify.getAnalysisObjId());item.setControlMethod(dangerCaseMethod.getControlMethod());item.setDangerCaseCode(dangerCase.getDangerCaseCode());item.setDangerName(dangerOrigin.getDangerName());item.setDangerOriKey(dangerOriKey);item.setCreator(ctuRiskIdentify.getCreator());item.setControlDept(dangerCase.getOrgDep());item.setIfClock(dangerCase.getIfClock()==1?"是":"否");item.setDangerSourceCause(dangerCase.getDangerCaseName());item.setDangerSourceType(dangerOrigin.getDangerName());if(dangerCase.getIfClock()==1){DictionaryInfo dictionaryInfo = dics.get(dangerCase.getControlRate());if(Objects.isNull(dictionaryInfo)){DictionaryInfo dbDic = dictionaryInfoReadMapper.selectById(dangerCase.getControlRate());if(Objects.nonNull(dbDic)){dics.put(Ints.tryParse(dangerCase.getControlRate()),dbDic);BeanUtils.copyProperties(dbDic,dictionaryInfo);}}item.setControlRate(dictionaryInfo.getSelectValue());String startEndTime=dangerCase.getControlStartTime()+"——"+dangerCase.getControlEndTime();item.setControlStartEndTime(startEndTime);}item.setControlType(dangerCase.getControlType()==1?"人工巡查":"物联智巡");//查询危险致因管控责任人QueryWrapper<CtuControlPerson> queryWrapperPerson = new QueryWrapper<>();queryWrapperPerson.eq("case_id",dangerCaseMethod.getDangerCaseId());queryWrapperPerson.eq("case_type",1);List<CtuControlPerson> ctuControlPeoples = ctuControlPersonReadMapper.selectList(queryWrapperPerson);if(!CollectionUtils.isEmpty(ctuControlPeoples)){List<String> controlPeoples = ctuControlPeoples.stream().map(CtuControlPerson::getControlPerson).collect(Collectors.toList());item.setControlPersons(String.join(",", controlPeoples));}if(Objects.nonNull(analysisObject)){item.setAnaObjName(analysisObject.getAnaObjName());}if(Objects.nonNull(business)){item.setBusinessName(business.getBusinessName());}if(Objects.nonNull(analysisCategory)){item.setAnalysisCategoryName(analysisCategory.getAnaCategoryName());}String createTime = DateFormatUtils.format(ctuRiskIdentify.getCreateTime(), "yyyy-MM-dd HH:mm:ss");item.setCreateTime(createTime);if(!CollectionUtils.isEmpty(riskLevelDTOS)){AssessRiskLevelDTO assessRiskLevelDTO = riskLevelDTOS.get(0);item.setRiskLevel(assessRiskLevelDTO.getRiskLevel());}setRiskUnitName(diffLevelRiskUnitList,item);resultList.add(item);}}}
2、合并策略
@Slf4j
public class HierarchicalMergeStrategy implements RowWriteHandler{private final List<RiskIdentifyDatoExportToDataExcelDto> dataList;public HierarchicalMergeStrategy(List<RiskIdentifyDatoExportToDataExcelDto> dataList) {this.dataList = dataList;}@Overridepublic void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer rowIndex, Boolean isHead) {// 排除表头行if (isHead || rowIndex == 0) {return;}Sheet sheet = writeSheetHolder.getSheet();RiskIdentifyDatoExportToDataExcelDto currentData = dataList.get(rowIndex);RiskIdentifyDatoExportToDataExcelDto previousData = dataList.get(rowIndex - 1);// 对不同列的层次进行合并mergeColumns(sheet, rowIndex, currentData, previousData);}private void mergeColumns(Sheet sheet, int rowIndex, RiskIdentifyDatoExportToDataExcelDto currentData, RiskIdentifyDatoExportToDataExcelDto previousData) {//危险致因相同 合并if (StringUtils.isNotBlank(currentData.getDangerCaseCode()) && currentData.getDangerCaseCode().equals(previousData.getDangerCaseCode())) {mergeCells(sheet, rowIndex, 13,currentData.getDangerSourceCause());}//事故成因相同 合并if (StringUtils.isNotBlank(currentData.getAccidCaseCode()) && currentData.getAccidCaseCode().equals(previousData.getAccidCaseCode())) {mergeCells(sheet, rowIndex, 13,currentData.getDangerSourceCause());}// 危险源描述相同 合并if (StringUtils.isNotBlank(currentData.getDangerCaseCode()) && currentData.getDangerCaseCode().equals(previousData.getDangerCaseCode())&¤tData.getDangerOriKey().equals(previousData.getDangerOriKey())) {mergeCells(sheet, rowIndex, 12,currentData.getDangerSourceType());}//事故相同 合并if (StringUtils.isNotBlank(currentData.getAccidCaseCode()) && currentData.getAccidCaseCode().equals(previousData.getAccidCaseCode())&& currentData.getAccidentTypeCode().equals(previousData.getAccidentTypeCode())) {mergeCells(sheet, rowIndex, 12,currentData.getDangerSourceType());}//危险源合并if(StringUtils.isNotBlank(currentData.getDangerOriKey()) && currentData.getDangerOriKey().equals(previousData.getDangerOriKey())){mergeCells(sheet, rowIndex, 10,currentData.getDangerName());}//分析对象合并if(Objects.nonNull(currentData.getAnalysisObjId()) && currentData.getAnalysisObjId().equals(previousData.getAnalysisObjId())){mergeCells(sheet, rowIndex, 9,currentData.getAnaObjName());}//风险单元合并if(StringUtils.isNotBlank(currentData.getOneUnitName())&¤tData.getOneUnitName().equals(previousData.getOneUnitName())){mergeCells(sheet, rowIndex, 2,currentData.getOneUnitName());}if(StringUtils.isNotBlank(currentData.getTwoUnitName())&¤tData.getTwoUnitName().equals(previousData.getTwoUnitName())){mergeCells(sheet, rowIndex, 3,currentData.getTwoUnitName());}if(StringUtils.isNotBlank(currentData.getThreeUnitName())&¤tData.getThreeUnitName().equals(previousData.getThreeUnitName())){mergeCells(sheet, rowIndex, 4,currentData.getThreeUnitName());}if(StringUtils.isNotBlank(currentData.getFourUnitName())&¤tData.getFourUnitName().equals(previousData.getFourUnitName())){mergeCells(sheet, rowIndex, 5,currentData.getFourUnitName());}if(StringUtils.isNotBlank(currentData.getFiveUnitName())&¤tData.getFiveUnitName().equals(previousData.getFiveUnitName())){mergeCells(sheet, rowIndex, 6,currentData.getFiveUnitName());}//分析对象类型和业态if(StringUtils.isNotBlank(currentData.getAnalysisCategoryName())&¤tData.getAnalysisCategoryName().equals(previousData.getAnalysisCategoryName())){mergeCells(sheet, rowIndex, 7,currentData.getAnalysisCategoryName());}if(StringUtils.isNotBlank(currentData.getBusinessName())&¤tData.getBusinessName().equals(previousData.getBusinessName())){mergeCells(sheet, rowIndex, 8,currentData.getBusinessName());}//上报时间上报人if(StringUtils.isNotBlank(currentData.getCreator())&¤tData.getCreator().equals(previousData.getCreator())){mergeCells(sheet, rowIndex, 0,currentData.getCreator());}if(StringUtils.isNotBlank(currentData.getCreateTime())&¤tData.getCreateTime().equals(previousData.getCreateTime())){mergeCells(sheet, rowIndex, 1,currentData.getCreateTime());}//风险等级if(StringUtils.isNotBlank(currentData.getRiskLevel()) && currentData.getRiskLevel().equals(previousData.getRiskLevel())){mergeCells(sheet, rowIndex, 11,currentData.getRiskLevel());}}private void mergeCells(Sheet sheet, int rowIndex, int columnIndex, String cellStringValue) {log.info("合并单元格内容,行数:{},列数:{},单元格内容:{}",rowIndex,columnIndex,cellStringValue);// 获取之前的合并区域并继续合并// 先检查是否已经有合并的区域int mergedRegionsCount = sheet.getNumMergedRegions();if (mergedRegionsCount > 0) {// 遍历已经存在的合并区域for (int i = 0; i < mergedRegionsCount; i++) {CellRangeAddress mergedRegion = sheet.getMergedRegion(i);if (mergedRegion.getFirstColumn() == columnIndex && mergedRegion.getLastColumn() == columnIndex) {// 如果当前行已经在某个合并区域内,扩展这个合并区域if ((rowIndex >= mergedRegion.getFirstRow() && rowIndex <= mergedRegion.getLastRow()) ||(rowIndex - 1 >= mergedRegion.getFirstRow() && rowIndex - 1 <= mergedRegion.getLastRow())) {if(cellStringValue !=null && cellStringValue.equals(sheet.getRow(rowIndex-1).getCell(columnIndex).getStringCellValue())){sheet.removeMergedRegion(i);sheet.addMergedRegion(new CellRangeAddress(mergedRegion.getFirstRow(), rowIndex+1, columnIndex, columnIndex));return;}else{sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex+1, columnIndex, columnIndex));return;}}}}}// 如果没有找到现有的合并区域,则创建新的合并区域sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex+1, columnIndex, columnIndex));}
}