利用EXCEL文件批量上传用户信息

java web 专栏收录该内容
17 篇文章 0 订阅

批量导入用户信息

批量导入通常是将指定格式的excel或word文件上传到服务器,服务器读取上传的文件内容,将文件内容全部读取到数据库的过程。
此案例包括:文件下载、文件上传、EXCEL文件解析、将解析后的数据存入数据库。

操作步骤:

一、预备工作

0、引入包

文件上传:commons-fileupload-1.2.2.jar、commons-io-2.1.jar

文件下载:不需要JAR包支持

XLS文件解析:poi-3.7-20101029.jar

1、建表users

create table users(
id int primary key auto_increment,
name varchar(20) not null,
password varchar(32) not null,
sex varchar(2) not null,
birthday date not null,
hobby varchar(50) not null,
telephone varchar(15) not null,
address varchar(50) not null,
type int not null -- 1 admin 2 common user
);

插入测试数据:

insert into users values(1,'admin','202CB962AC59075B964B07152D234B70','男',
'1980-1-1','体育,旅游','13953311099','山东淄博',1);
insert into users values(2,'abc','202CB962AC59075B964B07152D234B70','女',
'1990-1-1','体育,音乐','1895330000','山东济南',2);

效果如下:

2、建立相应的JavaBean:

package cn.sdut.po;
public class User {
private int id;
private String name;
private String password;
private String sex;
private String birthday;
private String hobby;
private String telephone;
private String address;
private int type;
public User() {
super();
}
public User(int id, String name, String password, String sex,
String birthday, String hobby, String telephone, String address,
int type) {
super();
this.id = id;
this.name = name;
this.password = password;
this.sex = sex;
this.birthday = birthday;
this.hobby = hobby;
this.telephone = telephone;
this.address = address;
this.type = type;
}
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 getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getBirthday() {
return birthday;
}
public void setBirthday(String birthday) {
this.birthday = birthday;
}
public String getHobby() {
return hobby;
}
public void setHobby(String hobby) {
this.hobby = hobby;
}
public String getTelephone() {
return telephone;
}
public void setTelephone(String telephone) {
this.telephone = telephone;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public int getType() {
return type;
}
public void setType(int type) {
this.type = type;
}
@Override
public String toString() {
return "User [id=" + id + ", name=" + name + ", password=" + password
+ ", sex=" + sex + ", birthday=" + birthday + ", hobby="
+ hobby + ", telephone=" + telephone + ", address=" + address
+ ", type=" + type + "]";
}
}

如果要进行批量导入,需要给用户提供一个模板供下载,user.xls(将其放置于WebRoot/excel/user.xls)

要在WebRoot下创建一个目录 excel,存放excel模板user.xls.

文件内容为:

序号姓名密码性别出生年月爱好联系方式地址   用户类型(1-管理员 2-普通用户)
1李华66661977-3-3音乐;美术196852452北京朝阳2

注意:数据类型与数据库中的数据类型相匹配。需要特别注意数据的类型与日期类型的格式,见下图。


二、文件下载

0、准备工作:前端页面——userMain.jsp,内容如下。

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'userMain.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
<script src="js/calendar.js"></script>
<script>
function selectUser()
{
form1.action="<%=path%>/UserServlet?method=bycond";
form1.submit();
}
</script>
</head>
<body>
<center>
<h1>用 户 管 理</h1></center>
<br>
<form action="" method="post" enctype="multipart/form-data" name="form1">
<p>查询条件:
<input type="text" name="condition" id="condition">
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 出生日期:
<input name="datefrom" type="text" id="datefrom" size="10" onclick="new Calendar().show(this)">
~
<input name="dateto" type="text" id="dateto" size="10" onclick="new Calendar().show(this)">
&nbsp;&nbsp;&nbsp;&nbsp;
<input type="button" name="select" id="select" value=" 查 询 " onclick="selectUser()">
</p>
<table width="899" border="1">
<tr>
<td width="67" align="center">选择</td>
<td width="67" align="center">序号</td>
<td width="82" align="center">姓名</td>
<td width="70" align="center">性别</td>
<td width="99" align="center">出生日期</td>
<td width="88" align="center">爱好</td>
<td width="108" align="center">电话</td>
<td width="108" align="center">地址</td>
<td width="200" align="center">操作</td>
</tr>
<c:forEach items="${userList}" var="user" varStatus="status">
<tr>
<td align="center">
<input type="checkbox" name="id" id="id" value="${user.id}"></td>
<td align="center"> ${status.count} </td>
<td align="center">${user.name }</td>
<td align="center">${user.sex }</td>
<td align="center">${user.birthday }</td>
<td>${user.hobby }</td>
<td>${user.telephone }</td>
<td>${user.address}</td>
<td>&nbsp;
<a href="<%=path%>/UserServlet?method=del&id=${user.id}">删除</a>
&nbsp; 修改&nbsp; 密码复位</td>
</tr>
</c:forEach>
<tr>
<td height="47" colspan="9" align="center"><input type="submit" name="addUser" id="addUser" value=" 增加用户 ">
&nbsp; &nbsp;
<input type="submit" name="batchDelete" id="batchDelete" value=" 批量删除 ">
&nbsp; &nbsp;
<input name="batchResetPassword" type="submit" id="batchResetPassword" value="批量密码复位">
&nbsp; &nbsp;</td>
</tr></table></form>
<tr>
<td height="47" colspan="9" align="left">
<form name="form2" method="post" action="<%=path%>/UserServlet?method=uploadExcel" enctype="multipart/form-data">
<p>
<h3>&nbsp;&nbsp;&nbsp;&nbsp; 导入用户
</h3>
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 选择导入文件:
<input type="file" name="file" id="file">
<input type="submit" name="ok" id="ok" value="确定">
&nbsp; &nbsp; &nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp;
<a href="<%=path%>/UserServlet?method=downloadExcel&filename=user.xls"> <img src="<%=path%>/images/excel.png" alt="" width="30" height="30">&nbsp; 下载EXCEL样例 </a>
</p>
<p>&nbsp;</p>
</form>
<p>&nbsp;</p>
<p><br><br>
</p></td>
</tr>
<p>&nbsp;</p>
${msg }
</body>
</html>

效果为:

1、下载流程

页面中的下载相关代码:

<a href="<%=path%>/UserServlet?method=downloadExcel&filename=user.xls">
<img src="<%=path%>/images/excel.png" width="30" height="30">&nbsp; 下载EXCEL样例
</a>

2、处理下载任务的Servlet

建立一个UserServlet,映射路径为: /UserServlet

public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
String method=request.getParameter("method");//得到客户端要进行何种操作
switch(method)
{
case "bycond":
queryUserByCondition(request,response);
break;
case "del":
delUser(request,response);
break;
case "downloadExcel":
downloadExcel(request,response);
break;
case "uploadExcel":
uploadExcel(request,response);
break;
}
}
private void downloadExcel(HttpServletRequest request,
HttpServletResponse response) throws IOException {
//从客户端得到待写出文件的名称
String filename=request.getParameter("filename");
//确定待写出的文件位置
String path=getServletContext().getRealPath("/excel");
//浏览器层面的处理
response.setHeader("Content-disposition","attachment;filename="+filename);
//实际写出文件
//创建输入流
File file=new File(path,filename);
System.out.println(file);
InputStream is=new FileInputStream(file);
//得到输出流
ServletOutputStream writer = response.getOutputStream();
//边读边写
byte[] bytes=new byte[1024];
int len=0;
while((len=(is.read(bytes)))!=0)
{
writer.write(bytes, 0, len);
}
//写出数据后做好扫尾工作,清空缓冲区,关闭输入输出流
writer.flush();
is.close();
writer.close();
}

执行,文件可以下载。

下载后,打开文件内容为:

在里在填充上数据,以便于进行文件的上传。
说明:模板数据暂时没有删除,如果需要的话可删除掉,填入真实有效的数据。

三、文件上传及批量导入流程

主要步骤:

1、上传excel文件到服务器

2、 服务器端解析excel文件并存储到链表集合中

3、 调用DBUtils的batch方法进行批量处理

实施步骤:

1. 页面上传 JSP文件代码:

<form name="form2"
method="post"
action="<%=path%>/UserServlet?method=uploadExcel"
enctype="multipart/form-data">
<p>
<h3>&nbsp;&nbsp;&nbsp;&nbsp; 导入用户
</h3>
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 选择导入文件:
<input type="file" name="file" id="file">
<input type="submit" name="ok" id="ok" value="确定"> &nbsp; &nbsp; &nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp;
<a href="<%=path%>/UserServlet?method=downloadExcel&filename=user.xls"> <img src="<%=path%>/images/excel.png" alt="" width="30" height="30">&nbsp; 下载EXCEL样例 </a>
</p>
<p>&nbsp;</p>
</form>

2. BaseDao中批量操作方法

//批量操作数据库的方法
public boolean bactchUpdate(String sql,Object[][] param)
{
int[] result=new int[param.length];
int r=1;
Connection conn=getConn(); //得到连接
QueryRunner runner=new QueryRunner(); //得到运行对象
try {
result=runner.batch(conn,sql,param);// 批量进行数据库操作
} catch (SQLException e) {
e.printStackTrace();
}
finally
{
DbUtils.closeQuietly(conn); //关闭数据库连接
}
//对返回数据进行加工,将整型数组转化为布尔类型
for(int i=0;i<result.length;i++)
{
r*=result[i];
}
return r>0?true:false;
}

3、建立UserDao,继承BaseDao类,定义批量插入数据的方法

public class UserDao extends BaseDao{
//批量导入用户
public boolean batchImport(List<User> users)
{
boolean flag=false;
String sql="insert into users(name,password,sex,birthday, hobby,telephone,address,type) values(?,?,?,?,?,?,?,?)";
Object[][] params=new Object[users.size()][8];
for(int i=0;i<users.size();i++)
{
User user = users.get(i);
params[i][0]=user.getName();
params[i][1]=user.getPassword();
params[i][2]=user.getSex();
params[i][3]=user.getBirthday();
params[i][4]=user.getHobby();
params[i][5]=user.getTelephone();
params[i][6]=user.getAddress();
params[i][7]=user.getType();
}
flag = bactchUpdate(sql, params);
return flag;
}
}

4.编写UserServlet实现批量请求

public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
String method=request.getParameter("method");//得到客户端要进行何种操作
switch(method)
{
case "bycond":
queryUserByCondition(request,response);
break;
case "del":
delUser(request,response);
break;
case "downloadExcel":
downloadExcel(request,response);
break;
case "uploadExcel":
uploadExcel(request,response);
break;
}
}
private void uploadExcel(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
UserDao dao=new UserDao();
//第一步,文件上传,需要commons-fileupload-1.2.2.jar和commons-io-2.1.jar的支持
boolean isMultipart = ServletFileUpload.isMultipartContent(request);
boolean isuploadSuccess = false;
File excelFile = null;
try {
// 设置上传路径
String uploaddir = request.getSession().getServletContext().getRealPath("/excels");
File upFile = new File(uploaddir);
if (!upFile.exists()) {
upFile.mkdirs();
}
if (isMultipart == true) {
FileItemFactory factory = new DiskFileItemFactory();
ServletFileUpload upload = new ServletFileUpload(factory);
List<FileItem> items;
items = upload.parseRequest(request);
Iterator<FileItem> itr = items.iterator();
while (itr.hasNext()) {
// 单文件上传
FileItem item = (FileItem) itr.next();
if (!item.isFormField()) { // 文件,不包含普通表单内容
File uploadFile = new File(item.getName());
excelFile = new File(uploaddir, uploadFile.getName());
try {
item.write(excelFile);
isuploadSuccess = true;
} catch (Exception e) {
e.printStackTrace();
isuploadSuccess = false;
System.out.println("上传失败!!");
}
} else {
// 普通表单内容 不处理
System.out.println(item.toString());
}
}
//第二步,解析文件内容(需要poi-3.7-20101029.jar的支持),将数据封装到List中
// 如果文件上传成功,解析excel,存入List集合中
//如果excel数据比较大,List可能放不下的问题
//你可以分页读取excel,一个List里面放2W条数据应该没什么问题
// 根据excel的格式 解析自己的数据,此处用户信息包含如下字段:
//1序号、2姓名、3密码、4性别、5出生年月、6爱好、7联系方式、8地址、9用户类型(1-管理员 2-普通用户)
List<User> list = new ArrayList<User>();
if (isuploadSuccess) { //若上传成功,就解析其中的数据
// poi解析
FileInputStream fis = null;
try {
System.out.println(excelFile.getAbsolutePath());
fis = new FileInputStream(excelFile);
HSSFWorkbook book = new HSSFWorkbook(fis);//由文件输入流得到工作簿对象
HSSFSheet sheet = book.getSheetAt(0); // 得到工作簿的第一个sheet
int lastRowNum = sheet.getLastRowNum(); // 得到有数据的最大行数
System.out.println("行数:" + lastRowNum);
// 一般excel第一行是标题
HSSFRow row = null;
for (int i = 1; i <= lastRowNum; i++) {
row = sheet.getRow(i); //得到工作表的第 i行
String name = getCellValue(row, 1);
String password = getCellValue(row, 2);
String sex = getCellValue(row, 3);
String birthday = getCellValue(row, 4);
String hobby = getCellValue(row, 5);
String telephone = getCellValue(row, 6);
String address = getCellValue(row, 7);
int type = getIntCellValue(row, 8);
if(name.length()*password.length()*sex.length()*
birthday.length()*hobby.length()*telephone.length()
*address.length()*type!=0)
{
User user = new User(0,name,password,sex,birthday,hobby,telephone,address,type);
list.add(user);
}
}
} catch (Exception e) {
System.out.println("excel解析出错!!!");
e.printStackTrace();
}
} else {
System.out.println("失败:");
}
// 解析excel成功,批量添加数据
System.out.println(list);
boolean result = dao.batchImport(list);
if(result == true){
response.getWriter().print("导入成功!<br>");
}else{
response.getWriter().print("导入失败!<br>");
}
} else {
System.out.print("the enctype must be multipart/form-data");
}
} catch (FileUploadException e) {
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
request.getRequestDispatcher("/UserServlet?method=bycond").forward(request, response); //重新显示数据
}
//得到row行cellIndex列的字符串数据
private String getCellValue(HSSFRow row, int cellIndex) {
String cellValue = "";
cellValue = row.getCell(cellIndex).getStringCellValue();
return cellValue;
}
//得到row行cellIndex列的int类型数据
private int getIntCellValue(HSSFRow row, int cellIndex) {
int cellValue = 0;
cellValue = (int) row.getCell(cellIndex).getNumericCellValue();
return cellValue;
}
//得到row行cellIndex列的long类型数据
private long getLongCellValue(HSSFRow row, int cellIndex) {
long cellValue = 0;
cellValue = (long) row.getCell(cellIndex).getNumericCellValue();
return cellValue;
}

执行文件上传后的效果为:

  • 4
    点赞
  • 0
    评论
  • 5
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值