EasyExcel批量导入数据

EasyExcel批量导入数据

easyExcel官网：https://easyexcel.opensource.alibaba.com/

一. 导入pom依赖：

```<!--excel导入导出-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.1</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.83</version>
        </dependency>

二. 监听器（用来解析excel文件内容）

@Slf4j
@Component
public class ExcelListener implements ReadListener<UserVo> {

    //日志
    private static final Log logger = LogFactory.getLog(ExcelListener.class);

    private UserService userService;

    /**
     * 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 10000;

    /**
     * 缓存的数据
     */
    private List<UserVo> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

    /**
     * 多线程缓存数据，需要不会被多线程影响的变量
     */
    private ThreadLocal<ArrayList<UserVo>> cachedDataListAsync = ThreadLocal.withInitial(ArrayList::new);

    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     */
    public ExcelListener(UserService userService) {
        this.userService = userService;
    }


    @Override
    public void invoke(UserVo userVo, AnalysisContext analysisContext) {
        //解析多条数据，统一执行
        log.info("解析到一条数据（多线程）:{}", JSON.toJSONString(userVo));
        cachedDataListAsync.get().add(userVo);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (cachedDataListAsync.get().size() >= BATCH_COUNT) {
            //单线程插入
            saveData();
        }


    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        //数据解析完，执行方法
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        log.info("所有数据解析完成！");
    }


    public void saveData() {
        log.info("{}条数据，开始存储数据库！");
        long wasteTimeStart = System.currentTimeMillis();
        System.out.println("开始时间" + wasteTimeStart);

        //mybatis-plus批量保存（mybatis-plus的批量保存）
        userService.saveBatch(cachedDataListAsync.get(), cachedDataListAsync.get().size());
        cachedDataListAsync.get().clear();

        long wasteTimeEnd = System.currentTimeMillis();
        System.out.println("结束时间" + wasteTimeEnd);
        log.info("存储数据库成功！保存数据库时间：" + (wasteTimeEnd - wasteTimeStart));
    }
}

三. Controller

@Slf4j
@RestController
@RequestMapping("/user")
public class UserConroller {

    @Autowired
    private ExcelListener excelListener;

    //创建20个线程的线程池
    private ExecutorService executorService = Executors.newFixedThreadPool(20);

    /**
     * excel文件上传(多线程处理)
     *
     * @param file
     * @return
     */
    @PostMapping("/uploadExcelAsync")
    public void uploadFileByEasyExcelUserInfoAsync(@RequestParam("file") MultipartFile file) {
        //开启20个线程分别导入
        ArrayList<Callable<Object>> tasks = new ArrayList<>();
        for (int i = 0; i < 3; i++) {
            int num = i;
            tasks.add(() -> {
                EasyExcel.read(file.getInputStream(), UserVo.class, excelListener).sheet(num).doRead();
                return null;
            });
        }
        try {
            // 调用该方法的线程会阻塞,直到tasks全部执行完成(正常完成/异常退出)
            executorService.invokeAll(tasks);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
    }
}

四. services

@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, UserVo> implements UserService {
//调用mybatis-plus中的saveBatch，批量保存（集成mybatis-plus，然后继承ServiceImpl直接用）
}

五.Vo`

@Data
@TableName("user")
public class UserVo implements Serializable {
    @ExcelProperty(value = "id")
    private Integer id;
    @ExcelProperty(value = "姓名")
    private String mingzi;

    //@ExcelProperty(value = "性别",converter = SexTypeConvert.class)
    @ExcelProperty(value = "性别")
    private String sex;
    @ExcelProperty(value = "地址")
    private String address;
    @ExcelProperty(value = "国家")
    private String country;
    @ExcelProperty(value = "民族")
    private String minzu;
    @ExcelProperty(value = "电话")
    private String phone;
    @ExcelProperty(value = "生日",format = "yyyy-MM-dd")
    private Date birthday;
    @ExcelProperty(value = "录入时间",format = "yyyy-MM-dd")
    private Date createtime;
}

六. 生成测试数据

#删除存储过程
DROP PROCEDURE IF EXISTS insert_while;

#创建存储过程
delimiter //
	CREATE PROCEDURE insert_while()
	BEGIN
		DECLARE i INT;
		DECLARE tempUUID text;
		SET i = 1;
		WHILE i <= 100000 DO
			INSERT INTO `user`(`mingzi`, `address`, `birthday`, `country`, `phone`, `minzu`, `sex`, `createtime`)
			VALUES(CONCAT('外包', i),"中国上海", now(), '中国', '15263852536',"汉",i%2,now());
			SET i = i + 1;
		END WHILE;
	END //
delimiter;

#执行存储过程
CALL insert_while();

#删除存储过程
DROP PROCEDURE IF EXISTS insert_while;