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

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

废话不多说，

1.所需jar包：

2.前端代码：

ieport.jsp：

				?

									<%@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：

				?

									<%@ 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:

				?

									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:

				?

									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:

				?

									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:

				?

									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.配置文件：

				?

									<?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>

				?

									<?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>

				?

									<?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>

				?

									<?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>

				?

									jdbc.driverClass=com.mysql.jdbc.Driver

									jdbc.jdbcUrl=jdbc:mysql://localhost:/mydb

									jdbc.user=myuser

									jdbc.password=myuser