Geen omschrijving
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 58KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955
  1. Imports CefSharp
  2. Imports Microsoft.Office.Interop.Excel.XlUnderlineStyle
  3. Imports Microsoft.Office.Interop.Excel.Constants
  4. Imports Microsoft.Office.Interop.Excel.XlBordersIndex
  5. Imports Microsoft.Office.Interop.Excel.XlLineStyle
  6. Imports Microsoft.Office.Interop.Excel.XlBorderWeight
  7. Imports Microsoft.Office.Interop.Excel.XlThemeFont
  8. Imports Microsoft.Office.Interop.Excel.XlThemeColor
  9. Imports Microsoft.Office.Interop.Excel.XlWindowState
  10. Imports Microsoft.Office.Interop.Excel
  11. Imports Telerik.Collections.Generic
  12. Public Class 採購付款控制表
  13. Dim 對話框(8) As String
  14. Dim N2 As Integer
  15. Dim xlApp As Microsoft.Office.Interop.Excel.Application : Dim xlBook As Microsoft.Office.Interop.Excel.Workbook : Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet
  16. Private Function IsVerticalScrollBarVisible(dgv As DataGridView) As Boolean
  17. Return dgv.FirstDisplayedScrollingRowIndex > 0 OrElse dgv.DisplayedRowCount(False) < dgv.Rows.Count
  18. End Function
  19. Private Sub Set_總表1()
  20. Dim ds2 As New DataSet : ds2.Clear() : 合計1_dgv.DataSource = Nothing
  21. 合計1_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
  22. 合計1_dgv.ColumnHeadersHeight = 40 : 合計1_dgv.RowTemplate.Height = 25
  23. 合計1_dgv.AllowUserToAddRows = False : 合計1_dgv.ColumnHeadersVisible = False : 合計1_dgv.Enabled = False
  24. SQL_採購付款總表1()
  25. da.Fill(ds2) : 合計1_dgv.DataSource = ds2.Tables(0) : conn.Close()
  26. 合計1_dgv.Columns(0).FillWeight = 35 : 合計1_dgv.Columns(1).FillWeight = 230 : 合計1_dgv.Columns(4).Visible = False
  27. 合計1_dgv.Columns(2).DefaultCellStyle.Format = "#,##0" : 合計1_dgv.Columns(3).DefaultCellStyle.Format = "#,##0" : 合計1_dgv.Columns(5).DefaultCellStyle.Format = "#,##0"
  28. 合計1_dgv.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
  29. 合計1_dgv.Columns(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
  30. 合計1_dgv.Columns(5).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
  31. 合計1_dgv.Columns(1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
  32. If 合計1_dgv.Rows.Count > 0 Then
  33. 合計1_dgv(0, 0).Value = "" : 合計1_dgv(1, 0).Value = "Total : " : 合計1_dgv(2, 0).Value = 0 : 合計1_dgv(3, 0).Value = 0 : 合計1_dgv(5, 0).Value = 0
  34. End If
  35. Dim ds1 As New DataSet : ds1.Clear() : 總表_dgv.DataSource = Nothing
  36. 總表_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
  37. 總表_dgv.ColumnHeadersHeight = 40 : 總表_dgv.RowTemplate.Height = 25
  38. 總表_dgv.AllowUserToAddRows = False
  39. If HX_ch.Checked = True And PGS_ch.Checked = False And ALL_ch.Checked = False Then : SQL2 = " AND (採購付款控制表.採購單號 LIKE N'HX%')"
  40. ElseIf HX_ch.Checked = False And PGS_ch.Checked = True And ALL_ch.Checked = False Then : SQL2 = " AND (採購付款控制表.採購單號 LIKE N'PGS%')"
  41. ElseIf HX_ch.Checked = False And PGS_ch.Checked = False And ALL_ch.Checked = True Then : SQL2 = "" : End If
  42. If 年份_cb.Text = "ALL" Then : SQL_採購付款總表("%%") : Else : SQL_採購付款總表(年份_cb.Text) : End If
  43. da.Fill(ds1) : 總表_dgv.DataSource = ds1.Tables(0) : conn.Close()
  44. 總表_dgv.Columns(0).FillWeight = 35 : 總表_dgv.Columns(1).FillWeight = 230 : 總表_dgv.Columns(4).Visible = False
  45. For i As Integer = 0 To 總表_dgv.Rows.Count - 1 : 總表_dgv(0, i).Value = i + 1 : Next
  46. 總表_dgv.Columns(2).DefaultCellStyle.Format = "#,##0" : 總表_dgv.Columns(3).DefaultCellStyle.Format = "#,##0" : 總表_dgv.Columns(5).DefaultCellStyle.Format = "#,##0"
  47. 總表_dgv.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
  48. 總表_dgv.Columns(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
  49. 總表_dgv.Columns(5).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
  50. For i As Integer = 0 To 總表_dgv.Rows.Count - 1
  51. 總表_dgv(5, i).Value = 總表_dgv(2, i).Value - 總表_dgv(3, i).Value
  52. 合計1_dgv(2, 0).Value += 總表_dgv(2, i).Value : 合計1_dgv(3, 0).Value += 總表_dgv(3, i).Value : 合計1_dgv(5, 0).Value += 總表_dgv(5, i).Value
  53. Next
  54. '-------------------------合計DGV變形----------------------------------------------------------------------------------------------------------------------
  55. If IsVerticalScrollBarVisible(總表_dgv) Then : 合計1_dgv.Size = New Size(總表_dgv.Size.Width - 17, 合計1_dgv.Size.Height)
  56. Else : 合計1_dgv.Size = New Size(總表_dgv.Size.Width, 合計1_dgv.Size.Height) : End If
  57. End Sub
  58. Private Sub Set_明細1()
  59. Dim ds2 As New DataSet : 合計2_dgv.DataSource = Nothing : ds2.Clear()
  60. 合計2_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
  61. 合計2_dgv.ColumnHeadersHeight = 40 : 合計2_dgv.RowTemplate.Height = 25
  62. 合計2_dgv.AllowUserToAddRows = False : 合計2_dgv.ColumnHeadersVisible = False : 合計2_dgv.Enabled = False
  63. SQL_採購付款清單1()
  64. da.Fill(ds2) : 合計2_dgv.DataSource = ds2.Tables(0) : conn.Close()
  65. 合計2_dgv.Columns(6).DefaultCellStyle.Format = "#,##0" : 合計2_dgv.Columns(7).DefaultCellStyle.Format = "#,##0"
  66. 合計2_dgv.Columns(11).DefaultCellStyle.Format = "#,##0" : 合計2_dgv.Columns(16).DefaultCellStyle.Format = "#,##0"
  67. 合計2_dgv.Columns(6).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
  68. 合計2_dgv.Columns(7).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
  69. 合計2_dgv.Columns(11).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
  70. 合計2_dgv.Columns(16).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
  71. 合計2_dgv.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
  72. 合計2_dgv.Columns(8).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
  73. 合計2_dgv.Columns(9).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
  74. 合計2_dgv.Columns(10).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
  75. 合計2_dgv.Columns(0).Visible = False : 合計2_dgv.Columns(1).FillWeight = 30 : 合計2_dgv.Columns(12).Visible = False : 合計2_dgv.Columns(13).Visible = False
  76. 合計2_dgv.Columns(14).Visible = False : 合計2_dgv.Columns(15).Visible = False : 合計2_dgv.Columns(4).Visible = False : 合計2_dgv.Columns(3).FillWeight = 160
  77. 合計2_dgv.Columns(5).FillWeight = 160 : 合計2_dgv.Columns(10).FillWeight = 160 : 合計2_dgv.Columns(17).Visible = False
  78. If 合計2_dgv.Rows.Count > 0 Then
  79. 合計2_dgv(6, 0).Value = 0 : 合計2_dgv(7, 0).Value = 0 : 合計2_dgv(11, 0).Value = 0 : 合計2_dgv(16, 0).Value = 0
  80. 合計2_dgv(1, 0).Value = "" : 合計2_dgv(2, 0).Value = "" : 合計2_dgv(3, 0).Value = "" : 合計2_dgv(5, 0).Value = "" : 合計2_dgv(8, 0).Value = "" : 合計2_dgv(9, 0).Value = ""
  81. 合計2_dgv(10, 0).Value = ""
  82. End If
  83. Dim ds1 As New DataSet : 明細_dgv.DataSource = Nothing : ds1.Clear()
  84. 明細_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
  85. 明細_dgv.ColumnHeadersHeight = 40 : 明細_dgv.RowTemplate.Height = 25
  86. 明細_dgv.AllowUserToAddRows = False
  87. If HX_ch.Checked = True And PGS_ch.Checked = False And ALL_ch.Checked = False Then : SQL2 = " AND (採購付款控制表.採購單號 LIKE N'HX%')"
  88. ElseIf HX_ch.Checked = False And PGS_ch.Checked = True And ALL_ch.Checked = False Then : SQL2 = " AND (採購付款控制表.採購單號 LIKE N'PGS%')"
  89. ElseIf HX_ch.Checked = False And PGS_ch.Checked = False And ALL_ch.Checked = True Then : SQL2 = "" : End If
  90. If 年份_cb.Text = "ALL" Then : SQL_採購付款清單(供應商編碼_tb.Text, "%%") : Else : SQL_採購付款清單(供應商編碼_tb.Text, 年份_cb.Text) : End If
  91. da.Fill(ds1) : 明細_dgv.DataSource = ds1.Tables(0) : conn.Close()
  92. 明細_dgv.EditMode = DataGridViewEditMode.EditOnEnter
  93. 明細_dgv.Columns(1).ReadOnly = True : 明細_dgv.Columns(5).ReadOnly = True : 明細_dgv.Columns(6).ReadOnly = True : 明細_dgv.Columns(10).ReadOnly = True
  94. 明細_dgv.Columns(16).ReadOnly = True
  95. 明細_dgv.Columns(6).DefaultCellStyle.Format = "#,##0" : 明細_dgv.Columns(7).DefaultCellStyle.Format = "#,##0"
  96. 明細_dgv.Columns(11).DefaultCellStyle.Format = "#,##0" : 明細_dgv.Columns(16).DefaultCellStyle.Format = "#,##0"
  97. 明細_dgv.Columns(6).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
  98. 明細_dgv.Columns(7).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
  99. 明細_dgv.Columns(11).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
  100. 明細_dgv.Columns(16).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
  101. 明細_dgv.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
  102. 明細_dgv.Columns(8).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
  103. 明細_dgv.Columns(9).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
  104. 明細_dgv.Columns(10).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
  105. 明細_dgv.Columns(0).Visible = False : 明細_dgv.Columns(1).FillWeight = 30 : 明細_dgv.Columns(12).Visible = False : 明細_dgv.Columns(13).Visible = False
  106. 明細_dgv.Columns(14).Visible = False : 明細_dgv.Columns(15).Visible = False : 明細_dgv.Columns(4).Visible = False : 明細_dgv.Columns(3).FillWeight = 160
  107. 明細_dgv.Columns(5).FillWeight = 160 : 明細_dgv.Columns(10).FillWeight = 160 : 明細_dgv.Columns(17).Visible = False
  108. For j As Integer = 0 To 8 : 明細_dgv.Columns(j).DefaultCellStyle.BackColor = Color.AntiqueWhite : Next
  109. For j As Integer = 9 To 16 : 明細_dgv.Columns(j).DefaultCellStyle.BackColor = Color.PaleTurquoise : Next
  110. For i As Integer = 0 To 明細_dgv.Rows.Count - 1
  111. 明細_dgv(1, i).Value = i + 1
  112. If IsDBNull(明細_dgv("稅", i).Value) = True Then : 明細_dgv("稅", i).Value = False : End If
  113. If IsDBNull(明細_dgv("金額", i).Value) = True Then : 明細_dgv("金額", i).Value = 0 : End If
  114. If IsDBNull(明細_dgv("DISC", i).Value) = True Then : 明細_dgv("DISC", i).Value = 0 : End If
  115. If IsDBNull(明細_dgv(5, i).Value) = True Then : 明細_dgv(5, i).Value = 0 : End If
  116. If 明細_dgv("稅", i).Value = True Then
  117. 明細_dgv(6, i).Value = CDbl(明細_dgv("金額", i).Value.ToString) - (CDbl(明細_dgv("金額", i).Value.ToString) * CDbl(明細_dgv("DISC", i).Value) / 100) +
  118. ((CDbl(明細_dgv("金額", i).Value.ToString) - (CDbl(明細_dgv("金額", i).Value.ToString) * CDbl(明細_dgv("DISC", i).Value) / 100)) *
  119. CDbl(明細_dgv("PPN", i).Value.ToString) / 100)
  120. Else : 明細_dgv(6, i).Value = CDbl(明細_dgv("金額", i).Value.ToString) - (CDbl(明細_dgv("金額", i).Value.ToString) * CDbl(明細_dgv("DISC", i).Value) / 100) : End If
  121. If 登入人級別 = "00" Then
  122. If 明細_dgv(5, i).Value = "" Then : 明細_dgv(5, i).Value = 明細_dgv(4, i).Value.ToString : End If
  123. End If
  124. If CDbl(明細_dgv(7, i).Value.ToString) = 0 Then : 明細_dgv(7, i).Value = CDbl(明細_dgv(6, i).Value.ToString) : End If
  125. If i = 0 Then : 明細_dgv(16, i).Value = 明細_dgv(7, i).Value - 明細_dgv(11, i).Value
  126. Else : 明細_dgv(16, i).Value = 明細_dgv(7, i).Value - 明細_dgv(11, i).Value + 明細_dgv(16, i - 1).Value : End If
  127. If IsDBNull(明細_dgv(17, i).Value) = True Then : 明細_dgv(17, i).Value = "" : End If
  128. If 明細_dgv(17, i).Value.ToString = "" Then : 明細_dgv(10, i).Value = ""
  129. ElseIf CDbl(明細_dgv(17, i).Value.ToString) = 0 Then : 明細_dgv(10, i).Value = 系統語言字典("G000-379-" & 語言)
  130. ElseIf CDbl(明細_dgv(17, i).Value.ToString) = 1 Then : 明細_dgv(10, i).Value = 系統語言字典("G000-380-" & 語言)
  131. ElseIf CDbl(明細_dgv(17, i).Value.ToString) = 2 Then : 明細_dgv(10, i).Value = 系統語言字典("G000-381-" & 語言) : End If
  132. Next
  133. Dim 上一個歸零 As Boolean : Dim H As Integer : H = 明細_dgv.Rows.Count - 1
  134. For i As Integer = 0 To 明細_dgv.Rows.Count - 1
  135. If H = 明細_dgv.Rows.Count - 1 Then : Else
  136. If CLng(明細_dgv(16, H + 1).Value.ToString) = 0 Then : 上一個歸零 = True : Else : 上一個歸零 = False : End If
  137. If 上一個歸零 = True Then : 明細_dgv(16, H).Value = 0 : End If
  138. End If : H -= 1
  139. 合計2_dgv(6, 0).Value += 明細_dgv(6, i).Value : 合計2_dgv(7, 0).Value += 明細_dgv(7, i).Value
  140. 合計2_dgv(11, 0).Value += 明細_dgv(11, i).Value : 合計2_dgv(16, 0).Value = 明細_dgv(16, i).Value
  141. Next
  142. '-------------------------合計DGV變形----------------------------------------------------------------------------------------------------------------------
  143. If IsVerticalScrollBarVisible(明細_dgv) Then : 合計2_dgv.Size = New Size(明細_dgv.Size.Width - 17, 合計2_dgv.Size.Height)
  144. Else : 合計2_dgv.Size = New Size(明細_dgv.Size.Width, 合計2_dgv.Size.Height) : End If
  145. End Sub
  146. Private Sub Set_清單1()
  147. Dim ds1 As New DataSet : 已核准_dgv.DataSource = Nothing : ds1.Clear()
  148. 已核准_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
  149. 已核准_dgv.ColumnHeadersHeight = 40 : 已核准_dgv.RowTemplate.Height = 25
  150. 已核准_dgv.AllowUserToAddRows = False
  151. Dim 供應商編號 As String
  152. If 供應商_cb.Text = 對話框(8) Then : 供應商編號 = "%%" : Else
  153. For i As Integer = 0 To 供應商資料_dgv.Rows.Count - 1
  154. If 供應商_cb.Text = 供應商資料_dgv.Rows(i).Cells(1).Value.ToString Then
  155. 供應商編號 = 供應商資料_dgv.Rows(i).Cells(0).Value.ToString : Exit For
  156. End If
  157. Next
  158. End If
  159. SQL_採購單已核准(供應商編號)
  160. da.Fill(ds1) : 已核准_dgv.DataSource = ds1.Tables(0) : conn.Close()
  161. 已核准_dgv.Columns(1).FillWeight = 55 : 已核准_dgv.Columns(2).FillWeight = 40 : 已核准_dgv.Columns(3).Visible = False
  162. For i As Integer = 0 To 已核准_dgv.Rows.Count - 1
  163. If 已核准_dgv.Rows(i).Cells(1).Value.ToString = "" Or Strings.Left(已核准_dgv.Rows(i).Cells(1).Value.ToString, 2) = "NA" Then
  164. 已核准_dgv.Rows(i).Cells(1).Value = 已核准_dgv.Rows(i).Cells(2).Value.ToString
  165. End If
  166. Next
  167. End Sub
  168. Private Sub Set_供應商清單()
  169. Dim ds1 As New DataSet : 供應商資料_dgv.DataSource = Nothing : ds1.Clear()
  170. 供應商資料_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
  171. 供應商資料_dgv.ColumnHeadersHeight = 40 : 供應商資料_dgv.RowTemplate.Height = 30 : 供應商資料_dgv.AllowUserToAddRows = False
  172. If 公司類別_cb.Text = "PT" Then : SQL2 = " AND 公司名 LIKE N'PT%'"
  173. ElseIf 公司類別_cb.Text = "CV" Then : SQL2 = " AND 公司名 LIKE N'CV%'"
  174. ElseIf 公司類別_cb.Text = "UD" Then : SQL2 = " AND 公司名 LIKE N'UD%'"
  175. 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
  176. SQL_採購歷史檔案_供應商清單() : da.Fill(ds1) : 供應商資料_dgv.DataSource = ds1.Tables(0) : conn.Close()
  177. 供應商_cb.Items.Clear() : 供應商_cb.Items.Add(對話框(8))
  178. For i As Integer = 0 To 供應商資料_dgv.Rows.Count - 1 : 供應商_cb.Items.Add(供應商資料_dgv.Rows(i).Cells(1).Value.ToString) : Next
  179. End Sub
  180. Private Sub 公司類別_cb_SelectedIndexChanged(sender As Object, e As EventArgs) Handles 公司類別_cb.SelectedIndexChanged
  181. Set_供應商清單() : 供應商_cb.Text = ""
  182. End Sub
  183. Private Sub 年份_cb_SelectedIndexChanged(sender As Object, e As EventArgs) Handles 年份_cb.SelectedIndexChanged
  184. If 首次開啟 = False Then : Set_總表1() : Set_清單1() : End If
  185. End Sub
  186. Private Sub 年分清單()
  187. SQL_年份清單() : 年份_cb.Items.Clear() : 年份_cb.Items.Add("ALL") : While (dr.Read()) : 年份_cb.Items.Add(dr("年份")) : End While : conn.Close()
  188. End Sub
  189. Private Sub 帳務說明()
  190. 帳務說明_ch.Items.Clear() : 帳務說明_ch.Items.Add("NA")
  191. 帳務說明_ch.Items.Add(系統語言字典("G000-379-" & 語言)) : 帳務說明_ch.Items.Add(系統語言字典("G000-380-" & 語言))
  192. If 登入人級別 = "00" Then : 帳務說明_ch.Items.Add(系統語言字典("G000-381-" & 語言)) : End If
  193. End Sub
  194. Private Sub 語言轉換讀取()
  195. If 系統語言 = "繁體中文" Then : 語言 = "CH" : ElseIf 系統語言 = "English" Then : 語言 = "EN" : ElseIf 系統語言 = "Indonesian" Then : 語言 = "IN" : End If
  196. Me.Text = 系統語言字典("H001-291-" & 語言) : ToolTip1.ToolTipTitle = 系統語言字典("G000-108-" & 語言) : Label2.Text = 系統語言字典("H314-101-" & 語言)
  197. 關鍵字_lb.Text = 系統語言字典("H104-106-" & 語言) : 採購單資料_lb.Text = 系統語言字典("G000-392-" & 語言) : 對話框(8) = 系統語言字典("G000-263-" & 語言)
  198. 表頭(0) = 系統語言字典("H206-145-" & 語言) : 表頭(1) = 系統語言字典("H206-146-" & 語言) : 表頭(2) = 系統語言字典("H402-108-" & 語言) : 表頭(3) = 系統語言字典("H319-101-" & 語言)
  199. 表頭(4) = 系統語言字典("G000-384-" & 語言) : 表頭(5) = 系統語言字典("H319-100-" & 語言) : 表頭(6) = 系統語言字典("H299-109-" & 語言) : 表頭(7) = 系統語言字典("H304-103-" & 語言)
  200. 表頭(8) = 系統語言字典("H314-120-" & 語言) : 表頭(9) = 系統語言字典("H314-121-" & 語言) : 表頭(10) = 系統語言字典("H904-104-" & 語言) : 表頭(11) = 系統語言字典("G000-385-" & 語言)
  201. 表頭(12) = 系統語言字典("H204-112-" & 語言) : 表頭(13) = 系統語言字典("G000-386-" & 語言) : 表頭(14) = 系統語言字典("G000-387-" & 語言) : 表頭(15) = 系統語言字典("G000-388-" & 語言)
  202. 表頭(16) = 系統語言字典("G000-389-" & 語言) : 表頭(17) = 系統語言字典("G000-390-" & 語言) : 表頭(18) = 系統語言字典("H208-111-" & 語言) : 表頭(19) = 系統語言字典("G000-391-" & 語言)
  203. 表頭(20) = 系統語言字典("G000-393-" & 語言)
  204. End Sub
  205. Private Sub 新增_bt_MouseEnter(sender As Object, e As EventArgs) Handles 新增_bt.MouseEnter
  206. ToolTip1.SetToolTip(Me.新增_bt, 系統語言字典("H304-101-" & 語言))
  207. End Sub
  208. Private Sub 存檔_bt_MouseEnter(sender As Object, e As EventArgs) Handles 存檔_bt.MouseEnter
  209. ToolTip1.SetToolTip(Me.存檔_bt, 系統語言字典("H605-100-" & 語言))
  210. End Sub
  211. Private Sub 刪除_tb_MouseEnter(sender As Object, e As EventArgs) Handles 刪除_tb.MouseEnter
  212. ToolTip1.SetToolTip(Me.刪除_tb, 系統語言字典("G000-112-" & 語言))
  213. End Sub
  214. Private Sub 查詢_bt_MouseEnter(sender As Object, e As EventArgs) Handles 查詢_bt.MouseEnter
  215. ToolTip1.SetToolTip(Me.查詢_bt, 系統語言字典("H104-106-" & 語言))
  216. End Sub
  217. Private Sub 新增資料_bt_MouseEnter(sender As Object, e As EventArgs) Handles 新增資料_bt.MouseEnter
  218. ToolTip1.SetToolTip(Me.新增資料_bt, 系統語言字典("G000-383-" & 語言))
  219. End Sub
  220. Private Sub 新增空白_tb_MouseEnter(sender As Object, e As EventArgs) Handles 新增空白_tb.MouseEnter
  221. ToolTip1.SetToolTip(Me.新增空白_tb, 系統語言字典("H115-103-" & 語言))
  222. End Sub
  223. Private Sub 採購付款控制表_Load(sender As Object, e As EventArgs) Handles MyBase.Load
  224. Me.MdiParent = HX_PGS_ERP_SYS : Me.WindowState = 2 : Me.AutoScroll = True
  225. H(210) = True : 介面 = "H210" : 語言轉換讀取() : 顯示說明(999, False)
  226. Panel1.SendToBack() : 視窗3_pl.Visible = False : 首次開啟 = True
  227. If 登入人級別 = "00" Then : 補資料_bt.Visible = True : ALL_ch.Visible = True : Else : 補資料_bt.Visible = False : ALL_ch.Visible = False : End If
  228. End Sub
  229. Private Sub 採購付款控制表_Shown(sender As Object, e As EventArgs) Handles MyBase.Shown
  230. 計算分流01.Enabled = True
  231. End Sub
  232. Private Sub 計算分流01_Tick(sender As Object, e As EventArgs) Handles 計算分流01.Tick
  233. 帳務說明() : 年分清單() : 年份_cb.Text = Year(Today)
  234. Set_總表1() : 全部資料_cb.Checked = True : 公司類別_cb.SelectedIndex = 0 : 首次開啟 = False : 計算分流01.Enabled = False
  235. End Sub
  236. Private Sub 採購付款控制表_Closed(sender As Object, e As EventArgs) Handles MyBase.Closed
  237. H(210) = False : MyMod.虛擬桌面開啟()
  238. End Sub
  239. Private Sub 採購付款控制表_SizeChanged(sender As Object, e As EventArgs) Handles MyBase.SizeChanged
  240. MyMod.清單字體大小調整()
  241. 已核准_dgv.RowsDefaultCellStyle.Font = New System.Drawing.Font("微軟正黑體", HX_PGS_ERP_SYS.字體_NUD.Value)
  242. 明細_dgv.RowsDefaultCellStyle.Font = New System.Drawing.Font("微軟正黑體", HX_PGS_ERP_SYS.字體_NUD.Value)
  243. 總表_dgv.RowsDefaultCellStyle.Font = New System.Drawing.Font("微軟正黑體", HX_PGS_ERP_SYS.字體_NUD.Value)
  244. End Sub
  245. Private Sub 登入閒置控制_MouseMove(sender As Object, e As EventArgs) Handles MyBase.MouseMove
  246. timeNow = 0 : HX_PGS_ERP_SYS.Timer1.Enabled = False : HX_PGS_ERP_SYS.Timer1.Enabled = True
  247. End Sub
  248. Private Sub 登入閒置控制_KeyPress(sender As Object, e As EventArgs) Handles MyBase.KeyPress
  249. timeNow = 0 : HX_PGS_ERP_SYS.Timer1.Enabled = False : HX_PGS_ERP_SYS.Timer1.Enabled = True
  250. End Sub
  251. Private Sub 語轉扭_bt_Click(sender As Object, e As EventArgs) Handles 語轉扭_bt.Click
  252. 介面 = "H210" : 語言轉換讀取() : Set_總表1() : Set_清單1()
  253. End Sub
  254. Private Sub 已核准_dgv_RowPostPaint(ByVal sender As Object, ByVal e As DataGridViewRowPostPaintEventArgs) Handles 已核准_dgv.RowPostPaint
  255. Dim linePen As New Pen(Color.Blue, 2)
  256. If e.RowIndex = 已核准_dgv.Rows.Count - 1 Then : Exit Sub : Else
  257. If 已核准_dgv(0, e.RowIndex).Value.ToString <> 已核准_dgv(0, e.RowIndex + 1).Value.ToString Then
  258. Dim startX As Integer = IIf(已核准_dgv.RowHeadersVisible, 已核准_dgv.RowHeadersWidth, 0) : Dim startY As Integer = e.RowBounds.Top + e.RowBounds.Height - 1
  259. Dim endX As Integer = startX + 已核准_dgv.Columns.GetColumnsWidth(DataGridViewElementStates.Visible) - 已核准_dgv.HorizontalScrollingOffset
  260. e.Graphics.DrawLine(linePen, startX, startY, endX, startY) : Exit Sub
  261. End If
  262. End If
  263. End Sub
  264. Private Sub 已核准_dgv_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles 已核准_dgv.CellClick
  265. If e.RowIndex = -1 Then : Else
  266. 手動號碼_tb.Text = 已核准_dgv("手寫單號", e.RowIndex).Value.ToString : 供應商編碼_tb.Text = 已核准_dgv("供應商編號", e.RowIndex).Value.ToString
  267. 採購單號_tb.Text = 已核准_dgv("採購單號", e.RowIndex).Value.ToString : 年份_cb.Text = Strings.Mid(已核准_dgv("採購單號", e.RowIndex).Value.ToString, 7, 4)
  268. If 登入人級別 = "00" Then
  269. Dim A As String = Strings.Mid(已核准_dgv("採購單號", e.RowIndex).Value.ToString, 7, 4) & "/" &
  270. Strings.Mid(已核准_dgv("採購單號", e.RowIndex).Value.ToString, 11, 2) & "/25"
  271. Dim dateValue As DateTime = DateTime.Parse(A) : Dim newDate As DateTime = dateValue.AddMonths(+1) : A = newDate.ToString("yyyy/MM/dd") : 申請日期_dtp.Value = A
  272. End If
  273. End If
  274. End Sub
  275. Private Sub 總表_dgv_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles 總表_dgv.CellClick
  276. If e.RowIndex = -1 Then : Else
  277. 供應商_lb.Text = 總表_dgv(1, e.RowIndex).Value.ToString : 供應商編碼_tb.Text = 總表_dgv("供應商編號", e.RowIndex).Value.ToString : Set_明細1()
  278. End If
  279. End Sub
  280. Private Sub 明細_dgv_RowPostPaint(ByVal sender As Object, ByVal e As DataGridViewRowPostPaintEventArgs) Handles 明細_dgv.RowPostPaint
  281. Dim linePen As New Pen(Color.Blue, 2)
  282. If e.RowIndex = 明細_dgv.Rows.Count - 1 Then : Exit Sub : Else
  283. If 明細_dgv(8, e.RowIndex).Value.ToString <> 明細_dgv(8, e.RowIndex + 1).Value.ToString Then
  284. Dim startX As Integer = IIf(明細_dgv.RowHeadersVisible, 明細_dgv.RowHeadersWidth, 0) : Dim startY As Integer = e.RowBounds.Top + e.RowBounds.Height - 1
  285. Dim endX As Integer = startX + 明細_dgv.Columns.GetColumnsWidth(DataGridViewElementStates.Visible) - 明細_dgv.HorizontalScrollingOffset
  286. e.Graphics.DrawLine(linePen, startX, startY, endX, startY) : Exit Sub
  287. End If
  288. End If
  289. End Sub
  290. Private Sub 明細_dgv_CellContentCl(sender As Object, e As DataGridViewCellEventArgs) Handles 明細_dgv.CellEndEdit
  291. Try
  292. If e.RowIndex = -1 Then : Else
  293. 合計2_dgv(6, 0).Value = 0 : 合計2_dgv(7, 0).Value = 0 : 合計2_dgv(11, 0).Value = 0 : 合計2_dgv(16, 0).Value = 0
  294. For i As Integer = 0 To 明細_dgv.Rows.Count - 1
  295. If i = 0 Then : 明細_dgv(16, i).Value = 明細_dgv(7, i).Value - 明細_dgv(11, i).Value
  296. Else : 明細_dgv(16, i).Value = 明細_dgv(7, i).Value - 明細_dgv(11, i).Value + 明細_dgv(16, i - 1).Value : End If
  297. Next
  298. Dim 上一個歸零 As Boolean : Dim H As Integer : H = 明細_dgv.Rows.Count - 1
  299. For i As Integer = 0 To 明細_dgv.Rows.Count - 1
  300. If H = 明細_dgv.Rows.Count - 1 Then : Else
  301. If CLng(明細_dgv(16, H + 1).Value.ToString) = 0 Then : 上一個歸零 = True : Else : 上一個歸零 = False : End If
  302. If 上一個歸零 = True Then : 明細_dgv(16, H).Value = 0 : End If
  303. End If : H -= 1
  304. 合計2_dgv(6, 0).Value += 明細_dgv(6, i).Value : 合計2_dgv(7, 0).Value += 明細_dgv(7, i).Value
  305. 合計2_dgv(11, 0).Value += 明細_dgv(11, i).Value : 合計2_dgv(16, 0).Value = 明細_dgv(16, i).Value
  306. Next
  307. Dim EU(2) As String : EU(0) = 明細_dgv(2, e.RowIndex).Value.ToString : EU(1) = 明細_dgv(8, e.RowIndex).Value.ToString : EU(2) = 明細_dgv(9, e.RowIndex).Value.ToString
  308. If EU(0) = "" Then : Else : 日期S_dtp.Text = EU(0) : 明細_dgv.Rows(e.RowIndex).Cells(2).Value = Strings.Format(日期S_dtp.Value, "yyyy/MM/dd") : End If
  309. If EU(1) = "" Then : Else : 日期S_dtp.Text = EU(1) : 明細_dgv.Rows(e.RowIndex).Cells(8).Value = Strings.Format(日期S_dtp.Value, "yyyy/MM/dd") : End If
  310. If EU(2) = "" Then : Else : 日期S_dtp.Text = EU(2) : 明細_dgv.Rows(e.RowIndex).Cells(9).Value = Strings.Format(日期S_dtp.Value, "yyyy/MM/dd") : End If
  311. End If
  312. Catch ex As Exception : MGB(ex.Message, 1) : End Try
  313. End Sub
  314. Private Sub 明細_dgv_MouseUp(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles 明細_dgv.MouseUp
  315. If 明細_dgv.SelectedCells.Count > 0 Then
  316. Dim 位置 As Integer = 明細_dgv.SelectedCells(0).RowIndex : Dim 位置Y As Integer = 明細_dgv.SelectedCells(0).ColumnIndex
  317. 流水號_tb.Text = 明細_dgv(0, 位置).Value.ToString
  318. If 位置Y = 10 Then
  319. CB選擇清單(帳務說明_ch, 帳務說明_ch.Text)
  320. If 帳務說明_ch.SelectedIndex = 0 Then : 明細_dgv(17, 位置).Value = ""
  321. Else : 明細_dgv(17, 位置).Value = 帳務說明_ch.SelectedIndex - 1 : End If
  322. If 明細_dgv(17, 位置).Value.ToString = "" Then : 明細_dgv(10, 位置).Value = ""
  323. ElseIf CDbl(明細_dgv(17, 位置).Value.ToString) = 0 Then : 明細_dgv(10, 位置).Value = 系統語言字典("G000-379-" & 語言)
  324. ElseIf CDbl(明細_dgv(17, 位置).Value.ToString) = 1 Then : 明細_dgv(10, 位置).Value = 系統語言字典("G000-380-" & 語言)
  325. ElseIf CDbl(明細_dgv(17, 位置).Value.ToString) = 2 Then : 明細_dgv(10, 位置).Value = 系統語言字典("G000-381-" & 語言) : End If
  326. End If
  327. End If
  328. End Sub
  329. Private Sub 全部資料_cb_Click(sender As Object, e As EventArgs) Handles 全部資料_cb.Click
  330. Set_清單1()
  331. End Sub
  332. Private Sub 供應商_cb_Click(sender As Object, e As EventArgs) Handles 供應商_cb.Click
  333. CB選擇清單(供應商_cb, 供應商_cb.Text) : 供應商_lb.Text = 供應商_cb.Text : Set_清單1()
  334. End Sub
  335. Private Sub 新增_bt_Click(sender As Object, e As EventArgs) Handles 新增_bt.Click
  336. If 視窗3_pl.Visible = False Then
  337. 視窗3_pl.Location = New System.Drawing.Point((Me.Size.Width - 視窗3_pl.Size.Width) / 2, (Me.Size.Height - 視窗3_pl.Size.Height) / 2) : 視窗3_pl.Visible = True
  338. 視窗3_pl.BringToFront() : 供應商_cb.SelectedIndex = 0 : Set_清單1()
  339. Else : 視窗3_pl.Visible = False : End If
  340. End Sub
  341. Private Sub 新增資料_bt_Click(sender As Object, e As EventArgs) Handles 新增資料_bt.Click
  342. If 採購單號_tb.Text <> "" Then : SQL_採購付款採購單確認(採購單號_tb.Text)
  343. If dr.Read Then : MGB(系統語言字典("G000-376-" & 語言), 1) : Else : 訊息回應 = "" : INB(系統語言字典("G000-377-" & 語言))
  344. If 訊息回應 <> "" Then
  345. Dim 編號 As String : SQL_採購付款最後一筆資料()
  346. If dr.Read() Then : 編號 = dr("流水號").ToString : Else : 編號 = "ES00000000" : End If : conn.Close()
  347. Dim NUM1 As Integer = Double.Parse(Strings.Right(編號, 8)) + 1
  348. If NUM1 < 10 Then : 編號 = "ES" & "0000000" & NUM1 : ElseIf NUM1 > 9 And NUM1 < 100 Then : 編號 = "ES" & "000000" & NUM1
  349. ElseIf NUM1 > 99 And NUM1 < 1000 Then : 編號 = "ES" & "00000" & NUM1 : ElseIf NUM1 > 999 And NUM1 < 10000 Then : 編號 = "ES" & "0000" & NUM1
  350. ElseIf NUM1 > 9999 And NUM1 < 100000 Then : 編號 = "ES" & "000" & NUM1 : ElseIf NUM1 > 99999 And NUM1 < 1000000 Then : 編號 = "ES" & "00" & NUM1
  351. ElseIf NUM1 > 999999 And NUM1 < 10000000 Then : 編號 = "ES" & "0" & NUM1 : ElseIf NUM1 > 9999999 Then : 編號 = "ES" & NUM1 : End If
  352. 輸入日期.ShowDialog()
  353. SQL_採購付款資料新增(編號, 供應商編碼_tb.Text, Strings.Format(申請日期_dtp.Value, "yyyy/MM/dd"), 訊息回應, 採購單號_tb.Text, "0", PA, "", "", "0", 手動號碼_tb.Text)
  354. MGB(系統語言字典("G000-118-" & 語言), 1) : Set_總表1() : Set_明細1() : Set_清單1()
  355. Else : MGB(系統語言字典("G000-378-" & 語言), 1) : End If
  356. End If
  357. Else : MGB(系統語言字典("G000-117-" & 語言), 1) : End If
  358. End Sub
  359. Private Sub 新增空白_tb_Click(sender As Object, e As EventArgs) Handles 新增空白_tb.Click
  360. If 供應商編碼_tb.Text <> "" Then
  361. INB(系統語言字典("G000-377-" & 語言))
  362. If 訊息回應 <> "" Then
  363. Dim 編號 As String : SQL_採購付款最後一筆資料()
  364. If dr.Read() Then : 編號 = dr("流水號").ToString : Else : 編號 = "ES00000000" : End If : conn.Close()
  365. Dim NUM1 As Integer = Double.Parse(Strings.Right(編號, 8)) + 1
  366. If NUM1 < 10 Then : 編號 = "ES" & "0000000" & NUM1 : ElseIf NUM1 > 9 And NUM1 < 100 Then : 編號 = "ES" & "000000" & NUM1
  367. ElseIf NUM1 > 99 And NUM1 < 1000 Then : 編號 = "ES" & "00000" & NUM1 : ElseIf NUM1 > 999 And NUM1 < 10000 Then : 編號 = "ES" & "0000" & NUM1
  368. ElseIf NUM1 > 9999 And NUM1 < 100000 Then : 編號 = "ES" & "000" & NUM1 : ElseIf NUM1 > 99999 And NUM1 < 1000000 Then : 編號 = "ES" & "00" & NUM1
  369. ElseIf NUM1 > 999999 And NUM1 < 10000000 Then : 編號 = "ES" & "0" & NUM1 : ElseIf NUM1 > 9999999 Then : 編號 = "ES" & NUM1 : End If
  370. If HX_ch.Checked = True Then : 採購單號_tb.Text = "HX" : Else : 採購單號_tb.Text = "PGS" : End If
  371. 輸入日期.ShowDialog()
  372. SQL_採購付款資料新增(編號, 供應商編碼_tb.Text, Strings.Format(申請日期_dtp.Value, "yyyy/MM/dd"), 訊息回應, 採購單號_tb.Text, "0", PA, "", "", "0", 手動號碼_tb.Text)
  373. MGB(系統語言字典("G000-118-" & 語言), 1) : Set_總表1() : Set_明細1() : Set_清單1()
  374. Else : MGB(系統語言字典("G000-378-" & 語言), 1) : End If
  375. Else : MGB(系統語言字典("G000-117-" & 語言), 1) : End If
  376. End Sub
  377. Private Sub 存檔_bt_Click(sender As Object, e As EventArgs) Handles 存檔_bt.Click
  378. Dim 格式異常1 As Boolean = False : Dim 格式異常2 As Boolean = False
  379. For i As Integer = 0 To 明細_dgv.Rows.Count - 1
  380. Dim A As String = 明細_dgv(2, i).Value.ToString : Dim B As String = 明細_dgv(8, i).Value.ToString
  381. Dim format As String = "yyyy/MM/dd" : Dim parsedDate As DateTime
  382. If DateTime.TryParseExact(A, format, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, parsedDate) Then : Else
  383. 格式異常1 = True : Exit For
  384. End If
  385. If DateTime.TryParseExact(B, format, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, parsedDate) Then : Else
  386. 格式異常2 = True : Exit For
  387. End If
  388. Next
  389. If 格式異常1 = True Or 格式異常2 = True Then : MGB(系統語言字典("G000-119-" & 語言), 1)
  390. Else
  391. For i As Integer = 0 To 明細_dgv.Rows.Count - 1
  392. SQL_採購付款表修改(明細_dgv(0, i).Value.ToString, 明細_dgv(2, i).Value.ToString, 明細_dgv(3, i).Value.ToString, 明細_dgv(7, i).Value.ToString,
  393. 明細_dgv(8, i).Value.ToString, 明細_dgv(9, i).Value.ToString, 明細_dgv(17, i).Value.ToString, 明細_dgv(11, i).Value.ToString)
  394. Next : Set_總表1() : Set_明細1() : MGB(系統語言字典("G000-119-" & 語言), 1)
  395. End If
  396. End Sub
  397. Private Sub 刪除_tb_Click(sender As Object, e As EventArgs) Handles 刪除_tb.Click
  398. If 流水號_tb.Text <> "" Then : MGB(系統語言字典("G000-213-" & 語言), 2)
  399. If 訊息回應 = "YES" Then : SQL_採購付款表刪除(流水號_tb.Text) : Set_總表1() : Set_明細1() : MGB(系統語言字典("G000-120-" & 語言), 1) : 流水號_tb.Text = "" : End If
  400. End If
  401. End Sub
  402. Private Sub 補資料_bt_Click(sender As Object, e As EventArgs) Handles 補資料_bt.Click
  403. For i As Integer = 0 To 明細_dgv.Rows.Count - 1
  404. Dim A As String = 明細_dgv(2, i).Value.ToString
  405. Dim dateValue As DateTime = DateTime.Parse(A) ' 將字串轉換為 DateTime
  406. Dim lastDayOfMonth As DateTime = New DateTime(dateValue.Year, dateValue.Month, DateTime.DaysInMonth(dateValue.Year, dateValue.Month)) ' 計算當月最後一天
  407. 明細_dgv(8, i).Value = lastDayOfMonth.ToString("yyyy/MM/dd")
  408. Next
  409. End Sub
  410. Private Sub 查詢_bt_Click(sender As Object, e As EventArgs) Handles 查詢_bt.Click
  411. lastFoundRowIndex = -1 : lastFoundColIndex = -1 : 查詢DGV中的關鍵字_自動(已核准_dgv, 關鍵字_tb)
  412. If lastFoundRowIndex <> -1 Then
  413. 手動號碼_tb.Text = 已核准_dgv("手寫單號", lastFoundRowIndex).Value.ToString : 供應商編碼_tb.Text = 已核准_dgv("供應商編號", lastFoundRowIndex).Value.ToString
  414. 採購單號_tb.Text = 已核准_dgv("採購單號", lastFoundRowIndex).Value.ToString : 年份_cb.Text = Strings.Mid(已核准_dgv("採購單號", lastFoundRowIndex).Value.ToString, 7, 4)
  415. End If
  416. End Sub
  417. Private Sub 關鍵字_tb_TextChanged(sender As Object, e As EventArgs) Handles 關鍵字_tb.TextChanged
  418. lastFoundRowIndex = -1 : lastFoundColIndex = -1 : 查詢DGV中的關鍵字_自動(已核准_dgv, 關鍵字_tb)
  419. If lastFoundRowIndex <> -1 Then
  420. 手動號碼_tb.Text = 已核准_dgv("手寫單號", lastFoundRowIndex).Value.ToString : 供應商編碼_tb.Text = 已核准_dgv("供應商編號", lastFoundRowIndex).Value.ToString
  421. 採購單號_tb.Text = 已核准_dgv("採購單號", lastFoundRowIndex).Value.ToString : 年份_cb.Text = Strings.Mid(已核准_dgv("採購單號", lastFoundRowIndex).Value.ToString, 7, 4)
  422. End If
  423. End Sub
  424. Private Sub HX_ch_CheckedChanged(sender As Object, e As EventArgs) Handles HX_ch.Click
  425. HX_ch.Checked = True : PGS_ch.Checked = False : ALL_ch.Checked = False : 新增_bt.Enabled = True : 存檔_bt.Enabled = True : 新增資料_bt.Enabled = True : Set_總表1() : Set_明細1()
  426. End Sub
  427. Private Sub PGS_ch_CheckedChanged(sender As Object, e As EventArgs) Handles PGS_ch.Click
  428. HX_ch.Checked = False : PGS_ch.Checked = True : ALL_ch.Checked = False : 新增_bt.Enabled = True : 存檔_bt.Enabled = True : 新增資料_bt.Enabled = True : Set_總表1() : Set_明細1()
  429. End Sub
  430. Private Sub ALL_ch_CheckedChanged(sender As Object, e As EventArgs) Handles ALL_ch.Click
  431. HX_ch.Checked = False : PGS_ch.Checked = False : ALL_ch.Checked = True : 新增_bt.Enabled = False : 存檔_bt.Enabled = False : 新增資料_bt.Enabled = False : Set_總表1() : Set_明細1()
  432. End Sub
  433. '----------------------自創視窗滑鼠拖曳功能---------------------------------------------------------------------------------------------------------
  434. Dim OldX, OldY As Long : Dim drag As Boolean
  435. Private Sub CanceL3_bt_Click(sender As Object, e As EventArgs) Handles CanceL3_bt.Click
  436. If 視窗3_pl.Visible = False Then : 視窗3_pl.Visible = True : 視窗3_pl.BringToFront() : Else
  437. 視窗3_pl.Visible = False : End If
  438. End Sub
  439. Private Sub 視窗3_pl_MouseDown(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles 視窗3_pl.MouseDown
  440. If e.Button = MouseButtons.Left Then : OldX = e.X : OldY = e.Y : drag = True : End If
  441. End Sub
  442. Private Sub 視窗3_pl_MouseMove(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles 視窗3_pl.MouseMove
  443. If drag Then : 視窗3_pl.Left = 視窗3_pl.Left + e.X - OldX : 視窗3_pl.Top = 視窗3_pl.Top + e.Y - OldY : End If
  444. End Sub
  445. Private Sub 視窗3_pl_MouseUp(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles 視窗3_pl.MouseUp
  446. drag = False
  447. End Sub
  448. Private Sub 模擬點擊第幾行(rowIndex As Integer)
  449. ' 確保 rowIndex 在合法範圍內
  450. If rowIndex >= 0 AndAlso rowIndex < 總表_dgv.Rows.Count Then
  451. ' 設置當前的單元格 (假設點擊第一列的單元格)
  452. 總表_dgv.CurrentCell = 總表_dgv.Rows(rowIndex).Cells(0)
  453. ' 觸發 CellClick 事件
  454. Dim args As New DataGridViewCellEventArgs(0, rowIndex)
  455. 總表_dgv_CellContentClick(總表_dgv, args)
  456. End If
  457. End Sub
  458. Private Sub 列印_bt_Click(sender As Object, e As EventArgs) Handles 列印_bt.Click
  459. xlApp = CType(CreateObject("Excel.Application"), Application)
  460. xlBook = xlApp.Workbooks.Add : xlApp.DisplayAlerts = True
  461. xlApp.Visible = True : xlApp.Application.WindowState = xlMaximized
  462. xlSheet = CType(xlBook.Sheets(1), Worksheet)
  463. xlSheet.Activate()
  464. xlSheet.Name = "GREND TOTAL"
  465. Dim title As String = ""
  466. If 系統語言 = "繁體中文" Then
  467. title = "採購單未付清單"
  468. ElseIf 系統語言 = "English" Then
  469. title = "Outstanding Purchase Orders"
  470. ElseIf 系統語言 = "Indonesian" Then
  471. title = "Daftar Pesanan Pembelian yang Belum Dibayar"
  472. End If
  473. If HX_ch.Checked = True Then
  474. xlSheet.Cells(2, 4) = 年份_cb.Text & "-HX" & title
  475. ElseIf PGS_ch.Checked = True Then
  476. xlSheet.Cells(2, 4) = 年份_cb.Text & "-PGS" & title
  477. ElseIf ALL_ch.Checked = True Then
  478. xlSheet.Cells(2, 4) = 年份_cb.Text & "-HX&PGS" & title
  479. End If
  480. Dim a As Integer = 4
  481. For i As Integer = 0 To 總表_dgv.Columns.Count - 1
  482. If i = 0 Or i = 1 Or i = 5 Then
  483. ' 將每個欄位的名稱放入 Excel 的第3行對應的單元格中
  484. xlSheet.Cells(3, a) = 總表_dgv.Columns(i).Name
  485. a += 1
  486. End If
  487. Next
  488. Dim b As Integer = 1
  489. Dim num As New List(Of Integer)
  490. For rowIndex As Integer = 0 To 總表_dgv.Rows.Count - 1
  491. a = 4 ' 重置 a 為 1,以便從第一列開始寫入數據
  492. If Math.Round(CDbl(總表_dgv(5, rowIndex).Value)) <> 0 Then
  493. num.Add(rowIndex)
  494. xlSheet.Cells(b + 3, a) = b.ToString()
  495. For colIndex As Integer = 0 To 總表_dgv.Columns.Count - 1
  496. If colIndex = 1 Then
  497. ' 將每個欄位的名稱放入 Excel 的第3行對應的單元格中
  498. xlSheet.Cells(b + 3, a + 1) = 總表_dgv.Rows(rowIndex).Cells(colIndex).Value
  499. End If
  500. If colIndex = 5 Then
  501. Dim cellValue1 As Double = Val(總表_dgv.Rows(rowIndex).Cells(colIndex).Value.ToString())
  502. xlSheet.Cells(b + 3, a + 2) = Math.Round(cellValue1).ToString("#,##0")
  503. End If
  504. Next
  505. b += 1
  506. End If
  507. Next
  508. Dim 總計 As Double = Val(合計1_dgv(5, 0).Value.ToString())
  509. xlSheet.Cells(b + 3, 4) = "GRAND TOTAL" : xlSheet.Cells(b + 3, 6) = Math.Round(總計).ToString("#,##0")
  510. N2 = b + 3
  511. AA(xlApp, xlSheet)
  512. xlSheet.Protect(Password:="0003065795", AllowFormattingCells:=False, AllowFormattingColumns:=False, AllowFormattingRows:=False)
  513. For Each index As Integer In num
  514. 模擬點擊第幾行(index)
  515. 新增工作表(總表_dgv.Rows(index).Cells(1).Value.ToString())
  516. Next
  517. Dim firstSheet As Worksheet
  518. firstSheet = xlBook.Worksheets(1)
  519. firstSheet.Select()
  520. Dim desktopPath As String = Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
  521. Dim savePath As String = System.IO.Path.Combine(desktopPath, "採購單未付清單.xlsx")
  522. ' 保存 Excel 文件
  523. If System.IO.File.Exists(savePath) Then
  524. Dim result As DialogResult = MessageBox.Show("檔案已存在,是否要取代?", "檔案已存在", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly)
  525. If result = DialogResult.Yes Then
  526. System.IO.File.Delete(savePath)
  527. xlBook.SaveAs(savePath)
  528. ElseIf result = DialogResult.No Then
  529. ' 使用者選擇了 "否",不保存檔案
  530. MessageBox.Show("檔案未保存", "操作取消", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly)
  531. xlBook.Close(False) ' 關閉檔案但不保存
  532. xlApp.Quit()
  533. ReleaseComObject(xlSheet)
  534. ReleaseComObject(xlBook)
  535. ReleaseComObject(xlApp)
  536. Exit Sub
  537. ElseIf result = DialogResult.Cancel Then
  538. ' 使用者選擇了 "取消",取消操作
  539. MessageBox.Show("操作已取消", "取消", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly)
  540. xlBook.Close(False) ' 關閉檔案但不保存
  541. xlApp.Quit()
  542. ReleaseComObject(xlSheet)
  543. ReleaseComObject(xlBook)
  544. ReleaseComObject(xlApp)
  545. Exit Sub
  546. End If
  547. Else
  548. ' 如果檔案不存在,直接保存
  549. xlBook.SaveAs(savePath)
  550. End If
  551. ' 關閉並釋放 Excel 資源
  552. xlBook.Close()
  553. xlApp.Quit()
  554. ReleaseComObject(xlSheet)
  555. ReleaseComObject(xlBook)
  556. ReleaseComObject(xlApp)
  557. MsgBox("文件已保存到桌面!!")
  558. End Sub
  559. Private Sub 新增工作表(名稱 As String)
  560. Dim num As New List(Of Integer) From {1, 2, 3, 5, 6, 7, 8, 9, 10, 11, 16}
  561. xlSheet = CType(xlBook.Worksheets.Add(), Worksheet)
  562. xlSheet.Name = 名稱
  563. xlSheet.Move(After:=xlBook.Worksheets(xlBook.Worksheets.Count))
  564. xlSheet.Cells(1, 1) = 名稱
  565. Dim columnIndex As Integer = 1 ' 用來追蹤 Excel 中的列位置
  566. For Each index As Integer In num
  567. xlSheet.Cells(2, columnIndex) = 明細_dgv.Columns(index).Name
  568. columnIndex += 1
  569. Next
  570. Dim a As Integer = 3
  571. For rowIndex As Integer = 0 To 明細_dgv.Rows.Count - 1
  572. columnIndex = 1
  573. For Each index As Integer In num
  574. If columnIndex = 5 Or columnIndex = 6 Or columnIndex = 10 Or columnIndex = 11 Then
  575. ' 將每個欄位的名稱放入 Excel 的第3行對應的單元格中
  576. Dim cellValue1 As Double = Val(明細_dgv.Rows(rowIndex).Cells(index).Value.ToString())
  577. xlSheet.Cells(a, columnIndex) = Math.Round(cellValue1).ToString("#,##0")
  578. Else
  579. xlSheet.Cells(a, columnIndex) = 明細_dgv.Rows(rowIndex).Cells(index).Value
  580. End If
  581. columnIndex += 1
  582. Next
  583. a += 1
  584. Next
  585. N2 = a
  586. Dim con1 As Double = Val(合計2_dgv(6, 0).Value.ToString())
  587. Dim con2 As Double = Val(合計2_dgv(7, 0).Value.ToString())
  588. Dim con3 As Double = Val(合計2_dgv(11, 0).Value.ToString())
  589. Dim con4 As Double = Val(合計2_dgv(16, 0).Value.ToString())
  590. xlSheet.Cells(a, 5) = Math.Round(con1).ToString("#,##0")
  591. xlSheet.Cells(a, 6) = Math.Round(con2).ToString("#,##0")
  592. xlSheet.Cells(a, 10) = Math.Round(con3).ToString("#,##0")
  593. xlSheet.Cells(a, 11) = Math.Round(con4).ToString("#,##0")
  594. BB(xlApp, xlSheet)
  595. xlSheet.Protect(Password:="0003065795", AllowFormattingCells:=False, AllowFormattingColumns:=False, AllowFormattingRows:=False)
  596. End Sub
  597. Private Sub ReleaseComObject(ByVal obj As Object)
  598. Try
  599. System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
  600. obj = Nothing
  601. Catch ex As Exception
  602. obj = Nothing
  603. Finally
  604. GC.Collect()
  605. End Try
  606. End Sub
  607. Private Sub BB(ByVal myExcel As Microsoft.Office.Interop.Excel.Application, ByVal xlSheet As Worksheet)
  608. xlSheet.Cells.Select()
  609. With myExcel.Selection.Font
  610. .Name = "微軟正黑體"
  611. .Size = 11
  612. .Strikethrough = False
  613. .Superscript = False
  614. .Subscript = False
  615. .OutlineFont = False
  616. .Shadow = False
  617. .Underline = xlUnderlineStyleNone
  618. .ThemeColor = xlThemeColorLight1
  619. .TintAndShade = 0
  620. .ThemeFont = xlThemeFontNone
  621. End With
  622. xlSheet.Range("1:1").Select()
  623. With myExcel.Selection.Font
  624. .Name = "微軟正黑體"
  625. .Size = 16
  626. .Strikethrough = False
  627. .Superscript = False
  628. .Subscript = False
  629. .OutlineFont = False
  630. .Shadow = False
  631. .Underline = xlUnderlineStyleNone
  632. .ThemeColor = xlThemeColorLight1
  633. .TintAndShade = 0
  634. .ThemeFont = xlThemeFontNone
  635. End With
  636. xlSheet.Range("2:2," & N2.ToString() & ":" & N2.ToString()).Select()
  637. With myExcel.Selection.Font
  638. .Name = "微軟正黑體"
  639. .Size = 14
  640. .Strikethrough = False
  641. .Superscript = False
  642. .Subscript = False
  643. .OutlineFont = False
  644. .Shadow = False
  645. .Underline = xlUnderlineStyleNone
  646. .ThemeColor = xlThemeColorLight1
  647. .TintAndShade = 0
  648. .ThemeFont = xlThemeFontNone
  649. End With
  650. xlSheet.Range("A1:K1").Select()
  651. With myExcel.Selection.Interior
  652. .Pattern = xlSolid
  653. .PatternColorIndex = xlAutomatic
  654. .Color = 5296274
  655. .TintAndShade = 0
  656. .PatternTintAndShade = 0
  657. End With
  658. myExcel.Selection.Font.Bold = True
  659. xlSheet.Range("A2:G2,A" & N2.ToString() & ":G" & N2.ToString()).Select()
  660. With myExcel.Selection.Interior
  661. .Pattern = xlSolid
  662. .PatternColorIndex = xlAutomatic
  663. .ThemeColor = xlThemeColorAccent4
  664. .TintAndShade = 0.399975585192419
  665. .PatternTintAndShade = 0
  666. End With
  667. myExcel.Selection.Font.Bold = True
  668. xlSheet.Range("H2:K2,H" & N2.ToString() & ":K" & N2.ToString()).Select()
  669. With myExcel.Selection.Interior
  670. .Pattern = xlSolid
  671. .PatternColorIndex = xlAutomatic
  672. .ThemeColor = xlThemeColorAccent1
  673. .TintAndShade = 0.599993896298105
  674. .PatternTintAndShade = 0
  675. End With
  676. myExcel.Selection.Font.Bold = True
  677. xlSheet.Range("A1:K1").Select()
  678. With myExcel.Selection
  679. .HorizontalAlignment = xlCenter
  680. .VerticalAlignment = xlCenter
  681. .WrapText = False
  682. .Orientation = 0
  683. .AddIndent = False
  684. .IndentLevel = 0
  685. .ShrinkToFit = False
  686. .ReadingOrder = xlContext
  687. .MergeCells = False
  688. End With
  689. myExcel.Selection.Merge
  690. xlSheet.Range("A2:K2").Select()
  691. With myExcel.Selection
  692. .HorizontalAlignment = xlCenter
  693. .VerticalAlignment = xlCenter
  694. .WrapText = False
  695. .Orientation = 0
  696. .AddIndent = False
  697. .IndentLevel = 0
  698. .ShrinkToFit = False
  699. .ReadingOrder = xlContext
  700. .MergeCells = False
  701. End With
  702. xlSheet.Range("A3:D" & N2.ToString() & ",G3:I" & N2.ToString()).Select()
  703. With myExcel.Selection
  704. .HorizontalAlignment = xlCenter
  705. .VerticalAlignment = xlCenter
  706. .WrapText = False
  707. .Orientation = 0
  708. .AddIndent = False
  709. .IndentLevel = 0
  710. .ShrinkToFit = False
  711. .ReadingOrder = xlContext
  712. End With
  713. xlSheet.Range("E3:F" & N2.ToString() & ",J3:K" & N2.ToString()).Select()
  714. With myExcel.Selection
  715. .HorizontalAlignment = xlRight
  716. .VerticalAlignment = xlCenter
  717. .WrapText = False
  718. .Orientation = 0
  719. .AddIndent = False
  720. .IndentLevel = 0
  721. .ShrinkToFit = False
  722. .ReadingOrder = xlContext
  723. End With
  724. xlSheet.Rows(1).RowHeight = 45
  725. xlSheet.Rows(2).RowHeight = 36
  726. For rowIndex As Integer = 0 To N2
  727. xlSheet.Rows(3 + rowIndex).RowHeight = 27
  728. Next
  729. xlSheet.Columns("A:J").AutoFit()
  730. xlSheet.Range("A1:J" & N2.ToString).Select()
  731. myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
  732. myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  733. With myExcel.Selection.Borders(xlEdgeLeft)
  734. .LineStyle = xlContinuous
  735. .ColorIndex = 0
  736. .TintAndShade = 0
  737. .Weight = xlThin
  738. End With
  739. With myExcel.Selection.Borders(xlEdgeTop)
  740. .LineStyle = xlContinuous
  741. .ColorIndex = 0
  742. .TintAndShade = 0
  743. .Weight = xlThin
  744. End With
  745. With myExcel.Selection.Borders(xlEdgeBottom)
  746. .LineStyle = xlContinuous
  747. .ColorIndex = 0
  748. .TintAndShade = 0
  749. .Weight = xlThin
  750. End With
  751. With myExcel.Selection.Borders(xlEdgeRight)
  752. .LineStyle = xlContinuous
  753. .ColorIndex = 0
  754. .TintAndShade = 0
  755. .Weight = xlThin
  756. End With
  757. With myExcel.Selection.Borders(xlInsideVertical)
  758. .LineStyle = xlContinuous
  759. .ColorIndex = 0
  760. .TintAndShade = 0
  761. .Weight = xlThin
  762. End With
  763. With myExcel.Selection.Borders(xlInsideHorizontal)
  764. .LineStyle = xlContinuous
  765. .ColorIndex = 0
  766. .TintAndShade = 0
  767. .Weight = xlThin
  768. End With
  769. End Sub
  770. Private Sub AA(ByVal myExcel As Microsoft.Office.Interop.Excel.Application, ByVal xlSheet As Worksheet)
  771. xlSheet.Cells.Select()
  772. With myExcel.Selection.Font
  773. .Name = "微軟正黑體"
  774. .Size = 11
  775. .Strikethrough = False
  776. .Superscript = False
  777. .Subscript = False
  778. .OutlineFont = False
  779. .Shadow = False
  780. .Underline = xlUnderlineStyleNone
  781. .ThemeColor = xlThemeColorLight1
  782. .TintAndShade = 0
  783. .ThemeFont = xlThemeFontNone
  784. End With
  785. xlSheet.Range("2:2,3:3," & N2.ToString() & ":" & N2.ToString()).Select()
  786. With myExcel.Selection.Font
  787. .Name = "微軟正黑體"
  788. .Size = 14
  789. .Strikethrough = False
  790. .Superscript = False
  791. .Subscript = False
  792. .OutlineFont = False
  793. .Shadow = False
  794. .Underline = xlUnderlineStyleNone
  795. .ThemeColor = xlThemeColorLight1
  796. .TintAndShade = 0
  797. .ThemeFont = xlThemeFontNone
  798. End With
  799. xlSheet.Range("D2:F3,D" & N2.ToString() & ":F" & N2.ToString()).Select()
  800. With myExcel.Selection.Interior
  801. .Pattern = xlSolid
  802. .PatternColorIndex = xlAutomatic
  803. .ThemeColor = xlThemeColorAccent2
  804. .TintAndShade = 0.399975585192419
  805. .PatternTintAndShade = 0
  806. End With
  807. myExcel.Selection.Font.Bold = True
  808. xlSheet.Range("D2:F2").Select()
  809. With myExcel.Selection
  810. .HorizontalAlignment = xlCenter
  811. .VerticalAlignment = xlCenter
  812. .WrapText = False
  813. .Orientation = 0
  814. .AddIndent = False
  815. .IndentLevel = 0
  816. .ShrinkToFit = False
  817. .ReadingOrder = xlContext
  818. .MergeCells = False
  819. End With
  820. myExcel.Selection.Merge
  821. xlSheet.Range("D" & N2.ToString() & ":E" & N2.ToString()).Select()
  822. With myExcel.Selection
  823. .HorizontalAlignment = xlCenter
  824. .VerticalAlignment = xlCenter
  825. .WrapText = False
  826. .Orientation = 0
  827. .AddIndent = False
  828. .IndentLevel = 0
  829. .ShrinkToFit = False
  830. .ReadingOrder = xlContext
  831. .MergeCells = False
  832. End With
  833. myExcel.Selection.Merge
  834. xlSheet.Range("F4:F" & N2.ToString()).Select()
  835. With myExcel.Selection
  836. .HorizontalAlignment = xlRight
  837. .VerticalAlignment = xlCenter
  838. .WrapText = False
  839. .Orientation = 0
  840. .AddIndent = False
  841. .IndentLevel = 0
  842. .ShrinkToFit = False
  843. .ReadingOrder = xlContext
  844. End With
  845. xlSheet.Range("D3:D" & (N2 - 1).ToString() & ",E3:E" & (N2 - 1).ToString() & ",F3").Select()
  846. With myExcel.Selection
  847. .HorizontalAlignment = xlCenter
  848. .VerticalAlignment = xlCenter
  849. .WrapText = False
  850. .Orientation = 0
  851. .AddIndent = False
  852. .IndentLevel = 0
  853. .ShrinkToFit = False
  854. .ReadingOrder = xlContext
  855. End With
  856. xlSheet.Rows(2).RowHeight = 33
  857. xlSheet.Rows(3).RowHeight = 45
  858. For rowIndex As Integer = 0 To N2 - 3
  859. xlSheet.Rows(4 + rowIndex).RowHeight = 21
  860. Next
  861. xlSheet.Rows(N2).RowHeight = 33
  862. xlSheet.Columns("D:F").AutoFit()
  863. xlSheet.Range("D2:F" & N2.ToString).Select()
  864. myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
  865. myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  866. With myExcel.Selection.Borders(xlEdgeLeft)
  867. .LineStyle = xlContinuous
  868. .ColorIndex = 0
  869. .TintAndShade = 0
  870. .Weight = xlThin
  871. End With
  872. With myExcel.Selection.Borders(xlEdgeTop)
  873. .LineStyle = xlContinuous
  874. .ColorIndex = 0
  875. .TintAndShade = 0
  876. .Weight = xlThin
  877. End With
  878. With myExcel.Selection.Borders(xlEdgeBottom)
  879. .LineStyle = xlContinuous
  880. .ColorIndex = 0
  881. .TintAndShade = 0
  882. .Weight = xlThin
  883. End With
  884. With myExcel.Selection.Borders(xlEdgeRight)
  885. .LineStyle = xlContinuous
  886. .ColorIndex = 0
  887. .TintAndShade = 0
  888. .Weight = xlThin
  889. End With
  890. With myExcel.Selection.Borders(xlInsideVertical)
  891. .LineStyle = xlContinuous
  892. .ColorIndex = 0
  893. .TintAndShade = 0
  894. .Weight = xlThin
  895. End With
  896. With myExcel.Selection.Borders(xlInsideHorizontal)
  897. .LineStyle = xlContinuous
  898. .ColorIndex = 0
  899. .TintAndShade = 0
  900. .Weight = xlThin
  901. End With
  902. End Sub
  903. Private Shared Function NewMethod(xlBook As Workbook) As Worksheet
  904. Return CType(xlBook.Worksheets.Add, Worksheet)
  905. End Function
  906. End Class