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

绝地灬酷狼 2022-06-10 14:18 174阅读 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/ * */ 
    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