导入多个sheet页到多张表(2023/4/7)修订版

控制层

 @PostMapping("test/excel/import")
    public void modelImport(MultipartFile serviceFile) throws IOException {
        //输入流
        InputStream inputStream = serviceFile.getInputStream();
        //监视器
        ExcelListener listener = new ExcelListener();
        ExcelReader excelReader = EasyExcel.read(inputStream, listener).build();
        // 第一个sheet读取类型
        ReadSheet readSheet1 = EasyExcel.readSheet(0).head(DictEeVo.class).build();
        // 第二个sheet读取类型
        ReadSheet readSheet2 = EasyExcel.readSheet(1).head(ExpertVo.class).build();
        // 开始读取第一个sheet
        excelReader.read(readSheet1);
        //excel sheet0 信息
        List<Object> list = listener.getDatas();
        //List<object> 转 List<实体类>
        List<Dict> dtoList = new ArrayList<>();
        //List object for 转换 实体类
        for (Object objects : list) {
            DictEeVo dto = (DictEeVo) objects;
            Dict dict = new Dict();
            BeanUtil.copyProperties(dto,dict);
            dtoList.add(dict);
        }
        //List 转JOSN
        String json = JSON.toJSONString(dtoList);
        System.out.println("json = " + json);
        //保存第一个sheet页中的数据
        dictService.saveBatch(dtoList);
        // 清空之前的数据
        listener.getDatas().clear();

        // 开始读取第二个sheet
        excelReader.read(readSheet2);
        //excel sheet1 信息
        List<Object> entry = listener.getDatas();
        //copy上面作法

        //List<object> 转 List<实体类>
        List<Dict> dtoList22 = new ArrayList<>();
        //List object for 转换 实体类
        List<ExpertVo> dtoList1 = new ArrayList<>();
        //List object for 转换 实体类
        for (Object objects : list) {
            ExpertVo dto = (ExpertVo) objects;

            dtoList1.add(dto);
        }


        //保存第二个sheet页中的数据//这次插入别的表 t_test_copy1
        dictService.saveBatcht_test_copy1(dtoList1);
        //List 转JOSN
        String json1 = JSON.toJSONString(dtoList);
        System.out.println("json2222 = " + json1);

    }

业务层

void saveBatcht_test_copy1(List<ExpertVo> dtoList1);

业务层实现类

@Override
public void saveBatcht_test_copy1(List<ExpertVo> dtoList1) {
    dictMapper.saveBatcht_test_copy1(dtoList1);
}

mapper

void saveBatcht_test_copy1(@Param("List") List<ExpertVo> dtoList1);

xml

 <insert id="saveBatcht_test_copy1">
        insert into t_test_copy1(id,name,date) values
        <foreach collection="List" item="item" separator=",">
            (#{item.id},#{item.name},#{item.date})
        </foreach>
    </insert>

postman

 

excel结构

 

 

执行的sql

 

数据库页面状态

 

 

监听器

package com.atguigu.yygh.cmn.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

import java.util.ArrayList;
import java.util.List;

/**
 * excel表格读取监视器
 */
public class ExcelListener extends AnalysisEventListener {
    //可以通过实例获取该值
    private List<Object> datas = new ArrayList<Object>();

    public void invoke(Object o, AnalysisContext analysisContext) {
        datas.add(o);
        doSomething(o);
    }

    private void doSomething(Object object) {
    }

    public List<Object> getDatas() {
        return datas;
    }

    public void setDatas(List<Object> datas) {
        this.datas = datas;
    }

    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
    }
}

两个vo