8
当 复杂头写入 和 合并单元格 同时使用的情况下，复杂表头失效，变成一个单元格。 异常代码
//方法一
// 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String tempFileName = URLEncoder.encode(fileName, "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + tempFileName + ".xlsx");
Object d = redisUtil.get("sxschool:study:cache:time:" + groupId);
String date = d == null ? DateUtil.now() : d.toString();
List<UserStudyrecordsResponse> userStudyrecordsResponseList = userService.exportUserStudyRecord(groupId);
//合并策略map
Map<String, List<RowRangeVo>> strategyMap = ExcelUtil.addMerStrategy(userStudyrecordsResponseList);
EasyExcel.write(response.getOutputStream(), UserStudyrecordsResponse.class)
.registerWriteHandler(new ExportUserStudyRecordHandler(date))
.registerWriteHandler(new BizMergeStrategy(strategyMap))
.sheet("sheet1").doWrite(userStudyrecordsResponseList);
//ExportUserStudyRecordHandler 类
public class ExportUserStudyRecordHandler implements CellWriteHandler {
private static final Logger LOGGER = LoggerFactory.getLogger(ExportUserStudyRecordHandler.class);
private String date;
public ExportUserStudyRecordHandler(String date) {
this.date = date;
}
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
Head head, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
// 这里可以对cell进行任何操作
//LOGGER.info("第{}行，第{}列写入完成。", cell.getRowIndex(), cell.getColumnIndex());
if (isHead && cell.getColumnIndex() == 0 && cell.getRowIndex() == 0) {
// CreationHelper createHelper = writeSheetHolder.getSheet().getWorkbook().getCreationHelper();
// Hyperlink hyperlink = createHelper.createHyperlink(HyperlinkType.URL);
// hyperlink.setAddress("https://github.com/alibaba/easyexcel");
cell.setCellValue("数据刷新时间:" + this.date);
}
}
}
///////////////////////////////////////////////////////////////////////////////////////
//BizMergeStrategy 类
public class BizMergeStrategy extends AbstractMergeStrategy {
private Map<String, List<RowRangeVo>> strategyMap;
private Sheet sheet;
public BizMergeStrategy(Map<String, List<RowRangeVo>> 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<String, List<RowRangeVo>> entry : strategyMap.entrySet()) {
Integer columnIndex = Integer.valueOf(entry.getKey());
entry.getValue().forEach(rowRange -> {
//添加一个合并请求
sheet.addMergedRegionUnsafe(new CellRangeAddress(rowRange.getStart(),
rowRange.getEnd(), columnIndex, columnIndex));
});
}
}
}
}
////////////////////////////////////////////////////////////////////////////////////////
//ExcelUtil 类
public class ExcelUtil {
/**
* @Author: TheBigBlue
* @Description: 添加合并策略
* @Date: 2020/3/16
* @Param:
* @return:
**/
public static Map<String, List<RowRangeVo>> addMerStrategy(List<UserStudyrecordsResponse> excelDtoList) {
Map<String, List<RowRangeVo>> strategyMap = new HashMap<>();
UserStudyrecordsResponse preExcelDto = null;
for (int i = 0; i < excelDtoList.size(); i++) {
UserStudyrecordsResponse currDto = excelDtoList.get(i);
if (preExcelDto != null) {
//从第二行开始判断是否需要合并
if (currDto.getUserName().equals(preExcelDto.getUserName())) {
//如果用户名一样，则可合并用户名一列
fillStrategyMap(strategyMap, "0", i + 1);
//如果用户名一样，并且账号一样，则可合并账号一列
if (currDto.getMobile().equals(preExcelDto.getMobile())) {
fillStrategyMap(strategyMap, "1", i + 1);
//如果用户名、账号一样，并且用户组也一样，则可合并用户组一列
if (currDto.getGroupName().equals(preExcelDto.getGroupName())) {
fillStrategyMap(strategyMap, "2", i + 1);
//如果用户名、账号、用户组一样，并且总完成率也一样，则可合并总完成率一列
if (currDto.getSumPercentage().equals(preExcelDto.getSumPercentage())) {
fillStrategyMap(strategyMap, "3", i + 1);
//如果用户名、账号、用户组、总完成率一样，并且总课程时长也一样，则可合并总课程时长一列
if (currDto.getSumVideoSeconds().equals(preExcelDto.getSumVideoSeconds())) {
fillStrategyMap(strategyMap, "4", i + 1);
//如果用户名、账号、用户组、总完成率、总课程时长一样，并且总学习时长也一样，则可合并总学习时长一列
if (currDto.getSumStudyVideoSeconds().equals(preExcelDto.getSumStudyVideoSeconds())) {
fillStrategyMap(strategyMap, "5", i + 1);
}
}
}
}
}
}
}
preExcelDto = currDto;
}
return strategyMap;
}
/**
* @Author: TheBigBlue
* @Description: 新增或修改合并策略map
* @Date: 2020/3/16
* @Param:
* @return:
**/
private static void fillStrategyMap(Map<String, List<RowRangeVo>> strategyMap, String key, int index) {
List<RowRangeVo> rowRangeDtoList = strategyMap.get(key) == null ? new ArrayList<>() : strategyMap.get(key);
boolean flag = false;
for (RowRangeVo dto : rowRangeDtoList) {
//分段list中是否有end索引是上一行索引的，如果有，则索引+1
if (dto.getEnd() == index) {
dto.setEnd(index + 1);
flag = true;
}
}
//如果没有，则新增分段
if (!flag) {
rowRangeDtoList.add(new RowRangeVo(index, index + 1));
}
strategyMap.put(key, rowRangeDtoList);
}
}
/////////////////////////////////////////////////////////////////////////////////
//UserStudyrecordsResponse 实体类
//导出学员学习记录分析表实体
//姓名
@ExcelProperty(value = {"主标题", "姓名"}, index = 0)
private String userName;
//账号
@ExcelProperty(value = {"主标题", "账号"}, index = 1)
private String mobile;
//用户组名称
@ExcelProperty(value = {"主标题", "用户组"}, index = 2)
private String groupName;
//总完成率
@ExcelProperty(value = {"主标题", "总完成率"}, index = 3)
private String sumPercentage;
//所有推送 总课程时长
@ExcelProperty(value = {"主标题", "总课程时长"}, index = 4)
private String sumVideoSeconds;
//所有推送课程已经观看的总时长
@ExcelProperty(value = {"主标题", "总学习时长"}, index = 5)
private String sumStudyVideoSeconds;
//开始学习时间
@ExcelProperty(value = {"主标题", "开始学习时间"}, index = 6)
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private Date startStudyTime;
//完成时间
@ExcelProperty(value = {"主标题", "完成时间"}, index = 7)
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private Date finishTime;
//完成率（百分比）
@ExcelProperty(value = {"主标题", "完成率"}, index = 8)
private String percentage;
//课程名字（任务名称）
@ExcelProperty(value = {"主标题", "任务名称"}, index = 9)
private String courseName;
//单个课程的时长
@ExcelProperty(value = {"主标题", "该课程时长"}, index = 10)
private String courseVideoSeconds;
//单个课程的学习时长
@ExcelProperty(value = {"主标题", "该课程的学习时长"}, index = 11)
private String courseStudyVideoSeconds;
//进度
@ExcelProperty(value = {"主标题", "进度"}, index = 12)
private String speedOfProgress;
异常提示 当 复杂头写入 和 合并单元格 同时使用的情况下，复杂表头失效，变成一个单元格。 建议描述