SHARE
TWEET

VB .NET : Input Data & Format Tabel MS Excel

stevennathaniel Jan 19th, 2016 77 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Imports Excel = Microsoft.Office.Interop.Excel
  2. 'Source code yg udah bagus kemampuan untuk mematikan service Excel nya
  3. 'Menggunakan framework ini aja untuk selanjutnya
  4.  
  5. Public Class Form4
  6.  
  7.     Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
  8.  
  9.         Dim appXL As Excel.Application
  10.  
  11.         Dim wbXL As Excel.Workbook
  12.  
  13.         Dim shXL As Excel.Worksheet
  14.  
  15.         Dim raXL As Excel.Range
  16.  
  17.  
  18.         appXL = CreateObject("Excel.Application")
  19.  
  20.         appXL.Visible = True
  21.  
  22.         wbXL = appXL.Workbooks.Add
  23.  
  24.         shXL = wbXL.ActiveSheet
  25.  
  26.         shXL.Cells(1, 1).Value = "Bulan"
  27.  
  28.         shXL.Cells(1, 2).Value = "Uang yg Dibelanjakan"
  29.  
  30.         shXL.Cells(6, 1).Value = "Total Pengeluaran"
  31.  
  32.         shXL.Cells(7, 1).Value = "Rata-rata Pengeluaran"
  33.  
  34.         With shXL.Range("A1", "B1")
  35.  
  36.             .Font.Bold = True
  37.  
  38.             .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
  39.  
  40.             .Font.ColorIndex = 3
  41.  
  42.             .Interior.ColorIndex = 20
  43.  
  44.  
  45.         End With
  46.  
  47.         Dim Bulan(5, 2) As String
  48.  
  49.         Dim Pengeluaran(5, 2) As Integer
  50.  
  51.         Bulan(0, 0) = "Januari"
  52.  
  53.         Bulan(1, 0) = "Februari"
  54.  
  55.         Bulan(2, 0) = "Maret"
  56.  
  57.         Bulan(3, 0) = "April"
  58.  
  59.  
  60.         Pengeluaran(0, 0) = 1000
  61.  
  62.         Pengeluaran(1, 0) = 1500
  63.  
  64.         Pengeluaran(2, 0) = 1200
  65.  
  66.         Pengeluaran(3, 0) = 1100
  67.  
  68.  
  69.         shXL.Range("A2", "A5").Value = Bulan
  70.  
  71.         shXL.Range("B2", "B5").Value = Pengeluaran
  72.  
  73.  
  74.         shXL.Range("B6").Formula = "=Sum(B2:B5)"
  75.  
  76.         shXL.Range("B7").Formula = "=Average(B2:B5)"
  77.  
  78.  
  79.         'shXL.Range("A1:B1").Interior.ColorIndex = 20
  80.  
  81.         'shXL.Range("A1:B1").Font.ColorIndex = 3
  82.  
  83.         shXL.Range("B6", "B7").Font.Bold = True
  84.  
  85.         shXL.Range("B2:B7").NumberFormat = "Rp #,###.00"
  86.  
  87.  
  88.  
  89.  
  90.  
  91.  
  92.  
  93.         'Autofit Columns A:B
  94.  
  95.         raXL = shXL.Range("A1", "B1")
  96.  
  97.         raXL.EntireColumn.AutoFit()
  98.  
  99.  
  100.         With shXL.Range("A1:B7")
  101.  
  102.             'Membuat garis samping tabel
  103.             With .Borders(Excel.XlBordersIndex.xlEdgeLeft)
  104.  
  105.                 .LineStyle = Excel.XlLineStyle.xlDouble
  106.  
  107.                 .ColorIndex = 0
  108.  
  109.                 .TintAndShade = 0
  110.  
  111.                 .Weight = Excel.XlBorderWeight.xlThin
  112.  
  113.             End With
  114.  
  115.  
  116.             With .Borders(Excel.XlBordersIndex.xlEdgeTop)
  117.  
  118.                 .LineStyle = Excel.XlLineStyle.xlContinuous
  119.  
  120.                 .ColorIndex = 0
  121.  
  122.                 .TintAndShade = 0
  123.  
  124.                 .Weight = Excel.XlBorderWeight.xlThin
  125.  
  126.             End With
  127.  
  128.  
  129.             With .Borders(Excel.XlBordersIndex.xlEdgeBottom)
  130.  
  131.                 .LineStyle = Excel.XlLineStyle.xlContinuous
  132.  
  133.                 .ColorIndex = 0
  134.  
  135.                 .TintAndShade = 0
  136.  
  137.                 .Weight = Excel.XlBorderWeight.xlThin
  138.  
  139.             End With
  140.  
  141.  
  142.             With .Borders(Excel.XlBordersIndex.xlEdgeRight)
  143.  
  144.                 .LineStyle = Excel.XlLineStyle.xlContinuous
  145.  
  146.                 .ColorIndex = 0
  147.  
  148.                 .TintAndShade = 0
  149.  
  150.                 .Weight = Excel.XlBorderWeight.xlThin
  151.  
  152.             End With
  153.  
  154.             With .Borders(Excel.XlBordersIndex.xlInsideVertical)
  155.  
  156.                 .LineStyle = Excel.XlLineStyle.xlContinuous
  157.  
  158.                 .ColorIndex = 0
  159.  
  160.                 .TintAndShade = 0
  161.  
  162.                 .Weight = Excel.XlBorderWeight.xlThin
  163.             End With
  164.  
  165.             With .Borders(Excel.XlBordersIndex.xlInsideHorizontal)
  166.  
  167.                 .LineStyle = Excel.XlLineStyle.xlContinuous
  168.  
  169.                 .ColorIndex = 0
  170.  
  171.                 .TintAndShade = 0
  172.  
  173.                 .Weight = Excel.XlBorderWeight.xlThin
  174.  
  175.             End With
  176.  
  177.         End With
  178.  
  179.         appXL.Visible = True
  180.  
  181.         appXL.UserControl = True
  182.  
  183.  
  184.         raXL = Nothing
  185.  
  186.         wbXL = Nothing
  187.  
  188.         shXL = Nothing
  189.  
  190.         appXL.Quit()
  191.  
  192.         appXL = Nothing
  193.  
  194.         Exit Sub
  195.  
  196.  
  197. Err_Handler:
  198.  
  199.         MsgBox(Err.Description, vbCritical, "Error: " & Err.Number)
  200.  
  201.  
  202.     End Sub
  203. End Class
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top