java之poi操作excel-批量导入导出

来源：互联网发布：在淘宝买东西省钱的app 编辑：程序博客网时间：2024/05/14 09:33

上一篇博文介绍了poi操作excel的基本读写操作后，接下来，介绍一下在项目中的实际用途：批量导入、批量导出功能。因为重点知识介绍批量导入导出excel功能，故而项目整体的搭建后台用jdbc与struts2，前端页面用jquery-easyui实现（其实也可以整合到ssm或者ssh中，有需要者可以加我qq：1974544863，愿意带酬劳为你定制开发）。

首先，看一下，项目的整体结构图：

首先，当然是放入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文件,然后读取第一个sheetInputStream 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.datepstmt.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" onclick="newUser()">添加用户</a>        <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-edit" plain="true" onclick="editUser()">编辑用户</a>        <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-remove" plain="true" onclick="deleteUser()">删除用户</a>        <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-export" plain="true" onclick="exportUser()">导出用户</a>        <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-export" plain="true" onclick="exportUser2()">用模版导出用户</a>        <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-import" plain="true" onclick="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" onclick="saveUser()">保存</a><a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-cancel" onclick="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"  onclick="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" onclick="uploadFile()">上传</a><a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-cancel" onclick="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交流！

6 1