Import Data from Excel(having a column consist of pictures) to DataGridView in vb.net

Question

0.00/5 (No votes)

See more:

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.

VB

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

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.

Posted 19-Jan-11 22:53pm

neha_prasad

Updated 20-Jan-11 9:16am

fjdiewornncalwe

v2

Add a Solution

Comments

Sandeep Mewara 20-Jan-11 5:36am

Ok. So what's the question? Issue? What have you tried?

fjdiewornncalwe 20-Jan-11 15:16pm

Moved OP's question from answer to question.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Prerak Patel · Answer 1 · 2011-01-20T23:49:00

VB

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

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)

Rupa1 · Answer 2 · 2011-01-19T23:56:00

VB

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("Table", "Net-informations.com")
        DtSet = New System.Data.DataSet
        MyCommand.Fill(DtSet)
        DataGridView1.DataSource = DtSet.Tables(0)
        MyConnection.Close()
    End Sub
End Class

Import Data from Excel(having a column consist of pictures) to DataGridView in vb.net

2 solutions

Solution 4

Solution 3

Add your solution here

Preview 0