Upload Excel and parse the import database using Ocupload and POI with one click

The following tools are used:

JQuery Ocupload Jquery.ocupload-1.1.2.js

Apache POI Poi-3.9.jar

If this is the MAVEN project add dependency is as follows:

<dependency>            <groupId>org.apache.poi</groupId>            <artifactid>poi</artifactid >            <version>3.9</version>    </dependency>  

JQuery Ocupload:

First to use one-click Upload to reference JS from the project (address above)

Examples of official uploads:

$ (Element). Upload ({                name: ' file ',//Upload component's Name property, i.e. <input type= ' file ' name= ' file '/>                action: ',// The path requested by the server                enctype: ' Multipart/form-data ',//mime type, the default                is params: {},//Additional parameters passed on request, default is empty                autosubmit:true, Whether to commit automatically, that is, when the file is selected, automatically closes the selection window, whether the request is automatically submitted.                onsubmit:function () {},//triggers event before                the form is submitted Oncomplete:function () {},//event triggered after the submission form completes                onselect:function () {}// Triggers an event when the user selects a file        });

Development code:

1. Customizing a button

<button type= "button" id= "Uploadexcel" ><i>&nbsp; upload excel</i></button>

My project uses the bootstrap you need to refer to the following styles, which can be ignored with other UI

<button type= "button" id= "Uploadexcel" class= "btn btn-primary Disabled" data-btn-type= "Selecticon" >         <i class= "fa fa-mail-forward" >&nbsp; upload excel-(Western medicine) </i></button>

2. Upload the file JS code

//Call the Ocupload plug-in method $ ("#uploadExcel"). Upload ({action:basepath+ "/excel/upload/westernmedicine",//Address of form submission Name: "MyFile", oncomplete:function (data) {//After submitting the form if (data = = "0") {$ ("#myModal"). Mo Dal (); Prompt box, Excel import succeeded}else{$ ("#myModal2"). modal ();//Prompt box, Excel import as Failed}}, on            Select:function () {///When the user selects a file and triggers an event//when the file is selected, the auto-commit this.autosubmit=false is turned off; Verify that the uploaded file name meets the suffix. xls or. xlsx var regex =/^.*\.            (?: xls|xlsx) $/i; This.filename () returns the currently selected file name (PS: I used this method to write a method to get the name of the file) $ ("[name = '" +this.name () + "']"). Val ())//alert (thi            S.filename ());            if (Regex.test ($ ("[name = '" "+this.name () +" '] "). Val ())) {//by verifying this.submit ();    }else{//Failed $ ("#myModal3"). modal ();//Error box, file format is incorrect, must end with. xls or. xlsx}} });

3. Background code uploaded using poi:
First set up a class Poiutil use: I wrote one, but in the query information, found a more comprehensive poiutil copy down as follows:

Source: Java uses POI to implement reading of Excel files, compatible suffix xls and xlsx

Class Poiutil:

Import Java.io.filenotfoundexception;import Java.io.ioexception;import Java.io.inputstream;import Java.util.arraylist;import Java.util.list;import Org.apache.log4j.logger;import Org.apache.poi.hssf.usermodel.hssfworkbook;import Org.apache.poi.ss.usermodel.cell;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.springframework.web.multipart.multipartfile;/** * Excel Read-write tool class * Excel import to MySQL * Created by CR7 on 2017-3-8 */public cl        The poiutil {private static Logger Logger = Logger.getlogger (Poiutil.class);        Private final static String xls = "xls";        Private final static String xlsx = "xlsx"; /** * read into Excel file, parse and return * @param file * @throws IOException */public static list<            String[]> readexcel (multipartfile file) throws ioexception{//Check files checkfile (file); BeenWorkbook work Thin object Workbook Workbook = getworkbook (file);            Creates a return object, takes the value in each row as an array, and returns all rows as a collection list<string[]> List = new arraylist<string[]> ();                    if (workbook! = null) {for (int sheetnum = 0;sheetnum < Workbook.getnumberofsheets (); sheetnum++) {                    Get the current Sheet worksheet Sheet Sheet = Workbook.getsheetat (sheetnum);                    if (sheet = = null) {continue;                    }//Gets the start line of the current sheet int firstrownum = Sheet.getfirstrownum ();                    Gets the end line of the current sheet int lastrownum = Sheet.getlastrownum ();                        Loops all rows except the first row for (int rowNum = Firstrownum+1;rownum <= lastrownum;rownum++) {                        Get the current row row row = Sheet.getrow (RowNum);                        if (row = = null) {continue;       }                 Gets the start column of the current row int firstcellnum = Row.getfirstcellnum ();                        Gets the number of columns of the current row int lastcellnum = Row.getphysicalnumberofcells ();                        String[] cells = new string[row.getphysicalnumberofcells ()];                            Loop current line for (int cellnum = Firstcellnum; cellnum < lastcellnum;cellnum++) {                            Cell cell = Row.getcell (Cellnum);                        Cells[cellnum] = Getcellvalue (cell);                    } list.add (cells);            }}//Workbook.close ();        } return list; public static void Checkfile (Multipartfile file) throws ioexception{//determine if the file exists if (null = = File) {Logger.error ("files do not exist!)                "); throw new FileNotFoundException ("file does not exist!)            "); }//Get file name String filename= File.getoriginalfilename (); Determine if the file is an Excel file if (!filename.endswith (xls) &&!filename.endswith (xlsx)) {Logger.error (f                Ilename + "not excel file");            throw new IOException (FileName + "not excel file");  }} public static Workbook Getworkbook (multipartfile file) {//Get file name String filename =            File.getoriginalfilename ();            Creates a Workbook Workbook object that represents the entire Excel Workbook Workbook = null;                try {//Get IO stream for Excel file InputStream is = File.getinputstream ();                    Different Workbook implementation class objects are obtained depending on the file suffix (xls and xlsx) (Filename.endswith (xls)) {//2003                workbook = new Hssfworkbook (IS);                }else if (Filename.endswith (xlsx)) {//2007 and above 2007 workbook = new Xssfworkbook (IS); }} catch (IOException e) {logger.info (E.getmessage ());           } return workbook;            public static string Getcellvalue (cell cell) {string cellvalue = "";            if (cell = = null) {return cellvalue; }//The number is read as a string to avoid the occurrence of 1 read into 1.0 if (cell.getcelltype () = = Cell.cell_type_numeric) {cel            L.setcelltype (cell.cell_type_string);                    }//Determine the type of data switch (Cell.getcelltype ()) {case Cell.cell_type_numeric://number                    Cellvalue = string.valueof (Cell.getnumericcellvalue ());                Break                    Case cell.cell_type_string://String cellvalue = String.valueof (Cell.getstringcellvalue ());                Break                    Case Cell.cell_type_boolean://boolean cellvalue = string.valueof (Cell.getbooleancellvalue ());                Break Case Cell.cell_type_formula://Formula Cellvalue = string.valueof (cell.geTcellformula ());                Break                    Case Cell.cell_type_blank://null value Cellvalue = "";                Break                    Case CELL.CELL_TYPE_ERROR://fault cellvalue = "illegal character";                Break                    Default:cellvalue = "Unknown type";            Break        } return cellvalue; }    }

Background code: Using SPRINGMVC and Spring and hibernate

Controller

Upload Western medicine    @RequestMapping (method = Requestmethod.post, value= "/upload/westernmedicine")    @ResponseBody    Private String Uploadwesternmedicine (Multipartfile myFile) throws IOException {        String flag = "0";        try{            list<string[]> List = Poiutil.readexcel (myFile);//Here is a collection where each element is a string[] array            Excelservice.savebath (list); Service implementation Method             } catch (Exception e) {           flag = "1";        }        return flag;    }

Serviceimpl: The idea is to iterate through the data to get each string[], that is, the class, and then save (where there may be some problems with the number conversion, there are workarounds below)

@Override public    void Savebath (list<string[]> List) {for        (string[] strings:list) {            Drug Drug = new Dr UG ();            Drug.setname (Strings[0]);            Drug.setspecification (Strings[1]);            Drug.setunit (strings[2]);            Drug.setprice (Strings[3]);  This method causes the price to appear as follows: 16.399999999999999  77.900000000000006            BigDecimal bd = new BigDecimal (strings[3]);            BD = Bd.setscale (1,BIGDECIMAL.ROUND_HALF_UP);            String Price = Bd.tostring ();            if ("0". Equals (Price.substring (Price.length ()-1)) {//If the last one is 0  such as 26.0 Yuan  33.0 yuan, put. 0 Delete                String newprice = Price.substring (0, Price.length ()-2);                Drug.setprice (Newprice);            } else{                Drug.setprice (price);            }            Drug.setstatus ("0");            Drug.setdrug_classify ("8afa9409591f762b01591ffa62ff0006");            This.save (drug);        }    }

Upload success:

As follows:

SOURCE Link: https://www.cnblogs.com/zhanghaoliang/p/6526089.html;

Upload Excel and parse the import database using Ocupload and POI with one click