Tips Excel connection dan vb.net bagian ke 1

Tips VB.net dan excel connection Bagian Pertama

Setting agar VB.Net bisa terkoneksi dengan excel

Pada project "Add reference" , masuk tabulasi "COM" lalu pilih Microsoft Excel Object Library

Masukan kode berikut sebelum Public (paling atas)

---

Imports Excel = Microsoft.Office.Interop.Excel

---

Membuat file excel baru

Berikut contohnya, dipasang pada button 1

---

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

'~~> mendefine object excel

Dim xlApp As New Excel.Application

Dim xlWorkBook As Excel.Workbook

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

'~~> membuat WorkBook (file excel) baru

xlWorkBook = xlApp.Workbooks.Add

'~~> memumculkan file excel 

xlApp.Visible = True

End Sub

End Class

---

Membuka file excel

Berikut contohnya, dipasang pada button2

---

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

    Dim xlApp As New Excel.Application

    Dim xlWorkBook As Excel.Workbook

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

'~~> membuka file excel, ganti path dan nama file nya sesuai kebutuhan

xlWorkBook = xlApp.Workbooks.Open("C:\vb dan excel\percobaan.xlsx")

xlApp.Visible = True

End Sub

End Class

---

Memasukan value pada file excel

Berikut contohnya, dipasang pada button1 membuat file baru dulu dan di insert

---

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

    Dim xlApp As New Excel.Application

    Dim xlWorkBook As Excel.Workbook

'~~> define untuk sheet excel nya

    Dim xlWorkSheet As Excel.Worksheet

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

xlWorkBook = xlApp.Workbooks.Add

xlApp.Visible = True

'~~> menghubungkan pada sheet file excelnya

xlWorkSheet = xlWorkBook.Sheets("Sheet1")

With xlWorkSheet

'~~> contoh langsung memasukan nilainya ke field

   .Range("A1").Value = "Hari"

   .Range("A2").Value = "Senin"

   .Range("A3").Value = "Selasa"

   .Range("A4").Value = "Rabu"

   .Range("A5").Value = "Kamis"

   .Range("B1").Value = "Pengeluaran"

   .Range("B2").Value = "1000"

   .Range("B3").Value = "1500"

   .Range("B4").Value = "1200"

   .Range("B5").Value = "2500"

End With

End Sub

End Class

---

Memasukan formula (rumus)

Berikut contohnya, dipasang pada button1 membuat file baru dulu dan di insert

Dicontohkan memasukan formula untuk summary

---

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

xlWorkBook = xlApp.Workbooks.Add

xlApp.Visible = True

xlWorkSheet = xlWorkBook.Sheets("Sheet1")

With xlWorkSheet

.Range("A1").Value = "Hari"

.Range("A2").Value = "Senin"

.Range("A3").Value = "Selasa"

.Range("A4").Value = "Rabu"

.Range("A5").Value = "Kamis"

.Range("B1").Value = "Pengeluaran"

.Range("B2").Value = "1000"

.Range("B3").Value = "1500"

.Range("B4").Value = "1200"

.Range("B5").Value = "2500"

'~~> Memasukan tulisan total pengeluaran

.Range("A6").Value = "Total Pengeluaran"

'~~> memasukan formula

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

End With

End Sub

---

Mengatur format text

Berikut contohnya, dipasang pada button1 membuat file baru dulu dan di insert

Dicontohkan diatur agar autofit colums, mengatur background hitam dan tulisan putih pada field nilai angka dan membuat garis tabelnya

---

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

xlWorkBook = xlApp.Workbooks.Add

xlApp.Visible = True

xlWorkSheet = xlWorkBook.Sheets("Sheet1")

With xlWorkSheet

.Range("A1").Value = "Hari"

.Range("A2").Value = "Senin"

.Range("A3").Value = "Selasa"

.Range("A4").Value = "Rabu"

.Range("A5").Value = "Kamis"

.Range("B1").Value = "Pengeluaran"

.Range("B2").Value = "1000"

.Range("B3").Value = "1500"

.Range("B4").Value = "1200"

.Range("B5").Value = "2500"

'~~> Memasukan tulisan total pengeluaran

.Range("A6").Value = "Total Pengeluaran"

'~~> memasukan formula

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

'~~> menghitamkan bacground dan text putih

With .Range("A1:B1,A6:A7")

.Interior.ColorIndex = 1 '<~~ Cell Back Color menjadi hitam

With .Font

.ColorIndex = 2 '<~~ Font Color menjadi putih

.Size = 8

.Name = "Tahoma"

.Underline = Excel.XlUnderlineStyle.xlUnderlineStyleSingle

.Bold = True

End With

End With

'~~> membuat garis tabel

With .Range("A1:B7")

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

'~~> auto fit tulisan pada kolom

.Columns("A:B").EntireColumn.AutoFit()

End With

End Sub

  Sekian....Semoga bermanfaat buat teman2....