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秒