使用线程池导出Excel数据

之前同事在做一个功能的时候，需要导出一些数据，但是组装数据量的过程比较麻烦一点，所以这里给了一个建议  
看看能不能使用多线程的方法来进行处理。顺便写了两个demo，一种方式是批量分段去往一个sheet页中写入数  
据，另一种方式是往多个sheet也中写入数据。demo如下：  

批量分段导出数据

package com.zkn.newlearn.opensource.poi;
/**
 * Created by zkn on 2017/11/15.
 */

import com.google.common.util.concurrent.ThreadFactoryBuilder;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellStyle;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.concurrent.*;

/**
 * @author zkn
 * @date 2017/11/15 23:34
 */
public class CreateMultipleSheet {

    public static void main(String[] args) {
        //处理器核心数
        int processor = Runtime.getRuntime().availableProcessors();
        //HSSFWorkbook 一个sheet页只能写入六万多条数据
        HSSFWorkbook workBook = new HSSFWorkbook();
        //创建格式
        CellStyle style = workBook.createCellStyle();
        //居中格式
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //创建sheet页
        HSSFSheet sheet = workBook.createSheet();
        //创建一行
        HSSFRow hssfRow = sheet.createRow(0);
        HSSFCell hssfCell = hssfRow.createCell(0);
        hssfCell.setCellStyle(style);
        hssfCell.setCellValue("第" + 1 + "个sheet页，第一行，第一个单元格");

        hssfCell = hssfRow.createCell(1);
        hssfCell.setCellStyle(style);
        hssfCell.setCellValue("第" + 1 + "个sheet页，第一行，第二个单元格");

        hssfCell = hssfRow.createCell(2);
        hssfCell.setCellStyle(style);
        hssfCell.setCellValue("第" + 1 + "个sheet页，第一行，第三个单元格");

        //手工创建线程池
        ExecutorService executorService = new ThreadPoolExecutor(processor, processor, 1000, TimeUnit.MILLISECONDS, new LinkedBlockingDeque(),
                new ThreadFactoryBuilder().setNameFormat("poi-task-%d").build());
        //计数器 等待线程池中的线程执行完毕
        CountDownLatch countDownLatch = new CountDownLatch(processor);
        for (int i = 1; i <= processor; i++) {
            int start = (i - 1) * 100 + 1;
            int end = i * 100;
            //放入线程池中
            executorService.execute(() -> createRows(sheet, start, end, countDownLatch));
        }
        try {
            //等待所有线程执行完毕
            countDownLatch.await();
            //关闭线程池
            executorService.shutdown();
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        FileOutputStream fou = null;
        try {
            fou = new FileOutputStream("D:\\LearnVideo\\multiSheet.xls");
            workBook.write(fou);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (fou != null) {
                try {
                    fou.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    private static void createRows(HSSFSheet hSSFSheet, int startRow, int endRow, CountDownLatch countDownLatch) {
        HSSFRow hssfRows;
        HSSFCell hSSFCells;
        int i = startRow;
        try {
            while (i <= endRow) {
                hssfRows = getRows(hSSFSheet, i);
                hSSFCells = hssfRows.createCell(0);
                hSSFCells.setCellValue("第" + (i + 1) + "行，第一个单元格");

                hSSFCells = hssfRows.createCell(1);
                hSSFCells.setCellValue("第" + (i + 1) + "行，第一个单元格");

                hSSFCells = hssfRows.createCell(2);
                hSSFCells.setCellValue("第" + (i + 1) + "行，第一个单元格");
                ++i;
            }
        } finally {
            countDownLatch.countDown();
        }
    }

    /**
     * 创建表格 这里要加锁
     *
     * @param hSSFSheet
     * @param row
     * @return
     */
    private static HSSFRow getRows(HSSFSheet hSSFSheet, int row) {
        synchronized (Object.class) {
            return hSSFSheet.createRow(row);
        }
    }
}

向多个sheet页中写入数据

package com.zkn.newlearn.opensource.poi;

import com.google.common.util.concurrent.ThreadFactoryBuilder;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.concurrent.*;

/**
 * @author zkn
 * @date 2017/11/15 23:34
 */
public class CreateMultipleSheetNew {

    private final static Object object = new Object();

    public static void main(String[] args) {
        //处理器核心数
        int processor = Runtime.getRuntime().availableProcessors();
        //XSSFWorkbook 一次只能写入六万多条数据，所以这里最好使用SXSSFWorkbook
        SXSSFWorkbook workBook = new SXSSFWorkbook();
        //创建格式
        CellStyle style = workBook.createCellStyle();
        //居中格式
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //手工创建线程池
        ExecutorService executorService = new ThreadPoolExecutor(processor, processor, 1000, TimeUnit.MILLISECONDS, new LinkedBlockingDeque(),
                new ThreadFactoryBuilder().setNameFormat("poi-task-%d").build());
        //计数器 等待线程池中的线程执行完毕
        CountDownLatch countDownLatch = new CountDownLatch(processor);
        for (int i = 0; i < processor; i++) {
            int sheetId = i;
            //放入线程池中
            executorService.execute(() -> createSheet(workBook, style, sheetId, countDownLatch));
        }
        try {
            //等待所有线程执行完毕
            countDownLatch.await();
            executorService.shutdown();
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        FileOutputStream fou = null;
        try {
            fou = new FileOutputStream("D:\\LearnVideo\\multiSheetNew.xls");
            workBook.write(fou);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (fou != null) {
                try {
                    fou.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    private static void createSheet(SXSSFWorkbook workBook, CellStyle style, int sheetId, CountDownLatch countDownLatch) {
        try {
            SXSSFSheet hSSFSheet;
            //这个地方一定要加锁，要不然会出现问题
            synchronized (object) {
                //创建sheet页
                hSSFSheet = workBook.createSheet(String.format("第%d个sheet页", sheetId));
            }
            //创建一行
            SXSSFRow hssfRow = hSSFSheet.createRow(0);
            SXSSFCell hssfCell = hssfRow.createCell(0);
            hssfCell.setCellStyle(style);
            hssfCell.setCellValue("第" + sheetId + "个sheet页，第一行，第一个单元格");

            hssfCell = hssfRow.createCell(1);
            hssfCell.setCellStyle(style);
            hssfCell.setCellValue("第" + sheetId + "个sheet页，第一行，第二个单元格");

            hssfCell = hssfRow.createCell(2);
            hssfCell.setCellStyle(style);
            hssfCell.setCellValue("第" + sheetId + "个sheet页，第一行，第三个单元格");
            SXSSFRow hssfRows;
            SXSSFCell hSSFCells;
            for (int i = 1; i < 3; i++) {
                hssfRows = hSSFSheet.createRow(i);
                hSSFCells = hssfRows.createCell(0);
                hSSFCells.setCellStyle(style);
                hSSFCells.setCellValue("第" + sheetId + "个sheet页，第" + (i + 1) + "行，第一个单元格");

                hSSFCells = hssfRows.createCell(1);
                hSSFCells.setCellStyle(style);
                hSSFCells.setCellValue("第" + sheetId + "个sheet页，第一个单元格");

                hSSFCells = hssfRows.createCell(2);
                hSSFCells.setCellStyle(style);
                hSSFCells.setCellValue("第" + sheetId + "个sheet页，第一个单元格");
            }
        } finally {
            //计数器减一
            countDownLatch.countDown();
        }
    }
}

这里说一下，对于HSSFWorkbook一个sheet页最多只能写入65535条数据，如果你导出的数量比较大的话，可以导出到多个sheet页，或者改用SXSSFWorkbook，另外可能会出现内存溢出的问题。