使用SXSSFWorkbook并發(fā)導出大批量數(shù)據(jù)Excel

主體邏輯實現(xiàn)類：

import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.*;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.*;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * @author 
 * 高性能并發(fā)，低內(nèi)存導出大批量數(shù)據(jù)Excel工具類
 * 解決大量數(shù)據(jù)導出可能引起的OOM和速度慢問題
 * 若數(shù)據(jù)量少，可以不用本封裝類
 * @param <T>
 */
public class TransferDataForExcel<T>{

    private final static Logger LOGGER = LoggerFactory.getLogger(TransferDataForExcel.class);

    /** 查詢導出數(shù)據(jù)服務 **/
    private QueryService<T> queryService;

    /** 導出文件名稱 **/
    private String fileName;

    /** 導出數(shù)據(jù)過程中，最大緩存行數(shù) **/
    public final static Integer ROW_ACCESS_WINDOW_SIZE = 5000;

    /** 每個sheet頁最多存放行數(shù)，最大65536條 **/
    public final static Integer MAX_ROW_SHEET = 10000;

    /**
     * 設置緩存隊列容量
     * 因此，最大載入內(nèi)存行數(shù) = QUEUE_CAPACITY * MAX_ROW_SHEET = 10 * 10000 = 100000
     **/
    public final static Integer QUEUE_CAPACITY = 10;

    /** 設置線程池核心線程數(shù) **/
    public final static Integer THREAD_NUM = 10;

    public TransferDataForExcel(QueryService<T> queryService,String fileName){
        this.queryService = queryService;
        this.fileName = fileName;
    }


    public void doExport() throws Exception {
        Integer totalNum = queryService.getTotalNum();
        LOGGER.info(String.format("當前導出Excel總行數(shù)=%s",totalNum));

        //引入SXSSFWorkbook，利用其高效的Excel數(shù)據(jù)處理特性
        SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(ROW_ACCESS_WINDOW_SIZE);
        Integer sheetNum = (totalNum / TransferDataForExcel.MAX_ROW_SHEET) + 1;
        LOGGER.info(String.format("當前導出Excel總Sheet頁數(shù)=%s",sheetNum));

        List<Sheet> sheetList = new ArrayList<>();
        for(int i=0;i<sheetNum;i++){
            Sheet sheet = sxssfWorkbook.createSheet();
            sheetList.add(sheet);
        }

        //把任務分片后交給線程池執(zhí)行，每個分頁查詢的結(jié)果保存進一個sheet頁
        ArrayBlockingQueue<List<T>> queue = new ArrayBlockingQueue<>(QUEUE_CAPACITY);
        ExecutorService producerExecutorService = Executors.newFixedThreadPool(THREAD_NUM);
        for (int i = 1; i <= sheetNum; i++){
            Producer task = new Producer(queryService,i,queue);
            producerExecutorService.submit(task);
        }
        producerExecutorService.shutdown();

        //消費者開始消費阻塞隊列中數(shù)據(jù)
        ExecutorService consumerExecutorService = Executors.newFixedThreadPool(THREAD_NUM);
        CountDownLatch countDownLatch = new CountDownLatch(sheetList.size());
        AtomicInteger count = new AtomicInteger(0);
        for(Sheet sheet : sheetList) {
            Consumer consumer = new Consumer(queryService,queue,countDownLatch,sheet,totalNum,count);
            consumerExecutorService.submit(consumer);
        }
        countDownLatch.await();
        consumerExecutorService.shutdown();
        LOGGER.info("消費者處理完成，線程池關(guān)閉");

        Http.Response response = Http.Response.current();
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        response.setHeader("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        try{
            sxssfWorkbook.write(response.out);
        }catch (Exception e){
            LOGGER.error(String.format("sxssfWorkbook寫入輸出流錯誤，原因=%s",e.getMessage()),e);
        }
    }

}

抽象接口，使用時只要實現(xiàn)這個接口的方法，new出TransferDataForExcel實例，把實現(xiàn)類當參數(shù)傳入即可。

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.util.List;

public interface QueryService<T> {

    /**
     * 獲取導出總數(shù)
     * @return
     */
    public Integer getTotalNum();

    /**
     * 分頁查詢導出的數(shù)據(jù)
     * @param page 起始頁
     * @param pageSize 每頁數(shù)量
     * @return
     */
    public List<T> queryData(Integer page,Integer pageSize);

    /**
     * 自定義構(gòu)建導出結(jié)果
     * @param row 第一行，表頭
     * @throws Exception
     */
    public void setHeaderForExcel(Row row);

    /**
     * 自定義構(gòu)建導出結(jié)果
     * @param row 當前遍歷到的正在操作的行對象
     * @param statisticsDTO 當前行對應的DTO對象
     */
    public void setCellForExcel(Row row, T statisticsDTO);
}

導出Excel的數(shù)據(jù)源生產(chǎn)者：

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.List;
import java.util.concurrent.ArrayBlockingQueue;
import java.util.concurrent.Callable;

public class Producer<T> implements Callable {

    private final static Logger LOGGER = LoggerFactory.getLogger(Producer.class);

    private QueryService queryService;
    private Integer page;
    private ArrayBlockingQueue<List<T>> queue;

    /**
     * 初始化任務類
     * @param page
     */
    public Producer(QueryService queryService, Integer page, ArrayBlockingQueue<List<T>> queue) {
        //對數(shù)據(jù)進行分頁查詢
        this.queryService = queryService;
        this.page = (page - 1) * TransferDataForExcel.MAX_ROW_SHEET;
        this.queue = queue;
    }

    @Override
    public Boolean call(){
        try {
            while(true){
                if(queue.remainingCapacity() > 0){
                    //最多同時查詢頁數(shù)控制等于隊列容量，避免同時查詢過多結(jié)果載入內(nèi)存引發(fā)OOM
                    List<T> orderList = queryService.queryData(page,TransferDataForExcel.MAX_ROW_SHEET);
                    queue.put(orderList);
                    break;
                }else{
                    Thread.sleep(300);
                    LOGGER.info(String.format("導出excel隊列空間=%s,生產(chǎn)者put隊列已滿，阻塞等待中",queue.size()));
                    continue;
                }
            }
            return true;
        } catch (InterruptedException e) {
            LOGGER.error(String.format("導出excel生產(chǎn)者入隊錯誤，原因=%s",e.getMessage()));
            return false;
        }
    }
}

消費數(shù)據(jù)導入Excel的消費者：

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;
import java.util.List;
import java.util.concurrent.ArrayBlockingQueue;
import java.util.concurrent.Callable;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * 導出excel隊列消費者
 * @param <T>
 */
public class Consumer<T> implements Callable{

    private final static Logger LOGGER = LoggerFactory.getLogger(Consumer.class);

    private QueryService<T> queryService;
    private ArrayBlockingQueue<List<T>> queue;
    private CountDownLatch countDownLatch;
    private AtomicInteger count; //已導入行數(shù)
    private Integer total;
    private Sheet sheet;

    public Consumer(QueryService<T> queryService, ArrayBlockingQueue<List<T>> queue,
                    CountDownLatch countDownLatch,Sheet sheet, Integer total, AtomicInteger count) {
       this.queryService = queryService;
       this.queue = queue;
       this.countDownLatch = countDownLatch;
       this.total = total;
       this.count = count;
       this.sheet = sheet;
    }

    @Override
    public Boolean call() {
        try {
            if(queue.remainingCapacity() == queue.size()){
                LOGGER.info(String.format("隊列空間=%s,消費者take隊列為空，阻塞等待中",queue.size()));
            }
            List<T> orderList = queue.take();
            //根據(jù)總條數(shù)確定消費者本次寫入數(shù)據(jù)時的sheet和開始行數(shù)
            int currentCount = count.intValue();
            count.addAndGet(orderList.size());

            writeExcel(orderList,sheet);
            LOGGER.info(String.format("需寫入excel總行數(shù)=%s，已寫入行數(shù)=%s,當前操作sheet頁=%s",
                    total,currentCount,sheet.getSheetName()));
            countDownLatch.countDown();
            LOGGER.info(String.format("消費者已處理任務數(shù)=%s",countDownLatch.getCount()));
            return true;
        } catch (Exception e) {
            countDownLatch.countDown();
            LOGGER.error(String.format("導出excel消費者處理錯誤=%s",e.getMessage()),e);
            LOGGER.info(String.format("消費者已處理任務數(shù)=%s",countDownLatch.getCount()));
            return false;
        }
    }


    private void writeExcel(List<T> orderList,Sheet sheet){
        if (CollectionUtils.isEmpty(orderList)){
            return;
        }
        Row headRow = sheet.createRow(0);
        queryService.setHeaderForExcel(headRow);
        for (int i = 0; i < orderList.size(); i++) {
            int beginRowNum = i+1;
            Row bodyRow = sheet.createRow(beginRowNum);
            T statisticsDTO = orderList.get(i);
            queryService.setCellForExcel(bodyRow,statisticsDTO);
        }
        orderList.clear();
    }
}
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。

友情鏈接更多精彩內(nèi)容