EasyExcel列表填充数据时指定单元格合并
package com.susing.security.handler;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.susing.common.utils.RowRangeDto;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.List;
import java.util.Map;
/**
* easyexcel 拦截器, 拦截设置相同的类别合并单元格
* @Author
* @Date 2021/11/30 22:31
* @description
*/
public class BizMergeStrategy extends AbstractMergeStrategy {
private Map<String, List<RowRangeDto>> strategyMap;
private Sheet sheet;
public BizMergeStrategy(Map<String, List<RowRangeDto>> strategyMap) {
this.strategyMap = strategyMap;
}
@Override
protected void merge(org.apache.poi.ss.usermodel.Sheet sheet, Cell cell, Head head, Integer integer) {
this.sheet = sheet;
if (cell.getRowIndex() == 2 && cell.getColumnIndex() == 0) {
/**
* 保证每个cell被合并一次,如果不加上面的判断,因为是一个cell一个cell操作的,
* 例如合并A2:A3,当cell为A2时,合并A2,A3,但是当cell为A3时,又是合并A2,A3,
* 但此时A2,A3已经是合并的单元格了
*/
for (Map.Entry<String, List<RowRangeDto>> entry : strategyMap.entrySet()) {
Integer columnIndex = Integer.valueOf(entry.getKey());
entry.getValue().forEach(rowRange -> {
//添加一个合并请求
sheet.addMergedRegionUnsafe(new CellRangeAddress(rowRange.getStart(),
rowRange.getEnd(), columnIndex, columnIndex));
});
}
}
}
}
ExcelUtil 方法
package com.susing.common.utils;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @Author
* @Date 2021/11/30 22:57
* @description
*/
public class ExcelUtil {
/**
* 添加合并策略
*/
public static Map<String, List<RowRangeDto>> addMerStrategy(List<Map<String, Object>> excelDtoList) {
Map<String, List<RowRangeDto>> strategyMap = new HashMap<>();
Map<String, Object> preExcelDto = null;
for (int i = 0; i < excelDtoList.size(); i++) {
Map<String, Object> currDto = excelDtoList.get(i);
if (preExcelDto != null) {
//从第二行开始判断是否需要合并
if (currDto.get("categoryName").equals(preExcelDto.get("categoryName"))) {
//如果类别一样,则可合并单位格为一列
fillStrategyMap(strategyMap, "0", i+1);
}
}
preExcelDto = currDto;
}
return strategyMap;
}
/**
* 新增或修改合并策略
**/
private static 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);
}
}
package com.susing.common.utils;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
/**
* @Author dfp
* @Date 2021/11/30 22:57
* @description
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class RowRangeDto {
private int start;
private int end;
}
引用
Map<String, List<RowRangeDto>> strategyMap = ExcelUtil.addMerStrategy(list);
WriteSheet writeSheet = EasyExcel.writerSheet("统计表")
.registerWriteHandler(new BizMergeStrategy(strategyMap)).build();
完结
…∧_∧ ∧_∧
( ゚ ー ゚) (´・ω・`)
( ∪ ∪ )
と__))((__つ