Export DataGridView ke Exel VB.NET

Artikel yang membahas ini sebenarnya sudah sangat banyak. Anda tinggal search di Google dan linknya langsung tampil semua...

Berikut ini adalah script untuk Export DataGridView ke Microsoft Exel.

Oh ya tapi sebelumnya, ini contoh tampilan DataGridViewnya yang akan saya Export.

KOmponen yang dibutuhkan yaitu:
* 1 buah DataGridView dalam kasus ini saya berinama appGrid
* 1 buah Button

Berikut langkahnya:

1. Sebelumnya Import Microsoft.Office.Interop.Excel terlebih dulu:
-----------------------------------------------
Imports Excel = Microsoft.Office.Interop.Excel
-----------------------------------------------

2. Oke. Silahkan buat Fungsi pertama dengan nama ExpExel:
----------------------------------------------
Public Function ExpExel(ByVal obj As Object)
Dim hasil As String = ""
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
Finally
GC.Collect()
End Try
Return hasil
End Function
----------------------------------------------

3. Kita buat fungsi ke dua dengan nama ExportExel
--------------------------------------------
Public Sub ExportExel()
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
Dim chartRange As Excel.Range

Dim i As Integer
Dim j As Integer

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

xlWorkSheet.Cells(5, 1) = "KELOMPOK"
xlWorkSheet.Cells(5, 2) = "REKENING"
xlWorkSheet.Cells(5, 3) = "SALDO"
xlWorkSheet.Cells(5, 4) = "TOTAL"

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

chartRange = xlWorkSheet.Range("A1", "b1")
chartRange.Merge()
chartRange.FormulaR1C1 = "Dicetak Oleh"
chartRange.Font.Bold = True
chartRange = xlWorkSheet.Range("c1", "d1")
chartRange.Merge()
chartRange.FormulaR1C1 = ": 01001"
'-------------------------------------------------------------------------
chartRange = xlWorkSheet.Range("A2", "b2")
chartRange.Merge()
chartRange.FormulaR1C1 = "Tanggal Cetak"
chartRange.Font.Bold = True
chartRange = xlWorkSheet.Range("c2", "d2")
chartRange.Merge()
chartRange.FormulaR1C1 = ": Jumat, 1 Oktober 2010 Pukul : 12:41:01"
'-------------------------------------------------------------------------
chartRange = xlWorkSheet.Range("A3", "d4")
chartRange.Merge()
chartRange.FormulaR1C1 = "AKTIVA"
chartRange.HorizontalAlignment = 3
chartRange.VerticalAlignment = 3
chartRange.Font.Bold = True
'-----------------------------------------------------------------------
chartRange = xlWorkSheet.Range("a5", "d5")
chartRange.VerticalAlignment = 3
chartRange.HorizontalAlignment = 3

xlWorkSheet.SaveAs("C:\ExportExel.xls")
xlWorkBook.Close()
xlApp.Quit()

ExpExel(xlApp)
ExpExel(xlWorkBook)
ExpExel(xlWorkSheet)

MsgBox("Export Exel Berhasil. Silahkan ambil File di C:\ExportExel.xls", MsgBoxStyle.Information)
End Sub
--------------------------------------------
4. Pada Button1 kita tinggal memanggil Fungsi ExportExel() yang sudah kita buat diatas.

Dan berikut ini adalah priview exportnya: