社区微信群开通啦，扫一扫抢先加入社区官方微信群
社区微信群
上一篇博文介绍了poi操作excel的基本读写操作后，接下来，介绍一下在项目中的实际用途：批量导入、批量导出功能。因为重点知识介绍批量导入导出excel功能，故而项目整体的搭建后台用jdbc与struts2，前端页面用jquery-easyui实现（其实也可以整合到ssm或者ssh中，而这一点现在我已经实现了，即基于SSM的博客以及视频教程我已经发布在我最新的博客中，欢迎观看，其中，视频教程地址：https://edu.csdn.net/course/detail/8894 欢迎支持！！）。
首先，看一下，项目的整体结构图：
首先，当然是放入jar包啦，可以来这我这里下载：poi批量导入导出的jar包
加入jquery-easyui-1.3.3，可以到easyui官网下载，配置web.xml:
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="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_2_5.xsd"
id="WebApp_ID" version="2.5">
<display-name>PoiDemo</display-name>
<welcome-file-list>
<welcome-file>index.htm</welcome-file>
</welcome-file-list>
<filter>
<filter-name>StrutsPrepareAndExecuteFilter</filter-name>
<filter-class>org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter
</filter-class>
</filter>
<filter-mapping>
<filter-name>StrutsPrepareAndExecuteFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
</web-app>
src目录下建立各个包，具体我就不说了，看上面的图即可。新建struts.xml：
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE struts PUBLIC
"-//Apache Software Foundation//DTD Struts Configuration 2.3//EN"
"http://struts.apache.org/dtds/struts-2.3.dtd">
<struts>
<package name="userInfo" namespace="/" extends="struts-default">
<action name="user" class="com.steadyjack.action.UserAction">
</action>
</package>
</struts>
接下来，介绍com.steadyjack.util下的各个工具类，有一些比较简单，我就不详细说了，注释写得很清楚了！
DateUtil.java：
package com.steadyjack.util;
import java.text.SimpleDateFormat;
import java.util.Date;
/**
* 简单日期处理工具
* @author 钟林森
*
*/
public class DateUtil {
public static String formatDate(Date date,String format){
String result="";
SimpleDateFormat sdf=new SimpleDateFormat(format);
if(date!=null){
result=sdf.format(date);
}
return result;
}
public static Date formatString(String str,String format) throws Exception{
SimpleDateFormat sdf=new SimpleDateFormat(format);
return sdf.parse(str);
}
public static void main(String[] args) throws Exception{
Date date=formatString("1993/10/12", "yyyy/MM/dd");
String str=formatDate(date, "yyyy-MM-dd");
System.out.println(str);
}
}
DbUtil.java：
package com.steadyjack.util;
import java.sql.Connection;
import java.sql.DriverManager;
/**
* 数据库链接工具
* @author 钟林森
*
*/
public class DbUtil {
private String dbUrl="jdbc:mysql://localhost:3306/db_poi";
private String dbUserName="root";
private String dbPassword="123456";
private String jdbcName="com.mysql.jdbc.Driver";
public Connection getCon()throws Exception{
Class.forName(jdbcName);
Connection con=DriverManager.getConnection(dbUrl,dbUserName,dbPassword);
return con;
}
public void closeCon(Connection con)throws Exception{
if(con!=null){
con.close();
}
}
}
excel导入导出工具类，这个很重要，ExcelUtil.java：
package com.steadyjack.util;
import java.io.InputStream;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
/**
* Excel文件处理工具类: 包括填充数据到普通excel、模板excel文件,单元格格式处理
* @author 钟林森
*
*/
public class ExcelUtil {
/**
* 填充数据到普通的excel文件中
* @param rs
* @param wb
* @param headers
* @throws Exception
*/
public static void fillExcelData(ResultSet rs,Workbook wb,String[] headers)throws Exception{
Sheet sheet=wb.createSheet();
Row row=sheet.createRow(0);
//先填充行头 : "编号","姓名","电话","Email","QQ","出生日期"
for(int i=0;i<headers.length;i++){
row.createCell(i).setCellValue(headers[i]);
}
//再填充数据
int rowIndex=1;
while(rs.next()){
row=sheet.createRow(rowIndex++);
for(int i=0;i<headers.length;i++){
Object objVal=rs.getObject(i+1);
if (objVal instanceof Date) {
row.createCell(i).setCellValue(DateUtil.formatDate((Date)objVal,"yyyy-MM-dd"));
}else{
row.createCell(i).setCellValue(objVal.toString());
}
}
}
}
/**
* 填充数据到模板excel文件
* @param rs
* @param templateFileName
* @return
* @throws Exception
*/
public static Workbook fillExcelDataWithTemplate(ResultSet rs,String templateFileName)throws Exception{
//首先:从本地磁盘读取模板excel文件,然后读取第一个sheet
InputStream inp=ExcelUtil.class.getResourceAsStream("/com/steadyjack/template/"+templateFileName);
POIFSFileSystem fs=new POIFSFileSystem(inp);
Workbook wb=new HSSFWorkbook(fs);
Sheet sheet=wb.getSheetAt(0);
//开始写入数据到模板中: 需要注意的是,因为行头以及设置好,故而需要跳过行头
int cellNums=sheet.getRow(0).getLastCellNum();
int rowIndex=1;
while(rs.next()){
Row row=sheet.createRow(rowIndex++);
for(int i=0;i<cellNums;i++){
Object objVal=rs.getObject(i+1);
if (objVal instanceof Date) {
row.createCell(i).setCellValue(DateUtil.formatDate((Date)objVal,"yyyy-MM-dd"));
}else{
row.createCell(i).setCellValue(objVal.toString());
}
}
}
return wb;
}
/**
* 处理单元格格式的简单方式
* @param hssfCell
* @return
*/
public static String formatCell(HSSFCell hssfCell){
if(hssfCell==null){
return "";
}else{
if(hssfCell.getCellType()==HSSFCell.CELL_TYPE_BOOLEAN){
return String.valueOf(hssfCell.getBooleanCellValue());
}else if(hssfCell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC){
return String.valueOf(hssfCell.getNumericCellValue());
}else{
return String.valueOf(hssfCell.getStringCellValue());
}
}
}
/**
* 处理单元格格式的第二种方式: 包括如何对单元格内容是日期的处理
* @param cell
* @return
*/
public static String formatCell2(HSSFCell cell) {
if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
//针对单元格式为日期格式
if (HSSFDateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
}
return String.valueOf(cell.getNumericCellValue());
} else {
return cell.getStringCellValue();
}
}
/**
* 处理单元格格式的第三种方法:比较全面
* @param cell
* @return
*/
public static String formatCell3(HSSFCell cell) {
if (cell == null) {
return "";
}
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
//日期格式的处理
if (HSSFDateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
}
return String.valueOf(cell.getNumericCellValue());
//字符串
case HSSFCell.CELL_TYPE_STRING:
return cell.getStringCellValue();
// 公式
case HSSFCell.CELL_TYPE_FORMULA:
return cell.getCellFormula();
// 空白
case HSSFCell.CELL_TYPE_BLANK:
return "";
// 布尔取值
case HSSFCell.CELL_TYPE_BOOLEAN:
return cell.getBooleanCellValue() + "";
//错误类型
case HSSFCell.CELL_TYPE_ERROR:
return cell.getErrorCellValue() + "";
}
return "";
}
}
将jdbc查询得到的ResultSet转为JsonArray工具类JsonUtil.java：
package com.steadyjack.util;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Date;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
/**
* jdbc 的结果集ResultSet转化为JsonArray工具
* @author 钟林森
*
*/
public class JsonUtil {
/**
* 把ResultSet集合转换成JsonArray数组
* @param rs
* @return
* @throws Exception
*/
public static JSONArray formatRsToJsonArray(ResultSet rs)throws Exception{
ResultSetMetaData md=rs.getMetaData();
int num=md.getColumnCount();
JSONArray array=new JSONArray();
while(rs.next()){
JSONObject mapOfColValues=new JSONObject();
for(int i=1;i<=num;i++){
Object strVal=rs.getObject(i);
if (strVal instanceof Date) {
mapOfColValues.put(md.getColumnName(i),DateUtil.formatDate((Date)strVal,"yyyy-MM-dd"));
}else{
mapOfColValues.put(md.getColumnName(i),strVal);
}
}
array.add(mapOfColValues);
}
return array;
}
}
ResponseUtil.java：
package com.steadyjack.util;
import java.io.OutputStream;
import java.io.PrintWriter;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.Workbook;
/**
* 将数据写回页面 jquery-ajax交互工具类
* @author 钟林森
*
*/
public class ResponseUtil {
/**
* 将数据写回页面 用于jquery-ajax的异步交互
* @param response
* @param o
* @throws Exception
*/
public static void write(HttpServletResponse response,Object o)throws Exception{
response.setContentType("text/html;charset=utf-8");
PrintWriter out=response.getWriter();
out.print(o.toString());
out.flush();
out.close();
}
/**
* 将excel文件写回客户端浏览器 用于下载
* @param response
* @param wb
* @param fileName
* @throws Exception
*/
public static void export(HttpServletResponse response,Workbook wb,String fileName)throws Exception{
response.setHeader("Content-Disposition", "attachment;filename="+new String(fileName.getBytes("utf-8"),"iso8859-1"));
response.setContentType("application/ynd.ms-excel;charset=UTF-8");
OutputStream out=response.getOutputStream();
wb.write(out);
out.flush();
out.close();
}
}
StringUtil.java：
package com.steadyjack.util;
/**
* 简单字符串处理工具
* @author 钟林森
*
*/
public class StringUtil {
public static boolean isEmpty(String str){
if("".equals(str)||str==null){
return true;
}else{
return false;
}
}
public static boolean isNotEmpty(String str){
if(!"".equals(str)&&str!=null){
return true;
}else{
return false;
}
}
}
接下来，是com.steadyjack.model中的User与PageBean：
package com.steadyjack.model;
import java.util.Date;
public class User {
private int id;
private String name;
private String phone;
private String email;
private String qq;
private Date birth;
public User() {
}
public User(String name, String phone, String email, String qq) {
this.name = name;
this.phone = phone;
this.email = email;
this.qq = qq;
}
public User(String name, String phone, String email, String qq, Date birth) {
super();
this.name = name;
this.phone = phone;
this.email = email;
this.qq = qq;
this.birth = birth;
}
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 String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getQq() {
return qq;
}
public void setQq(String qq) {
this.qq = qq;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public Date getBirth() {
return birth;
}
public void setBirth(Date birth) {
this.birth = birth;
}
}
package com.steadyjack.model;
public class PageBean {
private int page; // 第几页
private int rows; // 每页的记录数
private int start; // 起始页
public PageBean(int page, int rows) {
super();
this.page = page;
this.rows = rows;
}
public int getPage() {
return page;
}
public void setPage(int page) {
this.page = page;
}
public int getRows() {
return rows;
}
public void setRows(int rows) {
this.rows = rows;
}
public int getStart() {
return (page-1)*rows;
}
}
接下来是UserDao（其实，也开发了增删改查的功能，但在这里就不贴出来了，有意者可以加上面的qq联系）
package com.steadyjack.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import com.steadyjack.model.PageBean;
import com.steadyjack.model.User;
public class UserDao {
public ResultSet userList(Connection con,PageBean pageBean)throws Exception{
StringBuffer sb=new StringBuffer("select * from t_user");
if(pageBean!=null){
sb.append(" limit ?,?");
}
PreparedStatement pstmt=con.prepareStatement(sb.toString());
if(pageBean!=null){
pstmt.setInt(1, pageBean.getStart());
pstmt.setInt(2, pageBean.getRows());
}
return pstmt.executeQuery();
}
public int userCount(Connection con)throws Exception{
String sql="select count(*) as total from t_user";
PreparedStatement pstmt=con.prepareStatement(sql);
ResultSet rs=pstmt.executeQuery();
if(rs.next()){
return rs.getInt("total");
}else{
return 0;
}
}
public int userAdd(Connection con,User user)throws Exception{
String sql="insert into t_user values(null,?,?,?,?,?)";
PreparedStatement pstmt=con.prepareStatement(sql);
pstmt.setString(1, user.getName());
pstmt.setString(2, user.getPhone());
pstmt.setString(3, user.getEmail());
pstmt.setString(4, user.getQq());
//java.util.date转为 java.sql.date
pstmt.setDate(5, new java.sql.Date(user.getBirth() .getTime()));
return pstmt.executeUpdate();
}
}
然后注意com.steadyjack.template下有个 “用户模板文件.xls”，这个可以自己制作：
最后是重头戏了UserAction.java：
package com.steadyjack.action;
import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
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.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.struts2.ServletActionContext;
import com.opensymphony.xwork2.ActionSupport;
import com.steadyjack.dao.UserDao;
import com.steadyjack.model.PageBean;
import com.steadyjack.model.User;
import com.steadyjack.util.DateUtil;
import com.steadyjack.util.DbUtil;
import com.steadyjack.util.ExcelUtil;
import com.steadyjack.util.JsonUtil;
import com.steadyjack.util.ResponseUtil;
import com.steadyjack.util.StringUtil;
public class UserAction extends ActionSupport {
/**
*
*/
private static final long serialVersionUID = 1L;
private String page;
private String rows;
private String id;
private User user;
private String delId;
private File userUploadFile;
public String getPage() {
return page;
}
public void setPage(String page) {
this.page = page;
}
public String getRows() {
return rows;
}
public void setRows(String rows) {
this.rows = rows;
}
public String getDelId() {
return delId;
}
public void setDelId(String delId) {
this.delId = delId;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public File getUserUploadFile() {
return userUploadFile;
}
public void setUserUploadFile(File userUploadFile) {
this.userUploadFile = userUploadFile;
}
DbUtil dbUtil=new DbUtil();
UserDao userDao=new UserDao();
//获取用户列表
public String list()throws Exception{
Connection con=null;
PageBean pageBean=new PageBean(Integer.parseInt(page),Integer.parseInt(rows));
try{
con=dbUtil.getCon();
JSONObject result=new JSONObject();
JSONArray jsonArray=JsonUtil.formatRsToJsonArray(userDao.userList(con, pageBean));
int total=userDao.userCount(con);
result.put("rows", jsonArray);
result.put("total", total);
ResponseUtil.write(ServletActionContext.getResponse(),result);
}catch(Exception e){
e.printStackTrace();
}finally{
try {
dbUtil.closeCon(con);
} catch (Exception e) {
e.printStackTrace();
}
}
return null;
}
//导出用户 : 普通excel导出
public String export()throws Exception{
Connection con=null;
try {
con=dbUtil.getCon();
Workbook wb=new HSSFWorkbook();
String headers[]={"编号","姓名","电话","Email","QQ","出生日期"};
ResultSet rs=userDao.userList(con, null);
ExcelUtil.fillExcelData(rs, wb, headers);
ResponseUtil.export(ServletActionContext.getResponse(), wb, "用户excel表.xls");
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
dbUtil.closeCon(con);
} catch (Exception e) {
e.printStackTrace();
}
}
return null;
}
//用户导出 : 采用预先设置好的excel模板文件进行导出
public String export2()throws Exception{
Connection con=null;
try {
con=dbUtil.getCon();
ResultSet rs=userDao.userList(con, null);
Workbook wb=ExcelUtil.fillExcelDataWithTemplate(rs, "用户模板文件.xls");
ResponseUtil.export(ServletActionContext.getResponse(), wb, "利用模版导出用户excel表.xls");
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
dbUtil.closeCon(con);
} catch (Exception e) {
e.printStackTrace();
}
}
return null;
}
//excel文件导入,批量导入数据
public String upload()throws Exception{
//此时的Workbook应该是从 客户端浏览器上传过来的 uploadFile了,其实跟读取本地磁盘的一个样
POIFSFileSystem fs=new POIFSFileSystem(new FileInputStream(userUploadFile));
HSSFWorkbook wb=new HSSFWorkbook(fs);
HSSFSheet hssfSheet=wb.getSheetAt(0);
if(hssfSheet!=null){
//遍历excel,从第二行开始 即 rowNum=1,逐个获取单元格的内容,然后进行格式处理,最后插入数据库
for(int rowNum=1;rowNum<=hssfSheet.getLastRowNum();rowNum++){
HSSFRow hssfRow=hssfSheet.getRow(rowNum);
if(hssfRow==null){
continue;
}
User user=new User();
user.setName(ExcelUtil.formatCell(hssfRow.getCell(0)));
user.setPhone(ExcelUtil.formatCell(hssfRow.getCell(1)));
user.setEmail(ExcelUtil.formatCell(hssfRow.getCell(2)));
user.setQq(ExcelUtil.formatCell(hssfRow.getCell(3)));
//对于单元格日期需要进行特殊处理
user.setBirth(DateUtil.formatString(ExcelUtil.formatCell2(hssfRow.getCell(4)), "yyyy-MM-dd"));
Connection con=null;
try{
con=dbUtil.getCon();
userDao.userAdd(con, user);
}catch(Exception e){
e.printStackTrace();
}finally{
dbUtil.closeCon(con);
}
}
}
JSONObject result=new JSONObject();
result.put("success", "true");
ResponseUtil.write(ServletActionContext.getResponse(), result);
return null;
}
}
最后有一个WebContent目录下template文件夹有个userTemplateFile.xls，跟“用户模板文件.xls”是一模一样的，只是换个名字而已。
最后当然是页面了crud1.html：
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>poi操作excel</title>
<link rel="stylesheet" type="text/css" href="jquery-easyui-1.3.3/themes/default/easyui.css">
<link rel="stylesheet" type="text/css" href="jquery-easyui-1.3.3/themes/icon.css">
<script type="text/javascript" src="jquery-easyui-1.3.3/jquery.min.js"></script>
<script type="text/javascript" src="jquery-easyui-1.3.3/jquery.easyui.min.js"></script>
<script type="text/javascript" src="jquery-easyui-1.3.3/locale/easyui-lang-zh_CN.js"></script>
<script>
var url;
function deleteUser(){
var row=$('#dg').datagrid('getSelected');
if(row){
$.messager.confirm("系统提示","您确定要删除这条记录吗?",function(r){
if(r){
$.post('user!delete',{delId:row.id},function(result){
if(result.success){
$.messager.alert("系统提示","已成功删除这条记录!");
$("#dg").datagrid("reload");
}else{
$.messager.alert("系统提示",result.errorMsg);
}
},'json');
}
});
}
}
function newUser(){
$("#dlg").dialog('open').dialog('setTitle','添加用户');
$('#fm').form('clear');
url='user!save';
}
function editUser(){
var row=$('#dg').datagrid('getSelected');
if(row){
$("#dlg").dialog('open').dialog('setTitle','编辑用户');
$("#name").val(row.name);
$("#phone").val(row.phone);
$("#email").val(row.email);
$("#qq").val(row.qq);
url='user!save?id='+row.id;
}
}
function saveUser(){
$('#fm').form('submit',{
url:url,
onSubmit:function(){
return $(this).form('validate');
},
success:function(result){
var result=eval('('+result+')');
if(result.errorMsg){
$.messager.alert("系统提示",result.errorMsg);
return;
}else{
$.messager.alert("系统提示","保存成功");
$('#dlg').dialog('close');
$("#dg").datagrid("reload");
}
}
});
}
function exportUser(){
window.open('user!export');
}
function exportUser2(){
window.open('user!export2');
}
function openUploadFileDialog(){
$("#dlg2").dialog('open').dialog('setTitle','批量导入数据');
}
function downloadTemplate(){
window.open('template/userTemplateFile.xls');
}
function uploadFile(){
$("#uploadForm").form("submit",{
success:function(result){
var result=eval('('+result+')');
if(result.errorMsg){
$.messager.alert("系统提示",result.errorMsg);
}else{
$.messager.alert("系统提示","上传成功");
$("#dlg2").dialog("close");
$("#dg").datagrid("reload");
}
}
});
}
</script>
</head>
<body>
<table id="dg" title="用户管理" class="easyui-datagrid" style="width:700px;height:365px"
url="user!list"
toolbar="#toolbar" pagination="true"
rownumbers="true" fitColumns="true" singleSelect="true">
<thead>
<tr>
<th field="id" width="50" hidden="true">编号</th>
<th field="name" width="50">姓名</th>
<th field="phone" width="50">电话</th>
<th field="email" width="50">Email</th>
<th field="qq" width="50">QQ</th>
<th field="birth" width="50">出生日期</th>
</tr>
</thead>
</table>
<div id="toolbar">
<a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-add" plain="true" οnclick="newUser()">添加用户</a>
<a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-edit" plain="true" οnclick="editUser()">编辑用户</a>
<a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-remove" plain="true" οnclick="deleteUser()">删除用户</a>
<a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-export" plain="true" οnclick="exportUser()">导出用户</a>
<a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-export" plain="true" οnclick="exportUser2()">用模版导出用户</a>
<a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-import" plain="true" οnclick="openUploadFileDialog()">用模版批量导入数据</a>
</div>
<div id="dlg" class="easyui-dialog" style="width:400px;height:250px;padding:10px 20px"
closed="true" buttons="#dlg-buttons">
<form id="fm" method="post">
<table cellspacing="10px;">
<tr>
<td>姓名：</td>
<td><input id="name" name="user.name" class="easyui-validatebox" required="true" style="width: 200px;"></td>
</tr>
<tr>
<td>联系电话：</td>
<td><input id="phone" name="user.phone" class="easyui-validatebox" required="true" style="width: 200px;"></td>
</tr>
<tr>
<td>Email：</td>
<td><input id="email" name="user.email" class="easyui-validatebox" validType="email" required="true" style="width: 200px;"></td>
</tr>
<tr>
<td>QQ：</td>
<td><input id="qq" name="user.qq" class="easyui-validatebox" required="true" style="width: 200px;"></td>
</tr>
<tr>
<td>出生日期：</td>
<td><input id="birth" name="user.birth" class="easyui-validatebox" required="true" style="width: 200px;"></td>
</tr>
</table>
</form>
</div>
<div id="dlg-buttons">
<a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-ok" οnclick="saveUser()">保存</a>
<a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-cancel" οnclick="javascript:$('#dlg').dialog('close')">关闭</a>
</div>
<div id="dlg2" class="easyui-dialog" style="width:400px;height:180px;padding:10px 20px"
closed="true" buttons="#dlg-buttons2">
<form id="uploadForm" action="user!upload" method="post" enctype="multipart/form-data">
<table>
<tr>
<td>下载模版：</td>
<td><a href="javascript:void(0)" class="easyui-linkbutton" οnclick="downloadTemplate()">下载模板文件</a></td>
</tr>
<tr>
<td>上传文件：</td>
<td><input type="file" name="userUploadFile"></td>
</tr>
</table>
</form>
</div>
<div id="dlg-buttons2">
<a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-ok" οnclick="uploadFile()">上传</a>
<a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-cancel" οnclick="javascript:$('#dlg2').dialog('close')">关闭</a>
</div>
</body>
</html>
其中，添加、删除、修改的功能已经实现了，但代码我就不贴出来了，因为我重点是介绍批量导入导出excel。
当然啦，需要新建一个数据库db_poi，新建一个数据库表t_user：
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`phone` varchar(20) DEFAULT NULL,
`email` varchar(20) DEFAULT NULL,
`qq` varchar(20) DEFAULT NULL,
`birth` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8;
INSERT INTO `t_user` VALUES ('51', '钟林森', '12121212', 'linsenzhong@126.com', '121212121212122', '2016-10-11 22:36:57');
INSERT INTO `t_user` VALUES ('54', 'steadyjack', '11111', '1111@126.com', '1111122222121212', '2016-10-28 22:37:06');
INSERT INTO `t_user` VALUES ('55', '钟林森', '22222', '2222@126.com', '2222211111121212', '2016-10-20 22:37:09');
INSERT INTO `t_user` VALUES ('56', '钟稳杰', '33333', '3333@126.com', '3333322222121212', '2016-10-13 22:37:12');
在tomcat跑起来，下面是运行效果：
点击“导出用户”，即可下载得到“用户excel表.xls”，打开来瞧瞧，是否跟上图的数据一致：
发现一致是一致，但是就是丑陋了点。接下来，我们“用模板导出用户”，可以得到比较好看的数据：
最后，点击“用模板批量导入数据”：
可以先下载模板文件，填写好数据之后，再回来这里“选择文件”，上传填写好数据的那个excel文件，下面是我弄好的几条数据：
最后，上传这个文件，导入进去，再回来首页看看：
关于导入，目前还没有去深入搞：“判断一些是否合法，如何将不合法的数据再导入完成之后提示或者展示给用户观看。。。”这些，自己也在搞中，有兴趣的，可以加qq交流！个人QQ:1948831260
如果觉得我的文章对您有用，请随意打赏。你的支持将鼓励我继续创作！
