public static void writeExcelWithHeadAndData(OutputStream outputStream, List> head, List> body, ExcelTypeEnum excelTypeEnum) {
/* ExcelWriter writer = new ExcelWriter(outputStream, excelTypeEnum,true);
Sheet sheet1 = new Sheet(1, 0);
//手动写表，赋予表头内容和数据
Table table = new Table(1);
table.setHead(head);
writer.write0(body, sheet1, table);
writer.finish();*/
Map> stringListMap = addMerStrategy(body);
// 文件流会自动关闭
EasyExcel.write(outputStream)
.registerWriteHandler(new BizMergeStrategy(stringListMap))
.excelType(excelTypeEnum).
head(head).sheet(1,"运维周报").doWrite(body);
}
public static Map> addMerStrategy(List> excelDtoList) {
Map> strategyMap = new HashMap<>();
List preExcelDto = null;
for (int i = 0; i < excelDtoList.size(); i++) {
List currDto = excelDtoList.get(i);
if (preExcelDto != null) {
//从第二行开始判断是否需要合并
if (currDto.get(0).equals(preExcelDto.get(0))) {
//如果业务线一样，则可合并业务一列
fillStrategyMap(strategyMap, "0", i);
//如果业务线一样，并且模块一样，则可合并模块一列
if (currDto.get(1).equals(preExcelDto.get(1))) {
fillStrategyMap(strategyMap, "1", i);
}
}
}
preExcelDto = currDto;
}
return strategyMap;
}
private static void fillStrategyMap(Map> strategyMap, String key, int index) {
List 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);
}
/*
自定义合并单元格策略
*/
public class BizMergeStrategy extends AbstractMergeStrategy{
private Map> strategyMap;
private Sheet sheet;
public BizMergeStrategy(Map> 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() == 1 && cell.getColumnIndex() == 0) {
/**
* 保证每个cell被合并一次，如果不加上面的判断，因为是一个cell一个cell操作的，
* 例如合并A2:A3,当cell为A2时，合并A2,A3，但是当cell为A3时，又是合并A2,A3，
* 但此时A2,A3已经是合并的单元格了
*/
for (Map.Entry> entry : strategyMap.entrySet()) {
Integer columnIndex = Integer.valueOf(entry.getKey());
entry.getValue().forEach(rowRange -> {
// 添加一个合并请求
sheet.addMergedRegionUnsafe(new CellRangeAddress(rowRange.getStart(),
rowRange.getEnd(), columnIndex, columnIndex));
});
}
}
}
}
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class RowRangeDto {
private int start;
private int end;
}
