Apache POI Usage Detailed

Read CatalogueStart 1.POI structure with common class 2.Excel basic operation 3.Excel Cell operations 4. Use the Excel Formula 5. Use the Graphics 6.Excel table to operate the 7.Excel row and column operation 8.Excel Other functions 9.POI basic operations on word back to top1.POI structure and common class (1) POI IntroductionApache POI is an open source project for the Apache Software Foundation, where POI provides APIs to Java programs to read and write to Microsoft Office format archives. NET developers can take advantage of Npoi (POI for. net) to access the capabilities of Microsoft Office documents.(2) POI structure Description Package Name DescriptionHSSF provides the ability to read and write Microsoft Excel xls format files. XSSF provides the ability to read and write Microsoft Excel OOXML xlsx format files. HWPF provides the ability to read and write Microsoft Word doc format files. HSLF provides the ability to read and write Microsoft PowerPoint format files. HDGF provides the ability to read Microsoft Visio format archives. HPBF provides the ability to read Microsoft Publisher format files. HSMF provides the ability to read Microsoft Outlook format files.(3) POI Common class descriptionClass name Description Hssfworkbook Excel Document object Hssfsheet Excel form
Hssfrow lines of Excel
Hssfcell Excel's lattice unit
Hssffont Excel Font Hssfdataformat The date format of the grid cell Hssfheader Excel document sheet header hssffooter Excel document S Heet footer Hssfcellstyle grid cell style hssfdateutil Date Hssfprintsetup print hssferrorconstants error information table back to topbasic operation of 2.Excel (1) Create workbook and sheet

     
     
      
      public class Test00
      
      {public
      
      static void Main (string[] args) throws IOException
      
      {
      
      String filepath= "d:\\ users\\lizw\\ desktop \\POI\\sample.xls ";//file path
      
      hssfworkbook workbook = new Hssfworkbook ();//create Excel File (workbook)
      
      Hssfsheet sheet = workbook.createsheet ();//Create worksheet (sheet)
      
      sheet = workbook.createsheet ("Test");//create sheet (sheet)
      
      FileOutputStream out = new FileOutputStream (filePath);
      
      Workbook.write (out);//save Excel File
      
      out.close ();//close file stream
      
      System.out.println ("ok!");}
      
      }
     
     

(2) Create a cell

      
      
       
       Hssfsheet sheet = workbook.createsheet ("Test");//Create worksheet (sheet)
       
       hssfrow row = sheet.createrow (0);//Create line, starting
       
       from 0 Hssfcell cell = Row.createcell (0);//The cell that created the row is also starting from 0
       
       cell.setcellvalue ("Li Zhiwei");//Set cell contents
       
       Row.createcell (1). Setcellvalue (false);//Set cell contents, Overload
       
       Row.createcell (2). Setcellvalue (New Date ());//Set cell contents, reload
       
       Row.createcell (3). Setcellvalue (12.345);//Setting cell contents, overloading
      
      

(3) Create document summary information

      
      
       
       Workbook.createinformationproperties ();//Create Document Information
       
       documentsummaryinformation dsi= Workbook.getdocumentsummaryinformation ();//summary Information
       
       dsi.setcategory ("Category: Excel File");//category
       
       Dsi.setmanager ("Manager : Li Zhiwei ");//manager
       
       Dsi.setcompany (" Company:--");//company
       
       summaryinformation si = workbook.getsummaryinformation ();//summary information
       
       Si.setsubject ("Subject:-");//Theme
       
       Si.settitle ("title: Test Document");//title
       
       Si.setauthor ("Author: Li Zhiwei");//author
       
       Si.setcomments ("Note: Poi test document");//Notes
      
      

(4) Creating annotations

      
      
       
       Hssfsheet sheet = workbook.createsheet ("Test");//create sheet (sheet)
       
       hssfpatriarch patr = Sheet.createdrawingpatriarch ( );
       
       Hssfclientanchor anchor = patr.createanchor (0, 0, 0, 0, 5, 1, 8, 3);//create annotation position
       
       hssfcomment comment = Patr.createcellcomm ENT (anchor);//Create Annotation
       
       comment.setstring (new hssfrichtextstring ("This is an annotated paragraph. );//Set annotation content
       
       comment.setauthor ("Li Zhiwei");//Set annotation author
       
       comment.setvisible (TRUE);//Set annotation default display
       
       Hssfcell cell = Sheet.createrow (2). Createcell (1);
       
       Cell.setcellvalue ("test");
       
       Cell.setcellcomment (comment);//assigning Annotations to cells
      
      

Create an annotation location Hssfpatriarch.createanchor (dx1, Dy1, DX2, Dy2, col1, Row1, col2, Row2) method parameter Description:

     
     
      
      DX1 The X-axis offset in the 1th cell dy1 the offset of the y-axis in the
      
      1th cell dx2 The x-axis offset in the
      
      2nd cell dy2 The offset of the
      
      y-axis in the 2nd cell
      
      col1 The column number of the 1th cell
      
      row1 Row number of the 1th cell
      
      col2 The column number of the 2nd cell
      
      row2 The row number of the 2nd cell
     
     

(5) Creating headers and footers

      
      
       
       Hssfsheet sheet = workbook.createsheet ("Test");//create sheet (sheet)
       
       Hssfheader header =sheet.getheader ();//Get Header
       
       Header.setleft ("left of Header");
       
       Header.setright ("Right of Header");
       
       Header.setcenter ("middle of Header");
       
       Hssffooter footer =sheet.getfooter ();//Get Footer
       
       footer.setleft ("left footer");
       
       Footer.setright ("Right of Footer");
       
       Footer.setcenter ("middle of footer");
      
      

You can also use the label definitions from office, which you can access through Hssfheader or Hssffooter, which are static properties, listed below:

     
     
      
      Hssfheader.tab &a	table name
      
      hssfheader.file &f	file name
      
      hssfheader.startbold &b	Bold Start
      
      Hssfheader.endbold &b	Bold End
      
      Hssfheader.startunderline &u	underline start
      
      hssfheader.endunderline &u underline	End
      
      hssfheader.startdoubleunderline &e	double underline start
      
      Hssfheader.enddoubleunderline &e	Double underline end
      
      Hssfheader.time &t	time
      
      hssfheader.date &d	Date
      
      Hssfheader.numpages &n	Total number of pages
      
      hssfheader.page &p	Current page number
     
     

Back to Top cell operation in 3.Excel (1) Formatting

       
       
        
        Hssfsheet sheet = workbook.createsheet ("Test");//Create worksheet (sheet) hssfrow
        
        Row=sheet.createrow (0);
        
        Format date--using the format embedded in Excel Hssfcell Cell=row.createcell (0);
        
        Cell.setcellvalue (New Date ());
        
        Hssfcellstyle Style=workbook.createcellstyle ();
        
        Style.setdataformat (Hssfdataformat.getbuiltinformat ("M/d/yy h:mm"));
        
        Cell.setcellstyle (style);
        
        Set reserved 2 decimal places--using Excel Embedded Format Cell=row.createcell (1);
        
        Cell.setcellvalue (12.3456789);
        
        Style=workbook.createcellstyle ();
        
        Style.setdataformat (Hssfdataformat.getbuiltinformat ("0.00"));
        
        Cell.setcellstyle (style);
        
        Set currency format--use a custom format Cell=row.createcell (2);
        
        Cell.setcellvalue (12345.6789);
        
        Style=workbook.createcellstyle (); Style.setdataformat (workbOok.createdataformat (). GetFormat ("￥#,# #0"));
        
        Cell.setcellstyle (style);
        
        Set percent format--use a custom format Cell=row.createcell (3);
        
        Cell.setcellvalue (0.123456789);
        
        Style=workbook.createcellstyle ();
        
        Style.setdataformat (Workbook.createdataformat (). GetFormat ("0%"));
        
        Cell.setcellstyle (style);
        
        Set the Chinese capitalization format--Use the custom format Cell=row.createcell (4);
        
        Cell.setcellvalue (12345);
        
        Style=workbook.createcellstyle ();
        
        Style.setdataformat (Workbook.createdataformat (). GetFormat ("[dbnum2][$-804]0"));
        
        Cell.setcellstyle (style);
        
        Set scientific notation format--using a custom format Cell=row.createcell (5);
        
        Cell.setcellvalue (12345);
        
        Style=workbook.createcellstyle ();
        
        Style.setdataformat (Workbook.createdataformat (). GetFormat ("0.00E+00")); Cell.setcellStyle (style);