Java中使用POI将数据导出到Excel及各组件性能对比

常见需求

将数据导出到 Excel 文件中供用户下载是一个比较常见的功能。Apache POI 是一个强大的 Java 库，很多开源软件在POI层上再次封装，以便更方便地操作 Excel 文件，比如多数人熟知的EasyExcel。
本次仅介绍POI层面的如何将数据导出到Excel，给自己做下记录，便于以后反馈和优化。

POI中与Excel相关的主要组件

POI除了操作Excel，还可以支持操作Office其他格式的文档，咱们本次单纯说Excel。POI中提供了一些操作Excel的类，例如HSSFWorkook、XSSFWorkbook、SXSSFWorkbook。

1. HSSF：用于操作旧版 Excel 格式（.xls）的文件。它提供了一系列的类和方法，用于创建、读取和写入.xls文件。
2. XSSF：用于操作新版 Excel 格式（.xlsx）的文件。与 HSSF 类似，XSSF 也提供了一系列的类和方法，用于创建、读取和写入.xlsx文件。
3. SXSSF: 用于操作新版Excel格式（.xlsx）的文件，与XSSF相似，是基于XSSF的流式版本，它的目的是在处理大量数据时，通过限制内存中的行数来降低内存消耗，同时保持对 .xlsx 文件格式的操作能力。

三、准备工作

引入依赖

如果使用 Maven 项目，在pom.xml文件中添加以下依赖：

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->  
<dependency>  
	<groupId>org.apache.poi</groupId>  
	<artifactId>poi</artifactId>  
	<version>5.2.2</version>  
</dependency>  
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->  
<dependency>  
	<groupId>org.apache.poi</groupId>  
	<artifactId>poi-ooxml</artifactId>  
	<version>5.2.2</version>  
</dependency>

关键代码

    /**
     * 通过hssf将数据写入excel
     * @param dataList
     * @return
     */
    public static InputStream exportXls(List<List<String>> dataList) {
        // 创建一个新的HSSFWorkbook对象(同样的，如果是导出为xlsx，则可以使用XSSFWorkbook或者SXSSFWorkbook)
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 创建一个工作表
        Sheet sheet = workbook.createSheet("Data");
        // 遍历数据列表，将数据写入工作表
        for (int i = 0; i < dataList.size(); i++) {
            List<String> rowList = dataList.get(i);
            Row row = sheet.createRow(i);
            for (int j = 0; j < rowList.size(); j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(rowList.get(j));
            }
        }
        // 将工作簿写入字节数组输出流
		ByteArrayOutputStream baos = new ByteArrayOutputStream();
        try {
            workbook.write(baos);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            IOUtils.closeQuietly(workbook, baos);
        }
        return new ByteArrayInputStream(baos.toByteArray());
    }

行数限制

在不同版本的 Excel 中，一个Sheet的行数限制有所不同: 对于.xls格式，一个Sheet最多有 65536 行。.xlsx格式，一个Sheet最多有 1048576 行1。

如果我们要写入的数据超过这个数量，怎么办呢？如果能自动的扩展Sheet不就好了。

public static int maxRowsForHSSF = 65536;  
public static int maxRowsForXSSF = 1048576;

/**
     * 通过hssf将数据写入excel
     * @param dataList
     * @return
     */
    public static InputStream exportXls(List<List<String>> dataList) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        int sheetCount = 0;
        int currentRowIndex = 0;
        while (currentRowIndex < dataList.size()) {
            Sheet sheet = workbook.createSheet("Data" + sheetCount);
            for (int i = currentRowIndex; i < Math.min(currentRowIndex + maxRowsForHSSF, dataList.size()); i++) {
                List<String> rowList = dataList.get(i);
                Row row = sheet.createRow(i - currentRowIndex);
                for (int j = 0; j < rowList.size(); j++) {
                    Cell cell = row.createCell(j);
                    cell.setCellValue(rowList.get(j));
                }
            }
            currentRowIndex += maxRowsForHSSF;
            sheetCount++;
        }
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        try {
            workbook.write(baos);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            IOUtils.closeQuietly(workbook, baos);
        }
        return new ByteArrayInputStream(baos.toByteArray());
    }

性能对比

我们构造一个4列50000行，共计20万个单元格的列表，写入Excel，对比一下各个方法的执行耗时情况：

   public static void main(String[] args) {
        List<String> header = Arrays.asList("姓名", "年龄", "性别", "地址");
        List<List<String>> dataList = new ArrayList<>();
        for (int i = 0; i < 50000; i++) {
            dataList.add(header);
        }

        // 记录开始时间
        long start = System.currentTimeMillis();
        InputStream is = exportXls(dataList);
        long end = System.currentTimeMillis();
        InputStream isXlsx = exportXlsx(dataList);
        long end2 = System.currentTimeMillis();
        InputStream isXlsx2 = exportXlsxBySxssf(dataList);
        long end3 = System.currentTimeMillis();

        System.out.println("HSSF耗时：" + (end - start) + "ms");
        System.out.println("XSSF耗时：" + (end2 - end) + "ms");
        System.out.println("SXSSF耗时：" + (end3 - end2) + "ms");

        writeFile(is, "D:\\temp\\test_hssf.xls");
        writeFile(isXlsx, "D:\\temp\\test_xssf.xlsx");
        writeFile(isXlsx2, "D:\\temp\\test_sxssf.xlsx");

    }

输出结果：

// 4列 50000行
HSSF耗时：3657ms
XSSF耗时：2757ms
SXSSF耗时：738ms

我们再将列扩大5倍，即20列5万行：

HSSF耗时：11377ms
XSSF耗时：10128ms
SXSSF耗时：1895ms

进一步扩大行数，即20列20万行：

HSSF耗时：58823ms
XSSF耗时：76649ms
SXSSF耗时：6957ms

发现在较大数据量时，性能比对，SXSSF >> HSSF > XSSF。总结为以下三点：

1. 无论数量大小，性能上SXSSF都要远远优与HSSF和XSSF；
2. XSSF与HSSF性能差别不大，HSSF在大数据量时甚至要优与XSSF。

参考资料

[1] 查找XLS和XLSX格式支持的最大行数和列数