导入

jsp:

<button type="button" class="layui-btn layui-btn-primary" id="upload_excel"><i class="layui-icon"></i>请选择入学奖学金学生信息导入</button>
<div id = "file_div"></div>
<div class="head_div">
			<button id ="uploadBtn" class="layui-btn" style="display: none;" >点击上传</button> 
</div>

js

//初始加载
$(function() {
	/*加载layer的依赖*/
	loadLayerEL();
});
/**
 * 加载layer的依赖
 * @returns
 */
function loadLayerEL(){
	layui.use(['element','upload','form'], function(){
		  var element = layui.element;
		  var form = layui.form;
		  var $ = layui.jquery,upload = layui.upload;
		  /*上传文件*/
		  upload.render({ //允许上传的文件后缀
			    elem: '#upload_excel',
			    url: ctx+'/scholarShip/importScholarStudent',
			    accept: 'file', //普通文件,
			    auto:false,//是否自动上传
			    exts: 'xls|xlsx', //只允许上传excel文件
			    done: function(res){//上传完成后回调
			    	deletefile();
			    	if(res.msg=="请求上传接口出现异常"){
			    		layer.msg("请检查上传表格中数据");
			    		return false;
			    	}else{
			    		layer.msg(res.msg,{time:5000});
			    	}
			    	//查询列表的方法
			    	findScholarStudent(1,10);
			    },
			    bindAction:"#uploadBtn",
			    choose:function(obj){
			    	deletefile();
			    	//将每次选择的文件追加到文件队列
			        files = obj.pushFile();
			        if(Object.keys(files).length>0){
			        	$("#uploadBtn").show(); 
			        }else{
			        	$("#uploadBtn").hide(); 
			        }
			        //预读本地文件，如果是多文件，则会遍历。(不支持ie8/9)
			        obj.preview(function(index, file, result){
			        	$("#file_div").html("<span>"+file.name+"</span>");
			        	console.log(file.name); //得到文件对象
			        });
			    	
			    }
		  });
		  /*form监听*/
		  form.on('select(show_upload_btn)', function(data){
			  if(null != files  && data.value != ""){
					$("#uploadBtn").show();
				}else{
					$("#uploadBtn").hide();
			}
		  });
	});
}
/**
 * 清除文件
 * @returns
 */
function deletefile(){
	if(null != files){
		for(var i = 0 ; i<Object.keys(files).length;i++){
			delete  files[Object.keys(files)[i]];
		}
		files = null;
		$("#uploadBtn").hide();
		$("#file_div").html("");
	}
}

java

controller
/**
	 * 上传入学奖学金的excel文件
	 * @param request
	 * @return
	 * @throws Exception
	 */
	@SysLog("上传入学奖学金的excel文件")
	@RequestMapping(value="importScholarStudent", method=RequestMethod.POST)
	@ResponseBody
	public R feitongzhaoUploads(HttpServletRequest request) throws Exception{
		R uploadPackage = scholarShipService.scholarShipUploads(request);
		return uploadPackage;
	}
	
service：
/**
	 * 上传入学奖学金的excel文件
	 * @param request
	 * @return
	 * @throws IOException 
	 */
	@Transactional
	public R scholarShipUploads(HttpServletRequest request) throws IOException {
		R r = null;
		//创建多部分解析器
		CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(request.getSession().getServletContext());
		//判断是否有文件上传
		if(multipartResolver.isMultipart(request)){
			//将request转换为多部分request
			MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
			//获取multipartRequest中所有的文件
			Iterator<String> iter = multipartRequest.getFileNames();
			while(iter.hasNext()){
				//一次遍历所有文件
				MultipartFile file = multipartRequest.getFile(iter.next().toString());
				if (file != null) {
					//拿文件名
					String originalFilename = file.getOriginalFilename();
					//获取当前学年
					String year = DateUtil.getStringYear(new Date());
					//存放文件位置
					String fileSavePath = Global.getUserfilesBaseDir() + "/userfiles/enroll/scholarShip/"+year+"/";
					//重命名
					String UUIDname = UUID.randomUUID().toString();
					String newFileName =UUIDname+"_"+originalFilename;
					//创建文件的文件夹
					File newFile = new File(fileSavePath);
					if (!newFile.exists()) {
						newFile.mkdirs();
					}
				

					String fullPathName = newFile.getAbsolutePath();
					//保存文件
					File excelFile=new File(fullPathName + File.separator + newFileName);
					file.transferTo(excelFile);
					//文件路径
					String newexcelName = fullPathName + File.separator + newFileName;
					/*处理解析的文件*/
					
					Map<String,Object> scholarShipParseMap = scholarShipExcelParse(newexcelName);
					if(!"ok".equals(scholarShipParseMap.get("msg").toString())) {
						log.error("文件上传解析失败！");
						return R.error("203001").put("msg", scholarShipParseMap.get("msg").toString());
					}
		
					/*如果解析成功则存入数据库*/
					if(scholarShipParseMap != null && !scholarShipParseMap.isEmpty()) {
						@SuppressWarnings("unchecked")
						List<Map<String, String>>  lists= (List<Map<String, String>>) scholarShipParseMap.get("result");
						//将stuNumber查出来
						String stuNumber="";
						String examNumber="";
						for (Map<String, String> list : lists) {
							ScholarShip scholarShip = new ScholarShip();
							scholarShip.setStuName(list.get("stuName"));
							stuNumber=list.get("stuNumber");
							examNumber=list.get("examNumber");
							String stuCode=scholarShipDao.queryStuCode(stuNumber,examNumber);
							if(stuCode!=null) {
								scholarShip.setStuCode(stuCode);
								scholarShip.setExamNumber(list.get("examNumber"));
								scholarShip.setIdCard(list.get("idCard"));
								scholarShip.setTotalMoney(list.get("totalMoney"));
								scholarShip.setDistributeNumber(list.get("distributeNumber"));
								scholarShipDao.insert(scholarShip);
							}												
						}
					}
						log.info("文件上传解析成功！");
						r = R.ok().putData("0","文件上传解析成功！");
					}else {
						log.error("文件上传解析失败！");
						r = R.error("203001").put("msg", "文件上传解析失败！可能的原因：①文件有误 ②文件内容为空,请检查");
					}
				} 
			
		}
		return r;
	}
	/**
	 * 处理解析的文件存入数据库
	 * @param newSavePath
	 * @param emZip
	 * @return
	 */
	private Map<String,Object> scholarShipExcelParse(String newSavePath) {
		ScholarShip scholarShip = new ScholarShip();
		Map<String,Object> returnMaps=Maps.newHashMap();
		returnMaps.put("msg", "ok");
		List<Map<String, String>> list=Lists.newArrayList();
		try {
			/*获取固定的导入和导出的表头定义*/
			Map<String, String> maps = Constants.getScholarShipMap();
			/*解析文件*/
			List<Map<String, String>> returnList = NewStuImportExcel.getFeiTongZhaoExcel(newSavePath, maps);
			if(null == returnList) {
				returnMaps.put("msg", "文件上传解析失败！可能的原因：①文件有误 ②文件表头数量和模板不对应,请检查");
				return returnMaps;
			}
			/*在这里存入数据库需要json格式，，所以做处理*/
			int i=1;
			String str="";
			/*身份证正则验证*/
			String isIDCard2 ="^[1-9]\\d{5}[1-9]\\d{3}((0\\d)|(1[0-2]))(([0|1|2]\\d)|3[0-1])((\\d{4})|\\d{3}[A-Z])$";
			/*是否为数字*/
			Pattern pattern = Pattern.compile("^[-\\+]?[\\d]*$");
			List<String> idCardList=Lists.newArrayList();
			List<String> examNumberList=Lists.newArrayList();
			for (Map<String, String> map : returnList) {
				i++;
				/*数据校验*/
				/**验证姓名*/
				if(StringUtils.isBlank(map.get("stuName"))) {
					str="第"+i+"行姓名不能为空,请检查";
					break;
				}
				if(StringUtils.isBlank(map.get("stuNumber"))) {
					str="第"+i+"行学号不能为空,请检查";
					break;
				}
				/**验证奖学金总金额*/
				if(StringUtils.isBlank(map.get("totalMoney"))) {
					str="第"+i+"行奖学金总额不能为空,请检查";
					break;
				}else if(!pattern.matcher(map.get("totalMoney")).matches()){
					str="第"+i+"行奖学金总额只能为数字,请检查";
					break;
				}
				
				/**验证分发总次数*/
				if(StringUtils.isBlank(map.get("distributeNumber"))) {
					str="第"+i+"行奖学金分发次数不能为空,请检查";
					break;
				}else if(!pattern.matcher(map.get("distributeNumber")).matches()){
					str="第"+i+"行奖学金分发次数只能为数字,请检查";
					break;
				}
				
				if(StringUtils.isBlank(map.get("stuName"))) {
					str="第"+i+"行姓名不能为空,请检查";
					break;
				}
				/**验证身份证*/
				if(StringUtils.isBlank(map.get("idCard"))) {
					str="第"+i+"行身份证号不能为空,请检查";
					break;
				}else {
					if (!map.get("idCard").matches(isIDCard2)) {
						str="第"+i+"行身份证号格式不正确,请检查";
						break;
					}else {
						idCardList.add(map.get("idCard"));
						scholarShip.setIdCard(map.get("idCard").toString());
					}
				}

				/**验证考生号*/
				if(StringUtils.isBlank(map.get("examNumber"))) {
					str="第"+i+"行考生号不能为空,请检查";
					break;
				}else {
					if(!pattern.matcher(map.get("examNumber")).matches()) {
						str="第"+i+"行考生号只能为数字,请检查";
						break;
					}else {
						examNumberList.add(map.get("examNumber"));
						scholarShip.setExamNumber(map.get("examNumber").toString());
					}
				}
				String stuNumber=map.get("stuNumber");
				String examNumber=map.get("examNumber");
				String stuCode=scholarShipDao.queryStuCode(stuNumber,examNumber);
				if(stuCode==null) {
					str+="excel表格中学号为"+stuNumber+"的学生在数据库查无此人，请检查数据";
					break;
				}
				/*查询数据库是否有重复的学号 stuCode*/
				List<String> stuCodes=scholarShipDao.findRepeatStuCode(scholarShip);
				
				if(!stuCodes.isEmpty()) {
					str+="文件中学生已在入学奖学金列表中：她（他）的身份证号是"+String.join(",", map.get("idCard").toString()) +"<br>";
					break;
				}

				/*把map转换成json字符串*/
				list.add(map);
			}
			/*如果提示有错误则返回，停止解析*/
			if(!"".equals(str)) {
				returnMaps.put("msg",str );
				return returnMaps;
			}
			StringBuffer idCards=new StringBuffer();
			StringBuffer examNumbers=new StringBuffer();
			/**判断文件里的身份证是否有重复*/
			 Set<String> set = new HashSet<>();
		     Set<String> exist = new HashSet<>();
			for(String idcard:idCardList) {
				idCards.append("'");
				idCards.append(idcard);
				idCards.append("',");
				if (set.contains(idcard)) {
	                exist.add(idcard);
	            } else {
	                set.add(idcard);
	            }
			}
			if(!exist.isEmpty()) {
				returnMaps.put("msg","文件中身份证号："+String.join(",", exist)+"重复" );
				return returnMaps;
			}
			/*清空Set*/
			set.clear();
			exist.clear();
			
			/*清空Set*/
			set.clear();
			exist.clear();
			/**判断文件里的考生号是否有重复*/
			for(String examNumber:examNumberList) {
				examNumbers.append("'");
				examNumbers.append(examNumber);
				examNumbers.append("',");
				if (set.contains(examNumber)) {
					exist.add(examNumber);
				} else {
					set.add(examNumber);
				}
			}
			if(!exist.isEmpty()) {
				returnMaps.put("msg","文件中考生号："+String.join(",", exist)+"重复" );
				return returnMaps;
			}
			
			/*清空Set*/
			set.clear();
			exist.clear();
		
			
			/*如果提示有错误则返回，停止解析*/
			if(!"".equals(str)) {
				returnMaps.put("msg",str );
				return returnMaps;
			}
			returnMaps.put("result", list);
			return returnMaps;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
Global
package com.bigdata.campus.common.config;

import java.io.File;
import java.io.IOException;
import java.util.Map;

import org.springframework.core.io.DefaultResourceLoader;
import org.springframework.web.context.ContextLoader;

import com.bigdata.campus.common.utils.PropertiesLoader;
import com.bigdata.campus.common.utils.StringUtils;
import com.google.common.collect.Maps;

public class Global {

	/**
	 * 当前对象实例
	 */
	private static Global global = new Global();
    /**
	 * 保存全局属性值
	 */
	private static Map<String, String> map = Maps.newHashMap();
	/**
	 * 属性文件加载对象
	 */
	private static PropertiesLoader loader = new PropertiesLoader("campus.properties");
	/**
	 * 获取上传文件的根目录
	 * @return
	 */
	public static String getUserfilesBaseDir() {
		String dir = getConfig("userfiles.basedir");
		if(!dir.endsWith("/")) {
			dir += "/";
		}
//		System.out.println("userfiles.basedir: " + dir);
		return dir;
	}
	/**
	 * 获取配置
	 * @see ${fns:getConfig('adminPath')}
	 */
	public static String getConfig(String key) {
		String value = map.get(key);
		if (value == null){
			value = loader.getProperty(key);
			map.put(key, value != null ? value : StringUtils.EMPTY);
		}
		return value;
	}
}
util
	/**
	 * 解析非统招生excel
	 * @param newSavePath 文件存的绝对路径
	 * @param returnKeyMap 规定需要解析的行，key 为 表头中文对应的代码，，value为表头中文显示名称  如：  stuCode:学生编号
	 * @return
	 */
	public static List<Map<String,String>> getFeiTongZhaoExcel(String newSavePath,Map<String,String> returnKeyMap) {
		FileInputStream fis =null;
	     Workbook wookbook = null;
	     
		 if(!newSavePath.endsWith(".xls") && !newSavePath.endsWith(".xlsx")){
	           return null;
	     }
	     try{
	          //获取一个绝对地址的流
	          fis = new FileInputStream(newSavePath);
	          if(newSavePath.endsWith(".xls")) {
	        	  //2003版本的excel，用.xls结尾
	        	  wookbook = new HSSFWorkbook(fis);//得到工作簿
	        	
	          }else {
	        	  //2007版本的excel，用.xlsx结尾
	        	  wookbook = new XSSFWorkbook(fis);//得到工作簿
	          }
	          //得到一个工作表
	          Sheet sheet = wookbook.getSheetAt(0);
	        //获得表头
	         Row rowHead = sheet.getRow(0);
	         /*下载的表头是多少列，，导入就是多少列*/
	         if(rowHead.getPhysicalNumberOfCells() != returnKeyMap.size()) {
	        	 return null;
	         }
	       //获得数据的总行数
	         int totalRowNum = sheet.getLastRowNum();
	         /*行数除了表头，没数据则不解析*/
	         if(totalRowNum <=0) {
	        	 return null;
	         }
	         List<Map<String,String>> list=Lists.newArrayList();
	        //获得所有数据
	         for(int i = 1 ; i <= totalRowNum ; i++) {
	             //获得第i行对象
	             Row row = sheet.getRow(i);
	             int cellNum=0;
	             Map<String, String> stuMap=Maps.newLinkedHashMap();
	             for(Entry<String, String> map:returnKeyMap.entrySet()) {
	            	//循环获得第i行的列
		             Cell cell = row.getCell((short) cellNum);
		             String name = "";
		             if(cell != null) {
		            	 name = getRightTypeCell(cell);
		             }
		             stuMap.put(map.getKey(), name.trim());
		             cellNum++;
	             }
	             list.add(stuMap);
	         }
	         return list;
	     }catch(Exception e){
	            e.printStackTrace();
	            return null;
	     }
	}