The method of JSP uploading excel and inserting excel into database

Time:2020-5-27

In this paper, the method of JSP uploading excel and inserting excel into database is introduced. Share with you for your reference. The details are as follows:

This import excel is bound with 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'" style="width: 60px"
    onclick="subForm();">OK</a>
   <a
    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 ("operation prompt", "operation succeeded!"! ","info");
    },
    success: function(data) {
     alert("success");
    }
  });
  **/
  var filepath = $("#filepath").val();
  var re = /(\+)/g; 
  var filename = filepath.replace(re,"#"); 
  //Cut and intercept path string 
  var one = filename.split("#"); 
  //Gets the last file name in the array 
  var two = one[one.length-1]; 
  //Then the file name is truncated to get the suffix 
  var three = two.split("."); 
   //Gets the last string intercepted, which is the suffix 
  var last = three[three.length-1]; 
  //Add suffix type to be judged 
  var tp = "xls,xlsx"; 
  //Returns 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 prompt", "the upload file you selected is not a valid XLS or xlsx file! ","error");
   return false; 
  } 
 } else {
  $. messager.alert ("operation prompt", "please select upload file! ","error");
 }
}
</script>

2. Java code:

@RequestMapping("/excleImport")
 public void excleImport(HttpServletRequest request) throws IOException, Exception {
  request.setCharacterEncoding ("UTF-8"); // set the 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 the cache, it will be directly put into the temporary storage room 
  factory.setSizeThreshold(1024*1024) ; 
  //High level API file upload processing 
  ServletFileUpload upload = new ServletFileUpload(factory); 
  //Multiple files can be uploaded 
  List<FileItem> list = (List<FileItem>)upload.parseRequest(request); 
  for(FileItem item : list) 
  { 
   //Get the property name of the form 
   String name = item.getFieldName(); 
   //If the form information obtained is normal text information 
   if(item.isFormField()) 
   {      
    //Get the string entered by the user. The name is very good, because the form submitted is of string type 
    String value = item.getString() ; 
    request.setAttribute(name, value); 
   } 
   //Handle the incoming non simple strings, such as binary images and movies 
   else 
   { 
    /** 
     *The following three steps are to obtain the name of the uploaded file 
     */ 
    //Get pathname 
    String value = item.getName() ; 
    //Index to last backslash 
    int start = value.lastIndexOf("\"); 
    //Truncate the string name of the uploaded file. Add 1 to remove the backslash, 
    String filename = value.substring(start+1); 
    //File suffix
    String prefix = filename.substring(filename.lastIndexOf(".") + 1);
    CardCenter cardCenter = new CardCenter();
    request.setAttribute(name, filename); 
    //Write to disk 
    //The exception it throws is caught with exception 
    // item.write (new file (path, filename)); // provided by a third party 
    //Manually written 
    //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 total capacity of uploaded files:"+ item.getSize ()); 
    // in.read (buf) the data read each time is stored in the buf array 
    while( (length = in.read(buf) ) != -1) 
    { 
     //Fetching data from buf array and writing it to disk (output stream) 
     out.write(buf, 0, length); 
    } */
    in.close(); 
    //out.close(); 
   } 
  } 
}

3. Java code:

public class ExelUtil { 
 //First column start
 private static int start = 0;
 //Last column number
 private static int end =0;
 public static String getSubString(String str){
  return str.substring(0,str.lastIndexOf("."));
 }
 /** 
  *Method description: export from excel sheet to list
  * @param file
  * @param sheetNum
  * @return
  * @throws IOException
  * @author 
  *@ date 2013-3-25 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 from excel flow sheet to list 
  * @param is
  * @param extensionName
  * @param sheetNum
  * @return
  * @throws IOException
  * @author 
  *@ date 2013-3-25 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 2013-3-25 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); 
  //Analytical 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 formula analysis, there are only three data types: Boolean, numeric and string. In addition, error 
    //Other data types can be ignored according to official documents 
    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 the number type, which needs to be differentiated after discrimination 
     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 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;
 }
}

I hope this article is helpful to our JSP program design.

Recommended Today

Java security framework

The article is mainly divided into three parts1. The architecture and core components of spring security are as follows: (1) authentication; (2) authority interception; (3) database management; (4) authority caching; (5) custom decision making; and;2. To build and use the environment, the current popular spring boot is used to build the environment, and the actual […]