22 April 2015

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

4 comments:

Bonifasius Anggara said...

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 ?

rani irsan said...

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

Fihris, Ahmad said...

deklarasi openfiledialog1 mana ya mb?

rani irsan said...

itu nama control openfiledialog