如何将数据库查询结果导出至Excel文件？含Java代码场景咨询

Great question! Since you already have your data loaded into a JTable using DbUtils.resultSetToTableModel(rs), we can leverage that existing TableModel to make exporting to Excel really straightforward—no need to re-run the database query or work directly with the ResultSet (which becomes unusable once your connection closes). Here are two easy, reliable approaches:

Apache POI is the industry-standard Java library for working with Excel files. It lets you create fully formatted spreadsheets with headers, styling, and auto-sized columns.

Step 1: Add POI Dependencies #

If you're using Maven, add these to your pom.xml:

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

For manual JAR setups, download poi.jar and poi-ooxml.jar from the Apache POI project and add them to your project's classpath.

Step 2: Export Method Code #

This method takes your TableModel and output path to generate a polished Excel file:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.swing.table.TableModel;
import java.io.FileOutputStream;
import java.io.IOException;

public static void exportToExcel(TableModel tableModel, String outputPath) throws IOException {
    // Create a new workbook and sheet
    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet("Person Data");

    // Style header row with bold text
    CellStyle headerStyle = workbook.createCellStyle();
    Font boldFont = workbook.createFont();
    boldFont.setBold(true);
    headerStyle.setFont(boldFont);

    // Write header cells
    Row headerRow = sheet.createRow(0);
    for (int col = 0; col < tableModel.getColumnCount(); col++) {
        Cell cell = headerRow.createCell(col);
        cell.setCellValue(tableModel.getColumnName(col));
        cell.setCellStyle(headerStyle);
    }

    // Populate data rows
    for (int rowIdx = 0; rowIdx < tableModel.getRowCount(); rowIdx++) {
        Row dataRow = sheet.createRow(rowIdx + 1);
        for (int colIdx = 0; colIdx < tableModel.getColumnCount(); colIdx++) {
            Object cellValue = tableModel.getValueAt(rowIdx, colIdx);
            Cell cell = dataRow.createCell(colIdx);

            // Handle common data types (extend for Date/BigDecimal if needed)
            if (cellValue instanceof String) {
                cell.setCellValue((String) cellValue);
            } else if (cellValue instanceof Integer) {
                cell.setCellValue((Integer) cellValue);
            } else if (cellValue != null) {
                cell.setCellValue(cellValue.toString());
            }
        }
    }

    // Auto-size columns for readability
    for (int col = 0; col < tableModel.getColumnCount(); col++) {
        sheet.autoSizeColumn(col);
    }

    // Write to file
    try (FileOutputStream fos = new FileOutputStream(outputPath)) {
        workbook.write(fos);
    }
    workbook.close();
}

Step 3: Call the Method #

Since your jTable1 already holds the data, just run:

try {
    exportToExcel(jTable1.getModel(), "C:/your/path/persons_data.xlsx");
    System.out.println("Excel export completed successfully!");
} catch (IOException e) {
    e.printStackTrace();
}

If you don't need advanced Excel formatting, exporting to CSV is a zero-dependency option—Excel can open CSV files natively, and it's lightning-fast.

Export Method Code #

import javax.swing.table.TableModel;
import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;

public static void exportToCSV(TableModel tableModel, String outputPath) throws IOException {
    try (BufferedWriter writer = new BufferedWriter(new FileWriter(outputPath))) {
        // Write header row
        for (int col = 0; col < tableModel.getColumnCount(); col++) {
            writer.write(tableModel.getColumnName(col));
            if (col < tableModel.getColumnCount() - 1) {
                writer.write(",");
            }
        }
        writer.newLine();

        // Write data rows
        for (int rowIdx = 0; rowIdx < tableModel.getRowCount(); rowIdx++) {
            for (int colIdx = 0; colIdx < tableModel.getColumnCount(); colIdx++) {
                Object cellValue = tableModel.getValueAt(rowIdx, colIdx);
                writer.write(cellValue != null ? cellValue.toString() : "");
                if (colIdx < tableModel.getColumnCount() - 1) {
                    writer.write(",");
                }
            }
            writer.newLine();
        }
    }
}

Call the Method #

try {
    exportToCSV(jTable1.getModel(), "C:/your/path/persons_data.csv");
    System.out.println("CSV export completed successfully!");
} catch (IOException e) {
    e.printStackTrace();
}

Quick Tip #

Using the TableModel is better than reusing the ResultSet because:

• The ResultSet is tied to an open database connection and becomes useless once closed, while the TableModel holds data in memory.
• You avoid keeping a database connection open during export, which is more efficient and reduces resource leaks.

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