图一是原始数据,图二是期望导出的表格,班级相同的cell合并,在班级相同的前提下,地区相同的cell合并。
1. 第一步加依赖 ~
<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.1.6</version> </dependency>
2.接收查询数据的dto就不说了,我新建了个导出excel的dto
@ExcelProperty的value可以只传一个字符串,也可以传字符串数组,传数组的话效果就是上面图二所展示的,表头有多行的样式。
@Data
public class ExcelDto implements Serializable {
@ExcelProperty(value = "班级", index = 0)
private String classNo;
@ExcelProperty(value = "学生编号", index = 1)
private Integer id;
@ExcelProperty(value = {"姓名"}, index = 2)
private String name;
@ExcelProperty(value = {"学科","语文"}, index = 3)
private String ywScore;
@ExcelProperty(value = {"学科","数学"}, index = 4)
private String mathScore;
@ExcelProperty(value = {"学科","英语"}, index = 5)
private String engScore;
@ExcelProperty(value = {"学科","地区"}, index = 6)
private String area;
}
3 .新建合并策略
if 判断的这一行,由于我的表头是两行,所以实际数据是从表格的第三行开始展示的,也就是cell的行索引为2(cell.getRowIndex() == 2),开发时根据实际情况判断。
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(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(rowRangeDto -> {
//添加一个合并请求
sheet.addMergedRegionUnsafe(new CellRangeAddress(rowRangeDto.getStart(), rowRangeDto.getEnd(), columnIndex, columnIndex));
});
}
}
}
}
4. ExcelUtil代码
调用 fillStrategyMap时,传的"0"和“6”是需要合并的列,也就是学生班级和地区 对应的索引,i+1也是因为我需要导出的表格的表头有两行才+1,开发时也需要根据实际情况判断
public class ExcelUtil {
//添加合并策略
public static Map<String, List<RowRangeDto>> addMerStrategy(List<ExcelDto> excelDtoList) {
Map<String, List<RowRangeDto>> strategyMap = new HashMap<>();
ExcelDto preExcelDto = null;
for (int i = 0; i < excelDtoList.size(); i++) {
ExcelDto currExcelDto = excelDtoList.get(i);
if (preExcelDto != null) {
//从数据第二行开始判断是否需要合并
if (StringUtils.equals(currExcelDto.getClassNo(), preExcelDto.getClassNo())){
//班级一样,则合并
fillStrategyMap(strategyMap, "0", i + 1);
//班级一样时,如果地区一样,也需要合并
if (StringUtils.equals(currExcelDto.getArea(), preExcelDto.getArea())) {
fillStrategyMap(strategyMap, "6", i + 1);
}
}
}
preExcelDto = currExcelDto;
}
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 rowRangeDto : rowRangeDtoList) {
//分段list中是否有end索引是上一行索引的,如果有,则索引+1
if (rowRangeDto.getEnd() == index) {
rowRangeDto.setEnd(index + 1);
flag = true;
}
}
//如果没有,则新增分段
if (!flag) {
rowRangeDtoList.add(new RowRangeDto(index, index + 1));
}
strategyMap.put(key, rowRangeDtoList);
}
}
记录每段合并数据起止索引的对象
@Data
@NoArgsConstructor
@AllArgsConstructor
public class RowRangeDto {
private int start;
private int end;
}
根据合并策略得到的map是这样的:
5. 测试代码
@RequestMapping(value = "/exportExcle", method = RequestMethod.GET)
public void exportExcle(HttpServletResponse response) throws IOException {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = "test";
response.setHeader("Content-disposition", "attachment;fileName=" + fileName + ".xlsx");
//获取测试数据
List<StudentScoreDto> studentScoreDtos = studentMapper.selectAll();
List<ExcelDto> datas = new ArrayList<>();
studentScoreDtos.forEach(studentScoreDto -> {
ExcelDto excelDto = new ExcelDto();
BeanUtils.copyProperties(studentScoreDto, excelDto);
datas.add(excelDto);
});
Map<String, List<RowRangeDto>> strategyMap = ExcelUtil.addMerStrategy(datas);
EasyExcel.write(response.getOutputStream(), ExcelDto.class).registerWriteHandler(new BizMergeStrategy(strategyMap)).sheet("Sheet1").doWrite(datas);
}
这样就阔以了~
原教程讲得更详细,我根据我的需求做了一丢丢改变