Excel Office Automation menggunakan Visual Basic.NET

Posted on June 1, 2011. Filed under: IT, VB.NET | Tags: , , , , , , , , , |

Tulisan dibawah ini akan membahas bagaimana proses “Excel Office Automation ” pada Visual Basic.NET (EOA VB.NET),namun sebelum anda membuat sebuah proses EOA VB.NET,sebaiknya anda sudah familiar dengan Visual Basic.NET (Form,Class,Event,Property,Reference dll) karena dalam tutorial ini saya tidak mencantumkan tutorial bagaimana membuat beberapa hal tersebut.
Tutorial EOA VB.NET berikut ini akan menuntun kita bagaimana data yang ada kemudian diexport kedalam sebuah file berformat excel. Karena dalam banyak hal ternyata laporan-laporan dari end user adalah berformat excel. EOA VB.NET akan membuat pekerjaan user lebih efisien, karena laporan yang ada sudah terotomatisasi sehingga user tidak perlu lagi menghabiskan waktu untuk tampilan file excel tersebut.
Sebelum anda membuat EOA VB.NET,maka anda membutuhkan beberapa file yang diperlukan pada proses ini, diantaranya adalah :
– Interop.excel.dll
– Microsoft.Office.Core.dll
– Microsoft.Office.Interop.Excel.dll

Ketiga file ini harus berada didalam folder references dari solution yang akan anda buat
Gambar dibawah ini adalah sebuah data contoh yang akan diexport menjadi sebuah file berformat excel

Data EOA VB.NET

Data EOA VB.NET

Konstruktor class :

Class excel & region “Variable”

Region Variabel

Region Variabel

Region “Enumeration”

Region Enumeration

Region Enumeration

Foler Button Handles Click

Folder Button Handles.CLICK

Folder Button Handles.CLICK

Coding EOA VB.NET

Private Sub Excel_Transfer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Excel_Transfer.Click
If Me.ExcelFile_Txt.Text = “” Then
MsgBox(“File Path is Empty”)
Exit Sub
End If

__Date = Me.AR_Datetime.Value
__FileAS = Trim(Me.ExcelFile_Txt.Text) & “/Laporan_AR_” & Format(__Date, “ddMMMMyy_HHmmss”) & “.xls”

If System.IO.Directory.Exists(Me.ExcelFile_Txt.Text) Then

oXLsWBook = oXLsApps.Workbooks.Add

While oXLsWBook.Worksheets.Count > 1
Dim __X As Global.Excel.Worksheet = oXLsWBook.Worksheets(1)
__X.Delete()
End While

oXLsWSheet = oXLsWBook.Worksheets.Add
oXLsWSheet.Name = “Laporan AR”

With oXLsWSheet.Range(“B1”, “B1”)
.Value = “Laporan AR”
.Font.Bold = True
End With

With oXLsWSheet.Range(“B2”, “B2″)
.Value = __Date ‘”Periode : ” & Format(__Date, “dd-mm-yyyy”)
.NumberFormat = “dd-mmm-yyyy”
.Font.Bold = True
.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft
End With

With oXLsWSheet.Range(“A4”, “F4”)
.Value = __ARHeader
.Font.Bold = True
.Interior.ColorIndex = 1
.Font.ColorIndex = 2
.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter

End With

__TotROWs = 4
For i As Integer = 0 To Me.Grid1.Rows.Count – 2
__TotROWs += 1
oXLsWSheet.Range(“A” & __TotROWs).Value = “‘” & Me.Grid1.Item(0, i).Value.ToString
oXLsWSheet.Range(“B” & __TotROWs).Value = “‘” & Me.Grid1.Item(1, i).Value.ToString
oXLsWSheet.Range(“C” & __TotROWs).Value = Me.Grid1.Item(2, i).Value.ToString
oXLsWSheet.Range(“D” & __TotROWs).Value = Me.Grid1.Item(3, i).Value.ToString
oXLsWSheet.Range(“E” & __TotROWs).Value = Me.Grid1.Item(4, i).Value.ToString
oXLsWSheet.Range(“F” & __TotROWs).Value = Me.Grid1.Item(5, i).Value.ToString
Next

__TotROWs += 1
With oXLsWSheet.Range(“C4”, “F” & __TotROWs)
.EntireRow.NumberFormat = “#,##0_);[Red](#,##0)”

End With

With oXLsWSheet.Range(“C” & __TotROWs)
.Value = “=SUM(C5:C” & __TotROWs – 1 & “)”
.Interior.ColorIndex = 15

.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick
.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous

.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick
.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous

.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick
.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous

.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick
.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous

End With

With oXLsWSheet.Range(“D” & __TotROWs)
.Value = “=SUM(D5:D” & __TotROWs – 1 & “)”
.Interior.ColorIndex = 15

.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick
.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous

.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick
.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous

.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick
.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous

.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick
.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous

End With

With oXLsWSheet.Range(“E” & __TotROWs)
.Value = “=SUM(E5:E” & __TotROWs – 1 & “)”
.Interior.ColorIndex = 15

.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick
.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous

.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick
.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous

.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick
.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous

.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick
.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous

End With

With oXLsWSheet.Range(“F” & __TotROWs)
.Value = “=SUM(F5:F” & __TotROWs – 1 & “)”
.Interior.ColorIndex = 15

.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick
.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous

.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick
.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous

.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick
.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous

.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick
.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous

End With

oXLsWSheet.Range(“C” & __TotROWs, “F” & __TotROWs).Font.Bold = True

With oXLsWSheet.Range(“A1”, “F” & __TotROWs)
.EntireColumn.AutoFit()
.EntireColumn.Font.Name = FONTnya.Tahoma.ToString
.EntireColumn.Font.Size = 9
End With

oXLsWSheet.Range(“B1”).ColumnWidth = 60

oXLsWBook.SaveAs(__FileAS, FileFormat:=Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7)
oXLsApps.DisplayAlerts = True
oXLsWBook.Close()
oXLsApps.Quit()

MsgBox(“Proceed Transfer is Done!!!”)
Else
MsgBox(“Invalid Folder Path!!!”)
End If

End Sub

Output EOA VB.NET

Output Excel Office Automation

Output Excel Office Automation

Fungsi Class Excel

Class Excel

Class Excel

Excel Macro

apabila mengalami kendala dalam fungsi2 excel yang mungkin belum kita ketahui,dapat dicoba dengan menggunakan fungsi Macro pada Excel dan kemudian diterapkan pada Coding.

Excel Macro

Excel Macro

Demikian tutorial Excel Office Automation menggunakan Visual Basic.NET, semoga bermanfaat bagi kita semua

Bangun Ariyanto

ganbatte!!!!!

God Bless You ALL

Make a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

3 Responses to “Excel Office Automation menggunakan Visual Basic.NET”

RSS Feed for Bangun Ariyanto's Blog Comments RSS Feed

makasi bgt bro..
tp ketiga file tersebut kita copy dari mana?
sory kalo terlalu..:-)

maksudnya 3 file :
– Interop.excel.dll
– Microsoft.Office.Core.dll
– Microsoft.Office.Interop.Excel.dll

file ini secara otomatis akan masuk kedalam project/solusion ketika kita mengambil dari menu references.Jadi tinggal pilih komponen dimenu references saja.

tq
GBU
Ganbatte!!!!!!!!!

penjelasannya sangat detail dan jelas… terimakasih ya…


Where's The Comment Form?

Liked it here?
Why not try sites on the blogroll...

%d bloggers like this: