download gratis video tutorial vb 6 dan vb.net plus contoh aplikasi

cara export datagridview ke excel dengan vb.net

 

 

 

Imports System.Data.OleDb

 

Public Class exportkeexcel

 

Dim Conn As OleDbConnection
Dim DA As OleDbDataAdapter
Dim DS As DataSet
Dim CMD As OleDbCommand
Dim DR As OleDbDataReader
Dim TBL As DataTable

 

Public Sub Koneksi()
Conn = New OleDbConnection("provider=microsoft.jet.oledb.4.0;data source='" & Label1.Text & "'")
Conn.Open()
End Sub

 

Private Sub exportkeexcel_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Call Koneksi()
End Sub

 

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
On Error Resume Next
OpenFileDialog1.ShowDialog()
Label1.Text = OpenFileDialog1.FileName
Call tampiltabel()
End Sub

 

Sub tampiltabel()
Dim userTables As DataTable = Nothing
Call Koneksi()

Dim restrictions() As String = New String(4) {}
restrictions(3) = "Table"
userTables = Conn.GetSchema("Tables")
userTables = Conn.GetSchema("Tables", New String() {Nothing, Nothing, "TABLE"})
Conn.Close()

Dim i As Integer
For i = 0 To userTables.Rows.Count - 1 Step i + 1
System.Console.WriteLine(userTables.Rows(i)(2).ToString())
ListBox1.Items.Add(userTables.Rows(i)(2).ToString())
Next

Dim SchemaTable As DataTable
ListBox1.Items.Clear()
Call Koneksi()
SchemaTable = Conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, Nothing})
Dim int As Integer
For int = 0 To SchemaTable.Rows.Count - 1
If SchemaTable.Rows(int)!TABLE_TYPE.ToString = "TABLE" Then
ListBox1.Items.Add(SchemaTable.Rows(int)!TABLE_NAME.ToString())
End If
Next
End Sub


Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Try
CMD = New OleDbCommand("SELECT * INTO [Excel 8.0;DATABASE=" & ListBox1.Text & ".xls;].[" & ListBox1.Text & "] from [" & ListBox1.Text & "]", Conn)
CMD.ExecuteNonQuery()
Label2.Text = Application.StartupPath + "\" + ListBox1.Text + ".xls"
MsgBox("file sudah berhasil dikonversi")
Catch ex As Exception
MsgBox(ex.Message)
Dim pesan = InputBox("tulis nama tabel alternatif")
CMD = New OleDbCommand("SELECT * INTO [Excel 8.0;DATABASE=" & pesan & ".xls;].[" & ListBox1.Text & "] from [" & ListBox1.Text & "]", Conn)
CMD.ExecuteNonQuery()
MsgBox("file sudah berhasil dikonversi")
Label2.Text = Application.StartupPath + "\" + pesan + ".xls"
End Try
End Sub

 

Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
'==========================================
DGV.Columns.Clear()
DGV.Columns.Add("Nomor", "Nomor")
'==========================================
DA = New OleDbDataAdapter("select * from " & ListBox1.Text & "", Conn)
DS = New DataSet
DA.Fill(DS)
DGV.DataSource = DS.Tables(0)
DGV.ReadOnly = True
For baris As Integer = 0 To DGV.RowCount - 2
DGV.Rows(baris).Cells(0).Value = baris + 1
Next
Label2.Text = Application.StartupPath + "\" + ListBox1.Text + ".xls"
End Sub

 

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Try
System.Windows.Forms.Help.ShowHelp(Me, Label2.Text)
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub

End Class