Java-POI上传并解析Excel文件到数据库

Excel格式

Excel依赖导入

<!-- easy excel 依赖-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
        </dependency>

创建JavaBean

• 数据库字段
  

• 根据数据创建javaBean

@Data
public class EduLevel {
	
	//自动生成UUID
    @TableId(value = "level_id",type = IdType.ASSIGN_UUID)
    private String levelId;
    private String levelName;
    private String parentLevel;
}

Vo层

• 根据 excel数据设置字段

package com.czxy.level.vo;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

@Data
public class LevelVo {
    @ExcelProperty("部门")
    private String deptName;
    @ExcelProperty("团队")
    private String teamName;
    @ExcelProperty("头衔")
    private String levelName;
}

通用mapper

package com.czxy.level.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.czxy.domain.EduLevel;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface EduLevelMapper extends BaseMapper<EduLevel> {
}

通用service

package com.czxy.level.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.czxy.domain.EduLevel;

public interface EduLevelService extends IService<EduLevel> {
}

实现类

package com.czxy.level.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.czxy.domain.EduLevel;
import com.czxy.level.mapper.EduLevelMapper;
import com.czxy.level.service.EduLevelService;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
@Transactional
public class EduLevelServiceImpl extends ServiceImpl<EduLevelMapper, EduLevel> implements EduLevelService {
}

controller层

package com.czxy.level.controller;

import com.alibaba.excel.EasyExcel;
import com.czxy.level.listener.LevelListener;
import com.czxy.level.vo.LevelVo;
import com.czxy.vo.BaseResult;
import com.sun.org.apache.regexp.internal.RE;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.IOException;
import java.util.List;

@RestController
@RequestMapping("/level")
public class EduLevelController2 {
    @Resource
    private LevelListener levelListener;


    //上传文件
    @PostMapping("/upload")
    public BaseResult upload(MultipartFile file) throws IOException {
        System.out.println(false);
        //sheet中的数据表明是excel中的第几个表（0 代表第一张 依次后延）
        EasyExcel.read(file.getInputStream(), LevelVo.class,levelListener).sheet(0).doRead();
        return BaseResult.ok("上传成功");
    }
}

监听器listener

package com.czxy.level.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.czxy.domain.EduLevel;
import com.czxy.level.service.EduLevelService;
import com.czxy.level.vo.LevelVo;
import org.aspectj.weaver.patterns.AnyAnnotationTypePattern;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;

@Component
public class LevelListener extends AnalysisEventListener<LevelVo> {
    @Resource
    private EduLevelService eduLevelService;

    @Override
    public void invoke(LevelVo levelVo, AnalysisContext analysisContext) {
        //1.添加部门


        QueryWrapper<EduLevel>deptQueryWrapper = new QueryWrapper<>();
        deptQueryWrapper.eq("parent_level","0");
        deptQueryWrapper.eq("level_name",levelVo.getDeptName());

        //1.1 根据条件判断为一级（部门信息）
        EduLevel dept = eduLevelService.getOne(deptQueryWrapper);
        //1.2 部门为空  就新添加部门
        if(dept==null){
            dept = new EduLevel();
            dept.setParentLevel("0");
            dept.setLevelName(levelVo.getDeptName());

            eduLevelService.save(dept);


        }

        //2.添加团队
        QueryWrapper<EduLevel>teamQueryWrapper = new QueryWrapper<>();
        teamQueryWrapper.eq("parent_level",dept.getLevelId());
        teamQueryWrapper.eq("level_name",levelVo.getTeamName());
        EduLevel team = eduLevelService.getOne(teamQueryWrapper);
        if(team==null){
            team = new EduLevel();
            team.setLevelName(levelVo.getTeamName());
            team.setParentLevel(dept.getLevelId());

            eduLevelService.save(team);
        }
        //3.添加头衔
        QueryWrapper<EduLevel>levelQueryWrapper = new QueryWrapper<>();
        levelQueryWrapper.eq("parent_level",team.getLevelId());
        levelQueryWrapper.eq("level_name",levelVo.getLevelName());
        EduLevel level = eduLevelService.getOne(levelQueryWrapper);
        if(level==null){
            level = new EduLevel();
            level.setLevelName(levelVo.getLevelName());
            level.setParentLevel(team.getLevelId());

            eduLevelService.save(level);
        }

    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("解析完成");
    }
}

上传成功

• 数据库信息