UPDATE FILE EXCEL DARI VB .NET 2010

Berikut ini adalah contoh coding untuk mengedit/mengupdate file Excel dari VB  2010.

Option Explicit On

Imports Excel = Microsoft.Office.Interop.Excel

Imports Microsoft.VisualBasic

Public Class Form1

    Private aEmployee As New List(Of clsEmployee) From _

         {New clsEmployee With {.Name = "ANTO", .GajiPokok = 5000000, _ 
                                .TTransport =  1000000,.TJabatan = 2000000, _ 
                                .GajiTotal= "=D3+E3+F3", .AsuransiA = "=0.03*G3",_ 
                                .AsuransiB = "=0.02*G3", .Pinjaman = 200000, _ 
                                .TakeHomePay = "=G3-H3-I3-J3"},

          New clsEmployee With {.Name= "TOTO",.GajiPokok=5500000,.TTransport=1500000, _
                                .TJabatan = 3000000, .GajiTotal = "=D4+E4+F4", _

                                .AsuransiA = "=0.03*G4", .AsuransiB = "=0.02*G4", _ 
                                .Pinjaman = 300000, .TakeHomePay = "=G4-H4-I4-J4"},

          New clsEmployee With {.Name ="DINA",.GajiPokok=6000000,.TTransport=1000000, _
                                .TJabatan = 2000000, .GajiTotal = "=D5+E5+F5", _

                                .AsuransiA = "=0.03*G5", .AsuransiB = "=0.02*G5", _ 
                                .Pinjaman = 0, .TakeHomePay = "=G5+H5+I5+J5"},

          New clsEmployee With {.Name="WENY",.GajiPokok=3000000,.TTransport=500000, _
                                .TJabatan = 1000000, .GajiTotal = "=D6+E6+F6", _

                                .AsuransiA = "=0.03*G5", .AsuransiB = "=0.02*G5", _ 
                                .Pinjaman = 0, .TakeHomePay = "=G6+H6+I6+J6"}}

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

        Dim xlApp As Excel.Application

        Dim xlWBook As Excel.Workbook

        Dim xlSheet As Excel.Worksheet

        Dim sheetRange As Excel.Range

        Dim cRow As String

        Dim i As Integer

        xlApp = New Excel.Application

        xlWBook = xlApp.Workbooks.Open("c:\MyProject\Salary.xlsx")

        xlSheet = xlWBook.Worksheets("sheet1")

        Try

            For i = 0 To 3

                If i > 1 Then

                    cRow = Convert.ToString(i + 3)

                    sheetRange = xlSheet.Range("b" + cRow, "k" + cRow)

                    sheetRange.EntireRow.EntireRow.Insert()

                End If

                With xlSheet

                    .Cells(i + 3, 2) = i + 1

                    .Cells(i + 3, 3) = aEmployee(i).Name

                    .Cells(i + 3, 4) = aEmployee(i).GajiPokok

                    .Cells(i + 3, 5) = aEmployee(i).TTransport

                    .Cells(i + 3, 6) = aEmployee(i).TJabatan

                    .Cells(i + 3, 7) = aEmployee(i).GajiTotal

                    .Cells(i + 3, 8) = aEmployee(i).AsuransiA

                    .Cells(i + 3, 9) = aEmployee(i).AsuransiB

                    .Cells(i + 3, 10) = aEmployee(i).Pinjaman

                    .Cells(i + 3, 11) = aEmployee(i).TakeHomePay

                    If i = 3 Then

                        cRow = Convert.ToString(i + 3)

                        .Cells(i + 4, 3) = "GRAND TOTAL"

                        .Cells(i + 4, 4) = "=Sum(D3:D" + cRow + ")"

                        .Cells(i + 4, 5) = "=Sum(E3:E" + cRow + ")"

                        .Cells(i + 4, 6) = "=Sum(F3:F" + cRow + ")"

                        .Cells(i + 4, 7) = "=Sum(G3:G" + cRow + ")"

                        .Cells(i + 4, 8) = "=Sum(H3:H" + cRow + ")"

                        .Cells(i + 4, 9) = "=Sum(I3:I" + cRow + ")"

                        .Cells(i + 4, 10) = "=Sum(J3:J" + cRow + ")"

                        .Cells(i + 4, 11) = "=Sum(K3:K" + cRow + ")"

                    End If

                End With

            Next

            cRow = Convert.ToString(i + 3)

            sheetRange = xlSheet.Range("D3", "k" + cRow)

            sheetRange.NumberFormat = "#,##0_);[Red](#,##0)"

        Catch ex As Exception

           

        End Try

        xlWBook.Save()

        xlWBook.Close()

        xlApp.Quit()

        DeleteObject(xlApp)

        DeleteObject(xlWBook)

        DeleteObject(xlSheet)

    End Sub

    Private Sub DeleteObject(ByVal obj As Object)

        Try

            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)

            obj = Nothing

        Catch ex As Exception

            obj = Nothing

        Finally

            GC.Collect()

        End Try

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        End

    End Sub

End Class

Public Class clsEmployee

    Public Property Name As String = ""

    Public Property GajiPokok As Double = 0

    Public Property TTransport As Double = 0

    Public Property TJabatan As Double = 0

    Public Property GajiTotal As String = ""

    Public Property AsuransiA As String = ""

    Public Property AsuransiB As String = ""

    Public Property Pinjaman As Double = 0

    Public Property TakeHomePay As String = ""

End Class

Tampilan File Excel sebelum diupdate melalui VB 2010

Tampilan File Excel setelah diupdate melalui VB 2010

Semoga bermanfaat!

Posted in: Programing