从Excel(具有一列由图片组成)导入数据到vb.net中的DataGridView [英] Import Data from Excel(having a column consist of pictures) to DataGridView in vb.net

问题描述

HI
我必须将数据从Excel导入vb.net中的DataGridView.但是excel文件应包含一列，其中应包含图片.
并且所有列都应与图片一起传递到datagridview.

HI
I have to Import Data from Excel to DataGridView in vb.net. But the excel file should consist of a column which should contain pictures in it.
And all the columns should be passed to the datagridview along with the pictures.

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
   Dim ds As DataSet = GetExcel("c:\1KEYViewimage.xls")
   DataGridView1.DataSource = ds.Tables(0)

   '' DataGridView1.DataBind()
End Sub
Public Function GetExcel(ByVal fileName As String) As DataSet
   Dim oXL As Application
   Dim oWB As Workbook
   Dim oSheet As Worksheet
   Dim oRng As Range
   Try
      ' creat a Application object
      oXL = New ApplicationClass()
      ' get WorkBook object
      oWB = oXL.Workbooks.Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value)

      ' get WorkSheet object
      oSheet = CType(oWB.Sheets(1), _
         Microsoft.Office.Interop.Excel.Worksheet)
      Dim dt As New System.Data.DataTable("dtExcel")
      Dim ds As New DataSet()
      ds.Tables.Add(dt)
      Dim dr As DataRow

      Dim sb As New StringBuilder()
      Dim jValue As Integer = oSheet.UsedRange.Cells.Columns.Count
      Dim iValue As Integer = oSheet.UsedRange.Cells.Rows.Count
      ' get data columns
      For j As Integer = 1 To jValue
         dt.Columns.Add("column" & j, _
            System.Type.GetType("System.String"))
      Next j

      ' get data in cell
      For i As Integer = 1 To iValue
         dr = ds.Tables("dtExcel").NewRow()
         For j As Integer = 1 To jValue
            oRng = CType(oSheet.Cells(i, j), _
            Microsoft.Office.Interop.Excel.Range)
            Dim strValue As String = oRng.Text.ToString()
            dr("column" & j) = strValue
         Next j
         ds.Tables("dtExcel").Rows.Add(dr)
      Next i
      Return ds
   Catch ex As Exception
      Return Nothing
   'Finally
      'Dispose()
   End Try
End Function

但是thexecel文件中的图片列变得空白.无法将图片列传递给datagridview.

因此，请帮助我如何将excel文件的图片列传递给datagridview.

But the picture column in thexecel file is getting blank.Unable to pass the picture column to datagridview.

so, help me how to pass the picture column of excel file to datagridview.

推荐答案

For j As Integer = 1 To jValue
   dt.Columns.Add("column" & j, _
      System.Type.GetType("System.String"))
Next j
' get data in cell
For i As Integer = 1 To iValue
   dr = ds.Tables("dtExcel").NewRow()
   For j As Integer = 1 To jValue
      oRng = CType(oSheet.Cells(i, j), _
      Microsoft.Office.Interop.Excel.Range)
      Dim strValue As String = oRng.Text.ToString()
      dr("column" & j) = strValue
   Next j
   ds.Tables("dtExcel").Rows.Add(dr)
Next i

这里有两点可能会有所帮助:
1)您无法将图像添加到字符串列.如果不确定数据类型，请仅使用名称尝试.
2)尝试使用oRng.value代替oRng.Text(即dr("column"& j)= oRng.Value)

Here are two points which may help:
1) You cannot get an image to a string column. If you are not sure about datatype, try it with name only.
2) Try using oRng.value instead of oRng.Text (i.e. dr("column" & j) = oRng.Value)

Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim MyConnection As System.Data.OleDb.OleDbConnection
        Dim DtSet As System.Data.DataSet
        Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
        MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\vb.net-informations.xls';Extended Properties=Excel 8.0;")
        MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1

"，MyConnection中选择*) MyCommand.TableMappings.Add(" ，" Net-informations.com") DtSet = 新建 System.Data.DataSet MyCommand.Fill(DtSet) DataGridView1.DataSource = DtSet.Tables( 0 ) MyConnection.Close() 结束 子 结束 类

", MyConnection) MyCommand.TableMappings.Add("Table", "Net-informations.com") DtSet = New System.Data.DataSet MyCommand.Fill(DtSet) DataGridView1.DataSource = DtSet.Tables(0) MyConnection.Close() End Sub End Class

这篇关于从Excel(具有一列由图片组成)导入数据到vb.net中的DataGridView的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！