百万数据快速导入导出

百万数据快速导入

pom

		<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.2.0</version>
        </dependency>

	@Resource
    private SalariesListener salariesListener;

    private ExecutorService executorService = Executors.newFixedThreadPool(20);

    /**
     * 同步导入
     * @param file
     * @throws IOException
     */
    public void importExcel(MultipartFile file) throws IOException {
        EasyExcel.read(file.getInputStream(), Salaries.class, salariesListener).doReadAll();
    }


    /**
     * 异步导入
     * @param file
     */
    public void importExcelAsync(MultipartFile file) {
        // 开20个线程分别处理20个sheet
        try {
            InputStream inputStream = file.getInputStream();
            int activeSheetIndex = new XSSFWorkbook(inputStream).getNumberOfSheets();//多少个sheet  20

            List<Callable<Object>> tasks = new ArrayList<>(activeSheetIndex);
            for (int i = 0; i < activeSheetIndex; i++) {
                int num = i;
                tasks.add(() -> {
                    EasyExcel.read(inputStream, Salaries.class, salariesListener)
                            .sheet(num).doRead();
                    return null;
                });
            }

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

    }

监听器代码

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

        ArrayList<Salaries> salaries = salariesList.get();
        salaries.add(data);
        if (salaries.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() + "条数据");
        }
    }
}

百万数据快速导出

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

    @Resource
    private SalariesMapper salariesMapper;

    public void exportExcel1(HttpServletResponse response) throws IOException {
        setExportHeader(response);
        List<Salaries> salaries = salariesMapper.selectList(null);
        EasyExcel.write(response.getOutputStream(), Salaries.class).sheet().doWrite(salaries);
    }

    public void exportExcel2(HttpServletResponse response) throws IOException {
        setExportHeader(response);
        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);
        }
    }



    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 IOException, InterruptedException {

        setExportHeader(response);

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

        // https://github.com/alibaba/easyexcel/issues/1040
    }

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