在实际应用中,经常会遇到上传Excel或者下载Excel的情况,比如导入数据、下载统计数据等等场景。针对这个问题,我写了个基于SpringMVC的简单上传下载示例,其中Excel的处理使用Apache的POI组件。
主要依赖的包如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
< 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类
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
|
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); } } |
(二)实体类
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
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; } @Override public String toString() { return "UserInfo [userName=" + userName + ", password=" + password + "]" ; } } |
(三)View类
这个类在下载时用到,在Spring渲染页面时使用自定义的View类进行Excel的相关处理。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
|
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 { @Override protected 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类。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
<?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> |
(五)测试页面
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
< 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://blog.csdn.net/qiubabin/article/details/50113675