POI之HSSF,XSSF,SXSSF,PDF循环写入分页
程序员文章站
2022-03-06 23:49:40
...
针对POI3.9jar包中的相关工具类HSSF,XSSF,SXSSF的理解，以及PDF实现循环写入及分页的实现
需求如下：
根据接口传递的相关数据，导出一定样式（合并行列，上色，加粗，循环写入）的表格，行数视数据量多少而定
原有结构：
假定100个人 100份数据分100个excel导出，采用HSSFWorkbook类导出
接下来逐步由此产生问题进而解决：
- 改造原有写入逻辑，传入HSSFWorkbook参数在循环外层一直持有，不断获取最新一行然后追加----结果：报行数65535的错且写入后期超级慢（不可取）
private void generateUserArchive(List<RangeRetVo> selfInfo, List<RangeRetVo> platformInfo, List<RangeRetVo> courseInfo, Map<Long, List<TargetSetting>> sortedMap, FilterCondition filter, List<TargetEntity> targetEns, List<TargetDetailEntity> dTargetEns, String baseDir, Long timestamp, List<UserCustomPropertyEntity> customPropertyList) { //为每一个用户生成一个文档 for (RangeRetVo self : selfInfo) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("111"); //根据param中选择的总指标和统计周期类型设计表头 HSSFFont bold = wb.createFont(); bold.setFontName("仿宋_GB2312"); bold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示 bold.setFontHeightInPoints((short) 12); //字体大小 HSSFCellStyle centerHeadStyle = wb.createCellStyle(); centerHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //表头居中 centerHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); centerHeadStyle.setFont(bold); centerHeadStyle.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); centerHeadStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); HSSFCellStyle leftHeadStyle = wb.createCellStyle(); centerHeadStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); //表头居中 centerHeadStyle.setFont(bold); HSSFCellStyle centerStyle = wb.createCellStyle(); centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //居中 HSSFCellStyle leftStyle = wb.createCellStyle(); leftStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); //左端 HSSFCellStyle rightStyle = wb.createCellStyle(); rightStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT); //左端 HSSFRow row = sheet.createRow(0); HSSFCell cell = row.createCell(0); cell.setCellValue("时间范围：" + filter.getStartTime() + "~" + filter.getEndTime()); cell.setCellStyle(centerHeadStyle); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 8)); int headCellOffset = 0; //表格数据起始行 int rowCount = 5; //表头数据起始行 int headRowOffset = 1; row = sheet.createRow(headRowOffset); cell = row.createCell(headCellOffset); cell.setCellValue("个人信息"); cell.setCellStyle(centerHeadStyle); headCellOffset++; cell = row.createCell(headCellOffset); cell.setCellValue("姓名"); cell.setCellStyle(rightStyle); cell = row.createCell(headCellOffset + 1); cell.setCellValue(self.getUserName()); cell.setCellStyle(leftStyle); headRowOffset++; headRowOffset = headRowOffset % 5 == 0 ? 1 : headRowOffset % 5; headCellOffset = headRowOffset % 5 == 0 ? headCellOffset + 2 : headCellOffset; row = sheet.createRow(headRowOffset); cell = row.createCell(headCellOffset); cell.setCellValue("账号"); cell.setCellStyle(rightStyle); cell = row.createCell(headCellOffset + 1); cell.setCellValue(self.getLoginName()); cell.setCellStyle(leftStyle); headRowOffset++; headRowOffset = headRowOffset % 5 == 0 ? 1 : headRowOffset % 5; headCellOffset = headRowOffset % 5 == 0 ? headCellOffset + 2 : headCellOffset; //List<TargetSetting> selfSetting = sortedMap.get(ReportConts.SELF_RANGE); for (TargetResult res : self.getTargetResults()) { if (res.getTypeName().startsWith(ReportConts.CUSTOM_TARGET_PERFIX)) { //自定义信息 Long customId = Long.parseLong(res.getTypeName().split("_")[1]); Optional<UserCustomPropertyEntity> op = customPropertyList.stream() .filter(e -> e.getId().equals(customId)).findFirst(); if (op != null && op.isPresent()) { UserCustomPropertyEntity en = op.get(); row = sheet.getRow(headRowOffset); if (row == null) { row = sheet.createRow(headRowOffset); } cell = row.createCell(headCellOffset); cell.setCellValue(en.getName()); cell.setCellStyle(rightStyle); cell = row.createCell(headCellOffset + 1); cell.setCellValue(res.getTargetVal() + ""); cell.setCellStyle(leftStyle); headRowOffset++; headRowOffset = headRowOffset % 5 == 0 ? 1 : headRowOffset % 5; headCellOffset = headRowOffset == 1 ? headCellOffset + 2 : headCellOffset; } } else { Optional<TargetEntity> tEn = targetEns.stream() .filter(e -> e.getTypeName().equals(res.getTypeName())).findFirst(); if (tEn.isPresent()) { TargetEntity t = tEn.get(); row = sheet.getRow(headRowOffset); if (row == null) { row = sheet.createRow(headRowOffset); } cell = row.createCell(headCellOffset); cell.setCellValue(t.getName()); cell.setCellStyle(rightStyle); cell = row.createCell(headCellOffset + 1); cell.setCellValue(res.getTargetVal() + ""); cell.setCellStyle(leftStyle); headRowOffset++; headRowOffset = headRowOffset % 5 == 0 ? 1 : headRowOffset % 5; headCellOffset = headRowOffset == 1 ? headCellOffset + 2 : headCellOffset; } } } //合并个人信息 sheet.addMergedRegion(new CellRangeAddress(1, 4, 0, 0)); //处理平台信息 Optional<RangeRetVo> platformOp = platformInfo.stream() .filter(e -> self.getUserId().equals(e.getUserId())).findFirst(); if (platformOp.isPresent()) { headRowOffset = 1; headCellOffset = sheet.getRow(headRowOffset).getLastCellNum(); RangeRetVo vo = platformOp.get(); List<TargetSetting> platformSetting = sortedMap.get(ReportConts.PLATFORM_RANGE); row = sheet.getRow(headRowOffset); if (row == null) { row = sheet.createRow(headRowOffset); } cell = row.createCell(headCellOffset); cell.setCellValue("平台数据"); cell.setCellStyle(centerHeadStyle); sheet.addMergedRegion(new CellRangeAddress(1, 4, headCellOffset, headCellOffset)); headCellOffset++; List<Map<String, Object>> platformDetails = new ArrayList<>(); for (TargetResult res : vo.getTargetResults()) { Optional<TargetEntity> tEn = targetEns.stream() .filter(e -> e.getTypeName().equals(res.getTypeName())).findFirst(); if (tEn.isPresent()) { row = sheet.getRow(headRowOffset); if (row == null) { row = sheet.createRow(headRowOffset); } TargetEntity t = tEn.get(); cell = row.createCell(headCellOffset); cell.setCellValue(t.getName()); cell.setCellStyle(rightStyle); cell = row.createCell(headCellOffset + 1); cell.setCellValue(res.getTargetVal() + ""); cell.setCellStyle(leftStyle); headRowOffset++; headRowOffset = headRowOffset % 5 == 0 ? 1 : headRowOffset % 5; headCellOffset = headRowOffset == 1 ? headCellOffset + 2 : headCellOffset; if (!res.getDetails().isEmpty()) { platformDetails.addAll(res.getDetails()); } } } //处理平台数据详情 if (!platformDetails.isEmpty()) { List<TargetDetailSetting> dTypeNames = new ArrayList<>(); for (TargetSetting tSetting : platformSetting) { for (TargetDetailSetting dtSetting : tSetting.getDetailSettings()) { Optional<TargetDetailSetting> op = dTypeNames.stream() .filter(e -> dtSetting.getDTypeName().equals(e.getDTypeName())) .findFirst(); if (!op.isPresent()) { dTypeNames.add(dtSetting); } } } List<TargetDetailSetting> sortDTypeNames = dTypeNames.stream() .sorted((o1, o2) -> { if (o1.getDTargetOrder() >= o2.getDTargetOrder()) { return 1; } return 0; }).collect(Collectors.toList()); HSSFSheet loginSheet = wb.createSheet("222"); HSSFRow loginRow = null; HSSFCell loginCell = null; int loginRowCount = 0; loginRow = loginSheet.createRow(loginRowCount); for (int i = 0; i < sortDTypeNames.size(); i++) { TargetDetailSetting dtSetting = sortDTypeNames.get(i); TargetDetailEntity dtEn = dTargetEns.stream() .filter(e -> dtSetting.getDTypeName().equals(e.getdTypeName())) .findFirst().get(); loginCell = loginRow.createCell(i); loginCell.setCellValue(dtEn.getdName()); loginCell.setCellStyle(centerHeadStyle); } loginRowCount++; for (Map<String, Object> map : platformDetails) { loginRow = loginSheet.createRow(loginRowCount); for (int i = 0; i < sortDTypeNames.size(); i++) { TargetDetailSetting dtSetting = sortDTypeNames.get(i); String colName = dtSetting.getDTypeName(); loginCell = loginRow.createCell(i); loginCell.setCellValue(map.get(colName) + ""); loginCell.setCellStyle(centerStyle); } loginRowCount++; } } } //处理课程信息 Optional<RangeRetVo> courseOp = courseInfo.stream() .filter(e -> self.getUserId().equals(e.getUserId())).findFirst(); if (courseOp.isPresent()) { headRowOffset = 1; headCellOffset = sheet.getRow(headRowOffset).getLastCellNum(); RangeRetVo vo = courseOp.get(); List<TargetSetting> courseSetting = sortedMap.get(ReportConts.COURSE_RANGE); row = sheet.getRow(headRowOffset); if (row == null) { row = sheet.createRow(headRowOffset); } cell = row.createCell(headCellOffset); cell.setCellValue("333"); cell.setCellStyle(centerHeadStyle); sheet.addMergedRegion(new CellRangeAddress(1, 4, headCellOffset, headCellOffset)); headCellOffset++; List<Map<String, Object>> courseDetails = new ArrayList<>(); for (TargetResult res : vo.getTargetResults()) { Optional<TargetEntity> tEn = targetEns.stream() .filter(e -> e.getTypeName().equals(res.getTypeName())).findFirst(); if (tEn.isPresent()) { TargetEntity t = tEn.get(); row = sheet.getRow(headRowOffset); if (row == null) { row = sheet.createRow(headRowOffset); } cell = row.createCell(headCellOffset); cell.setCellValue(t.getName()); cell.setCellStyle(rightStyle); cell = row.createCell(headCellOffset + 1); if (reportProcessorFactory.isDuration(res.getTypeName())) { Long d = Long.parseLong(res.getTargetVal() + ""); String val = ReportProcessorFactory.durationFormat(d); //时间转换，换成 时:分:秒(0.0分钟/0.0课时) double minute = 0.0; double courseHour = 0.0; try { minute = BigDecimalUtil.divFloor(String.valueOf(d),"60",1); courseHour = BigDecimalUtil.divFloor(minute,45.0,1); } catch (IllegalAccessException e) { e.printStackTrace(); } val = val +"(" + minute + "分钟/"+ courseHour + "课时" +")"; cell.setCellValue(val); } else { cell.setCellValue(res.getTargetVal() + ""); } cell.setCellStyle(leftStyle); headRowOffset++; headRowOffset = headRowOffset % 5 == 0 ? 1 : headRowOffset % 5; headCellOffset = headRowOffset == 1 ? headCellOffset + 2 : headCellOffset; if (!res.getDetails().isEmpty()) { courseDetails.addAll(res.getDetails()); } } } if (courseDetails.size() > 0) { //根据courseId去重 courseDetails = courseDetails.stream() .collect(Collectors.collectingAndThen(Collectors.toCollection( // 利用 TreeSet 的排序去重构造函数来达到去重元素的目的 () -> new TreeSet<>( Comparator.comparing(e -> e.get("COURSE_ID").toString()))), ArrayList::new)); List<TargetDetailSetting> dTypeNames = new ArrayList<>(); for (TargetSetting tSetting : courseSetting) { for (TargetDetailSetting dtSetting : tSetting.getDetailSettings()) { Optional<TargetDetailSetting> op = dTypeNames.stream() .filter(e -> dtSetting.getDTypeName().equals(e.getDTypeName())) .findFirst(); if (!op.isPresent()) { dTypeNames.add(dtSetting); } } } List<TargetDetailSetting> sortDTypeNames = dTypeNames.stream() .sorted((o1, o2) -> { if (o1.getDTargetOrder() >= o2.getDTargetOrder()) { return 1; } return 0; }).collect(Collectors.toList()); row = sheet.createRow(rowCount); cell = row.createCell(0); cell.setCellValue("课程名称"); cell.setCellStyle(centerHeadStyle); for (int i = 0; i < sortDTypeNames.size(); i++) { TargetDetailSetting dtSetting = sortDTypeNames.get(i); TargetDetailEntity dtEn = dTargetEns.stream() .filter(e -> dtSetting.getDTypeName().equals(e.getdTypeName())) .findFirst().get(); cell = row.createCell(i + 1); cell.setCellValue(dtEn.getdName()); cell.setCellStyle(centerHeadStyle); } rowCount++; //根据类别分组 Map<Integer, List<Map<String, Object>>> categoryDetail = courseDetails.stream() .collect( Collectors.groupingBy(e -> (int) e.get(ReportConts.CATEGORY_TYPE))); //根据类别依次展示 for (Entry<Integer, List<Map<String, Object>>> en : categoryDetail.entrySet()) { int categoryType = en.getKey(); List<Map<String, Object>> typeDetail = en.getValue(); if (categoryType == ReportConts.CATEGORY_BASE) { row = sheet.createRow(rowCount); cell = row.createCell(0); cell.setCellValue("444"); cell.setCellStyle(centerHeadStyle); rowCount++; } else if (categoryType == ReportConts.CATEGORY_PROFESSION) { row = sheet.createRow(rowCount); cell = row.createCell(0); cell.setCellValue("555"); cell.setCellStyle(centerHeadStyle); rowCount++; } else if (categoryType == ReportConts.CATEGORY_PRACTICE) { row = sheet.createRow(rowCount); cell = row.createCell(0); cell.setCellValue("666"); cell.setCellStyle(centerHeadStyle); rowCount++; } for (Map<String, Object> map : typeDetail) { row = sheet.createRow(rowCount); cell = row.createCell(0); cell.setCellValue(map.get("COURSE_NAME") + ""); cell.setCellStyle(centerStyle); for (int i = 0; i < sortDTypeNames.size(); i++) { TargetDetailSetting dtSetting = sortDTypeNames.get(i); String colName = dtSetting.getDTypeName(); cell = row.createCell(i + 1); if (colName.equals(ReportConts.D_LEARN_DURATION) || colName .equals(ReportConts.D_COURSE_DURATION)) { Long d = Long.parseLong(map.get(colName) + ""); String val = ReportProcessorFactory.durationFormat(d); cell.setCellValue(val); } else { cell.setCellValue(map.get(colName) + ""); } cell.setCellStyle(centerStyle); } rowCount++; } } } } File file = new File(baseDir, filter.getReportId() + "-u-" + self.getUserName() + "-" + self.getUserId() + "-" + +timestamp + ".xls"); try (FileOutputStream out = new FileOutputStream(file)) { out.flush(); wb.write(out); } catch (IOException e) { e.printStackTrace(); } } - 改造后逻辑不变，采用XSSFWorkbook工具类，基于其比HSSFWorkbook可执行行数更多的优势进行写入，虽然慢但是突破了65535行数限制----结果：上线后该功能直接在大数据量情况下假死（4w行+），且后期依旧很慢，导出接近40分钟
if(null==xssfWorkbook) { xssfWorkbook = new XSSFWorkbook(); sheet = xssfWorkbook.createSheet("111"); }else{ sheet = sxssfWorkbook.getSheet("111"); createRows = sheet.getLastRowNum()+1; rowCount = sheet.getLastRowNum()+1; } -
发现上述两种方法的问题：1.上诉两种方法是查询大批量的数据写入一个临时的集合（数据量大集合也大），长期持有因为写入表格比较慢导致不释放。2.HSSFWorkbook和XSSFWorkbook都是占用当前的写入内存，两者结合导致内存爆满直接挂掉
解决办法：
1.采用SXSSFWorkbook工具类，优点之一在于可以设置将写入文件临时写入本地磁盘减少内存占用率，且可以对临时文件进行压缩保持大小
//设置可读文件的大小为300行数据 其余全部写入临时磁盘保存不占用内存
SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(300);
//设置是否可压缩--此处会影响导出效率
sxssfWorkbook.setCompressTempFiles(true);2.本地调试后减少每次查询数据规模，多批次写入减少临时集合大小及时释放---原有为200组，现优化为50组上下
3.经沟通后，减少合并行列的操作，进行常规的数据逐行写入的方式
4.优化结果：导出excel在4w+行速度在3-10分钟 应该还有压测空间，希望小伙伴有好的建议哦。。
PDF循环写入分页--后续相关需求直接在此上更新
//1.在外层声明一个document进行不断写入和接收
Document document = null;
//2.写入初始数据和表头后将document保持开启状态
documentDetail = new Document();
PdfArchiveUtil.setDocumentPageSize(documentDetail, sortedMap, true);
PdfWriter pdfWriterDetail = PdfWriter.getInstance(documentDetail, new FileOutputStream(f));
documentDetail.open();// 打开文档
//3.用声明的document进行循环写入接收，内层是一个一个的PdfPTable写入document内
PdfPTable selfTable = new PdfPTable(7);
PdfPCell cell = new PdfPCell();
selfTable.addCell(c);
//4.关于分页--不管老数据的显示问题情况下直接开新的一页进行写入
document.newPage();
//5.空行问题--只有设置了行间距后Chunk.NEWLINE才会生效
PdfWriter writer = PdfWriter.getInstance(document, new FileOutputStream(f));
//设置行间距
writer.setInitialLeading(10);
document.add(Chunk.NEWLINE);
document.add(Chunk.NEWLINE);
//6.写入效率问题--大概2000+页以上的数据写入10分钟。不知道慢不慢哈哈
突然发现还是一些场景和大数量的情况下还是得多考虑，还是得继续学习！！！
上一篇： TODO：Node.js pm2使用方法
下一篇： JDK里的设计模式