Java POI writes data to and exports from server download template

Source: Internet
Author: User
Tags setcell jboss server

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.