工作中常遇到导入导出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.本獂新手，由于还没清楚怎么添加附件，故将所有代码贴出并加上目录结构，日后了解怎么添加附件，再修改。
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持移动技术网。
如对本文有疑问， 点击进行留言回复！！
利用python将Mysql信息以Excel文件并作为邮件附件发送
springmvc+mybaits+mysql上传表情Incorrect string value: ‘\xF0\x9F\xA4\xB4\xF0\x9F...‘ for
SpringCloud Greenwich集成Seata1.2.0详解说明(原创by ulwfcyvi)
mybatis generator生成代码库 与指定的库不一致 为其他库的同名表
网友评论