Export DataGridView ke Excel 2007 VB 2008

Tips pemrograman kali ini menggunakan salah satu ToolBox DataGridView Microsoft Visual Basic 2008 Express Edition, Kali ini kita akan mengopi data pada tabel DataGridView ke Excel 2007. Ikuti langkah-langkahnya sebagai berikut:

Design Form dengan menggunakan beberapa ToolBox VB 2008 sebagai berikut

Perancangan/Design pada Form dari ToolBox di atas sebagai berikut:

Perancangan banyak kolom pada DataGridView perhatikan gambar dibawah ini baca pada tutorial sebelumnya. Klik di sini DataGridView Outo Rows Counter

Setelah perancangan di atas selesai arahkan kursor Anda pada Form Design lalu klik kanan pilih View Code. Anda mulailah menuliskan source program di bawah ini:

Source/ listing program sebagai berikut:

Option Explicit On
Imports System.IO
Imports Microsoft.Office.Interop
Public Class Form1

Dim t As Integer
Dim count As Integer

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Button5.Enabled = True
REM Inisialisasi data counter
count = 0
'DataGridResult.Rows.Clear()
TabelData.Rows.GetNextRow(count, DataGridViewElementStates.ReadOnly)
REM Aktifkan data counter agar dapat ditampilkan
REM ke tabel DataGridResult
Timer1.Enabled = True
Timer2.Enabled = True

End Sub

Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick

REM Menampilkan data diatur dengan timer
Dim b As Integer
b = CInt(TextBox1.Text)
TabelData.Rows.Add()
TabelData.Rows.GetNextRow(count, DataGridViewElementStates.ReadOnly)
TabelData.Item(0, b - 1).Value = count
TabelData.Item(1, b - 1).Value = Now
TabelData.Item(2, b - 1).Value = 5
TabelData.Item(3, b - 1).Value = 1
TabelData.Item(4, b - 1).Value = count


End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
REM Menghapus display data pada DataGridResult
Button5.Enabled = False
Timer1.Enabled = False
Timer2.Enabled = False
TabelData.Rows.Clear()
TabelData.Rows.GetNextRow(1, DataGridViewElementStates.ReadOnly)

End Sub

Private Sub Timer2_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer2.Tick

REM untuk counter banyak data
REM atau menentukan banyak baris pada tabel DataGridResult

If Timer2.Enabled = True Then

count = count + 1
TextBox1.Text = count
Else
count = Val(TextBox1.Text) + count
count = count + 1
TextBox1.Text = count
End If

End Sub

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

REM Stop data Counter
Timer1.Enabled = False
Timer2.Enabled = False

End Sub

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
End
End Sub

Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click

REM jika nilai count lebih besar pada textbox1 lebih besar dari 0
REM lanjutkan count pada angka yang ditunjukkan pada text box
If TextBox1.Text > 0 Then
count = TextBox1.Text
Timer1.Enabled = True
Timer2.Enabled = True
End If

End Sub


Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.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)

Dim sY As String
Dim iX As Integer
Dim iY As Integer

Dim TheRg As Excel.Range
Dim oRow As Excel.Range


xlApp.Visible = True
rowsTotal = TabelData.RowCount - 0
colsTotal = TabelData.Columns.Count - 1
For iX = 0 To TabelData.Rows.Count - 2

Next

For iY = 0 To TabelData.Columns.Count - 2

Next
With excelWorksheet
.Cells.Select()
.Cells.Delete()
For iC = 0 To colsTotal
.Cells(1, iC + 1).Value = TabelData.Columns(iC).HeaderText
Next
For I = 0 To rowsTotal - 1
For j = 0 To colsTotal - 0
.Cells(I + 2, j + 1).value = TabelData.Rows(I).Cells(j).Value
Next j
Next I
.Rows("1:1").Font.FontStyle = "Bold"
.Rows("1:1").Interior.colorindex = 37
.Rows("1:1").Font.Size = 11

TheRg = .Rows("2:500")
For Each oRow In TheRg
If (oRow.Row / 2) = Int(oRow.Row / 2) Then
With .Rows(oRow.Row).Interior
.ColorIndex = 40

End With
End If
Next

'test

.Cells.Columns.AutoFit()
.Cells.Select()
.Cells.EntireColumn.AutoFit()
.Cells(1, 1).Select()
sY = TabelData(iY, iX).Value.ToString & ","
End With

Catch ex As Exception
MsgBox("Export Excel Error " & ex.Message)
Finally

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

End Try
End Sub

End Class

Setelah Anda selesai mengetikkan program di atas tekan F5.

Hasil seperti Gambar di bawah ini, kemudian tekan Start, untuk pengambilan data.



Setelah di tekan Start dan di tekan Stop, tampilan seperti gambar di bawah ini.



Untuk mengeksport Ke Excel 2007 tekan tombol Export. Secara otomatis akan muncul file Book1.xlsx dan seterusnya jika lembar kerja excel tidak di simpan. Tampilan seperti gambar di bawah ini.



Selamat mencoba Guys! Nantikan Tips Aplikasi Cantik Lainnya by Verynandus Hutabalian