Java uses POI to export large data volumes to Excel

The overall realization idea is:

Every time you query out 2w data and write to a temporary file

Then write these files into an Excel, or compress the files into a zip file, and then provide the zip file to the download (Zip packaging is used here because it can also be zip-packaged 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  EG:ZIP.SETINCLUDes ("*.java");           //fileset.setexcludes (...);   Exclude which files or folders           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<string,string> map,string namesqlid)  {              String tempFileName ;              String fileName ;              ITransactionManager manager =  Transactionmanagerfactory.gettransactionmanager ();                           dataobject[]  objects={};            try {                 /**                  * here to get the total number of bars, in addition to the number of each file, to get how many files, the remainder if not zero,                  * is the number of files that need to be cycled. If nonzero, the number of files is +1; After the file is finished, compress                                     *, the address of the compressed file is returned to the JSP of the stream.                  */                 tempfilename =  filepath.gettempfilepath ("02_02_01.xls");//Zero file     example:xxx.xls                 filename = filepath.gettemplAtepath ("Aeanalysis/uic01_001.xls");//template file         Example: &NBSP;PAMANAGEMENT/UIC02 _001_004.xls                 Manager.begin ();                 objects = dao.query (Namesqlid, map);                 manager.commit ();                 //Substation Identification      grid substation identification       Substation name      voltage level      Substation address      Administrative Unit       operation status      substation identification      substation Business system id     substation name      voltage level      Substation address      Administrative Unit      Operational Status      notes                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 only data execl under the Temp folder      *       *  @param  args     *  @throws  IOException      */    public  boolean exportexceltotemp (String  Temfile,int beginrowindex,object[] objects,string[] fieldarrstr,string temppath)  {      &nbSp;  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  Automatic generation  catch  block              flag=false;        } finally {             try {                 if (fos != null) {                     fos.close ();                 }                              } catch  (ioexception e)  {                 // TODO  Automatic generation  catch  blocks                 e.printstacktrace ();             }         }        return flag;    }  }

JSP file

<% @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 ();//can add or not add     response.setcontenttype ("Application/x-download");     filename = urlencoder.encode (filename, "UTF-8");    simpledateformat  Df = new simpledateformat ("yyyy-mm-dd&nbsp HH:mm:ss ");//Set Date format     string dd=df.format (New date ());     / /SYSTEM.OUT.PRINTLN (DD);// new date () to get the current system time          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 ();     }%>

This article is from the "Conversations" blog, so be sure to keep this source http://7720365.blog.51cto.com/7710365/1672032

Java uses POI to export large data volumes to Excel