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

详解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-"%>

文件：

success.jsp：

<%@ page language="java" contentType="text/html; charset=utf-" pageEncoding="utf-"%>

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>

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 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 importFile(MultipartFile mFile, String rootPath){

List secUserList = new ArrayList();

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 importXls(File file) {

List secUserList = new ArrayList();

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 importXlsx(File file) {

List&lOpBeBuLMmt;SecUser> secUserList = new ArrayList();

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 {

OpBeBuLMm 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();

}

http:// }

}

}

/**

* 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 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 suRowMapper = null;

private IEportDao() {

suRowMapper = new RowMapper() {

@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 secUserList) {

try {

String sql = "INSERT INTO SEC_USER VALUES(UUID(),?,?)";

List

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 getSecUserList() {

List suList = new ArrayList();

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

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=".">

SpringSpringmvcPoi

ieport.jsp

contextConfigLocation

classpath:application-context.xml

classpath:dataSource-context.xml

org.springframework.web.context.ContextLoaderListener

DispatcherServlet

org.springframework.web.servlet.DispatcherServlet

contextConfigLocation

classpath:spring-mvc.xml

DispatcherServlet

*.go

CharacterEncodingFilter

org.springframework.web.filter.CharacterEncodingFilter

encoding

utf-

CharacterEncodingFilter

/*

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=".">

SpringSpringmvcPoi

ieport.jsp

contextConfigLocation

classpath:application-context.xml

classpath:dataSource-context.xml

org.springframework.web.context.ContextLoaderListener

DispatcherServlet

org.springframework.web.servlet.DispatcherServlet

contextConfigLocation

classpath:spring-mvc.xml

DispatcherServlet

*.go

CharacterEncodingFilter

org.springframework.web.filter.CharacterEncodingFilter

encoding

utf-

CharacterEncodingFilter

/*

xmlns:xsi="http://w.org//XMLSchema-instance"

xmlns:p="http://springframework.org/schema/p"

xmlns:context="http://springframework.org/schema/context"

xmlns:aop="http://sprihttp://ngframework.org/schema/aop"

xmlns:tx="http://springframework.org/schema/tx"

xmlns:mvc="http://springframework.org/schema/mvc"

xmlns:util="http://springframework.org/schema/util"

xsi:schemaLocation="http://springframework.org/schema/beans http://springframework.org/schema/beans/spring-beans-..xsd

http://springframework.org/schema/context http://springframework.org/schema/context/spring-context-..xsd

http://springframework.org/schema/mvc http://springframework.org/schema/mvc/spring-mvc-..xsd

http://springframework.org/schema/aop http://springframework.org/schema/aop/spring-aop-..xsd">

xmlns:xsi="http://w.org//XMLSchema-instance"

xmlns:p="http://springframework.org/schema/p"

xmlns:context="http://springframework.org/schema/context"

xmlns:aop="http://sprihttp://ngframework.org/schema/aop"

xmlns:tx="http://springframework.org/schema/tx"

xmlns:mvc="http://springframework.org/schema/mvc"

xmlns:util="http://springframework.org/schema/util"

xsi:schemaLocation="http://springframework.org/schema/beans http://springframework.org/schema/beans/spring-beans-..xsd

http://springframework.org/schema/context http://springframework.org/schema/context/spring-context-..xsd

http://springframework.org/schema/mvc http://springframework.org/schema/mvc/spring-mvc-..xsd

http://springframework.org/schema/aop http://springframework.org/schema/aop/spring-aop-..xsd">

xmlns:xsi="http://w.org//XMLSchema-instance"

xmlns:context="http://springframework.org/schema/context"

xsi:schemaLocation="http://springframework.org/schema/beans http://springframework.org/schema/beans/spring-beans-..xsd

http://springframework.org/schema/context http://springframework.org/schema/context/spring-context-..xsd">

xmlns:xsi="http://w.org//XMLSchema-instance"

xmlns:context="http://springframework.org/schema/context"

xsi:schemaLocation="http://springframework.org/schema/beans http://springframework.org/schema/beans/spring-beans-..xsd

http://springframework.org/schema/context http://springframework.org/schema/context/spring-context-..xsd">

xmlns:xsi="http://w.org//XMLSchema-instance" xmlns:context="http://springframework.org/schema/context"

xsi:schemaLocation="

http://springframework.org/schema/beans

http://springframework.org/schema/beans/spring-beans.xsd

http://springframework.org/schema/context

http://springframework.org/schema/context/spring-context.xsd">

xmlns:xsi="http://w.org//XMLSchema-instance" xmlns:context="http://springframework.org/schema/context"

xsi:schemaLocation="

http://springframework.org/schema/beans

http://springframework.org/schema/beans/spring-beans.xsd

http://springframework.org/schema/context

http://springframework.org/schema/context/spring-context.xsd">

jdbc.driverClass=com.mysql.jdbc.Driver

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

jdbc.user=myuser

jdbc.password=myuser

5.目录结构：

6.结果演示

导入：

导出：

PS:

1.本獂新手，由于还没清楚怎么添加附件，故将所有代码贴出并加上目录结构，日后了解怎么添加附件，再修改。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。