需求：
导出按月统计的产品销量和销售额，查询时间是动态的，比如我要导出2023-07月到2023-09月的数据，就是下面这种效果
思路：
查阅官方文档，发现一个类似问题的解决方案，但是文档上只写了简单表头的实现方式：https://easyexcel.opensource.alibaba.com/docs/current/quickstart/write#动态头实时生成头写入
/**
* 动态头，实时生成头写入
* <p>
* 思路是这样子的，先创建List<String>头格式的sheet仅仅写入头,然后通过table 不写入头的方式 去写入数据
*
* <p>
* 1. 创建excel对应的实体对象 参照{@link DemoData}
* <p>
* 2. 然后写入table即可
*/
@Test
public void dynamicHeadWrite() {
String fileName = TestFileUtil.getPath() + "dynamicHeadWrite" + System.currentTimeMillis() + ".xlsx";
EasyExcel.write(fileName)
// 这里放入动态头
.head(head()).sheet("模板")
// 当然这里数据也可以用 List<List<String>> 去传入
.doWrite(data());
}
private List<List<String>> head() {
List<List<String>> list = new ArrayList<List<String>>();
List<String> head0 = new ArrayList<String>();
head0.add("字符串" + System.currentTimeMillis());
List<String> head1 = new ArrayList<String>();
head1.add("数字" + System.currentTimeMillis());
List<String> head2 = new ArrayList<String>();
head2.add("日期" + System.currentTimeMillis());
list.add(head0);
list.add(head1);
list.add(head2);
return list;
}
其关键代码就在于head()这个方法，它的返回值是一个List<List
代码实现：
@Override
public void exportProductSkuSingleProductSales(List<SellItemDate> sellItemDateList, Boolean monthly) throws IOException {
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 12);
headWriteCellStyle.setWriteFont(headWriteFont);
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
HorizontalCellStyleStrategy styleStrategy =
new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
EasyExcel.write(response.getOutputStream())
.registerWriteHandler(styleStrategy)
.registerWriteHandler(new SimpleColumnWidthStyleStrategy(monthly ? 10 : 20))
.sheet("销量查询")
.head(getExportProductSkuSingleProductSalesHead(sellItemDateList, monthly))
.doWrite(this.getExportProductSkuSingleProductSaleDataList(sellItemDateList, monthly));
}
/**
* 获取表头:导出单品销量数据-按产品SKU
* @param sellItemDateList 数据
* @param monthly 是否按月统计
*/
private static List<List<String>> getExportProductSkuSingleProductSalesHead(List<SellItemDate> sellItemDateList, Boolean monthly) {
List<List<String>> headList = new ArrayList<>();
//创建简单标题
List<String> simpleTitleSet = new ArrayList<>(Arrays.asList("产品名称", "销量（单品）", "营业额"));
simpleTitleSet.forEach(item -> {
if (monthly) {
headList.add(new ArrayList<>(Arrays.asList(item, item)));
} else {
headList.add(new ArrayList<>(Arrays.asList(item)));
}
});
if (monthly && sellItemDateList.size() > 0) {
//创建复杂标题
SellItemDate sellItemDate = sellItemDateList.get(0);
List<String> monthList = sellItemDate.getSumQtyMap().keySet().stream().sorted().collect(Collectors.toList());
monthList.forEach(item -> {
headList.add(new ArrayList<>(Arrays.asList(item, "销量")));
headList.add(new ArrayList<>(Arrays.asList(item, "销售额")));
});
}
return headList;
}
