Java利⽤poi实现⼤数据量报表导出
项⽬中遇到了数据导出excel的需求，系统原有的导出功能实现是利⽤excel⽂件能够另存为html⽂件，通过拼接html形式⽂件去实现excel报表的导出。但是实际使⽤遇到了⼀个问题，当数据量⼤的时候，服务OOM，为此重新开发导出功能。
最终确定实现⽅案：使⽤POI对报表分批次查询并导出多个excel，最终对excel⽂件进⾏压缩打包导出。
excel分批查询导出（⽤jdbc实时查询数据（原有导出已封装了⽅法，这⾥不展⽰具体实现））
1public void excelDownLoad(String reportUuid,
2 HttpServletRequest request,
3 HttpServletResponse response) throws IOException{
4
8// 总⾏数
9long allRowNumbers = 0;
10// 每页最⼤⾏数量
11long rowMaxCount = 50000;
12java修改html文件
13// 查询报表信息
14 Report report = ReportByKey(reportUuid);
15// 报表显⽰字段转换json字符串
16 String columnStr = MetaColumns();
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());
23for(int i = 0; i<cj.size(); i++){
24 cNames.JSONObject(i).getString("text"));
25 eNames.add(StringUtils.JSONObject(i).getString("name")));
26 }
27
28// 处理查询参数
29 Map<?,?> parameters = ParameterMap();
30 Map<String, Object> formParams = DatasetId(), parameters);
31
32// 拼接查询参数，获取报表查询sql
33 String executeSql = SqlText(), formParams);
34
35//报表总⾏数
36 allRowNumbers = TenantId(), DatasourceCode(), executeSql);
37
38// 存放查询结果
39 List list = new ArrayList();
40 String reportName = ReportName();
41//1.设置压缩包名称
42 String filename = at(".zip");
43try {
44 filename = new Bytes("GBK"), "iso-8859-1");
45 } catch (UnsupportedEncodingException e) {
46 e.printStackTrace();
47 }
48 set();
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 = at(fileSuff);
59//存放⽣成的⽂件名称
60 List<String> fileNames = new ArrayList<String>();
61// String filePath = "C:/files/"; // 本地存放⽂件地址，⽤来测试，上线需切换linux地址
62 String filePath = "/tmp/report";
63if (!new File(filePath).exists()) {
64new File(filePath).mkdirs();
65 }
66 File zip = new File(filePath + fileName + ".zip"); //压缩⽂件路径
67
68//3.分批次⽣成excel
69long tempsize = (allRowNumbers % rowMaxCount) == 0 ? allRowNumbers / rowMaxCount : allRowNumbers / rowMaxCount + 1; // 分批数量
70long lineNum = 0; // 查询起始⾏
71 String querySql; // 执⾏查询sql
72for (int i = 0; i < tempsize; i++) {
73 lineNum = i * rowMaxCount;
75// 获取分批查询sql
76 querySql = LimitSql(executeSql, lineNum, rowMaxCount);
77// 执⾏查询sql
78 ReportDataSource reportDataSource = TenantId(), DatasourceCode());
79// 查询结果转换list
80 Map map = ate(reportDataSource).getMetaDataMap(false, querySql);
81 list = (ArrayList) ("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// 内存中限制⾏数，超过设置值则最早的⾏会输出到磁盘并在内存删除
89int rowMemory = 100;
90 SXSSFWorkbook wb = new SXSSFWorkbook(rowMemory);
91try {
92 wb = exportDataToExcelXLSX(wb, list, cNames, eNames); // 设置excel样式和值
93 wb.write(fos);
94 fos.flush();
95 fos.close();
96 } catch (RuntimeException runMsg) {
97 ("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 = ateRow(0);
// 设置excel标题样式和内容
for(int i=0;i<assetHeadTemp.size();i++){
cell = ateCell(i);
cell.setCellStyle(columnHeadStyle);
cell.(i));
sheet.setColumnWidth(i, (int)7000);
}
// 遍历设置每⾏的内容
if(listMap != null && listMap.size() > 0){
int rowIndex = 1;
for(Map map : listMap){
row = ateRow(rowIndex++);
int index = 0;
for(int i=0;i<assetNameTemp.size();i++ ){
cell = ateCell(index++);
cell.((i))!=null ?((i)).toString():"");
}
}
return wb;
}
⽂件压缩并导出
private void exportZip(HttpServletResponse response, List<String> fileNames, File zip)
throws FileNotFoundException, IOException {
OutputStream outPut = OutputStream();
//1.压缩⽂件
File srcFile[] = new File[fileNames.size()];
for (int i = 0; i < fileNames.size(); i++) {
srcFile[i] = new (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((ad(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];
ists() && temFile.isFile()){
temFile.delete();
}
}
//3.返回客户端压缩⽂件
FileInputStream inStream = new FileInputStream(zip);
byte[] buf = new byte[4096];
int readLenght;
while((readLenght = ad(buf)) != -1 ){
outPut.write(buf,0,readLenght);
}
inStream.close();
outPut.close();
//4.删除压缩⽂件
ists() && 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);
String();
}
注：测试中可通过jdk提供的jconsole去连接进程，去监控程序执⾏的内存、cpu等使⽤情况，⽅便优化。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论