这里有新鲜出炉的精品教程,程序狗速度看过来!
Spring是什么呢?首先它是一个开源的项目,而且目前非常活跃;它是一个基于IOC和AOP的构架多层j2ee系统的框架,但它不强迫你必须在每一层 中必须使用Spring,因为它模块化的很好,允许你根据自己的需要选择使用它的某一个模块;它实现了很优雅的MVC,对不同的数据访问技术提供了统一的接口,采用IOC使得可以很容易的实现bean的装配,提供了简洁的AOP并据此实现Transcation Managment,等等
本篇文章主要介绍了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} | 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 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
5.目录结构:
6.结果演示
导入:
导出:
PS:
1.本獂新手,由于还没清楚怎么添加附件,故将所有代码贴出并加上目录结构,日后了解怎么添加附件,再修改。
来源: http://www.phperz.com/article/17/1127/358980.html