Spring Boot通过上传Excel并到数据库
Spring Boot通过上传Excel并到数据库
适合场景：需要通过excel表格批量向数据库中导入信息
操作流程
【1】前端上传一个excel表格
【2】 后端接收这个excel表格，将表格中的数据存入List集合中
【3】后端通过这个List集合将数据批量填入数据库中
源码地址：https://gitee.com/residual-temperature/import-excel-demo
实现过程
1、pom文件中要加入的jar包
<!-- 导入excel相关 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
因为加入这两个jar包application.yml文件中没有添加配置，所以这里就不展示
2、实体类User
@Repository
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User implements Serializable {
private long id;
private String account;
private String password;
private String username;
private String address;
}
3、UserMapper文件
@Mapper
@Repository
public interface UserMapper {
// 批量添加用户数据
public int addBatchUser(List<User> userList);
// 根据用户账号查询用户数据
public User findUserByAccount(String account);
// 根据用户账号修改用户信息
public int updateUserByAccount(User user);
}
4、UserMapper.xml文件的配置
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace= "com.yuwen.mapper.UserMapper" >
<!-- 批量导入的实现 -->
<insert id="addBatchUser">
insert into user(account,password,username,address)
values
<foreach collection="userList" item="item" separator=",">
(
#{item.account},
#{item.password},
#{item.username},
#{item.address}
)
</foreach>
</insert>
<select id="findUserByAccount" resultType="com.yuwen.pojo.User">
select * from user where account = #{account}
</select>
<update id="updateUserByAccount">
update user set account = #{account},password = #{password},username = #{username},address = #{address}
where account = #{account}
</update>
</mapper>
因为UserService只有方法没有实现，所以直接看UserService的实现类UserServiceImpl文件
5、UserServiceImpl文件
@Service
public class UserServiceImpl implements UserService {
@Resource
UserMapper userMapper;
@Resource
ImportExcelUtils excelUtils;
/**
* 批量添加用户信息
* @param userList 用户信息
* @return 用户信息是否添加成功
*/
@Override
public boolean addBatchUser(List<User> userList) {
return userMapper.addBatchUser(userList) > 0;
}
/**
* 根据用户账号查看用户是否存在
* @param account 用户账号
* @return 查询到的用户信息
*/
@Override
public User findUserByAccount(String account) {
return userMapper.findUserByAccount(account);
}
/**
* 根据用户账号修改用户信息
* @param user 用户信息
* @return 是否修改成功
*/
@Override
public boolean updateUserByAccount(User user) {
return userMapper.updateUserByAccount(user) > 0;
}
/**
* 批量导入用户信息
* @param fileName 导入的表名
* @param is 导入表的输入流
* @return 是否导入成功
*/
@Override
public boolean importUserInfo(String fileName, InputStream is){
try {
List<User> userList = excelUtils.upload(fileName, is);
for (int i = 0; i < userList.size(); i++) {
User user = findUserByAccount(userList.get(i).getAccount()); // 查找数据库中看这个用户信息是否存在
if (user != null){
updateUserByAccount(userList.get(i)); // 更新数据库中的用户信息
userList.remove(i); // 移除更新后的用户
i = i - 1; // 因为移除了，所以userList大小减了一而循环加了一，所以要减回去
}
}
return addBatchUser(userList); // 批量添加
} catch (Exception e) {
e.printStackTrace();
return false;
}
}
}
6、UserController文件
@RestController
public class UserController {
@Resource
UserService userService;
// 批量添加数据
@PostMapping("/upload")
public Map<String,String> upload(MultipartFile excelFile) throws Exception{
Map<String,String> map = new HashMap<>();
if (excelFile.isEmpty()){
map.put("mag","文件夹为空，重新上传");
return map;
}else {
String fileName = excelFile.getOriginalFilename();
InputStream is = excelFile.getInputStream();
if (userService.importUserInfo(fileName,is)){
map.put("msg","数据添加成功");
return map;
}else {
map.put("msg","数据添加失败，请重新添加");
return map;
}
}
}
}
7、导入excel的工具类ImportExcelUtils的编写
批量导入的主要代码，主要呈现在这个工具类和上面UserMapper.xml中的批量导入
@Component
public class ImportExcelUtils {
// 将表格中的数据添加到List集合中
public List<User> upload(String fileName, InputStream is) throws Exception{
Workbook workbook = getWorkbook(fileName,is);
List<User> userList = new ArrayList<>();
int number = workbook.getNumberOfSheets(); // 获取sheet值
for (int i = 0; i < number; i++) {
Sheet sheet = workbook.getSheetAt(i); // 获取表格页码
if (sheet != null){
int rowNum = sheet.getLastRowNum(); // 获取该页表共有多少行
for (int j = 1; j <= rowNum; j++) { // 一般来说第一行是标题,所以第二行开始读取
Row row = sheet.getRow(j); // 获取表格行
User user = new User();
row.getCell(0).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
user.setAccount(row.getCell(0).getStringCellValue()); // 获取表格单元格并给User设置值
row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
user.setPassword(row.getCell(1).getStringCellValue());
row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
user.setUsername(row.getCell(2).getStringCellValue());
row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
user.setAddress(row.getCell(3).getStringCellValue());
System.out.println(user);
userList.add(user);
}
}
}
return userList;
}
// 判断传入的文件是哪种类型的excel文件
public Workbook getWorkbook(String fileName,InputStream is) throws Exception{
Workbook workbook = null;
String name = fileName.substring(fileName.lastIndexOf("."));
System.out.println(name);
if (".xls".equals(name)){
workbook = new HSSFWorkbook(is);
}else if (".xlsx".equals(name)){
workbook = new XSSFWorkbook(is);
}else {
throw new Exception(" 请上传.xls/.xlsx格式文件！");
}
return workbook;
}
}
8、html页面的编写
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>上传文件</title>
<script src="http://lib.sinaapp.com/js/jquery/1.9.1/jquery-1.9.1.min.js"></script>
<script>
function upload(){
var formData = new FormData(); //创建一个formData对象实例
var excelFile = $("#uploadFile").get(0).files[0];
formData.append("excelFile",excelFile)
$.ajax({
url: 'http://localhost:8081/upload',
data: formData,
type: 'post',
contentType:false, // 必须false才会自动加上正确的Content-Type
processData: false, // 必须false才会避开jQuery对 formdata 的默认处理,XMLHttpRequest会对 formdata 进行正确的处理
success: function (resp){
console.log(resp);
// 后续的操作
}
})
}
</script>
</head>
<body>
<p>上传的文件</p>
<input multiple="multiple" type="file" id="uploadFile">
<br>
<br>
<button onclick="upload()">上传</button>
</body>
</html>
因为这个项目主要是为了excel的读取和导入到数据库，所以就没有进行后续的操作了
项目测试
导入前的数据库和excel表格要导入的数据
