How to connect VB in Excel

หัวข้อ: คอมพิวเตอร์/เกมส์ และ Internet->Programming->VB/VB.net

เรื่อง: การออกรายงายด้วย Excel ด้วย "Vb.net"

'ทำการ อิมพอร์ท เทรด เทรดเข้ามาด้วยนะครับ ก่อนจะทำได้ต้องอิมพรอืฒริฟเฟอรเร้นของโปรเจค คือ

'Microsoft Excell 11.0 Object Libery 1.5 เข้ามาก่อนนะครับถึงจะอ้าง Excel ได้ครับ

Imports System.Globalization

Imports System.Threading.Thread

Dim excelapp As New Excel.Application

Dim excelbooks As Excel.Workbook

Dim excelsheets As Excel.Worksheet

‘ในกรณีของออฟฟิต 2003 ภาษาไทยไม่ต้องเปลี่ยนภาษาก็ได้ครับแต่เปลี่ยนไว้ก้อดี

Dim curentthread As System.Threading.Thread

curentthread = System.Threading.Thread.CurrentThread

curentthread.CurrentCulture = New CultureInfo("en-US")

excelapp.Visible = True

excelbooks = excelapp.Workbooks.Add

excelsheets = CType(excelbooks.Worksheets(1), Excel.Worksheet)

With excelsheets

'กำหนดขนาดคอลัม

.Range("A1:K35").Font.Name = "Tahoma"

.Range("A:A").ColumnWidth = 1.14

.Range("B:B").ColumnWidth = 5

.Range("C:C").ColumnWidth = 3.5

.Range("D:D").ColumnWidth = 40.57

.Range("E:E").ColumnWidth = 3.57

.Range("F:F").ColumnWidth = 2.14

.Range("G:G").ColumnWidth = 2.29

.Range("H:H").ColumnWidth = 2.86

.Range("I:I").ColumnWidth = 4.86

.Range("J:J").ColumnWidth = 4.57

.Range("K:K").ColumnWidth = 9.57

' กำหนดขนาดความสูงของแถว

.Range("1:1").RowHeight = 22.5

.Range("2:4").RowHeight = 18

.Range("5:5").RowHeight = 37.5

.Range("6:6").RowHeight = 24.75

.Range("7:8").RowHeight = 18

.Range("9:9").RowHeight = 20.25

.Range("10:10").RowHeight = 27.75

.Range("11:21").RowHeight = 24

.Range("22:22").RowHeight = 30.75

.Range("23:24").RowHeight = 18

.Range("25:25").RowHeight = 24

.Range("26:26").RowHeight = 27.75

'กำหนด ฟรอนของแต่ละ แถว

With .Range("A1")

.Font.Bold = True

.Font.Underline = True

.Value = "บริษัท เฮงรุ่งเรือง จำกัด"

.Font.Size = 14

End With

With .Range("d6")

.Font.Bold = True

.Font.Size = 12

End With

With .Range("E2")

.Font.Bold = True

.Value = "ใบเสร็จรับเงิน / ใบกำกับภาษี"

.Font.Size = 12

End With

With .Range("E3")

.FormulaR1C1 = "เลขประจำตัวผู้เสียภาษีอากร 3011924426" 'กำหนดว่า จะให้มีตัวหนาตั้งแต่ตัวไหน นับตามจำนวนอักขระ

With .Characters(Start:=26, Length:=32).Font

.FontStyle = "ตัวหนา"

.Size = 10

End With

.Font.Size = 9

End With

With .Range("E4")

.Font.Bold = True

.Value = "บริการ เคาะ พ่นสี ตัวถังรถยนต์ทุกชนิด"

.Font.Size = 10

End With

'กำหนดข้อมูลใน ช่องต่าง ๆ

.Range("a6").Value = "รหัสลูกค้า"

.Range("a7").Value = "นาม"

.Range("a8").Value = "ที่อยู่"

.Range("i7").Value = "วันที่"

.Range("i8").Value = "เลขที่"

'ทำการผสานเซลและจัดกึ่งกลางในช่อง 10 - 17

Dim i As Integer

For i = 10 To 17

'ผสานช่อง aช่องที่ i ถึง b ช่องที่ i

.Range("A" & i.ToString & ":B" & i.ToString).Merge()

'จัดกึ่งกลาง

.Range("A" & i.ToString & ":B" & i.ToString).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

.Range("c" & i.ToString & ":d" & i.ToString).Merge()

.Range("c" & i.ToString & ":d" & i.ToString).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

.Range("e" & i.ToString & ":f" & i.ToString).Merge()

.Range("e" & i.ToString & ":f" & i.ToString).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

.Range("g" & i.ToString & ":i" & i.ToString).Merge()

.Range("g" & i.ToString & ":i" & i.ToString).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight

.Range("j" & i.ToString & ":k" & i.ToString).Merge()

.Range("j" & i.ToString & ":k" & i.ToString).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight

.Range("a10:b10").Value = "ลำดับที่"

.Range("c10:d10").Value = "รายการ"

.Range("e10:f10").Value = "จำนวน"

.Range("g10:i10").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

.Range("g10:i10").Value = "ราคาต่อหน่วย"

.Range("j10:k10").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

.Range("j10:k10").Value = "จำนวนเงิน"

.Range("a18").Value = "ยี่ห้อ / ทะเบียน"

.Range("A19").Value = "กรมธรรม์"

.Range("a20").Value = "เลขเคลม"

For i = 18 To 21

.Range("e" & i.ToString & ":i" & i.ToString).Merge()

.Range("e" & i.ToString & ":i" & i.ToString).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

.Range("j" & i.ToString & ":k" & i.ToString).Merge()

.Range("j" & i.ToString & ":k" & i.ToString).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight

.Range("a21:d21").Merge()

.Range("e18").Value = "ส่วนลด"

.Range("e19").Value = "รวมจำนวนเงิน"

.Range("e20").Value = "ภาษีมูลค่าเพิ่ม 7 %"

.Range("e21").Value = "รวมจำนวนเงินทั้งสิ้น"

.Range("d24").Value = " ผู้รับเงิน"

.Range("h24").Value = "ผู้รับมอบอำนาจ"

.Range("c26").Value = "ลงชื่อ…………………………………………………....."

.Range("e26").Value = "ลงชื่อ...…………………………………………………"

.Range("d29").Value = "วันที่…………/……………/………….."

.Range("f29").Value = "วันที่…………/……………/………….."

'จัดรูปแบบ ของข้อความในช่องที่ต้องการให้เป็น Format ตัวเลขที่เป็นเงิน

.Range("G11:K17").NumberFormat = "#,##0.00;[Red]#,##0.00"

.Range("j18:k20").NumberFormat = "#,##0.00;[Red]#,##0.00"

' ตีตาราง ในส่วนนี้ ใช้ vba ของexcel แล้วนำโค๊ด ในโมดูลที่บันทึกเอามาใช้แล้วเปลี่ยนแปลงนิดหน่อย

'ไม่ยากครับ ส่วนมากจะเป็นการกำหนด เส้นบนเส้นล่างให้มีเส้นภายไหน ไหมอะไรทำนองนี้ครับ

With .Range("A10:K21")

.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.XlLineStyle.xlLineStyleNone

.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.XlLineStyle.xlLineStyleNone

With .Borders(Excel.XlBordersIndex.xlEdgeLeft)

.LineStyle = Excel.XlLineStyle.xlContinuous

.Weight = Excel.XlBorderWeight.xlThin

.ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic

End With

With .Borders(Excel.XlBordersIndex.xlEdgeTop)

.LineStyle = Excel.XlLineStyle.xlContinuous

.Weight = Excel.XlBorderWeight.xlThin

.ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic

End With

With .Borders(Excel.XlBordersIndex.xlEdgeBottom)

.LineStyle = Excel.XlLineStyle.xlContinuous

.Weight = Excel.XlBorderWeight.xlThin

.ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic

End With

With .Borders(Excel.XlBordersIndex.xlEdgeRight)

.LineStyle = Excel.XlLineStyle.xlContinuous

.Weight = Excel.XlBorderWeight.xlThin

.ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic

End With

.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.XlLineStyle.xlLineStyleNone

.Borders(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.XlLineStyle.xlLineStyleNone

End With

With .Range("J20:K20,E20:K20,A20:D20")

.Range("A20").Activate()

.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.XlLineStyle.xlLineStyleNone

.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.XlLineStyle.xlLineStyleNone

.Borders(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlLineStyleNone

With .Borders(Excel.XlBordersIndex.xlEdgeBottom)

.LineStyle = Excel.XlLineStyle.xlContinuous

.Weight = Excel.XlBorderWeight.xlThin

.ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic

End With

.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.XlLineStyle.xlLineStyleNone

End With

With .Range("J17:K17,A17:K17")

.Range("A17").Activate()

.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.XlLineStyle.xlLineStyleNone

.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.XlLineStyle.xlLineStyleNone

.Borders(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlLineStyleNone

With .Borders(Excel.XlBordersIndex.xlEdgeBottom)

.LineStyle = Excel.XlLineStyle.xlContinuous

.Weight = Excel.XlBorderWeight.xlThin

.ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic

End With

With .Borders(Excel.XlBordersIndex.xlEdgeRight)

.LineStyle = Excel.XlLineStyle.xlContinuous

.Weight = Excel.XlBorderWeight.xlThin

.ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic

End With

.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.XlLineStyle.xlLineStyleNone

End With

With .Range("J10:K10,A10:K10")

.Range("A10").Activate()

.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.XlLineStyle.xlLineStyleNone

.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.XlLineStyle.xlLineStyleNone

With .Borders(Excel.XlBordersIndex.xlEdgeLeft)

.LineStyle = Excel.XlLineStyle.xlContinuous

.Weight = Excel.XlBorderWeight.xlThin

.ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic

End With

With .Borders(Excel.XlBordersIndex.xlEdgeTop)

.LineStyle = Excel.XlLineStyle.xlContinuous

.Weight = Excel.XlBorderWeight.xlThin

.ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic

End With

With .Borders(Excel.XlBordersIndex.xlEdgeBottom)

.LineStyle = Excel.XlLineStyle.xlContinuous

.Weight = Excel.XlBorderWeight.xlThin

.ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic

End With

With .Borders(Excel.XlBordersIndex.xlEdgeRight)

.LineStyle = Excel.XlLineStyle.xlContinuous

.Weight = Excel.XlBorderWeight.xlThin

.ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic

End With

.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.XlLineStyle.xlLineStyleNone

End With

With .Range("A10:B17")

.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.XlLineStyle.xlLineStyleNone

.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.XlLineStyle.xlLineStyleNone

With .Borders(Excel.XlBordersIndex.xlEdgeLeft)

.LineStyle = Excel.XlLineStyle.xlContinuous

.Weight = Excel.XlBorderWeight.xlThin

.ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic

End With

With .Borders(Excel.XlBordersIndex.xlEdgeTop)

.LineStyle = Excel.XlLineStyle.xlContinuous

.Weight = Excel.XlBorderWeight.xlThin

.ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic

End With

With .Borders(Excel.XlBordersIndex.xlEdgeBottom)

.LineStyle = Excel.XlLineStyle.xlContinuous

.Weight = Excel.XlBorderWeight.xlThin

.ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic

End With

With .Borders(Excel.XlBordersIndex.xlEdgeRight)

.LineStyle = Excel.XlLineStyle.xlContinuous

.Weight = Excel.XlBorderWeight.xlThin

.ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic

End With

.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.XlLineStyle.xlLineStyleNone

End With

With .Range("E10:I21")

.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.XlLineStyle.xlLineStyleNone

.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.XlLineStyle.xlLineStyleNone

With .Borders(Excel.XlBordersIndex.xlEdgeLeft)

.LineStyle = Excel.XlLineStyle.xlContinuous

.Weight = Excel.XlBorderWeight.xlThin

.ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic

End With

With .Borders(Excel.XlBordersIndex.xlEdgeTop)

.LineStyle = Excel.XlLineStyle.xlContinuous

.Weight = Excel.XlBorderWeight.xlThin

.ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic

End With

With .Borders(Excel.XlBordersIndex.xlEdgeBottom)

.LineStyle = Excel.XlLineStyle.xlContinuous

.Weight = Excel.XlBorderWeight.xlThin

.ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic

End With

With .Borders(Excel.XlBordersIndex.xlEdgeRight)

.LineStyle = Excel.XlLineStyle.xlContinuous

.Weight = Excel.XlBorderWeight.xlThin

.ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic

End With

.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.XlLineStyle.xlLineStyleNone

End With

With .Range("E10:F17")

.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.XlLineStyle.xlLineStyleNone

.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.XlLineStyle.xlLineStyleNone

With .Borders(Excel.XlBordersIndex.xlEdgeLeft)

.LineStyle = Excel.XlLineStyle.xlContinuous

.Weight = Excel.XlBorderWeight.xlThin

.ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic

End With

With .Borders(Excel.XlBordersIndex.xlEdgeTop)

.LineStyle = Excel.XlLineStyle.xlContinuous

.Weight = Excel.XlBorderWeight.xlThin

.ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic

End With

With .Borders(Excel.XlBordersIndex.xlEdgeBottom)

.LineStyle = Excel.XlLineStyle.xlContinuous

.Weight = Excel.XlBorderWeight.xlThin

.ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic

End With

With .Borders(Excel.XlBordersIndex.xlEdgeRight)

.LineStyle = Excel.XlLineStyle.xlContinuous

.Weight = Excel.XlBorderWeight.xlThin

.ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic

End With

.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.XlLineStyle.xlLineStyleNone

End With

ความเห็น

บทความในวันเดียวกัน