需求:需要导出一个excel表格,但表格不是正常的单行表格,如下图,该如何导出实现?
实现逻辑:
1.获取集合数据;
2.根据属性进行分组;
3.根据单元格向x,y位置进行合并;
4.生成文件返回下载地址;
// 实现方法
@SneakyThrows
public String downloadExcel() {
// 创建文件夹
File downloadDir = FileUtil.newFile("download");
if (!FileUtil.exist(downloadDir)) {
FileUtil.mkdir(downloadDir);
}
// 文件夹里创建目标文件
File destFile = FileUtil.file(downloadDir, DateUtil.format(DateUtil.date(), DatePattern.PURE_DATETIME_PATTERN) + GeneralConstant.EXPORT_FILE_SUFFIX);
// 获取查询数据
List<ExcelDto> excelDtoList = getexcelDtoList();
// 定义基础数据
ExcelWriter writer = ExcelUtil.getWriter(destFile, "这是文件名");
List<String> rowHead = CollUtil.newArrayList("排名", "单位", "总分", "账号", "类型", "科目一分数", "科目二分数", "附加分");
// 写入标题
writer.writeHeadRow(rowHead);
// 合并数据
List<List<Object>> rows = loadRowList(excelDtoList, writer);
// 导出数据
writer.write(rows, true);
// 设置某列单元格宽度
writer.setColumnWidth(1, 25);
writer.setColumnWidth(3, 25);
// 输出到文件
writer.flush();
writer.close();
return destFile.getAbsolutePath();
}
private List<List<Object>> loadRowList(List<ExcelDto> list, ExcelWriter writer) {
// 定义开始行
int index = 1;
List<List<Object>> rows = new LinkedList<>();
// 按照单位名进行分组,单位名为key,list为value
Map<String, List<ExcelDto>> groupMaps =
list.stream().collect(Collectors.groupingBy(ExcelDto::getCompanyName, LinkedHashMap::new,Collectors.toList()));
for (Map.Entry<String, List<ExcelDto>> listEntry : groupMaps.entrySet()) {
// 获取key对应的list
List<ExcelDto> excelDtoList = listEntry.getValue();
if(excelDtoList.size() == 1){
// 如果只有一条数据,加一个空数据行
ExcelDto emptyExcelDto = ExcelDto.getEmptyExcelDto();
emptyExcelDto.setIndex(excelDtoList.get(0).getIndex());
emptyExcelDto.setCompanyName(excelDtoList.get(0).getCompanyName());
excelDtoList.add(emptyExcelDto);
}
// 合并排名、单位、总分、附加分
writer.merge(index, index + excelDtoList.size() - 1, 0, 0, null, true);
writer.merge(index, index + excelDtoList.size() - 1, 1, 1, null, true);
writer.merge(index, index + excelDtoList.size() - 1, 2, 2, null, true);
writer.merge(index, index + 1, 7, 7, null, true);
index = index + excelDtoList.size();
// 保存数据
excelDtoList.forEach(item -> {
List<Object> row = null;
String countName = item.getCountName();
String type = item.getType().toString();
String firstScore = item.getFirstScore().toString();
String secondScore = item.getSecondScore.toString();
if ("".equals(item.getCountName())) {
countName = "-";
type = "-";
firstScore = "-";
secondScore = "-";
}
row = CollUtil.newArrayList(
item.getIndex(),
item.getCompanyName(),
item.getTotalScore(),
countName,
type,
firstScore,
secondScore,
item.getAddValue();
rows.add(row);
});
}
return rows;
}
@Data
public class ExcelDto() {
// 就这几个属性值
index;
companyName;
totalScore();
countName;
type;
firstScore;
secondScore;
iddValue();
}
以上代码可根据自身需要进行更改。