download gratis video tutorial vb 6 dan vb.net plus contoh aplikasi

export import dari excel ke mysql

 

Imports System.Data.OleDb
Imports System.Data.Odbc

 

Public Class Import_excel_to_mysql

 

Dim connexcel As OleDbConnection
Dim daexcel As OleDbDataAdapter
Dim dsexcel As DataSet
Dim cmdexcel As OleDbCommand
Dim drexcel As OleDbDataReader

 

Dim connodbc As odbcConnection
Dim daodbc As odbcDataAdapter
Dim dsodbc As DataSet
Dim cmdodbc As odbcCommand
Dim drodbc As odbcDataReader

 

Private Sub import_excel_to_mysql_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.CenterToScreen()
End Sub

 

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
On Error Resume Next
'OpenFileDialog1.Filter = "(*.xls)|*.xls|(*.xlsx)|*.xlsx|All files (*.*)|*.*"
OpenFileDialog1.ShowDialog()
TextBox1.Text = OpenFileDialog1.FileName

connexcel = New OleDbConnection("provider=Microsoft.ace.OLEDB.12.0;data source=" & TextBox1.Text & ";Extended Properties=Excel 8.0;")
connexcel.Open()

 

Dim dtSheets As DataTable = connexcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
Dim listSheet As New List(Of String)
Dim drSheet As DataRow

 

For Each drSheet In dtSheets.Rows
listSheet.Add(drSheet("TABLE_NAME").ToString())
Next

 

For Each sheet As String In listSheet
ListBox1.Items.Add(sheet)
Next

End Sub

 

Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
daexcel = New OleDbDataAdapter("select * from [" & ListBox1.Text & "]", connexcel)
dsexcel = New DataSet
daexcel.Fill(dsexcel)
DGV.DataSource = dsexcel.Tables(0)
DGV.ReadOnly = True
End Sub

 

Sub Koneksimysql()
connodbc = New OdbcConnection("Driver=MySQL ODBC 5.1 Driver; SERVER=localhost; UID=root;DATABASE=importfromexcel;PORT=3306")
connodbc.Open()
End Sub

 

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

For baris As Integer = 0 To DGV.RowCount - 2
Call Koneksimysql()
Dim simpan As String = "insert into hasilimport values('" & DGV.Rows(baris).Cells(0).Value & "','" & DGV.Rows(baris).Cells(1).Value & "')"
cmdodbc = New OdbcCommand(simpan, connodbc)
cmdodbc.ExecuteNonQuery()
Next
MsgBox("data berhasil disimpan")
DGV.Columns.Clear()
End Sub


End Class