一、背景
公司有个报表需求是根据指定日期范围导出指定数据，并且要根据不同逻辑生成两个Sheet，这个日期影响的是列数而不是行数，即行的数量和列的数量都是动态变化的，根据用户的选择动态生成的，这个问题花了不少时间才解决的，这边记下笔记。
二、效果图
动态生成30个列，两张Sheet
动态生成1个列，两张Sheet
三 、准备
我们公司使用的版本是3.2.0，我们项目没有引入所有模块，只用到了base和annotation
<dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-base</artifactId> <version>3.2.0</version> <exclusions> <exclusion> <groupId>org.slf4j</groupId> <artifactId>slf4j-api</artifactId> </exclusion> <exclusion> <groupId>org.apache.commons</groupId> <artifactId>commons-lang3</artifactId> </exclusion> </exclusions> </dependency> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-annotation</artifactId> <version>3.2.0</version> </dependency>
四、详细步骤
定义表格样式
/** * 定义表格样式 * * @param start 查询起始日期 * @param end 查询结束日期 * @return java.util.List<cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity> * @author huan * @date 2019/6/21 * @since 2.8.2 */ private List<ExcelExportEntity> setExportExcelStyle(DateTime start, DateTime end) { //定义表格列名,该集合存放的就是表格的列明，每个对象就是表格中的一列 List<ExcelExportEntity> modelList = new ArrayList<ExcelExportEntity>(); //该对象就是定义列属性的对象 ExcelExportEntity excelentity = null; //定义第一个列 excelentity = new ExcelExportEntity("企业全称", "companyName"); excelentity.setWidth(20); excelentity.setHeight(10); modelList.add(excelentity); //定义第二个列 excelentity = new ExcelExportEntity("企业简称", "companyShortName"); excelentity.setWidth(20); excelentity.setHeight(10); modelList.add(excelentity); //定义第三个列，这里指定了日期显示格式 excelentity = new ExcelExportEntity("认证日期", "openDate"); excelentity.setWidth(20); excelentity.setHeight(10); excelentity.setFormat("yyyy-MM-dd"); modelList.add(excelentity); //定义第四个列,这边就是动态生成的,跟用用户选择的日期范围,动态生成列的数量 excelentity = new ExcelExportEntity(null, "recordDate"); //设置一个集合,存放动态生成的列 List<ExcelExportEntity> modelListChild = new ArrayList<ExcelExportEntity>(); start = DateUtils.getDateZeroTime(start); while (start.isBefore(end)) { String date = start.toString("yyyy-MM-dd"); modelListChild.add(new ExcelExportEntity(date, date, 15)); start = start.plusDays(1); } //日期按从小到大顺序排序,这里用了最简单的冒泡排序 for (int i = 0; i < modelListChild.size(); i++) { for (int j = 0; j < modelListChild.size(); j++) { String e1 = modelListChild.get(i).getKey().toString(); String e2 = modelListChild.get(j).getKey().toString(); if (e1.compareTo(e2) < 0) { ExcelExportEntity x1 = modelListChild.get(i); ExcelExportEntity x2 = modelListChild.get(j); modelListChild.set(j, x1); modelListChild.set(i, x2); } } } //将定义好的字列放到父列中 excelentity.setList(modelListChild); modelList.add(excelentity); //定义第五个列 excelentity = new ExcelExportEntity("应当使用天数", "shouldUseDay"); excelentity.setWidth(20); excelentity.setHeight(10); modelList.add(excelentity); //定义第六个列 excelentity = new ExcelExportEntity("实际使用天数", "actualUseDay"); excelentity.setWidth(20); excelentity.setHeight(10); modelList.add(excelentity); //定义第七个列 excelentity = new ExcelExportEntity("使用率", "rate"); excelentity.setWidth(20); excelentity.setHeight(10); modelList.add(excelentity); //定义第八个列 excelentity = new ExcelExportEntity("推荐人", "commandMan"); excelentity.setWidth(20); excelentity.setHeight(10); modelList.add(excelentity); //定义第九个列 excelentity = new ExcelExportEntity("拓客", "tk"); excelentity.setWidth(20); excelentity.setHeight(10); modelList.add(excelentity); //定义第十个列 excelentity = new ExcelExportEntity("对接人", "connector"); excelentity.setWidth(20); excelentity.setHeight(10); modelList.add(excelentity); return modelList; }
定义表格数据
private List<Map<String, Object>> getData(AnalyseStockQuery analyseStockQuery, boolean type) { //获取数据源 ArrayList<AnalyseStockExportDto> dtoList = listDetailDataWithNum(analyseStockQuery, type); List<Map<String, Object>> dataList = new ArrayList<>(); //存储没一行中的日期数据 List<Map<String, Object>> dataListChild = null; //存储表格中的每一行数据 Map<String, Object> mapParent = null; //数据排序 dtoList.sort(new ExportComparator()); //定义表格数据 for (AnalyseStockExportDto dto : dtoList) { mapParent = new HashMap(7); //这边只要和定义表格样式的时候 名称一致就行 我这边因为有三个字段不需要我这边后台生成，所以就没有设置默认值了 mapParent.put("companyName", dto.getCompanyName()); mapParent.put("companyShortName", dto.getCompanyShortName()); mapParent.put("openDate", dto.getOpenDate()); mapParent.put("shouldUseDay", dto.getShouldUseDay()); mapParent.put("actualUseDay", dto.getActualUseDay()); mapParent.put("rate", dto.getRate()); Map<String, Object> map = dto.getDateList(); dataListChild = new ArrayList<>(); dataListChild.add(map); mapParent.put("recordDate", dataListChild); dataList.add(mapParent); } return dataList; }
主体方法
/** * 报表导出 * * @param analyseStockQuery analyseStockQuery * @param response response * @return javax.servlet.http.HttpServletResponse * @author huan * @date 2019/6/21 * @since 2.8.2 */ public HttpServletResponse exportStock(AnalyseStockQuery analyseStockQuery, HttpServletResponse response) { try { //设置默认查询日期 analyseStockQuery = setDefaultQueryDate(analyseStockQuery); //参数校验 checkListDetailDataParam(analyseStockQuery); //日期格式化 DateTime start = new DateTime().withDate(new LocalDate(analyseStockQuery.getQueryStartDate())); DateTime end = new DateTime().withDate(new LocalDate(analyseStockQuery.getQueryLastDate())); //定义表格样式 List<ExcelExportEntity> modelList = setExportExcelStyle(start, end); //定义表格名称 String fileName = URLEncoder.encode("客户库存使用统计表-" + start.toString("yyyy年MM月dd日") + "~" + end.toString("yyyy年MM月dd日"), "utf-8"); // Sheet1样式 ExportParams sheet1ExportParams = new ExportParams(); // 设置sheet得名称 sheet1ExportParams.setSheetName("入库统计"); // 创建sheet1使用得map Map<String, Object> sheet1ExportMap = new HashMap<>(); // title的参数为ExportParams类型，目前仅仅在ExportParams中设置了sheetName sheet1ExportMap.put("title", sheet1ExportParams); //sheet1样式 sheet1ExportMap.put("entityList", modelList); //sheet1中要填充得数据,true表示查询入库数据，false表示查询易签待入库数据 sheet1ExportMap.put("data", getData(analyseStockQuery, true)); //Sheet2设置 ExportParams sheet2ExportParams = new ExportParams(); sheet2ExportParams.setSheetName("易签待入库统计"); Map<String, Object> sheet2ExportMap = new HashMap<>(); sheet2ExportMap.put("title", sheet2ExportParams); sheet2ExportMap.put("entityList", modelList); sheet2ExportMap.put("data", getData(analyseStockQuery, false)); // 将sheet1、sheet2使用得map进行包装 List<Map<String, Object>> sheetsList = new ArrayList<>(); sheetsList.add(sheet1ExportMap); sheetsList.add(sheet2ExportMap); // 执行方法 Workbook workBook = exportExcel(sheetsList, ExcelType.HSSF); //设置response response.setHeader("content-disposition", "attachment;filename=" + fileName + ".xls"); //设置编码格式 response.setCharacterEncoding("GBK"); //将表格内容写到输出流中并刷新缓存 @Cleanup ServletOutputStream out = response.getOutputStream(); workBook.write(out); out.flush(); workBook.close(); } catch (FileNotFoundException e) { log.debug("FileNotFoundException：{}", e.getMessage()); } catch (UnsupportedEncodingException e) { log.debug("UnsupportedEncodingException：{}", e.getMessage()); } catch (IOException e) { log.debug("IOException：{}", e.getMessage()); } return response; }
导出Excel
/** * 导出Ecel * * @return org.apache.poi.ss.usermodel.Workbook * @author zhuyongsheng * @date 2019/11/6 */ private static Workbook exportExcel(List<Map<String, Object>> list) { Workbook workbook = new HSSFWorkbook(); for (Map<String, Object> map : list) { MyExcelExportService service = new MyExcelExportService(); service.createSheetWithList(workbook, (ExportParams) map.get("title"), ExportParams.class, (List<ExcelExportEntity>) map.get("entityList"), (Collection<?>) map.get("data")); } return workbook; }
自定义导出逻辑
package com.ccb.service.analyse; import cn.afterturn.easypoi.excel.annotation.ExcelTarget; import cn.afterturn.easypoi.excel.entity.ExportParams; import cn.afterturn.easypoi.excel.entity.enmus.ExcelType; import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity; import cn.afterturn.easypoi.excel.export.ExcelExportService; import cn.afterturn.easypoi.exception.excel.ExcelExportException; import cn.afterturn.easypoi.exception.excel.enums.ExcelExportEnum; import cn.afterturn.easypoi.util.PoiPublicUtil; import lombok.extern.slf4j.Slf4j; import org.apache.poi.ss.usermodel.Workbook; import java.lang.reflect.Field; import java.util.ArrayList; import java.util.Collection; import java.util.List; /** * 自定义下导出逻辑 * @author huan * @version 2.8.2 * @date 2019/7/5 */ @Slf4j public class MyExcelExportService extends ExcelExportService { public void createSheetWithList(Workbook workbook, ExportParams entity, Class<?> pojoClass, List<ExcelExportEntity> entityList, Collection<?> dataSet) { if (LOGGER.isDebugEnabled()) { LOGGER.debug("Excel export start ,class is {}", pojoClass); LOGGER.debug("Excel version is {}", entity.getType().equals(ExcelType.HSSF) ? "03" : "07"); } if (workbook == null || entity == null || pojoClass == null || dataSet == null) { throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR); } try { List<ExcelExportEntity> excelParams = entityList; // 得到所有字段 Field[] fileds = PoiPublicUtil.getClassFields(pojoClass); ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class); String targetId = etarget == null ? null : etarget.value(); getAllExcelField(entity.getExclusions(), targetId, fileds, excelParams, pojoClass, null, null); //获取所有参数后,后面的逻辑判断就一致了 createSheetForMap(workbook, entity, excelParams, dataSet); } catch (Exception e) { LOGGER.error(e.getMessage(), e); throw new ExcelExportException(ExcelExportEnum.EXPORT_ERROR, e.getCause()); } } }
