cara export data mysql ke excel

 

Imports System.Data.Odbc
Imports System.IO

 

Public Class export_mysql_to_excel

Dim Conn As OdbcConnection
Dim DA As OdbcDataAdapter
Dim DS As DataSet
Dim CMD As OdbcCommand
Dim DR As OdbcDataReader

 

Sub KoneksiServer()
Conn = New OdbcConnection("Driver=MySQL ODBC 5.1 Driver; SERVER=localhost; UID=root; PWD=;DATABASE=information_schema;PORT=3306")
Conn.Open()
End Sub

 

Sub KoneksiDatabase()
Conn = New OdbcConnection("Driver=MySQL ODBC 5.1 Driver;SERVER=localhost;UID=root;PWD=;DATABASE=" & ListBox1.Text & ";PORT=3306")
Conn.Open()
End Sub

 

Private Sub export_mysql_to_excel_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.CenterToScreen()
End Sub

 

Private Sub BTNConnectServer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTNConnectServer.Click

Call KoneksiServer()
CMD = New OdbcCommand("select schema_name from schemata", Conn)
DR = CMD.ExecuteReader
ListBox1.Items.Clear()
Do While DR.Read
ListBox1.Items.Add(DR(0))
Loop
End Sub

 

Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
Call KoneksiServer()
CMD = New OdbcCommand("select TABLE_NAME from tables where table_schema='" & ListBox1.Text & "' and tables.table_type<>'SYSTEM VIEW'", Conn)
DR = CMD.ExecuteReader
ListBox2.Items.Clear()
Do While DR.Read
ListBox2.Items.Add(DR.Item(0))
Loop
End Sub

 

Private Sub ListBox2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox2.SelectedIndexChanged

Call KoneksiDatabase()
DA = New OdbcDataAdapter("select * from " & ListBox2.Text & "", Conn)
DS = New DataSet
DA.Fill(DS)
DGV.DataSource = DS.Tables(0)
DGV.ReadOnly = True

Label1.Text = Application.StartupPath + "\" + ListBox2.Text + ".xls"
End Sub

 

Private Sub BTNExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTNExport.Click
If ListBox2.Text = "" Then
MsgBox("pilih nama tabel di listbox sebelah kanan")
Exit Sub
End If

Try
Dim dt As New DataTable()
For Each col As DataGridViewColumn In DGV.Columns
dt.Columns.Add(col.HeaderText, col.ValueType)
Next

Dim count As Integer = 0
For Each row As DataGridViewRow In DGV.Rows
If count < DGV.Rows.Count - 1 Then
dt.Rows.Add()
For Each cell As DataGridViewCell In row.Cells
dt.Rows(dt.Rows.Count - 1)(cell.ColumnIndex) = cell.Value.ToString()
Next
End If
count += 1
Next

Dim wr As New StreamWriter(Application.StartupPath + "\" + ListBox2.Text + ".xls")
For i As Integer = 0 To dt.Columns.Count - 1
wr.Write(dt.Columns(i).ToString().ToUpper() & vbTab)
Next
wr.WriteLine()
For i As Integer = 0 To (dt.Rows.Count) - 1
For j As Integer = 0 To dt.Columns.Count - 1
If dt.Rows(i)(j) IsNot Nothing Then
wr.Write(Convert.ToString(dt.Rows(i)(j)) & vbTab)
Else
wr.Write(vbTab)
End If
Next
wr.WriteLine()
Next
wr.Close()
MsgBox("Data Exported Successfully")

Catch ex As Exception
MsgBox("Ada data yang tidak valid")

End Try
If MessageBox.Show("open file...?", "", MessageBoxButtons.YesNo) = Windows.Forms.DialogResult.Yes Then
Try
System.Windows.Forms.Help.ShowHelp(Me, Label1.Text)
Catch ex As Exception
MsgBox(ex.Message)
End Try
End If
End Sub
End Class

 

 

uusrusmawan.com