Java reads Excel (compatible with 03 and 07 formats)

Read Excel, first need to download the jar of poi, you can go to the official website, you can also download here

A simple explanation

Excel2003 and excel2007 difference is larger, the most intuitive feeling is not the same extension, haha

However, the use of POI APIs are interface-oriented programming, the actual use of the difference is not really big (know why interface programming?) The benefits are right here, O (∩_∩) o haha ~)

Code is the most intuitive, look directly at the code

Ii. examples

 Packagecom.hundsun.excel.test;ImportJava.io.File;ImportJava.io.FileInputStream;Importjava.io.FileNotFoundException;Importjava.io.IOException;ImportJava.io.InputStream;ImportOrg.apache.poi.hssf.usermodel.HSSFWorkbook;ImportOrg.apache.poi.ss.usermodel.Cell;ImportOrg.apache.poi.ss.usermodel.Row;ImportOrg.apache.poi.ss.usermodel.Sheet;ImportOrg.apache.poi.ss.usermodel.Workbook;ImportOrg.apache.poi.xssf.usermodel.XSSFWorkbook;Importorg.apache.xmlbeans.impl.piccolo.io.FileFormatException; Public classReadexceltest {Private Static FinalString Extension_xls = "XLS"; Private Static FinalString extension_xlsx = "XLSX"; /*** * <pre> * Get workbook objects (XLS and xlsx objects are different, but are workbook implementation classes) * Xls:hssfworkbook * XLSX:XSSFWORKBO OK *@paramFilePath *@return     * @throwsIOException * </pre>*/    PrivateWorkbook Getworkbook (String filePath)throwsIOException {Workbook Workbook=NULL; InputStream is=NewFileInputStream (FilePath); if(Filepath.endswith (Extension_xls)) {Workbook=NewHssfworkbook (IS); } Else if(Filepath.endswith (extension_xlsx)) {Workbook=NewXssfworkbook (IS); }        returnworkbook; }    /*** File Check *@paramFilePath *@throwsFileNotFoundException *@throwsfileformatexception*/    Private voidPrereadcheck (String FilePath)throwsFileNotFoundException, fileformatexception {//General CheckFile File =NewFile (FilePath); if(!file.exists ()) {            Throw NewFileNotFoundException ("Incoming file does not exist:" +FilePath); }        if(! (Filepath.endswith (Extension_xls) | |Filepath.endswith (extension_xlsx))) {            Throw NewFileformatexception ("Incoming file is not Excel"); }    }    /*** Read the contents of the Excel file *@paramFilePath *@throwsFileNotFoundException *@throwsfileformatexception*/     Public voidReadexcel (String FilePath)throwsFileNotFoundException, fileformatexception {//Check         This. Prereadcheck (FilePath); //Get Workbook ObjectWorkbook Workbook =NULL; Try{Workbook= This. Getworkbook (FilePath); //read the file a sheet a sheet to read             for(intNumsheet = 0; Numsheet < Workbook.getnumberofsheets (); numsheet++) {Sheet Sheet=Workbook.getsheetat (Numsheet); if(Sheet = =NULL) {                    Continue; } System.out.println ("=======================" + sheet.getsheetname () + "========================="); intFirstrowindex =Sheet.getfirstrownum (); intLastrowindex =Sheet.getlastrownum (); //read the first line, i.e., the table headerRow FirstRow =Sheet.getrow (Firstrowindex);  for(inti = Firstrow.getfirstcellnum (); I <= firstrow.getlastcellnum (); i++) {cell cell=Firstrow.getcell (i); String Cellvalue= This. Getcellvalue (Cell,true); System.out.print ("" + Cellvalue + "\ T"); } System.out.println (""); //reading data rows                 for(intRowIndex = Firstrowindex + 1; RowIndex <= Lastrowindex; rowindex++) {Row CurrentRow= Sheet.getrow (RowIndex);//Current Line                    intFirstcolumnindex = Currentrow.getfirstcellnum ();//first column                    intLastcolumnindex = Currentrow.getlastcellnum ();//last column                     for(intColumnIndex = Firstcolumnindex; ColumnIndex <= Lastcolumnindex; columnindex++) {Cell CurrentCell= Currentrow.getcell (columnindex);//Current CellString Currentcellvalue = This. Getcellvalue (CurrentCell,true);//the value of the current cellSystem.out.print (currentcellvalue + "\ T"); } System.out.println (""); } System.out.println ("======================================================"); }        } Catch(Exception e) {e.printstacktrace (); } finally {            if(Workbook! =NULL) {                Try{workbook.close (); } Catch(IOException e) {e.printstacktrace (); }            }        }    }    /*** Take the value of the cell *@paramCell Cells Object *@paramwhen Treatasstr is true, the value is taken as text (the text is taken, "1" is not taken as "1.0") *@return     */    PrivateString Getcellvalue (cell cell,Booleantreatasstr) {        if(Cell = =NULL) {            return""; }        if(treatasstr) {//Although the text is set in Excel, the digital text is also read incorrectly, such as "1" to take "1.0"//Add the following sentence and temporarily read it as textCell.setcelltype (cell.cell_type_string); }        if(Cell.getcelltype () = =Cell.cell_type_boolean) {            returnstring.valueof (Cell.getbooleancellvalue ()); } Else if(Cell.getcelltype () = =cell.cell_type_numeric) {            returnstring.valueof (Cell.getnumericcellvalue ()); } Else {            returnstring.valueof (Cell.getstringcellvalue ()); }    }}

Third, other

I try on the Excel2010, Excel is clearly set in the text type, but when reading, the number "1" read "1.0"

So in the Getcellvalue () method, change the cell format to make sure the number "1" text is read out or "1"

Java reads Excel (compatible with 03 and 07 formats)