详解poi+springmvc+springjdbc导入导出excel实例

时间：2022-07-01 01:43:45

工作中常遇到导入导出excel的需求，本獂有一简答实例与大家分享。

废话不多说，

1.所需jar包：

详解poi+springmvc+springjdbc导入导出excel实例

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>导入\导出页面</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="导入"/>
    <input type="button" value="导出" 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>成功页面</title>
   
   <script type="text/javascript">
 //     var secUserList = '${secUserList}';
 //     alert(secUserList);
   </script>
 </head>
 <body>
   <c:if test="${type == 'import'}">
     <div>导入成功！</div>
     <c:forEach items="${secUserList}" var="secUser">
       <div>Id：${secUser.userId}&nbsp;|&nbsp;Name：${secUser.userName}&nbsp;|&nbsp;Password：${secUser.userPassword}</div>
     </c:forEach>
   </c:if>
   <c:if test="${type == 'export'}">
     <div>导出成功！</div>
   </c:if>
 </body>
 </html>

3.后台代码：

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;
@Controller
public 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;
 @Service
public 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页头部
      setSheetHeader(xWorkbook, xSheet);
      
      //set Sheet页内容
      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页头部
  * @param xWorkbook
  * @param xSheet
  */
  private void setSheetHeader(XSSFWorkbook xWorkbook, XSSFSheet xSheet) {
    xSheet.setColumnWidth(, * );
    xSheet.setColumnWidth(, * );
    xSheet.setColumnWidth(, * );
    
    CellStyle cs = xWorkbook.createCellStyle();
    //设置水平垂直居中
    cs.setAlignment(CellStyle.ALIGN_CENTER);
    cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    //设置字体
    Font headerFont = xWorkbook.createFont();
    headerFont.setFontHeightInPoints((short) );
    headerFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
    headerFont.setFontName("宋体");
    cs.setFont(headerFont);
    cs.setWrapText(true);//是否自动换行
    
    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页内容
  * @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;
 
@Repository
public 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>
  
  <!-- 配置启动 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">
 
  <!-- 配置自动扫描的包 -->
  <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" />
 
  <!-- 配置数据源 -->
  <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>
    
    <!-- 当连接池中的连接用完时，CP一次性创建新连接的数目 -->
    <property name="acquireIncrement" value=""></property>
    <!-- 初始化时创建的连接数，必须在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.Driver
jdbc.jdbcUrl=jdbc:mysql://localhost:/mydb
jdbc.user=myuser
jdbc.password=myuser

5.目录结构：

详解poi+springmvc+springjdbc导入导出excel实例

6.结果演示

导入：

详解poi+springmvc+springjdbc导入导出excel实例

详解poi+springmvc+springjdbc导入导出excel实例

详解poi+springmvc+springjdbc导入导出excel实例

详解poi+springmvc+springjdbc导入导出excel实例

详解poi+springmvc+springjdbc导入导出excel实例

导出：

详解poi+springmvc+springjdbc导入导出excel实例

PS:

1.本獂新手，由于还没清楚怎么添加附件，故将所有代码贴出并加上目录结构，日后了解怎么添加附件，再修改。

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持服务器之家。

原文链接：http://www.cnblogs.com/littlecharacter/p/5580676.html

标签：

相关文章