EasyExcel导入多个sheet页码,将数据存储到数据库

控制层

 @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<Dict> dtoList1 = new ArrayList<>();
        //List object for 转换 实体类
        for (Object objects : list) {
            ExpertVo dto = (ExpertVo) objects;
            Dict dict = new Dict();
            BeanUtil.copyProperties(dto,dict);
            dtoList1.add(dict);
        }


        //保存第二个sheet页中的数据
        dictService.saveBatch(dtoList1);
        //List 转JOSN
        String json1 = JSON.toJSONString(dtoList);
        System.out.println("json2222 = " + json1);

    }

业务层

万能监听器

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) {
    }
}

javabean

package com.atguigu.yygh.vo.cmn;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import lombok.Data;

import java.util.Date;

/**
 * <p>
 * Dict
 * </p>
 *
 * @author qy
 */
@Data
public class ExpertVo {

	@ExcelProperty(value = "id")
	@ExcelIgnore
	private Long id;

	@ExcelProperty(value = "名称")
	private String name;

	@ExcelProperty(value = "日期")
	private Date date;

}

 

postman

excel中的数据结构

 

 

 

数据库结果