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

Javabean代码 
package bean; 
/** 
*  
* @author QQ:三二8二4七6七六 
* 
*/ 
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 QQ:三二8二4七6七六 
* 
*/ 
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 QQ:三二8二4七6七六 
*  
*/ 
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 QQ:三二8二4七6七六 
* 
*/ 
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，给力吧
以后记得代码加注释

亲，记得给个评论哦

评论

1 楼 extperfect 2012-04-24  

谢谢分享！！！