Public Class frmFussball
 
    'Labels 
    Dim lblM1 As New Label With _
        {.Parent = Me, .Width = 200, .Text = "Mannschaft1:"}
    Dim lblM2 As New Label With _
        {.Parent = Me, .Left = 205, .Width = 200, .Text = "Mannschaft2:"}
    Dim lblTore As New Label With _
        {.Parent = Me, .Left = 410, .Width = 50, .Text = "Tore:"}
    Dim lblDatum As New Label With _
        {.Parent = Me, .Left = 470, .Width = 70, .Text = "Datum ab:"}
 
    'Textboxen 
    Dim txtM1 As New TextBox With _
        {.Parent = Me, .Width = 200, .Top = 30}
    Dim txtM2 As New TextBox With _
        {.Parent = Me, .Left = 205, .Width = 200, .Top = 30}
 
    Dim txtTore As New TextBox With _
        {.Parent = Me, .Left = 410, .Width = 50, .Top = 30, .Text = "0"}
 
    Dim dtpDatum As New DateTimePicker With _
                {.Parent = Me, .Left = 470, .Width = 150, .Top = 30, _
                 .Format = DateTimePickerFormat.Short, .ShowUpDown = True}
 
    Dim WithEvents btnSelect As New Button With _
        {.Parent = Me, .Top = 80, .Width = 200, .Text = "Tor-Statistik"}
 
    Dim lblresult As New Label With _
        {.Parent = Me, .Width = 600, .Top = 120, .Height = 200}
 
    Dim dgv As New DataGridView With _
        {.Parent = Me, .Top = 330, .Width = 600, .Height = 220, _
         .AllowUserToAddRows = False}
 
    Dim oDs As New DataSet
    Dim bs As New BindingSource
 
    Private Sub frmFussball_Load(sender As System.Object, _
                                 e As System.EventArgs) Handles MyBase.Load
 
        Me.Size = New Size(640, 600)
 
        ' Pfad und Tabellennamen bitte anpassen!
        Dim sFile As String = "C:\daten\ab.xls"
        Dim sTable As String = "Tabelle1"
 
        ' Connection-String für die DB.Verbindung zur Excel-Datei
        Dim sConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & sFile & ";" & _
          "Extended Properties=Excel 8.0;"
 
        Try
            Using oConn As New OleDb.OleDbConnection(sConn), _
                oAdapter As New OleDb.OleDbDataAdapter _
                ("SELECT * FROM [" & sTable & "$]", oConn)
 
                ' DataSet erstellen und mit Daten aus dem DataAdapter füllen
                oDs = New System.Data.DataSet
 
                oAdapter.Fill(oDs)
                bs.DataSource = oDs.Tables(0)
                dgv.DataSource = bs
 
                dtpDatum.Value = _
                    CDate(Aggregate r As Object In oDs.Tables(0).Rows
                     Into Min(CType(r, DataRow)("Datum")))
 
            End Using
        Catch ex As Exception
            MsgBox("Fehler beim Lesen der Tabelle: " & _
                   ex.Message, MsgBoxStyle.Critical)
        End Try
 
    End Sub
 
 
    Private Sub btnSelect_Click(sender As Object, e As System.EventArgs) _
      Handles btnSelect.Click
 
        lblresult.Text = ""
 
        'Filter: Mannschaft
        Dim filterM1 As String = _
          "( TeamA like '" & Trim(txtM1.Text) & _
          "' OR TeamB like '" & Trim(txtM1.Text) & "' ) "
 
        Dim filterM2 As String = _
          "( TeamA like '" & Trim(txtM2.Text) & _
          "' OR TeamB like '" & Trim(txtM2.Text) & "' ) "
 
        'Filter: Datum 
        Dim datstr As String = _
        "#" & dtpDatum.Value.ToString("d", _
        Globalization.CultureInfo.InvariantCulture) & "#"
        Dim FilterDatum As String = "( Datum >= " & datstr & ") "
 
        'Gesamtfilter 
        bs.Filter = _
            "(" & filterM1 & "OR" & filterM2 & ") AND " & FilterDatum
 
        If bs.Count = 0 Then Exit Sub
 
        Dim torekrit As UShort
        If Not UShort.TryParse(txtTore.Text, torekrit) Then Exit Sub
 
        'Tor-Kriterium in gefilterten Datensätzen
        Dim anz As Integer = 0, tgame As Integer
        For i As Integer = 0 To bs.Count - 1
            Dim row As DataRow = CType(bs(i), DataRowView).Row
            tgame = CInt(row.Item("ToreTeamA")) + CInt(row("ToreTeamB"))
            If tgame >= torekrit Then anz += 1
        Next i
 
        Dim prozent As Single = CSng(Math.Round(anz / bs.Count * 100, 2))
 
        lblresult.Text = "Torstatistik: Alle Spiele von " & vbCrLf & _
            txtM1.Text & " und " & txtM2.Text & " : " & vbCrLf & _
            "ab Datum: " & CStr(dtpDatum.Value.Date) & vbCrLf & _
            "Gesamtzahl der Spiele: " & CStr(bs.Count) & vbCrLf & _
            "Spiele mit mindestens " & CStr(txtTore.Text) & _
            " Toren: " & CStr(prozent) & "%"
    End Sub
End Class