eazyexcel 读取excel数据插入数据库

1  写监听器

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.ynsy.outhospital.entity.Area;
import com.ynsy.outhospital.service.AreaService;
import com.ynsy.wenku.service.VideoService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

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

/***
 *  监听器
 */
public class ExcelModelListener extends AnalysisEventListener<ExcelMode> {

    // 注入为null
    @Autowired
    private AreaService areaService;

    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;
    private static List<ExcelMode> list = new ArrayList<ExcelMode>();
    private static int count = 1;

    @Override
    public void invoke(ExcelMode data, AnalysisContext context) {
        System.out.println("解析到一条数据:{ " + data.toString() + " }");
        list.add(data);
        count++;
        if (list.size() >= BATCH_COUNT) {
            saveData(count);
            list.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData(count);
        System.out.println("所有数据解析完成！");
        System.out.println(" count ：" + count);
    }

    /**
     * 加上存储数据库
     */
    private void saveData(int count) {
        System.out.println("{ " + count + " }条数据，开始存储数据库！" + list.size());
        System.out.println("存储数据库成功！");
        List<Area>  list2 = new ArrayList<>();
        for (int i = 0; i < list.size(); i++) {
            ExcelMode excelMode = list.get(i);
            Area area = new Area();
            area.setId((long) Integer.parseInt(excelMode.getId()));
            area.setAreaName(excelMode.getArea_name());
            area.setParentId((long) Integer.parseInt(excelMode.getParent_id()));
            area.setLeve((long) Integer.parseInt(excelMode.getLevel()));
            area.setAreaCode(excelMode.getArea_code());
            area.setParentCode(excelMode.getParent_code());
            area.setCreateTime(new Date());
            area.setCreateUserId("1");
            area.setCreateUserName("admin");
            area.setUpdateTime(new Date());
            area.setUpdateUserId("1");
            area.setUpdateUserName("admin");
            area.setIsDelete(0L);
            list2.add(area);
        }
        //Object areaService = SpringContextUtil.getBean("areaService");  //没有该bean
        AreaService areaServiceImpl = (AreaService)SpringContextUtil.getBean("areaServiceImpl");
        Object contorller = SpringContextUtil.getBean("areaController");
        areaServiceImpl.saveBatch(list2);
    }

}

2 生产实体类，通用mapper

3 使用SpringContextUtil，读取service的实例，主要默认bean名字为  areaServiceImpl  或者 areaController

import org.springframework.beans.BeansException;
import org.springframework.beans.factory.NoSuchBeanDefinitionException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;


@Component
public class SpringContextUtil implements ApplicationContextAware {

    private static ApplicationContext applicationContext; // Spring应用上下文环境

    // 下面的这个方法上加了@Override注解，原因是继承ApplicationContextAware接口是必须实现的方法
    @Override
    public void setApplicationContext(ApplicationContext applicationContext)
            throws BeansException {
        SpringContextUtil.applicationContext = applicationContext;
    }

    public static ApplicationContext getApplicationContext() {
        return applicationContext;
    }

    public static Object getBean(String name) throws BeansException {
        return applicationContext.getBean(name);
    }

    public static Object getBean(String name, Class requiredType)
            throws BeansException {
        return applicationContext.getBean(name, requiredType);
    }

    public static boolean containsBean(String name) {
        return applicationContext.containsBean(name);
    }

    public static boolean isSingleton(String name) throws NoSuchBeanDefinitionException {
        return applicationContext.isSingleton(name);
    }

    public static Class getType(String name)    throws NoSuchBeanDefinitionException {
        return applicationContext.getType(name);
    }

    public static String[] getAliases(String name) throws NoSuchBeanDefinitionException {
        return applicationContext.getAliases(name);
    }
}

4  设置插入数据的值，全部设置有值，否则报错

解决办法：全部设置默认值

5 测试读取插入数据库：3700条数据耗时3秒