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

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

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

    Private Sub DataGridView1_DragEnter(ByVal sender As Object, ByVal e As DragEventArgs) Handles DataGridView1.DragEnterIf e.Data.GetDataPresent(DataFormats.FileDrop) ThenDim 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)) Thene.Effect = DragDropEffects.CopyEnd IfEnd IfEnd SubPrivate Sub DataGridView1_DragDrop(ByVal sender As Object, ByVal e As DragEventArgs) Handles DataGridView1.DragDropTryDim files As String() = CType(e.Data.GetData(DataFormats.FileDrop), String())If files IsNot Nothing AndAlso files.Length > 0 ThenDim excelPath = files(0)If String.Equals(Path.GetExtension(excelPath), ".xlsx", StringComparison.OrdinalIgnoreCase) ThenReadExcelToDataGridView(excelPath)ElseMessageBox.Show("仅支持.xlsx格式的Excel文件")End IfEnd IfCatch ex As ExceptionMessageBox.Show("处理失败：{0}" & ex.Message)End TryEnd 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)Tryconnection.Open()Dim sheetName = GetExcelSheetName(connection)If String.IsNullOrEmpty(sheetName) ThenMessageBox.Show("无法获取Excel工作表名称")ReturnEnd If'这里指定要读取excel的工作表标签名为sheetNameDim 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 - 1Dim 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 = NothingIf Not String.IsNullOrEmpty(imgPath) AndAlso File.Exists(imgPath) Thenimg = Image.FromFile(imgPath)Elseimg = My.Resources.NoImage  ' 需要在项目中添加默认图片资源End IfDataGridView1.Rows.Add(id, name, img)NextCatch ex As ExceptionMessageBox.Show("读取Excel失败：{ex.Message}")End TryEnd UsingEnd Sub' 获取Excel第一个工作表名称Private Function GetExcelSheetName(ByVal connection As OleDbConnection) As StringTryDim dataTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)If dataTable IsNot Nothing AndAlso dataTable.Rows.Count > 0 ThenReturn dataTable.Rows(0)("TABLE_NAME").ToString()End IfReturn String.EmptyCatchReturn String.EmptyEnd TryEnd Function

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

5、最终效果

6、完整代码如下：

Imports System.IO
Imports System.Windows.Forms
Imports System.Data.OleDb  ' 使用OLEDB访问ExcelPublic Class Form3Private Sub MainForm_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load' 初始化DataGridViewDataGridView1.AllowDrop = TrueDataGridView1.Columns.Add("ID", "编号")DataGridView1.Columns.Add("Name", "名称")' 添加图片列Dim imgCol As New DataGridViewImageColumnimgCol.HeaderText = "图片"imgCol.ImageLayout = DataGridViewImageCellLayout.ZoomDataGridView1.Columns.Add(imgCol)End SubPrivate Sub DataGridView1_DragEnter(ByVal sender As Object, ByVal e As DragEventArgs) Handles DataGridView1.DragEnterIf e.Data.GetDataPresent(DataFormats.FileDrop) ThenDim 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)) Thene.Effect = DragDropEffects.CopyEnd IfEnd IfEnd SubPrivate Sub DataGridView1_DragDrop(ByVal sender As Object, ByVal e As DragEventArgs) Handles DataGridView1.DragDropTryDim files As String() = CType(e.Data.GetData(DataFormats.FileDrop), String())If files IsNot Nothing AndAlso files.Length > 0 ThenDim excelPath = files(0)If String.Equals(Path.GetExtension(excelPath), ".xlsx", StringComparison.OrdinalIgnoreCase) ThenReadExcelToDataGridView(excelPath)ElseMessageBox.Show("仅支持.xlsx格式的Excel文件")End IfEnd IfCatch ex As ExceptionMessageBox.Show("处理失败：{0}" & ex.Message)End TryEnd 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)Tryconnection.Open()Dim sheetName = GetExcelSheetName(connection)If String.IsNullOrEmpty(sheetName) ThenMessageBox.Show("无法获取Excel工作表名称")ReturnEnd If'这里指定要读取excel的工作表标签名为sheetNameDim 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 - 1Dim 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 = NothingIf Not String.IsNullOrEmpty(imgPath) AndAlso File.Exists(imgPath) Thenimg = Image.FromFile(imgPath)Elseimg = My.Resources.NoImage  ' 需要在项目中添加默认图片资源End IfDataGridView1.Rows.Add(id, name, img)NextCatch ex As ExceptionMessageBox.Show("读取Excel失败：{ex.Message}")End TryEnd UsingEnd Sub' 获取Excel第一个工作表名称Private Function GetExcelSheetName(ByVal connection As OleDbConnection) As StringTryDim dataTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)If dataTable IsNot Nothing AndAlso dataTable.Rows.Count > 0 ThenReturn dataTable.Rows(0)("TABLE_NAME").ToString()End IfReturn String.EmptyCatchReturn String.EmptyEnd TryEnd Function
End Class

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