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 
 *
 */
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 
 *
 */
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

操作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();
        }
    }
}

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