VB .NET : Input Data & Format Tabel MS Excel

Imports Excel = Microsoft.Office.Interop.Excel
'Source code yg udah bagus kemampuan untuk mematikan service Excel nya
'Menggunakan framework ini aja untuk selanjutnya

Public Class Form4

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

        Dim appXL As Excel.Application

        Dim wbXL As Excel.Workbook

        Dim shXL As Excel.Worksheet

        Dim raXL As Excel.Range


        appXL = CreateObject("Excel.Application")

        appXL.Visible = True

        wbXL = appXL.Workbooks.Add

        shXL = wbXL.ActiveSheet

        shXL.Cells(1, 1).Value = "Bulan"

        shXL.Cells(1, 2).Value = "Uang yg Dibelanjakan"

        shXL.Cells(6, 1).Value = "Total Pengeluaran"

        shXL.Cells(7, 1).Value = "Rata-rata Pengeluaran"

        With shXL.Range("A1", "B1")

            .Font.Bold = True

            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter

            .Font.ColorIndex = 3

            .Interior.ColorIndex = 20


        End With

        Dim Bulan(5, 2) As String

        Dim Pengeluaran(5, 2) As Integer

        Bulan(0, 0) = "Januari"

        Bulan(1, 0) = "Februari"

        Bulan(2, 0) = "Maret"

        Bulan(3, 0) = "April"


        Pengeluaran(0, 0) = 1000

        Pengeluaran(1, 0) = 1500

        Pengeluaran(2, 0) = 1200

        Pengeluaran(3, 0) = 1100


        shXL.Range("A2", "A5").Value = Bulan

        shXL.Range("B2", "B5").Value = Pengeluaran


        shXL.Range("B6").Formula = "=Sum(B2:B5)"

        shXL.Range("B7").Formula = "=Average(B2:B5)"


        'shXL.Range("A1:B1").Interior.ColorIndex = 20

        'shXL.Range("A1:B1").Font.ColorIndex = 3

        shXL.Range("B6", "B7").Font.Bold = True

        shXL.Range("B2:B7").NumberFormat = "Rp #,###.00"







        'Autofit Columns A:B

        raXL = shXL.Range("A1", "B1")

        raXL.EntireColumn.AutoFit()


        With shXL.Range("A1:B7")

            'Membuat garis samping tabel
            With .Borders(Excel.XlBordersIndex.xlEdgeLeft)

                .LineStyle = Excel.XlLineStyle.xlDouble

                .ColorIndex = 0

                .TintAndShade = 0

                .Weight = Excel.XlBorderWeight.xlThin

            End With


            With .Borders(Excel.XlBordersIndex.xlEdgeTop)

                .LineStyle = Excel.XlLineStyle.xlContinuous

                .ColorIndex = 0

                .TintAndShade = 0

                .Weight = Excel.XlBorderWeight.xlThin

            End With


            With .Borders(Excel.XlBordersIndex.xlEdgeBottom)

                .LineStyle = Excel.XlLineStyle.xlContinuous

                .ColorIndex = 0

                .TintAndShade = 0

                .Weight = Excel.XlBorderWeight.xlThin

            End With


            With .Borders(Excel.XlBordersIndex.xlEdgeRight)

                .LineStyle = Excel.XlLineStyle.xlContinuous

                .ColorIndex = 0

                .TintAndShade = 0

                .Weight = Excel.XlBorderWeight.xlThin

            End With

            With .Borders(Excel.XlBordersIndex.xlInsideVertical)

                .LineStyle = Excel.XlLineStyle.xlContinuous

                .ColorIndex = 0

                .TintAndShade = 0

                .Weight = Excel.XlBorderWeight.xlThin
            End With

            With .Borders(Excel.XlBordersIndex.xlInsideHorizontal)

                .LineStyle = Excel.XlLineStyle.xlContinuous

                .ColorIndex = 0

                .TintAndShade = 0

                .Weight = Excel.XlBorderWeight.xlThin

            End With

        End With

        appXL.Visible = True

        appXL.UserControl = True


        raXL = Nothing

        wbXL = Nothing

        shXL = Nothing

        appXL.Quit()

        appXL = Nothing

        Exit Sub


Err_Handler:

        MsgBox(Err.Description, vbCritical, "Error: " & Err.Number)


    End Sub
End Class