[Java知识库]SpringBoot，EasyExcel导入xlsx文件

实体类

/**
 * 前端Excel导入数据库对应实体
 * @author
 * @date 2022-03-10
 */
@Data
@TableName("")
//一定要实现序列化，防止内存溢出
public class EquipmentExcel implements Serializable {

    /**
     * 设备编码
     */
    @ExcelProperty(value = {"所属部门"})
    @TableField("dept_guid")
    private String deptGuid;
}

控制层

   @ControllerEndpoint(operation = "上传设备数据", exceptionMessage = "导入Excel失败")
   /*consumes： 指定处理请求的提交内容类型（Content-Type），
    例如:
    application/xml
	application/json
	application/x-www-form-urlencoded
	multipart/form-data
	application/octet-stream
	text/plain
	text/xml
	text/html*/
    @PostMapping(value = "/uploadExcel", consumes = "multipart/*", headers = "content-type=multipart/form-data")
    @ResponseBody
    public List<String> uploadEquipmentInfo(MultipartFile file) throws IOException {
      return equipmentService.uploadEquipmentInfo(file,deviceCategory,deviceType,status,user);
    }

业务层

 try{
            EasyExcel.read(file.getInputStream(), EquipmentExcel.class, new ExcelModelListener(baseMapper,deviceCategory)).sheet().doRead();
        }catch (Exception e) {
            StringTokenizer st = new StringTokenizer(e.getMessage(),"|");
            while (st.hasMoreElements()) {
                Message.add(st.nextElement().toString());
            }
        }finally {
            return Message;
        }

监听器

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelAnalysisException;
import com.alibaba.excel.exception.ExcelDataConvertException;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
import java.text.MessageFormat;
import java.util.*;

public class ExcelModelListener extends AnalysisEventListener<EquipmentExcel> {

    /**
     * 每隔100条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    // 读取三百条数据，批量插入一次。
    private static final int BATCH_COUNT = 300;
    // 记录Excel读取的行数
    private int COUNT = 0;

    // 记录Excel重复的行数
    private int duplicateCOUNT = 0;

    //保存读取成功的Excel数据
    private List<EquipmentExcel> list = new ArrayList<>();

    //保存读取失败的Excel消息
    private List<String> errorMessage = new ArrayList<>();

    //DAO
    private EquipmentMapper baseMapper;

    // 设备类别（有线设备，NB设备，消防主机，用户传输装置）
    private List<DictValue> deviceCategory = new ArrayList<>();
    

    //初始化监听器
    public ExcelModelListener(EquipmentMapper baseMapper, List<DictValue> deviceCategory, List<DictValue> deviceType, List<DictValue> status, CurrentUser user,List Tdept,List Tguiddata) {
        this.baseMapper = baseMapper;
        this.deviceCategory = deviceCategory;
    }


    //解析到一条数据,对每条数据进行操作
    @Override
    public void invoke(EquipmentExcel data, AnalysisContext context) {

        this.COUNT = this.COUNT + 1;

        //     判断字段长度和字段为空
        checkLengthAndNull(data);
        // 检查自定义字符串格式,将字符串转化成字典id,绑定默认值
        checkDiy(data);
        // 映射字段
        //添加到集合
        list.add(data);
        if (list.size() >= BATCH_COUNT) {
            saveData();
            list.clear();
        }
    }

    /**
     * 读取所有数据完毕进行的操作
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();

        StringBuilder sb = new StringBuilder();
        String countMessage = MessageFormat.format(
                "一共{0}条数据，成功导入{1}条数据，重复{2}条数据，失败{3}条数据:",
                this.COUNT, this.COUNT - this.duplicateCOUNT - this.errorMessage.size(),
                duplicateCOUNT, this.errorMessage.size());
        sb.append(countMessage);
        //将错误信息用字符串返回
        if (this.errorMessage.size() > 0) {
            errorMessage.forEach((it) -> {
                sb.append("|");
                sb.append(it);
            });
        }
        throw new ExcelAnalysisException(sb.toString());
    }

    /**
     * 获取表头名
     *
     * @param headMap
     * @param context
     */
    @Override
    public void invokeHeadMap(Map headMap, AnalysisContext context) {
        this.headMap = headMap;
    }


    /**
     * 在转换异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。
     *
     * @param exception
     * @param context
     * @throws Exception
     */
    @Override
    public void onException(Exception exception, AnalysisContext context) {

        if (exception instanceof ExcelDataConvertException) {
            //转换格式发生的异常,不会进入invoke解析,这里要++ 把解析失败数据也加上
            this.COUNT=this.COUNT+1;
            ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
            // 获取列名
            String columnName = (String) this.headMap.get(excelDataConvertException.getColumnIndex());
            // 获取行
            int row = excelDataConvertException.getRowIndex();
            // 获取出错的单元块
            String cellData = excelDataConvertException.getCellData().toString();
            String mes = MessageFormat.format("错误发生在{0}行，{1}列，数据为:{2}", (row+1), columnName, cellData);
            errorMessage.add(mes);
        }
        // 自定义抛出的异常
        else {
            errorMessage.add(exception.getMessage());
        }
    }

    /**
     * 具体执行读取到的excel数据进行存储的逻辑，比如写入到mysql等操作
     */

    private void saveData() {
        //一次性插入多条，自定义sql语句
        if (!CollectionUtils.isEmpty(list)) {
            //去重前数据条数
            int originSize = list.size();
            //  调用Excel内部数据去重方法
            list = duplicateRemovalExcel(list);
            //调用数据库内部数据去重方法
            duplicateRemoval(list);
            //去重后数据条数
            int duplicateSize = list.size();
            this.duplicateCOUNT = this.duplicateCOUNT + (originSize - duplicateSize);

        }
        //再判断一次，防止去重完毕List为空,插入数据库报错
        if(!CollectionUtils.isEmpty(list)) {
            //最后插入数据
            //插入数据库要不要捕捉异常
            baseMapper.insertexcel(list);
        }

    }

    //    自定义异常
    private void throwException(String message) {
        String mes = MessageFormat.format("错误发生在{0}行:{1}", this.COUNT+1,message);
        throw new ExcelAnalysisException(mes);
    }



    //     检查字段长度和字段为空
    private void checkLengthAndNull(EquipmentExcel data) {

        // 如果整行数据为空
        if (data == null) {
            throwException("整行数据为空");
        }
     
    //数据库中的数据去重
    private List<EquipmentExcel> duplicateRemoval(List<EquipmentExcel> data) {
        List<String> dbDeviceSource = baseMapper.getAllDeviceSource();

//        不能用foreach 迭代删除  java.util.ConcurrentModificationException
        for (Iterator<EquipmentExcel> i = data.iterator(); i.hasNext(); ) {
            EquipmentExcel equipmentExcel = i.next();
            for (Iterator<String> j = dbDeviceSource.iterator(); j.hasNext(); ) {
                String deviceSource = j.next();
                if (equipmentExcel.getDeviceSource().equals(deviceSource)) {
                    i.remove();
                }
            }
        }
        return data;
    }

    //    先把Excel中重复的数据去除
    private List<EquipmentExcel> duplicateRemovalExcel(List<EquipmentExcel> list) {
        Set<EquipmentExcel> EquipmentExcelSet = new TreeSet<>((o1, o2) -> o1.getDeviceSource().compareTo(o2.getDeviceSource()));
        EquipmentExcelSet.addAll(list);
        return new ArrayList<>(EquipmentExcelSet);
    }
}

创作打卡挑战赛

赢取流量/现金/CSDN周边激励大奖