在工作中经常遇到对Excel的读写操作，一般情况下我们都可能会使用第三方工具例如POI，读操作时我用的也是POI，但是如果要进行写操作的时候（生成Excel文件）但凡数据量稍大一些就会导致OutOfMemoryError（内存溢出），因为它要把数据都读进内存中然后一次性写入文件。
工作中随便一个小业务的数据量就用可能导致这种情况的发生。解决这种问题的一种方式是改为生成csv格式文件，这可以边读边写，不会用内存溢出的情况，如果有那就是你程序有问题了。以前我也是用这种方式解决这个问题的，大家用的都是windows没有啥问题，可惜现在遇到了Mac，生成的csv文件在领导的Mac打开就惨不忍睹了，这个方案得pass掉。然后就有了现在这种方法，按照Excel文件的XML数据格式进行拼装，以文本形式写入以.xls结尾命名的文件中（我是以2003版Excel文档的XML格式写的工具类）。
生成Excel的java工具类
1 | package com.zzcm.fourgad.util; |
使用实例
从数据库中读取数据并生成excel文档的方法1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109package com.zzcm.fourgad.service;
import com.zzcm.fourgad.entity.DataBean;
import com.zzcm.fourgad.util.ExcelUtil;
import com.zzcm.fourgad.util.StringUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
/**
* Created by demondevil
*/
@Component
@Transactional
public class BaseService {
protected Logger logger = LoggerFactory.getLogger(this.getClass());
@Autowired
private JdbcTemplate jdbcTemplate;
/** list集合保存最大的行数 */
private static final int LIST_MAX_SIZE = 200;
/** sheet页最大的行数 */
private static final int SHEET_MAX_ROWS = 30000;
/**
* 导出Excel
* @param sql 读取数据的SQL语句
* @param fileName 文件名 如xxx.xls
* @param columnWidths 每列的宽度
* @param culomnKeys 每列获取数据的key，结合SQL的select字段
* @return 文件生成的绝对路径
*/
protected String expExcel(String sql , String fileName , int[] columnWidths , String[] culomnKeys){
StringBuffer sb = new StringBuffer();
sb.append(sql).append(" limit ?,?");
// 文件输出位置 /WEB_INF/export_files/
String filePath = this.getClass().getClassLoader().getResource("/").getPath();
filePath = filePath.substring(0,filePath.lastIndexOf("classes/")) + "export_files/" + fileName;
System.out.println(filePath);
OutputStream outStream = null;
try {
File file = new File(filePath);
if(!file.getParentFile().exists()){
file.getParentFile().mkdirs();
}
outStream = new FileOutputStream(file);
// 准备写数据了
long beginTime = System.currentTimeMillis();
System.out.println("expExcel 开始写数据--"+beginTime);
// 写Excel头信息
outStream.write(ExcelUtil.getHeader().getBytes("UTF-8"));
// Excel sheet
outStream.write(ExcelUtil.beginSheet("sheet1", columnWidths).getBytes("UTF-8"));
//获取数据
int begin = 0;
List<Map<String, Object>> list = find(sb.toString(), new Object[]{begin, LIST_MAX_SIZE});
int count = 0;
while( list != null && list.size() > 0 ){
for( Map<String, Object> map : list ){
// 写数据到文件
outStream.write(ExcelUtil.createRow(culomnKeys,map).getBytes("UTF-8"));
count++;
if( count % SHEET_MAX_ROWS == 0){ // 新建sheet页
// 写Excel 结束sheet
outStream.write(ExcelUtil.endSheet(false).getBytes("UTF-8"));
// Excel sheet
outStream.write(ExcelUtil.beginSheet("sheet"+( count / SHEET_MAX_ROWS + 1 ), columnWidths).getBytes("UTF-8"));
}
}
outStream.flush();
// 重新获取数据
begin = begin + LIST_MAX_SIZE;
list.clear();
list = null;
list = find(sb.toString(), new Object[]{begin,LIST_MAX_SIZE});
}
// 写Excel 结束sheet
outStream.write(ExcelUtil.endSheet(true).getBytes("UTF-8"));
// Excel尾信息
outStream.write(ExcelUtil.getFooter().getBytes("UTF-8"));
long endTime = System.currentTimeMillis();
System.out.println("expExcel 写完成--总行数="+count+"--耗时："+(endTime-beginTime)+"毫秒");
} catch (Exception e) {
e.printStackTrace();
}finally{
if( outStream != null ){
try {
outStream.close();
} catch (IOException e) {
e.printStackTrace();
logger.error("expExcel出错，sql="+sql,e);
}
}
}
return filePath;
}
}
具体业务的使用实例1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38/**
* 从tb_cp_ord中导出号码和idfa
* @return 文件的绝对路径
*/
public String expAdMobileIDFA(String updt, String cid,String advertising_id,int os_type) {
StringBuffer sb = new StringBuffer();
StringBuffer fileName = new StringBuffer();
sb.append("select left(updt,10) updt,cid,mobile,");
if ( os_type == 1 ){
sb.append("sn idfa");
fileName.append("ios");
}else {
sb.append("imei idfa");
fileName.append("android");
}
sb.append(" from tb_cp_ord where updt is not null and os_type=").append(os_type);
if ( advertising_id != null && !"".equals(advertising_id.trim()) ){
sb.append(" and advertising_id ='").append(advertising_id).append("'");
fileName.append("_aaz");
}else {
sb.append(" and advertising_id is null");
fileName.append("_hf");
}
if(updt != null && !updt.trim().equals("")){
sb.append(" and updt>='").append(updt.trim()).append(" 00:00:00'");
sb.append(" and updt<='").append(updt.trim()).append(" 23:59:59'");
fileName.append("_").append(updt);
}
if(cid != null && !cid.trim().equals("")){
sb.append(" and cid='").append(cid.trim()).append("'");
fileName.append("_").append(cid);
}
// 文件名称
fileName.append(".xls");
return expExcel(sb.toString(),fileName.toString(),new int[]{100,100,100,300},new String[]{"updt","cid","mobile","idfa"});
}
每个参数对应具体的实际业务，这里解释，其实就是根据参数生成不同的SQL和文件名，然后再根据实际的数据的情况设置列宽（expExcel的第三个参数），根据SQL的select要查询的字段设置每列提取数据的key（expExel的第四个参数）。
然后在controller层，用户点击下载的代码如下：1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36@RequestMapping(value = "/expAdMobileIDFA")
public String expAdMobileIDFA(HttpServletRequest request,HttpServletResponse response,String updt,String cid,String advertising_id,int os_type) throws Exception{
// 生成文件
String filePath = null;
filePath = houseService.expAdMobileIDFA(updt, cid,advertising_id,os_type);
File file = new File(filePath);
InputStream in = new FileInputStream(file);
String fileName = filePath.substring(filePath.lastIndexOf("/")+1);
// 下载
if (in != null) {
response.setCharacterEncoding("UTF-8");
// 写数据到客户端 "application/octet-stream" application/vnd.ms-excel
response.setContentType("application/octet-stream");
response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
response.setHeader("Pragma", "public");
// 设置超时时间
response.setDateHeader("Expires", (System.currentTimeMillis() + 5000));
response.addHeader("Content-Disposition",
"attachment;filename=\""+fileName+"\"");
OutputStream out = response.getOutputStream();
int len = 0;
byte[] buffer = new byte[1024];
while ((len = in.read(buffer)) != -1) {
out.write(buffer, 0, len);
out.flush();
}
out.close();
in.close();
}
//删除文件
file.delete();
return null;
}
以上都是核心代码，可根据具体实际业务修改。