Excel Office Automation menggunakan Visual Basic.NET
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
Konstruktor class :
Class excel & region “Variable”
Region “Enumeration”
Foler 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 WhileoXLsWSheet = oXLsWBook.Worksheets.Add
oXLsWSheet.Name = “Laporan AR”With oXLsWSheet.Range(“B1”, “B1”)
.Value = “Laporan AR”
.Font.Bold = True
End WithWith 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 WithWith oXLsWSheet.Range(“A4”, “F4”)
.Value = __ARHeader
.Font.Bold = True
.Interior.ColorIndex = 1
.Font.ColorIndex = 2
.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenterEnd 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.xlContinuousEnd 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.xlContinuousEnd 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.xlContinuousEnd 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.xlContinuousEnd 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 WithoXLsWSheet.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 IfEnd Sub
Output EOA VB.NET
Fungsi 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.
Demikian tutorial Excel Office Automation menggunakan Visual Basic.NET, semoga bermanfaat bagi kita semua
Bangun Ariyanto
ganbatte!!!!!
God Bless You ALL
Read Full Post | Make a Comment ( 3 so far )