使用ApachePOI生成XLSX格式Excel文档大数据量导出

摘要：最近在做使用进行大数据量导出，现在把其整理成工具类供大家参考。版本增加了前缀为相关的类，主要用于大数据量的写入与读取。

最近在做使用POI进行大数据量导出，现在把其整理成工具类供大家参考。Apache POI 3.8版本增加了前缀为SXSSF相关的类，主要用于大数据量的写入与读取。关于ApachePOI导出Excel基本的使用我这里就不详解了，具体参考：

Apache POI官方网站

Apache POI使用详解

关于封装的工具类需要注意：

以下代码少ReportInternalException大家可以忽略(我们封装的一个异常类)

导出的Excel同时考虑到数据的本身类型，如整数、小数、日期等

写入数据方式需依次调用方法[writeExcelTitle、writeExcelData、dispose]，先完成写入Excel标题与列名，再完成数据写入(或者说基于模板方式写入数据)，最终关闭流与资源的释放

我们使用[styleMap]方法避免重复创建Excel单元格样式(否则受Excel创建样式数量限制)

SXSSFWorkbook基于模板写入数据的时候仍需要借助XSSFWorkbook

SXSSFWorkbook写入数据模式大致为：根据初始化设置的flushRows(内存存储条数)数随着数据写入逐步把数据刷新至硬盘，具体参考官方文档与API介绍

XSSFWorkbook tplWorkBook = new XSSFWorkbook(new FileInputStream(file));
Workbook writeDataWorkBook = new SXSSFWorkbook(tplWorkBook, flushRows);

Maven依赖配置

使用的Apache POI版本

3.9

    org.apache.poi
    poi
    ${poi.version}


    org.apache.poi
    poi-ooxml
    ${poi.version}


    org.apache.poi
    poi-ooxml-schemas
    ${poi.version}

开始晒代码时间

import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * Excel 相关操作类(大数据量写入但受Excel数据行数限制)
 * 先写入Excel标题(writeExcelTitle)，再写入数据(writeExcelData)，最后释放资源(dispose)
 */
public class ExportExcel2007 {

    //默认列宽度
    private final int DEFAULT_COLUMN_SIZE = 30;
    //刷新写入硬盘数据阀值
    private final int flushRows = 1000;
    //声明一个模板工作薄(写入流式数据)
    private Workbook writeDataWorkBook;
    //样式列表
    private Map cellStyleMap;
    //Excel当前数据行数(将要写入数据的索引数)
    private int currentRowNum = 0;
    //数据输出流
    private OutputStream outputStream;

    /**
     * 断言Excel文件写入之前的条件
     *
     * @param directory 目录
     * @param fileName  文件名
     * @return file
     * @throws IOException
     */
    private File assertFile(String directory, String fileName) throws IOException {
        File tmpFile = new File(directory + File.separator + fileName + ".xlsx");
        if (tmpFile.exists()) {
            if (tmpFile.isDirectory()) {
                throw new IOException("File "" + tmpFile + "" exists but is a directory");
            }
            if (!tmpFile.canWrite()) {
                throw new IOException("File "" + tmpFile + "" cannot be written to");
            }
        } else {
            File parent = tmpFile.getParentFile();
            if (parent != null) {
                if (!parent.mkdirs() && !parent.isDirectory()) {
                    throw new IOException("Directory "" + parent + "" could not be created");
                }
            }
        }
        return tmpFile;
    }

    /**
     * 日期转化为字符串,格式为yyyy-MM-dd HH:mm:ss
     */
    private String getCnDate(Date date) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        return sdf.format(date);
    }

    /**
     * Excel 导出，POI实现，先写入Excel标题，与writeExcelData配合使用
     * 先使用writeExcelTitle再使用writeExcelData
     *
     * @param directory   目录
     * @param fileName    文件名
     * @param sheetName   sheetName
     * @param columnNames 列名集合
     * @param sheetTitle  表格标题
     */
    public void writeExcelTitle(String directory, String fileName, String sheetName, List columnNames,
                                String sheetTitle) throws ReportInternalException, IOException {
        File tmpFile = assertFile(directory, fileName);
        exportExcelTitle(tmpFile, sheetName, columnNames, sheetTitle);
        loadTplWorkbook(tmpFile);
    }

    /**
     * Excel 导出，POI实现，写入Excel数据行列，与writeExcelTitle配合使用
     * 先使用writeExcelTitle再使用writeExcelData
     *
     * @param directory 目录
     * @param fileName  文件名
     * @param sheetName sheetName
     * @param objects   数据信息
     */
    public void writeExcelData(String directory, String fileName, String sheetName, List> objects)
            throws ReportInternalException, IOException {
        File tmpFile = assertFile(directory, fileName);
        outputStream = new FileOutputStream(tmpFile);
        exportExcelData(sheetName, objects);
    }

    /**
     * 释放资源
     */
    public void dispose() throws ReportInternalException {
        try {
            if (writeDataWorkBook != null) {
                writeDataWorkBook.write(outputStream);
            }
            if (outputStream != null) {
                outputStream.flush();
                outputStream.close();
            }
            if (cellStyleMap != null) {
                cellStyleMap.clear();
            }
            cellStyleMap = null;
            outputStream = null;
            writeDataWorkBook = null;
        } catch (IOException e) {
            throw new ReportInternalException(e);
        }
    }

    /**
     * 导出字符串数据
     *
     * @param file        文件名
     * @param columnNames 表头
     * @param sheetTitle  sheet页Title
     */
    private void exportExcelTitle(File file, String sheetName, List columnNames,
                                  String sheetTitle) throws ReportInternalException {
        Workbook tplWorkBook = new XSSFWorkbook();
        Map cellStyleMap = styleMap(tplWorkBook);
        // 表头样式
        CellStyle headStyle = cellStyleMap.get("head");
        // 生成一个表格
        Sheet sheet = tplWorkBook.getSheet(sheetName);
        if (sheet == null) {
            sheet = tplWorkBook.createSheet(sheetName);
        }
        //最新Excel列索引,从0开始
        //int lastRowIndex = sheet.getLastRowNum();
        // 设置表格默认列宽度
        sheet.setDefaultColumnWidth(DEFAULT_COLUMN_SIZE);
        // 合并单元格
        sheet.addMergedRegion(new CellRangeAddress(currentRowNum, currentRowNum, 0, columnNames.size() - 1));
        // 产生表格标题行
        Row rowMerged = sheet.createRow(currentRowNum);
        Cell mergedCell = rowMerged.createCell(0);
        mergedCell.setCellStyle(headStyle);
        mergedCell.setCellValue(new XSSFRichTextString(sheetTitle));
        //写入成功一行数据递增行数
        currentRowNum = currentRowNum + 1;
        // 产生表格表头列标题行
        Row row = sheet.createRow(currentRowNum);
        for (int i = 0; i < columnNames.size(); i++) {
            Cell cell = row.createCell(i);
            cell.setCellStyle(headStyle);
            RichTextString text = new XSSFRichTextString(columnNames.get(i));
            cell.setCellValue(text);
        }
        //写入成功一行数据递增行数
        currentRowNum = currentRowNum + 1;
        try {
            OutputStream ops = new FileOutputStream(file);
            tplWorkBook.write(ops);
            ops.flush();
            ops.close();
        } catch (IOException e) {
            throw new ReportInternalException(e);
        }
    }

    /**
     * 加载模板文件
     */
    private void loadTplWorkbook(File file) throws ReportInternalException {
        try {
            XSSFWorkbook tplWorkBook = new XSSFWorkbook(new FileInputStream(file));
            writeDataWorkBook = new SXSSFWorkbook(tplWorkBook, flushRows);
            cellStyleMap = styleMap(writeDataWorkBook);
        } catch (IOException e) {
            throw new ReportInternalException("Excel模板文件不存在");
        }
    }

    /**
     * 导出字符串数据
     *
     * @param objects 目标数据
     */
    private void exportExcelData(String sheetName, List> objects) throws ReportInternalException, IOException {
        // 正文样式
        CellStyle contentStyle = cellStyleMap.get("content");
        //正文整数样式
        CellStyle contentIntegerStyle = cellStyleMap.get("integer");
        //正文带小数整数样式
        CellStyle contentDoubleStyle = cellStyleMap.get("double");
        // 生成一个表格
        Sheet sheet = writeDataWorkBook.getSheet(sheetName);
        if (sheet == null) {
            throw new ReportInternalException("读取Excel模板错误");
        }
        // 设置表格默认列宽度
        sheet.setDefaultColumnWidth(DEFAULT_COLUMN_SIZE);
        // 遍历集合数据,产生数据行,前两行为标题行与表头行
        for (List