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

Source: Internet
Author: User

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

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.