java导出多个excel并打成zip包

这里主要是要针对数据量过大,通过生成多个excel文件并打成一个zip压缩包提供下载.

 

压缩excel的类  ZipUtil：

 

import java.io.*;
import java.util.*;
import java.util.zip.ZipOutputStream;
import java.util.zip.ZipEntry;
import java.util.zip.ZipFile;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;


public class ZipUtil {
    
    private static final Log log = LogFactory.getLog(ZipUtil.class);


    /**
     * 压缩文件
     * 
     * @param srcfile File[] 需要压缩的文件列表
     * @param zipfile File 压缩后的文件
     */
    public static void zipFiles(List<File> srcfile, File zipfile) {
        byte[] buf = new byte[1024];
        try {
            // Create the ZIP file
            ZipOutputStream out = new ZipOutputStream(new FileOutputStream(zipfile));
            // Compress the files
            for (int i = 0; i < srcfile.size(); i++) {
                File file = srcfile.get(i);
                FileInputStream in = new FileInputStream(file);
                // Add ZIP entry to output stream.
                out.putNextEntry(new ZipEntry(file.getName()));
                // Transfer bytes from the file to the ZIP file
                int len;
                while ((len = in.read(buf)) > 0) {
                    out.write(buf, 0, len);
                }
                // Complete the entry
                out.closeEntry();
                in.close();
            }
            // Complete the ZIP file
            out.close();
        } catch (IOException e) {
           log.error("ZipUtil zipFiles exception:"+e);
        }
    }

    /**
     * 解压缩
     * 
     * @param zipfile File 需要解压缩的文件
     * @param descDir String 解压后的目标目录
     */
    public static void unZipFiles(File zipfile, String descDir) {
        try {
            // Open the ZIP file
            ZipFile zf = new ZipFile(zipfile);
            for (Enumeration entries = zf.entries(); entries.hasMoreElements();) {
                // Get the entry name
                ZipEntry entry = ((ZipEntry) entries.nextElement());
                String zipEntryName = entry.getName();
                InputStream in = zf.getInputStream(entry);
                // System.out.println(zipEntryName);
                OutputStream out = new FileOutputStream(descDir + zipEntryName);
                byte[] buf1 = new byte[1024];
                int len;
                while ((len = in.read(buf1)) > 0) {
                    out.write(buf1, 0, len);
                }
                // Close the file and stream
                in.close();
                out.close();
            }
        } catch (IOException e) {
            log.error("ZipUtil unZipFiles exception:"+e);
        }
    }

    /**
     * Main
     * 
     * @param args
     */
    public static void main(String[] args) {
    	List<File> srcfile=new ArrayList<File>();
    	srcfile.add(new File("e:\\1.xls"));
    	srcfile.add(new File("e:\\2.xls"));
    	srcfile.add(new File("e:\\3.xls"));
    	srcfile.add(new File("e:\\4.xls"));
    	srcfile.add(new File("e:\\5.xls"));

        File zipfile = new File("e:\\edm.zip");
        ZipUtil.zipFiles(srcfile, zipfile);
    }
}

 

 

生成多个excel 类：

 

 /**
     * forward:导出Excel
     * @throws Exception
     */
    private ModelAndView forwardExport2Excel(HttpServletRequest request, HttpServletResponse response)
    throws Exception{
        try {
        	
	            Integer page=0;//页数
	            Integer pagesize=Integer.valueOf(PropertiesUtil.getPropertyValue("PAGESIZE"));//每页显示数
	            Integer total=0;//总数
	            int pageindex=1;//当前页数
	            Integer rowfrom = 1;
	            Integer rowto = 10;
	        	total =service.getTotal();
	        	if(total<pagesize){
	        		page=1;
	        		System.out.println("from:"+rowfrom+"---"+total);
    				List<Object> list = new ArrayList<Object>();
        			list = service.getAllList(rowfrom,total);
        			JXLUtil.writeEmailInfo2Excel(response, "邮箱信息总览", "titles", list,pageindex);
	        	}else{	        	
		        	Integer mode=total%pagesize;
		        	if(mode!=0){
		        		page=total/pagesize+1;
		        	}else{
		        		page=total/pagesize;
		        	}
		        	if(total!=0 && page!=0){
		        		while(pageindex<=page ){
		        			if(total<0){
		        				break;
		        			}
		        			if( total>pageindex*pagesize ){
			        			System.out.println("from:"+(rowfrom)+"----"+pageindex*pagesize);
			        			List<Object> list = new ArrayList<Object>();
			        			list = service.getAllList(rowfrom,pagesize);
				 	            JXLUtil.writeEmailInfo2Excel(response, "邮箱信息总览", "titles", list,pageindex);
			        			rowfrom=pageindex*pagesize;
			        			pageindex++;   
		        			}else{
		        				System.out.println("from:"+rowfrom+"--"+total);
		        				List<Object> list = new ArrayList<Object>();
			        			list = service.getAllList(rowfrom,total);
			        			JXLUtil.writeEmailInfo2Excel(response, "邮箱信息总览", "titles", list,pageindex);
			        			break;
		        			}
		        		}
		        	}
	        }	
        	List<File> srcfile=new ArrayList<File>();
        	for(int p=1;p<=page;p++){
            	srcfile.add(new File(PropertiesUtil.getPropertyValue("URL")+"zipname"+p+".xls"));
        	}
        	File zipfile = new File(PropertiesUtil.getPropertyValue("OUTPATH")+"zipname.zip");
            ZipUtil.zipFiles(srcfile, zipfile);
            String path = request.getContextPath();
            String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";	
	        writeObject("成功导出excel  地址：<html><a href="+basePath+"zipname.zip>点击下载</a></html>", response);	
	     
        } catch (Exception e) {
            log.error("error:"+e);
        }
        return null;
    }

 jxl生成excel参考我之前的一篇即可

评论

2 楼 18335864773 2017-06-12  

用过一次用pageoffice产品生成excel。其实利用PageOffice组件实现后台画出Excel报表是很容易的。与POI、JXL的比较。pageoffice既能在网页里显示编辑Office文档，也能在服务器端执行部分导入导出Office文档内容的任务。pageoffice可以兼容各种浏览器和各种office版本以及wps。

1 楼 lujialiang 2016-12-22  

writeEmailInfo2Excel（）  这个方法怎么写