No Description
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

採購單編輯.vb 104KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168
  1. Option Strict Off
  2. Imports System.IO
  3. Imports Microsoft.Office.Interop.Excel.XlUnderlineStyle
  4. Imports Microsoft.Office.Interop.Excel.Constants
  5. Imports Microsoft.Office.Interop.Excel.XlBordersIndex
  6. Imports Microsoft.Office.Interop.Excel.XlLineStyle
  7. Imports Microsoft.Office.Interop.Excel.XlBorderWeight
  8. Imports Microsoft.Office.Interop.Excel.XlThemeFont
  9. Imports Microsoft.Office.Interop.Excel.XlThemeColor
  10. Imports Microsoft.Office.Interop.Excel.XlWindowState
  11. Imports Microsoft.Office.Interop.Excel
  12. Imports System.Windows.Forms.DataVisualization.Charting
  13. Public Class 採購單編輯
  14. ReadOnly ds8 As New DataSet
  15. Dim xlApp As Application : Dim xlBook As Workbook : Dim xlSheet As Worksheet
  16. Dim QQQ1, QQQ2, QQQ3, 新項次, 物料規格, 採購選擇, 對話框(15) As String
  17. Dim NUM1, NA1, N1, N2 As Integer
  18. Dim 可繪圖 As Boolean
  19. Private Sub Set_明細清單()
  20. Dim ds As New DataSet : 明細_dgv.DataSource = Nothing : ds.Clear()
  21. 明細_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
  22. 明細_dgv.ColumnHeadersHeight = 40
  23. 明細_dgv.AllowUserToAddRows = False
  24. If 小間距_ch.Checked = False And 中間距_ch.Checked = False And 大間距_ch.Checked = True Then
  25. 明細_dgv.RowTemplate.Height = 65
  26. ElseIf 小間距_ch.Checked = False And 中間距_ch.Checked = True And 大間距_ch.Checked = False Then
  27. 明細_dgv.RowTemplate.Height = 45
  28. ElseIf 小間距_ch.Checked = True And 中間距_ch.Checked = False And 大間距_ch.Checked = False Then
  29. 明細_dgv.RowTemplate.Height = 25
  30. End If
  31. PA20 = 採購選擇 : If PA20 = "" Then : PA20 = "B" : End If
  32. PA1 = QQQ1 : PA2 = 採購單號_tb.Text : SQL_建築物料採購單生成_明細()
  33. da.Fill(ds) : 明細_dgv.DataSource = ds.Tables(0) : conn.Close()
  34. 明細_dgv.Columns(0).Visible = False : 明細_dgv.Columns(1).FillWeight = 52 : 明細_dgv.Columns(3).FillWeight = 243 : 明細_dgv.Columns(4).FillWeight = 230
  35. 明細_dgv.Columns(5).FillWeight = 145 : 明細_dgv.Columns(6).FillWeight = 80 : 明細_dgv.Columns(7).FillWeight = 80 : 明細_dgv.Columns(8).FillWeight = 80
  36. 明細_dgv.Columns(9).FillWeight = 80 : 明細_dgv.Columns(10).Visible = False : 明細_dgv.Columns(11).Visible = False : 明細_dgv.Columns(12).Visible = False
  37. 明細_dgv.Columns(13).Visible = False : 明細_dgv.Columns(14).Visible = False : 明細_dgv.Columns(15).Visible = False : 明細_dgv.Columns(16).FillWeight = 95
  38. 明細_dgv.Columns(17).Visible = False : 明細_dgv.Columns(18).Visible = False : 明細_dgv.Columns(19).Visible = False : 明細_dgv.Columns(20).Visible = False
  39. 明細_dgv.Columns(21).Visible = False : 明細_dgv.Columns(22).Visible = False : 明細_dgv.Columns(23).Visible = False : 明細_dgv.Columns(24).Visible = False
  40. 明細_dgv.Columns(25).Visible = False : 明細_dgv.Columns(26).FillWeight = 40 : 明細_dgv.Columns("KEY").Visible = False : 明細_dgv.Columns("OUP").FillWeight = 80
  41. 明細_dgv.Columns(2).FillWeight = 86 : 明細_dgv.Columns(27).FillWeight = 80 : 明細_dgv.Columns("SP%").FillWeight = 80
  42. 明細_dgv.Columns(6).DefaultCellStyle.Format = "#,##0" : 明細_dgv.Columns(8).DefaultCellStyle.Format = "#,##0" : 明細_dgv.Columns(9).DefaultCellStyle.Format = "#,##0"
  43. 明細_dgv.Columns("OUP").DefaultCellStyle.Format = "#,##0"
  44. 明細_dgv.Columns(8).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter : 明細_dgv.Columns(6).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
  45. 明細_dgv.Columns(9).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter : 明細_dgv.Columns("OUP").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
  46. 明細_dgv.Columns(8).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter : 明細_dgv.Columns(6).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
  47. 明細_dgv.Columns(9).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter : 明細_dgv.Columns("OUP").HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
  48. 明細_dgv.EditMode = DataGridViewEditMode.EditOnEnter
  49. 明細_dgv.Columns(1).ReadOnly = True : 明細_dgv.Columns(3).ReadOnly = True : 明細_dgv.Columns(4).ReadOnly = True : 明細_dgv.Columns(16).ReadOnly = True
  50. 明細_dgv.Columns(表頭(16)).ReadOnly = True : 明細_dgv.Columns(表頭(27)).ReadOnly = True : 明細_dgv.Columns("OUP").ReadOnly = True : 明細_dgv.Columns("SP%").ReadOnly = True
  51. If 低網速模式 = False Then
  52. 明細_dgv.Columns(29).FillWeight = 80 : 明細_dgv.Columns("Image").Visible = True
  53. Dim mycol As DataGridViewImageColumn = 明細_dgv.Columns("Image")
  54. mycol.ImageLayout = DataGridViewImageCellLayout.Zoom
  55. Else
  56. If 明細_dgv.Columns.Count = 30 Then
  57. 明細_dgv.Columns("Image").Visible = False
  58. End If
  59. End If
  60. 新項次 = "00"
  61. For i As Integer = 0 To 明細_dgv.Rows.Count - 1
  62. 明細_dgv.Columns("OUP").DefaultCellStyle.Format = "#,0"
  63. NUM1 = Double.Parse(新項次) + 1
  64. If NUM1 < 10 Then : 新項次 = "0" & NUM1
  65. ElseIf NUM1 > 9 And NUM1 < 100 Then : 新項次 = NUM1
  66. End If
  67. 明細_dgv.Rows(i).Cells(表頭(11)).Value = 新項次
  68. '-------------------------規格合併呈現----------------------------------------------------------------------------------------------------------------------------------------
  69. If i = 0 Then
  70. AAA1 = "" : AAA2 = "" : AAA3 = "" : AAA4 = "" : AAA5 = "" : AAA6 = "" : AAA7 = "" : AAA8 = "" : PA3 = 明細_dgv.Rows(i).Cells("規格庫抬頭指定").Value
  71. SQL_規格庫抬頭讀取()
  72. If dr.Read() Then : AAA1 = dr("A1") : AAA2 = dr("A2") : AAA3 = dr("A3") : AAA4 = dr("A4") : AAA5 = dr("A5") : AAA6 = dr("A6") : AAA7 = dr("A7") : AAA8 = dr("A8") : End If
  73. conn.Close()
  74. Else
  75. If 明細_dgv.Rows(i).Cells("規格庫抬頭指定").Value.ToString <> 明細_dgv.Rows(i - 1).Cells("規格庫抬頭指定").Value.ToString Then
  76. AAA1 = "" : AAA2 = "" : AAA3 = "" : AAA4 = "" : AAA5 = "" : AAA6 = "" : AAA7 = "" : AAA8 = "" : PA3 = 明細_dgv.Rows(i).Cells("規格庫抬頭指定").Value : SQL_規格庫抬頭讀取()
  77. If dr.Read() Then : AAA1 = dr("A1") : AAA2 = dr("A2") : AAA3 = dr("A3") : AAA4 = dr("A4") : AAA5 = dr("A5") : AAA6 = dr("A6") : AAA7 = dr("A7") : AAA8 = dr("A8") : End If
  78. conn.Close()
  79. End If
  80. End If
  81. Dim ZA1, ZA2, ZA3, ZA4, ZA5, ZA6, ZA7, ZA8 As String : 物料規格 = ""
  82. If 詳細資料2_ch.Checked = True Then
  83. If 明細_dgv.Rows(i).Cells(表頭(13)).Value.ToString <> "" Then
  84. ZA1 = AAA1 & "=" & 明細_dgv.Rows(i).Cells(表頭(13)).Value.ToString : 物料規格 = ZA1 : End If
  85. If 明細_dgv.Rows(i).Cells("A2").Value.ToString <> "" Then : ZA2 = AAA2 & "=" & 明細_dgv.Rows(i).Cells("A2").Value
  86. If 物料規格 = "" Then : 物料規格 = ZA2 : Else : 物料規格 = 物料規格 + "、" + ZA2 : End If : End If
  87. If 明細_dgv.Rows(i).Cells("A3").Value.ToString <> "" Then : ZA3 = AAA3 & "=" & 明細_dgv.Rows(i).Cells("A3").Value
  88. If 物料規格 = "" Then : 物料規格 = ZA3 : Else : 物料規格 = 物料規格 + "、" + ZA3 : End If : End If
  89. If 明細_dgv.Rows(i).Cells("A4").Value.ToString <> "" Then : ZA4 = AAA4 & "=" & 明細_dgv.Rows(i).Cells("A4").Value
  90. If 物料規格 = "" Then : 物料規格 = ZA4 : Else : 物料規格 = 物料規格 + "、" + ZA4 : End If : End If
  91. If 明細_dgv.Rows(i).Cells("A5").Value.ToString <> "" Then : ZA5 = AAA5 & "=" & 明細_dgv.Rows(i).Cells("A5").Value
  92. If 物料規格 = "" Then : 物料規格 = ZA5 : Else : 物料規格 = 物料規格 + "、" + ZA5 : End If : End If
  93. If 明細_dgv.Rows(i).Cells("A6").Value.ToString <> "" Then : ZA6 = AAA6 & "=" & 明細_dgv.Rows(i).Cells("A6").Value
  94. If 物料規格 = "" Then : 物料規格 = ZA6 : Else : 物料規格 = 物料規格 + "、" + ZA6 : End If : End If
  95. If 明細_dgv.Rows(i).Cells("A7").Value.ToString <> "" Then : ZA7 = AAA7 & "=" & 明細_dgv.Rows(i).Cells("A7").Value
  96. If 物料規格 = "" Then : 物料規格 = ZA7 : Else : 物料規格 = 物料規格 + "、" + ZA7 : End If : End If
  97. If 明細_dgv.Rows(i).Cells("A8").Value.ToString <> "" Then : ZA8 = AAA8 & "=" & 明細_dgv.Rows(i).Cells("A8").Value
  98. If 物料規格 = "" Then : 物料規格 = ZA8 : Else : 物料規格 = 物料規格 + "、" + ZA8 : End If : End If
  99. Else
  100. If 明細_dgv.Rows(i).Cells(表頭(13)).Value.ToString <> "" Then : ZA1 = 明細_dgv.Rows(i).Cells(表頭(13)).Value.ToString : 物料規格 = ZA1 : End If
  101. If 明細_dgv.Rows(i).Cells("A2").Value.ToString <> "" Then
  102. ZA2 = 明細_dgv.Rows(i).Cells("A2").Value : If 物料規格 = "" Then : 物料規格 = ZA2 : Else : 物料規格 = 物料規格 + "、" + ZA2 : End If : End If
  103. If 明細_dgv.Rows(i).Cells("A3").Value.ToString <> "" Then
  104. ZA3 = 明細_dgv.Rows(i).Cells("A3").Value : If 物料規格 = "" Then : 物料規格 = ZA3 : Else : 物料規格 = 物料規格 + "、" + ZA3 : End If : End If
  105. If 明細_dgv.Rows(i).Cells("A4").Value.ToString <> "" Then
  106. ZA4 = 明細_dgv.Rows(i).Cells("A4").Value : If 物料規格 = "" Then : 物料規格 = ZA4 : Else : 物料規格 = 物料規格 + "、" + ZA4 : End If : End If
  107. If 明細_dgv.Rows(i).Cells("A5").Value.ToString <> "" Then
  108. ZA5 = 明細_dgv.Rows(i).Cells("A5").Value : If 物料規格 = "" Then : 物料規格 = ZA5 : Else : 物料規格 = 物料規格 + "、" + ZA5 : End If : End If
  109. If 明細_dgv.Rows(i).Cells("A6").Value.ToString <> "" Then
  110. ZA6 = 明細_dgv.Rows(i).Cells("A6").Value : If 物料規格 = "" Then : 物料規格 = ZA6 : Else : 物料規格 = 物料規格 + "、" + ZA6 : End If : End If
  111. If 明細_dgv.Rows(i).Cells("A7").Value.ToString <> "" Then
  112. ZA7 = 明細_dgv.Rows(i).Cells("A7").Value : If 物料規格 = "" Then : 物料規格 = ZA7 : Else : 物料規格 = 物料規格 + "、" + ZA7 : End If : End If
  113. If 明細_dgv.Rows(i).Cells("A8").Value.ToString <> "" Then
  114. ZA8 = 明細_dgv.Rows(i).Cells("A8").Value : If 物料規格 = "" Then : 物料規格 = ZA8 : Else : 物料規格 = 物料規格 + "、" + ZA8 : End If : End If
  115. End If
  116. 明細_dgv.Rows(i).Cells(表頭(13)).Value = 物料規格
  117. '-------------------------歷史單價----------------------------------------------------------------------------------------------------------------------------------------
  118. PA = 明細_dgv.Rows(i).Cells("料號").Value : Set_單價()
  119. If 價錢_dgv.Rows.Count = 0 Then : 明細_dgv.Rows(i).Cells("OUP").Value = 0 : Else : 明細_dgv.Rows(i).Cells("OUP").Value = 價錢_dgv.Rows(0).Cells("單價").Value : End If
  120. Next i
  121. Dim FFGG As Long = 0
  122. 總金額_tb.Text = "0"
  123. If 採購選擇 = "A" Then
  124. For i As Integer = 0 To 明細_dgv.Rows.Count - 1
  125. 明細_dgv.Rows(i).Cells(表頭(26)).Value = "0.0"
  126. 明細_dgv.Rows(i).Cells(表頭(27)).Value = 明細_dgv.Rows(i).Cells(表頭(26)).Value * 明細_dgv.Rows(i).Cells(表頭(15)).Value
  127. FFGG = CLng(FFGG) + 明細_dgv.Rows(i).Cells(表頭(27)).Value : 明細_dgv.Rows(i).Cells("" & 表頭(28) & "").Value = False
  128. Next i
  129. ElseIf 採購選擇 = "B" Then
  130. For i As Integer = 0 To 明細_dgv.Rows.Count - 1
  131. 明細_dgv.Rows(i).Cells(表頭(27)).Value = 明細_dgv.Rows(i).Cells(表頭(26)).Value * 明細_dgv.Rows(i).Cells(表頭(15)).Value
  132. FFGG = CLng(FFGG) + 明細_dgv.Rows(i).Cells(表頭(27)).Value : 明細_dgv.Rows(i).Cells("" & 表頭(28) & "").Value = True
  133. Next i
  134. End If
  135. 折扣金額_tb.Text = CLng(折扣比_tb.Text) * FFGG / 100
  136. If 稅金計算_ch.Checked = True Then
  137. If 折扣比_tb.Text = "0" Then : 稅金金額_tb.Text = FFGG * CLng(稅金比_tb.Text) / 100 : Else : 稅金金額_tb.Text = (FFGG - 折扣金額_tb.Text) * CLng(稅金比_tb.Text) / 100 : End If
  138. Else : 稅金金額_tb.Text = "0" : End If
  139. 總金額_tb.Text = FFGG - CLng(折扣金額_tb.Text) + CLng(稅金金額_tb.Text)
  140. 折扣金額_tb.Text = Strings.Format(CLng(折扣金額_tb.Text), "#,##0") : 稅金金額_tb.Text = Strings.Format(CLng(稅金金額_tb.Text), "#,##0")
  141. 總金額_tb.Text = Strings.Format(CLng(總金額_tb.Text), "#,##0")
  142. For i As Integer = 0 To 明細_dgv.Rows.Count - 1
  143. If 明細_dgv.Rows(i).Cells("" & 表頭(28) & "").Value = True Then
  144. 明細_dgv.Rows(i).Cells("" & 表頭(28) & "").Value = False
  145. End If
  146. For II As Integer = 0 To 最新單價_dgv.Rows.Count - 1
  147. If 明細_dgv.Rows(i).Cells("料號").Value.ToString = 最新單價_dgv.Rows(II).Cells("料號").Value.ToString And CLng(明細_dgv.Rows(i).Cells(表頭(26)).Value.ToString) = 0 Then
  148. 明細_dgv.Rows(i).Cells(表頭(26)).Value = 最新單價_dgv.Rows(II).Cells("單價").Value.ToString : Exit For
  149. End If
  150. Next
  151. Next : 價格()
  152. End Sub
  153. Private Sub Set_清單1()
  154. Dim ds1 As New DataSet : 請購單_dgv.DataSource = Nothing : ds1.Clear()
  155. 請購單_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
  156. 請購單_dgv.ColumnHeadersHeight = 25
  157. 請購單_dgv.AllowUserToAddRows = False
  158. SQL_建築物料採購單生成_請購()
  159. da.Fill(ds1) : 請購單_dgv.DataSource = ds1.Tables(0) : conn.Close()
  160. 請購單_dgv.Columns(0).FillWeight = 125 : 請購單_dgv.Columns(1).Visible = False : 請購單_dgv.Columns(2).Visible = False : 請購單_dgv.Columns(3).Visible = False
  161. 請購單_dgv.Columns(4).Visible = False : 請購單_dgv.Columns(5).Visible = False : 請購單_dgv.Columns(6).Visible = False : 請購單_dgv.Columns(7).Visible = False
  162. 請購單_dgv.Columns(8).Visible = False : 請購單_dgv.Columns(9).Visible = False
  163. End Sub
  164. Private Sub Set_清單2()
  165. Dim ds2 As New DataSet : 採購單_dgv.DataSource = Nothing : ds2.Clear()
  166. 採購單_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
  167. 採購單_dgv.ColumnHeadersHeight = 25
  168. 採購單_dgv.AllowUserToAddRows = False
  169. If 未指定_ch.Checked = True Then
  170. SQL2 = " AND (採購單控制表.供應商編號 LIKE '' OR 採購單控制表.供應商編號 IS NULL) "
  171. Else
  172. SQL2 = " AND (採購單控制表.供應商編號 NOT LIKE '') AND (採購單控制表.供應商編號 IS NOT NULL) "
  173. End If : SQL_建築物料採購單生成_採購()
  174. da.Fill(ds2) : 採購單_dgv.DataSource = ds2.Tables(0) : conn.Close()
  175. For i As Integer = 0 To 18 : 採購單_dgv.Columns(i).Visible = False : Next
  176. 採購單_dgv.Columns(17).Visible = True
  177. For i As Integer = 0 To 採購單_dgv.Rows.Count - 1
  178. If 採購單_dgv.Rows(i).Cells("合約號").Value.ToString <> "" Then
  179. 採購單_dgv.Rows(i).Cells("NO.").Value = Strings.Mid(採購單_dgv.Rows(i).Cells(0).Value.ToString, 9, 6) & " _ " & 採購單_dgv.Rows(i).Cells("合約號").Value.ToString.ToUpper()
  180. Else
  181. 採購單_dgv.Rows(i).Cells("NO.").Value = 採購單_dgv.Rows(i).Cells(0).Value.ToString
  182. End If
  183. Next
  184. End Sub
  185. Private Sub Set_清單3()
  186. Dim ds1 As New DataSet : 最新單價_dgv.DataSource = Nothing : ds1.Clear()
  187. 最新單價_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
  188. 最新單價_dgv.ColumnHeadersHeight = 25
  189. 最新單價_dgv.AllowUserToAddRows = False
  190. PA14 = 供應商編碼_tb.Text : SQL_物料最新單價查詢()
  191. da.Fill(ds1) : 最新單價_dgv.DataSource = ds1.Tables(0) : conn.Close()
  192. For i As Integer = 0 To 明細_dgv.Rows.Count - 1
  193. For II As Integer = 0 To 最新單價_dgv.Rows.Count - 1
  194. If 明細_dgv.Rows(i).Cells("料號").Value.ToString = 最新單價_dgv.Rows(II).Cells("料號").Value.ToString And CLng(明細_dgv.Rows(i).Cells(表頭(26)).Value.ToString) = 0 Then
  195. 明細_dgv.Rows(i).Cells(表頭(26)).Value = 最新單價_dgv.Rows(II).Cells("單價").Value.ToString : Exit For
  196. End If
  197. Next
  198. Next
  199. End Sub
  200. Private Sub 總金額計算()
  201. Dim FFGG As Long = 0
  202. 總金額_tb.Text = "0"
  203. For i As Integer = 0 To 明細_dgv.Rows.Count - 1
  204. 明細_dgv.Rows(i).Cells(表頭(27)).Value = 明細_dgv.Rows(i).Cells(表頭(26)).Value * 明細_dgv.Rows(i).Cells(表頭(15)).Value
  205. If 明細_dgv.Rows(i).Cells(表頭(27)).Value = "0" Then
  206. 明細_dgv.Rows(i).Cells("" & 表頭(28) & "").Value = False
  207. Else
  208. 明細_dgv.Rows(i).Cells("" & 表頭(28) & "").Value = True
  209. End If
  210. FFGG = CLng(FFGG) + 明細_dgv.Rows(i).Cells(表頭(27)).Value
  211. If 明細_dgv(表頭(28), i).Value = True Then
  212. 明細_dgv.Rows(i).DefaultCellStyle.BackColor = Color.LightGreen
  213. Else
  214. 明細_dgv.Rows(i).DefaultCellStyle.BackColor = Color.White
  215. End If
  216. Next i
  217. If 折扣比_tb.Text = "" Then : 折扣比_tb.Text = "0" : End If
  218. 折扣金額_tb.Text = CLng(折扣比_tb.Text) * FFGG / 100
  219. If 稅金計算_ch.Checked = True Then
  220. If 折扣比_tb.Text = "0" Then
  221. 稅金金額_tb.Text = FFGG * CLng(稅金比_tb.Text) / 100
  222. Else
  223. 稅金金額_tb.Text = (FFGG - 折扣金額_tb.Text) * CLng(稅金比_tb.Text) / 100
  224. End If
  225. Else
  226. 稅金金額_tb.Text = "0"
  227. End If
  228. 總金額_tb.Text = FFGG - CLng(折扣金額_tb.Text) + CLng(稅金金額_tb.Text)
  229. 折扣金額_tb.Text = Strings.Format(CLng(折扣金額_tb.Text), "#,##0") : 稅金金額_tb.Text = Strings.Format(CLng(稅金金額_tb.Text), "#,##0")
  230. 總金額_tb.Text = Strings.Format(CLng(總金額_tb.Text), "#,##0")
  231. End Sub
  232. Private Sub Set_單價()
  233. Dim ds6 As New DataSet : 價錢_dgv.DataSource = Nothing : ds6.Clear()
  234. 價錢_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
  235. 價錢_dgv.ColumnHeadersHeight = 25
  236. 價錢_dgv.AllowUserToAddRows = False
  237. If 日期_rb.Checked = False And 單價_rb.Checked = True Then
  238. SQL_加權平均()
  239. ElseIf 日期_rb.Checked = True And 單價_rb.Checked = False Then
  240. SQL_最近日期()
  241. End If
  242. da.Fill(ds6) : 價錢_dgv.DataSource = ds6.Tables(0) : conn.Close()
  243. End Sub
  244. Private Sub Set_料號比價()
  245. Dim ds1 As New DataSet
  246. 料號_dgv.DataSource = Nothing : ds1.Clear()
  247. 料號_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
  248. 料號_dgv.ColumnHeadersHeight = 25
  249. 料號_dgv.AllowUserToAddRows = False
  250. PA = 料號1_tb.Text : SQL_料號比對價格()
  251. da.Fill(ds1) : 料號_dgv.DataSource = ds1.Tables(0) : conn.Close()
  252. 料號_dgv.Columns("排序").Visible = False : 料號_dgv.Columns(3).DefaultCellStyle.Format = "#,##0.00" : 料號_dgv.Columns(3).FillWeight = 50
  253. End Sub
  254. Private Sub 語言轉換讀取()
  255. If 系統語言 = "繁體中文" Then : 語言 = "CH" : ElseIf 系統語言 = "English" Then : 語言 = "EN" : ElseIf 系統語言 = "Indonesian" Then : 語言 = "IN" : End If
  256. 核准請購未生成_lb.Text = 系統語言字典("H205-100-" & 語言) : 文字_lb.Text = 系統語言字典("H205-101-" & 語言) : 日期_rb.Text = 系統語言字典("H205-102-" & 語言)
  257. 全部取消_bt.Text = 系統語言字典("H205-103-" & 語言) : 全部選擇_bt.Text = 系統語言字典("H205-104-" & 語言) : 未指定_ch.Text = 系統語言字典("H205-138-" & 語言)
  258. 核准請購未生成_lb.Text = 系統語言字典("H205-106-" & 語言) : 表頭(28) = 系統語言字典("H205-107-" & 語言) : 表頭(19) = 系統語言字典("H205-108-" & 語言)
  259. 已指定_ch.Text = 系統語言字典("H205-139-" & 語言)
  260. 表頭(134) = 系統語言字典("H205-109-" & 語言) : 表頭(27) = 系統語言字典("H205-110-" & 語言) : 表頭(12) = 系統語言字典("H205-111-" & 語言)
  261. 表頭(24) = 系統語言字典("H205-112-" & 語言) : 表頭(13) = 系統語言字典("H205-113-" & 語言) : 表頭(14) = 系統語言字典("H205-114-" & 語言)
  262. 表頭(16) = 系統語言字典("H205-115-" & 語言) : 表頭(18) = 系統語言字典("H205-116-" & 語言) : 表頭(26) = 系統語言字典("H205-117-" & 語言)
  263. 表頭(11) = 系統語言字典("H205-118-" & 語言) : 表頭(15) = 系統語言字典("H205-119-" & 語言) : 單價_rb.Text = 系統語言字典("H205-120-" & 語言)
  264. 對話框(0) = 系統語言字典("H205-121-" & 語言) : 對話框(9) = 系統語言字典("H205-122-" & 語言) : 對話框(10) = 系統語言字典("H205-123-" & 語言)
  265. 對話框(1) = 系統語言字典("H205-124-" & 語言) : 對話框(2) = 系統語言字典("H205-125-" & 語言) : 對話框(3) = 系統語言字典("H205-126-" & 語言)
  266. 對話框(4) = 系統語言字典("H205-127-" & 語言) : 對話框(5) = 系統語言字典("H205-128-" & 語言) : 對話框(6) = 系統語言字典("H205-129-" & 語言)
  267. 對話框(7) = 系統語言字典("H205-130-" & 語言) : 對話框(8) = 系統語言字典("H205-131-" & 語言) : 選擇公司_lb.Text = 系統語言字典("H205-132-" & 語言)
  268. 總金額_lb.Text = 系統語言字典("H205-133-" & 語言) : 簡稱_lb.Text = 系統語言字典("H205-134-" & 語言) : ToolTip1.ToolTipTitle = 系統語言字典("G000-108-" & 語言)
  269. Me.Text = 系統語言字典("H001-171-" & 語言) : 對話框(15) = 系統語言字典("H205-135-" & 語言) : 對話框(11) = 系統語言字典("H205-136-" & 語言)
  270. 對話框(12) = 系統語言字典("G000-110-" & 語言) : 對話框(13) = 系統語言字典("G000-114-" & 語言) : 對話框(14) = 系統語言字典("H205-137-" & 語言)
  271. End Sub
  272. Private Sub 清空供應商_bt_MouseEnter(sender As Object, e As EventArgs) Handles 清空供應商_bt.MouseEnter
  273. ToolTip1.SetToolTip(Me.清空供應商_bt, 對話框(11))
  274. End Sub
  275. Private Sub 生成採購單_bt_MouseEnter(sender As Object, e As EventArgs) Handles 生成採購單_bt.MouseEnter
  276. ToolTip1.SetToolTip(Me.生成採購單_bt, 對話框(12))
  277. End Sub
  278. Private Sub 列印成詢價單_bt_MouseEnter(sender As Object, e As EventArgs) Handles 列印成詢價單_bt.MouseEnter
  279. ToolTip1.SetToolTip(Me.列印成詢價單_bt, 對話框(13))
  280. End Sub
  281. Private Sub 料號_bt_MouseEnter(sender As Object, e As EventArgs) Handles 分析工具_bt.MouseEnter
  282. ToolTip1.SetToolTip(Me.分析工具_bt, 對話框(14))
  283. End Sub
  284. Private Sub 類別管理_cb_下拉表單資料載入()
  285. SQL_供應商類別_下拉清單() : 類別管理_cb.Items.Clear() : 類別管理_cb.Items.Add("")
  286. While (dr.Read()) : 類別管理_cb.Items.Add(dr("類別")) : End While : conn.Close()
  287. End Sub
  288. Private Sub 廠商_cb_下拉表單資料載入()
  289. If 公司類別_cb.Text = "PT" Then : SQL2 = " AND 公司名 LIKE N'PT%'"
  290. ElseIf 公司類別_cb.Text = "CV" Then : SQL2 = " AND 公司名 LIKE N'CV%'"
  291. ElseIf 公司類別_cb.Text = "UD" Then : SQL2 = " AND 公司名 LIKE N'UD%'"
  292. ElseIf 公司類別_cb.Text = "NA" Or 公司類別_cb.Text = "" Then : SQL2 = " AND 公司名 NOT LIKE N'CV%' AND 公司名 NOT LIKE N'PT%' AND 公司名 NOT LIKE N'UD%' " : End If
  293. SQL_供應商資料表_公司名_下拉用() : 廠商_cb.Items.Clear() : 廠商_cb.Items.Add("") : While (dr.Read()) : 廠商_cb.Items.Add(dr("公司名")) : End While : conn.Close() : SQL2 = ""
  294. End Sub
  295. Private Sub 公司類別_cb_SelectedIndexChanged(sender As Object, e As EventArgs) Handles 公司類別_cb.SelectedIndexChanged
  296. 廠商_cb_下拉表單資料載入()
  297. End Sub
  298. Private Sub 廠商清空()
  299. 供應商編碼_tb.Text = "" : 地址_tb.Text = "" : 電話_tb.Text = "" : 郵件_tb.Text = "" : 傳真_tb.Text = "" : 類別管理_cb_下拉表單資料載入() : 廠商_cb_下拉表單資料載入()
  300. End Sub
  301. Private Sub 採購單編輯_Load(sender As Object, e As EventArgs) Handles MyBase.Load
  302. Me.MdiParent = HX_PGS_ERP_SYS : Me.WindowState = 2 : Me.AutoScroll = True
  303. 風格() : H(205) = True : 介面 = "H205" : 語言轉換讀取() : 顯示說明(999, True)
  304. 單價_rb.Checked = True : Panel3.SendToBack()
  305. SQL_清單欄位查詢()
  306. If Acdr.Read() Then
  307. 小間距_ch.Checked = Acdr("L_1_1") : 中間距_ch.Checked = Acdr("L_2_1") : 大間距_ch.Checked = Acdr("L_3_1")
  308. End If : Access1.Close()
  309. MyMod.清單字體大小調整()
  310. 採購單_dgv.RowsDefaultCellStyle.Font = New System.Drawing.Font("微軟正黑體", HX_PGS_ERP_SYS.字體_NUD.Value)
  311. 明細_dgv.RowsDefaultCellStyle.Font = New System.Drawing.Font("微軟正黑體", HX_PGS_ERP_SYS.字體_NUD.Value)
  312. 料號_dgv.RowsDefaultCellStyle.Font = New System.Drawing.Font("微軟正黑體", HX_PGS_ERP_SYS.字體_NUD.Value)
  313. End Sub
  314. Private Sub 採購單編輯_Shown(sender As Object, e As EventArgs) Handles MyBase.Shown
  315. 計算分流01.Enabled = True
  316. End Sub
  317. Private Sub 計算分流01_Tick(sender As Object, e As EventArgs) Handles 計算分流01.Tick
  318. 計算分流01.Enabled = False
  319. Label4.Text = 對話框(6) & 比例1 & 對話框(7) & 比例2 & 對話框(8) & 比例2 & 對話框(7) & 比例3 & 對話框(9) & 比例3
  320. 公司類別_cb.SelectedIndex = 0
  321. 類別管理_cb_下拉表單資料載入() : Set_清單1() : Set_清單2() : 首次開啟 = False
  322. End Sub
  323. Private Sub 採購單編輯_SizeChanged(sender As Object, e As EventArgs) Handles MyBase.SizeChanged
  324. MyMod.清單字體大小調整()
  325. 採購單_dgv.RowsDefaultCellStyle.Font = New System.Drawing.Font("微軟正黑體", HX_PGS_ERP_SYS.字體_NUD.Value)
  326. 明細_dgv.RowsDefaultCellStyle.Font = New System.Drawing.Font("微軟正黑體", HX_PGS_ERP_SYS.字體_NUD.Value)
  327. 料號_dgv.RowsDefaultCellStyle.Font = New System.Drawing.Font("微軟正黑體", HX_PGS_ERP_SYS.字體_NUD.Value)
  328. End Sub
  329. Private Sub 採購單編輯_Closed(sender As Object, e As EventArgs) Handles MyBase.Closed
  330. H(205) = False : MyMod.虛擬桌面開啟()
  331. End Sub
  332. Private Sub 語轉扭_bt_Click(sender As Object, e As EventArgs) Handles 語轉扭_bt.Click
  333. 介面 = "H205" : 語言轉換讀取()
  334. 類別管理_cb_下拉表單資料載入() : Set_清單1() : Set_清單2() : Set_單價() : Set_明細清單() : Set_料號比價()
  335. Label4.Text = 對話框(6) & 比例1 & 對話框(7) & 比例2 & 對話框(8) & 比例2 & 對話框(7) & 比例3 & 對話框(9) & 比例3
  336. End Sub
  337. Private Sub 登入閒置控制_MouseMove(sender As Object, e As EventArgs) Handles MyBase.MouseMove
  338. timeNow = 0 : HX_PGS_ERP_SYS.Timer1.Enabled = False : HX_PGS_ERP_SYS.Timer1.Enabled = True
  339. End Sub
  340. Private Sub 登入閒置控制_KeyPress(sender As Object, e As EventArgs) Handles MyBase.KeyPress
  341. timeNow = 0 : HX_PGS_ERP_SYS.Timer1.Enabled = False : HX_PGS_ERP_SYS.Timer1.Enabled = True
  342. End Sub
  343. Private Sub 價格()
  344. Dim 差異, 比對 As Double
  345. For i As Integer = 0 To 明細_dgv.Rows.Count - 1
  346. If 明細_dgv.Rows(i).Cells("OUP").Value = 0 And 明細_dgv.Rows(i).Cells(表頭(26)).Value = 0 Then
  347. If 深色風格 = False Then : 明細_dgv.Rows(i).DefaultCellStyle.BackColor = Color.Lavender
  348. Else : 明細_dgv.Rows(i).DefaultCellStyle.BackColor = Color.Navy : End If
  349. 明細_dgv.Rows(i).Cells("SP%").Value = "0 % →"
  350. ElseIf 明細_dgv.Rows(i).Cells(表頭(26)).Value = 0 And 明細_dgv.Rows(i).Cells("OUP").Value <> 0 Then
  351. If 深色風格 = False Then : 明細_dgv.Rows(i).DefaultCellStyle.BackColor = Color.Lavender
  352. Else : 明細_dgv.Rows(i).DefaultCellStyle.BackColor = Color.Navy : End If
  353. 明細_dgv.Rows(i).Cells("SP%").Value = "0 % →"
  354. ElseIf 明細_dgv.Rows(i).Cells("OUP").Value <> 0 And 明細_dgv.Rows(i).Cells(表頭(26)).Value <> 0 Then
  355. 差異 = 明細_dgv.Rows(i).Cells(表頭(26)).Value - 明細_dgv.Rows(i).Cells("OUP").Value : 比對 = 差異 / 明細_dgv.Rows(i).Cells(表頭(26)).Value * 100
  356. If 比對 > 0 Then : 明細_dgv.Rows(i).Cells("SP%").Value = CInt(比對) & " % ↗" : 比對 *= 1
  357. ElseIf 比對 < 0 Then : 明細_dgv.Rows(i).Cells("SP%").Value = CInt(比對) & " % ↘" : 比對 *= -1
  358. ElseIf 比對 = 0 Then : 明細_dgv.Rows(i).Cells("SP%").Value = CInt(比對) & " % →" : End If
  359. If 深色風格 = False Then
  360. If 比對 < 比例1 Then : 明細_dgv.Rows(i).DefaultCellStyle.BackColor = Color.White
  361. ElseIf 比對 > 比例1 And 比對 < 比例2 Then : 明細_dgv.Rows(i).DefaultCellStyle.BackColor = Color.LightGreen
  362. ElseIf 比對 > 比例2 And 比對 < 比例3 Then : 明細_dgv.Rows(i).DefaultCellStyle.BackColor = Color.LightBlue
  363. ElseIf 比對 >= 比例3 Then : 明細_dgv.Rows(i).DefaultCellStyle.BackColor = Color.LightPink : End If
  364. Else
  365. If 比對 < 比例1 Then : 明細_dgv.Rows(i).DefaultCellStyle.BackColor = Color.DimGray
  366. ElseIf 比對 > 比例1 And 比對 < 比例2 Then : 明細_dgv.Rows(i).DefaultCellStyle.BackColor = Color.DarkGreen
  367. ElseIf 比對 > 比例2 And 比對 < 比例3 Then : 明細_dgv.Rows(i).DefaultCellStyle.BackColor = Color.DarkBlue
  368. ElseIf 比對 >= 比例3 Then : 明細_dgv.Rows(i).DefaultCellStyle.BackColor = Color.Purple : End If
  369. End If
  370. End If
  371. Next
  372. End Sub
  373. Private Sub 單價_rb_CheckedChanged(sender As Object, e As EventArgs) Handles 單價_rb.Click
  374. If 首次開啟 = False Then : Set_明細清單() : End If
  375. End Sub
  376. Private Sub 日期_rb_CheckedChanged(sender As Object, e As EventArgs) Handles 日期_rb.Click
  377. Set_明細清單()
  378. End Sub
  379. Dim 已超出 As Boolean
  380. Private Sub 明細_dgv_MouseUp(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles 明細_dgv.MouseUp
  381. 'If 明細_dgv.SelectedCells.Count = 1 Then : 通知訊息 = 明細_dgv.SelectedCells(0).ColumnIndex & " " & 明細_dgv.SelectedCells(0).RowIndex) : End If
  382. If 明細_dgv.SelectedCells.Count = 1 Then : 已超出 = False : End If
  383. If 明細_dgv.SelectedCells.Count > 0 Then
  384. If 明細_dgv.SelectedCells(0).ColumnIndex = 9 Then
  385. If 已超出 = False Then
  386. Dim x As Double = 0
  387. For i As Integer = 0 To 明細_dgv.SelectedCells.Count - 1
  388. Dim 變數 As String = "" : Dim 取變數 As String
  389. 取變數 = 明細_dgv.Rows(明細_dgv.SelectedCells.Item(i).RowIndex).Cells(明細_dgv.SelectedCells.Item(i).ColumnIndex).Value
  390. For ii As Integer = 1 To Len(取變數)
  391. If ii = 1 Then : 變數 = Strings.Left(取變數, ii) : Else : If Strings.Mid(取變數, ii, 1) <> "," Then : 變數 &= Strings.Mid(取變數, ii, 1) : End If : End If
  392. Next : x += CLng(變數)
  393. Next i : HX_PGS_ERP_SYS.合計_TSSL.Text = "Total : " & Strings.Format(x, "#,##0")
  394. Else : HX_PGS_ERP_SYS.合計_TSSL.Text = "Total : " & Strings.Format(0, "#,##0") : End If
  395. Else : 已超出 = True : HX_PGS_ERP_SYS.合計_TSSL.Text = "Total : " & Strings.Format(0, "#,##0") : End If
  396. End If
  397. End Sub
  398. Private Sub 明細_dgv_CellClick(ByVal sender As System.Object, ByVal e As DataGridViewCellEventArgs) Handles 明細_dgv.CellClick
  399. If e.RowIndex = -1 Then : Else
  400. 物料圖2_pb.Image = Nothing : 物料圖1_pb.Image = Nothing
  401. PA2 = 明細_dgv.Rows(e.RowIndex).Cells("料號原則").Value : SQL_物料圖庫_圖檔讀取一()
  402. While dr.Read() = True
  403. Dim unused As Byte() = New Byte(-1) {}
  404. Dim bytes As Byte() = DirectCast(dr.Item("圖檔"), Byte())
  405. Dim oStream As New MemoryStream(bytes)
  406. 物料圖1_pb.Image = Bitmap.FromStream(oStream)
  407. End While
  408. conn.Close() : 物料圖1_pb.SizeMode = 4
  409. 料號1_tb.Text = 明細_dgv("料號", e.RowIndex).Value.ToString
  410. 品名_tb.Text = 明細_dgv(表頭(12), e.RowIndex).Value.ToString
  411. If 歷史單價_P.Visible = True Then : Set_料號比價() : 繪圖() : 料號_dgv.Sort(料號_dgv.Columns("排序"), System.ComponentModel.ListSortDirection.Descending) : End If
  412. End If
  413. 價格()
  414. End Sub
  415. Private Sub 明細_dgv_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles 明細_dgv.CellEnter
  416. 總金額計算() : 價格()
  417. End Sub
  418. Private Sub 請購單_dgv_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles 請購單_dgv.CellClick
  419. 選擇公司_cb.Enabled = True : 採購選擇 = "A"
  420. If e.RowIndex = -1 Then : Else
  421. QQQ1 = 請購單_dgv(0, e.RowIndex).Value.ToString : QQQ2 = 請購單_dgv(3, e.RowIndex).Value.ToString : QQQ3 = 請購單_dgv(4, e.RowIndex).Value.ToString
  422. If 請購單_dgv.Rows(e.RowIndex).Cells("用途").Value.ToString = "" Then : 使用地點_tb.Text = "" : Else
  423. 使用地點_tb.Text = 請購單_dgv.Rows(e.RowIndex).Cells("用途").Value.ToString & "-" & 請購單_dgv.Rows(e.RowIndex).Cells("倉名").Value
  424. End If
  425. 折扣比_tb.Text = "0" : 稅金計算_ch.Checked = True : 稅金比_tb.Text = "10" : 採購單號_tb.Text = ""
  426. End If : Set_明細清單()
  427. End Sub
  428. Private Sub 採購單_dgv_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles 採購單_dgv.CellClick
  429. PA = "" : 廠商清空()
  430. 選擇公司_cb.Enabled = False : 採購選擇 = "B"
  431. If e.RowIndex = -1 Then : Else
  432. 採購單號_tb.Text = 採購單_dgv(0, e.RowIndex).Value.ToString : 折扣比_tb.Text = 採購單_dgv(13, e.RowIndex).Value.ToString : 稅金計算_ch.Checked = 採購單_dgv(14, e.RowIndex).Value
  433. 稅金比_tb.Text = 採購單_dgv(15, e.RowIndex).Value.ToString : 核對員_tb.Text = 採購單_dgv(4, e.RowIndex).Value.ToString : 核對日期_tb.Text = 採購單_dgv(5, e.RowIndex).Value.ToString
  434. 詢價員_tb.Text = 採購單_dgv(6, e.RowIndex).Value.ToString : 尋價日期_tb.Text = 採購單_dgv(7, e.RowIndex).Value.ToString : 手動號碼_tb.Text = 採購單_dgv(18, e.RowIndex).Value.ToString
  435. If Strings.Left(採購單號_tb.Text, 3) = "HXI" Then
  436. 選擇公司_cb.Text = "PT HONG XHE INDUSTRIAL"
  437. ElseIf Strings.Left(採購單號_tb.Text, 3) = "PGS" Then
  438. 選擇公司_cb.Text = "PT PUNCAK GEMILANG SEMESTA" : End If
  439. 供應商編碼_tb.Text = 採購單_dgv(1, e.RowIndex).Value.ToString : conn.Close()
  440. PA1 = 供應商編碼_tb.Text : SQL_建築物料採購單生成_供應商資料一()
  441. If dr.Read() Then : 廠商_cb.Text = dr("公司名") : conn.Close() : Else : 廠商_cb.Text = "" : End If : conn.Close()
  442. 使用地點_tb.Text = 採購單_dgv(3, e.RowIndex).Value.ToString : 需求日期_dtp.Value = 採購單_dgv(2, e.RowIndex).Value
  443. Set_明細清單() : 歷史單價_P.Visible = False
  444. PA1 = 廠商_cb.Text : SQL_建築物料採購單生成_供應商資料二()
  445. If dr.Read() Then
  446. 地址_tb.Text = dr("公司地址").ToString : 電話_tb.Text = dr("公司電話1").ToString : 郵件_tb.Text = dr("聯繫人1郵箱").ToString
  447. 傳真_tb.Text = dr("公司傳真1").ToString : 供應商編碼_tb.Text = dr("供應商編碼").ToString
  448. End If : conn.Close()
  449. End If
  450. End Sub
  451. Private Sub 廠商_cb_SelectedIndexChanged(sender As Object, e As EventArgs) Handles 廠商_cb.SelectedIndexChanged
  452. PA1 = 廠商_cb.Text : SQL_建築物料採購單生成_供應商資料二()
  453. If dr.Read() Then
  454. 地址_tb.Text = dr("公司地址").ToString : 電話_tb.Text = dr("公司電話1").ToString : 郵件_tb.Text = dr("聯繫人1郵箱").ToString
  455. 傳真_tb.Text = dr("公司傳真1").ToString : 供應商編碼_tb.Text = dr("供應商編碼").ToString
  456. End If : conn.Close()
  457. End Sub
  458. Private Sub Set_日期格式轉換()
  459. DTP = Strings.Format(Today(), "yyyy/MM/dd") : DTP1 = Strings.Format(Today(), "yyyyMMdd") : DTP2 = Strings.Format(需求日期_dtp.Value, "yyyy/MM/dd")
  460. End Sub
  461. Private Sub 類別管理_cb_SelectedIndexChanged(sender As Object, e As EventArgs) Handles 類別管理_cb.SelectedIndexChanged
  462. PA20 = 類別管理_cb.Text : SQL_供應商類別管理_查詢()
  463. If dr.Read() Then : 供應商簡稱_tb.Text = dr("編碼").ToString : Else : 供應商簡稱_tb.Text = "" : End If
  464. PA = 供應商簡稱_tb.Text : 廠商_cb_下拉表單資料載入()
  465. End Sub
  466. Private Sub 供應商編碼_tb_TextChanged(sender As Object, e As EventArgs) Handles 供應商編碼_tb.TextChanged
  467. Set_清單3()
  468. End Sub
  469. Private Sub 清空供應商_bt_Click(sender As Object, e As EventArgs) Handles 清空供應商_bt.Click
  470. If 採購單號_tb.Text = "" Then : Else
  471. SQL_採購單供應商_清空() : 廠商_cb.Text = "" : 地址_tb.Text = "" : 電話_tb.Text = "" : 郵件_tb.Text = "" : 傳真_tb.Text = "" : 供應商編碼_tb.Text = "" : MGB(對話框(15), 1)
  472. End If
  473. End Sub
  474. Private Sub 折扣比_tb_TextChanged(sender As Object, e As EventArgs) Handles 折扣比_tb.TextChanged
  475. 總金額計算()
  476. End Sub
  477. Private Sub 稅金計算_ch_CheckedChanged(sender As Object, e As EventArgs) Handles 稅金計算_ch.CheckedChanged
  478. 總金額計算()
  479. End Sub
  480. Private Sub 稅金比_tb_TextChanged(sender As Object, e As EventArgs) Handles 稅金比_tb.TextChanged
  481. 總金額計算()
  482. End Sub
  483. Private Sub 全部選擇_bt_Click(sender As Object, e As EventArgs) Handles 全部選擇_bt.Click
  484. For i As Integer = 0 To 明細_dgv.Rows.Count - 1 : 明細_dgv.Rows(i).Cells("" & 表頭(28) & "").Value = True : Next i
  485. End Sub
  486. Private Sub 全部取消_bt_Click(sender As Object, e As EventArgs) Handles 全部取消_bt.Click
  487. For i As Integer = 0 To 明細_dgv.Rows.Count - 1 : 明細_dgv.Rows(i).Cells("" & 表頭(28) & "").Value = False : Next i
  488. End Sub
  489. Private Sub 生成採購單_bt_Click(sender As Object, e As EventArgs) Handles 生成採購單_bt.Click
  490. If 手動號碼_tb.Text = "" Then : MGB(系統語言字典("H206-143-" & 語言), 1) : Else
  491. If 採購選擇 = "A" Then : 生成新的採購訂單() : ElseIf 採購選擇 = "B" Then : 修改的採購單存檔() : End If : Set_清單2()
  492. End If
  493. End Sub
  494. Private Sub 生成新的採購訂單()
  495. If 選擇公司_cb.Text = "PT HONG XHE INDUSTRIAL" Then
  496. Set_日期格式轉換() : Dim NUM1 As Integer
  497. For i As Integer = 1 To 999
  498. NUM1 = 0 + i
  499. If NUM1 < 10 Then : 採購單號_tb.Text = "HXI-C-" & DTP1 & "-00" & NUM1
  500. ElseIf NUM1 > 9 And NUM1 < 100 Then : 採購單號_tb.Text = "HXI-C-" & DTP1 & "-0" & NUM1
  501. ElseIf NUM1 > 99 Then : 採購單號_tb.Text = "HXI-C-" & DTP1 & "-" & NUM1
  502. End If
  503. PA1 = 採購單號_tb.Text
  504. SQL_採購單控制表_單號最後一筆() : If dr.Read() Then : Else : i = 999 : conn.Close() : End If : conn.Close()
  505. Next
  506. 確定新增()
  507. ElseIf 選擇公司_cb.Text = "PT PUNCAK GEMILANG SEMESTA" Then
  508. Set_日期格式轉換() : Dim NUM1 As Integer
  509. For i As Integer = 1 To 999
  510. NUM1 = 0 + i
  511. If NUM1 < 10 Then : 採購單號_tb.Text = "PGS-C-" & DTP1 & "-00" & NUM1
  512. ElseIf NUM1 > 9 And NUM1 < 100 Then : 採購單號_tb.Text = "PGS-C-" & DTP1 & "-0" & NUM1
  513. ElseIf NUM1 > 99 Then : 採購單號_tb.Text = "PGS-C-" & DTP1 & "-" & NUM1
  514. End If
  515. PA1 = 採購單號_tb.Text
  516. SQL_採購單控制表_單號最後一筆() : If dr.Read() Then : Else : i = 999 : conn.Close() : End If : conn.Close()
  517. Next
  518. 確定新增()
  519. End If
  520. End Sub
  521. Private Sub 修改的採購單存檔()
  522. If 廠商_cb.Text = "" Then : MGB(對話框(5), 1) : Else : Set_日期格式轉換()
  523. PA1 = 供應商編碼_tb.Text : PA2 = 使用地點_tb.Text : PA3 = 折扣比_tb.Text : BL1 = 稅金計算_ch.Checked : PA4 = 稅金比_tb.Text : PA5 = 採購單號_tb.Text
  524. PA39 = 手動號碼_tb.Text : SQL_採購單控制表_編輯() : conn.Close()
  525. For i As Integer = 0 To 明細_dgv.Rows.Count - 1
  526. 資料數 = 明細_dgv.Rows.Count : MyMod.進度條()
  527. PA6 = 明細_dgv.Rows(i).Cells(表頭(14)).Value : PA7 = 明細_dgv.Rows(i).Cells(表頭(26)).Value
  528. PA8 = 明細_dgv.Rows(i).Cells(表頭(15)).Value : PA9 = 明細_dgv.Rows(i).Cells(表頭(16)).Value
  529. PA10 = 明細_dgv.Rows(i).Cells("項次").Value : PA4 = 明細_dgv.Rows(i).Cells("KEY").Value
  530. SQL_採購單明細表_修改() : conn.Close()
  531. Next
  532. Set_清單1() : Set_清單2() : MGB(對話框(4), 1) : HX_PGS_ERP_SYS.ToolStripProgressBar1.Value = 0 : 最終數 = 0
  533. End If
  534. End Sub
  535. Private Sub 確定新增()
  536. Set_日期格式轉換()
  537. If 採購單號_tb.Text = "" Or 供應商編碼_tb.Text = "" Or 明細_dgv.Rows.Count = 0 Then : MGB(對話框(0), 1) : Else
  538. PA1 = 採購單號_tb.Text : PA2 = 供應商編碼_tb.Text : PA3 = 使用地點_tb.Text : PA4 = QQQ2
  539. PA5 = QQQ3 : PA6 = QQQ1 : PA7 = 折扣比_tb.Text : BL1 = 稅金計算_ch.Checked : PA8 = 稅金比_tb.Text
  540. SQL_採購單控制表_新增() : SQL_請購單控制表_轉採修改() : conn.Close()
  541. For i As Integer = 0 To 明細_dgv.Rows.Count - 1
  542. 資料數 = 明細_dgv.Rows.Count : MyMod.進度條()
  543. If 明細_dgv.Rows(i).Cells("" & 表頭(28) & "").Value = True Then
  544. PA9 = 明細_dgv.Rows(i).Cells("" & 表頭(28) & "").Value : PA2 = 明細_dgv.Rows(i).Cells(表頭(18)).Value : PA3 = 明細_dgv.Rows(i).Cells("項次").Value
  545. PA4 = 明細_dgv.Rows(i).Cells(表頭(12)).Value : PA5 = 明細_dgv.Rows(i).Cells(表頭(13)).Value : PA6 = 明細_dgv.Rows(i).Cells(表頭(14)).Value
  546. NU1 = 明細_dgv.Rows(i).Cells(表頭(15)).Value : PA8 = 明細_dgv.Rows(i).Cells(表頭(16)).Value : PA10 = 明細_dgv.Rows(i).Cells(表頭(26)).Value
  547. PA11 = 明細_dgv.Rows(i).Cells("群組碼").Value : PA12 = 明細_dgv.Rows(i).Cells("料號原則").Value : PA13 = 明細_dgv.Rows(i).Cells("料號").Value
  548. SQL_請購單明細表_轉採修改() : SQL_採購單明細表_新增() : conn.Close()
  549. End If
  550. Next
  551. Set_清單1() : Set_清單2() : MGB(對話框(1), 1) : HX_PGS_ERP_SYS.ToolStripProgressBar1.Value = 0 : 最終數 = 0
  552. End If
  553. End Sub
  554. Private Sub 料號_bt_Click(sender As Object, e As EventArgs) Handles 分析工具_bt.Click
  555. Set_料號比價()
  556. If 料號1_tb.Text = "" Then : MGB(對話框(10), 1) : Else
  557. If 歷史單價_P.Visible = True Then : 歷史單價_P.Visible = False
  558. Else : 歷史單價_P.Visible = True : 繪圖() : 料號_dgv.Sort(料號_dgv.Columns("排序"), System.ComponentModel.ListSortDirection.Descending) : End If
  559. End If
  560. End Sub
  561. Private Sub 繪圖()
  562. Chart1.Series.Clear() : Chart1.ChartAreas(0).AxisX.Minimum = 0
  563. If 品名_tb.Text <> "" Then
  564. If 折線圖_ch.Checked = True Then
  565. Chart1.Series.Add(品名_tb.Text & "_1") : Chart1.Series(品名_tb.Text & "_1").ChartType = SeriesChartType.Line : Chart1.Series(品名_tb.Text & "_1").IsVisibleInLegend = False
  566. End If
  567. If 長條圖_ch.Checked = True Then
  568. Chart1.Series.Add(品名_tb.Text) : Chart1.Series(品名_tb.Text).ChartType = SeriesChartType.Column
  569. End If
  570. For i As Integer = 0 To 料號_dgv.Rows.Count - 1
  571. Chart1.Series(品名_tb.Text).Points.AddXY(料號_dgv.Rows(i).Cells(表頭(134)).Value.ToString, 料號_dgv.Rows(i).Cells(表頭(26)).Value.ToString)
  572. Next
  573. End If
  574. End Sub
  575. Private Sub 小間距_ch_CheckedChanged(sender As Object, e As EventArgs) Handles 小間距_ch.Click
  576. 小間距_ch.Checked = True : 中間距_ch.Checked = False : 大間距_ch.Checked = False : MyMod.間距設定存檔_1() : Set_明細清單()
  577. End Sub
  578. Private Sub 中間距_ch_CheckedChanged(sender As Object, e As EventArgs) Handles 中間距_ch.Click
  579. 小間距_ch.Checked = False : 中間距_ch.Checked = True : 大間距_ch.Checked = False : MyMod.間距設定存檔_2() : Set_明細清單()
  580. End Sub
  581. Private Sub 類別管理_cb_Click(sender As Object, e As EventArgs) Handles 類別管理_cb.Click
  582. CB選擇清單(類別管理_cb, 類別管理_cb.Text)
  583. End Sub
  584. Private Sub 廠商_cb_Click(sender As Object, e As EventArgs) Handles 廠商_cb.Click
  585. CB選擇清單(廠商_cb, 廠商_cb.Text)
  586. End Sub
  587. Private Sub 選擇公司_cb_Click(sender As Object, e As EventArgs) Handles 選擇公司_cb.Click
  588. CB選擇清單(選擇公司_cb, 選擇公司_cb.Text)
  589. End Sub
  590. Private Sub 大間距_ch_CheckedChanged(sender As Object, e As EventArgs) Handles 大間距_ch.Click
  591. 小間距_ch.Checked = False : 中間距_ch.Checked = False : 大間距_ch.Checked = True : MyMod.間距設定存檔_3() : Set_明細清單()
  592. End Sub
  593. Private Sub 物料圖1_pb_Click(sender As Object, e As EventArgs) Handles 物料圖1_pb.Click
  594. If IsNothing(物料圖1_pb.Image) = False Then : 圖片傳遞 = 物料圖1_pb.Image : 圖片放大視窗.ShowDialog() : End If
  595. End Sub
  596. Private Sub 未指定_ch_Click(sender As Object, e As EventArgs) Handles 未指定_ch.Click
  597. 未指定_ch.Checked = True : 已指定_ch.Checked = False : Set_清單2()
  598. End Sub
  599. Private Sub 已指定_ch_Click(sender As Object, e As EventArgs) Handles 已指定_ch.Click
  600. 未指定_ch.Checked = False : 已指定_ch.Checked = True : Set_清單2()
  601. End Sub
  602. Private Sub 詳細資料2_ch_CheckedChanged(sender As Object, e As EventArgs) Handles 詳細資料2_ch.Click
  603. If 詳細資料2_ch.Checked = True Then : 詳細資料2_ch.Checked = True : Else : 詳細資料2_ch.Checked = False : End If : Set_明細清單()
  604. End Sub
  605. Private Sub 列印成詢價單_bt_Click(sender As Object, e As EventArgs) Handles 列印成詢價單_bt.Click
  606. If 採購單號_tb.Text = "" Then : MGB(對話框(2), 1) : Else
  607. Dim ds6 As New DataSet : 請購資料_dgv.DataSource = Nothing : ds6.Clear()
  608. 請購資料_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
  609. 請購資料_dgv.ColumnHeadersHeight = 25 : 請購資料_dgv.AllowUserToAddRows = False
  610. PA38 = 採購單號_tb.Text : SQL_採購資料() : da.Fill(ds6) : 請購資料_dgv.DataSource = ds6.Tables(0) : conn.Close()
  611. xlApp = CType(CreateObject("Excel.Application"), Application) : xlBook = xlApp.Workbooks.Add : xlApp.DisplayAlerts = True : xlBook.Activate()
  612. xlSheet = NewMethod(xlBook) : xlSheet.Activate() : xlApp.Visible = True : xlApp.Application.WindowState = xlMaximized
  613. 'xlSheet.Cells(2, 1) = 選擇公司_cb.Text
  614. xlSheet.Cells(3, 1) = "MINTA PESANAN HARGA"
  615. xlSheet.Cells(5, 1) = "ORDER TO : " & 廠商_cb.Text : xlSheet.Cells(6, 1) = "ALAMAT : " & 地址_tb.Text : xlSheet.Cells(7, 1) = "Telp : " & 電話_tb.Text
  616. xlSheet.Cells(8, 1) = "Fax : " & 傳真_tb.Text : xlSheet.Cells(9, 1) = "Email : " & 郵件_tb.Text
  617. xlSheet.Cells(10, 1) = "No." : xlSheet.Cells(10, 2) = "Nama produk" : xlSheet.Cells(10, 3) = "Spesifikasi" : xlSheet.Cells(10, 4) = "Komentar" : xlSheet.Cells(10, 5) = "SATUAN"
  618. xlSheet.Cells(10, 6) = "QTY" : xlSheet.Cells(10, 7) = "UNIT PRICE" : xlSheet.Cells(10, 8) = "TOTAL PRICE"
  619. xlSheet.Cells(5, 5) = "PO NOMOR 1 : " : xlSheet.Cells(5, 7) = 手動號碼_tb.Text
  620. xlSheet.Cells(7, 5) = "PO NOMOR 2 : " : xlSheet.Cells(7, 7) = 採購單號_tb.Text
  621. xlSheet.Cells(8, 5) = "TGL/BLN/THN : " : xlSheet.Cells(8, 7) = 需求日期_dtp.Value
  622. xlSheet.Cells(9, 5) = "TEMPAT PEMAKAIAN : " : xlSheet.Cells(9, 7) = 使用地點_tb.Text
  623. Dim GGHH As Long = 0
  624. For i As Integer = 0 To 明細_dgv.Rows.Count - 1
  625. xlSheet.Cells(i + 11, 1) = 明細_dgv.Rows(i).Cells(表頭(11)).Value : xlSheet.Cells(i + 11, 2) = 明細_dgv.Rows(i).Cells(表頭(12)).Value
  626. xlSheet.Cells(i + 11, 3) = 明細_dgv.Rows(i).Cells(表頭(13)).Value : xlSheet.Cells(i + 11, 4) = 明細_dgv.Rows(i).Cells(表頭(14)).Value
  627. xlSheet.Cells(i + 11, 5) = 明細_dgv.Rows(i).Cells(表頭(16)).Value : xlSheet.Cells(i + 11, 6) = 明細_dgv.Rows(i).Cells(表頭(15)).Value
  628. xlSheet.Cells(i + 11, 7) = 明細_dgv.Rows(i).Cells(表頭(26)).Value : xlSheet.Cells(i + 11, 8) = 明細_dgv.Rows(i).Cells(表頭(27)).Value
  629. GGHH += 明細_dgv.Rows(i).Cells(表頭(27)).Value : N1 = i + 11
  630. Next i
  631. xlSheet.Cells(N1 + 2, 7) = "TOTAL :" : xlSheet.Cells(N1 + 2, 8) = GGHH
  632. xlSheet.Cells(N1 + 3, 7) = "DISC " & 折扣比_tb.Text & "% :" : xlSheet.Cells(N1 + 3, 8) = 折扣金額_tb.Text
  633. xlSheet.Cells(N1 + 4, 7) = "PPN " & 稅金比_tb.Text & "% :" : xlSheet.Cells(N1 + 4, 8) = 稅金金額_tb.Text
  634. xlSheet.Cells(N1 + 5, 7) = "GRAND TOTAL :" : xlSheet.Cells(N1 + 5, 8) = 總金額_tb.Text
  635. xlSheet.Cells(N1 + 6, 1) = "Bagian Pembelian Barang"
  636. xlSheet.Cells(N1 + 6, 3) = "Direktur Utama"
  637. xlSheet.Cells(N1 + 6, 4) = "Bagian Pemeriksa"
  638. xlSheet.Cells(N1 + 6, 6) = "Bagian Pengisian Harga" : xlSheet.Cells(N1 + 8, 6) = 詢價員_tb.Text : xlSheet.Cells(N1 + 9, 6) = 尋價日期_tb.Text
  639. xlSheet.Cells(N1 + 6, 8) = "Asisten Proyek" : xlSheet.Cells(N1 + 8, 8) = 核對員_tb.Text : xlSheet.Cells(N1 + 9, 8) = 核對日期_tb.Text
  640. For i As Integer = 0 To 請購資料_dgv.Rows.Count - 1
  641. xlSheet.Cells(i + N1 + 12, 1) = 請購資料_dgv.Rows(i).Cells(0).Value
  642. If 系統語言 = "繁體中文" Then
  643. xlSheet.Cells(i + N1 + 12, 3) = 請購資料_dgv.Rows(i).Cells(2).Value
  644. Else
  645. xlSheet.Cells(i + N1 + 12, 3) = 請購資料_dgv.Rows(i).Cells(1).Value
  646. End If
  647. xlSheet.Cells(i + N1 + 12, 8) = 請購資料_dgv.Rows(i).Cells(3).Value : N2 += 1
  648. Next : AA(xlApp, xlSheet) : FF(xlApp, xlSheet)
  649. If Strings.Left(採購單號_tb.Text, 2) = "HX" Then : CC(xlApp, xlSheet) : Else : DD(xlApp, xlSheet) : End If
  650. '-------------轉換成PDF---------------------------------------------------------------------
  651. Dim SERT As String = Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
  652. xlBook.Sheets(1).ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, SERT & "\" & 採購單號_tb.Text & "_PO(採購單).pdf", XlFixedFormatQuality.xlQualityStandard, True)
  653. '-------------關閉EXCEL---------------------------------------------------------------------
  654. xlSheet.PageSetup.PrintArea = "" : xlApp.Cells.Select()
  655. xlApp.Application.WindowState = xlMinimized
  656. xlBook.Close(False)
  657. Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet)
  658. Runtime.InteropServices.Marshal.ReleaseComObject(xlBook)
  659. Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
  660. xlApp.Quit() : xlSheet = Nothing : xlBook = Nothing : xlApp = Nothing : GC.Collect()
  661. '-------------開啟PDF---------------------------------------------------------------------
  662. Dim pdfFilePath As String = SERT & "\" & 採購單號_tb.Text & "_PO(採購單).pdf"
  663. If IsChromeInstalled() Then
  664. Try
  665. Dim chromeProcess As New Process()
  666. chromeProcess.StartInfo.FileName = "chrome"
  667. chromeProcess.StartInfo.Arguments = """" & pdfFilePath & """"
  668. chromeProcess.Start()
  669. Catch ex As Exception
  670. End Try
  671. Else
  672. Try
  673. System.Diagnostics.Process.Start("""" & pdfFilePath & """")
  674. Catch ex As Exception
  675. End Try
  676. End If
  677. MGB(對話框(3), 1)
  678. End If
  679. End Sub
  680. ' 檢查系統是否已安裝 Google Chrome
  681. Private Function IsChromeInstalled() As Boolean
  682. Dim key As Microsoft.Win32.RegistryKey = Microsoft.Win32.Registry.LocalMachine.OpenSubKey("SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall")
  683. If key IsNot Nothing Then
  684. For Each subkeyName As String In key.GetSubKeyNames()
  685. Dim subkey As Microsoft.Win32.RegistryKey = key.OpenSubKey(subkeyName)
  686. Dim displayName As Object = subkey.GetValue("DisplayName")
  687. If displayName IsNot Nothing AndAlso displayName.ToString().Contains("Google Chrome") Then
  688. Return True
  689. End If
  690. Next
  691. End If
  692. Return False
  693. End Function
  694. Private Shared Function NewMethod(xlBook As Workbook) As Worksheet
  695. Return CType(xlBook.Worksheets.Add, Worksheet)
  696. End Function
  697. Private Sub AA(ByVal myExcel As Application, ByVal xlSheet As Worksheet)
  698. xlSheet.Cells.Select()
  699. With myExcel.Selection.Font : .Name = "微軟正黑體" : .Size = 12 : .Strikethrough = False : .Superscript = False : .Subscript = False : .OutlineFont = False : .Shadow = False
  700. .Underline = xlUnderlineStyleNone : .ThemeColor = xlThemeColorLight1 : .TintAndShade = 0 : .ThemeFont = xlThemeFontNone : End With
  701. xlSheet.Columns("A:A").Select : myExcel.Selection.ColumnWidth = 6 : xlSheet.Columns("B:B").Select : myExcel.Selection.ColumnWidth = 24
  702. xlSheet.Columns("C:C").Select : myExcel.Selection.ColumnWidth = 30 : xlSheet.Columns("D:D").Select : myExcel.Selection.ColumnWidth = 20
  703. xlSheet.Columns("E:E").Select : myExcel.Selection.ColumnWidth = 10 : xlSheet.Columns("F:F").Select : myExcel.Selection.ColumnWidth = 15
  704. xlSheet.Columns("G:G").Select : myExcel.Selection.ColumnWidth = 15 : xlSheet.Columns("H:H").Select : myExcel.Selection.ColumnWidth = 30
  705. xlSheet.Rows("1:1").Select : myExcel.Selection.RowHeight = 90 : xlSheet.Rows("2:2").Select : myExcel.Selection.RowHeight = 5
  706. xlSheet.Rows("3:3").Select : myExcel.Selection.RowHeight = 25 : xlSheet.Rows("4:4").Select : myExcel.Selection.RowHeight = 5
  707. xlSheet.Rows("5:9").Select : myExcel.Selection.RowHeight = 20 : xlSheet.Rows("10:10").Select : myExcel.Selection.RowHeight = 20
  708. xlSheet.Rows("11:" & N1).Select : myExcel.Selection.RowHeight = 65 : xlSheet.Rows(N1 + 1 & ":" & N1 + 1).Select : myExcel.Selection.RowHeight = 5
  709. xlSheet.Rows(N1 + 2 & ":" & N1 + 5).Select : myExcel.Selection.RowHeight = 17 : xlSheet.Rows(N1 + 6 & ":" & N1 + 6).Select : myExcel.Selection.RowHeight = 35
  710. xlSheet.Rows(N1 + 7 & ":" & N1 + 9).Select : myExcel.Selection.RowHeight = 35
  711. xlSheet.Range("A2:H2").Select()
  712. With myExcel.Selection : .HorizontalAlignment = xlCenter : .VerticalAlignment = xlCenter : .WrapText = False : .Orientation = 0 : .AddIndent = False : .IndentLevel = 0 : .ShrinkToFit = False
  713. .ReadingOrder = xlContext : .MergeCells = False : End With : myExcel.Selection.Merge
  714. With myExcel.Selection.Font : .Name = "微軟正黑體" : .Size = 26 : .Strikethrough = False : .Superscript = False : .Subscript = False : .OutlineFont = False : .Shadow = False
  715. .Underline = xlUnderlineStyleNone : .ThemeColor = xlThemeColorLight1 : .TintAndShade = 0 : .ThemeFont = xlThemeFontNone : End With
  716. xlSheet.Range("A3:H3").Select()
  717. With myExcel.Selection : .HorizontalAlignment = xlCenter : .VerticalAlignment = xlCenter : .WrapText = False : .Orientation = 0 : .AddIndent = False : .IndentLevel = 0 : .ShrinkToFit = False
  718. .ReadingOrder = xlContext : .MergeCells = False : End With : myExcel.Selection.Merge
  719. With myExcel.Selection.Font : .Name = "微軟正黑體" : .Size = 16 : .Strikethrough = False : .Superscript = False : .Subscript = False : .OutlineFont = False : .Shadow = False
  720. .Underline = xlUnderlineStyleNone : .ThemeColor = xlThemeColorLight1 : .TintAndShade = 0 : .ThemeFont = xlThemeFontNone : End With
  721. xlSheet.Range("A10:H" & N1).Select()
  722. myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  723. With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  724. With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  725. With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  726. With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  727. With myExcel.Selection.Borders(xlInsideVertical) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  728. With myExcel.Selection.Borders(xlInsideHorizontal) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  729. xlSheet.Range("A" & N1 + 2 & ":H" & N1 + 2).Select()
  730. myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  731. With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  732. With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  733. With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  734. With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  735. myExcel.Selection.Borders(xlInsideVertical).LineStyle = xlNone : myExcel.Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
  736. xlSheet.Range("A" & N1 + 3 & ":H" & N1 + 3).Select()
  737. myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  738. With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  739. With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  740. With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  741. With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  742. myExcel.Selection.Borders(xlInsideVertical).LineStyle = xlNone : myExcel.Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
  743. xlSheet.Range("A" & N1 + 4 & ":H" & N1 + 4).Select()
  744. myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  745. With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  746. With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  747. With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  748. With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  749. myExcel.Selection.Borders(xlInsideVertical).LineStyle = xlNone : myExcel.Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
  750. xlSheet.Range("A" & N1 + 5 & ":H" & N1 + 5).Select()
  751. myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  752. With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  753. With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  754. With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  755. With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  756. myExcel.Selection.Borders(xlInsideVertical).LineStyle = xlNone : myExcel.Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
  757. xlSheet.Range("A" & N1 + 6 & ":B" & N1 + 6).Select()
  758. With myExcel.Selection : .HorizontalAlignment = xlCenter : .VerticalAlignment = xlCenter : .WrapText = False : .Orientation = 0 : .AddIndent = False : .IndentLevel = 0 : .ShrinkToFit = False
  759. .ReadingOrder = xlContext : .MergeCells = False : End With : myExcel.Selection.Merge
  760. myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  761. With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  762. With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  763. With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  764. With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  765. myExcel.Selection.Borders(xlInsideVertical).LineStyle = xlNone : myExcel.Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
  766. xlSheet.Range("C" & N1 + 6 & ":C" & N1 + 6).Select()
  767. With myExcel.Selection : .HorizontalAlignment = xlCenter : .VerticalAlignment = xlCenter : .WrapText = False : .Orientation = 0 : .AddIndent = False : .IndentLevel = 0 : .ShrinkToFit = False
  768. .ReadingOrder = xlContext : .MergeCells = False : End With : myExcel.Selection.Merge
  769. myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  770. With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  771. With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  772. With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  773. With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  774. myExcel.Selection.Borders(xlInsideVertical).LineStyle = xlNone : myExcel.Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
  775. xlSheet.Range("D" & N1 + 6 & ":E" & N1 + 6).Select()
  776. With myExcel.Selection : .HorizontalAlignment = xlCenter : .VerticalAlignment = xlCenter : .WrapText = False : .Orientation = 0 : .AddIndent = False : .IndentLevel = 0 : .ShrinkToFit = False
  777. .ReadingOrder = xlContext : .MergeCells = False : End With : myExcel.Selection.Merge
  778. myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  779. With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  780. With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  781. With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  782. With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  783. myExcel.Selection.Borders(xlInsideVertical).LineStyle = xlNone : myExcel.Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
  784. xlSheet.Range("F" & N1 + 6 & ":G" & N1 + 6).Select()
  785. With myExcel.Selection : .HorizontalAlignment = xlCenter : .VerticalAlignment = xlCenter : .WrapText = False : .Orientation = 0 : .AddIndent = False : .IndentLevel = 0 : .ShrinkToFit = False
  786. .ReadingOrder = xlContext : .MergeCells = False : End With : myExcel.Selection.Merge
  787. myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  788. With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  789. With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  790. With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  791. With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  792. myExcel.Selection.Borders(xlInsideVertical).LineStyle = xlNone : myExcel.Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
  793. xlSheet.Range("H" & N1 + 6).Select()
  794. With myExcel.Selection : .HorizontalAlignment = xlCenter : .VerticalAlignment = xlCenter : .WrapText = True : .Orientation = 0 : .AddIndent = False : .IndentLevel = 0
  795. .ShrinkToFit = False : .ReadingOrder = xlContext : .MergeCells = False : End With
  796. myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  797. With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  798. With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  799. With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  800. With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  801. myExcel.Selection.Borders(xlInsideVertical).LineStyle = xlNone : myExcel.Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
  802. xlSheet.Range("A" & N1 + 7 & ":B" & N1 + 9).Select()
  803. myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  804. With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  805. With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  806. With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  807. With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  808. myExcel.Selection.Borders(xlInsideVertical).LineStyle = xlNone : myExcel.Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
  809. xlSheet.Range("C" & N1 + 7 & ":C" & N1 + 9).Select()
  810. myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  811. With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  812. With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  813. With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  814. With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  815. myExcel.Selection.Borders(xlInsideVertical).LineStyle = xlNone : myExcel.Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
  816. xlSheet.Range("D" & N1 + 7 & ":E" & N1 + 9).Select()
  817. myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  818. With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  819. With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  820. With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  821. With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  822. myExcel.Selection.Borders(xlInsideVertical).LineStyle = xlNone : myExcel.Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
  823. xlSheet.Range("F" & N1 + 7 & ":G" & N1 + 9).Select()
  824. myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  825. With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  826. With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  827. With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  828. With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  829. myExcel.Selection.Borders(xlInsideVertical).LineStyle = xlNone : myExcel.Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
  830. With myExcel.Selection : .HorizontalAlignment = xlCenter : .VerticalAlignment = xlCenter : .WrapText = False : .Orientation = 0 : .AddIndent = False : .IndentLevel = 0
  831. .ShrinkToFit = False : .ReadingOrder = xlContext : .MergeCells = False : End With
  832. xlSheet.Range("H" & N1 + 7 & ":H" & N1 + 9).Select()
  833. myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  834. With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  835. With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  836. With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  837. With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  838. myExcel.Selection.Borders(xlInsideVertical).LineStyle = xlNone : myExcel.Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
  839. With myExcel.Selection : .HorizontalAlignment = xlCenter : .VerticalAlignment = xlCenter : .WrapText = False : .Orientation = 0 : .AddIndent = False : .IndentLevel = 0
  840. .ShrinkToFit = False : .ReadingOrder = xlContext : .MergeCells = False : End With
  841. xlSheet.Range("A10:A" & N1).Select()
  842. With myExcel.Selection : .HorizontalAlignment = xlCenter : .VerticalAlignment = xlCenter : .WrapText = False : .Orientation = 0 : .AddIndent = False : .IndentLevel = 0
  843. .ShrinkToFit = False : .ReadingOrder = xlContext : .MergeCells = False : End With
  844. xlSheet.Range("B11:D" & N1).Select()
  845. With myExcel.Selection
  846. .HorizontalAlignment = xlGeneral : .VerticalAlignment = xlCenter : .WrapText = True : .Orientation = 0 : .AddIndent = False : .IndentLevel = 0 : .ShrinkToFit = False
  847. .ReadingOrder = xlContext : .MergeCells = False : End With
  848. xlSheet.Range("E11:F" & N1).Select()
  849. With myExcel.Selection : .HorizontalAlignment = xlCenter : .VerticalAlignment = xlCenter : .WrapText = False : .Orientation = 0 : .AddIndent = False : .IndentLevel = 0
  850. .ShrinkToFit = False : .ReadingOrder = xlContext : .MergeCells = False : End With
  851. xlSheet.Range("G11:H" & N1).Select() : myExcel.Selection.NumberFormatLocal = "#,##0"
  852. xlSheet.Range("H" & N1 + 2).Select() : myExcel.Selection.NumberFormatLocal = "#,##0"
  853. xlSheet.Range("H" & N1 + 2 & ":H" & N1 + 5).Select()
  854. myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  855. With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  856. With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  857. With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  858. With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  859. myExcel.Selection.Borders(xlInsideVertical).LineStyle = xlNone
  860. With myExcel.Selection.Borders(xlInsideHorizontal) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  861. myExcel.ActiveWindow.DisplayGridlines = False
  862. xlSheet.Range("A3:H3").Select()
  863. myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  864. myExcel.Selection.Borders(xlEdgeLeft).LineStyle = xlNone : myExcel.Selection.Borders(xlEdgeTop).LineStyle = xlNone
  865. With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  866. myExcel.Selection.Borders(xlEdgeRight).LineStyle = xlNone : myExcel.Selection.Borders(xlInsideVertical).LineStyle = xlNone : myExcel.Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
  867. xlSheet.Range("G7:G9").Select()
  868. With myExcel.Selection : .HorizontalAlignment = xlLeft : .VerticalAlignment = xlCenter : .WrapText = False : .Orientation = 0 : .AddIndent = False : .IndentLevel = 0 : .ShrinkToFit = False
  869. .ReadingOrder = xlContext : .MergeCells = False : End With
  870. xlSheet.Range("A10:H" & N1).Select()
  871. myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  872. With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  873. With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  874. With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  875. With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  876. With myExcel.Selection.Borders(xlInsideVertical) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  877. With myExcel.Selection.Borders(xlInsideHorizontal) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  878. xlSheet.Range("A" & N1 + 2 & ":H" & N1 + 5).Select()
  879. myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  880. With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  881. With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  882. With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  883. With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  884. With myExcel.Selection.Borders(xlInsideHorizontal) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  885. xlSheet.Range("A" & N1 + 6 & ":B" & N1 + 9).Select()
  886. myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  887. With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  888. With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  889. With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  890. With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  891. myExcel.Selection.Borders(xlInsideVertical).LineStyle = xlNone
  892. xlSheet.Range("C" & N1 + 6 & ":C" & N1 + 9).Select()
  893. myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  894. With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  895. With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  896. With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  897. With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  898. myExcel.Selection.Borders(xlInsideVertical).LineStyle = xlNone
  899. xlSheet.Range("D" & N1 + 6 & ":E" & N1 + 9).Select()
  900. myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  901. With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  902. With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  903. With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  904. With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  905. myExcel.Selection.Borders(xlInsideVertical).LineStyle = xlNone
  906. xlSheet.Range("F" & N1 + 6 & ":G" & N1 + 9).Select()
  907. myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  908. With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  909. With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  910. With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  911. With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  912. myExcel.Selection.Borders(xlInsideVertical).LineStyle = xlNone
  913. xlSheet.Range("H" & N1 + 6 & ":H" & N1 + 9).Select()
  914. myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  915. With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  916. With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  917. With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  918. With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  919. myExcel.Selection.Borders(xlInsideVertical).LineStyle = xlNone
  920. xlSheet.Rows("5:9").Select
  921. With myExcel.Selection.Font : .Name = "微軟正黑體" : .Size = 10 : .Strikethrough = False : .Superscript = False : .Subscript = False : .OutlineFont = False : .Shadow = False
  922. .Underline = xlUnderlineStyleNone : .ThemeColor = xlThemeColorLight1 : .TintAndShade = 0 : .ThemeFont = xlThemeFontNone : End With
  923. xlSheet.Rows("11:" & N1).Select
  924. With myExcel.Selection.Font : .Name = "微軟正黑體" : .Size = 10 : .Strikethrough = False : .Superscript = False : .Subscript = False : .OutlineFont = False : .Shadow = False
  925. .Underline = xlUnderlineStyleNone : .ThemeColor = xlThemeColorLight1 : .TintAndShade = 0 : .ThemeFont = xlThemeFontNone : End With
  926. xlSheet.Range("A10:H10").Select()
  927. myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  928. With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  929. With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  930. With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  931. With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  932. With myExcel.Selection.Borders(xlInsideVertical) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  933. myExcel.Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
  934. With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  935. With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  936. With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  937. With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  938. With myExcel.Selection.Borders(xlInsideVertical) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  939. myExcel.Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
  940. xlSheet.Range("A11:H" & N1).Select()
  941. myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  942. With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  943. With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  944. With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  945. With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  946. With myExcel.Selection.Borders(xlInsideVertical) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlHairline : End With
  947. With myExcel.Selection.Borders(xlInsideHorizontal) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlHairline : End With
  948. xlSheet.Range("B11:D" & N1).Select()
  949. myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  950. With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  951. With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  952. With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  953. With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  954. With myExcel.Selection.Borders(xlInsideVertical) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlHairline : End With
  955. With myExcel.Selection.Borders(xlInsideHorizontal) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlHairline : End With
  956. xlSheet.Range("F11:F" & N1).Select()
  957. myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  958. With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  959. With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  960. With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  961. With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  962. myExcel.Selection.Borders(xlInsideVertical).LineStyle = xlNone
  963. With myExcel.Selection.Borders(xlInsideHorizontal) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlHairline : End With
  964. xlSheet.Range("H11:H" & N1).Select()
  965. myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  966. With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  967. With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  968. With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  969. With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  970. myExcel.Selection.Borders(xlInsideVertical).LineStyle = xlNone
  971. With myExcel.Selection.Borders(xlInsideHorizontal) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlHairline : End With
  972. xlSheet.Range("A" & N1 + 2 & ":H" & N1 + 5).Select()
  973. myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  974. With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  975. With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  976. With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  977. With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  978. With myExcel.Selection.Borders(xlInsideHorizontal) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlHairline : End With
  979. xlSheet.Range("H" & N1 + 2 & ":H" & N1 + 5).Select()
  980. myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  981. With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  982. With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  983. With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  984. With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
  985. myExcel.Selection.Borders(xlInsideVertical).LineStyle = xlNone
  986. With myExcel.Selection.Borders(xlInsideHorizontal) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlHairline : End With
  987. xlSheet.Range("G" & N1 + 2 & ":G" & N1 + 5).Select()
  988. With myExcel.Selection : .HorizontalAlignment = xlRight : .VerticalAlignment = xlCenter : .WrapText = False : .Orientation = 0 : .AddIndent = False
  989. .IndentLevel = 0 : .ShrinkToFit = False : .ReadingOrder = xlContext : .MergeCells = False : End With
  990. xlSheet.Range("F" & N1 + 8 & ":G" & N1 + 8).Select()
  991. With myExcel.Selection : .HorizontalAlignment = xlCenter : .VerticalAlignment = xlCenter : .WrapText = False : .Orientation = 0 : .AddIndent = False
  992. .IndentLevel = 0 : .ShrinkToFit = False : .ReadingOrder = xlContext : .MergeCells = False : End With : myExcel.Selection.Merge
  993. xlSheet.Range("F" & N1 + 9 & ":G" & N1 + 9).Select()
  994. With myExcel.Selection : .HorizontalAlignment = xlCenter : .VerticalAlignment = xlCenter : .WrapText = False : .Orientation = 0 : .AddIndent = False
  995. .IndentLevel = 0 : .ShrinkToFit = False : .ReadingOrder = xlContext : .MergeCells = False : End With : myExcel.Selection.Merge
  996. xlSheet.Range("A" & N1 + 11 & ":H" & N1 + N2 + 11).Select()
  997. myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  998. With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  999. With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  1000. With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  1001. With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
  1002. myExcel.Selection.Borders(xlInsideVertical).LineStyle = xlNone
  1003. myExcel.Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
  1004. End Sub
  1005. Private Sub CC(ByVal myExcel As Microsoft.Office.Interop.Excel.Application, ByVal xlSheet As Worksheet)
  1006. Clipboard.SetDataObject(合約報價單.PictureBox1.Image)
  1007. xlSheet.Range("A1").Select()
  1008. myExcel.ActiveSheet.Pictures.Paste.Select
  1009. myExcel.Selection.ShapeRange.Height = 93
  1010. myExcel.Selection.ShapeRange.IncrementLeft(50)
  1011. myExcel.Selection.ShapeRange.IncrementTop(0)
  1012. End Sub
  1013. Private Sub DD(ByVal myExcel As Microsoft.Office.Interop.Excel.Application, ByVal xlSheet As Worksheet)
  1014. Clipboard.SetDataObject(合約報價單.PictureBox2.Image)
  1015. xlSheet.Range("A1").Select()
  1016. myExcel.ActiveSheet.Pictures.Paste.Select
  1017. myExcel.Selection.ShapeRange.Height = 93
  1018. myExcel.Selection.ShapeRange.IncrementLeft(50)
  1019. myExcel.Selection.ShapeRange.IncrementTop(0)
  1020. End Sub
  1021. Private Sub FF(ByVal myExcel As Application, ByVal xlSheet As Worksheet)
  1022. If 選擇公司_cb.Text = "PT HONG XHE INDUSTRIAL" Then
  1023. myExcel.ActiveSheet.PageSetup.CenterHeaderPicture.Filename =
  1024. Str01 & "\HXBWLOG.png"
  1025. Else
  1026. myExcel.ActiveSheet.PageSetup.CenterHeaderPicture.Filename =
  1027. Str01 & "\PGSBWLOG.png"
  1028. End If
  1029. myExcel.Application.PrintCommunication = False
  1030. With myExcel.ActiveSheet.PageSetup : .PrintTitleRows = "$1:$10" : .PrintTitleColumns = "" : End With
  1031. myExcel.Application.PrintCommunication = True : myExcel.ActiveSheet.PageSetup.PrintArea = "" : myExcel.Application.PrintCommunication = False
  1032. With myExcel.ActiveSheet.PageSetup : .LeftHeader = ""
  1033. .CenterHeader = "" & Chr(10) & "" & Chr(10) & "" & Chr(10) & "" & Chr(10) & "" & Chr(10) & "" & Chr(10) & "" & Chr(10) & "" & Chr(10) & "&G"
  1034. .RightHeader = "" : .LeftFooter = "" : .CenterFooter = "第 &P 頁,共 &N 頁" : .RightFooter = ""
  1035. .LeftMargin = myExcel.Application.InchesToPoints(0.236220472440945) : .RightMargin = myExcel.Application.InchesToPoints(0.236220472440945)
  1036. .TopMargin = myExcel.Application.InchesToPoints(0.748031496062992) : .BottomMargin = myExcel.Application.InchesToPoints(0.748031496062992)
  1037. .HeaderMargin = myExcel.Application.InchesToPoints(0.31496062992126) : .FooterMargin = myExcel.Application.InchesToPoints(0.31496062992126)
  1038. .PrintHeadings = False : .PrintGridlines = False : .CenterHorizontally = False : .CenterVertically = False : .Draft = False
  1039. .FirstPageNumber = xlAutomatic : .BlackAndWhite = False : .Zoom = False : .FitToPagesWide = 1 : .FitToPagesTall = False : .OddAndEvenPagesHeaderFooter = False
  1040. .DifferentFirstPageHeaderFooter = False : .ScaleWithDocHeaderFooter = True : .AlignMarginsHeaderFooter = True : .EvenPage.LeftHeader.Text = ""
  1041. .EvenPage.CenterHeader.Text = "" : .EvenPage.RightHeader.Text = "" : .EvenPage.LeftFooter.Text = "" : .EvenPage.CenterFooter.Text = ""
  1042. .EvenPage.RightFooter.Text = "" : .FirstPage.LeftHeader.Text = "" : .FirstPage.CenterHeader.Text = "" : .FirstPage.RightHeader.Text = ""
  1043. .FirstPage.LeftFooter.Text = "" : .FirstPage.CenterFooter.Text = "" : .FirstPage.RightFooter.Text = ""
  1044. End With : myExcel.Application.PrintCommunication = True
  1045. End Sub
  1046. Private Sub 風格()
  1047. If 深色風格 = False Then
  1048. Me.BackColor = SystemColors.Control : SplitContainer1.BackColor = SystemColors.Control
  1049. 選擇公司_lb.ForeColor = Color.Blue : 文字_lb.ForeColor = Color.Purple
  1050. 已生未核准的採購單_lb.ForeColor = Color.Black : 簡稱_lb.ForeColor = Color.Black : Label1.ForeColor = Color.Black : Label2.ForeColor = Color.Black
  1051. Label3.ForeColor = Color.Black : Label6.ForeColor = Color.Black : Label12.ForeColor = Color.Black : Label11.ForeColor = Color.Black
  1052. Label8.ForeColor = Color.Black : Label5.ForeColor = Color.Black : Label7.ForeColor = Color.Black : 總金額_lb.ForeColor = Color.Black
  1053. Label4.ForeColor = Color.Black : 小間距_ch.ForeColor = Color.Black : 中間距_ch.ForeColor = Color.Black : 大間距_ch.ForeColor = Color.Black
  1054. 詳細資料2_ch.ForeColor = Color.Black : 稅金計算_ch.ForeColor = Color.Black : 單價_rb.ForeColor = Color.Black : 日期_rb.ForeColor = Color.Black
  1055. 長條圖_ch.ForeColor = Color.Black : 折線圖_ch.ForeColor = Color.Black : Label9.ForeColor = Color.Black
  1056. 全部選擇_bt.BackColor = Color.White : 全部選擇_bt.ForeColor = Color.Olive : 全部取消_bt.BackColor = Color.White : 全部取消_bt.ForeColor = Color.Olive
  1057. 語轉扭_bt.BackColor = Color.White : 語轉扭_bt.ForeColor = Color.Black
  1058. 供應商簡稱_tb.BackColor = Color.White : 供應商簡稱_tb.ForeColor = Color.Black : 地址_tb.BackColor = Color.White : 地址_tb.ForeColor = Color.Black
  1059. 電話_tb.BackColor = Color.White : 電話_tb.ForeColor = Color.Black : 採購單號_tb.BackColor = Color.White : 採購單號_tb.ForeColor = Color.Black
  1060. 折扣比_tb.BackColor = Color.White : 折扣比_tb.ForeColor = Color.Black : 折扣金額_tb.BackColor = Color.White : 折扣金額_tb.ForeColor = Color.Black
  1061. 傳真_tb.BackColor = Color.White : 傳真_tb.ForeColor = Color.Black : 稅金比_tb.BackColor = Color.White : 稅金比_tb.ForeColor = Color.Black
  1062. 稅金金額_tb.BackColor = Color.White : 稅金金額_tb.ForeColor = Color.Black : 郵件_tb.BackColor = Color.White : 郵件_tb.ForeColor = Color.Black
  1063. 使用地點_tb.BackColor = Color.White : 使用地點_tb.ForeColor = Color.Black : 總金額_tb.BackColor = Color.White : 總金額_tb.ForeColor = Color.Black
  1064. 選擇公司_cb.BackColor = Color.White : 選擇公司_cb.ForeColor = Color.Black : 類別管理_cb.BackColor = Color.White : 類別管理_cb.ForeColor = Color.Black
  1065. 廠商_cb.BackColor = Color.White : 廠商_cb.ForeColor = Color.Black : 手動號碼_tb.BackColor = Color.White : 手動號碼_tb.ForeColor = Color.Black
  1066. 清空供應商_bt.BackColor = Color.White : 生成採購單_bt.BackColor = Color.White : 列印成詢價單_bt.BackColor = Color.White : 分析工具_bt.BackColor = Color.White
  1067. 採購單_dgv.EnableHeadersVisualStyles = True : 採購單_dgv.BackgroundColor = Color.White : 採購單_dgv.ColumnHeadersDefaultCellStyle.ForeColor = Color.Black
  1068. 採購單_dgv.AlternatingRowsDefaultCellStyle.BackColor = Color.LightPink
  1069. 採購單_dgv.DefaultCellStyle.BackColor = Color.White : 採購單_dgv.DefaultCellStyle.ForeColor = Color.Black
  1070. 明細_dgv.EnableHeadersVisualStyles = True : 明細_dgv.BackgroundColor = Color.White : 明細_dgv.ColumnHeadersDefaultCellStyle.ForeColor = Color.Black
  1071. 明細_dgv.AlternatingRowsDefaultCellStyle.BackColor = Color.LightBlue
  1072. 明細_dgv.DefaultCellStyle.BackColor = Color.White : 明細_dgv.DefaultCellStyle.ForeColor = Color.Black
  1073. 料號_dgv.EnableHeadersVisualStyles = True : 料號_dgv.BackgroundColor = Color.White : 料號_dgv.ColumnHeadersDefaultCellStyle.ForeColor = Color.Black
  1074. 料號_dgv.AlternatingRowsDefaultCellStyle.BackColor = Color.LightGray
  1075. 料號_dgv.DefaultCellStyle.BackColor = Color.White : 料號_dgv.DefaultCellStyle.ForeColor = Color.Black
  1076. Else
  1077. Me.BackColor = Color.Black : SplitContainer1.BackColor = Color.Black
  1078. 選擇公司_lb.ForeColor = Color.LightBlue : 文字_lb.ForeColor = Color.Violet
  1079. 已生未核准的採購單_lb.ForeColor = Color.White : 簡稱_lb.ForeColor = Color.White : Label1.ForeColor = Color.White : Label2.ForeColor = Color.White
  1080. Label3.ForeColor = Color.White : Label6.ForeColor = Color.White : Label12.ForeColor = Color.White : Label11.ForeColor = Color.White
  1081. Label8.ForeColor = Color.White : Label5.ForeColor = Color.White : Label7.ForeColor = Color.White : 總金額_lb.ForeColor = Color.White
  1082. Label4.ForeColor = Color.White : 小間距_ch.ForeColor = Color.White : 中間距_ch.ForeColor = Color.White : 大間距_ch.ForeColor = Color.White
  1083. 詳細資料2_ch.ForeColor = Color.White : 稅金計算_ch.ForeColor = Color.White : 單價_rb.ForeColor = Color.White : 日期_rb.ForeColor = Color.White
  1084. 長條圖_ch.ForeColor = Color.White : 折線圖_ch.ForeColor = Color.White : Label9.ForeColor = Color.Black
  1085. 全部選擇_bt.BackColor = Color.DimGray : 全部選擇_bt.ForeColor = Color.Yellow : 全部取消_bt.BackColor = Color.DimGray : 全部取消_bt.ForeColor = Color.Yellow
  1086. 語轉扭_bt.BackColor = Color.DimGray : 語轉扭_bt.ForeColor = Color.White
  1087. 供應商簡稱_tb.BackColor = Color.DimGray : 供應商簡稱_tb.ForeColor = Color.White : 地址_tb.BackColor = Color.DimGray : 地址_tb.ForeColor = Color.White
  1088. 電話_tb.BackColor = Color.DimGray : 電話_tb.ForeColor = Color.White : 採購單號_tb.BackColor = Color.DimGray : 採購單號_tb.ForeColor = Color.White
  1089. 折扣比_tb.BackColor = Color.DimGray : 折扣比_tb.ForeColor = Color.White : 折扣金額_tb.BackColor = Color.DimGray : 折扣金額_tb.ForeColor = Color.White
  1090. 傳真_tb.BackColor = Color.DimGray : 傳真_tb.ForeColor = Color.White : 稅金比_tb.BackColor = Color.DimGray : 稅金比_tb.ForeColor = Color.White
  1091. 稅金金額_tb.BackColor = Color.DimGray : 稅金金額_tb.ForeColor = Color.White : 郵件_tb.BackColor = Color.DimGray : 郵件_tb.ForeColor = Color.White
  1092. 使用地點_tb.BackColor = Color.DimGray : 使用地點_tb.ForeColor = Color.White : 總金額_tb.BackColor = Color.DimGray : 總金額_tb.ForeColor = Color.White
  1093. 選擇公司_cb.BackColor = Color.DimGray : 選擇公司_cb.ForeColor = Color.White : 類別管理_cb.BackColor = Color.DimGray : 類別管理_cb.ForeColor = Color.White
  1094. 廠商_cb.BackColor = Color.DimGray : 廠商_cb.ForeColor = Color.White : 手動號碼_tb.BackColor = Color.DimGray : 手動號碼_tb.ForeColor = Color.White
  1095. 清空供應商_bt.BackColor = Color.DimGray : 生成採購單_bt.BackColor = Color.DimGray : 列印成詢價單_bt.BackColor = Color.DimGray : 分析工具_bt.BackColor = Color.DimGray
  1096. 採購單_dgv.EnableHeadersVisualStyles = False : 採購單_dgv.BackgroundColor = Color.DimGray : 採購單_dgv.ColumnHeadersDefaultCellStyle.ForeColor = Color.White
  1097. 採購單_dgv.ColumnHeadersDefaultCellStyle.BackColor = Color.Black : 採購單_dgv.AlternatingRowsDefaultCellStyle.BackColor = Color.DimGray
  1098. 採購單_dgv.DefaultCellStyle.BackColor = Color.Black : 採購單_dgv.DefaultCellStyle.ForeColor = Color.White
  1099. 明細_dgv.EnableHeadersVisualStyles = False : 明細_dgv.BackgroundColor = Color.DimGray : 明細_dgv.ColumnHeadersDefaultCellStyle.ForeColor = Color.White
  1100. 明細_dgv.ColumnHeadersDefaultCellStyle.BackColor = Color.Black : 明細_dgv.AlternatingRowsDefaultCellStyle.BackColor = Color.DimGray
  1101. 明細_dgv.DefaultCellStyle.BackColor = Color.Black : 明細_dgv.DefaultCellStyle.ForeColor = Color.White
  1102. 料號_dgv.EnableHeadersVisualStyles = False : 料號_dgv.BackgroundColor = Color.DimGray : 料號_dgv.ColumnHeadersDefaultCellStyle.ForeColor = Color.White
  1103. 料號_dgv.ColumnHeadersDefaultCellStyle.BackColor = Color.Black : 料號_dgv.AlternatingRowsDefaultCellStyle.BackColor = Color.DimGray
  1104. 料號_dgv.DefaultCellStyle.BackColor = Color.Black : 料號_dgv.DefaultCellStyle.ForeColor = Color.White
  1105. End If
  1106. End Sub
  1107. End Class