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

朴灿烈づ我的快乐病毒、 2022-06-13 12:45 188阅读 0赞

转载于:[http://blog.csdn.net/u010003835/article/details/51590101][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/][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;  
\}  
\}

“\`

[http_blog.csdn.net_u010003835_article_details_51590101]: http://blog.csdn.net/u010003835/article/details/51590101
[http_javaflex.iteye.com]: http://javaflex.iteye.com/