Help i need to import excel to my listview using adodb or oledb

[ImZick]

Help i need to import excel to my listview using adodb or oledb

but i dont know how? in vb6 i use adodb and the code is like here

Dim lngI as Long
Dim lvItem as listItem
    *'adodb connection goes here...*
    *'then this code will import all excel data in listview* 
    If Not (rs.EOF And rs.BOF) Then
        For lngI = 0 To rs.Fields.Count - 1
        *'this will set the first row of excel data as columnheader of the listview*
        Listview1.ColumnHeaders.Add , , rs.Fields(lngI).Name
        Next lngI
        Do Until rs.EOF
            If Not IsNull(rs.Fields(0).Value) Then
                Set lvItem = Listview1.ListItems.Add(, , rs.Fields(0).Value)
                For lngI = 1 To rs.Fields.Count - 1
                    lvItem.ListSubItems.Add , , rs.Fields(lngI).Value
                Next lngI
            End If
        rs.MoveNext
        Loop
    End If

Can some please help me? thanks in advance..

[tinstaafl]

If you use the Microsoft Office Spreadsheet control you can open the spreadsheet right in your app.

[ImZick]

I finally found a way to load all data to my listview but the problem is that it only loads the items but not the columnheader

 Dim con As New OleDbConnection
Dim Exfilepath As String = "Path.xls"
Dim da As New OleDbDataAdapter("Select * from [Sheet1$]", con)
Dim dt As New DataTable
Dim ds As New DataSet

    con.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0; Data Source='" & Exfilepath & "';Extended Properties=Excel 8.0;"
    con.Open()
    ds.Tables.Add(dt)
    da.Fill(dt)
    For Each myRow In dt.Rows
        ListView1.Items.Add(myRow.Item(0))
        ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(myRow.Item(1))
    Next

    con.Close()

Please help me...

[ImZick]

hi thanks for the reply @tinstaafl
but i need to use OleDB or adodb..

:( please help :(

[Reverend Jim]

Try adding "HDR=No" to the extended properties.

[ImZick]

Hi @reverend Jim thanks for the reply but when i put the "HDR=No" to the extended properties it say that could not find installable ISAM.

    con.ConnectionString = ("provider=Microsoft.Jet.OLEDB.4.0; Data Source='" & Exfilepath & "';Extended Properties=Excel 8.0;HDR=No;")
    **con.Open() 'this highlight the codes**

I just want to add the first row name to the columns of my listview

[ImZick]

I finally found the solutions in my problem.. just want to share to the following readers
here is my code

Dim con As New OleDbConnection
Dim Exfilepath As String = "Path for excel"
Dim dt As New DataTable
Dim dt2 As New DataTable

    Dim ds As New DataSet
    con.ConnectionString = ("provider=Microsoft.Jet.OLEDB.4.0; Data Source='" & Exfilepath & "';Extended Properties=Excel 8.0;") 'provider for excel 2003
    con.Open()
    dt.Clear()
    ds.Tables.Add(dt)
    Dim da As New OleDbDataAdapter("Select * from [Sheet1$]", con)
    da.Fill(dt)
    Dim dc As DataColumn
    'this will input excel column name to listview column name
    For Each dc In ds.Tables(0).Columns
        Dim ch As New ColumnHeader
        ch.Text = dc.ColumnName 
        ch.Width = 110 'set the columns width
        Me.ListView1.Columns.Add(ch)
    Next
    'this will populate all data in listview
    For Each myRow In dt.Rows
        ListView1.Items.Add(myRow.Item(0))
        ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(myRow.Item(1))
    Next
    con.Close()