EasyExcel实现大量数据的导入导出

百万数据的导入导出

• 导出
  普通导出

response.setContentType(CONTENT_TYPE);
response.setCharacterEncoding(StandardCharsets.UTF_8.name());
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + "aa.xlsx");
List<Salaries> salaries = salariesMapper.selectList(null);
EasyExcel.write(response.getOutputStream(), Salaries.class).sheet().doWrite(salaries);

一次性读取全部，分批次写出

		response.setContentType(CONTENT_TYPE);
		response.setCharacterEncoding(StandardCharsets.UTF_8.name());
		response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + "aa.xlsx");
       List<Salaries> salaries = salariesMapper.selectList(null);

        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);
        }

分页读取导出

	    response.setContentType(CONTENT_TYPE);
		response.setCharacterEncoding(StandardCharsets.UTF_8.name());
		response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + "aa.xlsx");
        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);
            }
        }

多线程读取导出

        response.setContentType(CONTENT_TYPE);
        response.setCharacterEncoding(StandardCharsets.UTF_8.name());
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + "aa.xlsx");
        Long count = salariesMapper.selectCount(null);
        Integer pages = 20;
        Long size = count / pages;

        ExecutorService executorService = Executors.newFixedThreadPool(pages);
        CountDownLatch countDownLatch = new CountDownLatch(pages);

        Map<Integer, Page<Salaries>> pageMap = new HashMap<>();
        for (int i = 0; i < pages; i++) {
            int finalI = i;
            executorService.submit(() -> {
                Page<Salaries> page = new Page<>();
                page.setCurrent(finalI + 1);
                page.setSize(size);
                Page<Salaries> selectPage = salariesMapper.selectPage(page, null);

                pageMap.put(finalI, selectPage);
                countDownLatch.countDown();
            });
        }
        countDownLatch.await();

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

• 导入
  简单导入

 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) {
            throw new RuntimeException(e);
        }

    }

salariesListener代码

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

    private static final Log logger = LogFactory.getLog(SalariesListener.class);

    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 saveOne(Salaries data){
        save(data);
        logger.info("第" + count.getAndAdd(1) + "次插入1条数据");
    }

    public void saveData() {
        if (!salariesList.get().isEmpty()) {
            saveBatch(salariesList.get(), salariesList.get().size());
            logger.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));
            salariesList.get().clear();
        }
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void doAfterAllAnalysed(AnalysisContext context) {
        logger.info("一个Sheet全部处理完");
        if (salariesList.get().size() >= batchSize) {
            saveData();
        }
    }

    static class SaveTask implements Runnable {

        private List<Salaries> salariesList;
        private SalariesListener salariesListener;

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

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