Apache poi component operation excel, making reports (1)

 

The poi component of Apache is a powerful API used to operate Microsoft office suites in Java. It supports Word, Excel, and powperpoint. Of course, Excel is used mostly, because word and PowerPoint use less dynamic applications. This article introduces the Excel operation method in combination with poi.
The file structure of Office 2007 is completely different from that of Office 2003. Therefore, for office components of two versions, poi has different processing APIs for separate use. First, let's talk about some basic concepts of Excel. For an Excel file, this is called a workbook (workbook). After opening an Excel file, a tab such as sheet 1/2/3 is displayed below. You can click it to switch to a different sheet. This sheet is called a worksheet. Each worksheet is the area we edit. This is a two-dimensional table with Arabic numerals controlling the number of rows, starting from 1, and the program is still 0, similar to arrays and collections. The number of letters to control the number of columns. Starting from a, z is followed by two letters. For each row, we call it row, and the column is column. The row and column can determine a unique element, namely cell.
The poi component can easily manipulate these elements, but the initial exposure to poi may lead to fear, because to set each cell, whether it is an array or a collection, from the workbook, worksheet, the number of lines of code is not small, which cannot be avoided, but the results can be obtained by following the processing order.
With these basic concepts, we can operate on Excel. First, let's take a look at the required dependencies. Because 2007 is involved, additional dependencies are required.

 

Starting from reading Excel, we will first create an xls file of versions earlier than Excel 2003. Set several columns. The Excel table for storing student information is as follows:

 

Here, the name, gender, and class are text values, while age and score are numerical values, which must be distinguished when designing objects and processing. You can design this object as follows:

Package Org. ourpioneer. excel. bean;/*** student information ** @ author nanlei **/public class student {private string name; private string gender; private int age; private string sclass; private int score; public student () {super ();} public student (string name, string gender, int age, string sclass, int score) {super (); this. name = Name; this. gender = gender; this. age = age; this. sclass = sclass; this. score = score;} // The getter and setter methods are omitted. @ overridepublic string tostring () {return "student [age =" + age + ", Gender =" + gender + ", name = "+ name +", sclass = "+ sclass +", score = "+ score +"] ";}}

A parameter constructor is provided to generate an object and write it into an Excel file. This object can portray the data in the Excel file. below is the write program that loads and processes the Excel file and then reads the content, the read sequence is workbook> worksheet> row> cell. This analysis is easy. We define two Excel files with the same content, but the versions are different. The files are processed in 2003 and 2007.
When creating a workbook, you can receive an input stream object. The input stream object can be generated from the file object, so that you can continue. The worksheet is taken out, the row is taken out, and the cell is traversed, and the data is obtained. The Code is as follows:

Package Org. ourpioneer. excel; import Java. io. file; import Java. io. fileinputstream; import Java. io. ioexception; import Java. io. inputstream; import Java. util. arraylist; import Java. util. list; import Org. apache. poi. hssf. usermodel. hssfcell; import Org. apache. poi. hssf. usermodel. hssfrow; import Org. apache. poi. hssf. usermodel. hssfsheet; import Org. apache. poi. hssf. usermodel. hssfworkbook; import Org. ourpioneer. excel. be An. student;/*** example of POI reading Excel, which is divided into 2003 and 2007 ** @ author nanlei **/public class readexcel {Private Static string xls2003 = "C: \ student.xls "; private Static string xlsx2007 = "C: \ student.xlsx "; /*** sample method for reading excel2003 ** @ Param filepath * @ return */Private Static list <student> readfromxls2003 (string filepath) {file excelfile = NULL; // Excel file object inputstream is = NULL; // input stream object string cellstr = NULL; // Cell, And finally process the list by string <student> studentlist = new arraylist <student> (); // return the liststudent student = NULL of the encapsulated data; // try {excelfile = new file (filepath); is = new fileinputstream (excelfile); // obtain the file input stream hssfworkbook workbook2003 = new hssfworkbook (is ); // create the excel2003 file object hssfsheet sheet = workbook2003.getsheetat (0); // retrieve the first worksheet, index is 0 // start to traverse rows cyclically, the header is not processed, start from 1 for (INT I = 1; I <= sheet. getlastrownum (); I ++) {student = New student (); // instantiate the student object hssfrow ROW = sheet. getrow (I); // get the row object if (ROW = NULL) {// if it is null, do not process continue ;} // cyclically traverse the cell for (Int J = 0; j <row. getlastcellnum (); j ++) {hssfcell cell = row. getcell (j); // obtain the cell object if (cell = NULL) {// cell is empty. Set cellstr to an empty string cellstr = "";} else if (cell. getcelltype () = hssfcell. cell_type_boolean) {// process the Boolean value cellstr = string. valueof (cell. getbooleancellvalue ();} else if (cell. Getcelltype () = hssfcell. cell_type_numeric) {// process the numeric value cellstr = cell. getnumericcellvalue () + "";} else {// The rest are processed by the string cellstr = cell. getstringcellvalue () ;}// the following content is encapsulated into the bean according to the data location. If (j = 0) {student. setname (cellstr);} else if (j = 1) {student. setgender (cellstr);} else if (j = 2) {student. setage (New Double (cellstr ). intvalue ();} else if (j = 3) {student. setsclass (cellstr);} else {student. setscore (New Double (cellstr ). intvalue () ;}} studentlist. add (student); // data loading list} catch (ioexception e) {e. printstacktrace ();} finally {// close the file stream if (is! = NULL) {try {is. close ();} catch (ioexception e) {e. printstacktrace () ;}}return studentlist;}/*** main function ** @ Param ARGs */public static void main (string [] ARGs) {long start = system. currenttimemillis (); List <student> List = readfromxls2003 (xls2003); For (Student: List) {system. out. println (student);} long end = system. currenttimemillis (); system. out. println (end-Start) + "MS done! ");}}

If the table header is not processed, it starts from the row to be processed. The index of the entire sheet row starts from 0, while that in Excel is 1, this is similar to array/set. By default, numbers in cells are processed in the double type. Therefore, only strings can be converted to the double type, and INT values can be retrieved. Finally, execute the main function and obtain the following content:

In this way, you can get the list of objects, and then you have to wait for the database or directly perform the business logic as you like. The processing process of 2007 is similar to that of 2003. The difference is the object used. In 2003, the object is in hssf * format, while in 2007, the object is in xssf * format. The method is as follows:

Public static list <student> readfromxlsx2007 (string filepath) {file excelfile = NULL; // Excel file object inputstream is = NULL; // input stream object string cellstr = NULL; // cell, finally, list <student> studentlist = new arraylist <student> (); // return the liststudent student = NULL encapsulated data; // try {excelfile = new file (filepath); is = new fileinputstream (excelfile); // obtain the file input stream xssfworkbook workbook2007 = new xssfworkbook (is ); // create Create the excel2003 file object xssfsheet sheet = workbook2007.getsheetat (0); // retrieve the first worksheet. The index is 0 // start to traverse rows cyclically. the header is not processed, start from 1 for (INT I = 1; I <= sheet. getlastrownum (); I ++) {student = new student (); // instantiate the student object xssfrow ROW = sheet. getrow (I); // get the row object if (ROW = NULL) {// if it is null, do not process continue ;} // cyclically traverse the cell for (Int J = 0; j <row. getlastcellnum (); j ++) {xssfcell cell = row. getcell (j); // get the cell object if (cell = NULL) {// set cell to null and cellstr to null Cellstr = "";} else if (cell. getcelltype () = hssfcell. cell_type_boolean) {// process the Boolean value cellstr = string. valueof (cell. getbooleancellvalue ();} else if (cell. getcelltype () = hssfcell. cell_type_numeric) {// process the numeric value cellstr = cell. getnumericcellvalue () + "";} else {// The rest are processed by the string cellstr = cell. getstringcellvalue () ;}// the following content is encapsulated into the bean according to the data location. If (j = 0) {student. setname (cellstr);} else if (j = 1) {student. setg Ender (cellstr);} else if (j = 2) {student. setage (New Double (cellstr ). intvalue ();} else if (j = 3) {student. setsclass (cellstr);} else {student. setscore (New Double (cellstr ). intvalue () ;}} studentlist. add (student); // data loading list} catch (ioexception e) {e. printstacktrace ();} finally {// close the file stream if (is! = NULL) {try {is. Close () ;}catch (ioexception e) {e. printstacktrace () ;}} return studentlist ;}

 

Run the main function again and we get the following output:

It can be seen that the processing time for 2007 increases significantly, or the efficiency of 2003 is better, but 2007 is more convenient when using office components, and the processing efficiency of 2003 is better. How to use both? Make a comprehensive decision based on the Program business to see which part of the sacrifice is lost.
The following describes how to write a simple file, that is, prepare an input to write an Excel file. For demonstration, you can create an object directly. In actual application, the data may come from a database. Writing a file is the inverse process of file parsing. However, the poi component does not create files from cells, but also from the workbook, and then creates worksheets, rows, and cells, and finally writes the entire workbook to the file to complete the operation. Let's look at the specific writing method.

Package Org. ourpioneer. excel; import Java. io. file; import Java. io. fileoutputstream; import Java. util. arraylist; import Java. util. arrays; import Java. util. list; import Org. apache. poi. hssf. usermodel. hssfcell; import Org. apache. poi. hssf. usermodel. hssfrow; import Org. apache. poi. hssf. usermodel. hssfsheet; import Org. apache. poi. hssf. usermodel. hssfworkbook; import Org. ourpioneer. excel. bean. student;/*** generate an Excel example, 20 03 and 2007 ** @ author nanlei **/public class generateexcel {Private Static string xls2003 = "C: \ student.xls"; Private Static string xlsx2007 = "C: \ student.xlsx "; Private Static list <student> studentlist = NULL; Private Static student [] students = new student [4]; /*** static block initialization data */static {studentlist = new arraylist <student> (); students [0] = new student ("James", "male", 23, "Class 1", 94); students [1] = new student ("Li Si", "female", 20, "Class 1", 92); students [2] = new student ("Wang Wu", "male", 21, "Class 1", 87); students [3] = new student ("Zhao ", "female", 22, "Class 1", 83); studentlist. addall (arrays. aslist (students);}/*** method for creating a 2003 file ** @ Param filepath */public static void generateexcel2003 (string filepath) {// create a workbook object hssfworkbook workbook2003 = new hssfworkbook (); // create a worksheet object and name it hssfsheet sheet = workbook2003.createsheet ("student information statistics table"); // traverse the set Create row and cell for (INT I = 0; I <studentlist. size (); I ++) {// retrieves the student object student = studentlist. get (I); // create row hssfrow ROW = sheet. createrow (I); // start to create a cell and assign the value hssfcell namecell = row. createcell (0); namecell. setcellvalue (student. getname (); hssfcell gendercell = row. createcell (1); gendercell. setcellvalue (student. getgender (); hssfcell agecell = row. createcell (2); agecell. setcellvalue (student. getage (); HS Sfcell sclasscell = row. createcell (3); sclasscell. setcellvalue (student. getsclass (); hssfcell scorecell = row. createcell (4); scorecell. setcellvalue (student. getscore ();} // generate file = new file (filepath); fileoutputstream Fos = NULL; try {Fos = new fileoutputstream (File); workbook2003.write (FOS );} catch (exception e) {e. printstacktrace ();} finally {If (FOS! = NULL) {try {FOS. close ();} catch (exception e) {e. printstacktrace () ;}}}/*** main function ** @ Param ARGs */public static void main (string [] ARGs) {long start = system. currenttimemillis (); generateexcel2003 (xls2003); long end = system. currenttimemillis (); system. out. println (end-Start) + "MS done! ");}}

 

In this way, the 2003 Excel file is generated, which is the simplest operation and does not involve operations such as cell format. The 2007 method is to change the object name, which is very simple and will not be pasted here.