使用SXSSFWorkbook实现高效百万数据导入Excel的方法

在当今数据驱动的世界中，处理大规模数据并将其可视化是许多企业和组织面临的重要挑战之一。导出数据到Excel是常见的需求，但是当数据量很大时，常规的Excel库可能会遇到内存不足的问题。为了解决这个问题，Apache POI库提供了SXSSFWorkbook，这是一个针对大型数据集的高效处理工具。本文将介绍如何使用SXSSFWorkbook实现高效地将大规模数据导入Excel的方法。

yiridancan

5685人浏览 · 2024-03-13 10:23:31

yiridancan · 2024-03-13 10:23:31 发布

1. 理解SXSSFWorkbook

SXSSFWorkbook是Apache POI库的一部分，它是XSSFWorkbook的扩展版本，专门设计用于处理大型数据集。相比于XSSFWorkbook，SXSSFWorkbook通过将数据写入临时文件而不是保存在内存中来节省内存，因此可以处理更大的数据集。

2.XSSFWorkbook、HSSFWorkbook、SXSSFWorkbook的区别

XSSFWorkbook、HSSFWorkbook和SXSSFWorkbook是Apache POI库中用于处理Excel文件的不同组件，它们之间有一些区别，主要体现在以下几个方面：

内存占用:
- XSSFWorkbook：XSSFWorkbook适用于处理较小的Excel文件，它将整个Excel文件加载到内存中。因此，当处理大型Excel文件时，可能会遇到内存不足的问题
- HSSFWorkbook：HSSFWorkbook是用于处理旧版Excel（.xls格式）的组件，也将整个文件加载到内存中
- SXSSFWorkbook：SXSSFWorkbook是XSSFWorkbook的扩展版本，专门设计用于处理大量数据。与前两者不同，SXSSFWorkbook通过将数据写入临时文件而不是保存在内存中，以节省内存。这使得它能够处理更大的数据集，避免了内存溢出的问题
文件格式支持：
- XSSFWorkbook和SXSSFWorkbook：主要用于处理Excel 2007及以上版本的文件（.xlsx格式）
- HSSFWorkbook：用于处理旧版Excel（Excel 97-2003，.xls格式）
使用场景：
- XSSFWorkbook和HSSFWorkbook适用于处理小到中等大小的Excel文件，以及对内存使用要求不高的情况
- SXSSFWorkbook更适用于处理大型数据集，以及需要更高性能和较少内存消耗的情况
写入性能：
- XSSFWorkbook和HSSFWorkbook在写入大量数据时可能会遇到性能问题，因为它们将整个文件保存在内存中
- SXSSFWorkbook通过流式写入的方式，可以逐行逐列地将数据写入Excel文件，因此在处理大量数据时性能更好

总的来说，如果您需要处理大型数据集或者对内存使用有限制，推荐使用SXSSFWorkbook。而对于小到中等大小的Excel文件，您可以考虑使用XSSFWorkbook或HSSFWorkbook。

3.准备工作

在使用SXSSFWorkbook之前，您需要确保已经正确配置了Apache POI库和相关依赖项。但是要注意，不同版本之间可能存在一些不兼容的变化。因此，在升级或更改版本时，建议您查阅官方文档或发布说明，以了解版本之间的差异，并确保您的代码与新版本兼容。另外，一些具体的功能可能需要特定版本的Apache POI库来支持。例如，如果您需要处理.xlsx格式的Excel文件，则需要使用poi-ooxml模块。如果您需要处理.docx格式的Word文档，则可能需要poi-ooxml-schemas模块。

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>5.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.1.0</version>
        </dependency>

4.SXSSFWorkbook示例代码

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

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

public class Test {

    //导出附件路径
    private static final String PATH ="D://exportExcel.xlsx";

    @org.junit.Test
    public void exportExcel() throws IOException {
        Long startTime = System.currentTimeMillis();
        FileOutputStream fileOut = null;
        SXSSFWorkbook workbook = null;
        try  {

            workbook = new SXSSFWorkbook();
            fileOut = new FileOutputStream(PATH);
            //指定sheet名称
            Sheet sheet = workbook.createSheet("Sheet1");
            List<String[]> data = new ArrayList<>();
            //模拟100万条数据
            for (int i = 0; i < 1000000; i++) {
                String[] rowData = new String[]{UUID.randomUUID().toString(),"数据" + i, "其他数据" + i};
                data.add(rowData);
            }
            //导入数据
            for (int i = 0; i < data.size(); i++) {
                Row row = sheet.createRow(i);
                String[] rowData = data.get(i);
                for (int j = 0; j < rowData.length; j++) {
                    Cell cell = row.createCell(j);
                    cell.setCellValue(rowData[j]);
                }
            }
            workbook.write(fileOut);
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            // 清理临时文件
            workbook.dispose();
            if(null != fileOut){
                fileOut.close();
            }
        }
        Long endTime = System.currentTimeMillis();
        System.out.println("数据导出到excel所花时间："+String.valueOf(endTime-startTime));
    }
}

一百万条数据导入到Excel只花了11秒