java之poi操作excel-批量导入导出
上一篇博文介绍了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>
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" 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', '[email protected]', '121212121212122', '2016-10-11 22:36:57');
- INSERT INTO `t_user` VALUES ('54', 'steadyjack', '11111', '[email protected]', '1111122222121212', '2016-10-28 22:37:06');
- INSERT INTO `t_user` VALUES ('55', '钟林森', '22222', '[email protected]', '2222211111121212', '2016-10-20 22:37:09');
- INSERT INTO `t_user` VALUES ('56', '钟稳杰', '33333', '[email protected]', '3333322222121212', '2016-10-13 22:37:12');
在tomcat跑起来,下面是运行效果:
点击“导出用户”,即可下载得到“用户excel表.xls”,打开来瞧瞧,是否跟上图的数据一致:
发现一致是一致,但是就是丑陋了点。接下来,我们“用模板导出用户”,可以得到比较好看的数据:
最后,点击“用模板批量导入数据”:
可以先下载模板文件,填写好数据之后,再回来这里“选择文件”,上传填写好数据的那个excel文件,下面是我弄好的几条数据:
最后,上传这个文件,导入进去,再回来首页看看:
关于导入,目前还没有去深入搞:“判断一些是否合法,如何将不合法的数据再导入完成之后提示或者展示给用户观看。。。”这些,自己也在搞中,有兴趣的,可以加qq交流!