Java uses poi to export a large amount of data to EXCEL

The overall realization idea is as follows:

2w data is queried each time and written to a temporary file

Then write these files into an EXCEL, or compress them into a zip file, and then provide the Zip file for download (zip packaging is used here because Zip packaging can also be done on Linux).

/ / this zip packaging tool class 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 + "does not exist!") ; 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") Which files or folders are included in eg:zip.setIncludes ("* .java"); / / fileSet.setExcludes (...); which files or folders are excluded from zip.addFileset (fileSet); zip.execute ();}} business processing

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 (); / * Substation * / @ Bizlet (") public String export4boassets (Map map,String nameSqlid) {String tempFileName; String fileName; ITransactionManager manager = TransactionManagerFactory.getTransactionManager () DataObject [] objects= {}; try {/ * get the total number of files here, and divide the number of entries per file to get how many files are needed. If the remainder is not zero, * the number of cycles is the number of files, if not zero, the number of files + 1 After the file is written, it is compressed *, and the address of the compressed file is returned to the jsp of the stream. * / tempFileName = FilePath.getTempFilePath ("02_02_01.xls"); / 00:00 file example: xxx.xls fileName = FilePath.getTemplatePath ("aeanalysis/uic01_001.xls"); / / template file example: pamanagement/uic02_001_004.xls manager.begin (); objects = Dao.query (nameSqlid, map); manager.commit () / substation identification substation identification substation name voltage level substation address management unit operating status substation identification substation business system ID substation name voltage level substation address management unit operating status remarks 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 } / * generate data-only execl to temporary folder * * @ 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 automatically generate catch block flag=false } finally {try {if (fos! = null) {fos.close ();}} catch (IOException e) {/ / TODO automatically generate catch blocks e.printStackTrace ();}} return flag }}

Jsp file