Background call:
Multipartfile file;
List<string[]> exceldate
PackageCom.holike.crm.partner.sys;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;/*** Parse Excel upload data *@authorAdministrator **/ Public classExceldata {Private Static FinalLogger log = Logger.getlogger (exceldata.class); Public StaticList<string[]> getexceldata (multipartfile file)throwsioexception{checkfile (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.getlastcellnum (); String[] Cells=NewString[row.getlastcellnum ()]; //Loop Current Line for(intCellnum = Firstcellnum; Cellnum < lastcellnum;cellnum++) {cell cell=Row.getcell (Cellnum); Cells[cellnum]=Getcellvalue (cell); } list.add (cells); } } } returnlist; } /*** Check file *@paramfile *@throwsIOException*/ Public Static voidCheckfile (multipartfile file)throwsioexception{//determine if a file exists if(NULL==file) {Log.error ("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") {log.error (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")){ //2007 and above 2007Workbook =NewXssfworkbook (IS); } } Catch(IOException e) {log.error (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; } }
= Exceldata.getexceldata (file);
Java background parsing Excel upload Data tool class