fork-join、easyPOI解析excel速度

fork-join 完ok easyPOI

现在是比较注重速度的时代，例如导入excel表格，需要将数据入库，那么如果数据量很大的时候，速度就是很好的体验感了

看代码吧

控制层

package com.jay.controller;

import com.jay.service.StudentService;
import com.jay.util.ExcelUtil;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;


@Controller
@Api(tags = {"批量导入学生信息"})
@RequestMapping("/student")
public class StudentController {
    @Autowired
    private StudentService studentService;

    @ApiOperation(value = "通过EasyPoi的方式批量导入学生信息-1", httpMethod = "POST", notes = "批量导入业主信息")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "file", value = "文件流对象,接收数组格式", required = true, dataType = "MultipartFile")
    })
    @RequestMapping(value = "/batchUploadStudent_easyPOI.do", method = RequestMethod.POST)
    @ResponseBody
    public String batchUploadOwner_easyPOI(@RequestParam(value = "file") MultipartFile file) throws Exception {
        studentService.batchUploadStudent_easyPOI(file);
        return "success";
    }

    @ApiOperation(value = "通过forkjoin的方式批量导入学生信息-2", httpMethod = "POST", notes = "批量导入业主信息")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "file", value = "文件流对象,接收数组格式", required = true, dataType = "MultipartFile")
    })
    @RequestMapping(value = "/batchUploadStudent_forkjoin.do", method = RequestMethod.POST)
    @ResponseBody
    public String batchUploadOwner(@RequestParam(value = "file") MultipartFile file) throws Exception {
        Workbook workbook = ExcelUtil.getWorkbook(file.getInputStream());
        studentService.batchUploadStudent_forkjoin(workbook);
        return "success";
    }
}

业务层

package com.jay.service;

import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;


public interface StudentService {
    /**
     * 通过easyPOI读取数据
     * @param file
     * @return
     * @author xiagwei
     * @date 2020/4/18 4:09 PM
     */
    String batchUploadStudent_easyPOI(MultipartFile file) throws Exception;

    /**
     * 通过fork-join的方式读取数据
     * @param workbook
     * @return
     * @author xiagwei
     * @date 2020/4/18 4:10 PM
     */
    String batchUploadStudent_forkjoin(Workbook workbook);
}

业务层实现类

package com.jay.service;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import com.jay.model.Student;
import com.jay.util.StudentExcelImportWrapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.util.List;

@Slf4j
@Service
public class StudentServiceImpl implements StudentService {
    @Autowired
    private StudentExcelImportWrapper studentExcelImportWrapper;

    @Override
    public String batchUploadStudent_easyPOI(MultipartFile file) throws Exception {
        long startTime = System.currentTimeMillis();
        List<Student> studentList = ExcelImportUtil.importExcel(file.getInputStream(), Student.class, new ImportParams());
        log.info("********通过EasyPOI读取文件总耗时是={},读取到的数据总条数是={}", (System.currentTimeMillis() - startTime) + "毫秒", studentList.size());
        return null;
    }

    @Override
    public String batchUploadStudent_forkjoin(Workbook workbook) {
        long startTime = System.currentTimeMillis();
        List<Student> studentList = studentExcelImportWrapper.importExcel(workbook);
        log.info("********通过Fork-Join的方式读取文件总耗时是={},读取到的数据条数是={}", (System.currentTimeMillis() - startTime) + "毫秒", studentList.size());
        return null;
    }
}

fork-join的业务处理类

package com.jay.util;


import com.jay.model.Student;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.ForkJoinPool;
import java.util.concurrent.RecursiveTask;
import java.util.stream.Collectors;


@Slf4j
@Component
public class StudentExcelImportWrapper {



   public List<Student> importExcel(Workbook workbook) {
       //设置4条线程
       ForkJoinPool forkJoinPool = new ForkJoinPool(4);;
       //选择sheet页码
       Sheet sheet = workbook.getSheetAt(0);
       //开启任务， 参数是起始行，总行数， 这个实体类对象继承了RecursiveTask
       JoinTask joinTask = new JoinTask(1, sheet.getLastRowNum(), sheet);
       //开启异步处理任务
       List<Student> importVOList = forkJoinPool.invoke(joinTask);
       //excel内部去重
//        List<Student> noRepeatImportVOList = importVOList.stream().filter(DistinctUtil.distinctByKey(Student::getStudentNo)).collect(Collectors.toList());
       List<Student> noRepeatImportVOList = importVOList;
       return noRepeatImportVOList;
   }

   class JoinTask extends RecursiveTask<List<Student>> {
       private int start;
       private int end;
       private Sheet sheet;
       private int total;

       public JoinTask(int start, int end, Sheet sheet) {
           this.start = start;
           this.end = end;
           this.sheet = sheet;
           this.total = sheet.getLastRowNum();
       }

       @Override
       protected List<Student> compute() {
           //数据异常
           if (start > end || total < end) {
               return new ArrayList<>(1);
           }
           //人为设定的，看自己定，没200行一个解析
           if (end - start <= 200) {
//                return getData(sheet, start, end).stream().filter(DistinctUtil.distinctByKey(Student::getStudentNo)).collect(Collectors.toList());
               return getData(sheet, start, end);
           } else {
               //二分法，将数据平均分成两块
               int mid = (start + end) / 2;
               //递归调用，左边是序号小的那一块
               JoinTask rightTask = new JoinTask(start, mid, sheet);
               //递归调用，右边是序号大的那一块
               JoinTask leftTask = new JoinTask(mid + 1, end, sheet);

               //写法一
               rightTask.fork();
               List<Student> leftList =  leftTask.compute();
               List<Student> rightList = rightTask.join();
               //写法二
               //invokeAll(rightTask, leftTask);
               //List<Student> leftList = leftTask.join();
               //List<Student> rightList = rightTask.join();
               //将左右两边数据合并
               leftList.addAll(rightList);
               return leftList;
           }
       }
   }

   /**
    * 分页读取Excel中的数据
    *
    * @param sheet
    * @param start 开始页
    * @param end   结束页
    * 
    * @return
    */
   private List<Student> getData(Sheet sheet, int start, int end) {
       List<Student> mapList = new ArrayList<>();
       //解析单元格的方法，本demo是直接挨个读取每个单元格的，当然也可以通过注解的方式来实现
       for (int i = start; i <= end; i++) {
           Student student = null;
           try {
               Row row = sheet.getRow(i);
               student = new Student();
//                student.setClassName(ExcelUtil.getKeyValue(row.getCell(0)));
//                student.setStudentName(ExcelUtil.getKeyValue(row.getCell(1)));
//                student.setStudentMobile(ExcelUtil.getKeyValue(row.getCell(2)));
//                student.setIdCard(ExcelUtil.getKeyValue(row.getCell(3)));
               student.setStudentNo(ExcelUtil.getKeyValue(row.getCell(4)));
//                student.setIdCard(ExcelUtil.getKeyValue(row.getCell(5)));
               System.err.println("jjjjj");
           } catch (Exception e) {
               log.info("***************税号={},文件名={},数据解析出现异常={}", e);
               continue;
           }
           mapList.add(student);
       }
       return mapList;
   }
}

jar包

	<!--easypoi-->
	<dependency>
		<groupId>cn.afterturn</groupId>
		<artifactId>easypoi-spring-boot-starter</artifactId>
		<!--<version>3.3.0</version>-->
		<version>4.0.0</version>
		<!--<version>4.1.0</version>-->
	</dependency>
	<!--easypoi-->