VB.NET无外部下载实现Excel表格导入DataGridView的方法

• 公司开发环境严禁任何下载/安装操作
• 设备为64位，应用支持32/64位，但64位环境未安装ACE DB驱动（32位侧已安装），驱动不匹配
• 尝试用VBA中可行的ACE/JET连接字符串（ADODB方式）导入Excel，报错：System.Runtime.InteropServices.COMException: 找不到提供程序，可能未正确安装
• 无法通过NuGet获取OleDbConnection相关包
• 当前使用的ADODB代码：

Public Sub ImportWorksheet(WorkbookAddress As String, WorksheetName As String)
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim comm As ADODB.Command
    Dim path As String
    Dim sSQL As String


    conn = New ADODB.Connection
    rs = New ADODB.Recordset
    comm = New ADODB.Command
    conn.Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & WorkbookAddress & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=2""")

    With comm
        sSQL = "SELECT * FROM [" + WorksheetName + "$]"
        .CommandType = ADODB.CommandTypeEnum.adCmdText
        .CommandTimeout = 0
        .ActiveConnection = conn
        rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
        rs.Open(sSQL, conn, ADODB.CursorTypeEnum.adOpenDynamic)
    End With
    rs = Nothing
    conn = Nothing
End Sub

• 需求：无外部下载的前提下，实现Excel表格导入DataGridView的详细新手友好方法

方案1：使用.NET Framework内置System.Data.OleDb（无需NuGet） #

适用于.NET Framework项目（非.NET Core/.NET 5+），System.Data.OleDb是框架自带组件：

1. 右键项目 → 「引用」 → 找到System.Data并勾选（默认可能已添加）
2. 修改编译配置：右键项目 → 「属性」 → 「生成」 → 「平台目标」选择x86（强制程序以32位模式运行，调用已安装的32位ACE驱动，解决64位无驱动问题）
3. 替换代码：

Imports System.Data.OleDb
Imports System.Data

Public Sub ImportExcelToDgv(WorkbookPath As String, SheetName As String, targetDgv As DataGridView)
    ' 连接字符串适配Excel 2007+格式
    Dim connStr As String = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={WorkbookPath};Extended Properties=""Excel 12.0;HDR=Yes;IMEX=2"""
    
    Using conn As New OleDbConnection(connStr)
        conn.Open()
        ' 构造查询SQL
        Dim sql As String = $"SELECT * FROM [{SheetName}$]"
        ' 使用DataAdapter填充DataTable
        Using adapter As New OleDbDataAdapter(sql, conn)
            Dim dataTable As New DataTable()
            adapter.Fill(dataTable)
            ' 绑定到DataGridView
            targetDgv.DataSource = dataTable
        End Using
    End Using
End Sub

方案2：使用Office Interop Excel（依赖本地已装Excel） #

若公司电脑已安装Excel，可直接添加COM引用，无需下载：

1. 右键项目 → 「引用」 → 「COM」 → 找到Microsoft Excel xx.x Object Library（xx.x对应Office版本，如16.0=Office2016+）并勾选
2. 实现代码：

Imports Microsoft.Office.Interop.Excel
Imports System.Data

Public Sub ImportViaExcelInterop(WorkbookPath As String, SheetName As String, targetDgv As DataGridView)
    Dim excelApp As Application = Nothing
    Dim workbook As Workbook = Nothing
    Dim worksheet As Worksheet = Nothing
    
    Try
        ' 初始化Excel应用，隐藏窗口
        excelApp = New Application()
        excelApp.Visible = False
        ' 打开目标工作簿
        workbook = excelApp.Workbooks.Open(WorkbookPath)
        ' 获取指定工作表
        worksheet = workbook.Worksheets(SheetName)
        
        ' 获取已使用的单元格范围
        Dim usedRange As Range = worksheet.UsedRange
        Dim rowCount As Integer = usedRange.Rows.Count
        Dim colCount As Integer = usedRange.Columns.Count
        
        ' 创建DataTable存储数据
        Dim dataTable As New DataTable()
        ' 添加表头（第一行）
        For col As Integer = 1 To colCount
            dataTable.Columns.Add(usedRange.Cells(1, col).Value.ToString())
        Next
        
        ' 添加数据行
        For row As Integer = 2 To rowCount
            Dim dataRow As DataRow = dataTable.NewRow()
            For col As Integer = 1 To colCount
                dataRow(col - 1) = If(usedRange.Cells(row, col).Value, "")
            Next
            dataTable.Rows.Add(dataRow)
        Next
        
        ' 绑定到DataGridView
        targetDgv.DataSource = dataTable
    Catch ex As Exception
        MsgBox($"导入失败：{ex.Message}")
    Finally
        ' 释放COM资源，避免Excel进程残留
        If worksheet IsNot Nothing Then
            System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet)
        End If
        If workbook IsNot Nothing Then
            workbook.Close(SaveChanges:=False)
            System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook)
        End If
        If excelApp IsNot Nothing Then
            excelApp.Quit()
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)
        End If
    End Try
End Sub

方案3：使用Open XML SDK（仅支持.xlsx/.xlsm格式） #

适用于.NET Framework 3.5+（系统可能预装核心组件），无需额外下载：

1. 右键项目 → 「引用」 → 找到DocumentFormat.OpenXml并勾选（若系统未预装则此方案不可用）
2. 实现代码：

Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet
Imports System.Data

Public Sub ImportViaOpenXml(WorkbookPath As String, SheetName As String, targetDgv As DataGridView)
    Dim dataTable As New DataTable()
    
    Using doc As SpreadsheetDocument = SpreadsheetDocument.Open(WorkbookPath, False)
        ' 获取工作簿部件
        Dim workbookPart As WorkbookPart = doc.WorkbookPart
        ' 查找指定工作表
        Dim sheet As Sheet = workbookPart.Workbook.Descendants(Of Sheet)().FirstOrDefault(Function(s) s.Name = SheetName)
        If sheet Is Nothing Then
            MsgBox("指定工作表不存在")
            Return
        End If
        
        Dim worksheetPart As WorksheetPart = CType(workbookPart.GetPartById(sheet.Id), WorksheetPart)
        Dim sheetData As SheetData = worksheetPart.Worksheet.Elements(Of SheetData)().First()
        
        ' 处理表头
        Dim headerRow As Row = sheetData.Elements(Of Row)().First()
        For Each cell As Cell In headerRow.Elements(Of Cell)()
            dataTable.Columns.Add(GetCellValue(doc, cell))
        Next
        
        ' 处理数据行
        For Each row As Row In sheetData.Elements(Of Row)().Skip(1)
            Dim dataRow As DataRow = dataTable.NewRow()
            Dim colIndex As Integer = 0
            For Each cell As Cell In row.Elements(Of Cell)()
                dataRow(colIndex) = GetCellValue(doc, cell)
                colIndex += 1
            Next
            dataTable.Rows.Add(dataRow)
        Next
    End Using
    
    targetDgv.DataSource = dataTable
End Sub

' 辅助方法：获取单元格的实际值
Private Function GetCellValue(doc As SpreadsheetDocument, cell As Cell) As String
    Dim cellValue As String = cell.CellValue?.InnerText
    ' 处理共享字符串类型
    If cell.DataType IsNot Nothing AndAlso cell.DataType.Value = CellValues.SharedString Then
        Dim stringTable As SharedStringTablePart = doc.WorkbookPart.SharedStringTablePart
        cellValue = stringTable.SharedStringTable.Elements(Of SharedStringItem)().ElementAt(Integer.Parse(cellValue)).InnerText
    End If
    Return If(cellValue, "")
End Function

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