Hente data fra Excel

Hvad med at bruge en DataReader fremfor DataSet ?

Et gammelt eksempel fra lageret:

Imports System
Imports System.Data.OleDb

Class MainClass
    Public Shared Sub Main(ByVal args As String())
        Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Book3.xls;Extended Properties=""Excel 8.0;HDR=No;IMEX=1""")
        con.Open
        Dim cmd As OleDbCommand = New OleDbCommand("Select f2,f3 FROM [sheet1$] WHERE f1 >= 2", con)
        Dim rdr As OleDbDataReader = cmd.ExecuteReader
        While rdr.Read
            Dim k2 As Double = CType(rdr(0), Double)
            Dim k3 As Double = CType(rdr(1), Double)
            Console.WriteLine(k2 & " " & k3)
        End While
        con.Close
    End Sub
End Class

Det lyder meget godt MEN jeg får fortsat fejl ved: "[Sheet1$]"...er ikke et gyldigt navn!
Og hvorledes kan jeg hente data fra ARKET: Hæmodynamik: L18, L19, L20, L21, L22, L23, L24, L25, L26 og L27 og placere hver værdi i en forms textboxe?

Jeg har prøvet dette men det giver fejl ved ExecuteReader: "Der er ikke angivet nogen værdi for én eller flere krævede parametre"

Try
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\prøve\11111.xls;Extended Properties=""Excel 8.0;HDR=No;IMEX=1""")
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("Select L20, L21 FROM [Hæmodynamik$]", con)
            Dim rdr As OleDbDataReader = cmd.ExecuteReader
            While rdr.Read
                Dim k2 As Double = CType(rdr(0), Double)
                Dim k3 As Double = CType(rdr(1), Double)
                Console.WriteLine(k2 & " " & k3)
            End While
            con.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

Jeg gaetter paa at du mener:

SELECT L FROM [Hæmodynamik$]

og at du saa vil bruge raekke 20 og 21.

Enten skal du have en WHERE betingelse paa row nummer eller saa skal du selv taelle og
gemme i textbox ved nummer 20 og 21.

Det her fungerer MEN jeg skal kun hente data fra et bestemt område (L18:L27) i arket. Hvordan gør jeg det?

        Dim k2, k3 As Integer
        Try
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\prøve\test.xls;Extended Properties=""Excel 8.0;HDR=No;IMEX=1""")
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("Select * FROM [Ark1$]", con)
            Dim rdr As OleDbDataReader = cmd.ExecuteReader
            While rdr.Read
                If Not rdr.IsDBNull(0) Then
                    TextBox2901.Text = CType(rdr(0), Integer)
                End If
                If Not rdr.IsDBNull(1) Then
                    TextBox2902.Text = CType(rdr(1), Integer)
                End If
            End While
            con.Close()
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

Den her laver følgende fejl: Indekset lå uden for arrayets grænser:

        Try
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\prøve\11111.xls;Extended Properties=""Excel 8.0;HDR=No;IMEX=1""")
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("Select * FROM [Hæmodynamik$L18:L20]", con)
            Dim rdr As OleDbDataReader = cmd.ExecuteReader
            While rdr.Read
                If Not rdr.IsDBNull(0) Then
                    TextBox2901.Text = CType(rdr(0), Decimal)
                End If
                If Not rdr.IsDBNull(1) Then
                    TextBox2902.Text = CType(rdr(1), Integer)
                End If
            End While
            con.Close()
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

...men textbox2901 udfyldes med data fra celle L18

Det her udfører jobbet men elegant er det bestemt ikke...Det MÅ kunne gøres bedre:

        Try
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\prøve\11111.xls;Extended Properties=""Excel 8.0;HDR=No;IMEX=1""")
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("Select * FROM [Hæmodynamik$L18:L18]", con)
            Dim rdr As OleDbDataReader = cmd.ExecuteReader
            While rdr.Read
                If Not rdr.IsDBNull(0) Then
                    TextBox31.Text = CType(rdr(0), Decimal)
                End If
            End While

            Dim cmd1 As OleDbCommand = New OleDbCommand("Select * FROM [Hæmodynamik$L19:L19]", con)
            Dim rdr1 As OleDbDataReader = cmd1.ExecuteReader
            While rdr1.Read
                If Not rdr1.IsDBNull(0) Then
                    TextBox2154.Text = CType(rdr1(0), Decimal)
                End If
            End While

            Dim Cmd2 As OleDbCommand = New OleDbCommand("Select * FROM [Hæmodynamik$L20:L20]", con)
            Dim rdr2 As OleDbDataReader = Cmd2.ExecuteReader
            While rdr2.Read
                If Not rdr2.IsDBNull(0) Then
                    TextBox2155.Text = CType(rdr2(0), Decimal)
                End If
            End While

            Dim cmd3 As OleDbCommand = New OleDbCommand("Select * FROM [Hæmodynamik$L21:L21]", con)
            Dim rdr3 As OleDbDataReader = cmd3.ExecuteReader
            While rdr3.Read
                If Not rdr3.IsDBNull(0) Then
                    TextBox2156.Text = CType(rdr3(0), Decimal)
                End If
            End While

            Dim cmd4 As OleDbCommand = New OleDbCommand("Select * FROM [Hæmodynamik$L22:L22]", con)
            Dim rdr4 As OleDbDataReader = cmd4.ExecuteReader
            While rdr4.Read
                If Not rdr4.IsDBNull(0) Then
                    TextBox2162.Text = CType(rdr4(0), Decimal)
                End If
            End While

            Dim cmd5 As OleDbCommand = New OleDbCommand("Select * FROM [Hæmodynamik$L23:L23]", con)
            Dim rdr5 As OleDbDataReader = cmd5.ExecuteReader
            While rdr5.Read
                If Not rdr5.IsDBNull(0) Then
                    TextBox2161.Text = CType(rdr5(0), Decimal)
                End If
            End While

            Dim cmd6 As OleDbCommand = New OleDbCommand("Select * FROM [Hæmodynamik$L24:L24]", con)
            Dim rdr6 As OleDbDataReader = cmd6.ExecuteReader
            While rdr6.Read
                If Not rdr6.IsDBNull(0) Then
                    TextBox2158.Text = CType(rdr6(0), Decimal)
                End If
            End While

            Dim cmd7 As OleDbCommand = New OleDbCommand("Select * FROM [Hæmodynamik$L25:L25]", con)
            Dim rdr7 As OleDbDataReader = cmd7.ExecuteReader
            While rdr7.Read
                If Not rdr7.IsDBNull(0) Then
                    TextBox87.Text = CType(rdr7(0), Decimal)
                End If
            End While

            Dim cmd8 As OleDbCommand = New OleDbCommand("Select * FROM [Hæmodynamik$L26:L26]", con)
            Dim rdr8 As OleDbDataReader = cmd8.ExecuteReader
            While rdr8.Read
                If Not rdr8.IsDBNull(0) Then
                    TextBox2163.Text = CType(rdr8(0), Decimal)
                End If
            End While

            Dim cmd9 As OleDbCommand = New OleDbCommand("Select * FROM [Hæmodynamik$L27:L27]", con)
            Dim rdr9 As OleDbDataReader = cmd9.ExecuteReader
            While rdr9.Read
                If Not rdr9.IsDBNull(0) Then
                    TextBox2164.Text = CType(rdr9(0), Decimal)
                End If
            End While

            con.Close()
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

Hmm lukker