SpringMVC下Excel文件的上传下载实现

 在实际应用中，经常会遇到上传Excel或者下载Excel的情况，比如导入数据、下载统计数据等等场景。针对这个问题，我写了个基于SpringMVC的简单上传下载示例，其中Excel的处理使用Apache的POI组件。

 

主要依赖的包如下： 

<dependency><groupId>commons-io</groupId><artifactId>commons-io</artifactId><version>2.4</version></dependency><dependency><groupId>commons-fileupload</groupId><artifactId>commons-fileupload</artifactId><version>1.3.1</version></dependency><dependency><groupId>org.springframework</groupId><artifactId>spring-web</artifactId><version>4.0.0.RELEASE</version></dependency><dependency><groupId>org.springframework</groupId><artifactId>spring-webmvc</artifactId><version>4.0.0.RELEASE</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.10.1</version></dependency>

 

 

相关处理类：

（一）Controller类 

package com.research.spring.controller;import java.io.IOException;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.springframework.stereotype.Controller;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RequestParam;import org.springframework.web.multipart.MultipartFile;import org.springframework.web.servlet.ModelAndView;import com.research.spring.model.UserInfo;import com.research.spring.view.ExcelView;@Controller@RequestMapping("/file")public class FileController {/** * Excel文件上传处理 * @param file * @return */@RequestMapping("/upload")public ModelAndView uploadExcel(@RequestParam("file") MultipartFile file){List<UserInfo> list = new ArrayList<UserInfo>();                //这里只处理文件名包括“用户”的文件，模板使用下载模板if( file.getOriginalFilename().contains("用户") ){try {Workbook wb = new HSSFWorkbook(file.getInputStream());Sheet sheet = wb.getSheetAt(0);for( int i = 1; i <= sheet.getLastRowNum(); i++ ){Row row = sheet.getRow(i);UserInfo info = new UserInfo();info.setUserName(row.getCell(0).getStringCellValue());info.setPassword(row.getCell(1).getStringCellValue());list.add(info);}} catch (IOException e) {e.printStackTrace();}}ModelAndView mav = new ModelAndView("content");mav.addObject("content",list.toString());return mav;}/** * Excel文件下载处理 */@RequestMapping("/download")public ModelAndView downloanExcel(){List<UserInfo> list = new ArrayList<UserInfo>();UserInfo userInfo = new UserInfo();userInfo.setPassword("0000");userInfo.setUserName("sdfas");list.add(userInfo);list.add(userInfo);list.add(userInfo);list.add(userInfo);Map<String,List<UserInfo>> map = new HashMap<String, List<UserInfo>>();map.put("infoList", list);ExcelView ve = new ExcelView();return new ModelAndView(ve,map);}} 

 

 （二）实体类 

package com.research.spring.model;public class UserInfo {private String userName;private String password;public String getUserName() {return userName;}public void setUserName(String userName) {this.userName = userName;}public String getPassword() {return password;}public void setPassword(String password) {this.password = password;}@Overridepublic String toString() {return "UserInfo [userName=" + userName + ", password=" + password+ "]";}}

 

（三）View类

这个类在下载时用到，在Spring渲染页面时使用自定义的View类进行Excel的相关处理。 

package com.research.spring.view;import java.io.OutputStream;import java.net.URLEncoder;import java.util.List;import java.util.Map;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.springframework.web.servlet.view.document.AbstractExcelView;import com.research.spring.model.UserInfo;/** * 下载Excel视图 *  * @author wdmcygah * */  public class ExcelView extends AbstractExcelView {@Overrideprotected void buildExcelDocument(Map<String, Object> model,HSSFWorkbook workbook, HttpServletRequest request,HttpServletResponse response) throws Exception {@SuppressWarnings("unchecked")List<UserInfo> list = (List<UserInfo>) model.get("infoList");if (list != null && list.size() != 0) {int len = list.size();Sheet sheet = workbook.createSheet();// 第一行文字说明Row row = sheet.createRow(0);Cell cell = row.createCell(0, Cell.CELL_TYPE_STRING);cell.setCellValue("用户名");cell = row.createCell(1, Cell.CELL_TYPE_STRING);cell.setCellValue("密码");//下面是具体内容for (int i = 0; i < len; i++) {row = sheet.createRow(i + 1);cell = row.createCell(0, Cell.CELL_TYPE_STRING);cell.setCellValue(list.get(i).getUserName());cell = row.createCell(1, Cell.CELL_TYPE_STRING);cell.setCellValue(list.get(i).getPassword());}}response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");//这里对文件名进行编码，保证下载时汉字显示正常String fileName = URLEncoder.encode("用户.xls", "utf-8");//Content-disposition属性设置成以附件方式进行下载response.setHeader("Content-disposition", "attachment;filename="+ fileName);OutputStream os = response.getOutputStream();workbook.write(os);os.flush();os.close();}}

 

（四）主要配置文件

上传文件时需要在配置文件中配置MultipartResolver类，配置后Spring会自动将文件传成MultipartFile对象，然后就可以进行相应的处理。示例看Controller类。 

<?xml version="1.0" encoding="UTF-8"?><beans xmlns="http://www.springframework.org/schema/beans"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p"xmlns:context="http://www.springframework.org/schema/context"xmlns:mvc="http://www.springframework.org/schema/mvc" xmlns:util="http://www.springframework.org/schema/util"xsi:schemaLocation="http://www.springframework.org/schema/beans  http://www.springframework.org/schema/beans/spring-beans-3.0.xsd  http://www.springframework.org/schema/context  http://www.springframework.org/schema/context/spring-context-3.0.xsd   http://www.springframework.org/schema/mvc   http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd  http://www.springframework.org/schema/util  http://www.springframework.org/schema/util/spring-util-3.0.xsd"><context:component-scan base-package="com.research" /><bean class="org.springframework.web.servlet.view.InternalResourceViewResolver"><property name="viewClass"value="org.springframework.web.servlet.view.JstlView" /><property name="prefix" value="/WEB-INF/" /><property name="suffix" value=".jsp" /></bean><!-- 上传文件解析器配置 --><bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver"><property name="defaultEncoding" value="UTF-8"></property><!-- 上传文件的大小限制 ，单位是字节--><property name="maxUploadSize" value="5242880000000"></property><!-- 上传文件的临时路径，上传完成后会自动删除 --><property name="uploadTempDir" value="upload/temp"></property></bean></beans>

 

（五）测试页面

<html><head>        <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> </head><body><h3>测试下载Excel功能</h3><form action="file/download.htm" enctype="multipart/form-data" method="post"><input type="submit" value="下载Excel"></input></form><h3>测试上传Excel功能</h3><form action="file/upload.htm" enctype="multipart/form-data" method="post"><input type="file" name="file"></input><input type="submit" value="上传Excel"></input></form></body></html>

 

---

如果想看完整源码，可以到我的Github仓库查看：https://github.com/wdmcygah/research-spring。 其中，上传文件只处理符合下载模板的文件。若要处理其它文件需要自实现。代码测试通过无误。