SpringBoot提供接口实现Excel导入数据并存储到数据库中
1 效果展示
1.1 Excel数据
zxy.xlsx
1.2 导入后数据库中数据
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(255) DEFAULT NULL COMMENT '姓名',
`phone` varchar(255) DEFAULT NULL COMMENT '联系方式',
`address` varchar(255) DEFAULT NULL COMMENT '联系地址',
`birthday` datetime DEFAULT NULL COMMENT '出生日期',
`des` varchar(255) DEFAULT NULL COMMENT '自我描述',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
2 代码开发
2.1 Pojo
根据excel数据需求，设计数据库表结构，并开发实体类代码
public class User implements Serializable {
private Integer id;
private String name;
private String phone;
private String address;
private Date birthday;
private String des;
private static final long serialVersionUID = 1L;
public User(Integer id, String name, String phone, String address, Date birthday, String des) {
this.id = id;
this.name = name;
this.phone = phone;
this.address = address;
this.birthday = birthday;
this.des = des;
}
public User() {
super();
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name == null ? null : name.trim();
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone == null ? null : phone.trim();
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address == null ? null : address.trim();
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getDes() {
return des;
}
public void setDes(String des) {
this.des = des == null ? null : des.trim();
}
@Override
public boolean equals(Object that) {
if (this == that) {
return true;
}
if (that == null) {
return false;
}
if (getClass() != that.getClass()) {
return false;
}
User other = (User) that;
return (this.getId() == null ? other.getId() == null : this.getId().equals(other.getId()))
&& (this.getName() == null ? other.getName() == null : this.getName().equals(other.getName()))
&& (this.getPhone() == null ? other.getPhone() == null : this.getPhone().equals(other.getPhone()))
&& (this.getAddress() == null ? other.getAddress() == null : this.getAddress().equals(other.getAddress()))
&& (this.getBirthday() == null ? other.getBirthday() == null : this.getBirthday().equals(other.getBirthday()))
&& (this.getDes() == null ? other.getDes() == null : this.getDes().equals(other.getDes()));
}
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + ((getId() == null) ? 0 : getId().hashCode());
result = prime * result + ((getName() == null) ? 0 : getName().hashCode());
result = prime * result + ((getPhone() == null) ? 0 : getPhone().hashCode());
result = prime * result + ((getAddress() == null) ? 0 : getAddress().hashCode());
result = prime * result + ((getBirthday() == null) ? 0 : getBirthday().hashCode());
result = prime * result + ((getDes() == null) ? 0 : getDes().hashCode());
return result;
}
}
2.2 Controller
首先看controller层，这里是为外部提供一个访问接口，通过/excel/import路径访问。因为使用的POST请求方式，所以在请求的时候需要将file文件一同传入。然后获取file文件的文件名，用于后续判断他是不是一个excel文件。继续就是哟个了Service的batchImport方法进行业务逻辑处理。
@RestController
@RequestMapping("/excel/")
public class TestController {
@Autowired
private ImportExcelService importExcelService;
@PostMapping("/import")
public boolean addUser(@RequestParam("file") MultipartFile file) {
boolean a = false;
String fileName = file.getOriginalFilename();
try {
a = importExcelService.batchImport(fileName, file);
} catch (Exception e) {
e.printStackTrace();
}
return a;
}
}
2.3 Service
在Service层，首先设计一个interface接口，然后定义一个方法
public interface ImportExcelService {
boolean batchImport(String fileName, MultipartFile file) throws Exception;
}
ImportExcelServiceImpl 主要是对整个需求的逻辑进行开发，从controller处获得文件名称和文件。那么拿到这些信息后，我们首先要判断，这是不是一个excel文件，按照excel的扩展名来说有xls和xlsx两种，要使用不同的函数处理。其次，一个excel是可以有很多的sheet页的，需要使用getSheetAt获取你需要的sheet页或者依次获得sheet页。最后就是获取每个sheet页中的行数据和单元格ceil数据。使用mapper层定义的三个方法，使用selectByName()方法判断对应的姓名是否存在，存在即更新，不存在即删除。
@Service
@Transactional(readOnly = true)
public class ImportExcelServiceImpl implements ImportExcelService {
@Autowired
private UserMapper userMapper;
/**
* 首先判断文件扩展名
* 其次获取不同sheet页的内容
* 然后获取行数据
* 最后获取单元格数据
*/
@Transactional(readOnly = false,rollbackFor = Exception.class)
@Override
public boolean batchImport(String fileName, MultipartFile file) throws Exception {
boolean notNull = false;
List<User> userList = new ArrayList<User>();
if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
throw new MyException("上传文件格式不正确");
}
boolean isExcel2003 = true;
if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
isExcel2003 = false;
}
InputStream is = file.getInputStream();
Workbook wb = null;
/**
* Excel2003以前的版本，扩展名是.xls,使用HSSFWorkbook()
* Excel2007之后的版本，扩展名是.xlsx,使用XSSFWorkbook()
*/
if (isExcel2003) {
wb = new HSSFWorkbook(is);
} else {
wb = new XSSFWorkbook(is);
}
// 获取excel的第一个sheet页
Sheet sheet = wb.getSheetAt(0);
if(sheet!=null){
notNull = true;
}
User user;
// 循环行数
for (int r = 1; r <= sheet.getLastRowNum(); r++) {
// 获取sheet的第r行的数据
Row row = sheet.getRow(r);
if (row == null){
continue;
}
user = new User();
/**
* int CELL_TYPE_NUMERIC = 0;
* int CELL_TYPE_STRING = 1;
* int CELL_TYPE_FORMULA = 2;
* int CELL_TYPE_BLANK = 3;
* int CELL_TYPE_BOOLEAN = 4;
* int CELL_TYPE_ERROR = 5;
*/
if( row.getCell(0).getCellType() !=1){
throw new MyException("导入失败(第"+(r+1)+"行,姓名请设为文本格式)");
}
String name = row.getCell(0).getStringCellValue();
if(name == null || name.isEmpty()){
throw new MyException("导入失败(第"+(r+1)+"行,姓名未填写)");
}
row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
String phone = row.getCell(1).getStringCellValue();
if(phone==null || phone.isEmpty()){
throw new MyException("导入失败(第"+(r+1)+"行,电话未填写)");
}
String add = row.getCell(2).getStringCellValue();
if(add==null){
throw new MyException("导入失败(第"+(r+1)+"行,不存在此单位或单位未填写)");
}
Date date;
if(row.getCell(3).getCellType() !=0){
throw new MyException("导入失败(第"+(r+1)+"行,入职日期格式不正确或未填写)");
}else{
date = row.getCell(3).getDateCellValue();
}
String des = row.getCell(4).getStringCellValue();
user.setName(name);
user.setPhone(phone);
user.setAddress(add);
user.setBirthday(date);
user.setDes(des);
userList.add(user);
}
for (User userResord : userList) {
//首先判断该姓名是否已经存在
String name = userResord.getName();
int cnt = userMapper.selectByName(name);
if (cnt == 0) {
//不存在即插入
userMapper.addUser(userResord);
System.out.println(" 插入 "+userResord);
} else {
//存在即更新
userMapper.updateUserByName(userResord);
System.out.println(" 更新 "+userResord);
}
}
return notNull;
}
}
2.4 Mapper
定义三个函数，用于判断用户是否存在，进行更新和插入
@Mapper
public interface UserMapper {
// 当查到数据库中没有对应姓名记录时，插入
void addUser(User sysUser);
// 当数据库中有对应姓名记录时，更新
int updateUserByName(User sysUser);
// 判断是都存在对应的姓名记录
int selectByName(String name);
}
根据mapper定义的函数，在UserMapper.xml文件中写SQL
<?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.zxy.ImportExcelProject.mapper.UserMapper">
<resultMap id="BaseResultMap" type="com.zxy.ImportExcelProject.pojo.User">
<constructor>
<idArg column="t_id" javaType="java.lang.Integer" jdbcType="INTEGER" />
<arg column="t_name" javaType="java.lang.String" jdbcType="VARCHAR" />
<arg column="t_phone" javaType="java.lang.String" jdbcType="VARCHAR" />
<arg column="t_address" javaType="java.lang.String" jdbcType="VARCHAR" />
<arg column="t_birthday" javaType="java.util.Date" jdbcType="TIMESTAMP" />
<arg column="t_des" javaType="java.lang.String" jdbcType="VARCHAR" />
</constructor>
</resultMap>
<insert id="addUser" parameterType="com.zxy.ImportExcelProject.pojo.User">
insert into user
(name,phone,address,birthday,des)
values
(
#{name},
#{phone},
#{address},
#{birthday},
#{des}
)
</insert>
<update id="updateUserByName" parameterType="com.zxy.ImportExcelProject.pojo.User">
update user
set
phone=#{phone},
address=#{address},
birthday=#{birthday},
des=#{des}
where name = #{name}
</update>
<select id="selectByName" resultType="java.lang.Integer">
SELECT
count(*)
FROM user
WHERE name=#{name}
</select>
</mapper>
3 接口测试
