POI 3.8 Read 2003 with 2007 format Excel (xls, xlsx)

Nonsense less say directly on the code, remember is poi3.8 version AH. The method entry is unique, automatically determines the format, uses the interface reference, and automatically chooses the execution method.

Method Entry:

public static arraylist<string[]> explanexceltolist (String fileName) {
arraylist<string[]> list = new arraylist<string[]> ();
Workbook wb = null;
try {
WB = Get2003workbook (new FileInputStream (FileName));
if (WB = = null) {
WB = Get2007workbook (new FileInputStream (FileName));
if (WB = = null) {
throw new RuntimeException ("Unrecognized format, unexpected Excel type (" + FileName + ")");
}
}
List = Explanexceltolist (WB);

} catch (IOException e) {
E.printstacktrace ();
}
return list;
}

Package Com.order.cc.outboundcall.impexldate.svc;import Java.io.fileinputstream;import Java.io.IOException;import Java.io.inputstream;import Java.text.simpledateformat;import Java.util.arraylist;import Java.util.Date;import Org.apache.poi.hssf.usermodel.hssfworkbook;import Org.apache.poi.ss.formula.eval.erroreval;import Org.apache.poi.ss.usermodel.cell;import Org.apache.poi.ss.usermodel.cellstyle;import Org.apache.poi.ss.usermodel.dataformatter;import Org.apache.poi.ss.usermodel.dateutil;import Org.apache.poi.ss.usermodel.richtextstring;import Org.apache.poi.ss.usermodel.row;import Org.apache.poi.ss.usermodel.sheet;import Org.apache.poi.ss.usermodel.workbook;import Org.apache.poi.xssf.usermodel.xssfworkbook;import Org.apache.struts.upload.formfile;public class ExcelHelper { private static Integer Sheet_page_num = 0;//Read SHEET page The first page public static arraylist<string[]> explanexceltolist ( String fileName) {arraylist<string[]> list = new arraylist<string[]> (); Workbook wb = null;try {wb = Get2003workbook (new FileInputStream (FileName)), if (WB = = NULL) {WB = Get2007workbook (New FileInputStream (Filena Me), if (WB = = null) {throw new RuntimeException ("Unrecognized format, unexpected Excel type (" + FileName + ")");}} List = Explanexceltolist (WB);} catch (IOException e) {e.printstacktrace ();} return list;} public static arraylist<string[]> Explanexceltolist (Workbook wb) {arraylist<string[]> reslist = new Arraylist<string[]> (); if (WB = = null) {return reslist;} try {wb.setmissingcellpolicy (row.return_blank_as_null);//blank set to null//read first chapter table contents Sheet sheet = wb.getsheetat (sheet_ Page_num); if (sheet = = null) {return reslist;} The contents of the looping output table reslist = Explantsheet (sheet);} catch (Exception e) {e.printstacktrace ();} return reslist;} /** * * Function Description Using interface parsing sheet, support Hssfsheet and xssfsheet format * @ Creator YXH * @ time 2016-4-26 * @ parameter Description @param list * @ parameter Description @param sheet * @ parameter return description void */public static arraylist<string[]> explantsheet (Sheet Sheet) {arraylist<string[]> list = new A rraylist&Lt String[]> (); if (sheet = = null) {return list;} int rowNum = sheet.getrow (0)! = null?  Sheet.getrow (0). Getphysicalnumberofcells (): 0;//defines the number of bits of the array through the header, determining the fixed size if per row if (RowNum = = 0) {rowNum = Sheet.getrow (1)! = null?  Sheet.getrow (1). Getphysicalnumberofcells (): 0;//prevents the header}for from being written (int i = 0; i < sheet.getphysicalnumberofrows (); i++) {// Loop line Row row = Sheet.getrow (i); if (row = = null) {continue;} string[] Contentarr = new String[rownum];for (int j = 0; J < RowNum; J + +) {//cyclic column cell cell = Row.getcell (j); String text = ""; if (cell! = null) {text = Formatcell (cell);} CONTENTARR[J] = text;} List.add (Contentarr);} return list;} /** * * Function description Format Table contents * * @ Creator YXH * @ time 2016-4-26 * @ parameter Description @param cell * @ parameter Description @return * @ parameter return description String */public static S Tring Formatcell (cell cell) {Dataformatter _formatter = new Dataformatter (); switch (Cell.getcelltype ()) {case Cell.cell_ Type_string://April 27, 2016 11:19:20 in Excel as a string return Cell.getrichstringcellvalue (). getString (); case Cell.cell_ type_numeric://Numeric if (Dateutil.iscelldateformatted (cell)) {//Date Poireturn datetostring (Cell.getdatecellvalue ());} else {return _ Formatter.formatcellvalue (cell);} Case Cell.CELL_TYPE_BOOLEAN:return cell.getbooleancellvalue () = = true? "True": "false";//boolean//turn into Stringcase Cell.CELL_TYPE_ERROR:return Erroreval.gettext (Cell.geterrorcellvalue ()); /Return error code case cell.cell_type_formula://Formula switch (Cell.getcachedformularesulttype ()) {case cell.cell_type_string: richtextstring str = Cell.getrichstringcellvalue (); if (str! = null && str.length () > 0) {return str.tostring (); }case Cell.CELL_TYPE_NUMERIC:CellStyle style = Cell.getcellstyle (), if (style = = null) {return Cell.getnumericcellvalue ( ) + "";//double turns to string} else {return _formatter.formatrawcellcontents (Cell.getnumericcellvalue (), Style.getdataformat (), style.getdataformatstring ());} Case Cell.CELL_TYPE_BOOLEAN:return Cell.getbooleancellvalue ()? "True": "false";//boolean//turn into Stringcase Cell.CELL_TYPE_ERROR:return Erroreval.gettext (cell.geterrorceLlvalue ());} Default:throw new RuntimeException ("Unexpected cell type (" + cell.getcelltype () + ")");}} public static String datetostring (date date) {SimpleDateFormat SDF = new SimpleDateFormat ("Yyyy-mm-dd HH:mm:ss"); return s Df.format (date);} public static string datetostring (string date) {SimpleDateFormat SDF = new SimpleDateFormat ("Yyyy-mm-dd HH:mm:ss"); return Sdf.format (date);} public static void CloseFile (Formfile file) {if (file! = null) {try {File.destroy ()} catch (Exception E2) {E2.printstackt Race ();}}} public static Workbook Get2003workbook (InputStream are) {Workbook WB = null;try {wb = new Hssfworkbook (is);} catch (Excepti On e) {return WB;} return WB;} public static Workbook Get2007workbook (InputStream are) {Workbook WB = null;try {wb = new Xssfworkbook (is);} catch (Excepti On e) {return WB;} return WB;} public static Boolean isExcel2003 (InputStream are) {try {new Hssfworkbook (is);} catch (Exception e) {return false;} return true;} public static Boolean isExcel2007 (InputStream is) {try {new Xssfworkbook (IS);} catch (Exception e) {return false;} return true;} public static void Main (string[] args) {//Formfile file=null;//explanexceltolist (file);}}

POI 3.8 Read 2003 with 2007 format Excel (xls, xlsx)