KERJA IT - COMPUTER

Markas Rabbani Surabaya Jl.Pucang Anom Timur No. 14 Surabaya Telp. 08123015071 Email : Markas_rabbani_sby@yahoo.co.id

  • RSS
  • Google +
  • Facebook

Popular Posts

IDHOSTINGER memang OK
Radio - TV OnLine
Asyiiikk Bro

Translate Google

Popular Posts

Markas Rabbani Surabaya

HOSTINGER

Web Hosting

KOMINITAS VFP

IP ADDRESS INFO

IP

Total Tayangan Halaman

HOSTINGER

Web Hosting

Prof DR KH Said Aqil Sirodj - Ketua PB NU

Gus Mus - Prof DR KH Said Aqil Sirodj - Alm Gus Dur

HOSTINGER Web Gratis Tanpa Iklan

Gak Percaya Buktikan

Griya Asy-Syifa

Jl Suromulang Timur II/18 Perum Citra Surodinawan Estate Kota Mojokerto Contac : 081231821800, 08814353000, 085746201881 Pin BB : 24D0B84C e-mail : ariesmojokerto@gmail.com

HOSTINGER Gratis tanpa Iklan

Web Hosting GRATIS dI Indonesia Tanpa Iklan

WaliSongo

Babat alas Tanah Jawa - Pengikut Nabi Muhammad saw - NU

Kamis, 18 Oktober 2012

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


5 komentar: