Java uses POI to manipulate Excel files, achieve batch export, and import

First, the definition of POI

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, first to download the following interface:

After downloading, open "poi-bin-3.15-20160924.tar.gz" to get the jar packages that Excel requires and copy the jar packages into the project. For Excel that only operates 2003 and earlier versions, only Poi-3.15.jar is required, and if you need to operate on both 2007 and later versions, you need to copy

Poi-ooxml-3.15.jar
Poi-ooxml-schemas-3.15.jar

and copy in the Ooxml-lib directory Xmlbeans-2.6.0.jar (but somehow, I do not have the jar file Dom4j.jar) This file, or add Dom4j.jar, to prevent error.

Ii. working with JUnit for Excel testing

You first define Excel workbook objects, sheet objects, row objects, and cell objects.

The specific code is to be aware of whether it was before version 2007, or after 2007 (including the 2007 version): The following code is before the 2007 version:

This code simply writes the data to an Excel file to create

public static void Main (string[] args) throws Exception {        /**         * Note this is just a 07 version of the previous procedure that corresponds to an Excel file with the suffix named. xls         * 07 version and 07 after the procedure of the Excel file suffix named. xlsx         *        //Create New workbook        Hssfworkbook workbook = Hssfworkbook ();        New sheet        Hssfsheet sheet = workbook.createsheet ("Hello");        Create lines, line numbers are passed as parameters to the CreateRow () method, and the first line is calculated from 0        hssfrow row = sheet.createrow (0);        Create the cell, row has determined the line number, the column number is passed as a parameter to Createcell (), the first column is calculated from 0        Hssfcell cell = Row.createcell (2);        Sets the value of the cell, that is, the value of C1 (first row, third column)        cell.setcellvalue ("Hello sheet");        Output to disk        fileoutputstream fos = new FileOutputStream (New File ("E:\\root\\sheet\\11.xls"));        Workbook.write (FOS);        Workbook.close ();        Fos.close ();    }

Results such as:

Also can read the Excel file, get the Excel file data, and print it out, the code is as follows:

@Test public    void Testreadexcel () throws Exception    {        //Create input stream        fileinputstream fis = new FileInputStream (New File ("E:\\root\\sheet\\11.xls"));        Hssfworkbook workbook = new Hssfworkbook (FIS) via the constructor function;        get worksheet        Hssfsheet sheet = workbook.getsheetat (0);        Gets the row, the line number is passed as an argument to the GetRow method, the first line is calculated from 0        hssfrow row = sheet.getrow (0);        Gets the cell, row has determined the line number, the column number is passed as an argument to Getcell, the first column is calculated from 0        Hssfcell cell = Row.getcell (2);        Sets the value of the cell, that is, the value of C1 (first row, third column),        String cellvalue = Cell.getstringcellvalue ();        System.out.println ("The value of the third column in the first row is" +cellvalue);        Workbook.close ();        Fis.close ();    }

Results such as:

All of the above are 07 versions of the previous Excel file, that is, the suffix named. xls,07 and the 07 version after the Excel file suffix named. xlsx the object name of the corresponding workbook is also changed to:

Create Workbook        Xssfworkbook workbook = new Xssfworkbook ();

The code below creates an Excel file and saves the data to an Excel file:

@Test public    void Write07 () throws Exception    {        //Create workbook        Xssfworkbook workbook = new Xssfworkbook ();        New sheet        Xssfsheet sheet = workbook.createsheet ("Hello");        Create line, 0 indicates the first row        xssfrow row = sheet.createrow (0);        The creation unit cells number is determined by row and the column number is passed as a parameter to Createcell; The first column is calculated from 0        Xssfcell cell = Row.createcell (2);        Assigns a value to        the cell cell.setcellvalue ("Hello sheet");        Create output stream        fileoutputstream fos = new FileOutputStream (New File ("e:\\root\\sheet\\hello.xlsx"));        Workbook.write (FOS);        Workbook.close ();        Fos.close ();    }

The corresponding reading data, the code is as follows:

@Test public    void Read07 () throws Exception    {        //Create input stream        fileinputstream fis = new FileInputStream (new File ("e:\\root\\sheet\\hello.xlsx"));        Get Workbook        Xssfworkbook workbook = new Xssfworkbook (FIS) by input stream;        get worksheet        Xssfsheet sheet = workbook.getsheet ("Hello");        Get the row, 0 means the first row        xssfrow row = sheet.getrow (0);        The creation unit cells number is determined by row and the column number is passed as a parameter to Createcell; The first column is calculated from 0        Xssfcell cell = Row.getcell (2);        Assign a value to a cell        String cellvalue = Cell.getstringcellvalue ();        System.out.println ("The value of C1 is" +cellvalue);        int a[][] = new INT[10][30];        for (int i=0;i<a.length;i++)        {            System.out.println (i);        }        Workbook.close ();        Fis.close ();    }

The problem arises, and can be explained as a requirement: when you are not sure whether to read 07 (for example, 2003,95,97,2000) or after the 07 version of the Excel file, we certainly want the program to automatically recognize and create the appropriate object, to manipulate the Excel file, the code is as follows:

@Test public    void Reda03and07 () throws Exception    {        //Read version of 03 or 07        String FilePath = "e:\\root\\sheet\\ Hello.xlsx ";        if (Filepath.matches ("^.+\\" (? i) ((xls) | ( xlsx))        {            FileInputStream fis = new FileInputStream (filePath);            Boolean Is03excell = Filepath.matches ("^.+\\. i) (XLS) $ ")? True:false;            Workbook Workbook = Is03excell? New Hssfworkbook (FIS): New Xssfworkbook (FIS);            Sheet Sheet = workbook.getsheetat (0);            Row row = Sheet.getrow (0);            Cell cell = Row.getcell (2);            SYSTEM.OUT.PRINTLN ("The data for the first column of the first row is:" +cell.getstringcellvalue ());}    }

After learning the above examples, the next is to apply it, we often need to batch export and batch export data in a page, here involves the operation of the Excel file, of course, there are other file formats, we use a llist<user> list to save, Here we write a excelutil this tool class: The code is as follows:

Package Com.ittax.core.util;import Java.util.list;import Javax.servlet.servletoutputstream;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.hssfheader;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.ss.util.cellrangeaddress;import com.ittax.nsfw.user.entity.user;/** * Excel Tool class, support batch export * @author LIZEWU * */public class Excelutil {/** * will User's letter Import into an Excel file * @param userlist user list * @param out output table */public static void Exportuserexcel (List<user&gt ; Userlist,servletoutputstream out) {try{//1. Create workbook Hssfworkbook workbook = new Hssfworkboo            K (); 1.1 Create merged Cell object cellrangeaddress callrangeaddress = new cellrangeaddress (0,0,0,4);//start line, end row, start column, end column/       /1.2 Header Style     Hssfcellstyle Headstyle = Createcellstyle (Workbook, (short) 16);            1.3 column heading style Hssfcellstyle colstyle = Createcellstyle (Workbook, (short) 13);            2. Create worksheet Hssfsheet sheet = workbook.createsheet ("user list");            2.1 Load merged Cell object Sheet.addmergedregion (callrangeaddress);            Sets the default column width sheet.setdefaultcolumnwidth (25);            3. Create line//3.1 to create Header header row, and set header header Hssfrow row = sheet.createrow (0);                    Hssfcell cell = Row.createcell (0);            Load cell style Cell.setcellstyle (Headstyle);                        Cell.setcellvalue ("user list");            3.2 Create column headings, and set column headings Hssfrow row2 = Sheet.createrow (1);            String[] titles = {"username", "account", "department", "Gender", "E-mail"};                for (int i=0;i<titles.length;i++) {Hssfcell cell2 = Row2.createcell (i);                Load cell style Cell2.setcellstyle (Colstyle); Cell2.setcellvalue (Titles[i]); }//4. manipulate cells; Write the user list to Excel if (userlist! = null) {fo  R (int j=0;j<userlist.size (); j + +) {//Create data row, preceded by two lines, header header row and column header row Hssfrow                    ROW3 = Sheet.createrow (j+2);                    Hssfcell cell1 = Row3.createcell (0);                    Cell1.setcellvalue (Userlist.get (j). GetName ());                    Hssfcell cell2 = Row3.createcell (1);                    Cell2.setcellvalue (Userlist.get (j). Getaccount ());                    Hssfcell Cell3 = Row3.createcell (2);                    Cell3.setcellvalue (Userlist.get (j). Getdept ());                    Hssfcell CELL4 = Row3.createcell (3); Cell4.setcellvalue (Userlist.get (j). Isgender ()? "                    Male ":" female ");                    Hssfcell cell5 = Row3.createcell (4);                Cell5.setcellvalue (Userlist.get (j). Getemail ()); }}//5. Output workbook.write (out);           Workbook.close ();        Out.close ();        }catch (Exception e) {e.printstacktrace (); }}/** * * @param workbook * @param fontsize * @return cell style */private static Hssfcell Style Createcellstyle (Hssfworkbook workbook, short fontsize) {//TODO auto-generated method stub Hssfcellst        YLE style = Workbook.createcellstyle (); Style.setalignment (hssfcellstyle.align_center);//Horizontal Center style.setverticalalignment (hssfcellstyle.vertical_center)        ;//Vertical Center//Create font Hssffont font = Workbook.createfont ();        Font.setboldweight (Hssffont.boldweight_bold);        Font.setfontheightinpoints (fontsize);        Load font style.setfont (fonts);    return style; }}

This is followed by calling the method in Useservice and writing out the Exportexcel method:

@Override public void Exportexcel (List<user> userlist, servletoutputstream out) {//TODO auto-generated m    Ethod stub Excelutil.exportuserexcel (userlist, out);        } @Override public void importexcel (file file, String excelfilename) {//TODO auto-generated method stub            1. Create the input stream try {fileinputstream InputStream = new FileInputStream (file); Boolean is03excel = Excelfilename.matches ("^.+\\.            i) (XLS) $ ");            1. Read workbook Workbook Workbook = is03excel?new Hssfworkbook (inputstream): New Xssfworkbook (InputStream);            2. Read sheet Sheet Sheet = workbook.getsheetat (0);                3. Read rows//Determine the number of rows is greater than two because the data is inserted from the third line to insert if (Sheet.getphysicalnumberofrows () > 2) {                User user = null;                    Skip the first two lines for (int k=2;k<sheet.getphysicalnumberofrows (); k++) {//Read cells Row row0 = Sheet.getrow (k);                    user = new User ();                    Username Cell cell0 = Row0.getcell (0);                    User.setname (Cell0.getstringcellvalue ());                    Account Cell cell1 = Row0.getcell (1);                    User.setaccount (Cell1.getstringcellvalue ());                    Department Cell cell2 = Row0.getcell (2);                    User.setdept (Cell2.getstringcellvalue ());                    Set gender Cell Cell3 = Row0.getcell (3);                    Boolean gender = Cell3.getstringcellvalue () = = "Male"? True:false;                    User.setgender (gender);                    Set phone String mobile = "";                    Cell CELL4 = Row0.getcell (4);                    try {mobile = Cell4.getstringcellvalue (); } catch (Exception e) {//TODO auto-generated catch block double Dmoblie = Cell4.gEtnumericcellvalue ();                    Mobile = bigdecimal.valueof (Dmoblie). toString ();                    } user.setmobile (mobile);                    Set e-mail Cell cell5 = Row0.getcell (5);                    User.setemail (Cell5.getstringcellvalue ());                    The default user password is 123456 user.setpassword ("123456");                    User default state is valid User.setstate (user.user_state_valide);                Save user Save (username);            }} workbook.close ();        Inputstream.close ();        } catch (Exception e) {//TODO auto-generated catch block E.printstacktrace (); }    }

Finally, the service method is called in action:

Export user list public void Exportexcel () {try {//1. Find user List us                Erlist = Userservice.findobjects ();                2. Export HttpServletResponse response = Servletactioncontext.getresponse ();                The file format set here is Application/x-excel response.setcontenttype ("Application/x-excel"); Response.setheader ("Content-disposition", "attachment;filename=" + New String ("user list. xls". GetBytes (), "iso-8859-1"))                ;                Servletoutputstream outputstream = Response.getoutputstream ();                Userservice.exportexcel (userlist, OutputStream);            if (outputstream! = null) outputstream.close ();            }catch (Exception e) {e.printstacktrace ();                }} public String Importexcel () {if (userexcel!= null) { Determines whether the Excel file if (userexcelfilename.matches ("^.+\\. (? i) ((xls) | (xlsx))                $ ")) {Userservice.importexcel (userexcel, userexcelfilename);        }} return "List"; }

Note that the Servletoutputstream class should be used, and finally the bulk export and import data is implemented.

Export user results such as;

Import results such as;

Before import:

The results after import;

OK, the Excel file on the POI operation is over for the time being.

Java uses POI to manipulate Excel files, achieve batch export, and import