public void exportBulldingRoomList(TaskCustomSearchQo taskCustomSearchQo, HttpServletResponse response) throws Exception { String fileName = "数据采集统计表"; response.setContentType("application/application/vnd.ms-excel"); response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8")); String status = String.valueOf(taskCustomSearchQo.getStatus()); taskCustomSearchQo.setOffset(0); int limit=taskCustomSearchQo.getLimit(); if (limit==0){ limit=50; } taskCustomSearchQo.setLimit(200000); Integer userId = getUserId(); if (userId == null) { return; } taskCustomSearchQo.setUserId(userId); List<TaskCustomVo> taskCustomVoParentList = bulldingRoomService.exportTaskCustomList(taskCustomSearchQo); String count=taskCustomSearchQo.getIsCount(); if (StringUtils.isNotBlank(count)){ if ("1".equals(count)){ taskCustomVoParentList= getOne(taskCustomVoParentList); } } List<TaskCustomVo> taskCustomVoList = new ArrayList<TaskCustomVo>(); if (status!=null&&"4".equals(status)){ List<Integer> taskids=new ArrayList<Integer>() ; for (TaskCustomVo taskCustomVo : taskCustomVoParentList) { taskids.add(taskCustomVo.getTaskTemplateId()); } taskCustomSearchQo.setTaskids(taskids); List<TaskCustomVo> taskCustomVoChildList = bulldingRoomService.queryAllChildFinishTaskCustomList(taskCustomSearchQo); if (StringUtils.isNotBlank(count)){ if ("1".equals(count)){ taskCustomVoChildList=getOne(taskCustomVoChildList); } } for (TaskCustomVo taskCustomVo2 : taskCustomVoParentList) { taskCustomVoList.add(taskCustomVo2); for (TaskCustomVo taskCustomVo3 : taskCustomVoChildList) { if (taskCustomVo2.getTaskTemplateId()==taskCustomVo3.getParentId()){ taskCustomVoList.add(taskCustomVo3); } } } }else{ taskCustomVoList=taskCustomVoParentList; } //根据动态获取列表头名称加载相对的动态列数据 taskCustomVoList = getFromData(status, taskCustomVoList, taskCustomSearchQo); OutputStream outputStream = response.getOutputStream();// 打开流 // 声明一个工作薄 SXSSFWorkbook wb = new SXSSFWorkbook(100); //临时文件进行压缩,建议不要true,否则会影响导出时间 wb.setCompressTempFiles(false); int tatol = taskCustomVoList.size() - 1; int pagerow = 50000; int pages = tatol / pagerow; if (tatol < 50000) { //声明一个单子并命名 SXSSFSheet sheet = wb.createSheet("数据采集统计表"); sheet.setColumnWidth(1, 40 * 256); sheet.setColumnWidth(2, 30 * 256); //给单子名称一个长度 sheet.setDefaultColumnWidth((short) 15); // 生成一个样式 CellStyle style = wb.createCellStyle(); //创建第一行(也可以称为表头) SXSSFRow row = sheet.createRow(0); //样式字体居中 style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //给表头第一行一次创建单元格 SXSSFCell cell = row.createCell((short) 0); cell.setCellValue("任务编码"); cell.setCellStyle(style); cell = row.createCell((short) 1); cell.setCellValue("任务名称"); cell.setCellStyle(style); cell = row.createCell((short) 2); cell.setCellValue("任务所属区域"); cell.setCellStyle(style); cell = row.createCell((short) 3); cell.setCellValue("状态"); cell.setCellStyle(style); cell = row.createCell((short) 4); if (status != null && "4".equals(status)) { cell.setCellValue("完成时间"); cell.setCellStyle(style); cell = row.createCell((short) 5); cell.setCellValue("完成人"); cell.setCellStyle(style); cell = row.createCell((short) 6); cell.setCellValue("建筑种类"); cell.setCellStyle(style); cell = row.createCell((short) 7); cell.setCellValue("是否有门牌"); cell.setCellStyle(style); cell = row.createCell((short) 8); cell.setCellValue("建筑物参考定位"); cell.setCellStyle(style); cell = row.createCell((short) 9); cell.setCellValue("街路巷门牌号"); cell.setCellStyle(style); cell = row.createCell((short) 10); cell.setCellValue("备注"); cell.setCellStyle(style); } //向单元格里填充数据 for (int i = 0; i < taskCustomVoList.size(); i++) { row = sheet.createRow(i + 1); row.createCell(0).setCellValue(taskCustomVoList.get(i).getTaskTemplateId()); row.createCell(1).setCellValue(taskCustomVoList.get(i).getTaskName()); row.createCell(2).setCellValue(taskCustomVoList.get(i).getNamePath()); row.createCell(3).setCellValue(taskCustomVoList.get(i).getStatus()); if (status != null && "4".equals(status)) { row.createCell(4).setCellValue(taskCustomVoList.get(i).getFinishData()); row.createCell(5).setCellValue(taskCustomVoList.get(i).getTaskUserName()); row.createCell(6).setCellValue(taskCustomVoList.get(i).getBuildType()); row.createCell(7).setCellValue(taskCustomVoList.get(i).getHavaRoomNumber()); row.createCell(8).setCellValue(taskCustomVoList.get(i).getBuildingLocation()); row.createCell(9).setCellValue(taskCustomVoList.get(i).getRoomNumber()); row.createCell(10).setCellValue(taskCustomVoList.get(i).getRemarks()); } } } else { for (int l = 0; l < pages + 1; l++) { int rows=0; int o = l * pagerow; //声明一个单子并命名 SXSSFSheet sheet = wb.createSheet("数据采集统计表" + l); sheet.setColumnWidth(1, 40 * 256); sheet.setColumnWidth(2, 30 * 256); //给单子名称一个长度 sheet.setDefaultColumnWidth((short) 15); // 生成一个样式 CellStyle style = wb.createCellStyle(); //创建第一行(也可以称为表头) SXSSFRow row = sheet.createRow(rows); //样式字体居中 style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setWrapText(true); //给表头第一行一次创建单元格 SXSSFCell cell = row.createCell((short) 0); cell.setCellValue("任务编码"); cell.setCellStyle(style); cell = row.createCell((short) 1); cell.setCellValue("任务名称"); cell.setCellStyle(style); cell = row.createCell((short) 2); cell.setCellValue("任务所属区域"); cell.setCellStyle(style); cell = row.createCell((short) 3); cell.setCellValue("状态"); cell.setCellStyle(style); cell = row.createCell((short) 4); if (status != null && "4".equals(status)) { cell.setCellValue("完成时间"); cell.setCellStyle(style); cell = row.createCell((short) 5); cell.setCellValue("完成人"); cell.setCellStyle(style); cell = row.createCell((short) 6); cell.setCellValue("建筑种类"); cell.setCellStyle(style); cell = row.createCell((short) 7); cell.setCellValue("是否有门牌"); cell.setCellStyle(style); cell = row.createCell((short) 8); cell.setCellValue("建筑物参考定位"); cell.setCellStyle(style); cell = row.createCell((short) 9); cell.setCellValue("街路巷门牌号"); cell.setCellStyle(style); cell = row.createCell((short) 10); cell.setCellValue("备注"); cell.setCellStyle(style); } for (int p = o; p < pagerow * (l + 1); p++) { rows++; if (p < tatol) { //向单元格里填充数据 row = sheet.createRow(rows); if (p>100000){ System.out.println(taskCustomVoList.get(p).getTaskTemplateId()); } row.createCell(0).setCellValue(taskCustomVoList.get(p).getTaskTemplateId()); row.createCell(1).setCellValue(taskCustomVoList.get(p).getTaskName()); row.createCell(2).setCellValue(taskCustomVoList.get(p).getNamePath()); row.createCell(3).setCellValue(taskCustomVoList.get(p).getStatus()); if (status != null && "4".equals(status)) { row.createCell(4).setCellValue(taskCustomVoList.get(p).getFinishData()); row.createCell(5).setCellValue(taskCustomVoList.get(p).getTaskUserName()); row.createCell(6).setCellValue(taskCustomVoList.get(p).getBuildType()); row.createCell(7).setCellValue(taskCustomVoList.get(p).getHavaRoomNumber()); row.createCell(8).setCellValue(taskCustomVoList.get(p).getBuildingLocation()); row.createCell(9).setCellValue(taskCustomVoList.get(p).getRoomNumber()); row.createCell(10).setCellValue(taskCustomVoList.get(p).getRemarks()); } } if (rows % 100 == 0) { ((SXSSFSheet) sheet).flushRows(); } } } } wb.write(outputStream);// HSSFWorkbook写入流 outputStream.flush();// 刷新流 outputStream.close();// 关闭流 wb.close(); }
poi分页导出,只能解决十几万数据的导出,内存问题没解决,有方案的可以留言
最新推荐文章于 2022-12-09 14:59:57 发布