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)