Java POI Operations Excel

1.1Excel Introduction

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.

1.2 POI Toolkit

There are two main toolkits for working with Excel in Java: JXL and POI. JXL can only manipulate Excel 95, 97, or 2000, which is the suffix of. xls. The poi can operate in Excel 95 and later versions of Excel with the suffix. xls and. xlsx two formats.

The POI 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, so choose Poi to explain.

1.3 POI Hello World

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

Using these objects, we simply create an Excel worksheet that writes and reads "Hello World" to the C1 cell inside:

        //Create a workbookHssfworkbook workbook=NewHssfworkbook (); //New worksheetHssfsheet sheet=workbook.createsheet ("Liu Yang")); //Create line, line number as parameter, first row is calculated from 0Hssfrow Row=sheet.createrow (0); //create cell, row has determined line number, column as parameter, first column is calculated starting from 0Hssfcell Cell=row.createcell (2); //Set cell (first row, third column)Cell.setcellvalue ("Hello word"); FileOutputStream OutputStream=NewFileOutputStream ("d:\\ workbook. xls");                Workbook.write (OutputStream); Outputstream.close ();

Then use the above objects to read "Hello World":

        //reading Excel FilesFileInputStream inputstream=NewFileInputStream ("d:\\ workbook. xls"); //convert an input stream to workbookHssfworkbook workbook=NewHssfworkbook (InputStream); //Get WorksheetsHssfsheet Sheet=workbook.getsheetat (0); //Get RowHssfrow Row=sheet.getrow (0); //get cell (first row, third column)Hssfcell Cell=row.getcell (2); System.out.println ("Cell contents are:" +cell.getstringcellvalue ());

1.4 Poi with Excel

Through the door example we know the following information:

1. Excel workbooks correspond to hssfworkbook objects of POI;

2. Excel's worksheets correspond to POI's Hssfsheet objects;

3, the line of Excel corresponds to the Poi Hssfrow object;

4. The Excel cell corresponds to the POI's Hssfcell object.

POI can also read and write to the Excel version after 07, read and write 03 version is the same, but the object name has changed, the original object of the beginning of the letter H into X, the operation is unchanged.

1. Excel workbooks correspond to xssfworkbook objects of POI;

2. Excel's worksheets correspond to POI's Xssfsheet objects;

3, the line of Excel corresponds to the Poi Xssfrow object;

4. The Excel cell corresponds to the POI's Xssfcell object.

The file suffix is. xlsx

Looking at the POI API documentation, we can query the properties and methods of all these objects in the POI.

From the API documentation we learned that:

Hssfworkbook and Xssfworkbook both implement the workbook interface;

Hssfsheet and Xssfsheet implement the sheet interface;

Hssfrow and Xssfrow implement the row interface;

Hssfcell and Xssfcell implement the Cell interface;

Because the two types of processing objects together implement the corresponding interface, it will be greatly convenient and simplify the simultaneous processing of different formats of Excel file encoding work. For example, when working with 03 and 07 versions of Excel files, you can analyze two versions of Excel data using a unified interface.

Poi reads both 03 and 07 versions of Excel.

Method One: After judging the name of the file, call the corresponding version of the read-in method.

Method Two: According to different versions with workbook interface to read files and unified processing.

Java POI Operations Excel