VB.Net账单软件Excel下拉框选择后余额显示错误问题求助

核心问题原因 #

1. foundValue全局变量残留旧值：代码中foundValue未在方法内局部声明，若为全局变量，当某次查找不到目标供应商时，会保留上一次查询的余额，导致显示错误的历史数据。
2. 选中项获取方式错误：使用cmbSupplierName.Text获取供应商名称，Text属性可能是用户输入内容（若下拉框允许输入）或未及时更新的旧值，无法准确获取选中的目标供应商。
3. 字符串匹配未兼容大小写差异：直接用=做字符串比较，若Excel内的供应商名称与下拉框选项大小写不一致（如"Kathir"和"kathir"），会导致匹配失败，进而使用旧的foundValue。

修复步骤 #

1. 将foundValue改为局部变量并初始化 #

每次查询前重置foundValue，避免旧值干扰：

Dim foundValue As String = String.Empty ' 局部声明，每次方法执行时初始化为空

2. 改用SelectedItem获取选中的供应商名称 #

确保获取的是下拉框实际选中的项，而非输入框文本：

' 获取选中项，处理未选中的情况
Dim searchName As String = If(cmbSupplierName.SelectedItem IsNot Nothing, 
                              cmbSupplierName.SelectedItem.ToString().Trim(), 
                              String.Empty)

如果下拉框绑定的是自定义对象（如包含Name属性的供应商类），需调整为：

Dim selectedSupplier As YourSupplierClass = CType(cmbSupplierName.SelectedItem, YourSupplierClass)
Dim searchName As String = If(selectedSupplier IsNot Nothing, selectedSupplier.Name.Trim(), String.Empty)

3. 改用不区分大小写的字符串匹配 #

避免大小写差异导致的匹配失败：

If String.Equals(cellValue, searchName, StringComparison.OrdinalIgnoreCase) Then

4. 优化Excel操作（可选但推荐） #

每次SelectionChanged都打开Excel会导致性能低下且可能残留Excel进程，建议一次性将Excel数据加载到内存（如DataTable），后续查询直接从内存读取：

' 全局声明DataTable存储供应商数据
Private supplierData As DataTable

' 初始化时加载数据（如Form_Load事件）
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    supplierData = LoadSupplierDataFromExcel()
    ' 绑定下拉框
    cmbSupplierName.DataSource = supplierData
    cmbSupplierName.DisplayMember = "SupplierName"
    cmbSupplierName.ValueMember = "Balance"
End Sub

' 从Excel加载数据到DataTable的方法
Private Function LoadSupplierDataFromExcel() As DataTable
    ' 实现Excel读取逻辑，将数据存入DataTable后返回
End Function

' 修改SelectionChanged事件，直接从DataTable查询
Private Sub cmbSupplierName_SelectionChanged(sender As Object, e As SelectionChangedEventArgs) Handles cmbSupplierName.SelectionChanged
    If cmbSupplierName.SelectedValue IsNot Nothing Then
        txtobalance.Text = cmbSupplierName.SelectedValue.ToString()
    Else
        txtobalance.Text = "0"
    End If
End Sub

修改后的完整事件代码 #

Private Sub cmbSupplierName_SelectionChanged(sender As Object, e As SelectionChangedEventArgs) Handles cmbSupplierName.SelectionChanged
    ' 局部声明所有变量，避免全局残留
    Dim excelApp As New Microsoft.Office.Interop.Excel.Application()
    Dim workbook As Workbook = Nothing
    Dim worksheet As Worksheet = Nothing
    Dim searchName As String = If(cmbSupplierName.SelectedItem IsNot Nothing, 
                                  cmbSupplierName.SelectedItem.ToString().Trim(), 
                                  String.Empty)
    Dim totalRows As Long
    Dim foundValue As String = String.Empty ' 局部初始化

    Try
        workbook = excelApp.Workbooks.Open(excelPath)
        worksheet = CType(workbook.Sheets("Creditors"), Worksheet)

        totalRows = worksheet.Cells(worksheet.Rows.Count, 1).End(XlDirection.xlUp).Row

        For row As Integer = 2 To totalRows
            Dim cellValue As String = Convert.ToString(worksheet.Cells(row, 1).Value)?.Trim() ' 处理Null值
            If Not String.IsNullOrEmpty(cellValue) Then
                ' 不区分大小写匹配
                If String.Equals(cellValue, searchName, StringComparison.OrdinalIgnoreCase) Then
                    foundValue = Convert.ToString(worksheet.Cells(row, 2).Value)
                    Exit For
                End If
            End If
        Next

        txtobalance.Text = If(String.IsNullOrEmpty(foundValue), "0", foundValue)

    Catch ex As Exception
        MessageBox.Show("Error loading data: " & ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error)
    Finally
        If workbook IsNot Nothing Then workbook.Close(False)
        If excelApp IsNot Nothing Then excelApp.Quit()

        ReleaseObject(worksheet)
        ReleaseObject(workbook)
        ReleaseObject(excelApp)
    End Try
End Sub

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