Home>

This import excel is bound to pojo, (disadvantage) the excle header must be the field value of pojo

1. html page:

<form method="post" enctype="multipart/form-data">
<table>
 <tr>
  <td></td>
  <td>
   <input type="file" name="filepath"
    required=true
    validtype="equallength [4]" missingmessage="file!" value="" />
  </td>
 </tr>
 <tr align="center">
  <td colspan="2">
   <a
    data-options="iconcls:" icon-ok ""
    onclick="subform ();">ok</a>
   <a
    data-options="iconcls:" icon-cancel ""
    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 ("Operation Tips", "Operation Successful!", "info");
    },    success:function (data) {
     alert ("success");
    }
  });
  ** /
  var filepath=$("#filepath"). val ();
  var re =/(\\ +)/g;
  var filename=filepath.replace (re, "#");
  //Cut and intercept the path string
  var one=filename.split ("#");
  //Get the last one in the array,File name
  var two=one [one.length-1];
  //Then intercept the file name,To get the suffix name
  var three=two.split (".");
   //Get the last string intercepted,Suffix name
  var last=three [three.length-1];
  //Add the suffix name type that needs to be judged
  var tp="xls, xlsx";
  //Return the position of the qualified suffix in the string
  var rs=tp.indexof (last);
  if (rs!=-1) {
   $("#myform"). attr ("action", "excleimport");
   $("#myform"). submit ();
  } else {
   $.messager.alert ("Operation Tips", "The upload file you selected is not a valid xls or xlsx file!", "error");
   return false;
  }
 } else {
  $.messager.alert ("Operation Tips", "Please choose to upload a file!", "error");
 }
}
</script>

2. Java code:

@requestmapping ("/excleimport")
 public void excleimport (httpservletrequest request) throws ioexception, exception {
  request.setcharacterencoding ("utf-8");//Set encoding
  //Get disk file entry factory
  diskfileitemfactory factory=new diskfileitemfactory ();
  //Get 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 capacity of the uploaded file exceeds this cache,Directly into 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 attribute name of the form
   string name=item.getfieldname ();
   //If the obtained form information is ordinary text information
   if (item.isformfield ())
   {
    //Get the user-specified string, the name plays well,Because the form is submitted as a string
    string value=item.getstring ();
    request.setattribute (name, value);
   }
   //Process the incoming non-simple strings, such as binary pictures, movies, etc.
   else
   {
    /**
     * The following three steps,Mainly get the name of the uploaded file
     * /
    //Get the path name
    string value=item.getname ();
    //Index to the last backslash
    int start=value.lastindexof ("\\");
    //Intercept the string name of the uploaded file,Add 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);
    //really write to disk
    //The exception it throws is caught with an exception
    //item.write (new file (path, filename));//provided by a third party
    //written manually
    //outputstream 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 capacity of uploaded files:" + item.getsize ());
    //in.read (buf) each time the data is stored in the buf array
    while ((length=in.read (buf))!=-1)
    {
     //Remove data from buf array and write to (output stream) disk
     out.write (buf, 0, length);
    } * /
    in.close ();
    //out.close ();
   }
  }
}

3. Java code:

public class exelutil {
 //Beginning of the first column
 private static int start=0;
 //The last column number
 private static int end=0;
 public static string getsubstring (string str) {
  return str.substring (0, str.lastindexof ("."));
 }
 /**
  * Method description:export from sheet of excel file to list
  * @param file
  * @param sheetnum
  * @return
  * @throws ioexception
  * @author
  * @date 3/25/2013 10:44:26 PM
  * @comment
  * /
 public static list<?>exportlistfromexcel (file file, string fileformat, object dtoobj)
   throws ioexception {
  return exportlistfromexcel (new fileinputstream (file), fileformat, dtoobj);
 }
 /**
  * Method description:export sheet from excel stream to list
  * @param is
  * @param extensionname
  * @param sheetnum
  * @return
  * @throws ioexception
  * @author
  * @date 3/25/2013 10:44:03 PM
  * @comment
  * /
 public static list<?>exportlistfromexcel (inputstream is, string fileformat, object dtoobj) throws ioexception {
  workbook 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:export from specified sheet to list
  * @param workbook
  * @param sheetnum
  * @return
  * @author
  * @date 3/25/2013 10:43:46 PM
  * @comment
  * /
 private static list<object>exportlistfromexcel (workbook workbook, object dtoobj) {
  list<object>list=new arraylist<object>();
  string [] model=null;
  sheet sheet=workbook.getsheetat (0);
  //Parse the result of the formula
  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 formula analysis,Finally, there are only three data types:boolean, numeric, and string.
In addition, it is error
    //the remaining data types,According to the official documentation,Totally negligible
    switch (cellvalue.getcelltype ()) {
    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,Need to distinguish after processing
     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 3/25/2013 10:53:43 PM
  * @comment
  * /
 @suppresswarnings ("deprecation")
 private static object reflectutil (object objone, string [] 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 (fields [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) double.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 (objone, double.parsedouble (listvales [j]));
       } else {
        fields [i] .set (objone, 0.0);
       }
      }
     } catch (illegalargumentexception e) {
      e.printstacktrace ();
     } catch (illegalaccessexception e) {
      e.printstacktrace ();
     }
     break;
    }
   }
  }
  return objone;
 }
}
jsp
  • Previous Detailed explanation of unicode transcoding method in js
  • Next Simple generation method of JSP verification code