Imports System.Data
Imports System.Data.Common
Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Data.OleDb
Imports System.IO
Imports System.Data.SqlClient
Imports Microsoft.Office.Core
 
Public Class Form2
  Private dt As New DataTable
  Private Sub btnGridLaden_Click(ByVal sender As System.Object, ByVal e As _
    System.EventArgs) _
                                        Handles btnGridLaden.Click
 
    ' Excel-Tabelle anzeigen
    Dim oDs As System.Data.DataSet
    Dim oConn As System.Data.OleDb.OleDbConnection
    Dim oAdapter As System.Data.OleDb.OleDbDataAdapter
 
    ' Pfad und Tabellennamen bitte anpassen!
    Dim sFile As String = "F:\Zeugnis\Zeugnisse\_Formular" & _
      "CJD-Ergebnisse_X-Test-Prf1.xls"
    Dim sTable As String = "Gesamtübersicht"
 
    ' 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;"
 
    ' Connection herstellen
    oConn = New System.Data.OleDb.OleDbConnection(sConn)
 
    ' Excel-Tabelle referenzieren
    ' Hinweis: Tabellenname muss mit abschließendem $-Zeichen und
    ' in eckigen Klammern stehen
    oAdapter = New System.Data.OleDb.OleDbDataAdapter( _
      "SELECT * FROM [" & sTable & "$]", oConn)
 
    ' DataSet erstellen und mit Daten aus dem DataAdapter füllen
    oDs = New System.Data.DataSet
    Try
      oAdapter.Fill(oDs)
 
      ' DataGrid füllen und Daten anzeigen
      With dgvExcel
        .DataSource = oDs.Tables(0)
        .ReadOnly = True
        .Refresh()
      End With
    Catch ex As Exception
      MsgBox("Fehler beim Anzeigen der Excel-Daten!")
    Finally
      ' DB-Verbindung schließen
      oConn.Close()
    End Try
 
    '_____________________Adresse 
    ' übertragen_______________________________________________
 
    txtNr.Text = Me.dgvExcel.Item(rowIndex:=2, columnIndex:=0).Value.ToString
    txtVorname.Text = Me.dgvExcel.Item(rowIndex:=2, _
      columnIndex:=8).Value.ToString
    txtName.Text = Me.dgvExcel.Item(rowIndex:=2, columnIndex:=1).Value.ToString
 
    '________________________1. 
    ' Zwischenprüfung_____________________________________________
 
    'txtBWL.Text = Me.dgvExcel.FirstDisplayedCell = 
    ' Me.dgvExcel.CurrentCell.Value.ToString
 
    txtBWL.Text = Me.dgvExcel.Item(rowIndex:=2, columnIndex:=9).Value.ToString
    txtDB.Text = Me.dgvExcel.Item(rowIndex:=2, columnIndex:=12).Value.ToString
    txtIT.Text = Me.dgvExcel.Item(rowIndex:=2, columnIndex:=15).Value.ToString
    txtVB.Text = Me.dgvExcel.Item(rowIndex:=2, columnIndex:=18).Value.ToString
    txtElektro.Text = Me.dgvExcel.Item(rowIndex:=2, _
      columnIndex:=21).Value.ToString
    txtWISO.Text = Me.dgvExcel.Item(rowIndex:=2, columnIndex:=24).Value.ToString
    txtNZ.Text = Me.dgvExcel.Item(rowIndex:=2, columnIndex:=27).Value.ToString
    txtProg.Text = Me.dgvExcel.Item(rowIndex:=2, columnIndex:=30).Value.ToString
    txtEng.Text = Me.dgvExcel.Item(rowIndex:=2, columnIndex:=33).Value.ToString
 
    Dim Erg(7) As Integer
    Dim x As Integer
    Dim Zahl1 As Integer
 
    Erg(0) = Val(txtBWL.Text)
    Erg(1) = Val(txtDB.Text)
    Erg(2) = Val(txtIT.Text)
    Erg(3) = Val(txtVB.Text)
    Erg(4) = Val(txtElektro.Text)
    Erg(5) = Val(txtWISO.Text)
    Erg(6) = Val(txtNZ.Text)
    Erg(7) = Val(txtProg.Text)
    '___________________________Mittelwert 
    ' berechnen_________________________________________
 
    For x = 0 To 7
      If Erg(x) > 0 Then
 
        Zahl1 = Zahl1 + 1
 
      End If
    Next x
 
    ' txtZahl.Text = Zahl1
 
    txtSchnitt.Text = Math.Round((Val(txtBWL.Text) + Val(txtDB.Text) + Val( _
      txtIT.Text) + _
                                 Val(txtVB.Text) + Val(txtElektro.Text) + Val( _
                                 txtWISO.Text) + _
                                 Val(txtNZ.Text) + Val(txtProg.Text)) / Zahl1, _
                                 2)
  End Sub
 
 
  Private Sub btnTest_Click(ByVal sender As System.Object, ByVal e As _
    System.EventArgs) _
                            Handles btnTest.Click
 
      If dgvExcel.CurrentRow.Index < dgvExcel.RowCount Then
 
      Me.dgvExcel.Rows(dgvExcel.RowCount - 1).Selected = True
 
    End If
    ' MsgBox(dgvExcel.RowCount)
 
  End Sub
End Class