1.首先我们先导入poi和文件上传的依赖
<!--POI-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.14-beta1</version>
</dependency>
<!--文件上传依赖-->
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.2.2</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.4</version>
</dependency>
2.在spring-mvc.xml中配置文件上传解析器
<!-- 配置文件上传解析器 -->
<!-- id 的值是固定的-->
<bean id="multipartResolver"
class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
<!-- 设置上传文件的最大尺寸为 5MB -->
<property name="maxUploadSize">
<value>5242880</value>
</property>
</bean>
3.创建index.html
<!-- excel文件导出 --> <p><a href="User/exportExcel.do" rel="external nofollow" >导出</a> <!-- excel文件导入 --> <form action="User/importExcel.do" method="post" enctype="multipart/form-data"> <input type="file" name="userExcel" /> <input type="submit" value="导入"> </form>
4.创建实体类
public class User {
private Integer id;
private String username;
private String password;
/* get 和 set */
}
5.Controller层
/**
* 导出Excel
* @param request
* @param response
*/
@RequestMapping("/exportExcel")
@ResponseBody
public void exportExcel(HttpServletRequest request, HttpServletResponse response){
try {
//获取数据源
List<User> userList = service.queryUserAll();
//导出excel
response.setHeader("Content-Disposition","attachment;filename="+new String("用户信息.xls".getBytes(),"ISO-8859-1"));
response.setContentType("application/x-excel;charset=UTF-8");
OutputStream outputStream = response.getOutputStream();
//导出
service.exportExcel(userList,outputStream);
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 导入exc
* @param userExcel
* @param request
* @param session
* @return
*/
@RequestMapping("/importExcel")
@ResponseBody
public String importExcel(MultipartFile userExcel, HttpServletRequest request, HttpSession session) throws IOException, InvalidFormatException {
if(userExcel == null){
session.setAttribute("excelName", "未上传文件，上传失败！");
return null;
}
String userExcelFileName = userExcel.getOriginalFilename();
if(!userExcelFileName.matches("^.+\\.(?i)((xls)|(xlsx))$")){
session.setAttribute("excelName", "文件格式不正确！请使用.xls或.xlsx后缀的文档，导入失败！");
return null;
}
//导入
service.importExcel(userExcel);
session.setAttribute("excelName", "导入成功！");
return "redirect:queryUserAll.do";
}
6.运行测试
1.点击导出将数据库的内容以后缀为 .xls的文件下载下来
2. 选择Excel文件点击导入会将文件里的内容导入到数据库中
