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<Person> list =new ArrayList<Person>();
		for(int i=0;i<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<Person> 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<Person> list, HttpServletRequest request,
			int length, String f, OutputStream out) throws IOException {
		List<String> fileNames = new ArrayList();// 用于存放生成的文件名称s
		File zip = new File(request.getRealPath("/files") + "/" + f + ".zip");// 压缩文件
		// 生成excel
		for (int j = 0, n = list.size() / length + 1; j < 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) > (length + 1) ? (length + 1)
						: (list.size() - j * length + 1); i < 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 < 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 < srcfile.length; i++) {
				FileInputStream in = new FileInputStream(srcfile[i]);
				out.putNextEntry(new ZipEntry(srcfile[i].getName()));
				int len;
				while ((len = in.read(buf)) > 0) {
					out.write(buf, 0, len);
				}
				out.closeEntry();
				in.close();
			}
			out.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}

OK全部内容完成

 

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

以后记得代码加注释

 

亲，记得给个评论哦

评论

19 楼 liangzzong 2018-03-07  

厉害了，又学会一招。

18 楼 羽翼的心动 2017-02-21  

自己用过一款中间件产品叫做pageoffice，批量导出word，excel代码非常简单，而且很稳定呢，poi代码有点多，比较繁琐，推荐楼主用pageoffice。

17 楼 贝塔ZQ 2017-01-03  

poi导出大量数据，这样比较麻烦，代码也比较复杂，还得区分xls格式和xlsx格式的文件，可以用pageoffice插件，很好使的。

16 楼 张进飞 2015-07-27  

存放数据list,如果数据量比较大会溢出

15 楼 aa00aa00 2014-11-16  

楼主好,刚才测试一下,测试导出1000320,100w条数据,压缩后的包能有27.8m,速速还行,谢谢

14 楼 zzj_rjkf 2014-10-13  

你好 我参考你的代码！但是导出4万条数据时！报内存溢出！QQ:1424557018！有时间帮我解答一下 谢了

13 楼 zhaozengbing1987 2014-08-25  

感谢博主分享！

12 楼 ae6623 2014-06-24  

573281868mengyong 写道

双击下载的zip文件,为什么报错"C:\Documents and Settings\wot_mengyong\桌面\Person-20140416131433.zip: 不可预料的压缩文件末端".解压出来就没问题.这是为什么呢？

可能你的excel文件在制作的过程中出现问题了，比如后缀名，比如poi的Style样式使用过多等原因。

11 楼 573281868mengyong 2014-04-16  

双击下载的zip文件,为什么报错"C:\Documents and Settings\wot_mengyong\桌面\Person-20140416131433.zip: 不可预料的压缩文件末端".解压出来就没问题.这是为什么呢？

10 楼 573281868mengyong 2014-04-16  

双击下载的zip文件,为什么报错"C:\Documents and Settings\wot_mengyong\桌面\Person-20140416131433.zip: 不可预料的压缩文件末端".解压出来就没问题.这是为什么呢？

9 楼 ae6623 2013-11-25  

ae6623 写道

楼主，超级给力！！！楼下的你们就知道来挑刺，自己去做啊？人家楼主抛砖引玉出来不是让你们挑刺的，想挑刺，贴解决代码啊。一群眼高手低的人。poi大数据，谁都知道要分页，还用你们强调？楼主是一个简单的Demo，真正用到产品里肯定是调优的。能把这个放出来，你们百度到就已经很给力了，要有感激的心。楼主好样的。下面解决一下并发的问题。大数据量5W条*40多列的情况下20个并发在was6.5 2GJvm下直接服务卡死了。poi3.8据说可以限制内存释放，但是不支持2003xls格式，所以我也在想办法继续搞。10000条导出一次，压缩包导出最好了。

解决方法就是使用poi-3.10最新版的，里面可以自动释放内存，不过只支持Excel2007以上格式，生成文件后缀改为xlsx。非常爽，无内存溢出，还需要自己手动清空一下表格即可，清空方法为：

  int rowInMemory = 100;// 100测试最佳
  // 刷新到硬盘

     SXSSFWorkbook wb = new SXSSFWorkbook(rowInMemory); // keep 100 rows in memory,
           
  //临时文件进行压缩，建议不要true，否则会影响导出时间  
         wb.setCompressTempFiles(false);

  // sheet名字
            SXSSFSheet sheet = (SXSSFSheet) wb.createSheet(sheetName);
最后的最后你可以手动释放内存，强制刷新sheet到硬盘。
        

  // 刷新到硬盘
            sheet.flushRows();
            logger.debug("-----------------------------flush ok-----------------------------------");

            bodys.clear();
            bodys = null;

            logger.debug("内存制作数据已经结束！");
            // 拿到输出流
            fos = new FileOutputStream(file);
            // 缓冲
            outr = new BufferedOutputStream(fos);
            // 生成excel
            wb.write(outr);
            outr.flush();
            outr.close();

8 楼 H4X0R 2013-10-21  

学习一下

7 楼 ae6623 2013-09-17  

楼主，超级给力！！！楼下的你们就知道来挑刺，自己去做啊？人家楼主抛砖引玉出来不是让你们挑刺的，想挑刺，贴解决代码啊。一群眼高手低的人。poi大数据，谁都知道要分页，还用你们强调？楼主是一个简单的Demo，真正用到产品里肯定是调优的。能把这个放出来，你们百度到就已经很给力了，要有感激的心。楼主好样的。下面解决一下并发的问题。大数据量5W条*40多列的情况下20个并发在was6.5 2GJvm下直接服务卡死了。poi3.8据说可以限制内存释放，但是不支持2003xls格式，所以我也在想办法继续搞。10000条导出一次，压缩包导出最好了。

6 楼 c.hle2008 2013-03-14  

这个应该是属于假分页式的导出数据。

5 楼 红旗无风不飘 2012-06-12  

不知道你这个效率怎么样，如果创建多个Sheet是不是会好一些

4 楼 yn5411 2011-11-18  

如果导出XLS，大数话都必须这样分文件做。

3 楼 javaflex 2011-11-18  

Reset 写道

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

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

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

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

2 楼 Reset 2011-11-18  

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

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

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

1 楼 ZZX19880809 2011-11-18  

不错，能简单应用了