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

    上一篇博文介绍了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

发布了140 篇原创文章 · 获赞 253 · 访问量 64万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 精致技术 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览