VB.NET项目中无需DataTable直接读取Excel单元格值至文本框可行吗？

阿华AIGC实验室

2026-5-20

无需DataTable，直接读取Excel单元格值的VB.NET实现

当然可以不用DataTable直接读取目标单元格的值！完全没必要先把整个Excel数据导入DataTable，尤其是你只需要根据电话号码匹配单行数据的场景，直接定位目标行读取对应字段反而更高效。下面给你两种实用的实现方案：

方案1：使用OLEDB（无需安装Office，推荐）

这种方式通过数据库连接的方式操作Excel，不需要依赖本地Office环境，适合部署到没有装Excel的机器上。核心思路是用OleDbDataReader直接读取查询结果，跳过DataTable的内存存储：

Imports System.Data.OleDb

Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
    Dim phoneToSearch As String = txtPhoneInput.Text.Trim()
    If String.IsNullOrEmpty(phoneToSearch) Then
        MessageBox.Show("请输入电话号码！")
        Return
    End If

    ' Excel 97-2003 (.xls) 的连接字符串
    Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\Project\customer.xls;Extended Properties=""Excel 8.0;HDR=YES;"""
    
    ' 注意列名有空格的话，要用方括号包裹
    Dim sql As String = "SELECT [Name], [Date of Birth], [ID Card], [IA Code] FROM [Sheet1$] WHERE [Telephone] = @Phone"

    Using conn As New OleDbConnection(connString)
        Using cmd As New OleDbCommand(sql, conn)
            ' 参数化查询，避免SQL注入
            cmd.Parameters.AddWithValue("@Phone", phoneToSearch)
            
            Try
                conn.Open()
                Using reader As OleDbDataReader = cmd.ExecuteReader()
                    If reader.Read() Then
                        ' 读取对应字段并赋值给文本框
                        txtName.Text = reader("[Name]").ToString()
                        txtDoB.Text = reader("[Date of Birth]").ToString()
                        txtIDCard.Text = reader("[ID Card]").ToString()
                        txtIACode.Text = reader("[IA Code]").ToString()
                    Else
                        MessageBox.Show("未找到匹配的电话号码！")
                        ' 清空文本框
                        txtName.Clear()
                        txtDoB.Clear()
                        txtIDCard.Clear()
                        txtIACode.Clear()
                    End If
                End Using
            Catch ex As Exception
                MessageBox.Show($"查询出错：{ex.Message}")
            End Try
        End Using
    End Using
End Sub

方案2：使用Excel Interop（需要安装Office）

如果你开发环境或部署机器上装有Excel，可以用官方的Interop库直接操作Excel对象，适合需要更灵活Excel操作的场景：

Imports Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices

Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
    Dim phoneToSearch As String = txtPhoneInput.Text.Trim()
    If String.IsNullOrEmpty(phoneToSearch) Then
        MessageBox.Show("请输入电话号码！")
        Return
    End If

    Dim excelApp As Application = Nothing
    Dim workbook As Workbook = Nothing
    Dim worksheet As Worksheet = Nothing

    Try
        ' 创建Excel应用实例
        excelApp = New Application()
        ' 后台运行，不显示Excel窗口
        excelApp.Visible = False
        ' 打开目标工作簿
        workbook = excelApp.Workbooks.Open("d:\Project\customer.xls")
        ' 假设数据在Sheet1，根据实际表名修改
        worksheet = workbook.Sheets("Sheet1")

        ' 查找Telephone列中匹配的单元格（Telephone是第4列，对应D列）
        Dim foundCell As Range = worksheet.Columns("D").Find(What:=phoneToSearch, LookIn:=XlFindLookIn.xlValues, LookAt:=XlLookAt.xlWhole)
        
        If foundCell IsNot Nothing Then
            ' 获取匹配行的对应列值（Excel行号从1开始：Name是B列=2，DoB是E列=5，ID Card是F列=6，IA Code是G列=7）
            txtName.Text = worksheet.Cells(foundCell.Row, 2).Value?.ToString()
            txtDoB.Text = worksheet.Cells(foundCell.Row, 5).Value?.ToString()
            txtIDCard.Text = worksheet.Cells(foundCell.Row, 6).Value?.ToString()
            txtIACode.Text = worksheet.Cells(foundCell.Row, 7).Value?.ToString()
        Else
            MessageBox.Show("未找到匹配的电话号码！")
            txtName.Clear()
            txtDoB.Clear()
            txtIDCard.Clear()
            txtIACode.Clear()
        End If
    Catch ex As Exception
        MessageBox.Show($"操作出错：{ex.Message}")
    Finally
        ' 必须释放Excel资源，避免残留进程
        If worksheet IsNot Nothing Then Marshal.ReleaseComObject(worksheet)
        If workbook IsNot Nothing Then
            workbook.Close(SaveChanges:=False)
            Marshal.ReleaseComObject(workbook)
        End If
        If excelApp IsNot Nothing Then
            excelApp.Quit()
            Marshal.ReleaseComObject(excelApp)
        End If
    End Try
End Sub