EasyExcel导入Excel文件，并对文件内容作校验
首页是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 {@Resourceprivate 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;@Resourceprivate IUserService userService;@Transactional@Overridepublic 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 可以获取当前读取的表格的相关信息*/@Overridepublic 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(); // 持久化逻辑// 存储完成清理 listdataList.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()));}}}/* 所有数据解析完成会来调用*/@Overridepublic 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测试，选择文件
导入成功
