Springboot使用EasyExcel讀取大量數(shù)據(jù)并且將得到的數(shù)據(jù)分批插入到數(shù)據(jù)庫(kù)中

???????
????????????com.alibaba
????????????easyexcel
????????????2.1.6
????????

????@ExcelProperty(value?=?"BIRTHDAY",?index?=?5)
????@DateTimeFormat(pattern?=?"yyyy-MM-dd?HH:mm:ss")
????private?Date?birthday;

//創(chuàng)建的監(jiān)聽(tīng)器必須繼承?AnalysisEventListener，泛型為剛剛創(chuàng)建的實(shí)體類
public?class?ExcelListener?extends?AnalysisEventListener?{
//由于數(shù)據(jù)是按行讀取，所以此處需要?jiǎng)?chuàng)建兩個(gè)靜態(tài)變量來(lái)實(shí)現(xiàn)批量插入
????public??static??int?vipCount;
//EasyExcel不能使用Spring注解，所以直接創(chuàng)建一個(gè)對(duì)應(yīng)的Service（這個(gè)Service就是Controller中所調(diào)用的Service）
????public?AddVipsServiceTest?addVipsService;
????public??static?List?list=new?ArrayList();

????public?ExcelListener()?{
????}
//有參構(gòu)造
????public?ExcelListener(AddVipsServiceTest?addVipsServiceTest)?{
????????this.addVipsService?=?addVipsServiceTest;
????}
????//此處為得到一行后的數(shù)據(jù)轉(zhuǎn)換為實(shí)體類
????@Override
????public?void?invoke(Users?users,?AnalysisContext?context)?{
????//添加到list中
????????list.add(users);
????????vipCount++;
????????//得到當(dāng)前操作表格的所有行數(shù)，由于行數(shù)包含了表頭，所以需要減一為所有數(shù)據(jù)的條目數(shù)
????????Integer?a?=context.getTotalCount()-1;
????????//讀取的行數(shù)到行尾時(shí)，將剩下的數(shù)據(jù)全部插入到數(shù)據(jù)庫(kù)中
????????if?(a==vipCount){
//????????????System.out.println(list);
????????????addVipsService.addVip(list);
????????}
????????//每當(dāng)list中存儲(chǔ)的條目數(shù)達(dá)到2000條時(shí)，批量插入到數(shù)據(jù)庫(kù)中，并清空當(dāng)前l(fā)ist的數(shù)據(jù)
????????if?(list.size()%2000==0){
//????????????System.out.println(list);
????????????addVipsService.addVip(list);
//????????????System.out.println(2);
????????????list.clear();
????????}
????}

????//最開(kāi)始執(zhí)行的函數(shù)，可以返回表頭的數(shù)據(jù)
????@Override
????public?void?invokeHeadMap(Map?headMap,?AnalysisContext?context)?{
????????System.out.println(headMap);
????}

????//全部讀取完成之后執(zhí)行函數(shù)
????@Override
????public?void?doAfterAllAnalysed(AnalysisContext?context)?{
????????System.out.println("讀取表格內(nèi)容完畢");
????}
}

//此處編寫插入的方法
@Service
public?class?AddVipsServiceTest?{
????@Autowired
???private?VipMapper?vipMapper;
????@Transactional(rollbackFor?=?Exception.class)
????public?Integer?addVip(List?list)?{
????????vipMapper.insertVips(list);
????????return?1;
????}

????public?void?readExcel(String?path,AddVipsServiceTest?addVipsServiceTest){

????}
}

?@Service
public?class?AddVipsService?extends?AddVipsServiceTest{
//path為讀取文件的路徑，此處需要繼承剛才創(chuàng)建的Service并重寫readExcel方法
????@Override
????public?void?readExcel(String?path,AddVipsServiceTest?addVipsServiceTest)?{
????????EasyExcel.read(path,Users.class,?new?ExcelListener(addVipsServiceTest)).sheet().doRead();
????}
}

????public?ReturnT?orderStatusClose(String?param){
????????XxlJobLogger.log("["+this.name+"]開(kāi)始");
//????????System.out.println(vipMapper);
//傳入對(duì)應(yīng)的表格地址
????????addVipsService.readExcel("C:\\Users\\Desktop\\VIP.xlsx",addVipsService);
????????XxlJobLogger.log("["+this.name+"]結(jié)束");
????????return?ReturnT.SUCCESS;
????}