POI导出大量数据的简略解决方案(附源码)

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

package bean;/** *  * @author http://javaflex.iteye.com/ * */public class Person {private Integer id;private String name;private String address;private String tel;private Double money=0.0;public Double getMoney() {return money;}public void setMoney(Double money) {this.money = money;}public Person(Integer id, String name, String address, String tel,Double money) {super();this.id = id;this.name = name;this.address = address;this.tel = tel;this.money=money;}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getAddress() {return address;}public void setAddress(String address) {this.address = address;}public String getTel() {return tel;}public void setTel(String tel) {this.tel = tel;}}

第二、PersonService模拟业务逻辑循环生成100023个Person对象

package service;import java.util.ArrayList;import java.util.List;import bean.Person;/** *  * @author http://javaflex.iteye.com/ * */public class PersonService {public static List getPerson(){List&lt;Person&gt; list =new ArrayList&lt;Person&gt;();for(int i=0;i&lt;100320;i++){list.add(new Person(i,"zhangsan"+i,"北京"+i,"13214587632",123123.12+i));}return list;}}

?第三、业务处理Servlet

package servlet;import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;import java.io.OutputStream;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.List;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.CellRangeAddress;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import bean.Person;import service.PersonService;/** *  * @author http://javaflex.iteye.com/ * */public class PersonServlet extends HttpServlet {private String fileName;public PersonServlet() {super();}public void destroy() {super.destroy(); // Just puts "destroy" string in log// Put your code here}public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {// 文件名获取Date date = new Date();SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");String f = "Person-" + format.format(date);this.fileName = f;setResponseHeader(response);OutputStream out = null;try {out = response.getOutputStream();List&lt;Person&gt; list = PersonService.getPerson();toExcel(list,request,10000,f,out);} catch (IOException e1) {e1.printStackTrace();} finally {try {out.flush();out.close();} catch (IOException e) {e.printStackTrace();}}}/** 设置响应头 */public void setResponseHeader(HttpServletResponse response) {try {response.setContentType("application/octet-stream;charset=UTF-8");response.setHeader("Content-Disposition", "attachment;filename="+ java.net.URLEncoder.encode(this.fileName, "UTF-8")+ ".zip");response.addHeader("Pargam", "no-cache");response.addHeader("Cache-Control", "no-cache");} catch (Exception ex) {ex.printStackTrace();}}public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {doGet(request, response);}public void init() throws ServletException {// Put your code here}public void toExcel(List&lt;Person&gt; list, HttpServletRequest request,int length, String f, OutputStream out) throws IOException {List&lt;String&gt; fileNames = new ArrayList();// 用于存放生成的文件名称sFile zip = new File(request.getRealPath("/files") + "/" + f + ".zip");// 压缩文件// 生成excelfor (int j = 0, n = list.size() / length + 1; j &lt; n; j++) {Workbook book = new HSSFWorkbook();Sheet sheet = book.createSheet("person");double d = 0;// 用来统计String file = request.getRealPath("/files") + "/" + f + "-" + j+ ".xls";fileNames.add(file);FileOutputStream o = null;try {o = new FileOutputStream(file);// sheet.addMergedRegion(new// CellRangeAddress(list.size()+1,0,list.size()+5,6));Row row = sheet.createRow(0);row.createCell(0).setCellValue("ID");row.createCell(1).setCellValue("NAME");row.createCell(2).setCellValue("ADDRESS");row.createCell(3).setCellValue("TEL");row.createCell(4).setCellValue("Money");int m = 1;for (int i = 1, min = (list.size() - j * length + 1) &gt; (length + 1) ? (length + 1): (list.size() - j * length + 1); i &lt; min; i++) {m++;Person user = list.get(length * (j) + i - 1);Double dd = user.getMoney();if (dd == null) {dd = 0.0;}d += dd;row = sheet.createRow(i);row.createCell(0).setCellValue(user.getId());row.createCell(1).setCellValue(user.getName());row.createCell(2).setCellValue(user.getAddress());row.createCell(3).setCellValue(user.getTel());row.createCell(4).setCellValue(dd);}CellStyle cellStyle2 = book.createCellStyle();cellStyle2.setAlignment(CellStyle.ALIGN_CENTER);row = sheet.createRow(m);Cell cell0 = row.createCell(0);cell0.setCellValue("Total");cell0.setCellStyle(cellStyle2);Cell cell4 = row.createCell(4);cell4.setCellValue(d);cell4.setCellStyle(cellStyle2);sheet.addMergedRegion(new CellRangeAddress(m, m, 0, 3));} catch (Exception e) {e.printStackTrace();}try {book.write(o);} catch (Exception ex) {ex.printStackTrace();} finally {o.flush();o.close();}}File srcfile[] = new File[fileNames.size()];for (int i = 0, n = fileNames.size(); i &lt; n; i++) {srcfile[i] = new File(fileNames.get(i));}util.FileZip.ZipFiles(srcfile, zip);FileInputStream inStream = new FileInputStream(zip);byte[] buf = new byte[4096];int readLength;while (((readLength = inStream.read(buf)) != -1)) {out.write(buf, 0, readLength);}inStream.close();}}

最后还有个工具类package util;

import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;import java.util.zip.ZipEntry;import java.util.zip.ZipOutputStream;/** *  * @author http://javaflex.iteye.com/ * */public class FileZip {/** *  * @param srcfile 文件名数组 * @param zipfile 压缩后文件 */public static void ZipFiles(java.io.File[] srcfile, java.io.File zipfile) {byte[] buf = new byte[1024];try {ZipOutputStream out = new ZipOutputStream(new FileOutputStream(zipfile));for (int i = 0; i &lt; srcfile.length; i++) {FileInputStream in = new FileInputStream(srcfile[i]);out.putNextEntry(new ZipEntry(srcfile[i].getName()));int len;while ((len = in.read(buf)) &gt; 0) {out.write(buf, 0, len);}out.closeEntry();in.close();}out.close();} catch (IOException e) {e.printStackTrace();}}}

OK全部内容完成

POI导出大量数据的简略解决方案(附源码)

12.8M的excel内容压缩后2.68M，给力吧

以后记得代码加注释

亲，记得给个评论哦 POI导出大量数据的简略解决方案(附源码)

List<Person> list = PersonService.getPerson(); toExcel(list,request,10000,f,out);

代码不够优雅，应分页读出写入excel,而不是一次性从数据库里读出20W数据

大数据量的导入导出数据一般使用文本格式的中介存储,根本没必要放到excel。

List<Person> list = PersonService.getPerson(); toExcel(list,request,10000,f,out);

代码不够优雅，应分页读出写入excel,而不是一次性从数据库里读出20W数据

大数据量的导入导出数据一般使用文本格式的中介存储,根本没必要放到excel。

这个地方确实应该分页导出

4 楼 yn5411 2011-11-18 如果导出XLS，大数话都必须这样分文件做。 5 楼红旗无风不飘 2012-06-12 不知道你这个效率怎么样，如果创建多个Sheet是不是会好一些

POI导出大量数据的简略解决方案(附源码

热点推荐