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 {

    @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测试，选择文件        

 导入成功