JSP+ajax+springMVC+MayBatis处理excel上传导入

2019独角兽企业重金招聘Python工程师标准>>>

jsp

                   	<div class="subtext1">
                         <div class="rtxt">
	                          <input id="importData" name="importData" type="file"><a href="javascript:importData();">导入</a>
                         </div>
                     </div>

jquery

//数据导入
function importData(){
	
	var files = $('input[name="importData"]').prop('files');//获取到文件列表
	if(files.length == 0){
	  alert('请选择文件');
	  return;
	}else{
		var formData = new FormData();
	    var name = $("#importData").val();
	    formData.append("file",$("#importData")[0].files[0]);
	    formData.append("name",name);
		 var reg = /^.*\.(?:xls|xlsx)$/i; 
         if (!reg.test(name)) {//校验是否是excel格式的文件
             alert("请上传excel格式的文件!");
             return;
         }
      var  url = "url";
      
      $.ajax({
			type : "post",
			async : false,
			traditional : true,
			url : url,
			dataType : "json",
			data : formData,
	        processData : false,
	        contentType : false,
			success : function(result) {
				if (result.success) {
					//保存刷新
					//........
					alert("导入成功!");
					
				}
			},
			error : function(errorMsg) {
				alert("导入失败!");
			}
		});
       
	}
}

java

@Autowired
	private IDimUserFileConfService dimUserFileConfService;
	@Autowired
	private IDimUserGiConfService dimUserGiConfService;
	@RequestMapping(value = "importExel.json", method = RequestMethod.POST)
	@ResponseBody
	public Object importdata(@RequestParam("file") MultipartFile file,HttpServletRequest request, HttpServletResponse response,@RequestParam("user_id") String user_id,
			@RequestParam("user_name") String user_name) {
		 String id = user_id+DateUtils.getDateTimeStr();
		 Date create_time = new Date();
         String filename = file.getOriginalFilename();//原文件名字
         XSSFWorkbook   workbook1 = null;
         HSSFWorkbook   workbook2 = null;
         try {
			InputStream is = file.getInputStream();//获取输入流
			 if(filename.endsWith(".xlsx")){ 
				 workbook1 =  new XSSFWorkbook(is); 
				 XSSFSheet sheet =  workbook1.getSheetAt(0); // 创建对工作表的引用  
				
		         int rows = sheet.getPhysicalNumberOfRows();// 获取表格的 
		         int columns = 0;  
		            for (int r = 0; r < rows; r++) { // 循环遍历表格的行  
		                if(r==0){  
		                    //在第一行标题行计算出列宽度,因为数据行中可能会有空值  
		                    columns = sheet.getRow(r).getLastCellNum();  
		                    continue;  
		      
		                }  
		                String value = "";  
		                Row row = sheet.getRow(r); // 获取单元格中指定的行对象  
		                if (row != null) { //遍历单元格
		                	for(int i = 0 ; i < columns; i++) {
		                		Cell cell = row.getCell(i);
		                		cell.setCellType(Cell.CELL_TYPE_STRING);
		                		String temp = cell.getStringCellValue();
		                		if (temp.indexOf(".") > -1) {  //处理有小数的读取错误
		                			value += String.valueOf(new Double(temp)).trim()+",";  
		                        } else {  
		                        	value += temp.trim()+",";  
		                        }  
		                		
		                	}
		                	
		                }
		                
		                String[] str = value.split(",");
		                int k = 0;
		                if(r % 200 == 0) {//文件拆分，每200行一个文件
		                	k++;
		                	id = k+user_id+DateUtils.getDateTimeStr()+"p";
		                	 //保存文件信息
				            dimUserFileConfService.addUserGiFile(id, filename+"_p"+k, user_id, user_name, new Date());
		                } 
		              //处理用户地理信息保存
		               dimUserGiConfService.addUserGi(str[0], str[1], str[2], id);

		              
		            } 
		             if(!id.endsWith("p")) {
			             //保存文件信息
			             dimUserFileConfService.addUserGiFile(id, filename, user_id, user_name, create_time);
		             }

	            } else {
	            workbook2 = new HSSFWorkbook(is);
	            HSSFSheet sheet = workbook2.getSheetAt(0); // 创建对工作表的引用  
		         int rows = sheet.getPhysicalNumberOfRows();// 获取表格的 
		         int columns = 0;  
		            for (int r = 0; r < rows; r++) { // 循环遍历表格的行  
		                if(r==0){  
		                    //在第一行标题行计算出列宽度,因为数据行中可能会有空值  
		                    columns = sheet.getRow(r).getLastCellNum();  
		                    continue;  
		      
		                }  
		                String value = "";  
		                Row row = sheet.getRow(r); // 获取单元格中指定的行对象  
		                if (row != null) { //遍历单元格
		                	for(int i = 0 ; i < columns; i++) {
		                		Cell cell = row.getCell(i);
		                		cell.setCellType(Cell.CELL_TYPE_STRING);
		                		String temp = cell.getStringCellValue();
		                		if (temp.indexOf(".") > -1) {  //处理有小数的读取错误
		                			value += String.valueOf(new Double(temp)).trim()+",";  
		                        } else {  
		                        	value += temp.trim()+",";  
		                        }  
		                		
		                	}
		                	
		                }  
		                
		                String[] str = value.split(",");
		                int k = 0;
		                if(r % 200 == 0) {//文件拆分，每200行一个文件
		                	k++;
		                	id = k+user_id+DateUtils.getDateTimeStr()+"p";
		                	 //保存文件信息
				            dimUserFileConfService.addUserGiFile(id, filename+"_p"+k, user_id, user_name, new Date());
		                } 
		              //处理用户地理信息保存
		               dimUserGiConfService.addUserGi(str[0], str[1], str[2], id);

		              
		            } 
		             if(!id.endsWith("p")) {
			             //保存文件信息
			             dimUserFileConfService.addUserGiFile(id, filename, user_id, user_name, create_time);
		             }
	            }

		} catch (IOException e) {
			e.printStackTrace();
		}finally {
			try {
				if(workbook1 != null) {
					workbook1.close();
				}
				if(workbook2 != null) {
					workbook2.close();
				}
			
			} catch (IOException e) {
				e.printStackTrace();
			} 
		}
         
	 return new RestResult<Object>(true, "ok");
		
	}
	

maybatis

   <!-- 导入文件 -->
   <insert id="addUserGiFile">
  	INSERT INTO dim_user_gi_file
 		 
 	values 
 		(#{id},#{f_name},#{user_id},#{user_name},#{create_time, jdbcType=DATE})
  </insert>

 

转载于:https://my.oschina.net/chendongj/blog/1616970