export import dari excel ke sql server
Imports System.Data.OleDb
Imports System.Data.SqlClient
Public Class import_excel_to_sql_server
Dim connexcel As OleDbConnection
Dim daexcel As OleDbDataAdapter
Dim dsexcel As DataSet
Dim cmdexcel As OleDbCommand
Dim drexcel As OleDbDataReader
Dim connsql As SqlConnection
Dim dasql As SqlDataAdapter
Dim dssql As DataSet
Dim cmdsql As SqlCommand
Dim drsql As SqlDataReader
Private Sub import_excel_to_sql_server_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 Koneksisql()
connsql = New SqlConnection("data source=.\sqlexpress;initial catalog=importfromexcel;integrated security=true")
connsql.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 Koneksisql()
Dim simpan As String = "insert into hasilimport values('" & DGV.Rows(baris).Cells(0).Value & "','" & DGV.Rows(baris).Cells(1).Value & "')"
cmdsql = New SqlCommand(simpan, connsql)
cmdsql.ExecuteNonQuery()
Next
MsgBox("data berhasil disimpan")
DGV.Columns.Clear()
End Sub
End Class