工作中常遇到導(dǎo)入導(dǎo)出excel的需求，本獂有一簡(jiǎn)答實(shí)例與大家分享。
廢話不多說(shuō)，
1.所需jar包：
2.前端代碼：
ieport.jsp：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
<%@page import="java.util.Date"%><%@ page language="java" contentType="text/html; charset=utf-" pageEncoding="utf-"%><!DOCTYPE html PUBLIC "-//WC//DTD XHTML . Transitional//EN" "http://www.w.org/TR/xhtml/DTD/xhtml-transitional.dtd"><html xmlns="http://www.w.org//xhtml"><head> <meta http-equiv="Content-Type" content="text/html; charset=utf-" /> <title>導(dǎo)入\導(dǎo)出頁(yè)面</title> <script type="text/javascript"> function exportFile(){ window.location.href = "<%=request.getContextPath()%>/export.go"; } </script></head><body> <form action="import.go" method="post" enctype="multipart/form-data"> 文件：<input type="file" name="uploadFile"/> <br></br> <input type="submit" value="導(dǎo)入"/> <input type="button" value="導(dǎo)出" onclick="exportFile()"/> </form> </body></html> |
success.jsp：
|
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
|
<%@ page language="java" contentType="text/html; charset=utf-" pageEncoding="utf-"%> <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%> <!DOCTYPE html PUBLIC "-//WC//DTD XHTML . Transitional//EN" "http://www.w.org/TR/xhtml/DTD/xhtml-transitional.dtd"> <html xmlns="http://www.w.org//xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-" /> <title>成功頁(yè)面</title> <script type="text/javascript"> // var secUserList = '${secUserList}'; // alert(secUserList); </script> </head> <body> <c:if test="${type == 'import'}"> <div>導(dǎo)入成功！</div> <c:forEach items="${secUserList}" var="secUser"> <div>Id：${secUser.userId} | Name：${secUser.userName} | Password：${secUser.userPassword}</div> </c:forEach> </c:if> <c:if test="${type == 'export'}"> <div>導(dǎo)出成功！</div> </c:if> </body> </html> |
3.后臺(tái)代碼：
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
|
package com.controller;import java.io.File;import java.util.List;import javax.annotation.Resource;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;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.domain.SecUser;import com.service.IEportService;@Controllerpublic class IEportController { @Resource private IEportService ieportService; @RequestMapping("/import") public ModelAndView importFile(@RequestParam(value="uploadFile")MultipartFile mFile, HttpServletRequest request, HttpServletResponse response){ String rootPath = request.getSession().getServletContext().getRealPath(File.separator); List<SecUser> secUserList = ieportService.importFile(mFile, rootPath); ModelAndView mv = new ModelAndView(); mv.addObject("type", "import"); mv.addObject("secUserList", secUserList); mv.setViewName("/success"); return mv; } @RequestMapping("/export") public ModelAndView exportFile(HttpServletResponse response) { ieportService.exportFile(response); ModelAndView mv = new ModelAndView(); mv.addObject("type", "export"); mv.setViewName("/success"); return mv; }} |
service:
|
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
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
|
package com.service; import java.io.File; import java.io.FileInputStream; import java.io.InputStream; import java.io.OutputStream; import java.net.URLEncoder; import java.text.SimpleDateFormat; import java.util.ArrayList;import java.util.Date;import java.util.List;import javax.annotation.Resource;import javax.servlet.http.HttpServletResponse;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Font;import org.apache.poi.xssf.usermodel.XSSFCell;import org.apache.poi.xssf.usermodel.XSSFFont;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.springframework.stereotype.Service;import org.springframework.web.multipart.MultipartFile;import com.dao.IEportDao;import com.domain.SecUser; @Servicepublic class IEportService { @Resource private IEportDao ieportDao; public List<SecUser> importFile(MultipartFile mFile, String rootPath){ List<SecUser> secUserList = new ArrayList<SecUser>(); String fileName = mFile.getOriginalFilename(); String suffix = fileName.substring(fileName.lastIndexOf(".") + , fileName.length()); String ym = new SimpleDateFormat("yyyy-MM").format(new Date()); String filePath = "uploadFile/" + ym + fileName; try { File file = new File(rootPath + filePath); if (file.exists()) { file.delete(); file.mkdirs(); }else { file.mkdirs(); } mFile.transferTo(file); if ("xls".equals(suffix) || "XLS".equals(suffix)) { secUserList = importXls(file); ieportDao.importFile(secUserList); }else if ("xlsx".equals(suffix) || "XLSX".equals(suffix)) { secUserList = importXlsx(file); ieportDao.importFile(secUserList); } } catch (Exception e) { e.printStackTrace(); } return secUserList; } private List<SecUser> importXls(File file) { List<SecUser> secUserList = new ArrayList<SecUser>(); InputStream is = null; HSSFWorkbook hWorkbook = null; try { is = new FileInputStream(file); hWorkbook = new HSSFWorkbook(is); HSSFSheet hSheet = hWorkbook.getSheetAt(); if (null != hSheet){ for (int i = ; i < hSheet.getPhysicalNumberOfRows(); i++){ SecUser su = new SecUser(); HSSFRow hRow = hSheet.getRow(i); su.setUserName(hRow.getCell().toString()); su.setUserPassword(hRow.getCell().toString()); secUserList.add(su); } } } catch (Exception e) { e.printStackTrace(); }finally { if (null != is) { try { is.close(); } catch (Exception e) { e.printStackTrace(); } } if (null != hWorkbook) { try { hWorkbook.close(); } catch (Exception e) { e.printStackTrace(); } } } return secUserList; } private List<SecUser> importXlsx(File file) { List<SecUser> secUserList = new ArrayList<SecUser>(); InputStream is = null; XSSFWorkbook xWorkbook = null; try { is = new FileInputStream(file); xWorkbook = new XSSFWorkbook(is); XSSFSheet xSheet = xWorkbook.getSheetAt(); if (null != xSheet) { for (int i = ; i < xSheet.getPhysicalNumberOfRows(); i++) { SecUser su = new SecUser(); XSSFRow xRow = xSheet.getRow(i); su.setUserName(xRow.getCell().toString()); su.setUserPassword(xRow.getCell().toString()); secUserList.add(su); } } } catch (Exception e) { e.printStackTrace(); }finally { if (null != is) { try { is.close(); } catch (Exception e) { e.printStackTrace(); } } if (null != xWorkbook) { try { xWorkbook.close(); } catch (Exception e) { e.printStackTrace(); } } } return secUserList; } public void exportFile(HttpServletResponse response) { SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd"); OutputStream os = null; XSSFWorkbook xWorkbook = null; try { String fileName = "User" + df.format(new Date()) + ".xlsx"; os = response.getOutputStream(); response.reset(); response.setHeader("Content-disposition", "attachment; filename = " + URLEncoder.encode(fileName, "UTF-")); response.setContentType("application/octet-streem"); xWorkbook = new XSSFWorkbook(); XSSFSheet xSheet = xWorkbook.createSheet("UserList"); //set Sheet頁(yè)頭部 setSheetHeader(xWorkbook, xSheet); //set Sheet頁(yè)內(nèi)容 setSheetContent(xWorkbook, xSheet); xWorkbook.write(os); } catch (Exception e) { e.printStackTrace(); } finally { if (null != os) { try { os.close(); } catch (Exception e) { e.printStackTrace(); } } if (null != xWorkbook) { try { xWorkbook.close(); } catch (Exception e) { e.printStackTrace(); } } } } /** * set Sheet頁(yè)頭部 * @param xWorkbook * @param xSheet */ private void setSheetHeader(XSSFWorkbook xWorkbook, XSSFSheet xSheet) { xSheet.setColumnWidth(, * ); xSheet.setColumnWidth(, * ); xSheet.setColumnWidth(, * ); CellStyle cs = xWorkbook.createCellStyle(); //設(shè)置水平垂直居中 cs.setAlignment(CellStyle.ALIGN_CENTER); cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER); //設(shè)置字體 Font headerFont = xWorkbook.createFont(); headerFont.setFontHeightInPoints((short) ); headerFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); headerFont.setFontName("宋體"); cs.setFont(headerFont); cs.setWrapText(true);//是否自動(dòng)換行 XSSFRow xRow = xSheet.createRow(); XSSFCell xCell = xRow.createCell(); xCell.setCellStyle(cs); xCell.setCellValue("用戶ID"); XSSFCell xCell = xRow.createCell(); xCell.setCellStyle(cs); xCell.setCellValue("用戶名"); XSSFCell xCell = xRow.createCell(); xCell.setCellStyle(cs); xCell.setCellValue("密碼"); } /** * set Sheet頁(yè)內(nèi)容 * @param xWorkbook * @param xSheet */ private void setSheetContent(XSSFWorkbook xWorkbook, XSSFSheet xSheet) { List<SecUser> secUserList = ieportDao.getSecUserList(); CellStyle cs = xWorkbook.createCellStyle(); cs.setWrapText(true); if (null != secUserList && secUserList.size() > ) { for (int i = ; i < secUserList.size(); i++) { XSSFRow xRow = xSheet.createRow(i + ); SecUser secUser = secUserList.get(i); for (int j = ; j < ; j++) { XSSFCell xCell = xRow.createCell(j); xCell.setCellStyle(cs); switch (j) { case : xCell.setCellValue(secUser.getUserId()); break; case : xCell.setCellValue(secUser.getUserName()); break; case : xCell.setCellValue(secUser.getUserPassword()); break; default: break; } } } } }} |
dao:
|
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
|
package com.dao; import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import javax.annotation.Resource;import org.springframework.stereotype.Repository;import com.domain.SecUser;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.core.RowMapper;@Repositorypublic class IEportDao { @Resource private JdbcTemplate jdbcTemplate; private RowMapper<SecUser> suRowMapper = null; private IEportDao() { suRowMapper = new RowMapper<SecUser>() { @Override public SecUser mapRow(ResultSet rs, int index) throws SQLException { SecUser secUser = new SecUser(); secUser.setUserId(rs.getString("USER_ID")); secUser.setUserName(rs.getString("USER_NAME")); secUser.setUserPassword(rs.getString("USER_PASSWORD")); return secUser; } }; } public void importFile(List<SecUser> secUserList) { try { String sql = "INSERT INTO SEC_USER VALUES(UUID(),?,?)"; List<Object[]> paramsList = new ArrayList<Object[]>(); for (int i = ; i < secUserList.size(); i++) { SecUser secUser = secUserList.get(i); Object[] params = new Object[]{secUser.getUserName(),secUser.getUserPassword()}; paramsList.add(params); } jdbcTemplate.batchUpdate(sql, paramsList); } catch (Exception e) { e.printStackTrace(); } } public List<SecUser> getSecUserList() { List<SecUser> suList = new ArrayList<SecUser>(); StringBuffer sb = new StringBuffer(); sb.append("SELECT SU.USER_ID,SU.USER_NAME,SU.USER_PASSWORD FROM SEC_USER SU"); try { suList = jdbcTemplate.query(sb.toString(), suRowMapper); } catch (Exception e) { e.printStackTrace(); } return suList; }} |
domain:
|
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
|
package com.domain; public class SecUser { String userId; //用戶ID String userName; //用戶名 String userPassword; //密碼 public String getUserId() { return userId; } public void setUserId(String userId) { this.userId = userId; } public String getUserPassword() { return userPassword; } public void setUserPassword(String userPassword) { this.userPassword = userPassword; } public String getUserName() { return userName; } public void setUserName(String userName) { this.userName = userName; } } |
4.配置文件：
|
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
|
<?xml version="." encoding="UTF-"?><web-app xmlns:xsi="http://www.w.org//XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app__.xsd" id="WebApp_ID" version="."> <display-name>SpringSpringmvcPoi</display-name> <welcome-file-list> <welcome-file>ieport.jsp</welcome-file> </welcome-file-list> <!-- 指定 Spring 配置文件的名稱和位置 --> <context-param> <param-name>contextConfigLocation</param-name> <param-value> classpath:application-context.xml classpath:dataSource-context.xml </param-value> </context-param> <!-- 配置啟動(dòng) Spring 的 Listener --> <listener> <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class> </listener> <!-- 配置 SpringMVC 的 DispatcherServlet --> <servlet> <servlet-name>DispatcherServlet</servlet-name> <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class> <!-- 配置 SpringMVC 的配置文件的位置 --> <init-param> <param-name>contextConfigLocation</param-name> <param-value>classpath:spring-mvc.xml</param-value> </init-param> </servlet> <servlet-mapping> <servlet-name>DispatcherServlet</servlet-name> <url-pattern>*.go</url-pattern> </servlet-mapping> <!-- 上傳文件編碼，防止亂碼 --> <filter> <filter-name>CharacterEncodingFilter</filter-name> <filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class> <init-param> <param-name>encoding</param-name> <param-value>utf-</param-value> </init-param> </filter> <filter-mapping> <filter-name>CharacterEncodingFilter</filter-name> <url-pattern>/*</url-pattern> </filter-mapping></web-app> |
|
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
|
<?xml version="." encoding="UTF-"?><beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w.org//XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p" xmlns:context="http://www.springframework.org/schema/context" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" 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-..xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-..xsd http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-..xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-..xsd"> <!-- 配置自動(dòng)掃描的包 --> <context:component-scan base-package="com.controller"></context:component-scan> <!-- 配置SpringMVC的視圖解析器 --> <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver"> <property name="prefix" value="/WEB-INF/views/"></property> <property name="suffix" value=".jsp"></property> </bean> <!-- 支持上傳文件 --> <bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver"/></beans> |
|
1
2
3
4
5
6
7
8
9
10
|
<?xml version="." encoding="UTF-"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w.org//XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-..xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-..xsd"> <context:component-scan base-package="com"></context:component-scan> </beans> |
|
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
|
<?xml version="." encoding="UTF-"?><beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w.org//XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation=" http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd"> <!-- 讀取jdbc配置文件 --> <context:property-placeholder location="classpath:jdbc.properties" /> <!-- 配置數(shù)據(jù)源 --> <bean id="dataSource" class="com.mchange.v.cp.ComboPooledDataSource" destroy-method="close"> <property name="user" value="${jdbc.user}"></property> <property name="password" value="${jdbc.password}"></property> <property name="driverClass" value="${jdbc.driverClass}"></property> <property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property> <!-- 當(dāng)連接池中的連接用完時(shí)，CP一次性創(chuàng)建新連接的數(shù)目 --> <property name="acquireIncrement" value=""></property> <!-- 初始化時(shí)創(chuàng)建的連接數(shù)，必須在minPoolSize和maxPoolSize之間 --> <property name="initialPoolSize" value=""></property> <property name="maxPoolSize" value=""></property> <property name="minPoolSize" value=""></property> <property name="maxConnectionAge" value=""></property> <property name="maxIdleTime" value=""></property> <property name="maxIdleTimeExcessConnections" value=""></property> <property name="breakAfterAcquireFailure" value="false"></property> <property name="testConnectionOnCheckout" value="false"></property> <property name="testConnectionOnCheckin" value="false"></property> <!-- 每秒檢查連接池中的空閑連接 --> <property name="idleConnectionTestPeriod" value=""></property> <property name="acquireRetryAttempts" value=""></property> <property name="acquireRetryDelay" value=""></property> <property name="preferredTestQuery" value="SELECT FROM DUAL"></property> </bean> <!-- 配置Jdbc模板JdbcTemplate --> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <constructor-arg ref="dataSource"></constructor-arg> </bean></beans> |
|
1
2
3
4
|
jdbc.driverClass=com.mysql.jdbc.Driverjdbc.jdbcUrl=jdbc:mysql://localhost:/mydbjdbc.user=myuserjdbc.password=myuser |
5.目錄結(jié)構(gòu)：
6.結(jié)果演示
導(dǎo)入：
導(dǎo)出：
PS:
1.本獂新手，由于還沒(méi)清楚怎么添加附件，故將所有代碼貼出并加上目錄結(jié)構(gòu)，日后了解怎么添加附件，再修改。
以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持服務(wù)器之家。
原文鏈接：http://www.cnblogs.com/littlecharacter/p/5580676.html
