工作中常遇到導入導出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}|Name：${secUser.userName}|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> <property name="prefix" value="/WEB-INF/views/"></property> <property name="suffix" value=".jsp"></property> </bean> <!-- 支持上傳文件--> <bean id="multipartResolver"/> </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" 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"> <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
5.目錄結構：
6.結果演示
導入：
導出：
PS:
1.本新手，由於還沒清楚怎麼添加附件，故將所有代碼貼出並加上目錄結構，日後了解怎麼添加附件，再修改。
以上就是本文的全部內容，希望對大家的學習有所幫助，也希望大家多多支持武林網。