EasyExcel导入Excel文件，并对文件内容作校验
easyexcel
快速、简洁、解决大文件内存溢出的java处理Excel工具
项目地址：https://gitcode.com/gh_mirrors/ea/easyexcel
免费下载资源
·
首页是pom文件导入EasyExcel的依赖
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.2.0</version>
</dependency>mysql中添加三个字段做测试
自定义异常类
package com.example.demo.config;
import lombok.Data;
import lombok.EqualsAndHashCode;
@EqualsAndHashCode(callSuper = true)
@Data
public class ImportException extends RuntimeException {
private String msg;
private String code;
public ImportException() {
super();
}
public ImportException(String code, String message) {
super(message);
this.code = code;
this.msg = message;
}
public ImportException(String message) {
super(message);
this.msg = message;
}
public String getCode() {
return code;
}
public String getMessage() {
return msg;
}
}
package com.example.demo.config;
import com.example.demo.response.BaseResponse;
import com.example.demo.response.RespGenerator;
import org.apache.ibatis.logging.Log;
import org.apache.ibatis.logging.LogFactory;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.bind.annotation.RestControllerAdvice;
@RestControllerAdvice
public class GlobalExceptionHandler {
private static final Log logger = LogFactory.getLog(GlobalExceptionHandler.class);
@ExceptionHandler(ImportException.class)
public BaseResponse<Object> businessException(ImportException importException) {
return RespGenerator.fail(importException.getCode(),importException.getMsg());
}
}
BaseResponse
package com.example.demo.response;
import lombok.Data;
@Data
public class BaseResponse<T> {
private String code;
private String message;
private T data;
/**
*
* 默认构造方法
*
* @param code
* 状态码
* @param message
* 接口信息
* @param data
* 接口数据
*/
public BaseResponse(String code, String message, T data) {
super();
this.code = code;
this.message = message;
this.data = data;
}
/**
* 默认构造方法
*/
public BaseResponse() {
super();
}
}实体类User
其中value中的值跟Excel中的表头名称对应，index与excel中的表头位置对应
package com.example.demo.model.entity;
import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.extension.activerecord.Model;
import lombok.*;
import lombok.experimental.Accessors;
import java.io.Serializable;
@Data
@EqualsAndHashCode(callSuper = true)
//@Accessors(chain = true)
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class User extends Model implements Serializable {
private static final long serialVersionUID = 1L;
// value 通过标题文本对应 index 通过文本行号对应
@ExcelProperty(value = "id", index = 0)
@TableField("uid")
private String uid;
@ExcelProperty(value = "名称", index = 1)
@TableField("userName")
private String userName;
@ExcelProperty(value = "密码", index = 2)
@TableField("password")
private String password;
}
controller中
@Slf4j的依赖可自行百度导入
package com.example.demo.controller;
import com.example.demo.response.BaseResponse;
import com.example.demo.service.ImportService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
@RestController
@Slf4j
@RequestMapping(value = "/import")
public class ImportController {
@Resource
private ImportService importService;
/**
* 导入Excel
* @param file
* @return
*/
@PostMapping("/upLoad")
public BaseResponse upLoad(@RequestParam("file") MultipartFile file) {
return importService.importExcel(file);
}
}
service
import com.example.demo.response.BaseResponse;
import org.springframework.web.multipart.MultipartFile;
public interface ImportService {
BaseResponse importExcel(MultipartFile file);
}ImportServiceImpl
package com.example.demo.service.impl;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.example.demo.config.ImportException;
import com.example.demo.config.UserReadListener;
import com.example.demo.model.entity.User;
import com.example.demo.response.BaseResponse;
import com.example.demo.service.IUserService;
import com.example.demo.service.ImportService;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.ObjectUtils;
import org.springframework.web.multipart.MultipartFile;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
@Service
@Slf4j
public class ImportServiceImpl implements ImportService {
/**
* 每隔2条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
*/
private static final int BATCH_COUNT = 2;
@Resource
private IUserService userService;
@Transactional
@Override
public BaseResponse importExcel(MultipartFile file){
// 判断文件是否为空
if (ObjectUtils.isEmpty(file) || file.getSize() <= 0) {
throw new ImportException("上传文件大小为空");
}
// 获取文件名
String originalFilename = file.getOriginalFilename();
String s = originalFilename.toLowerCase();
String s1 = s.substring(s.indexOf('.') + 1).toLowerCase();
// 文件格式作校验
if (!"xlsx".equals(s1) && !"xls".equals(s1)) {
throw new ImportException("文件格式错误");
}
List<User> dataList = null;
try(InputStream inputStream = file.getInputStream()) {
// easyexcel导入文件 new UserReadListener：我们自己写的监听器
dataList = EasyExcel.read(inputStream, User.class, new UserReadListener()).sheet().doReadSync();
} catch (Exception e) {
log.error("导入文件失败：{}", e.getMessage(), e);
throw new ImportException("导入文件失败", e.getMessage());
}
log.info("{}条数据，开始存储数据库！", dataList.size());
// 使用mybatis plus 的批量新增方法
userService.saveBatch(dataList, BATCH_COUNT);
log.info("存储数据库成功！");
// 返回格式作拼接
BaseResponse response = new BaseResponse();
response.setCode("200");
response.setMessage("导入成功");
return response;
}
}
UserReadListener
重点在这个监听类中，我们如果需要校验数据，则在invoke方法中作数据校验
package com.example.demo.config;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.baomidou.mybatisplus.extension.activerecord.Model;
import com.example.demo.model.entity.User;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Component;
import java.util.ArrayList;
import java.util.List;
@Slf4j
@Component
public class UserReadListener<T extends Model> extends AnalysisEventListener<T> {
private static final int BATCH_COUNT = 5;
/**
* 自定义用于暂时存储data。
* 可以通过实例获取该值
*/
private final List<T> dataList = new ArrayList<>();
/**
* 每条数据都会调用一次这个方法
* @param analysisContext 可以获取当前读取的表格的相关信息
*/
@Override
public void invoke(T object, AnalysisContext analysisContext) {
log.info("invoke---开始校验表格数据");
validChoiceInfo(object, analysisContext);
log.info("invoke---开始解析表格数据");
dataList.add(object);
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
/*if (dataList.size() >= BATCH_COUNT) {
saveData(); // 持久化逻辑
// 存储完成清理 list
dataList.clear();
}*/
}
private static<T> void validChoiceInfo(T object, AnalysisContext context) {
// 泛型为用户
if(object instanceof User) {
//泛型转换为实体类型
Object temp = object;
User user = (User)temp;
if(StringUtils.isBlank(user.getUid())){
log.info("上传失败:第{}行ID信息为空",context.readRowHolder().getRowIndex());
throw new ImportException("上传失败:第"+context.readRowHolder().getRowIndex().toString()+"行ID信息为空");
}
if(StringUtils.isBlank(user.getUserName())){
log.info(String.format("上传失败:第{}行用户名信息为空",context.readRowHolder().getRowIndex()));
throw new ImportException(String.format("上传失败:第{}行用户名信息为空",context.readRowHolder().getRowIndex()));
}
if(StringUtils.isBlank(user.getPassword())){
log.info(String.format("上传失败:第{}行密码信息为空",context.readRowHolder().getRowIndex()));
throw new ImportException(String.format("上传失败:第{}行密码信息为空",context.readRowHolder().getRowIndex()));
}
}
}
/**
* 所有数据解析完成会来调用
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
// 这里也要保存数据，确保最后遗留的数据也存储到数据库
log.info("doAfterAllAnalysed---开始处理");
if(dataList.isEmpty() || dataList.size() == 0) {
throw new ImportException("上传失败，Excel中无数据");
}
log.info("所有数据解析完成！");
}
public List<T> getData() {
return dataList;
}
}
IUserService
package com.example.demo.service;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.example.demo.model.entity.User;
import com.baomidou.mybatisplus.extension.service.IService;
import com.example.demo.model.bo.UserDetailBO;
import com.example.demo.model.bo.UserLoginBO;
import com.example.demo.model.vo.GetUserVO;
import com.example.demo.response.BaseResponse;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletRequest;
import java.io.IOException;
/**
* <p>
* 服务类
* </p>
*
* @since 2023-03-16
*/
public interface IUserService extends IService<User> {
}
UserServiceImpl
package com.example.demo.service.impl;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.demo.config.BaseErrorEnum;
import com.example.demo.config.BaseException;
import com.example.demo.model.entity.User;
import com.example.demo.mapper.UserMapper;
import com.example.demo.model.bo.UserDetailBO;
import com.example.demo.model.bo.UserLoginBO;
import com.example.demo.model.vo.GetUserVO;
import com.example.demo.service.IUserService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.demo.utils.RedisUtils;
import com.example.demo.utils.TokenUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.concurrent.TimeUnit;
/**
* <p>
* 服务实现类
* </p>
* @since 2023-03-16
*/
@Service
@Slf4j
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements IUserService {
}
UserMapper
package com.example.demo.mapper;
import com.example.demo.model.entity.User;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
/**
* <p>
* Mapper 接口
* </p>
*
* @since 2023-03-16
*/
@Mapper
public interface UserMapper extends BaseMapper<User> {
}
postman测试，选择文件
导入成功
GitHub 加速计划 / ea / easyexcel
31.64 K
7.47 K
下载
快速、简洁、解决大文件内存溢出的java处理Excel工具
最近提交(Master分支：3 个月前 )
c42183df
Bugfix 3 个月前
efa7dff6 * 重新加回 `commons-io`
3 个月前
旨在为数千万中国开发者提供一个无缝且高效的云端环境，以支持学习、使用和贡献开源项目。
更多推荐
2
0- 0
已为社区贡献1条内容
所有评论(0)