Java学生成绩Excel上传工具：下拉选项与柱状图错误修复请求

我正在为学校项目开发一款工具，可上传包含学生成绩的Excel表格并生成柱状图。

Excel表格格式 #

表格包含以下列：
| Student ID | Subject ID | Term (semester) | DP-Year | Final-Grade |

每位学生修读6门科目，每个DP学年（分为Year 1和Year 2）包含4个学期。

功能需求 #

• 工具需提供Student ID、DP-Year、Term的筛选选项
• 生成的柱状图要求：
  • X轴为6门科目
  • Y轴为1-7分的成绩

当前代码（BlueJ编写） #

import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;
import java.util.List;
import javax.swing.*;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableColumnModel;
import javax.swing.table.TableModel;
import javax.swing.table.TableRowSorter;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.ss.usermodel.*;
import org.jfree.chart.ChartFactory;
import org.jfree.chart.ChartPanel;
import org.jfree.chart.JFreeChart;
import org.jfree.chart.plot.PlotOrientation;
import org.jfree.data.category.DefaultCategoryDataset;

public class HomeScreen extends JFrame {
    private JTable resultTable;

    private DefaultTableModel tableModel;

    private List<JComboBox<String>> columnFilterDropdowns;

    private TableRowSorter<TableModel> sorter;

    private ChartPanel chartPanel;

    private JFrame chartFrame;

    public HomeScreen() {
        setTitle("Excel File Uploader");

        setSize(800, 600);

        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

        JPanel panel = new JPanel(new BorderLayout());

        JButton uploadButton = new JButton("Upload Excel");

        uploadButton.addActionListener(new ActionListener() {
            @Override

            public void actionPerformed(ActionEvent e) {
                JFileChooser fileChooser = new JFileChooser();

                int result = fileChooser.showOpenDialog(null);

                if (result == JFileChooser.APPROVE_OPTION) {
                    try {
                        File selectedFile = fileChooser.getSelectedFile();

                        displayExcelContent(selectedFile);

                        updateColumnFilterDropdowns();

                    } catch (Exception ee) {
                        ee.printStackTrace();
                    }
                }
            }
        });

        tableModel = new DefaultTableModel();

        resultTable = new JTable(tableModel);

        resultTable.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);

        columnFilterDropdowns = new ArrayList<>();

        sorter = new TableRowSorter<>(tableModel);

        resultTable.setRowSorter(sorter);

        JScrollPane scrollPane = new JScrollPane(resultTable);

        panel.add(uploadButton, BorderLayout.NORTH);

        panel.add(scrollPane, BorderLayout.CENTER);

        add(panel);

        setLocationRelativeTo(null);
        setVisible(true);
    }

    private void displayExcelContent(File file) throws Exception {
        try (InputStream inputStream = new FileInputStream(file)) {
            Workbook workbook = WorkbookFactory.create(inputStream);
            List<List<String>> excelData = readExcel(workbook);

            // Extract column names
            String[] columnNames = excelData.get(0).toArray(new String[0]);
            // Remove the header row
            excelData.remove(0);

            // Convert data to a two-dimensional array
            Object[][] data = new Object[excelData.size()][];
            for (int i = 0; i < excelData.size(); i++) {
                List<String> rowData = excelData.get(i);
                data[i] = rowData.toArray(new Object[0]);
            }

            // Set data to the DefaultTableModel
            tableModel.setDataVector(data, columnNames);

            // Display bar chart
            displayBarChart(tableModel);

        } catch (IOException | EncryptedDocumentException ex) {
            ex.printStackTrace();
            JOptionPane.showMessageDialog(this, "Error reading the Excel file",
                "Error", JOptionPane.ERROR_MESSAGE);
        }
    }

    private List<List<String>> readExcel(Workbook workbook) {
        List<List<String>> excelData = new ArrayList<>();

        Sheet sheet = workbook.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.iterator();

        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();

            List<String> rowData = new ArrayList<>();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        rowData.add(cell.getStringCellValue());
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        rowData.add(String.valueOf(cell.getNumericCellValue()));
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        rowData.add(String.valueOf(cell.getBooleanCellValue()));
                        break;
                    default:
                        rowData.add("");
                }
            }
            excelData.add(rowData);
        }

        return excelData;
    }

    private void displayBarChart(DefaultTableModel tableModel) {
        Map<String, Integer> gradeCountMap = new HashMap<>();

        for (int rowIndex = 0; rowIndex < tableModel.getRowCount();
             rowIndex++) {
            String finalGrade =
                tableModel.getValueAt(rowIndex, tableModel.getColumnCount() - 1)
                    .toString();
            gradeCountMap.put(
                finalGrade, gradeCountMap.getOrDefault(finalGrade, 0) + 1);
        }

        DefaultCategoryDataset dataset = new DefaultCategoryDataset();

        for (Map.Entry<String, Integer> entry : gradeCountMap.entrySet()) {
            dataset.addValue(entry.getValue(), "Grades", entry.getKey());
        }

        JFreeChart barChart = ChartFactory.createBarChart(
            "Final Grade Distribution", // Chart title
            "Sujects", // X-axis label
            "Grades Achieved", // Y-axis label
            dataset, // Dataset
            PlotOrientation.VERTICAL, true, true, false);

        if (chartPanel != null && chartFrame != null) {
            chartFrame.getContentPane().remove(chartPanel);
            chartFrame.getContentPane().revalidate();
            chartFrame.getContentPane().repaint();
            chartFrame.dispose();
        }

        chartPanel = new ChartPanel(barChart);
        chartPanel.setPreferredSize(new Dimension(400, 300));

        chartFrame = new JFrame("Bar Chart");
        chartFrame.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
        chartFrame.getContentPane().add(chartPanel);
        chartFrame.pack();
        chartFrame.setLocationRelativeTo(null);
        chartFrame.setVisible(true);
    }

    private void updateColumnFilterDropdowns() {
        // Remove existing dropdowns
        for (JComboBox<String> comboBox : columnFilterDropdowns) {
            remove(comboBox);
        }
        columnFilterDropdowns.clear();

        // Create and add new dropdowns to a separate panel
        JPanel dropdownPanel =
            new JPanel(new FlowLayout()); // Use an appropriate layout
        TableColumnModel columnModel = resultTable.getColumnModel();
        for (int columnIndex = 0; columnIndex < 4;
             /*columnModel.getColumnCount(); */ columnIndex++) {
            JComboBox<String> columnDropdown =
                createColumnDropdown(columnIndex);
            columnFilterDropdowns.add(columnDropdown);
            dropdownPanel.add(columnDropdown);
        }

        // Add the dropdown panel to the main panel
        add(dropdownPanel, BorderLayout.SOUTH);

        revalidate();
        repaint();
    }

    private JComboBox<String> createColumnDropdown(int columnIndex) {
        Vector<String> distinctValues = getDistinctColumnValues(columnIndex);
        JComboBox<String> comboBox = new JComboBox<>(distinctValues);
        comboBox.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                applyFilters();
            }
        });
        return comboBox;
    }

    private Vector<String> getDistinctColumnValues(int columnIndex) {
        Vector<String> distinctValues = new Vector<>();
        TableModel model = resultTable.getModel();

        for (int rowIndex = 0; rowIndex < model.getRowCount(); rowIndex++) {
            Object value = model.getValueAt(rowIndex, columnIndex);
            if (!distinctValues.contains(value)) {
                distinctValues.add(value.toString());
            }
        }

        return distinctValues;
    }

    private void applyFilters() {
        List<RowFilter<Object, Object>> filters = new ArrayList<>();
        for (int columnIndex = 0; columnIndex < columnFilterDropdowns.size();
             columnIndex++) {
            JComboBox<String> comboBox = columnFilterDropdowns.get(columnIndex);
            String selectedValue = (String) comboBox.getSelectedItem();
            if (selectedValue != null && !selectedValue.isEmpty()) {
                filters.add(RowFilter.regexFilter(selectedValue, columnIndex));
            }
        }
        RowFilter<Object, Object> compoundRowFilter =
            RowFilter.andFilter(filters);
        sorter.setRowFilter(compoundRowFilter);

        // Refresh the bar chart
        displayBarChart(tableModel);
    }

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

当前问题 #

目前代码存在两个主要问题：

1. 生成的柱状图不符合需求：当前图表统计的是各分数的出现次数，而非指定学生在对应学年、学期下每门科目的成绩
2. 下拉筛选选项存在错误：当前生成了4个下拉框，但实际只需要Student ID、DP-Year、Term这三个筛选项，且筛选后图表未按要求更新

希望修复这些问题，实现按指定Student ID、DP-Year、Term生成符合要求的柱状图。

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