比较好用的java导出大数据量Excel

转载于:http://blog.csdn.net/u010003835/article/details/51590101
注:代码还没仔细研究过……………..

原理:指定条数生成一个Excel文件,写入到本机,然后将这些Excel一起打成一个压缩包,发给客户端.

1.将多个文件一起生成压缩文件和设置Http响应头的Utils

package com.tiglle.utils;

import java.io.File;
import java.io.FileInputStream;  
import java.io.FileOutputStream;  
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.zip.ZipEntry;  
import java.util.zip.ZipOutputStream;

import javax.servlet.http.HttpServletResponse;  
/**  
 *   
 * @author http://javaflex.iteye.com/  
 *  
 */  
public class FileZip {  
    /**  
     *   
     * @param srcfile 文件名数组  
     * @param zipfile 压缩后文件名称  
     * @throws IOException 
     */  
    public static void ZipFiles(File[] srcfile, File zipfile) throws IOException {
        if(zipfile.exists()){
            zipfile.createNewFile();
        }
        byte[] buf = new byte[1024];  
        try {  
            ZipOutputStream out = new ZipOutputStream(new FileOutputStream(  
                    zipfile));  
            for (int i = 0; i < srcfile.length; i++) {  
                File tempFile = srcfile[i];
                FileInputStream in = new FileInputStream(tempFile);  
                out.putNextEntry(new ZipEntry(srcfile[i].getName()));  
                int len;  
                while ((len = in.read(buf)) > 0) {  
                    out.write(buf, 0, len);  
                }  
                out.closeEntry();  
                in.close(); 
                //删除文件,免得占用服务器内存
                tempFile.delete(); 
            }  
            out.close();  
        } catch (IOException e) {  
            e.printStackTrace();  
        }  
    } 

    /** 设置响应头信息和默认文件名称 */ 
    /**
     * 
     * @param response
     * @param defaultFileName 默认文件名称
     */
    public static void setResponseHeader(HttpServletResponse response,String defaultFileName) {  
        try {  
            response.setContentType("application/octet-stream;charset=UTF-8");  
            response.setHeader("Content-Disposition", "attachment;filename=" + java.net.URLEncoder.encode(defaultFileName, "UTF-8")  + ".zip");  
            response.addHeader("Pargam", "no-cache");  
            response.addHeader("Cache-Control", "no-cache");  
        } catch (Exception ex) {  
            ex.printStackTrace();  
        }  
    }

    /**
     * 根据指定名称加时间生成字符串
     */
    public static String generateName(String name){
        Date date = new Date();  
        SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");  
        return name + format.format(date);
    }
}  

2.装数据的实体类:

package com.tiglle.bean;

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;  
    }  
}  

3.生成Excel和发送给客户端的servlet

package com.tiglle.servlet;

import Java.io.File; 
import java.io.FileInputStream; 
import java.io.FileOutputStream; 
import java.io.IOException; 
import java.io.OutputStream; 
import java.util.ArrayList; 
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.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 org.apache.poi.ss.util.CellRangeAddress;

import com.tiglle.bean.Person; 
import com.tiglle.utils.FileZip;

/** 
* 
* @author http://javaflex.iteye.com/ 
* 
*/ 
public class PersonServlet extends HttpServlet {

public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {  
    doGet(request, response);  
} 





public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {  
    //文件名获取  
    String zipFileName = FileZip.generateName("中奖记录");
    //设置响应头信息
    FileZip.setResponseHeader(response, zipFileName); 
    OutputStream out = null;  
    try {  
        out = response.getOutputStream();
        //模拟从数据库获得数据
        List<Person> list = new ArrayList<Person>();//PersonService.getPerson();  
        for(int i=0;i<999999;i++){
            Person p = new Person(i, "xiaoming"+i, "湖北随机", "12123123", 30.3);
            list.add(p);
        }

       //每个xls文件的全路径
       String fileName = request.getRealPath("/files") + "/" + zipFileName;

       //生成n多个Excel,并将多个xls文件名+n用于区分,返回每个生成的xls的文件全路径
       List<String> fileNames = toExcel(list,10000,fileName,out);  

        //将生成的n个xls压缩成一个zip文件,并删除xls文件
        File zip = new File(fileName + ".zip");// 压缩文件  
        File srcfile[] = new File[fileNames.size()];  
        for (int i = 0, n = fileNames.size(); i < n; i++) {  
            srcfile[i] = new File(fileNames.get(i));//根据路径生成File对象  
        } 
        //压缩成zip
        FileZip.ZipFiles(srcfile, zip);

        //将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();  
        //删除zip文件,免得占用服务器内存
        zip.delete();

    } catch (IOException e1) {  
        e1.printStackTrace();  
    } finally {  
        try {  
            out.flush();  
            out.close();  
        } catch (IOException e) {  
            e.printStackTrace();  
        }  
    }  
}  

@SuppressWarnings({"rawtypes","unchecked","deprecation"})
/**
 * 
 * @param list 里面组装的实体类
 * @param request
 * @param length 多少条分一个文件
 * @param f 文件名称
 * @param out
 * @throws IOException
 */
public List<String> toExcel(List<Person> list,/* HttpServletRequest request,*/int length, String fileName, OutputStream out) throws IOException {  
    List<String> fileNames = new ArrayList();// 用于存放生成的文件名称s  
    // 生成excel（一个对象一行）  
    for (int j = 0, n = list.size() / length + 1; j < n; j++) {  
        Workbook book = new HSSFWorkbook(); 
        //创建Excel的第一页并指定名称
        Sheet sheet = book.createSheet("中奖记录"); 
        //将每个xls+j用于区分
        String tempFileName = fileName + "-" + j + ".xls";
        //将名字纪录起来
        fileNames.add(tempFileName);  
        FileOutputStream o = null;  
        try {  
            o = new FileOutputStream(tempFileName);  
            // 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;  
                }  
                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 { 
book.close(); 
o.flush(); 
o.close(); 
} 
} 
return fileNames; 
} 
}