java使用poi实现大数据量导出为EXCEL

总体的实现思想为：

每次查询出2w数据，并写入到临时文件

然后把这些文件写入到一个EXCEL里边，或者把这些文件压缩为zip文件，然后把Zip文件提供给下载(这里使用zip打包是因为，在Linux上也能进行Zip打包)。

//这个zip打包工具类package net.szh.zip;    import java.io.File;    import org.apache.tools.ant.Project;  import org.apache.tools.ant.taskdefs.Zip;  import org.apache.tools.ant.types.FileSet;    public class ZipCompressorByAnt {        private File zipFile;        public ZipCompressorByAnt(String pathName) {          zipFile = new File(pathName);      }            public void compress(String srcPathName) {          File srcdir = new File(srcPathName);          if (!srcdir.exists())              throw new RuntimeException(srcPathName + "不存在！");                    Project prj = new Project();          Zip zip = new Zip();          zip.setProject(prj);          zip.setDestFile(zipFile);          FileSet fileSet = new FileSet();          fileSet.setProject(prj);          fileSet.setDir(srcdir);          //fileSet.setIncludes("**/*.java"); 包括哪些文件或文件夹 eg:zip.setIncludes("*.java");          //fileSet.setExcludes(...); 排除哪些文件或文件夹          zip.addFileset(fileSet);                    zip.execute();      }  }

   import java.util.Map;    import com.eos.common.transaction.ITransactionManager;    import com.eos.common.transaction.TransactionManagerFactory;    import com.eos.system.annotation.Bizlet;    import com.pns.framework.dao.Dao;    import com.pns.framework.execl.Excel;    import com.pns.framework.filepath.ExportExeclUtil;    import com.pns.framework.filepath.FilePath;    import commonj.sdo.DataObject;    @Bizlet("")    public class Aeanalysis {            private  ExportExeclUtil util= new ExportExeclUtil();        /**         * 变电站         *         */        @Bizlet("")        public String export4boassets(Map map,String nameSqlid) {             String tempFileName ;             String fileName ;             ITransactionManager manager = TransactionManagerFactory.getTransactionManager();                          DataObject[] objects={};            try {                /**                 *在这儿得到总共的条数，再除每个文件的条数，得到需要多少个文件，求余如果不为零，                 *则需要循环的次数即为文件数，如果不为零则为文件数+1；文件写完之后进行压缩                                  *，把压缩文件的地址返回到流的jsp中。                 */                tempFileName = FilePath.getTempFilePath("02_02_01.xls");//零时文件   例：xxx.xls                fileName = FilePath.getTemplatePath("aeanalysis/uic01_001.xls");//模板文件       例： pamanagement/uic02_001_004.xls                manager.begin();                objects = Dao.query(nameSqlid, map);                manager.commit();                //变电站标识    电网变电站标识    变电站名称    电压等级    变电站地址    管理单位    运行状态    变电站标识    变电站业务系统ID    变电站名称    电压等级    变电站地址    管理单位    运行状态    备注                String[] fieldArrStr = new String[] {"SUBS_ID", "PMS_SUBS_ID", "SUBS_NAME", "VOLT_CODE","SUBS_ADDR","ORG_NAME","RUN_STATUS","PMS_ID","PMS_GLOBEID",                        "PMS_SUBS_NAME","PMS_VOLT_CODE","PMS_SUBS_ADDR","PMS_ORG_NAME","PMS_RUN_STATUS",                        "REMARK"};             util.exportExcelToTemp(fileName, 2, objects, fieldArrStr, tempFileName);            } catch (RuntimeException e) {                manager.rollback();                tempFileName="ex";            }                          return tempFileName;        }        /**     * 生成只有数据的execl到临时文件夹下     *      * @param args     * @throws IOException     */    public  boolean exportExcelToTemp(String temFile,int beginRowIndex,Object[] objects,String[] fieldArrStr,String tempPath) {        boolean flag;        FileOutputStream fos = null;                try {            this.getWorkBook(temFile);            this.getSheet();            this.setDataRow(beginRowIndex, objects, fieldArrStr);            fos = new FileOutputStream(tempPath);            workBook.write(fos);            flag=true;        } catch (IOException e) {            // TODO 自动生成 catch 块            flag=false;        } finally {            try {                if(fos != null){                    fos.close();                }                            } catch (IOException e) {                // TODO 自动生成 catch 块                e.printStackTrace();            }        }        return flag;    }  }

jsp文件

<%@page pageEncoding="UTF-8"%><%@page contentType="text/plain; charset=utf-8" %><%@page import="org.apache.commons.fileupload.*" %><%@page import="java.net.URLEncoder"%><%@page import="java.io.BufferedOutputStream"%><%@page import="java.io.FileInputStream"%><%@page import="java.io.IOException"%><%@page import="java.util.Date"%><%@page import="java.text.SimpleDateFormat"%><%String path =request.getParameter("path");    String filename=request.getParameter("filename");    //String time=request.getParameter("time");    response.reset();//可以加也可以不加    response.setContentType("application/x-download");    filename = URLEncoder.encode(filename,"UTF-8");    SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式    String dd=df.format(new Date());    //System.out.println(dd);// new Date()为获取当前系统时间        response.addHeader("Content-Disposition", "p_w_upload; filename=" +dd+"_"+filename);        BufferedOutputStream bos = null;      FileInputStream fis = null;      try {           fis = new FileInputStream(path);          bos = new BufferedOutputStream(response.getOutputStream());          byte[] buffer = new byte[1024];          while(fis.read(buffer) != -1){                bos.write(buffer);          }          response.flushBuffer();        out.clear();    }catch(IOException e) {          e.printStackTrace();      }finally {           fis.close();        bos.close();    }%>