[EasyExcel]: 使用AnalysisEventListener回调监听器读取Excel
easyexcel
快速、简洁、解决大文件内存溢出的java处理Excel工具
项目地址:https://gitcode.com/gh_mirrors/ea/easyexcel
免费下载资源
·
Pom.xml
<!-- EasyExcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.3.2</version>
</dependency>
Controller:
@PostMapping("/readExcel")
public R<String> readExcelToDB(@RequestParam(value = "multipartFile") MultipartFile multipartFile) {
// 非管理员禁止调用
if (!tokenService.getLoginUser(ServletUtils.getRequest()).getUser().getNickName().equals("admin")) {
log.info(tokenService.getLoginUser(ServletUtils.getRequest()).getUser().getNickName() + "非法调用此接口");
throw new ServiceException("参数异常");
}
try (InputStream inputStream = multipartFile.getInputStream()) {
return skywingUserService.readExcelToDB(multipartFile.getName(), inputStream);
} catch (Exception e) {
log.error("读取 " + multipartFile.getName() + " 文件失败, 原因: {}", e.getMessage());
throw new ServiceException("读取 " + multipartFile.getName() + " 文件失败");
}
}
Service:
import com.alibaba.excel.EasyExcel;
@Override
public R<String> readExcelToDB(String fileName, InputStream inputStream) {
try {
// 读取文件内容
EasyExcel.read(inputStream, RegisterInfoExcel.class, new RegisterInfoExcelListener(sysUserMapper, meetZoomuserinfoTabMapper)).sheet().doRead();
return R.ok("读取Excel文件成功");
} catch (Exception e) {
log.error("读取 " + fileName + " 文件失败, 原因: {}", e.getMessage());
}
return R.ok();
}
Listener
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.util.ListUtils;
import org.apache.commons.lang3.ObjectUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.List;
/**
* 读取注册用户信息Excel表并写入DB
*
* @author 毛毛
*/
public class RegisterInfoExcelListener extends AnalysisEventListener<RegisterInfoExcel> {
private static final Logger log = LoggerFactory.getLogger(RegisterInfoExcelListener.class);
private static final int BATCH_COUNT = 100;
// 由于@Autowired注解无法注入, 所以使用参数的方式注入
private final SysUserMapper sysUserMapper;
// 由于@Autowired注解无法注入, 所以使用参数的方式注入
private final MeetZoomuserinfoTabMapper meetZoomuserinfoTabMapper;
private List<RegisterInfoExcel> cacheDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
public RegisterInfoExcelListener(SysUserMapper sysUserMapper, MeetZoomuserinfoTabMapper meetZoomuserinfoTabMapper) {
this.sysUserMapper = sysUserMapper;
this.meetZoomuserinfoTabMapper = meetZoomuserinfoTabMapper;
}
/**
* 批量读取Excel写入DB
*
* @param registerInfoExcel 全局监听内容
* @param analysisContext 读取到的Excel内容
*/
@Override
public void invoke(RegisterInfoExcel registerInfoExcel, AnalysisContext analysisContext) {
// 将监听到的数据存入缓存集合中
cacheDataList.add(registerInfoExcel);
// 批量处理缓存的数据
if (cacheDataList.size() >= BATCH_COUNT) {
saveToDB();
cacheDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
}
/**
* 处理结尾, 不足100条
*
* @param analysisContext 全局监听内容
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
saveToDB();
log.info(analysisContext.getCustom().toString() + "所有数据解析完成");
}
/**
* 将读取到的内容写入DB
*/
private void saveToDB() {
for (RegisterInfoExcel userInfoExcel : cacheDataList) {
// 若该用户已存在, 则更新
MeetZoomuserinfoTab registeredUser = meetZoomuserinfoTabMapper.selectByPhone(userInfoExcel.getPhone());
if (ObjectUtils.isNotEmpty(registeredUser)) {
try {
// 更新nid
if (!registeredUser.getZoomcode().equals(userInfoExcel.getAccount())) {
registeredUser.setZoomcode(userInfoExcel.getAccount());
// 更新DB
meetZoomuserinfoTabMapper.updateMeetZoomuserinfoTab(registeredUser);
log.info(userInfoExcel.getUserName() + " 更新数据库成功\n");
}
} catch (Exception e) {
log.error(userInfoExcel.getUserName() + " 更新DB失败, 原因: \n" + e.getMessage());
}
} else { // 该用户不存在
// 根据手机号 和 用户姓名查询用户信息
SysUser userInfo = sysUserMapper.getByUserNameAndPhone(userInfoExcel.getUserName(), userInfoExcel.getPhone());
if (ObjectUtils.isNotEmpty(userInfo)) {
// 构建用户注册信息
MeetZoomuserinfoTab registerInfo = MeetZoomuserinfoTab.builder()
.userid(String.valueOf(userInfo.getUserId()))
.hostid(userInfo.getNickName())
.zoomcode(userInfoExcel.getAccount())
.email(userInfo.getEmail())
.role("普通角色")
.dept("XXX分公司")
.type(1)
.phone(userInfo.getPhonenumber())
.build();
// 写入注册信息表
try {
meetZoomuserinfoTabMapper.insertMeetZoomuserinfoTab(registerInfo);
log.info(userInfo.getUserName() + " 写入数据库成功\n");
} catch (Exception e) {
log.error(userInfo.getUserName() + " 写入DB失败, 原因: \n" + e.getMessage());
}
} else {
log.error("\n" + userInfoExcel.getUserName() + " 用户不存在");
}
}
}
}
}
Domain
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import java.util.Date;
/**
* 导入用户信息-Excel表对象
* @author 毛毛
*/
@Data
@EqualsAndHashCode
public class RegisterInfoExcel {
/**
* 部门或组织全路径
*/
@ExcelProperty("部门或组织全路径")
private String deptPath;
/**
* 用户名
*/
@ExcelProperty("用户名")
private String userName;
/**
* 手机号
*/
@ExcelProperty("手机号")
private String phone;
/**
* 邮箱
*/
@ExcelProperty("邮箱")
private String email;
/**
* 账号
*/
@ExcelProperty("账号")
private String account;
/**
* 加入企业时间
*/
@ExcelProperty("加入企业时间")
private Date timeOfFirm;
}
GitHub 加速计划 / ea / easyexcel
31.63 K
7.47 K
下载
快速、简洁、解决大文件内存溢出的java处理Excel工具
最近提交(Master分支:2 个月前 )
c42183df
Bugfix 1 个月前
efa7dff6 * 重新加回 `commons-io`
1 个月前
更多推荐
已为社区贡献1条内容
所有评论(0)