Java parsing Excel (xls, xlsx two formats)

One, the jar to be imported

1.commons-collections4-4.1.jar

2.poi-3.17-beta1.jar

3.poi-ooxml-3.17-beta1.jar

4.poi-ooxml-schemas-3.17-beta1.jar

5.xmlbeans-2.6.0.jar

Second, the main API

1.import Org.apache.poi.ss.usermodel.Workbook, corresponding to Excel documents;

2.import Org.apache.poi.hssf.usermodel.HSSFWorkbook, Excel document corresponding to XLS format;

3.import Org.apache.poi.xssf.usermodel.XSSFWorkbook, Excel document corresponding to XLSX format;

4.import Org.apache.poi.ss.usermodel.Sheet, corresponding to a Sheet in an Excel document;

5.import Org.apache.poi.ss.usermodel.Row, corresponding to a row in a sheet;

6.import Org.apache.poi.ss.usermodel.Cell, corresponding to a cell.

Third, the code is as follows

 Packagepoi;ImportJava.io.FileInputStream;Importjava.io.FileNotFoundException;Importjava.io.IOException;ImportJava.io.InputStream;Importjava.util.ArrayList;ImportJava.util.LinkedHashMap;Importjava.util.List;ImportJava.util.Map;ImportJava.util.Map.Entry;ImportOrg.apache.poi.hssf.usermodel.HSSFWorkbook;ImportOrg.apache.poi.ss.usermodel.Cell;ImportOrg.apache.poi.ss.usermodel.DateUtil;ImportOrg.apache.poi.ss.usermodel.Row;ImportOrg.apache.poi.ss.usermodel.Sheet;ImportOrg.apache.poi.ss.usermodel.Workbook;ImportOrg.apache.poi.xssf.usermodel.XSSFWorkbook; Public classTestpoi { Public Static voidMain (string[] args) {Workbook WB=NULL; Sheet Sheet=NULL; Row Row=NULL; List<Map<String,String>> list =NULL; String Celldata=NULL; String FilePath= "D:\\test.xlsx"; String columns[]= {"Name", "Age", "score"}; WB=Readexcel (FilePath); if(WB! =NULL){            //used to store data in a tableList =NewArraylist<map<string,string>>(); //get the first sheetSheet = wb.getsheetat (0); //get maximum number of rows            intRowNum =sheet.getphysicalnumberofrows (); //get first rowrow = Sheet.getrow (0); //get maximum number of columns            intColnum =Row.getphysicalnumberofcells ();  for(inti = 1; i<rownum; i++) {Map<String,String> map =NewLinkedhashmap<string,string>(); Row=Sheet.getrow (i); if(Row! =NULL){                     for(intj=0;j<colnum;j++) {Celldata=(String) Getcellformatvalue (Row.getcell (j));                    Map.put (Columns[j], celldata); }                }Else{                     Break;            } list.add (map); }        }        //traverse the parsed list         for(map<string,string>map:list) {             for(entry<string,string>Entry:map.entrySet ()) {System.out.print (Entry.getkey ()+ ":" +entry.getvalue () + ",");        } System.out.println (); }    }    //Read Excel     Public StaticWorkbook readexcel (String filePath) {Workbook WB=NULL; if(filepath==NULL){            return NULL; } String extstring= Filepath.substring (Filepath.lastindexof ("."))); InputStream is=NULL; Try{ is=NewFileInputStream (FilePath); if(". xls". Equals (extstring)) {                returnWB =NewHssfworkbook (IS); }Else if(". xlsx". Equals (extstring)) {                returnWB =NewXssfworkbook (IS); }Else{                returnWB =NULL; }                    } Catch(FileNotFoundException e) {e.printstacktrace (); } Catch(IOException e) {e.printstacktrace (); }        returnWB; }     Public Staticobject Getcellformatvalue (cell cell) {Object Cellvalue=NULL; if(cell!=NULL){            //determine the cell type            Switch(Cell.getcelltype ()) { Casecell.cell_type_numeric:{Cellvalue=string.valueof (Cell.getnumericcellvalue ());  Break; }             Casecell.cell_type_formula:{//determine if the cell is a date format                if(dateutil.iscelldateformatted (cell)) {//Convert to date format Yyyy-mm-ddCellvalue =Cell.getdatecellvalue (); }Else{                    //DigitalCellvalue =string.valueof (Cell.getnumericcellvalue ()); }                 Break; }             Casecell.cell_type_string:{Cellvalue=Cell.getrichstringcellvalue (). getString ();  Break; }            default: Cellvalue= ""; }        }Else{Cellvalue= ""; }        returnCellvalue; }}

Iv. Results of operation

Before running the code to ensure that there is a test.xlsx document under the D disk, otherwise the file cannot find an exception; the header in the Excel document should correspond to string columns[] = {"Name", "Age", "score"} in the code.

　　

　　

　　

Java parsing Excel (xls, xlsx two formats)