java 把execl列表的数据导入数据库

框架为：springboot+mybatis+mysql

mysql：数据库的建表：

CREATE TABLE `sunchao` (
                           `id` bigint(20) NOT NULL AUTO_INCREMENT,
                           `parent_id` int(11) DEFAULT NULL,
                           `name` varchar(200) DEFAULT NULL,
                           `value` varchar(200) DEFAULT NULL,
                           `dict_code` varchar(200) DEFAULT NULL,
                           PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=82170 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='数据字典';

1.先看下execl表中的数据

 2.在java dao层创建于之对应的属性

package com.ecc.srb.core.pojo.dto;

import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

/**
 * @author sunyc
 * @create 2022-07-07 9:41
 */
@Data //简化开发实体类
@TableName(value = "sunchao") //对应数据的表
public class ExeclSunchao {
    @ExcelProperty("id")
    private Long id;
    @ExcelProperty("上级id")
    private Long parentId;
    @ExcelProperty("名称")
    private String name;
    @ExcelProperty("值")
    private Integer value;
    @ExcelProperty("编码")
    private String dictCode;
}

3.创建接口的mapper

package com.ecc.srb.core.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ecc.srb.core.pojo.dto.ExeclSunchao;
import sun.print.BackgroundServiceLookup;

/**
 * @author sunyc
 * @create 2022-07-07 9:42
 */
public interface ExeclSunchaoMapper extends BaseMapper<ExeclSunchao> {
}

4.创建接口的service

package com.ecc.srb.core.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.ecc.srb.core.pojo.dto.ExeclSunchao;

import java.io.InputStream;

/**
 * @author sunyc
 * @create 2022-07-07 9:43
 */
public interface ExeclSunchaoService extends IService<ExeclSunchao> {
    //文件导入的接口
    void importFile(InputStream inputStream);
}

5.service的实现类

package com.ecc.srb.core.service.impl;

import com.alibaba.excel.EasyExcel;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.ecc.srb.core.listener.ExecDictDtoListener;
import com.ecc.srb.core.listener.ExeclSunchaoListener;
import com.ecc.srb.core.mapper.ExeclSunchaoMapper;
import com.ecc.srb.core.pojo.dto.ExecDictDto;
import com.ecc.srb.core.pojo.dto.ExeclSunchao;
import com.ecc.srb.core.service.ExeclSunchaoService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.io.InputStream;

/**
 * @author sunyc
 * @create 2022-07-07 9:44
 */
@Service
@Slf4j
public class ExeclSunchaoServiceImpl extends ServiceImpl<ExeclSunchaoMapper, ExeclSunchao> implements ExeclSunchaoService {

    @Transactional(rollbackFor = {Exception.class})
    @Override //文件导入接口的实现类
    public void importFile(InputStream inputStream) {
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        System.out.println("ExeclSunchaoServiceImpl .importData,,Execl 导入的第2步--------------开始解析");
        EasyExcel.read(inputStream, ExeclSunchao.class,new ExeclSunchaoListener(baseMapper)).sheet().doRead();
        log.info("importData finished");
    }
}

6.创建监听，这是主要的解析execl文件的接口

package com.ecc.srb.core.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.ecc.srb.core.mapper.DictMapper;
import com.ecc.srb.core.mapper.ExeclSunchaoMapper;
import com.ecc.srb.core.pojo.dto.ExecDictDto;
import com.ecc.srb.core.pojo.dto.ExeclSunchao;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;

/**
 * @author sunyc
 * @create 2022-07-07 9:46
 */
@Slf4j
@NoArgsConstructor //无参构造器
public class ExeclSunchaoListener extends AnalysisEventListener<ExeclSunchao> {
    
    private ExeclSunchaoMapper execlSunchaoMapper;
    //传入mapper对象
    public ExeclSunchaoListener(ExeclSunchaoMapper execlSunchaoMapper){
        this.execlSunchaoMapper=execlSunchaoMapper;
    }
    /**
     *遍历每一行的记录
     */
    @Override
    public void invoke(ExeclSunchao data, AnalysisContext analysisContext) {
        System.out.println("ExeclSunchaoListener .invoke,,Execl 导入的第3步--------------开始导入");
        log.info("解析到一条记录:"+data);
        execlSunchaoMapper.insert(data);
    }
    /**
     * 所有数据解析完成了 都会来调用
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("ExeclSunchaoListener .doAfterAllAnalysed,,Execl 导入的第4步--------------完成导入");
        log.info("所有数据解析完成！");
    }
}

7.控制类

package com.ecc.srb.core.controller.admin;

import com.ecc.common.exception.BusinessException;
import com.ecc.common.result.R;
import com.ecc.common.result.ResponseEnum;
import com.ecc.srb.core.service.ExeclSunchaoService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;

/**
 * @author sunyc
 * @create 2022-07-07 9:50
 */
@Api(tags = "导入execl表的数据")
@RequestMapping("/admin/sunchao")
@Slf4j
@RestController
@CrossOrigin
public class AdminExeclSunchaoController {

    @Autowired
    ExeclSunchaoService execlSunchaoService;

    @PostMapping("/importfile")
    @ApiOperation("Execl 批量导入数据字典")
    public R importFile(@ApiParam(value = "Execl 文件",required = true)
                        @RequestParam("file")MultipartFile file){

        //获取一个文件输入流
        try {
            InputStream inputStream=file.getInputStream();
            execlSunchaoService.importFile(inputStream);
            return R.ok().message("字典数据导入成功");
        } catch (IOException e) {
            //return e.printStackTrace();
            throw new BusinessException(ResponseEnum.UPLOAD_ERROR,e);
        }
    }

}

8.配置swagger2测试工具

配置依赖

        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>2.9.2</version>
        </dependency>
  
        <!--swagger ui-->
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
        </dependency>

配置swagger2配置类

package com.ecc.srb.base.config;

import com.google.common.base.Predicates;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import springfox.documentation.builders.ApiInfoBuilder;
import springfox.documentation.builders.PathSelectors;
import springfox.documentation.service.ApiInfo;
import springfox.documentation.service.Contact;
import springfox.documentation.spi.DocumentationType;
import springfox.documentation.spring.web.plugins.Docket;
import springfox.documentation.swagger2.annotations.EnableSwagger2;

/**
 * @author sunyc
 * @create 2022-06-30 13:54
 */
@Configuration
@EnableSwagger2
public class Swagger2config {
    @Bean
    public Docket adminApiConfig(){

        return new Docket(DocumentationType.SWAGGER_2)
                .groupName("adminApi")
                .apiInfo(adminApiInfo())
                .select()
                //只显示admin路径下的页面
                .paths(Predicates.and(PathSelectors.regex("/admin/.*")))
                .build();

    }
//http://localhost:8110/swagger-ui.html
    private ApiInfo adminApiInfo(){

        return new ApiInfoBuilder()
                .title("ecc后台管理系统-API文档")
                .description("本文档描述了ecc后台管理系统接口")
                .version("1.0")
                .contact(new Contact("Helen", "http://ecc.com", "99999@qq.com"))
                .build();
    }
}

进入页面：http://localhost:8110/swagger-ui.html

 

 去数据库查询看结果：