实现过程中还是遇到比较多的坑的,本来想用POI,但是代码太多了
产品需求:多月条件筛选,列不固定,表头有合并
实现效果,基本上和需求相符:
贴代码:
//创建表头集合 List<List<String>> headList = new ArrayList<>();
//第一列,合并三行 headList.add(Arrays.asList("项目名称","项目名称","项目名称"));
//以月份为维度,添加表头
LocalDateTime startTime1 = DateUtils.parse(vo.getStartTime(), DateUtils.YYYY_MM); LocalDateTime endTime1 = DateUtils.parse(vo.getEndTime(), TimeUtil.MONTH); while (!startTime1.isAfter(endTime1)){ String monthStr = TimeUtil.format(startTime1, TimeUtil.MONTH); if (StringUtils.hasText(vo.getDetailType())){ addHeadList(headList, monthStr, vo.getDetailType());//列数过多,控制导出表头的列,代码太麻瓜,就不贴出来了 大概就是这样
headList.add(Arrays.asList(monthStr, DictEnums.DetailType.INCOME.getDesc(), DictEnums.MonthReportQueryLine.INCOME.getDesc()));
} startTime1 = startTime1.plusMonths(1); }
//追加合计列
addHeadList(headList, "合计", vo.getDetailType());
//添加数据,和表头集合保持相同格式和列数
List<List<Object>> dataList = new ArrayList<>();
if (CollectionUtils.isNotEmpty(resultData)){ Map<Integer, MonthlyReportVO> listMap = resultData.stream().collect(Collectors.toMap(x->x.getProjectId(), Function.identity())); Map<Integer, MonthlyReportVO> listMap1 = Maps.newLinkedHashMap(); listMap.entrySet().stream().sorted(Collections.reverseOrder(Map.Entry.comparingByKey())).forEachOrdered(x -> listMap1.put(x.getKey(), x.getValue())); for (Map.Entry<Integer, MonthlyReportVO> entry : listMap1.entrySet()){ List<Object> dataLine = Lists.newArrayList(); MonthlyReportVO projectData = entry.getValue(); dataLine.add(projectData.getProjectName()); LocalDateTime startTime = DateUtils.parse(vo.getStartTime(), DateUtils.YYYY_MM); LocalDateTime endTime = DateUtils.parse(vo.getEndTime(), TimeUtil.MONTH); Map<String,MonthReportDataVO> monthDataMap = projectData.getDataList().stream().collect(Collectors.toMap(x->x.getMonth(), Function.identity())); while (!startTime.isAfter(endTime)){ String monthStr = TimeUtil.format(startTime, TimeUtil.MONTH); MonthReportDataVO monthData = monthDataMap.get(monthStr); if (StringUtils.hasText(vo.getDetailType())){ addDataList(dataLine, vo.getDetailType(), monthData);//列数过多,控制导出的列,因为要和表头保持列数一致,所以也需要和表头做相同的处理,不然导出的时候会报错 } startTime = startTime.plusMonths(1); } addDataList(dataLine, vo.getDetailType(), monthDataMap.get("total")); dataList.add(dataLine); } }
try { String fileName="月报表" + DateUtil.format(new Date(), TimeUtil.TIME); // excel文件名称 ExcelUtil.excelResponse(res, fileName); EasyExcel.write(res.getOutputStream()) .head(headList) .registerWriteHandler(new SimpleColumnWidthStyleStrategy(14))//这里注册了列宽14的策略,自动列宽则设置策略,根据实际需求设定,new LongestMatchColumnWidthStyleStrategy() .sheet("sheet1") .doWrite(dataList); }catch (Exception e){ e.printStackTrace(); }
如有错误请指正!谢谢