Cara Export Data Datagridview ke Excel di VB.NET

Saya membuat aplikasi penilaian KTSP & K13, kebetulan saya malas mendesign laporan dari Crystal Report dan export CR ke Excel. Tutorial ini berisi source code untuk menampilkan laporan dengan komponen di dalam form, komponen yang saya pakai adalah :
  1. Datagridview (GridDataNilaiCopy)
  2. Microsoft.Office.Interop.Excel
Gambar Pendukung :
Cara Export Data Datagridview ke Excel di VB.NET

Ketika di klik tombol excel, maka hasilnya seperti ini : Cara Export Data Datagridview ke Excel di VB.NET

Source Code :
Public Sub Cetak_Excel()
        Dim dset As New DataSet
        dset.Tables.Add()
        For i As Integer = 0 To 13
            dset.Tables(0).Columns.Add(GridDataNilai.Columns(i).HeaderText)
        Next
        dset.Tables(0).Columns.Add(GridDataNilai.Columns(21).HeaderText)
        dset.Tables(0).Columns.Add(GridDataNilai.Columns(27).HeaderText)
        Dim dr1 As DataRow
        For i As Integer = 0 To GridDataNilai.RowCount - 1
            dr1 = dset.Tables(0).NewRow
            For j As Integer = 0 To 15
                dr1(j) = GridDataNilai.Rows(i).Cells(j).Value
            Next
            For ii As Integer = 14 To 14
                dr1(ii) = GridDataNilai.Rows(i).Cells(21).Value
            Next
            For ii As Integer = 15 To 15
                dr1(ii) = GridDataNilai.Rows(i).Cells(27).Value
            Next
            dset.Tables(0).Rows.Add(dr1)
        Next
        Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass
        Dim wBook As Microsoft.Office.Interop.Excel.Workbook
        Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet
        wBook = excel.Workbooks.Add()
        wSheet = wBook.ActiveSheet()
        Dim dt As System.Data.DataTable = dset.Tables(0)
        Dim dc As System.Data.DataColumn
        Dim dr As System.Data.DataRow
        Dim colIndex As Integer = 0
        Dim rowIndex As Integer = 5 'Baris ke 5
        excel.Cells(1, 1) = Me.Text & " // Tanggal : " & Now.Date
        excel.Cells(1, 1).Font.Bold = True
        excel.Cells(3, 1) = "K.D. 1 : "
        excel.Cells(3, 1).Font.Bold = True
        excel.Cells(3, 2) = KD1.Text
        excel.Cells(3, 2).Font.Bold = True
        excel.Cells(4, 1) = "K.D. 2 : "
        excel.Cells(4, 1).Font.Bold = True
        excel.Cells(4, 2) = KD2.Text
        excel.Cells(4, 2).Font.Bold = True
        excel.Cells(3, 4) = "K.D. 3 : "
        excel.Cells(3, 4).Font.Bold = True
        excel.Cells(3, 5) = KD3.Text
        excel.Cells(3, 5).Font.Bold = True
        excel.Cells(4, 4) = "K.D. 4 : "
        excel.Cells(4, 4).Font.Bold = True
        excel.Cells(4, 5) = KD4.Text
        excel.Cells(4, 5).Font.Bold = True
        excel.Cells(3, 7) = "K.D. 5 : "
        excel.Cells(3, 7).Font.Bold = True
        excel.Cells(3, 8) = KD5.Text
        excel.Cells(3, 8).Font.Bold = True
        excel.Cells(4, 7) = "K.D. 6 : "
        excel.Cells(4, 7).Font.Bold = True
        excel.Cells(4, 8) = KD6.Text
        excel.Cells(4, 8).Font.Bold = True
        excel.Cells(3, 10) = "K.D. 7 : "
        excel.Cells(3, 10).Font.Bold = True
        excel.Cells(3, 11) = KD7.Text
        excel.Cells(3, 11).Font.Bold = True
        excel.Cells(4, 10) = "K.D. 8 : "
        excel.Cells(4, 10).Font.Bold = True
        excel.Cells(4, 11) = KD8.Text
        excel.Cells(4, 11).Font.Bold = True
        For Each dc In dt.Columns
            colIndex = colIndex + 1
            excel.Cells(6, colIndex).Font.Bold = True
            excel.Cells(6, colIndex) = dc.ColumnName.ToUpper
            Dim border As Microsoft.Office.Interop.Excel.Borders = excel.Cells(6, colIndex).Borders
            border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous
            border.Weight = 2.0
        Next
        For Each dr In dt.Rows
            rowIndex = rowIndex + 1
            colIndex = 0
            For Each dc In dt.Columns
                colIndex = colIndex + 1
                excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName) 'Data di Datagrid
                Dim border As Microsoft.Office.Interop.Excel.Borders = excel.Cells(rowIndex + 1, colIndex).Borders
                border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous
                border.Weight = 2.0
            Next
        Next
        'wSheet.Columns.AutoFit()
        excel.Visible = True
 End Sub
Sekian dan terima kasih.

Artikel Terkait Cara Export Data Datagridview ke Excel di VB.NET :