DataGridView中拖放带有图片的Excel，实现数据批量导入

llwzjf

已于 2025-05-21 09:49:44 修改

阅读量235

点赞数 3

分类专栏： VB.NET 文章标签： excel DataGridView拖放

于 2025-05-21 09:15:18 首次发布

本文链接：https://blog.csdn.net/llwzjf/article/details/148103598

版权

VB.NET 专栏收录该内容

11 篇文章

订阅专栏

1、带有DataGridView的窗体，界面如下

2、编写DataGridView支持拖放的代码

    Private Sub DataGridView1_DragEnter(ByVal sender As Object, ByVal e As DragEventArgs) Handles DataGridView1.DragEnter
        If e.Data.GetDataPresent(DataFormats.FileDrop) Then
            Dim files As String() = CType(e.Data.GetData(DataFormats.FileDrop), String())
            If files IsNot Nothing AndAlso files.Any(Function(f) _
                String.Equals(Path.GetExtension(f), ".xlsx", StringComparison.OrdinalIgnoreCase)) Then
                e.Effect = DragDropEffects.Copy
            End If
        End If
    End Sub

    Private Sub DataGridView1_DragDrop(ByVal sender As Object, ByVal e As DragEventArgs) Handles DataGridView1.DragDrop
        Try
            Dim files As String() = CType(e.Data.GetData(DataFormats.FileDrop), String())
            If files IsNot Nothing AndAlso files.Length > 0 Then
                Dim excelPath = files(0)
                If String.Equals(Path.GetExtension(excelPath), ".xlsx", StringComparison.OrdinalIgnoreCase) Then
                    ReadExcelToDataGridView(excelPath)
                Else
                    MessageBox.Show("仅支持.xlsx格式的Excel文件")
                End If
            End If
        Catch ex As Exception
            MessageBox.Show("处理失败：{0}" & ex.Message)
        End Try
    End Sub

3、使用OLEDB读取Excel文件

' 使用OLEDB读取Excel文件
    Private Sub ReadExcelToDataGridView(ByVal excelPath As String)
        DataGridView1.Rows.Clear()
        Dim connectionString As String = String.Format(
            "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES""",
            excelPath)

        Using connection As New OleDbConnection(connectionString)
            Try
                connection.Open()
                Dim sheetName = GetExcelSheetName(connection)
                If String.IsNullOrEmpty(sheetName) Then
                    MessageBox.Show("无法获取Excel工作表名称")
                    Return
                End If

                '这里指定要读取excel的工作表标签名为sheetName
                Dim query = String.Format("SELECT * FROM [{0}]", sheetName)
                Dim adapter As New OleDbDataAdapter(query, connection)
                Dim dataTable As New DataTable()
                adapter.Fill(dataTable)

                ' 填充DataGridView（跳过标题行）
                For i As Integer = 0 To dataTable.Rows.Count - 1
                    Dim row = dataTable.Rows(i)
                    Dim id = If(IsDBNull(row(0)), "", row(0).ToString())
                    Dim name = If(IsDBNull(row(1)), "", row(1).ToString())
                    Dim imgPath = If(IsDBNull(row(2)), "", row(2).ToString())

                    Dim img As Image = Nothing
                    If Not String.IsNullOrEmpty(imgPath) AndAlso File.Exists(imgPath) Then
                        img = Image.FromFile(imgPath)
                    Else
                        img = My.Resources.NoImage  ' 需要在项目中添加默认图片资源
                    End If

                    DataGridView1.Rows.Add(id, name, img)
                Next
            Catch ex As Exception
                MessageBox.Show("读取Excel失败：{ex.Message}")
            End Try
        End Using
    End Sub

    ' 获取Excel第一个工作表名称
    Private Function GetExcelSheetName(ByVal connection As OleDbConnection) As String
        Try
            Dim dataTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
            If dataTable IsNot Nothing AndAlso dataTable.Rows.Count > 0 Then
                Return dataTable.Rows(0)("TABLE_NAME").ToString()
            End If
            Return String.Empty
        Catch
            Return String.Empty
        End Try
    End Function

4、创建一个Excel文件，这里要求为Excel第一个工作表

5、最终效果

6、完整代码如下：

Imports System.IO
Imports System.Windows.Forms
Imports System.Data.OleDb  ' 使用OLEDB访问Excel

Public Class Form3
    Private Sub MainForm_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
        ' 初始化DataGridView
        DataGridView1.AllowDrop = True
        DataGridView1.Columns.Add("ID", "编号")
        DataGridView1.Columns.Add("Name", "名称")

        ' 添加图片列
        Dim imgCol As New DataGridViewImageColumn
        imgCol.HeaderText = "图片"
        imgCol.ImageLayout = DataGridViewImageCellLayout.Zoom
        DataGridView1.Columns.Add(imgCol)
    End Sub

    Private Sub DataGridView1_DragEnter(ByVal sender As Object, ByVal e As DragEventArgs) Handles DataGridView1.DragEnter
        If e.Data.GetDataPresent(DataFormats.FileDrop) Then
            Dim files As String() = CType(e.Data.GetData(DataFormats.FileDrop), String())
            If files IsNot Nothing AndAlso files.Any(Function(f) _
                String.Equals(Path.GetExtension(f), ".xlsx", StringComparison.OrdinalIgnoreCase)) Then
                e.Effect = DragDropEffects.Copy
            End If
        End If
    End Sub

    Private Sub DataGridView1_DragDrop(ByVal sender As Object, ByVal e As DragEventArgs) Handles DataGridView1.DragDrop
        Try
            Dim files As String() = CType(e.Data.GetData(DataFormats.FileDrop), String())
            If files IsNot Nothing AndAlso files.Length > 0 Then
                Dim excelPath = files(0)
                If String.Equals(Path.GetExtension(excelPath), ".xlsx", StringComparison.OrdinalIgnoreCase) Then
                    ReadExcelToDataGridView(excelPath)
                Else
                    MessageBox.Show("仅支持.xlsx格式的Excel文件")
                End If
            End If
        Catch ex As Exception
            MessageBox.Show("处理失败：{0}" & ex.Message)
        End Try
    End Sub

    ' 使用OLEDB读取Excel文件
    Private Sub ReadExcelToDataGridView(ByVal excelPath As String)
        DataGridView1.Rows.Clear()
        Dim connectionString As String = String.Format(
            "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES""",
            excelPath)

        Using connection As New OleDbConnection(connectionString)
            Try
                connection.Open()
                Dim sheetName = GetExcelSheetName(connection)
                If String.IsNullOrEmpty(sheetName) Then
                    MessageBox.Show("无法获取Excel工作表名称")
                    Return
                End If

                '这里指定要读取excel的工作表标签名为sheetName
                Dim query = String.Format("SELECT * FROM [{0}]", sheetName)
                Dim adapter As New OleDbDataAdapter(query, connection)
                Dim dataTable As New DataTable()
                adapter.Fill(dataTable)

                ' 填充DataGridView（跳过标题行）
                For i As Integer = 0 To dataTable.Rows.Count - 1
                    Dim row = dataTable.Rows(i)
                    Dim id = If(IsDBNull(row(0)), "", row(0).ToString())
                    Dim name = If(IsDBNull(row(1)), "", row(1).ToString())
                    Dim imgPath = If(IsDBNull(row(2)), "", row(2).ToString())

                    Dim img As Image = Nothing
                    If Not String.IsNullOrEmpty(imgPath) AndAlso File.Exists(imgPath) Then
                        img = Image.FromFile(imgPath)
                    Else
                        img = My.Resources.NoImage  ' 需要在项目中添加默认图片资源
                    End If

                    DataGridView1.Rows.Add(id, name, img)
                Next
            Catch ex As Exception
                MessageBox.Show("读取Excel失败：{ex.Message}")
            End Try
        End Using
    End Sub

    ' 获取Excel第一个工作表名称
    Private Function GetExcelSheetName(ByVal connection As OleDbConnection) As String
        Try
            Dim dataTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
            If dataTable IsNot Nothing AndAlso dataTable.Rows.Count > 0 Then
                Return dataTable.Rows(0)("TABLE_NAME").ToString()
            End If
            Return String.Empty
        Catch
            Return String.Empty
        End Try
    End Function
End Class

最后说明，Excel中如果使用图片的绝对路径，Excel放在任何位置都行。