后端实现:
1.pom.xml引入依赖
<!--导入Excel依赖 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.0</version>
</dependency>
2.编写将输入的文件输入流转化为类实力集合的工具类
package com.example.upload.demo.utils;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import com.example.upload.demo.Bean.Student;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class UserDataUtil {
// 从本地文件中导入用户表
public static List<Student> excelToUsers(InputStream is) {
try {
Workbook workbook = new XSSFWorkbook(is);
Sheet sheet = workbook.getSheetAt(0);
Iterator<Row> rows = sheet.iterator();
List<Student> users = new ArrayList<Student>();
int rowNumber = 0;
while (rows.hasNext())
{
Row currentRow = rows.next();
// 跳过表头
if (rowNumber == 0)
{
rowNumber++;
continue;
}
Iterator<Cell> cellsInRow = currentRow.iterator();
Student user = new Student();
String s1 = null;
int cellIdx = 0;
while (cellsInRow.hasNext())
{
Cell currentCell = cellsInRow.next();
currentCell.setCellType(CellType.STRING);
switch (cellIdx)
{
case 0:
s1 = currentCell.getStringCellValue();
user.setUsername(s1);
break;
case 1:
s1 = currentCell.getStringCellValue();
user.setPassword(s1);
break;
case 2:
s1 = currentCell.getStringCellValue();
user.setName(s1);
break;
case 3:
s1 = currentCell.getStringCellValue();
user.setCharSex(s1);
break;
case 4:
s1 = currentCell.getStringCellValue();
int age = Integer.parseInt(s1);
user.setAge(age);
break;
case 5:
s1 = currentCell.getStringCellValue();
user.setStuEmail(s1);
break;
case 6:
s1 = currentCell.getStringCellValue();
user.setProfessional(s1);
break;
default:
break;
}
// 注册时间
// DateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
// Date date = new Date();
// try
// {
// date = fmt.parse(currentCell.getStringCellValue());
// } catch (ParseException e)
// {
// // TODO Auto-generated catch block
// e.printStackTrace();
// }
// user.setRegisteDate(date);
cellIdx++;
}
System.out.println("读取到的User:" + user.toString());
// 用户名不能重复
for (int i = 0; i < users.size(); i++)
{
// 判断当前得用户名与列表中的用户名是否重复
if (user.getUsername().equals(users.get(i).getUsername()))
{
return null;
}
}
users.add(user);
}
workbook.close();
return users;
}
catch (IOException e)
{
throw new RuntimeException("Excel文件解析异常: " + e.getMessage());
}
}
}
3.编写批量注册的方法,工具类也是为这个方法服务的,在导入文件进行批量注册时,根据自己系统的要求和特征写相应的判断和处理,以保证系统的健壮性以及友好的提示
// 导入学生信息 批量注册学生
@PostMapping("/studentImport")
public Msg importStudent(@RequestParam("file") MultipartFile file)
{
System.out.println("file:"+file);
List<Student> students = null;
try
{
students = UserDataUtil.excelToUsers(file.getInputStream());
if (students == null)
{
System.out.println("文件没有数据或者用户名重复");
Msg fail = Msg.fail();
fail.setMsg("批量注册失败,文件没有数据或者用户名重复");
return fail;
}
System.out.println("从execl文件读取到得数据信息:"+students);
// 判断导入的用户信息的用户名是否与数据库中的用户名重复
for (int i = 0; i < students.size(); i++)
{
Student student = studentService.getUserByUserName(students.get(i).getUsername());
if (student != null)
{
System.out.println("批量注册失败,用户名重复");
Msg fail = Msg.fail();
fail.setMsg("批量注册失败,【"+students.get(i).getUsername()+"】与数据库中的用户名重名");
return fail;
}
}
// 批量插入学生信息
for (int i = 0; i < students.size(); i++)
{
// 使用 MD5 + salt + hash散列
String password = students.get(i).getPassword();
String salt = SaltUtils.getSalt(8);
Md5Hash md5Hash = new Md5Hash(password, salt, 1024);
students.get(i).setMd5Password(md5Hash.toHex());
students.get(i).setMd5Salt(salt);
students.get(i).setRoleId(1);
students.get(i).setType(1);
if (students.get(i).getCharSex().equals("男"))
{
students.get(i).setSex(1);
}
else
{
students.get(i).setSex(0);
}
// 查询所有的专业信息
List<ProfessionalMenus> professionals = adminService.getAllProfessional();
boolean flag = false;
for (int j = 0; j < professionals.size(); j++)
{
if (students.get(i).getProfessional().equals(professionals.get(j).getName()))
{
students.get(i).setProfessionalId(professionals.get(j).getId());
flag = true;
break;
}
}
if (!flag)
{
System.out.println("专业信息填写错误");
Msg fail = Msg.fail();
fail.setMsg(students.get(i).getProfessional()+"专业信息填写错误,批量注册终止");
return fail;
}
else
{
studentService.addStudent(students.get(i));
}
}
} catch (Exception e)
{
System.out.println("文件导入异常");
Msg fail = Msg.fail();
fail.setMsg("批量注册失败,文件信息不规范");
return fail;
}
Msg success = Msg.success();
success.setMsg("批量注册成功");
return success;
}
前端实现:
前端的比较简单,话不多说直接上代码
<template>
<div>
<el-upload
class="upload-demo"
action="http://localhost:8083/api/studentImport"
:limit="1"
accept="application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
:before-upload="fileChange"
:on-success="uploadSuccess"
>
<el-button size="small" type="primary" v-show="show">
批量注册<i class="el-icon-upload el-icon--right"></i>
</el-button>
</el-upload>
</div>
</template>
<script>
export default {
data() {
return {
show: false,
};
},
created() {
const role = window.sessionStorage.getItem("role");
if (role === "admin") {
this.show = true;
}
},
methods: {
uploadSuccess(e) {
if (e.code !== 200) {
this.$message.error(e.msg);
} else {
this.$message.success(e.msg);
}
},
async fileChange(file) {
const confirmResult = await this.$confirm(
"确定要使用此文件进行批量注册吗?",
"提示",
{
confirmButtonText: "确定",
cancelButtonText: "取消",
type: "warning",
}
).catch((err) => err);
if (confirmResult !== "confirm") {
this.$message.info("您取消了批量注册操作");
reject(file);
return false;
}
},
},
};
</script>
<style lang="less" scoped></style>
总结:在实现这个功能的过程中,我觉得总难的是,你要知道前端跟后端是怎么交互的。
1.前端的文件上传后怎么传递到后台
2.后台怎么给前端返回数据信息
后台给前端返回的数据会被自动封装在下面这个函数的参数中,作为一个主要开发后端的人来说实在是太神奇了
3.怎么询问用户是否上传,用户点击取消后取消上传
官方文档的说明:
实现效果: