java Excel表格导入

上篇文章介绍了Excel表格的导出共能，这次介绍Excel表格导入到保存。

1.我们先在页面创建一个导入按钮，我们要用form表单提交，input file选择文件

<input type="file" style="display: none;" id="import_file" name="import_file" />
<a href="javascript:void(0);" id="according-user" class="btn btn-export">导入</a>

input框选用file类型，下面做一个导入的按钮（链接也行）

<form id="staffListForm" action="<c:url value='/staff/list?entId=${entId}'/>" method="post" enctype="multipart/form-data">

使用form表单提交，导入文件enctype="multipart/form-data"必不可少

下面是导入的js，需要做导入文件的判断

/*导入员工*/
       $("#according-user").click(function(){
		$("#import_file").click();
	});
	
	$("#import_file").change(function(){
		var fileName = $("#import_file").val();
		if(fileName.length > 1 && fileName ) {  
			var ldot = fileName.lastIndexOf(".");
			var type = fileName.substring(ldot + 1).toLowerCase();
			if(type == "xls" || type == "xlsx") {
				var fileFrontName = fileName.substring(0,ldot);
				$("#fileNameDiv").html(fileName);
				var requestUrl = basePath+"/staff/importStaff";
					$("#staffListForm").ajaxSubmit({
						url:requestUrl,// 跳转到 action
						type: "POST",
						dataType: "json",
						success:function(data) {    
							if(!data.status){
								parent.layer.msg("导入失败！请优先检查好上传文件内容是否正确！",{icon:3},function(){
									parent.location.reload();
								});
							}
							else{
								parent.layer.msg("文件 "+fileFrontName + " 导入成功",{icon:1},function(){
									parent.location.reload();
								});  
							}
						},
						error:function() {    
							parent.layer.msg("导入操作异常！",{icon:5},function(){parent.location.reload();});
						}  
					});
			} else {
				this.value="";
				layer.msg("文件格式不正确");
				return;
			}
		}
		
	});

2.将文件导入并保存

/**
	 * 导入员工
	 * @param request
	 * @param entStaffInfo
	 * @return
	 */
	@ResponseBody
	@RequestMapping(value = "/importStaff", method = {RequestMethod.POST,RequestMethod.GET})
	public Map<String, Object> importStaff(HttpServletRequest request) {
		Map<String, Object> result = new HashMap<String, Object>();
		try {
			MultipartHttpServletRequest multipartRequest=(MultipartHttpServletRequest)request;
	        MultipartFile importFile = multipartRequest.getFile("import_file");
	    	ExcelFile ef =new ExcelFile();
	    	List<Map<String, Object>> presentList = null;
	    	if(importFile.getOriginalFilename().toLowerCase().endsWith(".xls")){
	    		presentList = ef.readWithXls(importFile.getInputStream(), 0, 2, new String[]{"name","gender","cellphone", "email","entName","departName","positionName"});
	     	}else{
	     		presentList = ef.readWithXlsx(importFile.getInputStream(), 0, 2, new String[]{"name","gender","cellphone", "email","entName","departName","positionName"});
	     	}
	    	EntStaffInfo entStaffInfo = null;
	    	for(Map<String, Object> map : presentList) {
	    		Map<String,Object> properties = new HashMap<String, Object>();
	    		result.put("status", flag);
	    	}
		} catch (IOException e) {
			e.printStackTrace();
		}
		return result;
	}	

跟上一篇文章一样，我们需要导入poi的jar包

读取上传的文件需要spring-web-3.2.9.RELEASE.jar

MultipartHttpServletRequest multipartRequest=(MultipartHttpServletRequest)request;
MultipartFile importFile = multipartRequest.getFile("import_file");

配置上传spring-servlet.xml

<!-- 上传文件解释器 -->
	<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
		<property name="defaultEncoding" value="utf-8" />
		<property name="maxUploadSize" value="10485760" />
		<property name="maxInMemorySize" value="4096" />
		<property name="resolveLazily" value="true" />
	</bean>

读取Excel方法

/**
	 * 读取Excel文件
	 * 
	 * @param fileStream Excel文件流
	 * @param sheetIndex Sheet编号(起始为0)
	 * @param titleRow 标题列的高度(如果为0，则表明没有标题行)
	 * @param columns 列名
	 * 
	 * @return 读取后的Excel数据
	 */
	public List<Map<String, Object>> readWithXlsx(InputStream fileStream, int sheetIndex, int titleRow, String[] columns) {
		Workbook wb = null;
		try {
			wb = new XSSFWorkbook(fileStream);
		} catch (IOException e) {
			e.printStackTrace();
		}
		return this.read(wb, sheetIndex, titleRow, columns);
	}
	
	/**
	 * 读取Excel文件
	 * 
	 * @param fileStream Excel文件流
	 * @param sheetIndex Sheet编号(起始为0)
	 * @param titleRow 标题列的高度(如果为0，则表明没有标题行)
	 * @param columns 列名
	 * 
	 * @return 读取后的Excel数据
	 */
	public List<Map<String, Object>> readWithXls(InputStream fileStream, int sheetIndex, int titleRow, String[] columns) {
		Workbook wb = null;
		try{
			wb = new HSSFWorkbook(new POIFSFileSystem(fileStream));
		}catch(Exception e){
			e.printStackTrace();
		}
		return this.read(wb, sheetIndex, titleRow, columns);
	}

继续补充完善。。。