總體的實現思想為：

每次查詢出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.xlsfileName?=?FilePath.getTemplatePath("aeanalysis/uic01_001.xls");//模板文件???????例：?pamanagement/uic02_001_004.xlsmanager.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();}%>

轉載于:https://blog.51cto.com/7720365/1672032

總結

以上是生活随笔為你收集整理的java使用poi实现大数据量导出为EXCEL的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。