说明：我的电脑 2.0CPU 2G内存 能够十秒钟导出 20W 条数据 ,12.8M的excel内容压缩后2.68M
我们知道在POI导出Excel时，数据量大了，很容易导致内存溢出。由于Excel 一个sheet允许的最大行数是65536这时我们想到分sheet进行导出；但是这种情况也不能解决内存溢出的问题。毕竟数据还是一次性在内存中进行保存的。这时我们想是不是可以导出多个excel呢？下面我就尝试着按照导出多个excel
首先：我们要确定数据量有多大，然后确定一个excel导出多少条数据，这样就可以确定导出的Excel的数量，于是我们就可以循环的导出excel并保存在任意的临时目录中。去这样如果内存不够的话虚拟机就会去进行回收已经保存的excel在内存中的空间。
假设我们我们已经成功的生成了多个excel，这时我们怎么把这N个excel文档传到客户端呢？其实一个一个的传也未尝不可，但是考虑那样对用户来说体验不够好，再次多个文件在网络上传输也比较慢。我们可以考虑对生成的几个文件进行压缩，然后传到客户端。
总结一下第一、分批次生成excel第二、压缩后到客户端
下面我把我的一个小实例贴上供大家参考
第一、Person.java 普通javabean
Javabean代码
1. package bean;
2. /**
3. *
4. * @author http://javaflex.iteye.com/
5. *
6. */
7. public class Person {
8.
9. private Integer id;
10. private String name;
11. private String address;
12. private String tel;
13. 0.0;
14. public Double getMoney() {
15. return money;
16. }
17. public void setMoney(Double money) {
18. this.money = money;
19. }
20. public Person(Integer id, String name, String address, String tel,Double money) {
21. super();
22. this.id = id;
23. this.name = name;
24. this.address = address;
25. this.tel = tel;
26. this.mnotallow=money;
27. }
28. public Integer getId() {
29. return id;
30. }
31. public void setId(Integer id) {
32. this.id = id;
33. }
34. public String getName() {
35. return name;
36. }
37. public void setName(String name) {
38. this.name = name;
39. }
40. public String getAddress() {
41. return address;
42. }
43. public void setAddress(String address) {
44. this.address = address;
45. }
46. public String getTel() {
47. return tel;
48. }
49. public void setTel(String tel) {
50. this.tel = tel;
51. }
52. }第二、PersonService模拟业务逻辑循环生成100023个Person对象
模拟业务逻辑代码
1. package service;
2.
3. import java.util.ArrayList;
4. import java.util.List;
5.
6. import bean.Person;
7. /**
8. *
9. * @author http://javaflex.iteye.com/
10. *
11. */
12. public class PersonService {
13. public static List getPerson(){
14. List<Person> list =new ArrayList<Person>();
15. 0;i<100320;i++){
16. "zhangsan"+i,"北京"+i,"13214587632",123123.12+i));
17. }
18. return list;
19. }
20.
21. }第三、业务处理Servlet
操作servlet代码
1. package servlet;
2.
3. import java.io.File;
4. import java.io.FileInputStream;
5. import java.io.FileOutputStream;
6. import java.io.IOException;
7. import java.io.OutputStream;
8. import java.text.SimpleDateFormat;
9. import java.util.ArrayList;
10. import java.util.Date;
11. import java.util.List;
12.
13. import javax.servlet.ServletException;
14. import javax.servlet.http.HttpServlet;
15. import javax.servlet.http.HttpServletRequest;
16. import javax.servlet.http.HttpServletResponse;
17.
18. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
19. import org.apache.poi.hssf.util.CellRangeAddress;
20. import org.apache.poi.ss.usermodel.Cell;
21. import org.apache.poi.ss.usermodel.CellStyle;
22. import org.apache.poi.ss.usermodel.Row;
23. import org.apache.poi.ss.usermodel.Sheet;
24. import org.apache.poi.ss.usermodel.Workbook;
25.
26. import bean.Person;
27.
28. import service.PersonService;
29.
30. /**
31. *
32. * @author http://javaflex.iteye.com/
33. *
34. */
35. public class PersonServlet extends HttpServlet {
36. private String fileName;
37.
38. public PersonServlet() {
39. super();
40. }
41.
42. public void destroy() {
43. "destroy" string in log
44. // Put your code here
45. }
46.
47. public void doGet(HttpServletRequest request, HttpServletResponse response)
48. throws ServletException, IOException {
49. // 文件名获取
50. Date date = new Date();
51. "yyyyMMddHHmmss");
52. "Person-" + format.format(date);
53. this.fileName = f;
54. setResponseHeader(response);
55. OutputStream out = null;
56. try {
57. out = response.getOutputStream();
58. List<Person> list = PersonService.getPerson();
59. 10000,f,out);
60. } catch (IOException e1) {
61. e1.printStackTrace();
62. } finally {
63. try {
64. out.flush();
65. out.close();
66. } catch (IOException e) {
67. e.printStackTrace();
68. }
69. }
70. }
71.
72. /** 设置响应头 */
73. public void setResponseHeader(HttpServletResponse response) {
74. try {
75. "application/octet-stream;charset=UTF-8");
76. "Content-Disposition", "attachment;filename="
77. "UTF-8")
78. ".zip");
79. "Pargam", "no-cache");
80. "Cache-Control", "no-cache");
81. } catch (Exception ex) {
82. ex.printStackTrace();
83. }
84. }
85. public void doPost(HttpServletRequest request, HttpServletResponse response)
86. throws ServletException, IOException {
87.
88. doGet(request, response);
89. }
90. public void init() throws ServletException {
91. // Put your code here
92. }
93.
94. public void toExcel(List<Person> list, HttpServletRequest request,
95. int length, String f, OutputStream out) throws IOException {
96. List<String> fileNames = new ArrayList();// 用于存放生成的文件名称s
97. "/files") + "/" + f + ".zip");// 压缩文件
98. // 生成excel
99. 0, n = list.size() / length + 1; j < n; j++) {
100. Workbook book = new HSSFWorkbook();
101. "person");
102.
103. 0;// 用来统计
104. "/files") + "/" + f + "-" + j
105. ".xls";
106.
107. fileNames.add(file);
108. FileOutputStream o = null;
109. try {
110. o = new FileOutputStream(file);
111.
112. // sheet.addMergedRegion(new
113. 1,0,list.size()+5,6));
114. 0);
115. 0).setCellValue("ID");
116. 1).setCellValue("NAME");
117. 2).setCellValue("ADDRESS");
118. 3).setCellValue("TEL");
119. 4).setCellValue("Money");
120.
121. 1;
122.
123. 1, min = (list.size() - j * length + 1) > (length + 1) ? (length + 1)
124. 1); i < min; i++) {
125. m++;
126. 1);
127. Double dd = user.getMoney();
128. if (dd == null) {
129. 0.0;
130. }
131. d += dd;
132. row = sheet.createRow(i);
133. 0).setCellValue(user.getId());
134. 1).setCellValue(user.getName());
135. 2).setCellValue(user.getAddress());
136. 3).setCellValue(user.getTel());
137. 4).setCellValue(dd);
138.
139. }
140. CellStyle cellStyle2 = book.createCellStyle();
141. cellStyle2.setAlignment(CellStyle.ALIGN_CENTER);
142. row = sheet.createRow(m);
143. 0);
144. "Total");
145. cell0.setCellStyle(cellStyle2);
146. 4);
147. cell4.setCellValue(d);
148. cell4.setCellStyle(cellStyle2);
149. 0, 3));
150. } catch (Exception e) {
151. e.printStackTrace();
152. }
153. try {
154. book.write(o);
155. } catch (Exception ex) {
156. ex.printStackTrace();
157. } finally {
158. o.flush();
159. o.close();
160. }
161. }
162. File srcfile[] = new File[fileNames.size()];
163. 0, n = fileNames.size(); i < n; i++) {
164. srcfile[i] = new File(fileNames.get(i));
165. }
166. util.FileZip.ZipFiles(srcfile, zip);
167. FileInputStream inStream = new FileInputStream(zip);
168. 4096];
169. int readLength;
170. 1)) {
171. 0, readLength);
172. }
173. inStream.close();
174. }
175. }最后还有个工具类package util;
压缩工具类代码
1. import java.io.FileInputStream;
2. import java.io.FileOutputStream;
3. import java.io.IOException;
4. import java.util.zip.ZipEntry;
5. import java.util.zip.ZipOutputStream;
6. /**
7. *
8. * @author http://javaflex.iteye.com/
9. *
10. */
11. public class FileZip {
12. /**
13. *
14. * @param srcfile 文件名数组
15. * @param zipfile 压缩后文件
16. */
17. public static void ZipFiles(java.io.File[] srcfile, java.io.File zipfile) {
18. 1024];
19. try {
20. ZipOutputStream out = new ZipOutputStream(new FileOutputStream(
21. zipfile));
22. 0; i < srcfile.length; i++) {
23. FileInputStream in = new FileInputStream(srcfile[i]);
24. out.putNextEntry(new ZipEntry(srcfile[i].getName()));
25. int len;
26. 0) {
27. 0, len);
28. }
29. out.closeEntry();
30. in.close();
31. }
32. out.close();
33. } catch (IOException e) {
34. e.printStackTrace();
35. }
36. }
37. }OK全部内容完成
12.8M的excel内容压缩后2.68M，给力吧
以后记得代码加注释
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
