cara membaca sheet excel dengan vb.net
Imports System.Data.OleDb
Public Class ImportSoalUjian
'membuat definisi variabel untuk koneksi yang baru ke data excel
Dim connexcel As OleDbConnection
Dim daexcel As OleDbDataAdapter
Dim dsexcel As DataSet
Dim cmdexcel As OleDbCommand
Dim drexcel As OleDbDataReader
Sub excelkoneksi()
'string koneksi ke data excel
connexcel = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & Label1.Text & "';Extended Properties=""Excel 12.0 Xml;HDR=YES"";")
connexcel.Open()
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
OpenFileDialog1.Filter = "(*.xls)|*.xls|(*.xlsx)|*.xlsx|All files (*.*)|*.*"
OpenFileDialog1.ShowDialog()
Label1.Text = OpenFileDialog1.FileName
Call excelkoneksi()
Dim dtSheets As DataTable = connexcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
Dim listSheet As New List(Of String)
Dim drSheet As DataRow
'menambahan nama-nama sheet excel agar dianggap sebagai tabel
For Each drSheet In dtSheets.Rows
listSheet.Add(drSheet("TABLE_NAME"))
Next
'menampilkan nama-nama worksheet dalam file excel
For Each sheet As String In listSheet
ListBox1.Items.Add(sheet)
Next
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Label1.Text = ""
ListBox1.Items.Clear()
DGV.Columns.Clear()
End Sub
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
Me.Close()
End Sub
Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
Call excelkoneksi()
'menampilkan isi worksheet excel yang dipilih dalam listbox1 ke dalam grid
daexcel = New OleDbDataAdapter("select * from [" & ListBox1.Text & "]", connexcel)
dsexcel = New DataSet
daexcel.Fill(dsexcel)
DGV.DataSource = dsexcel.Tables(0)
DGV.ReadOnly = True
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
If ListBox1.Text = "" Then
MsgBox("pilih soal ujian dalam listbox")
Exit Sub
End If
For baris As Integer = 0 To DGV.RowCount - 2
Call Koneksi()
CMD = New OleDbCommand("select idkuliah from Tblsoal where idkuliah='" & DGV.Rows(baris).Cells(0).Value & "'", Conn)
DR = CMD.ExecuteReader
DR.Read()
If DR.HasRows Then
'data yang sudah ada dihapus terlebih dahulu
Dim hapus As String = "delete * from Tblsoal where idkuliah='" & DGV.Rows(baris).Cells(0).Value & "'"
CMD = New OleDbCommand(hapus, Conn)
CMD.ExecuteNonQuery()
End If
Next
'data dari excel diimport ke dalam tabel soal ujian
For baris As Integer = 0 To DGV.RowCount - 2
Dim simpan As String = "insert into Tblsoal values('" & DGV.Rows(baris).Cells(0).Value & "','" & DGV.Rows(baris).Cells(1).Value & "','" & DGV.Rows(baris).Cells(2).Value & "','" & DGV.Rows(baris).Cells(3).Value & "','" & DGV.Rows(baris).Cells(4).Value & "','" & DGV.Rows(baris).Cells(5).Value & "','" & DGV.Rows(baris).Cells(6).Value & "','" & DGV.Rows(baris).Cells(7).Value & "')"
CMD = New OleDbCommand(simpan, Conn)
CMD.ExecuteNonQuery()
Next
DGV.Columns.Clear()
End Sub
End Class