Java read Excel file compatible 97-2013 V2.0

Note There are struts in the upload file and springmvc some different. Read and write are the same  fix the problem of deleting cache file usage

Package Com.telling.cw.util.poi;import Org.apache.poi.hssf.usermodel.*;import Org.apache.poi.ss.usermodel.dataformatter;import Org.apache.poi.ss.usermodel.dateutil;import Org.apache.poi.xssf.usermodel.xssfcell;import Org.apache.poi.xssf.usermodel.xssfrow;import Org.apache.poi.xssf.usermodel.xssfsheet;import Org.apache.poi.xssf.usermodel.xssfworkbook;import Org.springframework.web.multipart.multipartfile;import Java.io.*;import Java.text.decimalformat;import Java.text.simpledateformat;import Java.util.arraylist;import Java.util.hashmap;import Java.util.List;import  java.util.map;/** * POI read Excel Support 2003--2007 and above file * * @author Sunny * @version V 2.0 * @CreatTime 2013-11-19 @ */public Class Excelutils {/** * Merge method, read Excel file * Automatically recognize read based on file name * Excel document supported in 97-2013 format * * @param filename * Upload file name * @p Aram File * uploaded files * @return returns the list content format: * Each row of data is the header of the corresponding column as the key content value such as Excel table is: * =============== * a | B | C | D * ===|===|===|=== * 1 | 2 | 3 | 4 *---|---|---|---* a | B | C | d *---------------* return value map: * map1:a:1 b:2 c:3 d:4 * map2:a:a b:b c:d d:d * @throws java.io.IOException */@Su Ppresswarnings ("Rawtypes") public static list<map> Readexcel (String filename,multipartfile file) throws exception{//Prepare the return value list list<map> valuelist=new arraylist<map> ();//String tempsavepath= "tmp";// The folder name of the cache file directory (struts) string filepathtemp= "/mnt/b2b/tmp";//cache file directory string Tmpfilename= System.currenttimemill Is () + "."        +getextensionname (FileName); String Extensionname=getextensionname (FileName);//String filepathtemp= Servletactioncontext.getservletcontext (). Getrealpath (Tempsavepath);//strut gets the project path file FileList = new file (filepathtemp), if (!filelist. Exists () &&!fi    Lelist. Isdirectory ()) {filelist. mkdir ();        } String FilePath = Filepathtemp+system.getproperty ("File.separator") +tmpfilename;    File Tmpfile = new file (FilePath); Copy file to server cache directory (under Project)//copy (file,tmpfile);//stuts method        Copy (file, filepathtemp,tmpfilename);//spring mvc method//system.out.println ("suffix name:" +extensionname); if (extensionname . Equalsignorecase ("xls")) {valuelist=readexcel2003 (FilePath);} else if (extensionname.equalsignorecase ("xlsx")) {valuelist=readexcel2007 (FilePath);}        Delete cache file Tmpfile.delete (); return valueList; }/** * Read 97-2003 format * @param filePath file path * @throws java.io.IOException */@SuppressWarnings ("Rawtypes") public static list& Lt Map> readExcel2003 (String filePath) throws ioexception{//returns the result set list<map> valuelist=new arraylist<map> (        ); FileInputStream fis=null;try {fis=new fileinputstream (FilePath); Hssfworkbook Wookbook = new Hssfworkbook (FIS);//Create a reference to an Excel workbook file Hssfsheet sheet = wookbook.getsheetat (0);//In an Excel document, The default index for the first sheet is 0int rows = sheet.getphysicalnumberofrows ();//Gets all the lines in the Excel file Map<integer,string> keys=new Hashmap<integer, string> (); int cells=0;//traverse Line (1th row header) Prepare the Keyhssfrow firstrow = Sheet.getrow (0) in the map; if (firstrow ! = NULL) {//Gets all the columns in the Excel file cells = Firstrow.getphysicalnumberofcells ();//Traversal column for (int j = 0; J < cells; J + +) {//Get to The value of the column try {Hssfcell cell = Firstrow.getcell (j); String Cellvalue = getcellvalue (cell); Keys.put (J,cellvalue);} catch (Exception e) {e.printstacktrace ();}}}  Traverse rows (starting at the second row) for (int i = 1; i < rows; i++) {//Read Left upper cell (starting from the second row) Hssfrow row = Sheet.getrow (i);//Line not empty if (row! = null) {//Prepare mapmap<string for the value stored in the current row, object> val=new hashmap<string, object> (); Boolean isvalidrow = false;//traverse column for (int j = 0; J < cells; J + +) {//Get to the value of the column try {Hssfcell cell = Row.getcell (j); String Cellvalue = getcellvalue (cell); Val.put (Keys.get (j), Cellvalue); if (!isvalidrow && Cellvalue!=null & & Cellvalue.trim (). Length () >0) {Isvalidrow = true;}} catch (Exception e) {e.printstacktrace ();}} All column data in line I is read, put into Valuelistif (Isvalidrow) {Valuelist.add (val);}}} catch (IOException e) {e.printstacktrace ();}        finally {fis.close (); } return valueList;} /** * Read 2007-2013 format * @param filePath file path * @return * @throws java.io.IOException */@SuppressWarnings ("Rawtypes") public static List        <Map> readExcel2007 (String filePath) throws ioexception{list<map> valuelist=new arraylist<map> ();        FileInputStream FIS =null;            try {fis =new fileinputstream (FilePath); Xssfworkbook XWB = new Xssfworkbook (FIS);//Construct Xssfworkbook object, strpath incoming file path xssfsheet sheet = Xwb.getsheetat (            0);//Read the first chapter table contents//define row, cell Xssfrow row;            The first row of contents in the Loop output table header Map<integer, string> keys=new Hashmap<integer, string> ();            row = Sheet.getrow (0);                if (row!=null) {//system.out.println ("j = Row.getfirstcellnum ()::" +row.getfirstcellnum ());                System.out.println ("Row.getphysicalnumberofcells ()::" +row.getphysicalnumberofcells ());    for (int j = Row.getfirstcellnum (), J <=row.getphysicalnumberofcells (); j + +) {                Gets the cell contents by Row.getcell (j). ToString (), if (Row.getcell (j)!=null) {                        if (!row.getcell (j). ToString (). IsEmpty ()) {Keys.put (J, Row.getcell (j). ToString ());                    }}else{Keys.put (J, "k-r1c" +j+ "E");  }}}//The start of the second row in the Loop output table for (int i = Sheet.getfirstrownum () + 1; I <= Sheet.getphysicalnumberofrows ();                i++) {row = Sheet.getrow (i);                    if (row! = null) {Boolean isvalidrow = false;                    Map<string, object> val = new hashmap<string, object> (); for (int j = Row.getfirstcellnum (); J <= Row.getphysicalnumberofcells (); j + +) {Xssfcell cell =                        Row.getcell (j);                        if (cell! = null) {String cellvalue = null;    if (Cell.getcelltype () ==xssfcell.cell_type_numeric) {if (dateutil.iscelldateformatted (cel L) {cellvalue = new Dataformatter (). Formatrawcellcontents (Cell.getnumericcellvalue (),                                0, "Yyyy-mm-dd HH:mm:ss"); } else{Cellvalue = string.valueof (Cell.getnumericcellva                                Lue ()); }} else{Cellvalue = Cell.tostring ()                            ;                                } if (Cellvalue!=null&&cellvalue.trim (). Length () <=0) {                            Cellvalue=null;                            } val.put (Keys.get (j), Cellvalue); if (!isvalidrow && cellvalue!= null && cellvalue.trim (). Length () >0) {Isva Lidrow = tRue }}}//Line I all column data is read and put into ValueList if (IsV                    Alidrow) {Valuelist.add (val);        }}}} catch (IOException e) {e.printstacktrace ();        }finally {fis.close (); } return valueList;} /** * File operation get file extension * * @Author: Sunny * @param filename * file name contains extension * @return */public static String Getextens Ionname (String filename) {if ((filename! = null) && (filename.length () > 0)) {int dot = filename.lastindexof ('. if (dot >-1) && (dot < (Filename.length ()-1)) {return filename.substring (dot + 1);}} return filename;}  /**-----------Upload File, tool method---------*/private static final int buffer_size = 2 * 1024;/** * * @param src * source file * @param DST * Target location */private static void copy (file src, file dst) {InputStream in = Null;outputstream out = null;try {in = new Bufferedinputstream (new FileInputStream (SRC), buffer_size); out = new Bufferedoutputstream (New F  Ileoutputstream (DST), buffer_size); byte[] BUFFER = new Byte[buffer_size];int len = 0;while (len = In.read (BUFFER)) > 0) {out.write (buffer, 0, Len);}} catch (Exception e) {e.printstacktrace ();} finally {if (null! = in) {try {in.close ();} catch (IOException e) {E.printstack Trace ();}}    if (null! = out) {try {out.close ();} catch (IOException e) {e.printstacktrace ();}}}} /** * Upload Copy file method (for multipartfile) * @param savepath to save full path on Linux * @param newname new file name, use system time to do file name to prevent Chinese error Question * @throws Exception */public static void copy (Multipartfile file,string savepath,string newname) throws Ex            ception {try {file targetfile = new File (savepath,newname);            if (!targetfile.exists ()) {//determines if the folder exists, does not exist to create targetfile.mkdirs ();        } file.transferto (TargetFile); } catch (ExcEption e) {e.printstacktrace (); }}private static String Getcellvalue (Hssfcell cell) {DecimalFormat df = new DecimalFormat ("#"); String cellvalue=null;if (cell = = null) return Null;switch (Cell.getcelltype ()) {Case HSSFCell.CELL_TYPE_NUMERIC:if ( hssfdateutil.iscelldateformatted (cell)) {SimpleDateFormat SDF = new SimpleDateFormat ("Yyyy-mm-dd HH:mm:ss"); Cellvalue=sdf.format (Hssfdateutil.getjavadate (Cell.getnumericcellvalue ())); Cellvalue=df.format (Cell.getnumericcellvalue ()); Break;case hssfcell.cell_type_string:cellvalue=string.valueof ( Cell.getstringcellvalue ()); Break;case hssfcell.cell_type_formula:cellvalue=string.valueof (Cell.getCellFormula () ); Break;case Hssfcell.cell_type_blank:cellvalue=null;break;case hssfcell.cell_type_boolean:cellvalue= String.valueof (Cell.getbooleancellvalue ()); Break;case hssfcell.cell_type_error:cellvalue=string.valueof ( Cell.geterrorcellvalue ()); break;} if (Cellvalue!=null&&cellvalue.trim (). Length () <=0) {cellvalue=null;} Return celLValue;}} 

Java read Excel file compatible 97-2013 V2.0