I often encounter the need to import and export excel at work. I have a brief answer example to share with you.
Don't say much nonsense,
1. Required jar package:
2. Front-end code:
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>Import/export page</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"> File: <input type="file" name="uploadFile"/> <br></br> <input type="submit" value="import"/> <input type="button" value="export" 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>Success page</title> <script type="text/javascript"> // var secUserList = '${secUserList}'; // alert(secUserList); </script> </head> <body> <c:if test="${type == 'import'}"> <div>Import successfully! </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>Export successfully! </div> </c:if> </body> </html>3. Background code:
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 page header setSheetHeader(xWorkbook, xSheet); //set Sheet page content 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 page header* @param xWorkbook * @param xSheet */ private void setSheetHeader(XSSFWorkbook xWorkbook, XSSFSheet xSheet) { xSheet.setColumnWidth(, * ); xSheet.setColumnWidth(, * ); xSheet.setColumnWidth(, * ); CellStyle cs = xWorkbook.createCellStyle(); //Set horizontal and vertical centering cs.setAlignment(CellStyle.ALIGN_CENTER); cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER); //Set font Font headerFont = xWorkbook.createFont(); headerFont.setFontHeightInPoints((short)); headerFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); headerFont.setFontName("安体"); cs.setFont(headerFont); cs.setWrapText(true);//Is it possible to wrap the line automatically XSSFRow xRow = xSheet.createRow(); XSSFCell xCell = xRow.createCell(); xCell.setCellStyle(cs); xCell.setCellValue("User ID"); XSSFCell xCell = xRow.createCell(); xCell.setCellStyle(cs); xCell.setCellValue("User Name"); XSSFCell xCell = xRow.createCell(); xCell.setCellStyle(cs); xCell.setCellValue("password"); } /** * set Sheet page content* @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; //User ID String userName; //Username String userPassword; //Password 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. Configuration file:
<?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 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> <!-- Specify the name and location of the Spring configuration file --> <context-param> <param-name>contextConfigLocation</param-name> <param-value> classpath:application-context.xml classpath:dataSource-context.xml </param-value> </context-param> <!-- Configure Listener --> <listener> <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class> </listener> <!-- Configure SpringMVC's DispatcherServlet --> <servlet> <servlet-name>DispatcherServlet</servlet-name> <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class> <!-- Configure SpringMVC's configuration file location --> <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> <!-- Upload file encoding to prevent garbled code--> <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/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"> <!-- Configure automatic scanning packages --> <context:component-scan base-package="com.controller"></context:component-scan> <!-- Configure SpringMVC's view resolver --> <bean> <property name="prefix" value="/WEB-INF/views/"></property> <property name="suffix" value=".jsp"></property> </bean> <!-- Support uploading of files --> <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"> <!-- Read jdbc configuration file --> <context:property-placeholder location="classpath:jdbc.properties" /> <!-- Configure data source--> <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> <!-- When the connections in the connection pool are used up, the number of new connections created at one time by CP --> <property name="acquireIncrement" value=""></property> <!-- The number of connections created during initialization must be between minPoolSize and 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="testConnectionOnCheckout" value="false"></property> <property name="testConnectionOnCheckin" value="false"></property> <!-- Check idle connections in the connection pool every second-> <property name="idleConnectionTestPeriod" value=""></property> <property name="acquireRetryAttempts" value=""></property> <property name="acquireRetryDelay" value=""></property> <property name="preferredTestQuery" value="SELECT FROM DUAL"></property> </bean> <!-- Configure Jdbc template 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. Directory structure:
6. Results demonstration
Import:
Export:
PS:
1. This novice, since she has not yet known how to add attachments, she posts all the code and adds the directory structure. She will learn how to add attachments in the future and then modify them.
The above is all the content of this article. I hope it will be helpful to everyone's learning and I hope everyone will support Wulin.com more.