详解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"%>
 
 
 
   
   成功页面
   
   
 
 
   
     导入成功！
     
Id：${secUser.userId} | Name：${secUser.userName} | Password：${secUser.userPassword}
     
   
   
     导出成功！

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 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 {
    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();
  }
}
     }
     
   }
 
   
   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("密码");  
   }
 
   
   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 paramsList = new ArrayList();
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.配置文件：

 
 
   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
     /*

 jdbc.driverClass=com.mysql.jdbc.Driver
 jdbc.jdbcUrl=jdbc:mysql://localhost:/mydb
 jdbc.user=myuser
 jdbc.password=myuser

5.目录结构：

6.结果演示

导入：

导出：

PS:

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

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持考高分网。

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

Java相关栏目本月热门文章