多级动态表头导出-easyexcel
导出如下动态表头
主要的构造tabCols和tableData，
注意表头的字段，基本构造出了该格式所有的都能适配
@GetMapping("/exportData")
public void excelExport(TbDtTargetHealthMon tbDtTargetHealthMon, HttpServletResponse response) throws IOException {
response.reset();
response.setCharacterEncoding("UTF-8");
//响应内容格式
response.setContentType("application/vnd.ms-excel");
//设置文件名
// String fileName =System.currentTimeMillis() + ".xlsx";
try {
//设置前端下载文件名
String urlFileName = URLEncoder.encode(tbDtTargetHealthMon.getModuleName() + "健康度详情" + System.currentTimeMillis() , "UTF-8");
response.setHeader("content-disposition", "attachment; filename=" + urlFileName + ".xlsx");
// *代表所有请求都可访问
response.setHeader("Access-Control-Allow-Origin", "*");
response.setHeader("Access-Control-Allow-Methods", "GET");
response.setHeader("Access-Control-Allow-Headers", "Origin, No-Cache, X-Requested-With, " +
"If-Modified-Since, Pragma, Last-Modified, Cache-Control, Expires, Content-Type, X-E4M-With");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
ExcelWriter excelWriter = null;
try {
Map<String, Object> result = tbDtTargetHealthMonService.getTabCols(tbDtTargetHealthMon);
List<Map<String, String>> tabCols = (List<Map<String, String>>) result.get("tabCols");
List<List<String>> excelHead = head(tabCols);
// 第一页数据
List<Map<String, String>> dataOne = (List<Map<String, String>>) result.get("tableData");
//5w数据一个sheet
int batchSize = AutoExportConstants.SHEET_PAGE_SIZE;
int totalSize = dataOne.size();
int sheetCount = (int) Math.ceil((double) totalSize / batchSize);
excelWriter = EasyExcelFactory.write(response.getOutputStream()).build();
for (int i = 0; i < sheetCount; i++) {
int fromIndex = i * batchSize;
int toIndex = Math.min((i + 1) * batchSize, totalSize);
List<Map<String, String>> currentData = dataOne.subList(fromIndex, toIndex);
WriteSheet writeSheet = EasyExcel
.writerSheet("sheet" + (i + 1))
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.registerWriteHandler(horizontalCellStyleStrategy())
.head(excelHead)
.build();
excelWriter.write(dataList(tabCols, currentData), writeSheet);
}
} catch (Exception e) {
e.printStackTrace();
} catch (Throwable throwable) {
throwable.printStackTrace();
} finally {
if (excelWriter != null) {
excelWriter.finish();
}
if(response.getOutputStream() != null){
response.getOutputStream().close();
}
}
}
private List<List<String>> head(List<Map<String, String>> tabCols) {
return tabCols.stream()
.map(e -> Stream.of(e.get("columnParent"), e.get("columnName")).filter(Objects::nonNull).collect(Collectors.toList()))
.collect(Collectors.toList());
}
private List<List<Object>> dataList(List<Map<String, String>> tabCols, List<Map<String, String>> mapList) {
List<List<Object>> list = new ArrayList<List<Object>>();
for (int i = 0; i < mapList.size(); i++) {
Map row = mapList.get(i);
List<Object> data = new ArrayList<Object>();
for (int j = 0; j < tabCols.size(); j++) {
String filed = tabCols.get(j).get("columnField");
Object objVal = row.get(filed);
String val = Objects.toString(objVal, "");
data.add(val);
}
list.add(data);
}
return list;
}
private HorizontalCellStyleStrategy horizontalCellStyleStrategy() {
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 背景设置为红色
headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 14);
headWriteCellStyle.setWriteFont(headWriteFont);
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
// 背景绿色
contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());
WriteFont contentWriteFont = new WriteFont();
contentWriteFont.setFontHeightInPoints((short) 10);
contentWriteFont.setColor(IndexedColors.GREY_80_PERCENT.getIndex());
contentWriteCellStyle.setWriteFont(contentWriteFont);
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
contentWriteCellStyle.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
contentWriteCellStyle.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
contentWriteCellStyle.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}
tabCols的格式
这个tabCols的顺序就是导出表头的顺序，columnField相当于就是取值映射的key，columnName就是表头的中文，columnParent就是父级表头，这些名字可以自己定义
[{
"columnField": "monthId",
"columnName": "月份"
}, {
"columnField": "latnName",
"columnName": "本地网"
}, {
"columnField": "销售费用整体效能",
"columnName": "销售费用整体效能得分"
}, {
"columnParent": "销售费用占收比",
"columnField": "targetValue_xsfy_001",
"columnName": "指标值"
}, {
"columnParent": "销售费用占收比",
"columnField": "targetGrades_xsfy_001",
"columnName": "指标得分"
}, {
"columnParent": "百元基础费拉动增量基础业务收入",
"columnField": "targetValue_xsfy_002",
"columnName": "指标值"
}, {
"columnParent": "百元基础费拉动增量基础业务收入",
"columnField": "targetGrades_xsfy_002",
"columnName": "指标得分"
}, {
"columnParent": "百元销售费用拉动增量主营收入",
"columnField": "targetValue_xsfy_003",
"columnName": "指标值"
}, {
"columnParent": "百元销售费用拉动增量主营收入",
"columnField": "targetGrades_xsfy_003",
"columnName": "指标得分"
}, {
"columnParent": "新增用户户均销售费用",
"columnField": "targetValue_xsfy_004",
"columnName": "指标值"
}, {
"columnParent": "新增用户户均销售费用",
"columnField": "targetGrades_xsfy_004",
"columnName": "指标得分"
}, {
"columnField": "渠道费用效能",
"columnName": "渠道费用效能得分"
}, {
"columnParent": "价值积分平均单价",
"columnField": "targetValue_xsfy_008",
"columnName": "指标值"
}, {
"columnParent": "价值积分平均单价",
"columnField": "targetGrades_xsfy_008",
"columnName": "指标得分"
}, {
"columnParent": "建店12月以上门店月均价值积分",
"columnField": "targetValue_xsfy_009",
"columnName": "指标值"
}, {
"columnParent": "建店12月以上门店月均价值积分",
"columnField": "targetGrades_xsfy_009",
"columnName": "指标得分"
}, {
"columnParent": "价值积分同比增长",
"columnField": "targetValue_xsfy_010",
"columnName": "指标值"
}, {
"columnParent": "价值积分同比增长",
"columnField": "targetGrades_xsfy_010",
"columnName": "指标得分"
}, {
"columnParent": "千分店占比",
"columnField": "targetValue_xsfy_011",
"columnName": "指标值"
}, {
"columnParent": "千分店占比",
"columnField": "targetGrades_xsfy_011",
"columnName": "指标得分"
}, {
"columnField": "客户服务费效能",
"columnName": "客户服务费效能得分"
}, {
"columnParent": "客户服务费占收比",
"columnField": "targetValue_xsfy_012",
"columnName": "指标值"
}, {
"columnParent": "客户服务费占收比",
"columnField": "targetGrades_xsfy_012",
"columnName": "指标得分"
}, {
"columnParent": "单位用户客户服务费",
"columnField": "targetValue_xsfy_013",
"columnName": "指标值"
}, {
"columnParent": "单位用户客户服务费",
"columnField": "targetGrades_xsfy_013",
"columnName": "指标得分"
}, {
"columnParent": "客户保有率",
"columnField": "targetValue_xsfy_014",
"columnName": "指标值"
}, {
"columnParent": "客户保有率",
"columnField": "targetGrades_xsfy_014",
"columnName": "指标得分"
}, {
"columnParent": "收入保有率",
"columnField": "targetValue_xsfy_015",
"columnName": "指标值"
}, {
"columnParent": "收入保有率",
"columnField": "targetGrades_xsfy_015",
"columnName": "指标得分"
}]
tableData的格式
这个顺序无所谓，key就对应的就是tabCols的columnField
[{
"targetValue_xsfy_008": "2.6900",
"targetGrades_xsfy_012": "86.67",
"targetValue_xsfy_009": "3542.0100",
"targetGrades_xsfy_013": "93.33",
"targetGrades_xsfy_010": "6.67",
"targetGrades_xsfy_011": "86.67",
"targetValue_xsfy_004": "227.9600",
"targetValue_xsfy_002": "0.0000",
"targetGrades_xsfy_014": "0.00",
"targetValue_xsfy_003": "73.8400",
"渠道费用效能": "58.67",
"targetGrades_xsfy_015": "0.00",
"monthId": "202401",
"销售费用整体效能": "21.67",
"targetValue_xsfy_011": "0.8081",
"targetValue_xsfy_012": "1.64%",
"targetValue_xsfy_010": "76.18%",
"targetGrades_xsfy_001": "13.33",
"targetGrades_xsfy_002": "0.00",
"targetValue_xsfy_015": "0.00%",
"targetGrades_xsfy_003": "0.00",
"targetValue_xsfy_013": "0.7500",
"客户服务费效能": "54.00",
"targetGrades_xsfy_004": "73.33",
"targetValue_xsfy_014": "0.00%",
"targetGrades_xsfy_009": "40.00",
"targetGrades_xsfy_008": "80.00",
"targetValue_xsfy_001": "10.19%",
"latnName": "合肥"
}]
