VB.NET MySQL: Import Data dari Excel - Part 3

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