POI components: POI operations Excel

1. Introduction to Excel

An Excel file is a workbook workbook, where you can create multiple sheet sheet in a workbook, and a worksheet that contains multiple cell cells, which are made up of column rows (row), and are listed in uppercase English letters. From A to Z a total of 26 columns, and then from AA to AZ and 26 columns, and then from BA to BZ and then 26 columns and so on. Rows are represented by numbers, for example; A3 represents the first column of the third row, and E5 represents the fifth row, column fifth.

2. POI Tool Pack

POI The full name Poor obfuscation implementation, literally "poor fuzzy implementations", uses the POI interface to manipulate the read and write capabilities of Microsoft Office suite tools through Java. Official website:http://poi.apache.org, POI supports all versions of Office (. xls and. xlsx).

I downloaded version 3.14.

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M00/85/A4/wKiom1eq2MSzAyeSAADXRtx5dbU411.png "title=" Poi.png "alt=" Wkiom1eq2mszayesaadxrtx5dbu411.png "/>

Unzip the downloaded Poi-bin-3.14.zip file

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/85/A4/wKiom1eq2VrhnJy5AAB1rgP88k0016.png "title=" Poi_ Jar.png "alt=" Wkiom1eq2vrhnjy5aab1rgp88k0016.png "/>

Import the required jar packages into the project

Version	Jar Package
Operations 2003 and previous versions of Excel	Poi-3.14-20160307.jar
Operate on 2007 and later versions	Poi-ooxml-3.14-20160307.jar Poi-ooxml-schemas-3.14-20160307.jar And the jar package in the Ooxml-lib directory

3. POI programming

3.1. Object relationship between Poi object and Excel

There are several main objects in the POI package that correspond to several objects in Excel:

Hssfworkbook	Excel Workbook Workbook
Hssfsheet	Excel Worksheet Sheet
Hssfrow	Excel Line
Hssfcell	Excel Cell Cell

HSSFhorrible Spreadsheet Format (Java implementation of Excel)

HSSF is the abbreviation for horrible SpreadSheet format, also known as "Nasty spreadsheet format." Perhaps HSSF's name is a bit comical, in essence it is a very serious, formal API. With HSSF, you can read, write, and modify Excel files in plain Java code.

HSSF provides two types of Api:usermodel and Eventusermodel for read operations, the user model and the event-user model. The former is very well understood, the latter is more abstract, but the operation efficiency is much higher.

Worksheets (belonging to workbooks) Row (belongs to worksheet) Cell (belongs to row; determined by rows and columns) -------------manipulating Excel 1. Create/Read workbooks 2. Create/Read worksheets 3. Create/Read rows 4. Create/Read cells

3.2. Write operations to 03 versions of Excel

@Testpublic void Testwrite03excel () throws Exception{//1, create workbook hssfworkbook workbook = new Hssfworkbook ();//2, Create a worksheet Hssfsheet sheet = workbook.createsheet ("Hello World");//Specify the sheet name//3, create the line: Create line 3rd (first row starting from 0) Hssfrow row = Sheet.createrow (2);//4, creating cells: Create 3rd row 3rd column (first column starting from 0) Hssfcell cell = Row.createcell (2); Cell.setcellvalue ("Hello World"); /output to hard disk fileoutputstream OutputStream = new FileOutputStream ("d:/rk/test. xls");//output Excel to a specific address workbook.write ( OutputStream); Workbook.close (); Outputstream.close ();}

3.3. Write operations to 07 versions of Excel

@Testpublic void Testwrite07excel () throws Exception{//1, create workbook xssfworkbook workbook = new Xssfworkbook ();//2, create worksheet Xssfsheet sheet = workbook.createsheet ("Hello");//Specify Sheet Name//3, Create line: Create 3rd row Xssfrow row = Sheet.createrow (2);//4, Create cell: Create 3rd row 3rd column Xssfcell cell = Row.createcell (2); Cell.setcellvalue ("Hello World");//output to hard drive FileOutputStream OutputStream = new FileOutputStream ("d:/rk/test. xlsx");//output Excel to the specific address workbook.write (OutputStream); Workbook.close ( ); Outputstream.close ();}

3.4. Read operations on 03 versions of Excel

@Testpublic void Testread03excel () throws Exception{fileinputstream InputStream = new FileInputStream ("d:/rk/test. xls") ;//1, Reading workbook: Convert input to Workbook object Hssfworkbook workbook = new Hssfworkbook (inputstream);//2, read first worksheet hssfsheet sheet = Workbook.getsheetat (0);//3, read line: reads 3rd row hssfrow row = Sheet.getrow (2),//4, read cell: reads 3rd row 3rd column Hssfcell cell = Row.getcell (2); System.out.println (The contents of the 3rd row, column 3rd cell are: "+ cell.getstringcellvalue ()); Workbook.close (); Inputstream.close ();}

3.5. Read operations on 07 versions of Excel

@Testpublic void Testread07excel () throws Exception{fileinputstream InputStream = new FileInputStream ("d:/rk/test. xlsx") ;//1, reading workbook xssfworkbook workbook = new Xssfworkbook (inputstream);//2, reading the first sheet xssfsheet sheet = workbook.getsheetat (0) //3, read line: Reads line 3rd xssfrow row = Sheet.getrow (2),//4, reads cell: reads 3rd row 3rd column Xssfcell cell = Row.getcell (2); System.out.println (The contents of the 3rd row, column 3rd cell are: "+ cell.getstringcellvalue ()); Workbook.close (); Inputstream.close ();}

3.6. Read the 03 and 07 versions of Excel data in a unified manner

In the \poi-3.14\docs\apidocs directory, open index.html to view the POI API documentation

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M00/85/A4/wKioL1eq3miD_1_vAAA9KRWRsC4422.png "title=" Hssfworkbook.png "alt=" Wkiol1eq3mid_1_vaaa9krwrsc4422.png "/>

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M02/85/A4/wKioL1eq3tCQN8_IAABF3rff63Q126.png "title=" Xssfworkbook.png "alt=" Wkiol1eq3tcqn8_iaabf3rff63q126.png "/>

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/85/A5/wKiom1eq3wXTIIKDAAA6VdxGj_4652.png "title=" Workbook.png "alt=" Wkiom1eq3wxtiikdaaa6vdxgj_4652.png "/>

From the API documentation we learned that:

Hssfworkbook and Xssfworkbook have realized the workbook interface;

Hssfsheet and Xssfsheet realizes the sheet interface;

Hssfrow and Xssfrow realizes the row interface;

Hssfcell and Xssfcell realizes the cell interface;

Because these two types of processing objects together implement the corresponding interface, this will greatly facilitate and simplify the processing of different formats of Excel file encoding work: When working with 03 and 07 versions of Excel files, the use of a unified interface can be used to process two versions of Excel data.

@Testpublic void Testread03and07excel () throws exception{//string FileName = "d:/rk/test. xls"; String fileName = "d:/rk/test. xlsx"; if (Filename.matches ("^.+\\. i) ((xls) | ( xlsx)) {Boolean is03excel = Filename.matches ("^.+\\. i) (XLS) $ "); FileInputStream InputStream = new FileInputStream (fileName)//1, reading workbook Workbook workbook = Is03excel? New Hssfworkbook (InputStream): New Xssfworkbook (InputStream);//2, read the first sheet sheet sheet = workbook.getsheetat (0);//3, Read line: Read line 3rd row row = Sheet.getrow (2);//4, read cell: Read 3rd row 3rd column Cell cell = Row.getcell (2); System.out.println ("The contents of the 3rd row 3rd cell are:" +cell.getstringcellvalue ()); Workbook.close (); Inputstream.close ();}}

In a regular expression (? i) Case-insensitive matching can also be enabled via the embedded flag expression (? i).

3.7. Formatting Excel

You can format an Excel document by using formatting objects in the POI, or by setting the style of the Excel content. The main formatting objects in a POI often have merged cells, set cell fonts, borders, background colors, and so on.

(1) Merging cells

(2) Cell style

Cell content Location: centered horizontally, vertically centered

Font, bold, color, size

Cell background color

Set column width, row height

Merged cell objects (cellrangeaddress) belong to the workbook; Apply to Worksheets cellrangeaddress (int firstrow, int lastrow, int firstcol, int lastcol) Start line number, end line number, start column number, end column number A style belongs to a workbook; applied to a cell Fonts are part of the workbook, loaded in styles, and common styles applied to cells

@Testpublic  void testexcelstyle ()  throws exception{//1, creating a workbook Hssfworkbook workbook  = new hssfworkbook ();//1.1, creating merged cell objects: Merge the 3rd column of row 3rd to column 5th cellrangeaddress cellrangeaddress  = new cellrangeaddress (2, 2, 2, 4);//start line number, end line number, start column number, end column number//1.2, create cell style Hssfcellstyle  style = workbook.createcellstyle (); style.setalignment (hssfcellstyle.align_center);// Center style.setverticalalignment horizontally (hssfcellstyle.vertical_center);//Vertical Center//1.3, create font hssffont font =  Workbook.createfont (); Font.setfontname (hssffont.font_arial);//font is Arialfont.setcolor (HSSFColor.BLUE.index);// Font color is bluefont.setboldweight (hssffont.boldweight_bold),//Bold font font.setfontheightinpoints ((short) 16);//Set Font size// Load Fonts Style.setfont (font),//cell background//Set cell fill mode, mode is all foreground color style.setfillpattern (hssfcellstyle.solid_foreground);// Set Fill background color Style.setfillbackgroundcolor (HSSFColor.YELLOW.index);//Set fill foreground color Style.setfillforegroundcolor ( HSSFCOLOR.RED.INDEX);//2, create worksheet Hssfsheet sheEt = workbook.createsheet ("Hello");//Specifies the sheet name//2.1, loads the merged cell object Sheet.addmergedregion (cellrangeaddress); Sheet.setcolumnwidth (0, 20*256);//Column width units are 1/256//3 of characters, creating lines: Creating Line 3rd Hssfrow row = sheet.createrow ( 2); row.setheightinpoints;  //set the height of the row//4, create cell: Create 3rd row 3rd column Hssfcell cell = row.createcell (2) ;//Load Style cell.setcellstyle (style); Cell.setcellvalue ("Hello world");//output to hard drive fileoutputstream  Outputstream = new fileoutputstream ("d:/rk/test style. xls");//output Excel to a specific address workbook.write ( OutputStream); Workbook.close (); Outputstream.close ();}

The complete code Testpoi.java

package com.rk.test;import java.io.fileinputstream;import java.io.fileoutputstream;import  org.apache.poi.hssf.usermodel.hssfcell;import org.apache.poi.hssf.usermodel.hssfcellstyle;import  org.apache.poi.hssf.usermodel.hssffont;import org.apache.poi.hssf.usermodel.hssfrow;import  org.apache.poi.hssf.usermodel.hssfsheet;import org.apache.poi.hssf.usermodel.hssfworkbook;import  org.apache.poi.hssf.util.hssfcolor;import org.apache.poi.ss.usermodel.cell;import  org.apache.poi.ss.usermodel.row;import org.apache.poi.ss.usermodel.sheet;import  org.apache.poi.ss.usermodel.workbook;import org.apache.poi.ss.util.cellrangeaddress;import  org.apache.poi.xssf.usermodel.xssfcell;import org.apache.poi.xssf.usermodel.xssfrow;import  org.apache.poi.xssf.usermodel.xssfsheet;import org.apache.poi.xssf.usermodel.xssfworkbook;import  org.junit.test;public class testpoi {@Testpublic  VOID TESTWRite03excel ()  throws exception{//1, creating workbooks Hssfworkbook workbook = new hssfworkbook ();//2, create sheet Hssfsheet sheet = workbook.createsheet ("Hello world");//Specify Sheet Name//3, Create lines: Create line 3rd (first line starting from 0) Hssfrow row = sheet.createrow (2),//4, create cell: Create 3rd row 3rd column (first column starts from 0) hssfcell  Cell = row.createcell (2); Cell.setcellvalue ("Hello world");//output to hard drive fileoutputstream  Outputstream = new fileoutputstream ("d:/rk/test. xls");//output Excel to a specific address workbook.write (OutputStream ); Workbook.close (); Outputstream.close ();} @Testpublic  void testwrite07excel ()  throws exception{//1, creating a workbook Xssfworkbook workbook  = new xssfworkbook ();//2, create worksheet Xssfsheet sheet = workbook.createsheet ("Hello"); /Specify Sheet name//3, create row: Create line 3rd Xssfrow row = sheet.createrow (2),//4, create cell: Create 3rd row 3rd column xssfcell cell  = row.createcell (2); Cell.setcellvalue ("Hello world");//output to hard drive Fileoutputstream outputstream = new fileoutputstream ("d:/rk/test. xlsx");//output Excel to the specific address workbook.write (OutputStream); Workbook.close (); Outputstream.close ();} @Testpublic  void testread03excel ()  throws Exception{FileInputStream inputStream  = new fileinputstream ("d:/rk/test. xls");//1, reading workbook: Transferring input to Workbook object hssfworkbook workbook =  new hssfworkbook (InputStream);//2, read the first sheet hssfsheet sheet = workbook.getsheetat (0); /3, read line: Read line 3rd Hssfrow row = sheet.getrow (2),//4, read cell: Read 3rd row 3rd column hssfcell cell =  Row.getcell (2); System.out.println ("The contents of the 3rd row, column 3rd cell are:"  + cell.getstringcellvalue ()); Workbook.close (); Inputstream.close ( );} @Testpublic  void testread07excel ()  throws Exception{FileInputStream inputStream  = new fileinputstream ("d:/rk/test. xlsx"),//1, read workbook xssfworkbook workbook = new  Xssfworkbook (InputStream);//2. Read the first worksheet xssfsheet sheet = workbook.geTsheetat (0);//3, read line: Read line 3rd Xssfrow row = sheet.getrow (2),//4, read cell: Read 3rd row 3rd column Xssfcell cell  = row.getcell (2); System.out.println ("The contents of the 3rd row, column 3rd cell are:"  + cell.getstringcellvalue ()); Workbook.close (); Inputstream.close ( );} @Testpublic  void testread03and07excel ()  throws exception{//string filename =   "d:/rk/test. xls"; string filename =  "d:/rk/test. xlsx"; if (Filename.matches ("^.+\\. i) ((xls) | ( xlsx)) {boolean is03excel = filename.matches ("^.+\\. i) (XLS) $ "); Fileinputstream inputstream = new fileinputstream (fileName);//1, reading workbook workbook  Workbook = is03excel ? new hssfworkbook (InputStream)  : new  Xssfworkbook (InputStream);//2, read the first sheet sheet sheet = workbook.getsheetat (0);//3, read line: Reads row 3rd  row = sheet.getrow (2);//4, reading Cell: reads 3rd row 3rd column Cell cell = row.getcell (2); System.out.println (The contents of the 3rd row, column 3rd cell are: "+cell.getstrinGcellvalue ()); Workbook.close (); Inputstream.close ();}} @Testpublic  void testexcelstyle ()  throws exception{//1, creating a workbook Hssfworkbook workbook  = new hssfworkbook ();//1.1, creating merged cell objects: Merge the 3rd column of row 3rd to column 5th cellrangeaddress cellrangeaddress  = new cellrangeaddress (2, 2, 2, 4);//start line number, end line number, start column number, end column number//1.2, create cell style Hssfcellstyle  style = workbook.createcellstyle (); style.setalignment (hssfcellstyle.align_center);// Center style.setverticalalignment horizontally (hssfcellstyle.vertical_center);//Vertical Center//1.3, create font hssffont font =  Workbook.createfont (); Font.setfontname (hssffont.font_arial);//font is Arialfont.setcolor (HSSFColor.BLUE.index);// Font color is bluefont.setboldweight (hssffont.boldweight_bold),//Bold font font.setfontheightinpoints ((short) 16);//Set Font size// Load Fonts Style.setfont (font),//cell background//Set cell fill mode, mode is all foreground color style.setfillpattern (hssfcellstyle.solid_foreground);// Set Fill background color Style.setfillbackgroundcolor (HSSFColor.YELLOW.index);//Set fill foreground color STYLE.SETFILLFOREGROUNDCOlor (HSSFColor.RED.index);//2, create worksheet hssfsheet sheet = workbook.createsheet ("Hello");//Specify Sheet name// 2.1. Load merged Cell object Sheet.addmergedregion (cellrangeaddress); Sheet.setcolumnwidth (0, 20*256);//Column width unit is the 1/256//3 of characters, Create line: Create line 3rd Hssfrow row = sheet.createrow (2); row.setheightinpoints,  //set line height//4, Create cells: Create 3rd row 3rd column Hssfcell cell = row.createcell (2);//Load Style cell.setcellstyle (style); Cell.setcellvalue ("Hello world");//output to hard drive fileoutputstream outputstream = new  FileOutputStream ("d:/rk/test style. xls");//output Excel to a specific address workbook.write (OutputStream); Workbook.close (); O Utputstream.close ();}}

POI components: POI operations Excel