Java POI writes data to and exports from server download template

Recently wrote a, Excel write and export.   The demand is like this. In the new contract, there will be the export contract data, the exported template is fixed, you need to write the contract information in the template.

First: Download templates > Write data > Output

Download the template:

 StringBuilder Path = new  StringBuilder (")                    ;                 Path.append (Request.getsession (). Getservletcontext (). Getrealpath ( " "Web-inf" );                 Path.append (File.separator);                 Path.append ( "Classes" );                 Path.append (File.separator);                 Path.append ( "template" );             Path.append (File.separator); String filePath  =path.tostring () + "\ \" + "contractdemo.xlsx" ;// This is to get the template path on the JBoss server 
  

New FileInputStream (FilePath); Xssfworkbook workBook=new  xssfworkbook (FIS);  Create a new workbook  

This is the download and output of Excel Excel write data is written in another method, the method is separate to write relatively clear.

try{

 StringBuilder path = new StringBuilder ("");                 Path.append (Request.getsession (). Getservletcontext (). Getrealpath (""));                 Path.append (File.separator);                 Path.append ("Web-inf");                 Path.append (File.separator);                 Path.append ("classes");                 Path.append (File.separator);                 Path.append ("template");             Path.append (File.separator);   String filepath=path.tostring () + "\ \" + "contractdemo.xlsx";  The template path on the server FileInputStream FIS = new FileInputStream (FilePath);             Input stream Xssfworkbook workbook=new xssfworkbook (FIS);             String filename= "Test_" +system.currenttimemillis () + ". xlsx";             OutputStream out=new FileOutputStream ("d:/" +filename);             Contractexporttemplate.createsheet (workbook,vo,conf);             Workbook.setforceformularecalculation (TRUE);                   Workbook.write (out);             Fis.close (); Out.flush ();               Out.close ();         Return "Success";                         }catch (Exception e) {e.printstacktrace ();         return "error"; }

　　

The following code actually writes the data in Excel, I write according to the template, so the format of the sheet table is fixed. I just need to get the specific cell and write the data.

Add data to Excel table public void Excelcontractwritedata (Xssfworkbook workbook,xssfsheet sheet, list< Contractexcelgroupbystorevo> ConList1, list<contractexcelgroupbyareavo> conlist2,list<      Contractexcelgroupbystorevo> conList3) throws exception{if (Conlist1.size ()!=0) {Xssfrow row0=sheet.getrow (0); Row0.getcell (2). Setcellvalue (Conlist1.get (0). GetTaskID () + "" + (Conlist1.get (0). Getprintseqno () ==null? "  1 ": Conlist1.get (0). Getprintseqno ());//write Print number Xssfrow Row=sheet.getrow (2);    Gets the cell for the sheet table, writes the data Row.getcell (2). Setcellvalue (Conlist1.get (0). GetYear ());   Row.getcell (4). Setcellvalue (Conlist1.get (0). Getcatlgid ());    Row.getcell (6). Setcellvalue (Conlist1.get (0). Getsupno ()); if (Conlist2.size ()!=0) {for (Contractexcelgroupbyareavo vo:conlist2) {if (Vo.getareaname ()! = "" && "East China". equ   ALS (Vo.getareaname (). toString ())) {Sheet.getrow (6). Getcell (2). Setcellvalue (Vo.getstorenum ());   Sheet.getrow (6). Getcell (3). Setcellvalue (Vo.gettargetnum ()); } IF (vo.getareaname ()! = "" && "Southwest". Equals (Vo.getareaname (). toString ())) {Sheet.getrow (7). Getcell (2).   Setcellvalue (Vo.getstorenum ());   Sheet.getrow (7). Getcell (3). Setcellvalue (Vo.gettargetnum ()); } if (Vo.getareaname ()! = "" && "North". Equals (Vo.getareaname (). toString ())) {Sheet.getrow (8). Getcell (2). Setcell   Value (Vo.getstorenum ());   Sheet.getrow (8). Getcell (3). Setcellvalue (Vo.gettargetnum ()); } if (Vo.getareaname ()! = "" && "South China". Equals (Vo.getareaname (). toString ())) {Sheet.getrow (9). Getcell (2). Setcell   Value (Vo.getstorenum ());   Sheet.getrow (9). Getcell (3). Setcellvalue (Vo.gettargetnum ()); } if (Vo.getareaname ()! = "" && "Huazhong". Equals (Vo.getareaname (). toString ())) {Sheet.getrow (). Getcell (2). Setcel   LValue (Vo.getstorenum ());   Sheet.getrow (+). Getcell (3). Setcellvalue (Vo.gettargetnum ());  }}}if (Conlist3.size ()!=0) {int rowindex=14; This number is based on the Excel template for the for (Contractexcelgroupbystorevo conexcel:conlist3) {sheet.getrow (RowIndex). Getcell (1). SetcellValue (Conexcel.getareaname ()); Sheet.getrow (RowIndex). Getcell (2). Setcellvalue (Conexcel.getprovincename ()); Sheet.getrow (RowIndex). Getcell (3). Setcellvalue (Conexcel.getcityname ()); Sheet.getrow (RowIndex). Getcell (4). Setcellvalue (Conexcel.getstorenum () + "-" +conexcel.getstorename ()); The store's number Sheet.getrow (RowIndex). Getcell (5). Setcellvalue (Conexcel.getstoreamount ()); Sheet.getrow (RowIndex). Getcell (6). Setcellvalue (Conexcel.getmemo ()); if (CONEXCEL.GETSERVREQD ()!=null) {String month= conexcel.getservreqd ( ). toString (); Sheet.getrow (RowIndex). Getcell (7). Setcellvalue (This.stringtointarray (month));} Else{sheet.getrow (RowIndex). Getcell (7). Setcellvalue (Conexcel.getdatestr ());}     rowindex++;}   }   }

　　

Here is a sort of month with 1 2 3 4 5 6 7 8 9 10 11 12 But each occurrence of the month is not fixed and may have a broken month in between. So it is necessary to sort and then determine the difference between the second number and the first number, if the difference is greater than 2, it means that the middle of the month is broken. Then the successive numbers of the tail string together, showing

Converts the month array to the string public strings  Stringtointarray (String str) {        stringbuffer datestage= new StringBuffer ("");   String strs[] = Str.split (",");     int array[] = new Int[strs.length];     for (int i=0;i<strs.length;i++) {         array[i]=integer.parseint (strs[i]);    }          for (int i = 0, i < array.length; i++) {for               (int j = 0; j<array.length-i-1; j + +) {                   if (array[j]>array[j+1] ) {                      int temp = array[j];                      ARRAY[J] = array[j+1];                      ARRAY[J+1] = temp;}}            }      int a=array[0];        for (int j = 1; j < Array.Length; J + +) {if (Array[j]-array[j-1]>=2) {datestage.append ("+a+"-"+array[j-1]+", "); a=a RRAY[J];   } if (j== (array.length-1)) {datestage.append ("" +a+ "-" +array[j]);}}     return datestage.tostring ();       }

　　

Java POI writes data to and exports from server download template