吾将上下而求索……

博客园 首页 新随笔 联系 订阅 管理
POI导出大量数据的简单解决方案

说明：我的电脑 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

 1 package bean;
 2 /**
 3  * 
 4  * @author 
 5  *
 6  */
 7 public class Person {
 8 
 9     private Integer id;
10     private String name;
11     private String address;
12     private String tel;
13     private Double money=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.money=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 
10  *
11  */
12 public class PersonService {
13     public static List getPerson(){
14         List<Person> list =new ArrayList<Person>();
15         for(int i=0;i<100320;i++){
16             list.add(new Person(i,"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         super.destroy(); // Just puts "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         SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
 52         String f = "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             toExcel(list,request,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             response.setContentType("application/octet-stream;charset=UTF-8");
 76             response.setHeader("Content-Disposition", "attachment;filename="
 77                     + java.net.URLEncoder.encode(this.fileName, "UTF-8")
 78                     + ".zip");
 79             response.addHeader("Pargam", "no-cache");
 80             response.addHeader("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         File zip = new File(request.getRealPath("/files") + "/" + f + ".zip");// 压缩文件
 98         // 生成excel
 99         for (int j = 0, n = list.size() / length + 1; j < n; j++) {
100             Workbook book = new HSSFWorkbook();
101             Sheet sheet = book.createSheet("person");
102 
103             double d = 0;// 用来统计
104             String file = request.getRealPath("/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                 // CellRangeAddress(list.size()+1,0,list.size()+5,6));
114                 Row row = sheet.createRow(0);
115                 row.createCell(0).setCellValue("ID");
116                 row.createCell(1).setCellValue("NAME");
117                 row.createCell(2).setCellValue("ADDRESS");
118                 row.createCell(3).setCellValue("TEL");
119                 row.createCell(4).setCellValue("Money");
120 
121                 int m = 1;
122 
123                 for (int i = 1, min = (list.size() - j * length + 1) > (length + 1) ? (length + 1)
124                         : (list.size() - j * length + 1); i < min; i++) {
125                     m++;
126                     Person user = list.get(length * (j) + i - 1);
127                     Double dd = user.getMoney();
128                     if (dd == null) {
129                         dd = 0.0;
130                     }
131                     d += dd;
132                     row = sheet.createRow(i);
133                     row.createCell(0).setCellValue(user.getId());
134                     row.createCell(1).setCellValue(user.getName());
135                     row.createCell(2).setCellValue(user.getAddress());
136                     row.createCell(3).setCellValue(user.getTel());
137                     row.createCell(4).setCellValue(dd);
138 
139                 }
140                 CellStyle cellStyle2 = book.createCellStyle();
141                 cellStyle2.setAlignment(CellStyle.ALIGN_CENTER);
142                 row = sheet.createRow(m);
143                 Cell cell0 = row.createCell(0);
144                 cell0.setCellValue("Total");
145                 cell0.setCellStyle(cellStyle2);
146                 Cell cell4 = row.createCell(4);
147                 cell4.setCellValue(d);
148                 cell4.setCellStyle(cellStyle2);
149                 sheet.addMergedRegion(new CellRangeAddress(m, m, 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         for (int i = 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         byte[] buf = new byte[4096];
169         int readLength;
170         while (((readLength = inStream.read(buf)) != -1)) {
171             out.write(buf, 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         byte[] buf = new byte[1024];
19         try {
20             ZipOutputStream out = new ZipOutputStream(new FileOutputStream(
21                     zipfile));
22             for (int i = 0; i < srcfile.length; i++) {
23                 FileInputStream in = new FileInputStream(srcfile[i]);
24                 out.putNextEntry(new ZipEntry(srcfile[i].getName()));
25                 int len;
26                 while ((len = in.read(buf)) > 0) {
27                     out.write(buf, 0, len);
28                 }
29                 out.closeEntry();
30                 in.close();
31             }
32             out.close();
33         } catch (IOException e) {
34             e.printStackTrace();
35         }
36     }
37 }

优化：（部分人观点，参考）
---
poi大数据分页
大数据量并发的问题，会出现卡死；
poi3.8可以限制内存释放，但是不支持2003xls格式；
压缩包导出最好；
--
大数据量的导入导出数据一般使用文本格式的中介存储，根本没必要导出到excel；
也应该分页读出写入excel，而不是一次性从数据库里读出20W数据；

 

posted on 2013-11-08 20:55  sun&flower  阅读(2292)  评论(0)  编辑  收藏  举报