springmvc+bootstrap +java jxl批量导入（excel）数据库-文件上传下载

导入maven依赖

<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>

<!– https://mvnrepository.com/artifact/commons-io/commons-io –>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.4</version>
</dependency>
<!– 文件上传所依赖的jar包 –>
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>

配置springmvc-config.xml

<bean id=”multipartResolver”
class=”org.springframework.web.multipart.commons.CommonsMultipartResolver”>
<!–上传文件的最大大小–>
<property name=”maxUploadSize” value=”17367648787″></property>
<!– 上传文件的编码 –>
<property name=”defaultEncoding” value=”UTF-8″></property>
</bean>

前端界面：bootstrap模态框+form提交

<button type=”button” class=”btn btn-primary” data-toggle=”modal” data-target=”.bs-example-modal-sm”>批量导入</button>

<form action=”uploadFile” method=”post” enctype=”multipart/form-data”>
<div class=”modal fade bs-example-modal-sm” tabindex=”-1″ role=”dialog” aria-labelledby=”mySmallModalLabel”>
<div class=”modal-dialog modal-sm” role=”document”>
<div class=”modal-content”>
<div class=”modal-header”>
<button type=”button” class=”close” data-dismiss=”modal” aria-label=”Close”><span aria-hidden=”true”>&times;</span></button>
<h4 class=”modal-title”>用户信息导入</h4>
</div>
<div class=”modal-body”>
<input type=”file” name=”upfile”>
</div>
<div class=”modal-footer”>
<button type=”button” class=”btn btn-default” data-dismiss=”modal”>Close</button>
<button type=”submit” class=”btn btn-primary”>导入数据</button>
</div>
</div>
</div>
</div>
</form>

前端效果演示

Controller层测试代码，业务逻辑最好写在Service层

@RequestMapping(value = “/uploaduser.action” ,method = RequestMethod.POST)
@ResponseBody
public String upload(@RequestParam(value=”upfile” ,required = false) MultipartFile part ,Model model) {
	try{
		userservice.upload(part.getInputStream());
	}catch(Exception e) {
		e.printStackTrace();
	}
	return “success”;
}

service层

public void upload(InputStream in)throws Exception{
	try{
		//1.将part获取到的文件转成二进制流由workbook获取到了excel文件
		Workbook book = Workbook.getWorkbook(in) ;
		//2.获取文件的sheet从0开始为sheet1
		Sheet sheet = book.getSheet(0) ;
		//3.获取文件的cell表格通过坐标（x,y）获取
		// Cell cell = sheet.getCell(0, 0) ;
		//获取到的总行和总列数
		System.out.println(sheet.getColumns());
		System.out.println(sheet.getRows());
		//由循环获取表格的所有坐标
		for(int i = 0 ;i < sheet.getRows(); i++) {
			UserInfo user = new UserInfo() ;
			//由循环获取表格的所有坐标
			//行数不固定
			for(int i = 0 ;i < sheet.getRows(); i++) {
				//姓名
				Cell nameCell = sheet.getCell(0, i) ;
				user.setUserName(nameCell.getContents());
				//性别
				Cell sexCell = sheet.getCell(1, i) ;
				user.setUserSex(sexCell.getContents());
				//手机
				Cell phoneCell = sheet.getCell(2, i) ;
				user.setUserPhone(phoneCell.getContents());
				//密码
				Cell passwdCell = sheet.getCell(3, i) ;
				user.setUserPw(passwdCell.getContents());
				//注册日期
				Cell timeCell = sheet.getCell(4, i) ;
				//类型
				Cell typeCell = sheet.getCell(5, i) ;
				user.setUserType(typeCell.getContents());
				//添加用户,此处添加方法
				userdao.add(user) ;
			}
		}
		book.close();
	}catch(Exception e){
		e.printStackTrace();
	}
	
}

在后台查看结果完成！