Java parsing Excel (xls, xlsx two formats)

Source: Internet
Author: User

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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.