Java uses POI packets to read Excel documents

The project needs to parse the Excel document to obtain the data, has found some information on the net, unifies own this use, writes the experience:

1. The MAVEN project should include the following dependencies:

<dependency>        <groupId>org.apache.poi</groupId>        <artifactid>poi</artifactid >        <version>3.10-FINAL</version>    </dependency>    <dependency>        < groupid>org.apache.poi</groupid>        <artifactId>poi-ooxml</artifactId>        <version >3.10-FINAL</version>    </dependency>

Directly on the test class, there are well-commented in the Class:

 packageshindo. Java;Importjava.io.FileInputStream;Importjava.io.InputStream;Importjava.util.ArrayList;Importjava.util.List;Importorg.apache.poi.hssf.usermodel.HSSFCell;Importorg.apache.poi.hssf.usermodel.HSSFRow;Importorg.apache.poi.hssf.usermodel.HSSFSheet;Importorg.apache.poi.hssf.usermodel.HSSFWorkbook;Importorg.apache.poi.ss.usermodel.Cell;Importorg.apache.poi.xssf.usermodel.XSSFCell;Importorg.apache.poi.xssf.usermodel.XSSFRow;Importorg.apache.poi.xssf.usermodel.XSSFSheet;Importorg.apache.poi.xssf.usermodel.XSSFWorkbook; public classExcelutil { public Static voidmain (string[] Args) {String path= "d:\\ide\\workspace-neon\\java\\src\\refund.xls"; Try{List<List<String>> result =Newexcelutil (). Readxls (path);            System.out.println (result.size ());  for(inti = 0; I < result.size (); i++) {List<String> model =Result.get (i); System.out.println ("ordernum:" + model.get (0) + "-orderamount:" + model.get (1)); }        } Catch(Exception E) {e.printstacktrace (); }    }    /*** * @Title: Readxls * @Description: working with XLS files *@param @paramPath *@param @return    * @param @throwsException settings File *@returnlist<list<string>> return type *@throws* * From the code is not difficult to find its processing logic: * 1. Use InputStream to get the IO stream of the Excel file * 2. Then wear an in-memory Excel file Hssfworkbook type Object that represents the entire Excel File. * 3. Cycle through each page of the Excel file * 4. Cycle through each line of the page * 5. For each cell in each row, get the value of this cell * 6. Add the result of this line to a list array * 7. Add the result of each row to the last Total results * 8. After parsing, a list<list<string>> type of object has been acquired **/    Privatelist<list<string>> Readxls (String Path)throwsException {inputstream is=NewFileInputStream (path); //Hssfworkbook identifies the entire ExcelHssfworkbook Hssfworkbook =NewHssfworkbook (is); List<List<String>> result =NewArraylist<list<string>>(); intSize =hssfworkbook.getnumberofsheets (); //cycle through each page and process the current loop page         for(intNumsheet = 0; Numsheet < size; numsheet++) {            //Hssfsheet identifies a pageHssfsheet Hssfsheet =Hssfworkbook.getsheetat (numsheet); if(hssfsheet = =NULL) {                Continue; }            //processes the current page, looping through each row             for(intRowNum = 1; RowNum <= Hssfsheet.getlastrownum (); rownum++) {                //Hssfrow indicates that the rowHssfrow Hssfrow =Hssfsheet.getrow (rowNum); intMincolix =Hssfrow.getfirstcellnum (); intMaxcolix =Hssfrow.getlastcellnum (); List<String> rowlist =NewArraylist<string>(); //traverse a row to get processing for each cell element                 for(intColix = mincolix; Colix < maxcolix; colix++) {                    //Hssfcell represents a cellHssfcell cell =Hssfrow.getcell (colix); if(cell = =NULL) {                        Continue;                } rowlist.add (getstringval (cell));            } Result.add (rowlist); }        }        returnresult; }    /*** * @Title: readxlsx * @Description: handling xlsx Files *@param @paramPath *@param @return    * @param @throwsException settings File *@returnlist<list<string>> return type *@throws     */    Privatelist<list<string>> readxlsx (String Path)throwsException {inputstream is=NewFileInputStream (path); Xssfworkbook Xssfworkbook=NewXssfworkbook (is); List<List<String>> result =NewArraylist<list<string>>(); //cycle through each page and process the current loop page         for(xssfsheet Xssfsheet:xssfworkbook) {if(xssfsheet = =NULL) {                Continue; }            //processes the current page, looping through each row             for(intRowNum = 1; RowNum <= Xssfsheet.getlastrownum (); rownum++) {xssfrow Xssfrow=Xssfsheet.getrow (rowNum); intMincolix =Xssfrow.getfirstcellnum (); intMaxcolix =Xssfrow.getlastcellnum (); List<String> rowlist =NewArraylist<string>();  for(intColix = mincolix; Colix < maxcolix; colix++) {xssfcell Cell=Xssfrow.getcell (colix); if(cell = =NULL) {                        Continue;                } rowlist.add (cell.tostring ());            } Result.add (rowlist); }        }        returnresult; }    //problems that exist    /** In fact, sometimes we want to get the data is the data in excel, but finally found that the results are not ideal * If your Excel data is a number, you will find that the corresponding in Java has become a scientific notation.     * So when you get the value you have to do something special to make sure you get what you want. * Online practice is for numeric data formatting, to get the results you want. * Here's another way, before we take a look at the ToString () method in the Poi: * * This method is the POI method, from the source we can find that the process Is: * 1. Gets the type of cell * 2. Format the data according to the type and Output.     This creates a lot of things that are not what we want * so make a makeover on this method. */    /*public String toString () {switch (getcelltype ()) {case cell_type_blank:return ""; Case Cell_type_boolean:return Getbooleancellvalue ()?            "TRUE": "FALSE";            Case Cell_type_error:return Erroreval.gettext (geterrorcellvalue ());            Case Cell_type_formula:return Getcellformula (); Case Cell_type_numeric:if (dateutil.iscelldateformatted (THIS)) {dateformat sdf = new Sim                Pledateformat ("dd-mmm-yyyy") return Sdf.format (getdatecellvalue ());            } return Getnumericcellvalue () + "";            Case Cell_type_string:return Getrichstringcellvalue (). toString ();        Default:return "Unknown Cell Type:" + getcelltype (); }    }*/    /*** Transform poi The default ToString () method is as follows * @Title: Getstringval * @Description: 1. for unfamiliar types, or null to return "" "control String * 2. If it is a number, modify the cell type to string and return a string so that the number is not formatted *@param @paramcell *@param @returnSettings File *@returnString return type *@throws     */     public StaticString getstringval (hssfcell cell) {Switch(cell.getcelltype ()) { casecell.cell_type_boolean:returnCell.getbooleancellvalue ()? "TRUE": "FALSE";  caseCell.cell_type_formula:returnCell.getcellformula ();  caseCell.CELL_TYPE_NUMERIC:cell.setCellType (cell.cell_type_string); returnCell.getstringcellvalue ();  casecell.cell_type_string:returnCell.getstringcellvalue (); default:            return""; }    }}

Execute the above test class, the original Excel document content is as Follows:

After executing the results the console output is as Follows:

Java reads Excel documents using POI packets