java使用poi實現大資料量匯出為EXCEL

標籤：poi大批量資料匯出   zip壓縮   java實現匯出   

總體的實現思想為：

每次查詢出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<String,String> 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", "attachment; 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();    }%>

本文出自 “會話” 部落格，請務必保留此出處http://7720365.blog.51cto.com/7710365/1672032

java使用poi實現大資料量匯出為EXCEL