MYSQL-DATAGRIDVIEW EXPORT KE EXCEL

DATAGRIDVIEW DI EXPORT KE EXCEL

 Karena kita DataBase nya pakai MYSQL
Langkahnya :
1. Buat Form
2. kita ambil dari toolbox yaitu:
    - DataGridView -  Button
3. Add Modul
4. Add Reference 
    - di (Browse) ..Connect ke MYSQL connector NET 6.5.4
    - di ( COM ).....Microsoft Excel 11.0

Lihat Gambar

  
kita beri nama misal : Modulku
ini untuk menulis Perintah tentang Connection antara MYSQL dng VB-2010

Supaya Connect dng MYSQL lihat gambar :

Selanjutnya untuk connection ke EXCEL sama pojok kanan  atas klik kanan Add Reference
lalu COM pilih Microsoft Excel 11.0 Library

lihat gambar

Baru Peng Codingan :
Coding di Modulku :

Imports MySql.Data.MySqlClient

Imports System.Data

Module modulku

    Public cmd As New MySqlCommand

    Public dadapter As New MySqlDataAdapter

    Public datardr As MySqlDataReader

    Public strSql As String

    Dim serv As String = "Server=localhost;"

    Dim dbase As String = "Database=toko;"

    Dim uid As String = "uid=root;"

    Dim pwd As String = "pwd=;"

    Dim ConString = serv & dbase & uid & pwd

    Public db As New MySqlConnection(ConString)

    Public Sub konek()

        Try

            tutupDB()

            db.Open()

            MsgBox("Koneksi DataBase berhasil dilakukan")

        Catch ex As MySqlException

            MsgBox(ex.Message)

            MsgBox("Koneksi tidak berhasil dilakukan")

        End Try

    End Sub

    Public Sub tutupDB()

        If db.State = ConnectionState.Open Then

            db.Close()

        End If

    End Sub

End Module

baru Coding di Form1

Imports Microsoft.Office.Interop

Imports Excel = Microsoft.Office.Interop.Excel

Imports MySql.Data.MySqlClient

Imports System.Configuration

Imports System.Runtime.InteropServices

Public Class Form1

    Private Sub DisplayData()

  

        Dim query As String = "SELECT * FROM CUSTOMER"

        Dim da As New MySqlDataAdapter(query, db)

        Dim ds As New DataSet()

        If da.Fill(ds) Then

            dgv.DataSource = ds.Tables(0)

        End If

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        konek()

    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        DisplayData()

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim rowsTotal, colsTotal As Short

        Dim I, j, iC As Short

        System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor

        Dim xlApp As New Excel.Application

        Try

            Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add

            Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)

            xlApp.Visible = True

            rowsTotal = dgv.RowCount - 1

            colsTotal = dgv.Columns.Count - 1

            With (excelWorksheet)

                .Cells.Select()

                .Cells.Delete()

                For iC = 0 To colsTotal

                    .Cells(1, iC + 1).Value = dgv.Columns(iC).HeaderText

                Next

                For I = 0 To rowsTotal

                    For j = 0 To colsTotal

                        .Cells(I + 2, j + 1).value = dgv.Rows(I).Cells(j).Value

                    Next (j)

                Next (I)

                .Rows("1:1").Font.FontStyle = "Bold"

                .Rows("1:1").Font.Size = 10

                .Cells.Columns.AutoFit()

                .Cells.Select()

                .Cells.EntireColumn.AutoFit()

                .Cells(1, 1).Select()

            End With

        Catch ex As Exception

            MsgBox("Export Excel Error " & ex.Message)

        Finally

            'RELEASE ALLOACTED RESOURCES 

            System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default

            xlApp = Nothing

        End Try

    End Sub

End Class

Kemudian Save All

lalu jalankan dengan Debug atau F5

Hasilnya :

 lalu Klik tombol button Export to Excel

hasilnya :

moga manfaat