Java uses POI to enable reading of Excel files, compatible suffix xls and xlsx

The jar packages that need to be used are as follows:

If you are a MAVEN managed project, add the following dependencies:

<!--Https://mvnrepository.com/artifact/org.apache.poi/poi -  <Dependency>      <groupId>Org.apache.poi</groupId>      <Artifactid>Poi</Artifactid>      <version>3.14</version>  </Dependency>  <!--Https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -  <Dependency>      <groupId>Org.apache.poi</groupId>      <Artifactid>Poi-ooxml</Artifactid>      <version>3.14</version>  </Dependency>

The Poiutil code is as follows:

Importjava.io.FileNotFoundException; Importjava.io.IOException; ImportJava.io.InputStream; Importjava.util.ArrayList; Importjava.util.List; ImportOrg.apache.log4j.Logger; 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.springframework.web.multipart.MultipartFile; /*** Excel read/write tool class*/   Public classPoiutil {Private StaticLogger Logger = Logger.getlogger (poiutil.class); Private Final StaticString xls = "xls"; Private Final StaticString xlsx = "xlsx"; /*** read into Excel file and return after parsing *@paramfile *@throwsIOException*/       Public StaticList<string[]> readexcel (multipartfile file)throwsioexception{//Check Filecheckfile (file); //get Workbook working thin objectWorkbook Workbook =getworkbook (file); //creates a return object, takes the value in each row as an array, and returns all rows as a collectionlist<string[]> list =NewArraylist<string[]>(); if(Workbook! =NULL){               for(intSheetnum = 0;sheetnum < Workbook.getnumberofsheets (); sheetnum++){                  //get current sheet worksheetSheet Sheet =Workbook.getsheetat (Sheetnum); if(Sheet = =NULL){                      Continue; }                  //get the start line for the current sheet                intFirstrownum =Sheet.getfirstrownum (); //get the end line of the current sheet                intLastrownum =Sheet.getlastrownum (); //loop all rows except the first line                 for(intRowNum = Firstrownum+1;rownum <= lastrownum;rownum++){                      //Get Current lineRow row =Sheet.getrow (RowNum); if(Row = =NULL){                          Continue; }                      //get the Start column of the current row                    intFirstcellnum =Row.getfirstcellnum (); //get the number of columns in the current row                    intLastcellnum =Row.getphysicalnumberofcells (); String[] Cells=Newstring[row.getphysicalnumberofcells ()]; //Loop Current Line                     for(intCellnum = Firstcellnum; Cellnum < lastcellnum;cellnum++) {cell cell=Row.getcell (Cellnum); Cells[cellnum]=Getcellvalue (cell);                  } list.add (cells);          }} workbook.close (); }          returnlist; }       Public Static voidCheckfile (multipartfile file)throwsioexception{//determine if a file exists        if(NULL==file) {Logger.error ("File does not exist!" "); Throw NewFileNotFoundException ("file does not exist!) "); }          //Get file nameString FileName =File.getoriginalfilename (); //determine if the file is an Excel file        if(!filename.endswith (xls) &&!Filename.endswith (xlsx)) {Logger.error (FileName+ "not excel file"); Throw NewIOException (FileName + "not excel file"); }      }       Public StaticWorkbook getworkbook (multipartfile file) {//Get file nameString FileName =File.getoriginalfilename (); //Create a workbook Workbook object that represents the entire ExcelWorkbook Workbook =NULL; Try {              //get the IO stream for an Excel fileInputStream is =File.getinputstream (); //different Workbook implementation class objects depending on file suffix (xls and xlsx)            if(Filename.endswith (xls)) {//2003Workbook =NewHssfworkbook (IS); }Else if(Filename.endswith (xlsx)) {// -Workbook =NewXssfworkbook (IS); }          } Catch(IOException e) {logger.info (E.getmessage ()); }          returnworkbook; }       Public Staticstring Getcellvalue (cell cell) {string Cellvalue= ""; if(Cell = =NULL){              returnCellvalue; }          //read the number as a string to avoid 1 Read 1.0        if(Cell.getcelltype () = =cell.cell_type_numeric)          {Cell.setcelltype (cell.cell_type_string); }          //determine the type of data        Switch(Cell.getcelltype ()) { CaseCell.cell_type_numeric://DigitalCellvalue =string.valueof (Cell.getnumericcellvalue ());  Break;  CaseCell.cell_type_string://stringCellvalue =string.valueof (Cell.getstringcellvalue ());  Break;  CaseCell.cell_type_boolean://BooleanCellvalue =string.valueof (Cell.getbooleancellvalue ());  Break;  CaseCell.cell_type_formula://FormulaCellvalue =string.valueof (Cell.getcellformula ());  Break;  CaseCell.cell_type_blank://Null valueCellvalue = "";  Break;  CaseCell.cell_type_error://malfunctionCellvalue = "illegal character";  Break; default: Cellvalue= "Unknown Type";  Break; }          returnCellvalue; }  }

This tool class is primarily used to process Excel files that are uploaded from the client to the server. If you need to deal with local files, as long as the method Getworkbook to make certain changes, the Readexcel method of the formal parameter types also need to be modified.  

Source: Java uses POI to implement reading of Excel files, compatible suffix xls and xlsx

Java uses POI to enable reading of Excel files, compatible suffix xls and xlsx