业务背景:要求同列的不同行的相同数据进行合并,部分列需要依据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;
}
@Override
public 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())&&
currentData.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));
}
}