Artikel ini masih merupakan artikel lanjutan dari Part2, yang akan menampilkan kode lengkap yang sudah kita susun sebelumnya.
Imports System.IO Imports System.Data Imports System.Data.OleDb Imports MySql.Data.MySqlClient Public Class frmImportExcel Dim conn As New MySqlConnection Dim strConn As String = "server=localhost;user id=root;" & _ "password=xxxx;database=latihan" Dim tblImport As DataTable Sub MySQLDataLoad() Dim myCommand As New MySqlCommand Dim myAdapter As New MySqlDataAdapter Dim myData As New DataTable Dim SQL As String Try conn.Open() SQL = "Select nik, nama From tblkaryawan" myCommand.Connection = conn myCommand.CommandText = SQL myAdapter.SelectCommand = myCommand myAdapter.Fill(myData) DataGridView2.DataSource = myData conn.Close() Catch myerror As MySqlException MessageBox.Show("Error: " & myerror.Message) Finally conn.Dispose() End Try End Sub Private Sub frmImportExcel_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load TextBox1.ReadOnly = True Button1.Text = "Browse..." Button2.Enabled = False Button2.Text = "Import" Label1.Text = "Data Excel" Label2.Text = "Data MySQL" With DataGridView1 .AllowUserToDeleteRows = False .AllowUserToAddRows = False .ReadOnly = True End With With DataGridView2 .AllowUserToDeleteRows = False .AllowUserToAddRows = False .ReadOnly = True End With conn.ConnectionString = strConn MySQLDataLoad() End Sub 'Untuk menampilkan data sheet di grid Sub LoadExcel2Grid(ByVal FileName As String, ByVal SheetName As String) Dim exConn As OleDbConnection Dim dt As DataSet Dim cmd As OleDbDataAdapter
Dim sConn As String sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _ FileName & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";" exConn = New System.Data.OleDb.OleDbConnection(sConn) cmd = New System.Data.OleDb.OleDbDataAdapter( _ "select * from [" & SheetName & "]", exConn) cmd.TableMappings.Add("Table", SheetName) dt = New System.Data.DataSet cmd.Fill(dt) tblImport = dt.Tables(0) DataGridView1.DataSource = tblImport exConn.Close() End Sub 'Untuk menampilkan nama sheet di listbox Sub GetExcelSheetNames(ByVal FileName As String) Dim sConn As String sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _ FileName & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";" Dim conn As New OleDbConnection(sConn) conn.Open() Dim dtSheets As DataTable = _ conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing) Dim drSheet As DataRow ListBox1.Items.Clear() For Each drSheet In dtSheets.Rows ListBox1.Items.Add(drSheet("TABLE_NAME").ToString()) Next LoadExcel2Grid(FileName, ListBox1.Items(0).ToString) conn.Close() End Sub Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click With OpenFileDialog1 .FileName = String.Empty .InitialDirectory = "C:\" .Title = "Open Excel File" .Filter = "Excel 97-2003|*.xls|Excel 2007|*.xlsx" End With Dim result As DialogResult = OpenFileDialog1.ShowDialog() If result = Windows.Forms.DialogResult.OK Then Try TextBox1.Text = OpenFileDialog1.FileName GetExcelSheetNames(TextBox1.Text) Catch ex As Exception MsgBox("Error : " & ex.Message) End Try End If End Sub Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged LoadExcel2Grid(TextBox1.Text, ListBox1.SelectedItem.ToString) End Sub Private Sub TextBox1_TextChanged(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles TextBox1.TextChanged Button2.Enabled = (Len(TextBox1.Text) > 0) End Sub Private Sub Button2_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button2.Click For i As Integer = 0 To tblImport.Rows.Count - 1 DoInsert(tblImport.Rows(i).Item(0).ToString, _ tblImport.Rows(i).Item(1).ToString) Next MsgBox("Proses Import Selesai!") MySQLDataLoad() End Sub Sub DoInsert(ByVal strNIK As String, ByVal strNama As String) Dim myCommand As New MySqlCommand Dim myAdapter As New MySqlDataAdapter Dim myData As New DataTable Dim SQL As String Try If conn.State = ConnectionState.Closed Then conn.Open() SQL = "INSERT IGNORE INTO tblKaryawan (nik, nama) VALUES " & _ "('" & strNIK & "', '" & strNama & "')" myCommand.Connection = conn myCommand.CommandText = SQL myCommand.ExecuteNonQuery() MsgBox("Data baru tersimpan") conn.Close() Catch myerror As MySqlException MessageBox.Show("Error: " & myerror.Message) Finally conn.Dispose() End Try End Sub End Class
That's all kawan, klo kira2 ada yang terlewat comment aja yah. Happy Coding ^_^v
Mbak Rani, maaf mau tanya, saya ada kasus gini, pertama database kosong, o ya, saya pakai MS Sql Server, nah pertama import data dari excel ya kan, sudah ter-insert ke data base nih....lalu saya mau import lagi, row yg di excelnya, nambah, jadi ada data lama, dan data baru juga, nah yg data lama, mau saya lewatin, kaya di skip kalo kita copy file di windows gitu, nah yang data barunya, row row barunya, pengennya bisa ke insert...itu kodenya kayamana ya mbak ya ? soalnya, program saya selalu terhenti oleh cmd.ExecuteNonQuery(), jadi aja programnya berhenti, pengennya sih yg row row baru di excelnya itu bisa ter-insert, dan yg row row lama, yg data nya udah ada di database, dilewati saja.... mohon bantuannya mbak.... o ya, tanya satu lagi mbak, kalo yg punya mbak pake database mysql, itu saat deployment, komputer target harus diinstalin database mysql juga kah ?
coba diakalin di query nya, klo di MySQL ada yang nama nya INSERT IGNORE, klo di SQL Server ada yang nama nya WHERE NOT EXIST, googling aja sample nya
iya satu komputer yang berfungsi sebagai database server harus diinstall MySQL nya, yg lainya klo terhubung jaringan tinggal konek ke server database tersebut tanpa menginstall MySQL
win7 64bit office 2010 vs2010 sukses , tapi jika project di win10 64bit office 2016 vs2019 comunity, error ('Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine), solusinya gmn ya mbk ?
wah saya sendiri office nya masih mentok di 2010 sih ^_^ coba install dulu ini jalan ga? https://www.microsoft.com/en-us/download/details.aspx?id=13255
@Jamaludin: OLEDB nya disini justru buat koneksi ke Excel nya. Untuk ke MySQL saya pakai MySQL Connector. Klo @jamaludin pakai ODBC untuk koneksi ke database nya ya berarti object2 mysql nya yang perlu diganti ke ODBC, contohnya:
Imports System.Data.Odbc Dim conn As New OdbcConnection Dim myCommand As New OdbcCommand Dim myAdapter As New OdbcDataAdapter
Dan tentu saja connection string nya pun menyesuaikan.
11 Comments
soalnya, program saya selalu terhenti oleh cmd.ExecuteNonQuery(), jadi aja programnya berhenti, pengennya sih yg row row baru di excelnya itu bisa ter-insert, dan yg row row lama, yg data nya udah ada di database, dilewati saja....
mohon bantuannya mbak....
o ya, tanya satu lagi mbak, kalo yg punya mbak pake database mysql, itu saat deployment, komputer target harus diinstalin database mysql juga kah ?
iya satu komputer yang berfungsi sebagai database server harus diinstall MySQL nya, yg lainya klo terhubung jaringan tinggal konek ke server database tersebut tanpa menginstall MySQL
coba install dulu ini jalan ga?
https://www.microsoft.com/en-us/download/details.aspx?id=13255
pilih yang 64bit klo pake win10 64bit
OLEDB nya disini justru buat koneksi ke Excel nya.
Untuk ke MySQL saya pakai MySQL Connector.
Klo @jamaludin pakai ODBC untuk koneksi ke database nya ya berarti object2 mysql nya yang perlu diganti ke ODBC, contohnya:
Imports System.Data.Odbc
Dim conn As New OdbcConnection
Dim myCommand As New OdbcCommand
Dim myAdapter As New OdbcDataAdapter
Dan tentu saja connection string nya pun menyesuaikan.