Java 利用poi实现大数据量报表导出
项目中遇到了数据导出excel的需求，系统原有的导出功能实现是利用excel文件能够另存为html文件，通过拼接html形式文件去实现excel报表的导出。但是实际使用遇到了一个问题，当数据量大的时候，服务OOM，为此重新开发导出功能。
最终确定实现方案：使用POI对报表分批次查询并导出多个excel，最终对excel文件进行压缩打包导出。
excel分批查询导出（用jdbc实时查询数据（原有导出已封装了方法，这里不展示具体实现））
1 public void excelDownLoad(String reportUuid, 2 HttpServletRequest request, 3 HttpServletResponse response) throws IOException{ 4 8 // 总行数 9 long allRowNumbers = 0; 10 // 每页最大行数量 11 long rowMaxCount = 50000; 12 13 // 查询报表信息 14 Report report = this.reportMetaService.getReportByKey(reportUuid); 15 // 报表显示字段转换json字符串 16 String columnStr = report.getMetaColumns(); 17 JSONArray cj = JSON.parseArray(columnStr); 18 19 // excel头标题 20 List<String> cNames = new ArrayList<>(cj.size()); 21 // 报表sql查询返回map的key 22 List<String> eNames = new ArrayList<>(cj.size()); 23 for(int i = 0; i<cj.size(); i++){ 24 cNames.add(cj.getJSONObject(i).getString("text")); 25 eNames.add(StringUtils.upperCase(cj.getJSONObject(i).getString("name"))); 26 } 27 28 // 处理查询参数 29 Map<?,?> parameters = request.getParameterMap(); 30 Map<String, Object> formParams = this.reportMetaService.getFormParameters(report.getDatasetId(), parameters); 31 32 // 拼接查询参数，获取报表查询sql 33 String executeSql = this.reportMetaService.getExecuteSqlText(report.getSqlText(), formParams); 34 35 //报表总行数 36 allRowNumbers = this.reportMetaService.getMetaDataCount(report.getTenantId(), report.getDatasourceCode(), executeSql); 37 38 // 存放查询结果 39 List list = new ArrayList(); 40 String reportName = report.getReportName(); 41 //1.设置压缩包名称 42 String filename = reportName.concat(".zip"); 43 try { 44 filename = new String(filename.getBytes("GBK"), "iso-8859-1"); 45 } catch (UnsupportedEncodingException e) { 46 e.printStackTrace(); 47 } 48 response.reset(); 49 response.setContentType("application/octet-stream;charset=UTF-8"); 50 response.setHeader("Content-Disposition", "attachment;filename=" + filename); 51 response.addHeader("pargam", "no-cache"); 52 response.addHeader("Cache-Control", "no-cache"); 53 54 //2.设置批次文件名 55 String fileSuff = ""; 56 SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss"); 57 fileSuff = sdf.format(new Date()); 58 String fileName = reportName.concat(fileSuff); 59 //存放生成的文件名称 60 List<String> fileNames = new ArrayList<String>(); 61 // String filePath = "C:/files/"; // 本地存放文件地址，用来测试，上线需切换linux地址 62 String filePath = "/tmp/report"; 63 if (!new File(filePath).exists()) { 64 new File(filePath).mkdirs(); 65 } 66 File zip = new File(filePath + fileName + ".zip"); //压缩文件路径 67 68 //3.分批次生成excel 69 long tempsize = (allRowNumbers % rowMaxCount) == 0 ? allRowNumbers / rowMaxCount : allRowNumbers / rowMaxCount + 1; // 分批数量 70 long lineNum = 0; // 查询起始行 71 String querySql; // 执行查询sql 72 for (int i = 0; i < tempsize; i++) { 73 lineNum = i * rowMaxCount; 74 75 // 获取分批查询sql 76 querySql = this.getLimitSql(executeSql, lineNum, rowMaxCount); 77 // 执行查询sql 78 ReportDataSource reportDataSource = this.reportMetaService.getReportDataSource(report.getTenantId(), report.getDatasourceCode()); 79 // 查询结果转换list 80 Map map = QueryerFactory.create(reportDataSource).getMetaDataMap(false, querySql); 81 list = (ArrayList) map.get("DATA"); 82 querySql = null; 83 84 //生成excel 85 String tempExcelFile = fileName + "[" + (i + 1) + "].xlsx"; 86 fileNames.add(tempExcelFile); 87 FileOutputStream fos = new FileOutputStream(tempExcelFile); 88 // 内存中限制行数，超过设置值则最早的行会输出到磁盘并在内存删除 89 int rowMemory = 100; 90 SXSSFWorkbook wb = new SXSSFWorkbook(rowMemory); 91 try { 92 wb = exportDataToExcelXLSX(wb, list, cNames, eNames); // 设置excel样式和值 93 wb.write(fos); 94 fos.flush(); 95 fos.close(); 96 } catch (RuntimeException runMsg) { 97 logger.error("RuntimeException:{}", runMsg); 98 } finally { 99 fos.flush(); 100 fos.close(); 101 //手动清除list 102 list.clear(); 103 cj.clear(); 104 map.clear(); 105 } 106 } 107 //4.导出zip压缩文件 108 exportZip(response, fileNames, zip); 109 110 cNames.clear(); 111 eNames.clear(); 115 }
设置excel样式和内容
private static SXSSFWorkbook exportDataToExcelXLSX(SXSSFWorkbook wb, List<Map> listMap, List<String> assetHeadTemp, List<String> assetNameTemp){
SXSSFSheet sheet = null;
CellStyle columnHeadStyle = wb.createCellStyle();
columnHeadStyle.setBorderBottom(BorderStyle.THIN); // 下边框
columnHeadStyle.setBorderLeft(BorderStyle.THIN); // 左边框
columnHeadStyle.setBorderRight(BorderStyle.THIN); // 右边框
columnHeadStyle.setBorderTop(BorderStyle.THIN); // 上边框
// 文字位置-居中
columnHeadStyle.setAlignment(HorizontalAlignment.CENTER);
columnHeadStyle.setVerticalAlignment(VerticalAlignment.CENTER);
columnHeadStyle.setWrapText(true);
Font f = wb.createFont();// 字体
f.setFontHeightInPoints((short) 9); // 字号
f.setBold(true); // 加粗
columnHeadStyle.setFont(f);
SXSSFRow row;
SXSSFCell cell;
sheet = wb.createSheet("sheet");
row = sheet.createRow(0);
sheet.createFreezePane(0, 1, 0, 1);
// 设置excel标题样式和内容
for(int i=0;i<assetHeadTemp.size();i++){
cell = row.createCell(i);
cell.setCellStyle(columnHeadStyle);
cell.setCellValue(assetHeadTemp.get(i));
sheet.setColumnWidth(i, (int)7000);
}
// 遍历设置每行的内容
if(listMap != null && listMap.size() > 0){
int rowIndex = 1;
for(Map map : listMap){
row = sheet.createRow(rowIndex++);
int index = 0;
for(int i=0;i<assetNameTemp.size();i++ ){
cell = row.createCell(index++);
cell.setCellValue(map.get(assetNameTemp.get(i))!=null ?map.get(assetNameTemp.get(i)).toString():"");
}
}
}
return wb;
}
文件压缩并导出
private void exportZip(HttpServletResponse response, List<String> fileNames, File zip)
throws FileNotFoundException, IOException {
OutputStream outPut = response.getOutputStream();
//1.压缩文件
File srcFile[] = new File[fileNames.size()];
for (int i = 0; i < fileNames.size(); i++) {
srcFile[i] = new File(fileNames.get(i));
}
byte[] byt = new byte[1024];
ZipOutputStream out = new ZipOutputStream(new FileOutputStream(zip));
// out.setEncoding("UTF-8");
for (int i = 0; i < srcFile.length; i++) {
FileInputStream in = new FileInputStream(srcFile[i]);
out.putNextEntry(new ZipEntry(srcFile[i].getName()));
int length;
while((length=in.read(byt)) > 0){
out.write(byt,0,length);
}
out.closeEntry();
in.close();
}
out.close();
//2.删除服务器上的临时文件(excel)
for (int i = 0; i < srcFile.length; i++) {
File temFile = srcFile[i];
if(temFile.exists() && temFile.isFile()){
temFile.delete();
}
}
//3.返回客户端压缩文件
FileInputStream inStream = new FileInputStream(zip);
byte[] buf = new byte[4096];
int readLenght;
while((readLenght = inStream.read(buf)) != -1 ){
outPut.write(buf,0,readLenght);
}
inStream.close();
outPut.close();
//4.删除压缩文件
if(zip.exists() && zip.isFile()){
zip.delete();
}
}
拼接分批查询sql
private String getLimitSql(final String sql, long start, long limit){
StringBuilder stringBuilder = new StringBuilder(sql.length() + 40);
stringBuilder.append(sql);
stringBuilder.append(" limit ");
stringBuilder.append(start);
stringBuilder.append(", ");
stringBuilder.append(limit);
return stringBuilder.toString();
}
注：测试中可通过jdk提供的jconsole去连接进程，去监控程序执行的内存、cpu等使用情况，方便优化。
浙公网安备 33010602011771号