从数据库多表提取ResultSet并写入Excel分表的技术实现咨询

Hey there! Let's break down how to pull data from multiple database tables, store each as a ResultSet, then write each table's data (including column headers) into separate sheets of the same Excel file. I'll use Java with JDBC and Apache POI—this stack is reliable, widely adopted, and perfect for this task.

Step 1: Set Up Dependencies #

First, add Apache POI dependencies to your project (if using Maven):

<dependencies>
    <!-- For Excel .xlsx files -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.2.5</version>
    </dependency>
    <!-- JDBC driver for your database (example: MySQL) -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.33</version>
    </dependency>
</dependencies>

For Gradle, adjust the dependencies accordingly to match your build setup.

Step 2: Implementation Code #

Here's a complete, reusable class that handles the entire workflow. I've added comments to explain each key part:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.sql.*;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

public class DbToExcelExporter {

    // Replace these with your actual database credentials
    private static final String DB_URL = "jdbc:mysql://localhost:3306/your_database";
    private static final String DB_USER = "your_username";
    private static final String DB_PASSWORD = "your_password";

    // Get a database connection using JDBC
    private static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
    }

    // Main entry point to trigger the export
    public static void main(String[] args) {
        // List of tables you want to export (customize this list)
        List<String> tablesToExport = List.of("customers", "orders", "products", "categories");
        String outputExcelPath = "database_export.xlsx";

        try (Workbook workbook = new XSSFWorkbook()) {
            for (String tableName : tablesToExport) {
                // Create a sheet for each table (clean up the name to meet Excel's rules)
                String sheetName = sanitizeSheetName(tableName);
                Sheet sheet = workbook.createSheet(sheetName);

                // Fetch data from the table and write it to the sheet
                try (Connection conn = getConnection();
                     Statement stmt = conn.createStatement();
                     ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName)) {

                    // Write column headers to the first row
                    writeColumnHeaders(rs, sheet);

                    // Write all data rows from the ResultSet
                    writeDataRows(rs, sheet);

                    // Auto-size columns for better readability
                    for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
                        sheet.autoSizeColumn(i);
                    }
                } catch (SQLException e) {
                    System.err.println("Failed to process table: " + tableName);
                    e.printStackTrace();
                }
            }

            // Save the final Excel file
            try (FileOutputStream fos = new FileOutputStream(outputExcelPath)) {
                workbook.write(fos);
                System.out.println("Export done! File saved to: " + outputExcelPath);
            }
        } catch (IOException e) {
            System.err.println("Error creating or writing to Excel file");
            e.printStackTrace();
        }
    }

    // Write column names from ResultSet metadata to the sheet's header row
    private static void writeColumnHeaders(ResultSet rs, Sheet sheet) throws SQLException {
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();

        Row headerRow = sheet.createRow(0);
        // Style the header row to make it stand out
        CellStyle headerStyle = sheet.getWorkbook().createCellStyle();
        Font headerFont = sheet.getWorkbook().createFont();
        headerFont.setBold(true);
        headerStyle.setFont(headerFont);

        for (int i = 1; i <= columnCount; i++) {
            Cell cell = headerRow.createCell(i - 1);
            cell.setCellValue(metaData.getColumnName(i));
            cell.setCellStyle(headerStyle);
        }
    }

    // Iterate through the ResultSet and write each row to the sheet
    private static void writeDataRows(ResultSet rs, Sheet sheet) throws SQLException {
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();
        int rowNum = 1; // Start after the header row

        while (rs.next()) {
            Row row = sheet.createRow(rowNum++);
            for (int i = 1; i <= columnCount; i++) {
                Object value = rs.getObject(i);
                Cell cell = row.createCell(i - 1);

                // Handle different data types (extend this based on your DB schema)
                if (value instanceof String) {
                    cell.setCellValue((String) value);
                } else if (value instanceof Integer) {
                    cell.setCellValue((Integer) value);
                } else if (value instanceof Double) {
                    cell.setCellValue((Double) value);
                } else if (value instanceof Date) {
                    cell.setCellValue((Date) value);
                    // Format dates for readability
                    CellStyle dateStyle = sheet.getWorkbook().createCellStyle();
                    CreationHelper creationHelper = sheet.getWorkbook().getCreationHelper();
                    dateStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
                    cell.setCellStyle(dateStyle);
                } else {
                    // Fallback to string for unsupported types
                    if (value != null) {
                        cell.setCellValue(value.toString());
                    }
                }
            }
        }
    }

    // Clean up table names to comply with Excel sheet name rules (max 31 chars, no invalid symbols)
    private static String sanitizeSheetName(String tableName) {
        String sanitized = tableName.replaceAll("[^a-zA-Z0-9_]", "_");
        return sanitized.length() > 31 ? sanitized.substring(0, 31) : sanitized;
    }
}

Key Notes & Best Practices #

• Database Driver: Swap the MySQL JDBC driver with the one matching your database (e.g., PostgreSQL, Oracle, SQL Server).
• Resource Safety: We use try-with-resources to automatically close connections, statements, result sets, and the workbook—this prevents resource leaks and simplifies cleanup.
• Sheet Name Rules: Excel restricts sheet names to 31 characters and blocks symbols like /\:*?[]. The sanitizeSheetName method fixes this automatically.
• Large Datasets: If exporting tables with millions of rows, use stmt.setFetchSize(n) to load rows in batches and avoid memory overload.
• Data Type Customization: Extend the writeDataRows method to handle additional types like BigDecimal or Boolean if your schema uses them.

Alternative No-Code Options #

If you don't want to write code, these tools can handle the task:

• Pentaho Data Integration (Kettle): A visual ETL tool with pre-built connectors for databases and Excel.
• Apache Camel: A routing framework with components for DB-to-Excel workflows.

内容的提问来源于stack exchange，提问作者nirmal