百万级excel导入导出

项目目录结构：

*

导入思路：

excel拆分为多个sheet，开启20个线程分别处理20个sheet，采用批量插入的方式入库
*

导出思路：

开启20个线程分页读取数据，放入到map中，CountDownLatch保证并发安全，将数据写入到excel中导出，easyExcel不支持多线程并发导出，只能单线程导出
*

@Data
@TableName("aaa")
public class Salaries extends Model {

    private String empNo;

    private String salary;

    private String formDate;

    private String toDate;
}

@Mapper
public interface SalariesMapper extends BaseMapper<Salaries> {

    @Select("select id,emp_no,salary,form_date,to_date from aaa limit #{offSet},#{size}")
    public List<Salaries> page(@Param("offSet") Integer offSet, @Param("size") Integer size);
}

@Service
public class ImportService {


    @Resource
    private SalariesListener salariesListener;

    private ExecutorService executorService = Executors.newFixedThreadPool(20);

    public void importExcel(MultipartFile file) throws IOException {
        EasyExcel.read(file.getInputStream(), Salaries.class,salariesListener).doReadAll();
    }

    public void importExcelAsync(MultipartFile file){
        // 开20个线程分别处理20个sheet
        List<Callable<Object>> tasks = new ArrayList<>();
        for (int i = 0; i < 20; i++) {
            int num = i;
            tasks.add(() -> {
                EasyExcel.read(file.getInputStream(),Salaries.class,salariesListener).sheet(num).doRead();
                return null;
            });
        }

        try {
            executorService.invokeAll(tasks);
        } catch (InterruptedException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

}

@Component
@Slf4j
public class SalariesListener extends ServiceImpl<SalariesMapper,Salaries> implements ReadListener<Salaries>, IService<Salaries> {
// public class SalariesListener extends ServiceImpl<SalariesMapper, Salaries> implements ReadListener<Salaries>, IService<Salaries> {

    private ExecutorService executorService = Executors.newFixedThreadPool(20);

    private ThreadLocal<ArrayList<Salaries>> salariesList = ThreadLocal.withInitial(ArrayList::new);
    private static AtomicInteger count = new AtomicInteger(1);
    private static final int batchSize = 10000;


    @Resource
    private SalariesListener salariesListener;

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void invoke(Salaries data, AnalysisContext context) {
        // 第一种方案：单条插入
        // saveOne(data);
        salariesList.get().add(data);
        if (salariesList.get().size() >= batchSize) {
            saveData();
            // asyncSaveData();
        }
    }

    public void saveData() {
        if (!salariesList.get().isEmpty()) {
            saveBatch(salariesList.get(), salariesList.get().size());
            log.info("第" + count.getAndAdd(1) + "次插入" + salariesList.get().size() + "条数据");
            salariesList.get().clear();
        }
    }

    public void asyncSaveData(){
        if (!salariesList.get().isEmpty()) {
            ArrayList<Salaries> salaries = (ArrayList<Salaries>) salariesList.get().clone();
            executorService.execute(new SaveTask(salaries,salariesListener));
        }
    }


    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        log.info("一个Sheet全部处理完");
        if (salariesList.get().size() >= batchSize) {
            saveData();
        }
    }


    public void saveOne(Salaries data){
        save(data);
        log.info("第"+"次插入1条数据");
    }


    /**
     * 方案1：单条插入
     * 方案2：批量插入,放入到List中,>10000条数据时插入,并将list清空,从threadLocal中清空
     *
     *
     * 方案3：
     */

    static class SaveTask implements Runnable {
        private List<Salaries> salariesList;
        private SalariesListener salariesListener;

        public SaveTask(List<Salaries> salaries,SalariesListener salariesListener) {
            this.salariesList = salaries;
            this.salariesListener = salariesListener;
        }

        @Override
        public void run() {
            salariesListener.saveBatch(salariesList);
            log.info("第"+count.getAndAdd(1) + "次插入" + salariesList.size() + "条数据");
        }
    }

}

@Service
public class ExportService {

    public static final String CONTENT_TYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

    @Resource
    private SalariesMapper salariesMapper;

    public void exportExcel(HttpServletResponse response) throws IOException {
        setExportHeader(response);
        // 方案1：全量查询,写一个sheet中
        List<Salaries> salaries = salariesMapper.selectList(null);
        EasyExcel.write(response.getOutputStream(),Salaries.class).sheet().doWrite(salaries);
    }

    public void exportExcel2(HttpServletResponse response) throws IOException {
        setExportHeader(response);
        // 方案2：全量查询,写多个sheet中
        List<Salaries> salaries = salariesMapper.selectList(null);
        // EasyExcel.write(response.getOutputStream(),Salaries.class).sheet().doWrite(salaries);
        try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(),Salaries.class).build()) {
            WriteSheet writeSheet1 = EasyExcel.writerSheet(1,"模板1").build();
            WriteSheet writeSheet2 = EasyExcel.writerSheet(2,"模板2").build();
            WriteSheet writeSheet3 = EasyExcel.writerSheet(3,"模板3").build();

            List<Salaries> data1 = salaries.subList(0, salaries.size() / 3);
            List<Salaries> data2 = salaries.subList(salaries.size() / 3, salaries.size() * 2 / 3);
            List<Salaries> data3 = salaries.subList(salaries.size() * 2 / 3, salaries.size());

            excelWriter.write(data1, writeSheet1);
            excelWriter.write(data2, writeSheet2);
            excelWriter.write(data3, writeSheet3);
        }
    }

    public void exportExcel3(HttpServletResponse response) throws IOException {
        setExportHeader(response);
        try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(),Salaries.class).build()) {
            Long count = salariesMapper.selectCount(null);
            Integer pages = 10;
            Long size = count /pages;
            for (int i = 0; i < pages; i++) {
                WriteSheet writeSheet = EasyExcel.writerSheet(i,"模板" + i).build();
                Page<Salaries> page = new Page<>();
                page.setCurrent(i+1);
                page.setSize(size);
                Page<Salaries> selectPage = salariesMapper.selectPage(page,null);
                excelWriter.write(selectPage.getRecords(),writeSheet);
            }
        }
    }


    public void exportExcel4(HttpServletResponse response) throws InterruptedException, IOException {
        setExportHeader(response);
        Long count = salariesMapper.selectCount(null);

        Integer pages = 20;
        Integer size = count.intValue() / pages;
        ExecutorService executorService = Executors.newFixedThreadPool(pages);
        CountDownLatch countDownLatch = new CountDownLatch(pages);

        //Map<Integer,Page<Salaries>> pageMap = new HashMap<>();
        Map<Integer,List<Salaries>> pageMap = new HashMap<>();
        for (int i = 0; i < pages; i++) {
            int finalI = i;
            executorService.submit(new Runnable() {
                @Override
                public void run() {
//                    Page<Salaries> page = new Page<>();
//                    page.setCurrent(finalI + 1);
//                    page.setSize(size);
//                    Page<Salaries> selectPage = salariesMapper.selectPage(page,null);
                    List<Salaries> list = salariesMapper.page(finalI*size,size);
                    pageMap.put(finalI,list);
                    countDownLatch.countDown();
                }
            });
        }

        countDownLatch.await();

        try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), Salaries.class).build()) {
            for(Map.Entry<Integer,List<Salaries>> entry : pageMap.entrySet()) {
                Integer num = entry.getKey();
                List<Salaries> salariesPage = entry.getValue();
                WriteSheet writeSheet = EasyExcel.writerSheet(num,"模板"+num).build();
                excelWriter.write(salariesPage,writeSheet);
            }
        }
    }


    private static void setExportHeader(HttpServletResponse response) {
        response.setContentType(CONTENT_TYPE);
        response.setCharacterEncoding(StandardCharsets.UTF_8.name());
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + "zhouyu.xlsx");
    }
}

程序运行结果，导入时间花费247s，导出时间花费43s