Java read Excel file compatible 97-2013 V2.0

Source: Internet
Author: User
Tags delete cache

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

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.