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
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
Mencari sheet yang akan digunakan
Private Sub Button2_Click(ByVal
sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim
SheetNameToCheck As String = "sheetyangdipakai"
Dim
xs As Excel.Worksheet
xlWorkBook = xlApp.Workbooks.Open("C:\vb dan excel\percobaan.xlsx")
xlApp.Visible = True
'~~> looping pencarian
For
Each xs In xlWorkBook.Sheets
If xs.Name = SheetNameToCheck Then
MessageBox.Show("sheet " &
SheetNameToCheck & " ditemukan.")
End If
Next
End Sub
Menambah / menghapus sheet
Berikut hanya syntaxnya saja, untuk validasi bisa
menggunakan looping diatas untuk pencarian nama sheet dimaksud ada atau tidak
'~~> Menambah
xlWorkSheet = xlWorkBook.Sheets.Add
xlWorkSheet = xlWorkBook.Sheets.Add
'~~> Menghapus
xlWorkSheet.Delete()
Bersambung di Part 2 heheheh
No comments:
Post a Comment