Java exports a large number of Excel

Talk less, straight into the subject

Basic idea to create a temporary file to write data to export data to delete temporary files

Two jar packages required first

ANTLR and Stringtemplate

Create a class in the database row

    private string name1;        private  String name2;        private String name3;     public string getname1 ()  {         Return name1;    }    public void setname1 (String  NAME1)  {        this.name1 = name1;     }    public string getname2 ()  {         return name2;    }    public void setname2 ( STRING NAME2)  {        this.name2 = name2;     }    public string getname3 ()  {        &nbsP;return name3;    }    public void setname3 (String  name3)  {        this.name3 = name3;     }

Then you need to create the corresponding worksheet class

private int columnnum;        private int rownum;         private List<Row> rows;         private List<SfOrder> orders;                 public List<SfOrder>  GetOrders ()  {        return orders;    }     public void setorders (list<sforder> orders)  {         this.orders = orders;    }     public string getsheet ()  {        return  Sheet;    }    public void setsheet (String sheet)   {&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSp;  this.sheet = sheet;    }    public list <row> getrows ()  {        return rows;     }    public void setrows (list<row> rows)  {         this.rows = rows;    }     public int getcolumnnum ()  {        return  columnnum;    }    public void setcolumnnum (int  Columnnum)  {        this.columnNum = columnNum;     }    public int getrownum ()  {         return rownum;    }    public void  setrownum (Int rownum)  {        this.rowNum = rowNum;     }

Then you need to write two files for

Head.st

?

<?xml version= "1.0"? ><?mso-application progid= "Excel.Sheet"? ><workbook xmlns= " Urn:schemas-microsoft-com:office:spreadsheet " xmlns:o=" Urn:schemas-microsoft-com:office:office "  xmlns:x= "Urn:schemas-microsoft-com:office:excel"  xmlns:ss= "Urn:schemas-microsoft-com:office:spreadsheet"  xmlns:html= "http://www.w3.org/TR/REC-html40" > <documentproperties xmlns= "urn: Schemas-microsoft-com:office:office ">  <Created>1996-12-17T01:32:42Z</Created>   <lastsaved>2013-08-02t09:21:24z</lastsaved>  <version>11.9999</version > </documentproperties> <officedocumentsettings xmlns= "Urn:schemas-microsoft-com:o Ffice:office ">  <RemovePersonalInformation/> </OfficeDocumentSettings> < excelworkbook xmlns= "Urn:schemas-microsoft-com:office:excel" >  <windowheight>4530</ Windowheight>  <windowwidth>8505</windowwidth>  <windowtopx>480</windowtopx>  < windowtopy>120</windowtopy>  <acceptlabelsinformulas/>  < Protectstructure>false</protectstructure>  <protectwindows>false</protectwindows > </excelworkbook> <styles>  <style ss:id= "Default"  ss:Name= " Normal ">   <alignment ss:vertical=" Bottom "/>   <borders/>    <font ss:fontname= "Song Body"  x:charset= "134"  ss:size= "/>   " <interior/>   <numberformat/>   <protection/>  </ Style> </styles>

and Body.st

? $worksheet: {<worksheet ss:name= "$it. sheet$" > <table ss:expandedcolumncount= "$it. columnnum$" SS: Expandedrowcount= "$it. rownum$" x:fullcolumns= "1" x:fullrows= "1" ss:defaultcolumnwidth= "WU" ss:defaultrowheight= " 14.25 "> $it. rows:{<Row> <cell><data ss:type=" String "> $it .name1$</data></cell> < ;/row>}$ </Table> </worksheet>}$

Here is the program code

First you want to query data from the data, assuming that the data you are querying is list data

            //my list here is from the map. You can get the    directly           list<row> list= (List<Row>)   Result.get ("list");               list= ( list<sforder>)  result.get ("list");               if (List.size () ==0) {                   return null;               }             Long starttimne = system.currenttimemillis ();             stringtemplategroup stgroup = new stringtemplategroup (" Stringtemplate ");                stgroup.setfilecharencoding (" UTF-8 ");//set encoding, otherwise garbled, export Excel format is incorrect             // Write header information for Excel file             stringtemplate head  =  stgroup.getinstanceof ("/template/head");             string path=request.getsession (). Getservletcontext (). GetRealPath ("/upload/excel/ Test1.xls ");             file file =  new file (path);             printwriter  writer = new printwriter (New bufferedoutputstream (new fileoutputstream (file));             writer.print (Head.toString ());              writer.flush ();             int sheets = 10;             //excel single table maximum number of rows is 65535            int  maxrownum = 3000;            int  maxrownumlast=0;            //calculation to be divided into several sheet             sheets= (List.size ()%maxRowNum==0)  ? list.size ()/maxrownum:list.size ()/maxrownum+1;             //Calculate how many rows the last sheet has              maxrownumlast=list.size ()-(sheets-1) *maxrownum;

The

is pre-staged, and the following writes data directly to the file

Write Excel file data information             for (int i=0;i< sheets;i++) {                 integer nums=0;                 stringtemplate body =  stgroup.getinstanceof ("/template/body");                 worksheet worksheet =  new worksheet ();                 worksheet.setsheet (" " + (i+1) + " ");                 worksheet.setcolumnnum (3);                 worksheet.setrownum (MaxRowNum);                 list<row> orders = new  Arraylist<row> ();                 if (i== (sheets-1)) {                     for (int j=0;j<maxrownumlast;j++) {                          nums=i*maxRowNumLast+j;                         sforder order=new sforder ();                          order.setorderid (List.get (nums). Getorderid ());                          orders.add (order);                      }                }else{                      for (int j=0;j<maxrownum;j++) {                         nums=i*maxRowNum+j;                          row order=new row ();                          Order.setorderid (List.get (nums). GetoRderid ());                         orders.add (Order);                     }                 }                 worksheet.setorders (Orders);                 body.setattribute ("Worksheet",  Worksheet);                 Writer.print (Body.tostring ());                 writer.flush ();                 orders.cleAR ();                 orders  = null;                 worksheet = null;                 body = null;                 runtime.getruntime (). GC ();                 system.out.println ("Generating  sheet for Excel Files" + (i+1));             }

The following writes to the server disk

Write to Excel file trailing Writer.print ("</Workbook>");            Writer.flush ();            Writer.close ();            SYSTEM.OUT.PRINTLN ("Generate Excel file Completion");            Long endTime = System.currenttimemillis (); System.out.println ("spents =" + ((Endtime-starttimne)/1000) + "seconds");

Here to read the file, that is, the formal export

 //creating a File object             file upload=new  file (path);             //Setting response encoding method             response.setcontenttype ("application/ Vnd.ms-excel ");             response.setheader (" Content-disposition "," Attachment;filename=ceshi.xls ");             //read-out file to I/O stream              Fileinputstream fis=new fileinputstream (upload);             bufferedinputstream buff=new bufferedinputstream (FIS);             byte [] b=new byte[1024];//equivalent to our cache              long k=0;//This value is used to calculate how many bytes are currently actually downloaded              //get the output stream from the response object, ready to download              outputstream myout=response.getoutputstream ();             //Start cycle Download              while (K<upload.length ()) {                 int j=buff.read (b,0,1024);                 k+=j;                 //writes the data in B to the client's memory                  myout.write (B,0,J);             }            //writes data to the client's memory, flushes to disk              myout.flush ();             myout.close ();

If necessary, such as this file is dynamic, we can make this file is dynamic, it is necessary to delete this file

File Filedel = new file (path);                         If the file path corresponds to a file that exists and is a file, delete the IF (filedel.exists () && file.isfile ()) { Filedel.delete ()}

This is done, the time to export is mostly spent in database queries, of course, this is your database skills problem

Java exports a large number of Excel