最近用 POI 做了个 导入 导出,所以自己就搭了个 SpringMVC + JDBC + POI 的简单框架,把过程 记录一下,方便回忆
demo框架: SpringMVC + JDBC 数据库:mysql
我们 侧重点在 表格导入导出,关于框架部分 不在描述
直接贴代码:
页面部分:show.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions"%>
<c:set var="baseurl" value="${pageContext.request.contextPath}" />
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<script type="text/javascript" src="${baseurl }/js/jquery.js"></script>
<script type="text/javascript" src="${baseurl }/js/jquery.form.js"></script>
<title>demo</title>
</head>
<body>
<form method="post" action="<%=basePath%>/excel/exportExcel" data-validate-type="server">
<input type="submit" value="导出Excel">
</form>
<form id="file_form" action="<%=basePath%>/excel/importExcel" enctype="multipart/form-data"
method="post">
<input type="file" name="file" id="file_input" />
<input type="submit" value="文件上传" id='upFile-btn'>
</form>
<script type="text/javascript">
$(function() {
$("#file_form").submit(
function() {
//首先验证文件格式
var fileName = $('#file_input').val();
if (fileName === '') {
alert('请选择文件');
return false;
}
var fileType = (fileName.substring(fileName
.lastIndexOf(".") + 1, fileName.length))
.toLowerCase();
if (fileType !== 'xls' && fileType !== 'xlsx') {
alert('文件格式不正确,excel文件!');
return false;
}
$("#file_form").ajaxSubmit({
// dataType : "json",
success : function(data) {
if (data === 'OK') {
console.log('上传文件成功');
} else {
console.log('文件格式错误');
}
return false;
},
error : function(){
alert("error....");
return false;
}
});
return false;
});
});
</script>
</body>
</html>
controller 部分:
package com.demo.main.excel.controller;
import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import com.demo.main.excel.service.ExcelService;
import com.demo.main.util.WebUtil;
@Controller
@RequestMapping("/excel")
public class ExcelController {
// 引入 service 层
@Autowired
private ExcelService excelService;
@RequestMapping("/show")
public String main(){
System.out.println("main...");
return "show";
}
// excel 表格导出
@RequestMapping("/exportExcel")
public String exportExcel(HttpServletRequest request,HttpServletResponse response){
OutputStream os = null;
HSSFWorkbook wb = excelService.writeExcel();
String fileName = "部门名单.xls";
response.setContentType("application/vnd.ms-excel");
try {
response.setHeader("Content-disposition", "attachment;filename="+ URLEncoder.encode(fileName, "utf-8"));
os = response.getOutputStream();
wb.write(os);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
@RequestMapping("/importExcel")
@ResponseBody
public String importExcel(HttpServletRequest request,HttpServletResponse response){
PrintWriter out = null;
try {
out = response.getWriter();
String msg = excelService.importExcel(request, response);
out.write(msg);
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
@RequestMapping("/downLoadFile")
public String downLoadFile(HttpServletRequest request,HttpServletResponse response){
try {
//baseUrl:E:\gyy\local_tomcat_7.0.53\webapps\demo
String path = WebUtil.getBaseUrl()+File.separator+"downloads"+File.separator+"botnet"+File.separator+"CCBlack.xls";
excelService.dowmloads(path, response, request );
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
}
service 部分:
package com.demo.main.excel.service;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.net.URL;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileItemFactory;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.commons.fileupload.util.mime.MimeUtility;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.demo.main.excel.dao.PeopleDao;
import com.demo.main.excel.data.AllPeople;
@Service("excelService")
public class ExcelService {
@Autowired
private PeopleDao peopleDao;// = new PeopleDao()
// peopleDao = new PeoPleDao();
public HSSFWorkbook writeExcel(){
HSSFWorkbook wook = new HSSFWorkbook();
HSSFSheet sheet=wook.createSheet("部门名单");
CreationHelper createHelper=wook.getCreationHelper();
HSSFCellStyle style=wook.createCellStyle();
//在sheet中创建一行
HSSFRow rowTop=sheet.createRow(0);
HSSFCell createCell = rowTop.createCell(0);
createCell.setCellValue("部门名额");
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
createCell.setCellStyle(style);
//合并单元格CellRangeAddress构造参数依次表示起始行,截至行,起始列, 截至列
sheet.addMergedRegion(new CellRangeAddress(0,0,0,2));
HSSFRow row=sheet.createRow(1);
//在该行写入各种类型的数据
row.createCell(0).setCellValue("序号");
row.createCell(1).setCellValue("姓名");
row.createCell(2).setCellValue("时间");
// 遍历数据库 取出数据,放入表格里
//在写入 日期格式的 数据需要进行特殊处理(这是一种 简单的处理方式)
style.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd"));
int rowNum = 1;
List<AllPeople> peopleList = peopleDao.queryList(0);
for (AllPeople allPeople : peopleList) {
rowNum ++;
HSSFRow rowInsert=sheet.createRow(rowNum);
rowInsert.createCell(0).setCellValue(allPeople.getId());
rowInsert.createCell(1).setCellValue(allPeople.getName());
HSSFCell cell = rowInsert.createCell(2);
cell.setCellValue(new Date());
cell.setCellStyle(style);
}
return wook;
}
/**
* 从页面上传 Excel 表格
* @param request
* @param response
*/
public String importExcel(HttpServletRequest request,HttpServletResponse response){
response.setCharacterEncoding("utf-8");
// response.setContentType("application/json");
try {
FileItemFactory factory = new DiskFileItemFactory();
ServletFileUpload upload = new ServletFileUpload(factory);
upload.setHeaderEncoding(request.getCharacterEncoding());
List<FileItem> list = upload.parseRequest(request);
// 暂时 只读取一个 sheet
for (int i = 0; i <1; i++) {
FileItem item = list.get(0);
if (item.getName().endsWith(".xls")||item.getName().endsWith(".xlsx")) {
List<AllPeople> excelList = getExcelList(item.getInputStream());
peopleDao.insertData(excelList);
return "ok";
} else {
return "false";
}
}
} catch (Exception e) {
e.printStackTrace();
return "false";
}
return "false";
}
/**
* 下载模板
*/
public void dowmloads(String filepath, HttpServletResponse response,
HttpServletRequest request) throws IOException {
File file = new File(filepath);
if (!(file.exists())) {
response.sendError(404);
return;
}
BufferedInputStream bis = new BufferedInputStream(new FileInputStream(
file));
byte[] buy = new byte[1024];
response.reset();
//1.设置文件ContentType类型,这样设置,会自动判断下载文件类型
response.setContentType("multipart/form-data");
//2.设置文件头:最后一个参数是设置下载文件名(假如我们叫a.pdf)
response.setHeader("Content-Disposition", "attachment;fileName="+ file.getName());
OutputStream ops = response.getOutputStream();
int len;
while ((len = bis.read(buy)) > 0) {
ops.write(buy, 0, len);
}
bis.close();
ops.close();
ops.flush();
}
/***
* 这种方法支持03,和07版本的excel读取
* 但是对于合并的单元格,除了第一行第一列之外,其他部分读取的值为空
* @param is
* @throws IOException
* @throws Exception
*/
public List<AllPeople> getExcelList(InputStream is) throws Exception {
ArrayList<AllPeople> peopleList = new ArrayList<AllPeople>();
Workbook wb = WorkbookFactory.create(is);
int sheetNumber = wb.getNumberOfSheets();
// 所有的 sheet
for (int i = 0; i < sheetNumber; i++) {
Sheet sheet = wb.getSheetAt(i);
// 跳过表头
for (int j = 2; j <= sheet.getLastRowNum(); j++) {
Row row = sheet.getRow(j);
AllPeople people = new AllPeople();
// 读取每一个单元格
people.setId(Integer.parseInt(getValue(row.getCell(0))));//.split(".")[0]
people.setName(getValue(row.getCell(1)));
people.setSelectTime(getValue(row.getCell(2)));
peopleList.add(people);
}
}
return peopleList;
}
/**
* 获取单元格内容
*
* @param cell
* @return
*/
public static String getValue(Cell cell) {
String cellValue = "";
if (cell == null)
return cellValue;
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
cellValue = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
return cellValue;
} else {
double strCell = cell.getNumericCellValue();
DecimalFormat formatCell = (DecimalFormat) NumberFormat.getPercentInstance();
formatCell.applyPattern("0");
cellValue = formatCell.format(strCell);
if (Double.parseDouble(cellValue) != strCell) {
formatCell.applyPattern("0.00");
cellValue = formatCell.format(strCell);
}
return cellValue;
}
case HSSFCell.CELL_TYPE_STRING:
cellValue = cell.getStringCellValue();
return cellValue.trim();
case HSSFCell.CELL_TYPE_FORMULA:
cellValue = cell.getCellFormula();
return cellValue.trim();
case HSSFCell.CELL_TYPE_BLANK:
return cellValue.trim();
case HSSFCell.CELL_TYPE_BOOLEAN:
cellValue = cell.getBooleanCellValue() + "";
return cellValue.trim();
case HSSFCell.CELL_TYPE_ERROR:
cellValue = cell.getErrorCellValue() + "";
return cellValue.trim();
default:
return cellValue.trim();
}
}
public static void main(String[] args) {
new ExcelService().writeExcel();
}
}
dao 层 部分:
package com.demo.main.excel.dao;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Random;
import org.springframework.stereotype.Repository;
import com.demo.main.excel.data.AllPeople;
import com.demo.main.util.JdbcUtil;
import com.mysql.jdbc.PreparedStatement;
@Repository("peopleDao")
public class PeopleDao {
/**
* 初始化数据 导入,把图片数据(all 中) 全部导入 数据库
* @param allPepple
*/
public void initData(List<String> allPepple){
Connection con = JdbcUtil.getConnection();
try {
Statement statemenet = con.createStatement();
if(allPepple != null && allPepple.size() > 0){
for (String name : allPepple) {
String query = "insert into allpeople (name, isSelected) values('"
+ name + "','" + 0 + "')";
statemenet.addBatch(query);
}
}
int[] number = statemenet.executeBatch();
System.out.println(" 共插入 数据:"+number.length +"条!");
statemenet.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
//JdbcUtil.close(con);
}
}
/**
* 初始化数据 导入,把图片数据(all 中) 全部导入 数据库
* @param allPepple
*/
public void insertData(List<AllPeople> peppleList){
Connection con = JdbcUtil.getConnection();
try {
Statement statemenet = con.createStatement();
if(peppleList != null && peppleList.size() > 0){
for (AllPeople people : peppleList) {
String query = "insert into allpeople (name, isSelected,selectTime) values('"
+ people.getName() + "','" +people.getIsSelect() + "','" + people.getSelectTime() + "')";
statemenet.addBatch(query);
}
}
int[] number = statemenet.executeBatch();
System.out.println(" 共插入 数据:"+number.length +"条!");
statemenet.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 抽中后 ,更新状态
* @param name
*/
public void updateInfo(String name ,int isSelected){
Connection con = JdbcUtil.getConnection();
String insertTime ="";
if(isSelected == 1){
SimpleDateFormat now = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String nowTime = now.format( new Date() );
insertTime = nowTime;
}
String sql ="update allpeople SET selectTime = '"+insertTime+"',isSelected = '" + isSelected + "' where name='" + name + "'";
PreparedStatement pstmt;
try {
pstmt = (PreparedStatement) con.prepareStatement(sql);
pstmt.executeUpdate();
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
//JdbcUtil.close(con);
}
}
/**
*
* @param isSelected 1: 选择 0:没有选中
* @return
*/
public List<AllPeople> queryList(int isSelected){
Connection con = JdbcUtil.getConnection();
List<AllPeople> list = new ArrayList<AllPeople>();
String sql = "select * from allpeople where isSelected = ?";
PreparedStatement pstmt;
try {
//根据sql语句创建预处理对象
pstmt = (PreparedStatement) con.prepareStatement(sql);
pstmt.setObject(1, isSelected);
//执行更新
ResultSet rs = pstmt.executeQuery();
while(rs.next()){
AllPeople people = new AllPeople();
String name=rs.getString("name");
int id=rs.getInt("id");
people.setId(id);
people.setName(name);
list.add(people);
}
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
public void clearData(){
Connection con = JdbcUtil.getConnection();
try {
Statement sta = con.createStatement();
String sql = "TRUNCATE TABLE allpeople";
sta.execute(sql);
sta.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
实体类部分:
package com.demo.main.excel.data;
public class AllPeople {
private int id;
private String name;
private int isSelect;
private String selectTime;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getIsSelect() {
return isSelect;
}
public void setIsSelect(int isSelect) {
this.isSelect = isSelect;
}
public String getSelectTime() {
return selectTime;
}
public void setSelectTime(String selectTime) {
this.selectTime = selectTime;
}
}