public void exportTableDataByPoi(String startTime, String endTime, String timeType, String cp, HttpServletResponse response) throws Exception {
// 注销的代码为一行的数据的生成方式
Map map = this.getPlayData(startTime, endTime, timeType, cp, -1, 0);
List<EpgPlayTableDateVo> list = (List<EpgPlayTableDateVo>) map.get("data2");
// hashmap未将label进行排序
Set<String> labelSet = new TreeSet<>();
for (EpgPlayTableDateVo epgPlayTableDateVo : list) {
List<EpgPlayTableCountsVo> countsVos = epgPlayTableDateVo.getVos();
// 当前这个vo下的countvo里无重复的cp
// countsVos.stream().distinct().collect(Collectors.toList())
countsVos.stream().forEach(i -> {
labelSet.add(i.getLabel());
});
}
List<String> resList = new ArrayList<>();
for (String s : labelSet) {
resList.add(s + "播放量");
resList.add(s + "播放时长");
}
// 添加一个时间label
resList.add("date");
List<ExcelExportEntity> entityList = new ArrayList<>(resList.size());
/*for (String s : resList) {
ExcelExportEntity entity = new ExcelExportEntity(s+"1", s, 20);
//先出现的s已经吧位置占住了
*//*if ("date".equals(s)) {
entity.setOrderNum(1);
} else if ("total播放量".equals(s)) {
entity.setOrderNum(resList.size() - 2);
} else if ("total播放量".equals(s)){
entity.setOrderNum(resList.size() - 1);
}*//*
entityList.add(entity);
}*/
// labelset中只有这些cp名字
List<String> objects = new ArrayList(labelSet);
for (int i = 0; i < objects.size(); i++) {
String cpName = objects.get(i);
// 前面的cpName是展示在页面上用的 后面的是用来和数据中的map的key对应的
ExcelExportEntity entityGroup = new ExcelExportEntity(cpName, cpName, 20);
if ("total".equals(cpName)){
// 直接让他放在最后面 size+1
entityGroup.setOrderNum(objects.size() + 1);
}else {
entityGroup.setOrderNum(i + 1);// 第一列放空
}
List<ExcelExportEntity> undeeCpList = new ArrayList<ExcelExportEntity>();
ExcelExportEntity playCountName = null;
ExcelExportEntity playDurationName = null;
for (int j = 0; j < resList.size(); j++) {
String countName = resList.get(j);
if (countName.contains(cpName)){
if (countName.contains("播放量")){
playCountName = new ExcelExportEntity("播放量", countName, 20);
}else if (countName.contains("播放时长")){
playDurationName = new ExcelExportEntity("播放时长", countName, 20);
}
}
}
undeeCpList.add(playCountName);
undeeCpList.add(playDurationName);
entityGroup.setList(undeeCpList);
entityList.add(entityGroup);
}
ExcelExportEntity entity = new ExcelExportEntity("日期", "date", 20);
entity.setOrderNum(0);
entityList.add(entity);
// 数据的格式也要和上边的一二行的设计格式一样
List<Map<String, Object>> dataList = new ArrayList<>();
if (!CollectionUtils.isEmpty(list)) {
for (EpgPlayTableDateVo vo : list) {
Map m = new HashMap();
String date = vo.getDate();
m.put("date", date);
for (String cpName : objects) {
List<Map<String, Object>> detailList = null;
for (EpgPlayTableCountsVo countsVo : vo.getVos()) {
if (cpName.equals(countsVo.getLabel())){
detailList = new ArrayList<>();
Map<String,Object> detailMap = new HashMap<>();
String label = countsVo.getLabel();
Integer playCount = countsVo.getPlayCount();
detailMap.put(label + "播放量", playCount);
Integer playDuration = countsVo.getPlayDuration();
detailMap.put(label + "播放时长", playDuration);
detailList.add(detailMap);
}
}
m.put(cpName,detailList);
}
dataList.add(m);
}
}
// 数据
/*List<Map<String, Object>> dataList = new ArrayList<>();
if (!CollectionUtils.isEmpty(list)) {
for (EpgPlayTableDateVo vo : list) {
Map m = new HashMap();
String date = vo.getDate();
m.put("date", date);
for (EpgPlayTableCountsVo countsVo : vo.getVos()) {
String label = countsVo.getLabel();
Integer playCount = countsVo.getPlayCount();
m.put(label + "播放量", playCount);
Integer playDuration = countsVo.getPlayDuration();
m.put(label + "播放时长", playDuration);
}
dataList.add(m);
}
}*/
// 数据源需要改成LIST<mAP>的形式
ExportParams params = new ExportParams();
Workbook excel = ExcelExportUtil.exportExcel(params, entityList, dataList);
String fname = URLEncoder.encode("播放信息", "utf-8");
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename=" + fname + ExcelTypeEnum.XLSX.getValue());
excel.write(response.getOutputStream());
excel.close();
}
pom文件
3.14版本 ,有的使用3.17会报错
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>1.1.2-beta4</version>
</dependency>
<!-- eassypoi -->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.3.0</version>
</dependency>
EasyPoi教程_V1.0 2.4中有讲解
easypoi导出动态表头excel - 灰信网(软件开发博客聚合) 下面为原文地址
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.2.0</version>
</dependency>
-
@Test public void dynaCol() { try { List<ExcelExportEntity> colList = new ArrayList<ExcelExportEntity>(); ExcelExportEntity colEntity = new ExcelExportEntity("商品名称", "title"); colEntity.setNeedMerge(true); colList.add(colEntity); colEntity = new ExcelExportEntity("供应商", "supplier"); colEntity.setNeedMerge(true); colList.add(colEntity); ExcelExportEntity deliColGroup = new ExcelExportEntity("得力", "deli"); List<ExcelExportEntity> deliColList = new ArrayList<ExcelExportEntity>(); deliColList.add(new ExcelExportEntity("市场价", "orgPrice")); deliColList.add(new ExcelExportEntity("专区价", "salePrice")); deliColGroup.setList(deliColList); colList.add(deliColGroup); ExcelExportEntity jdColGroup = new ExcelExportEntity("京东", "jd"); List<ExcelExportEntity> jdColList = new ArrayList<ExcelExportEntity>(); jdColList.add(new ExcelExportEntity("市场价", "orgPrice")); jdColList.add(new ExcelExportEntity("专区价", "salePrice")); jdColGroup.setList(jdColList); colList.add(jdColGroup); List<Map<String, Object>> list = new ArrayList<Map<String, Object>>(); for (int i = 0; i < 10; i++) { Map<String, Object> valMap = new HashMap<String, Object>(); valMap.put("title", "名称." + i); valMap.put("supplier", "供应商." + i); List<Map<String, Object>> deliDetailList = new ArrayList<Map<String, Object>>(); for (int j = 0; j < 3; j++) { Map<String, Object> deliValMap = new HashMap<String, Object>(); deliValMap.put("orgPrice", "得力.市场价." + j); deliValMap.put("salePrice", "得力.专区价." + j); deliDetailList.add(deliValMap); } valMap.put("deli", deliDetailList); List<Map<String, Object>> jdDetailList = new ArrayList<Map<String, Object>>(); for (int j = 0; j < 2; j++) { Map<String, Object> jdValMap = new HashMap<String, Object>(); jdValMap.put("orgPrice", "京东.市场价." + j); jdValMap.put("salePrice", "京东.专区价." + j); jdDetailList.add(jdValMap); } valMap.put("jd", jdDetailList); list.add(valMap); } Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("价格分析表", "数据"), colList, list); FileOutputStream fos = new FileOutputStream("D:/价格分析表.tt.xls"); workbook.write(fos); fos.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
导出结果图