JSP upload Excel and Excel inserts to the database method _jsp programming

This article describes the JSP upload Excel and Excel inserted into the database method. Share to everyone for your reference. Specifically as follows:

This import Excel is bound to Pojo, (disadvantage) Excle the header must be a Pojo field value

1. HTML page:

<form id= "MyForm" method= "post" enctype= "Multipart/form-data" > <table> <tr> <td></td> <td> <input type= "file" name= "filepath" id= "filepath" class= "Easyui-validatebox" Required=true validty Pe= "equallength[4]" missingmessage= file! "value=" "/> </td> </tr> <tr align=" center "> <td Col span= "2" > <a id= "btn1" class= "Easyui-linkbutton" data-options= "iconcls: ' Icon-ok '" style= "width:60px" Onc Lick= "Subform ();" 
    >OK</a> <a id= "btn2" class= "Easyui-linkbutton" data-options= iconcls: ' Icon-cancel ' style= ' width:60px ' Onclick= "Closedig ();"
>Cancel</a> </td> </tr> </table> </form> <script type= "Text/javascript" >
  Function Subform () {if ($ (' #myform '). Form (' Validate ') {/** var filepath = $ ("#filepath"). Val ();
  alert (filepath); $.ajax ({url: ' Excleimport ', Typs: "POST", data: {"filepath": filepath}, Async:false, error:fUnction (Request) {$ (' #dg '). DataGrid (' reload ');
    Closedig (); $.messager.alert ("Action Prompt", "Operation successful!")
    "," info ");
    }, Success:function (data) {alert ("Success");
  }
  });
  **/var filepath = $ ("#filepath"). Val (); 
  var re =/(\\+)/g; 
  var filename = filepath.replace (Re, "#"); 
  Cut the path string to intercept var one = Filename.split ("#"); 
  Gets the last of the array, that is, the filename var two = one[one.length-1]; 
   The filename is then intercepted to obtain the suffix name var three = Two.split ("."); 
  Gets the last string of the interception, that is, the suffix name, var, three[three.length-1]; 
  Add a suffix name type that needs to be judged var tp = "Xls,xlsx"; 
  Returns the position of the qualifying suffix name in the string var rs = Tp.indexof (last);
   if (Rs!=-1) {$ ("#myform"). attr ("Action", "Excleimport");
  $ ("#myform"). Submit (); }else{$.messager.alert ("Action hint", "Your selected upload file is not a valid XLS or xlsx file!")
   "," error "); 
  return false; } else {$.messager.alert ("Action prompt", "Please select Upload file!")
 "," error ");

 }} </script>

2. Java code:

@RequestMapping ("/excleimport") public void Excleimport (HttpServletRequest request) throws IOException, Exception {RE Quest.setcharacterencoding ("Utf-8"); 
  Set encoding//Get Disk File entry Factory Diskfileitemfactory factory = new Diskfileitemfactory (); 
  Gets the path to which the file needs to be uploaded String Path = Request.getrealpath ("/upload/kaku");
  File Uploaddir = new file (path);
  if (!uploaddir.exists ()) {uploaddir.mkdirs (); 
  } factory.setrepository (Uploaddir); 
  Set the size of the cache, when the upload file capacity exceeds the cache, directly to the temporary storage Room factory.setsizethreshold (1024*1024); 
  High-level API file upload processing servletfileupload upload = new Servletfileupload (factory); 
  Can upload multiple files list<fileitem> List = (list<fileitem>) upload.parserequest (request); 
   for (Fileitem item:list) {//Get the form's property name String name = Item.getfieldname (); If the obtained form information is a plain text message if (Item.isformfield ()) {//Gets the user-specific input string, the name is very good because the form submits a string Val 
    UE = Item.getstring (); 
   Request.setattribute (name, value); //pairs of incoming non simpleString to handle, such as binary pictures, movies these else {/** * below three steps, mainly to obtain the name of the upload file///Get pathname String value = Item 
    . GetName (); 
    Index to the last backslash int start = Value.lastindexof ("\"); 
    Intercept the string name of the uploaded file, plus 1 to remove the backslash, string filename = value.substring (start+1);
    File suffix name String prefix = filename.substring (Filename.lastindexof (".") + 1);
    Cardcenter cardcenter = new Cardcenter (); 
    Request.setattribute (name, filename); The exception that is actually written to disk//It throws is caught with exception//item.write (new File (Path,filename)),//manually written by a third party//outputstrea 
    m out = new FileOutputStream (new File (Path,filename)); 
    InputStream in = Item.getinputstream (); List<cardcenter> Listfromexcel = (list<cardcenter>) exelutil.exportlistfromexcel (in, prefix, CardCenter)
    ;
    This.cardCenterService.excleImport (Listfromexcel); 
    /*int length = 0; 
    byte [] buf = new byte[1024]; 
    System.out.println ("Get the total volume of uploaded files:" +item.getsize ()); In.read (BUF) storing data every time it is readIn buf array while (length = In.read (BUF))!=-1) {//Fetch data in BUF array write to (output stream) on disk out.write (buf, 0, le 
    Ngth); 
    } */In.close (); 
   Out.close ();

 } 
  } 
}

3. Java code:

public class Exelutil {//First column begins private static int start = 0;
 Last column ordinal private static int end = 0;
 public static string getsubstring (String str) {return str.substring (0,str.lastindexof ("."));  
  /** * Method Description: Exported from Excel file sheet to list * @param file * @param sheetnum * @return * @throws IOException * @author * @date 2013-3-25 PM 10:44:26 * @comment/public static list<?> exportlistfromexcel (file file, String file Format,object dtoobj) throws IOException {return Exportlistfromexcel (new FileInputStream (file), fileformat,dtoobj) 
 ;  /** * Method Description: Exported by Excel stream sheet to list * @param is * @param extensionname * @param sheetnum * @return * @throws IOException * @author * @date 2013-3-25 PM 10:44:03 * @comment * * public static list<?> Exportlistfromexce 
  L (InputStream is,string fileformat,object dtoobj) throws IOException {workbook = null; 
  if (Fileformat.equals (Bizconstant.xls)) {workbook = new Hssfworkbook (IS);else if (fileformat.equals (bizconstant.xlsx)) {workbook = new Xssfworkbook (IS); 
 Return Exportlistfromexcel (workbook,dtoobj); /** * Method Description: Exported from specified sheet to list * @param workbook * @param sheetnum * @return * @author * @date 2013-3-25 afternoon 1 0:43:46 * @comment * * private static list<object> exportlistfromexcel (Workbook workbook, Object dtoobj) {Lis
  t<object> list = new arraylist<object> ();
  string[] model = NULL; 
  Sheet Sheet = workbook.getsheetat (0); 
  Analytic formula results Formulaevaluator evaluator = Workbook.getcreationhelper (). Createformulaevaluator (); 
  int Minrowix = Sheet.getfirstrownum (); 
  int Maxrowix = Sheet.getlastrownum ();
   for (int rowix = Minrowix; Rowix <= Maxrowix; rowix++) {Object obj = null;
    if (Rowix==minrowix) {start = Sheet.getrow (Rowix). Getfirstcellnum ();
   End = Sheet.getrow (Rowix). Getlastcellnum (); 
   Row row = Sheet.getrow (Rowix);  
   StringBuilder sb = new StringBuilder (); for (int i = StArt I < end; 
    i++) {Cell cell = Row.getcell (new Integer (i)); 
    Cellvalue Cellvalue = evaluator.evaluate (cell);
     if (Cellvalue = = null) {sb.append (bizconstant.separator+null); 
    Continue ///After the formula parsing, only Boolean, numeric, and string three data types exist, in addition to the error//The remaining data types, according to the official document, can completely ignore the switch (cellvalue.getcellt 
     Ype ()) {case Cell.CELL_TYPE_BOOLEAN:sb.append (Bizconstant.separator + cellvalue.getbooleanvalue ()); 
    Break Case Cell.cell_type_numeric://The date type here will be converted to a numeric type, which needs to be discriminated against if (dateutil.iscelldateformatted (Cell)) {SB 
     . Append (Bizconstant.separator + cell.getdatecellvalue ()); 
     else {sb.append (bizconstant.separator + cellvalue.getnumbervalue ()); 
    } break; 
     Case Cell.CELL_TYPE_STRING:sb.append (Bizconstant.separator + cellvalue.getstringvalue ()); 
    Break 
    Case Cell.CELL_TYPE_FORMULA:break; 
    Case Cell.CELL_TYPE_BLANK:break; 
   Case CELL.CELL_TYPE_ERROR:  Break 
    Default:break;
    } if (Rowix==minrowix) {String index = string.valueof (SB);
    String Realmodel =index.substring (1, Index.length ());
   Model =realmodel.split (",");
    }else{String index = string.valueof (SB);
    String Realvalue =index.substring (1, Index.length ());
    String[] Value =realvalue.split (",");
    Field mapping try {dtoobj =dtoobj.getclass (). newinstance ();
    catch (Instantiationexception e) {e.printstacktrace ();
    catch (Illegalaccessexception e) {e.printstacktrace ();
    obj = Reflectutil (dtoobj,model,value);
   List.add (obj); 
 } return list; /** * Method Description: Field mapping Assignment * @param objone * @param listname * @param listvales * @return * @author * @date 2013 -3-25 PM 10:53:43 * @comment * * * @SuppressWarnings ("deprecation") private static object Reflectutil (object Objone, STR
  Ing[] ListName, string[] listvales) {field[] fields = Objone.getclass (). Getdeclaredfields (); for (iNT i = 0; i < fields.length;
   i++) {fields[i].setaccessible (true); for (int j = 0; J < Listname.length; J +) {if (Listname[j].equals (Fields[i].getname ())) {try {if (fie Lds[i].gettype (). GetName (). Equals (Java.lang.String.class.getName ())) {//String type if (listvales[j]!=null
       ) {Fields[i].set (Objone, listvales[j]);
       }else{Fields[i].set (Objone, ""); } else if (Fields[i].gettype (). GetName (). Equals (Java.lang.Integer.class.getName ()) | | Fields[i].gettype (). GetName (). Equals ("int")) {//Integer type if (listvales[j]!=null) {Fields[i].set (objone, (int) Doub 
       Le.parsedouble (Listvales[j])); 
       }else{Fields[i].set (Objone,-1);
        }else if (Fields[i].gettype (). GetName (). Equals ("Date") {//date type if (listvales[j]!=null) {
       Fields[i].set (Objone, Date.parse (listvales[j));
}else if (Fields[i].gettype (). GetName (). Equals ("Double")        || Fields[i].gettype (). GetName (). Equals ("float")) {//double if (listvales[j]!=null) {Fields[i].set (Objon 
       E, double.parsedouble (Listvales[j])); 
       }else{Fields[i].set (Objone, 0.0);
     A catch (IllegalArgumentException e) {e.printstacktrace ());
     catch (Illegalaccessexception e) {e.printstacktrace ();
    } break;
 }} return objone;

 }
}

I hope this article will help you with your JSP programming.