5:37 PM
Ridwan
Setelah agak lama tidak update blog karena kesibukan, alhamdulillah hari ini saya bisa mengunjungi dan mengupdate kembali blog saya.
Setelah sebelumnya saya share coding untuk menyimpan data ke Excel dengan VB 2010, kali ini saya akan share coding tentang bagaimana membaca data dari file Excel, mengolah datanya dan kemudian menyimpannya ke dalam database. Dalam hal ini saya menggunakan Microsoft Access Database (MDB).
File Excel yang akan saya simpan adalah file tsjob.xlsx seperti tampak di bawah ini.
Sedangkan Tabel yang akan saya update adalah table tsjob yang ada pada database PM.MDB. Struktur tabel tsjob adalah sebagai beikut :
Field Name Field Type Field Size
brandnm text 35
projectcat text 50
jobgroupno text 11
jobgroupnm text 50
jobno text 50
oldjobno text 50
oldjobgroupno text 11
oldjobgroupnm text 50
jobid text 10
jobnm text 150
customernm text 40
jobmgr text 40
begin date/time
status text 30
billable Yes/No
description memo
projectcode text 61
company text 25
Selanjutnya lakukan langkah-langkah berikut :
1. Buka VB 2010 kemudian create project baru.
2. Tambahkan form dan beri name frmExcel2mdb.
3. Tambahkan 2 buah datagrid pada form yang telah dibuat.
4. Beri nama salah satu Datagrid yang baru saja ditambahkan dengan grdData. Datagrid ini digunakan untuk menampilkan data hasil import dari file excel.
5. Beri nama datagrid yang lainnya dengan grdJob. Datagrid ini digunakan untuk menampilkan data dari file mdb.
6. Tambahkan 2 button pada form. Satu button diberi nama cmdReadExcel. Dan yang satunya diberi nama cmdSavetoMDB.
7. Selanjutnya copy code di bawah ini.
8. Semoga bermanfaat.
Imports System.Data
Imports System.Data.OleDb
Public Class frmExcel2mdb
Dim ds As DataSet
Dim da As OleDbDataAdapter
Dim ds2 As DataSet
Dim da2 As OleDbDataAdapter
Private Sub cmdReadExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdReadExcel.Click
Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\MYBLOG\VB\EXCEL2MDB\tsjob.xlsx;Extended Properties=Excel 12.0;"
Dim myConnection As OleDbConnection = New OleDbConnection
Dim strSQL As String
Try
myConnection.ConnectionString = connString
strSQL = "Select * from [Sheet1$]"
da = New OleDbDataAdapter(strSQL, myConnection)
ds = New DataSet
da.Fill(ds, "dataTable")
grdData.DataSource = ds.Tables(0)
myConnection.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
Private Sub cmdSavetoMDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSavetoMDB.Click
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\MYBLOG\VB\EXCEL2MDB\PM.mdb;Jet OLEDB:Database Password=;"
Dim MyCon = New OleDbConnection()
Dim strSql As String
Dim dgRow As DataGridViewRow
Dim strSQLi As String
Dim strCmd As OleDbCommand
Try
MyCon.ConnectionString = strConn
MyCon = New OleDbConnection(strConn)
If MyCon.State <> ConnectionState.Closed Then MyCon.Close()
MyCon.Open()
For Each dgRow In Me.grdData.Rows
Dim cmdJob As New OleDbCommand
Dim daJob As New OleDbDataAdapter
Dim dsJob As New DataSet
Dim dtJob As New DataTable
strSql = "Select count(*) from tbJob"
cmdJob = MyCon.CreateCommand
cmdJob.CommandText = "SELECT * FROM tsJob where Trim(JobNo) = '" & Trim(dgRow.Cells(3).Value) & "'"
daJob.SelectCommand = cmdJob
daJob.Fill(dsJob, "Job")
dtJob = dsJob.Tables("Job")
If dtJob.Rows.Count > 0 Then
'TO DO : NOTHING
Else
strSQLi = "Insert into tsjob (BRANDNM,PROJECTCAT,JOBGROUPNO,JOBGROUPNM,JOBNO,OLDJOBNO,OLDJOBGROUPNO,OLDJOBGROUPNM," & _
"JOBID,JOBNM,CUSTOMERNM,STATUS,DESCRIPTION,COMPANY) " & _
"VALUES('" & RTrim(dgRow.Cells(0).Value) & _
"','" & RTrim(dgRow.Cells(2).Value) & "2012" & _
"','" & RTrim(dgRow.Cells(1).Value) & _
"','" & RTrim(dgRow.Cells(2).Value) & _
"','" & RTrim(dgRow.Cells(3).Value) & _
"','" & RTrim(dgRow.Cells(3).Value) & _
"','" & RTrim(dgRow.Cells(1).Value) & _
"','" & RTrim(dgRow.Cells(2).Value) & _
"','" & RTrim(Mid(dgRow.Cells(3).Value, 4)) & _
"','" & RTrim(dgRow.Cells(4).Value) & _
"','" & RTrim(dgRow.Cells(5).Value) & _
"','" & RTrim(dgRow.Cells(6).Value) & _
"','" & "" & _
"','" & "PUBLICIS INDONESIA" & "')"
strCmd = New OleDbCommand(strSQLi, MyCon)
strCmd.ExecuteNonQuery()
strSQLi = ""
End If
Next
Call DisplayJob()
MyCon.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
Private Sub DisplayJob()
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\MYBLOG\VB\EXCEL2MDB\PM.mdb;Jet OLEDB:Database Password=;"
Dim MyCon = New OleDbConnection()
Try
MyCon.ConnectionString = strConn
MyCon = New OleDbConnection(strConn)
If MyCon.State <> ConnectionState.Closed Then MyCon.Close()
MyCon.Open()
Dim cmdJob As New OleDbCommand
Dim daJob As New OleDbDataAdapter
Dim dsJob As New DataSet
Dim dtJob As New DataTable
cmdJob = MyCon.CreateCommand
cmdJob.CommandText = "SELECT * FROM tsJob"
daJob.SelectCommand = cmdJob
daJob.Fill(dsJob, "Job")
dtJob = dsJob.Tables("Job")
Me.grdJob.DataSource = dsJob.Tables(0)
MyCon.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
Private Sub frmExcel2mdb_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.grdData.AllowUserToAddRows = False
Me.grdJob.AllowUserToAddRows = False
Call DisplayJob()
End Sub
End Class
Posted in: Programing
