关于使用Java程序实现Excel数据检索与GUI结果回显的技术问询

Got it, let's put together a Java program that lets you search Excel data and display matching rows in Swing text fields. We'll use Apache POI for handling Excel files and Swing for the GUI components—this is the standard approach for this kind of task.

Prerequisites #

First, add the Apache POI dependencies to your project. If you're using Maven, drop these into your pom.xml:

<dependencies>
    <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>
</dependencies>

For Gradle users, add these to your build.gradle:

implementation 'org.apache.poi:poi:5.2.5'
implementation 'org.apache.poi:poi-ooxml:5.2.5'

Step 1: Build the Swing GUI #

We'll create a simple window with:

• A text field for entering search keywords
• A "Search" button to trigger the lookup
• Three text fields to display matching row data (adjust the number based on your Excel columns)

Here's the GUI setup code:

import javax.swing.*;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.util.List;

public class ExcelSearchGUI extends JFrame {
    private final JTextField searchInput;
    private final JTextField resultField1;
    private final JTextField resultField2;
    private final JTextField resultField3;
    private static final String EXCEL_PATH = "path/to/your/excel/file.xlsx"; // Replace with your file path

    public ExcelSearchGUI() {
        // Frame setup
        setTitle("Excel Data Search");
        setSize(500, 200);
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        setLayout(new GridBagLayout());
        GridBagConstraints gbc = new GridBagConstraints();
        gbc.insets = new Insets(5, 5, 5, 5);

        // Search input
        gbc.gridx = 0;
        gbc.gridy = 0;
        add(new JLabel("Search Keyword:"), gbc);

        searchInput = new JTextField(20);
        gbc.gridx = 1;
        add(searchInput, gbc);

        // Search button
        JButton searchBtn = new JButton("Search");
        gbc.gridx = 2;
        add(searchBtn, gbc);

        // Result fields (adjust based on your Excel columns)
        gbc.gridx = 0;
        gbc.gridy = 1;
        add(new JLabel("Column 1:"), gbc);

        resultField1 = new JTextField(20);
        resultField1.setEditable(false);
        gbc.gridx = 1;
        add(resultField1, gbc);

        gbc.gridx = 0;
        gbc.gridy = 2;
        add(new JLabel("Column 2:"), gbc);

        resultField2 = new JTextField(20);
        resultField2.setEditable(false);
        gbc.gridx = 1;
        add(resultField2, gbc);

        gbc.gridx = 0;
        gbc.gridy = 3;
        add(new JLabel("Column 3:"), gbc);

        resultField3 = new JTextField(20);
        resultField3.setEditable(false);
        gbc.gridx = 1;
        add(resultField3, gbc);

        // Add button action listener
        searchBtn.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                performSearch();
            }
        });
    }

    // ... Rest of the code goes here
}

Step 2: Implement the Excel Search Logic #

Next, we'll write a method that reads the Excel file, searches for the keyword in all cells, and returns the first matching row (you can modify this to return all matches if needed). We'll handle both .xls and .xlsx files using WorkbookFactory.

Add this method inside the ExcelSearchGUI class:

private List<String> searchExcel(String keyword) {
    try (var workbook = org.apache.poi.ss.usermodel.WorkbookFactory.create(new java.io.File(EXCEL_PATH))) {
        var sheet = workbook.getSheetAt(0); // Use the first sheet; adjust if needed

        for (var row : sheet) {
            for (var cell : row) {
                String cellValue = getCellValueAsString(cell);
                if (cellValue.contains(keyword)) {
                    // Extract all cells from this row
                    List<String> rowData = new java.util.ArrayList<>();
                    for (var rowCell : row) {
                        rowData.add(getCellValueAsString(rowCell));
                    }
                    return rowData; // Return first matching row
                }
            }
        }
    } catch (Exception ex) {
        JOptionPane.showMessageDialog(this, "Error reading Excel file: " + ex.getMessage(), "Error", JOptionPane.ERROR_MESSAGE);
        ex.printStackTrace();
    }
    return null; // No match found
}

// Helper method to convert any cell type to String
private String getCellValueAsString(org.apache.poi.ss.usermodel.Cell cell) {
    if (cell == null) {
        return "";
    }
    return switch (cell.getCellType()) {
        case STRING -> cell.getStringCellValue();
        case NUMERIC -> String.valueOf(cell.getNumericCellValue());
        case BOOLEAN -> String.valueOf(cell.getBooleanCellValue());
        case FORMULA -> cell.getCellFormula();
        default -> "";
    };
}

Step 3: Connect Search Logic to GUI #

Finally, implement the performSearch() method that triggers the search and populates the result fields:

private void performSearch() {
    String keyword = searchInput.getText().trim();
    if (keyword.isEmpty()) {
        JOptionPane.showMessageDialog(this, "Please enter a search keyword!", "Warning", JOptionPane.WARNING_MESSAGE);
        return;
    }

    List<String> matchingRow = searchExcel(keyword);
    if (matchingRow != null && !matchingRow.isEmpty()) {
        // Populate result fields (adjust indices based on your Excel columns)
        resultField1.setText(matchingRow.size() >= 1 ? matchingRow.get(0) : "");
        resultField2.setText(matchingRow.size() >= 2 ? matchingRow.get(1) : "");
        resultField3.setText(matchingRow.size() >= 3 ? matchingRow.get(2) : "");
    } else {
        JOptionPane.showMessageDialog(this, "No matching rows found!", "Info", JOptionPane.INFORMATION_MESSAGE);
        // Clear result fields
        resultField1.setText("");
        resultField2.setText("");
        resultField3.setText("");
    }
}

Main Method to Run the Program #

Add the main method to launch the GUI:

public static void main(String[] args) {
    SwingUtilities.invokeLater(() -> {
        new ExcelSearchGUI().setVisible(true);
    });
}

Key Notes & Improvements #

• Handle Multiple Matches: Right now, the code returns the first matching row. To show all matches, you could use a JTable instead of individual text fields.
• Large Excel Files: For very large Excel files, use SXSSFWorkbook instead of XSSFWorkbook to avoid memory issues—it uses streaming to process rows without loading the entire file into memory.
• Case Insensitivity: Modify the search to be case-insensitive by converting both the keyword and cell value to lowercase: if (cellValue.toLowerCase().contains(keyword.toLowerCase())).
• Specific Column Search: If you only want to search in a specific column, remove the inner loop and target the desired column index directly.

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