Java，excel大量百万数据导出优化措施，SXSSFWorkbook流式、分批次导出示例

你的名字 2024-03-22 08:40 44阅读 0赞

在导出百万级的数据时，如果不采用适当的优化措施，确实可能会造成死机和内存崩溃等问题。

为避免这些问题，可以采用以下优化措施：

1.  分批次读取数据：将需要导出的数据分成多个批次进行读取和写入，每次读取部分数据，写入 Excel 后即时清除内存。这样可以避免一次性加载全部数据导致内存占用过大的问题。
2.  使用流式写入方式：在遍历结果集的同时，使用流式写入方式（如 SXSSF）将数据写入 Excel 表格中，避免一次性将所有数据都写入内存中。
3.  关闭不必要的连接：在写代码时应当及时关闭 ResultSet、Statement 和 Connection 连接，以释放资源，减轻系统负担。
4.  使用合适的服务器硬件配置: 导出数据量较大的情况下，建议使用高性能的服务器，如配备更多的 CPU和内存， 以提高系统处理能力和速度。
5.  合理设置 JVM 内存分配：如果 JVM 内存分配不合理，则可能会导致内存溢出等问题。可以通过调整-Xmx、-Xms 等参数，来合理设置 JVM 的内存分配。

在 pom.xml 文件中添加以下依赖：

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

public void exportExcel() throws Exception{
        // 加载驱动程序
        Class.forName("com.mysql.cj.jdbc.Driver");
    
        // 连接数据库
        Connection conn = DriverManager.getConnection(url, username, password);
    
        // 查询语句
        String sql = "SELECT * FROM user";
        Statement stmt = conn.createStatement();
        ResultSet rs = null;
    
        // 创建一个 Excel 文档
        SXSSFWorkbook workbook = new SXSSFWorkbook(100);
    
        try {
            // 设置表格格式
            Sheet sheet = workbook.createSheet("数据导出");
            Row headerRow = sheet.createRow(0);
            String[] headers = {
         "ID", "姓名", "年龄"};
            for (int i = 0; i < headers.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers[i]);
            }
            
            int rownum = 1;
            while (true) {
                rs = stmt.executeQuery(sql + " LIMIT " + rownum + ", 10000"); // 每次查询10000条数据
    
                if (!rs.next()) { // 数据已经读取完
                    rs.close();
                    break;
                }
    
                do {
                    Row row = sheet.createRow(rownum++);
                    String id = rs.getString("id");
                    String name = rs.getString("name");
                    String age = rs.getString("age");
    
                    row.createCell(0).setCellValue(id);
                    row.createCell(1).setCellValue(name);
                    row.createCell(2).setCellValue(age);
                } while (rs.next());
    
                ((SXSSFSheet) sheet).flushRows();
                ((SXSSFSheet) sheet).clearRowBreaks();
            }
    
            // 保存 Excel 文件
            FileOutputStream out = new FileOutputStream("数据导出.xlsx");
            workbook.write(out);
            out.close();
    
        } finally {
            // 关闭连接
            if (rs != null) { rs.close(); }
            stmt.close();
            conn.close();
    
            // 释放资源
            workbook.dispose();
        }
    
    }

分批次读取数据、使用流式写入方式、设置合理的缓存大小以及关闭不必要的连接等优化措施，大大提高了代码效率和稳定性。同时也建议尽量避免在导出时一次性导出所有数据，对于百万级别的数据可以采用类似以上示例所示的方式进行分页导出。