Selasa, 01 Februari 2011

Cara Ekspor DataGridView ke Excel

Dalam beberapa aplikasi, banyak yang menginginkan hasil pengolahan data yang ditampilkan di grid. langsung di Printout menggunakan excel. selain mudah untuk di print juga dapat diedit jika terjadi kesalahan input. di sini kita akan membahas itu. dan ini List Prognya.


Imports System.Data
Imports System.Data.SqlClient
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles Button1.Click

        Dim cnn As SqlConnection
        Dim connectionString As String
        Dim sql As String

        connectionString = "data source=servername;" & _
        "initial catalog=databasename;user id=username;password=password;"
        cnn = New SqlConnection(connectionString)
        cnn.Open()
        sql = "SELECT * FROM Product"
        Dim dscmd As New SqlDataAdapter(sql, cnn)
        Dim ds As New DataSet
        dscmd.Fill(ds)
        DataGridView1.DataSource = ds.Tables(0)
        cnn.Close()
    End Sub

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


        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim i As Integer
        Dim j As Integer

        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")

        For i = 0 To DataGridView1.RowCount - 2
            For j = 0 To DataGridView1.ColumnCount - 1
                xlWorkSheet.Cells(i + 1, j + 1) = _
                    DataGridView1(j, i).Value.ToString()
            Next
        Next

        xlWorkSheet.SaveAs("C:\vbexcel.xlsx")
        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

        MsgBox("You can find the file C:\vbexcel.xlsx")
    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class


Demikian ekspor data ke excel. silahkan dicoba !!!

4 komentar:

  1. mas kq saya selalu eror di sini ya?
    xlApp = New Excel.ApplicationClass
    itu knapa? masak gara" interopnya?

    BalasHapus
  2. Pak,,,,
    Kritik dikit ya pak,,,,

    kalau Bagi ilmu tu yang lengkap dong pak,,,,
    Yang dimagsud Button 1 itu untuk apa,,??
    BUTTON2 UNTUK APA,,??

    KALAU bISA PAKE GAMBAR langsung biar pengujung blogs paham,,,,,
    Terima kasih,,

    ##maaf,,,,,,,,,,,,,

    BalasHapus
    Balasan
    1. Terima Kasih Atas Kritikannya...., Insya Alloh Nanti akan lebih di Perjelas.

      Hapus
  3. xlApp = New Excel.ApplicationClass ini funsinya untuk apa ?

    BalasHapus