POI Stencil Printing

Step: Make a template file (path to a template file) import (load) a template file, and get a workbook to read a worksheet read a row read a cell read a cell style Set cell content other cells can use the style you read

Package Cn.xxxxxx.jk.action.cargo;
Import Java.io.ByteArrayOutputStream;
Import Java.io.FileInputStream;
Import Java.io.InputStream;

Import java.util.List;

Import Javax.servlet.http.HttpServletResponse;
Import Org.apache.poi.hssf.usermodel.HSSFWorkbook;
Import org.apache.poi.hssf.util.CellRangeAddress;
Import Org.apache.poi.ss.usermodel.Cell;
Import Org.apache.poi.ss.usermodel.CellStyle;
Import Org.apache.poi.ss.usermodel.Font;
Import Org.apache.poi.ss.usermodel.Row;
Import Org.apache.poi.ss.usermodel.Sheet;
Import Org.apache.poi.ss.usermodel.Workbook;

Import Org.apache.struts2.ServletActionContext;
Import cn.xxxxxx.jk.action.BaseAction;
Import cn.xxxxxx.jk.domain.ContractProduct;
Import Cn.xxxxxx.jk.service.ContractProductService;
Import Cn.xxxxxx.jk.util.DownloadUtil;

Import Cn.xxxxxx.jk.util.UtilFuns;
    public class Outproductaction extends Baseaction {//injection date private String inputdate;
 public void Setinputdate (String inputdate) {this.inputdate = inputdate;   //Inject service private contractproductservice contractproductservice;  public void Setcontractproductservice (Contractproductservice contractproductservice) {This.contractproductservice
    = Contractproductservice;
    /** * Enter the printed page of the shipping table/public String Toedit () throws Exception {return "Toedit"; /** * Print/public string print () throws Exception {//1. Create workbook string path = Servleta
        Ctioncontext.getservletcontext (). Getrealpath ("/");   Path = path+ "/make/xlsprint/toutproduct.xls";     Get the template file location InputStream is = new FileInputStream (path);

        Gets the specified file stream according to the file path Workbook wb = new Hssfworkbook (IS);//The specified workbook is loaded according to the file stream it can only operate the excel2003 version   2. Reading sheet Sheet Sheet = wb.getsheetat (0);
        0 represents the subscript of the worksheet//extracts some common variables Row nrow=null;

        Cell NCell = null; int rowno=0;//line number int cellno=1;//column number//3. Create Row Objects//========================================== the production of large headings nrow = Sheet.getrow (rowno++); Read Row Object NCell = Nrow.getcell (  CELLNO)//Read cell/Set the contents of the cell Ncell.setcellvalue (inputdate.replace ("-0", "-"). Replace ("-", "year") + "month shipping table");
        2015-10 2015-01----->2015-1----->2015 Year 1//============================================ small title   rowno++; Skip the second line, enter the third row (rowno=2)//=========================================== the data content//first read the third row of the style Nrow = Sheet.getrow (ROWNO)//Read the third line//guest order number number quantity factory delivery shipping date of shipment trade terms cellstyle Customercellstyle = nrow.g
        Etcell (cellno++). Getcellstyle ()//Read the cell's style String str = Nrow.getcell (Cellno). Getstringcellvalue ()//Read the contents of the cell

        System.out.println (str); CellStyle Ordernocellstyle = Nrow.getcell (cellno++). Getcellstyle ()//Read the cell style CellStyle Productnocellstyle = NRow. Getcell (cellno++). Getcellstyle ()//Read the cell's style cellstyle Cnumbercellstyle = Nrow.getcell (Cellno++). Getcellstyle ()//Read the cell's style cellstyle Factorycellstyle = Nrow.getcell (cellno++). Getcellstyle ()//Read cell style CellStyle Deliveryperiodcellstyle = Nrow.getcell (cellno++). Getcellstyle ();//Read the cell style CellStyle shiptimecellstyl E = Nrow.getcell (cellno++). Getcellstyle ()//Read the cell's style cellstyle Tradetermscellstyle = Nrow.getcell (cellno++). GetCel Lstyle ()//reading the cell's style String hql = "from Contractproduct where To_char (contract.shiptime, ' yyyy-mm ') = '" +inputdate   +"'"; 2015-01 list<contractproduct> List = Contractproductservice.find (hql, contractproduct.class, NULL);/load specified ship Period of Goods List//Traverse cargo list for (contractproduct cp:list) {//Generate a new row Nrow = Sheet.createrow (r
            owno++);

            Nrow.setheightinpoints (24f);//set row high cellno=1; Generate cell Customer NCell = Nrow.createcell (cellno++);//Create Cell Ncell.setcellvalue (Cp.getcontract (). Get Customname ())//Set cell content Ncell.setCellStyle (Customercellstyle); Set cell style//Generate cell order number NCell = Nrow.createcell (cellno++);//Create cell ncell.setcellvalue (CP.    Getcontract (). Getcontractno ());//Set cell content Ncell.setcellstyle (Ordernocellstyle); Set cell style//Generate cell Item Number NCell = Nrow.createcell (cellno++);//Create Cell ncell.setcellvalue (CP.G    Etproductno ())//Set cell content Ncell.setcellstyle (Productnocellstyle); Set cell style//number of cells NCell = Nrow.createcell (cellno++);//Create Cell ncell.setcellvalue (CP.G    Etcnumber ())//Set cell content Ncell.setcellstyle (Cnumbercellstyle); Set cell style//produce cell factory NCell = Nrow.createcell (cellno++);//Create Cell ncell.setcellvalue (CP.G    Etfactoryname ())//Set cell content Ncell.setcellstyle (Factorycellstyle); Set cell styles//Generate cells factory delivery NCell = Nrow.createcell (cellno++);//Create Cell ncell.setcellvalue (U Tilfuns.datetimeformat (Cp.getcontract (). Getdeliveryperiod ()))//Set cell content Ncell.setcellstyle (Deliveryperiodcellstyle); Set cell style//produce a cell sailing NCell = Nrow.createcell (cellno++);//Create Cell ncell.setcellvalue (Util    Funs.datetimeformat (Cp.getcontract (). Getshiptime ()))//Set cell content Ncell.setcellstyle (Shiptimecellstyle); Set cell style//Generate cell trade Terms NCell = Nrow.createcell (cellno++);//Create Cell Ncell.setcellvalue (c    P.getcontract (). Gettradeterms ());//Set cell content Ncell.setcellstyle (Tradetermscellstyle); Set cell style}//output Bytearrayoutputstream Bytearrayoutputstream = new Bytearrayoutputstream ()//memory

        Buffer Wb.write (bytearrayoutputstream);

        Provide response object HttpServletResponse response = Servletactioncontext.getresponse ();
        Downloadutil downloadutil = new Downloadutil ();
        Downloadutil.download (Bytearrayoutputstream, Response, "outgoing table. xls");
    return NONE;


 }   /*** * is not printed using a template * @return * @throws Exception/public String printnottemplate () throws Exce ption {//1. Create workbook Workbook wb = new Hssfworkbook ();//It can only operate excel2003 version//2. Create a worksheet Sheet she

        ET = Wb.createsheet ();
        Setting the column width itself is a bug sheet.setcolumnwidth (0, 3*256);
        Sheet.setcolumnwidth (1, 26*256);
        Sheet.setcolumnwidth (2, 11*256);
        Sheet.setcolumnwidth (3, 29*256);
        Sheet.setcolumnwidth (4, 12*256);
        Sheet.setcolumnwidth (5, 15*256);
        Sheet.setcolumnwidth (6, 10*256);
        Sheet.setcolumnwidth (7, 10*256);



        Sheet.setcolumnwidth (8, 8*256);
        Extract some common variables Row nrow=null;

        Cell NCell = null;
        int rowno=0;//line number int cellno=1;//column number//3. Creating row Objects//========================================== the production of large headings Nrow = Sheet.createrow (rowno++);//Create Row Object Nrow.setheightinpoints (36.26f);//set row height NCell = nrow.createce

 ll (Cellno);       Sets the contents of the cell Ncell.setcellvalue (Inputdate.replace ("0", "-"). Replace ("-", "year") + "month shipping table");

        2015-10 2015-01----->2015-1----->2015 Year 1//Set cell style Ncell.setcellstyle (Bigtitle (WB));



        Merge cell sheet.addmergedregion (new cellrangeaddress (0,0,1,8)); ============================================ Small Caption Nrow = Sheet.createrow (rowno++);//Create Row Object Nrow.setheighti
        Npoints (26f)//Set row high String titles [] = {"Customer", "Order Number", "Item Number", "Quantity", "Factory", "Factory delivery", "Sailing date", "Trade terms"}; for (String title:titles) {NCell = Nrow.createcell (cellno++);//Create cell Ncell.setcellvalue on a small heading (titl e)//Set cell content Ncell.setcellstyle (this.title);//small heading style settings}//============================ =============== data content String hql = "from Contractproduct where To_char (contract.shiptime, ' yyyy-mm-dd ') like '" +inpu   tdate+ "%"; 2015-01% list<contractproduct> List = Contractproductservice.find (HQL,Contractproduct.class, NULL);//Load List of goods under the specified sailing schedule//Traverse cargo list for (contractproduct cp:list) {//Generate a new line
            Nrow = Sheet.createrow (rowno++);

            Nrow.setheightinpoints (24f);//set row high cellno=1; Generate cell Customer NCell = Nrow.createcell (cellno++);//Create Cell Ncell.setcellvalue (Cp.getcontract (). Get    Customname ());//Set cell content Ncell.setcellstyle (TEXT (WB)); Set cell style//Generate cell order number NCell = Nrow.createcell (cellno++);//Create cell ncell.setcellvalue (CP.    Getcontract (). Getcontractno ());//Set cell content Ncell.setcellstyle (WB); Set cell style//Generate cell Item Number NCell = Nrow.createcell (cellno++);//Create Cell ncell.setcellvalue (CP.G    Etproductno ());//Set cell content Ncell.setcellstyle (TEXT (WB)); Set cell style//number of cells NCell = Nrow.createcell (cellno++);//Create Cell ncell.setcellvalue (CP.G 
          Etcnumber ())//Set cell contents  Ncell.setcellstyle (TEXT (WB)); Set cell style//produce cell factory NCell = Nrow.createcell (cellno++);//Create Cell ncell.setcellvalue (CP.G    Etfactoryname ());//Set cell content Ncell.setcellstyle (TEXT (WB)); Set cell styles//Generate cells factory delivery NCell = Nrow.createcell (cellno++);//Create Cell ncell.setcellvalue (U    Tilfuns.datetimeformat (Cp.getcontract (). Getdeliveryperiod ()))//Set cell content Ncell.setcellstyle (TEXT (WB)); Set cell style//produce a cell sailing NCell = Nrow.createcell (cellno++);//Create Cell ncell.setcellvalue (Util    Funs.datetimeformat (Cp.getcontract (). Getshiptime ()))//Set cell content Ncell.setcellstyle (TEXT (WB)); Set cell style//Generate cell trade Terms NCell = Nrow.createcell (cellno++);//Create Cell Ncell.setcellvalue (c    P.getcontract (). Gettradeterms ());//Set cell content Ncell.setcellstyle (WB); Set cell style}//output Bytearrayoutputstream Bytearrayoutputstream = New Bytearrayoutputstream ();//Memory Buffer Wb.write (bytearrayoutputstream);

        Provide response object HttpServletResponse response = Servletactioncontext.getresponse ();
        Downloadutil downloadutil = new Downloadutil ();
        Downloadutil.download (Bytearrayoutputstream, Response, "A.xls");
    return NONE;
        }//The style of the large title public CellStyle bigtitle (Workbook wb) {CellStyle style = Wb.createcellstyle ();
        Font font = Wb.createfont ();
        Font.setfontname ("Song Body");
        Font.setfontheightinpoints ((short) 16);                   Font.setboldweight (Font.boldweight_bold);

        Fonts Bold Style.setfont (font);                 Style.setalignment (Cellstyle.align_center);      Horizontally centered style.setverticalalignment (cellstyle.vertical_center);
    Center the return style vertically;
        }//The style of the small title public CellStyle title (Workbook wb) {CellStyle style = Wb.createcellstyle ();
        Font font = Wb.createfont (); Font.setfonTname ("Blackbody");

        Font.setfontheightinpoints ((short) 12);

        Style.setfont (font);                 Style.setalignment (Cellstyle.align_center);      Horizontally centered style.setverticalalignment (cellstyle.vertical_center);                  Vertically centered style.setbordertop (Cellstyle.border_thin);               Style.setborderbottom (Cellstyle.border_thin) on thin lines;                 Lower thread style.setborderleft (Cellstyle.border_thin);                Left Thin Line style.setborderright (Cellstyle.border_thin);
    Right thin line return style;
        }//Text style public CellStyle text (workbook wb) {CellStyle style = Wb.createcellstyle ();
        Font font = Wb.createfont ();
        Font.setfontname ("Times New Roman");

        Font.setfontheightinpoints ((short) 10);

        Style.setfont (font);                   Style.setalignment (Cellstyle.align_left);      Lateral living Left style.setverticalalignment (cellstyle.vertical_center); Center vertically Style.setbordeRtop (Cellstyle.border_thin);               Style.setborderbottom (Cellstyle.border_thin) on thin lines; Lower thread Style.setborderleft (Cellstyle.border_thin)