123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955 |
- Imports CefSharp
- Imports Microsoft.Office.Interop.Excel.XlUnderlineStyle
- Imports Microsoft.Office.Interop.Excel.Constants
- Imports Microsoft.Office.Interop.Excel.XlBordersIndex
- Imports Microsoft.Office.Interop.Excel.XlLineStyle
- Imports Microsoft.Office.Interop.Excel.XlBorderWeight
- Imports Microsoft.Office.Interop.Excel.XlThemeFont
- Imports Microsoft.Office.Interop.Excel.XlThemeColor
- Imports Microsoft.Office.Interop.Excel.XlWindowState
- Imports Microsoft.Office.Interop.Excel
- Imports Telerik.Collections.Generic
-
- Public Class 採購付款控制表
- Dim 對話框(8) As String
- Dim N2 As Integer
- Dim xlApp As Microsoft.Office.Interop.Excel.Application : Dim xlBook As Microsoft.Office.Interop.Excel.Workbook : Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet
-
- Private Function IsVerticalScrollBarVisible(dgv As DataGridView) As Boolean
- Return dgv.FirstDisplayedScrollingRowIndex > 0 OrElse dgv.DisplayedRowCount(False) < dgv.Rows.Count
- End Function
- Private Sub Set_總表1()
- Dim ds2 As New DataSet : ds2.Clear() : 合計1_dgv.DataSource = Nothing
- 合計1_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
- 合計1_dgv.ColumnHeadersHeight = 40 : 合計1_dgv.RowTemplate.Height = 25
- 合計1_dgv.AllowUserToAddRows = False : 合計1_dgv.ColumnHeadersVisible = False : 合計1_dgv.Enabled = False
- SQL_採購付款總表1()
- da.Fill(ds2) : 合計1_dgv.DataSource = ds2.Tables(0) : conn.Close()
- 合計1_dgv.Columns(0).FillWeight = 35 : 合計1_dgv.Columns(1).FillWeight = 230 : 合計1_dgv.Columns(4).Visible = False
- 合計1_dgv.Columns(2).DefaultCellStyle.Format = "#,##0" : 合計1_dgv.Columns(3).DefaultCellStyle.Format = "#,##0" : 合計1_dgv.Columns(5).DefaultCellStyle.Format = "#,##0"
- 合計1_dgv.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
- 合計1_dgv.Columns(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
- 合計1_dgv.Columns(5).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
- 合計1_dgv.Columns(1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
- If 合計1_dgv.Rows.Count > 0 Then
- 合計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
- End If
-
-
-
-
- Dim ds1 As New DataSet : ds1.Clear() : 總表_dgv.DataSource = Nothing
- 總表_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
- 總表_dgv.ColumnHeadersHeight = 40 : 總表_dgv.RowTemplate.Height = 25
- 總表_dgv.AllowUserToAddRows = False
- If HX_ch.Checked = True And PGS_ch.Checked = False And ALL_ch.Checked = False Then : SQL2 = " AND (採購付款控制表.採購單號 LIKE N'HX%')"
- ElseIf HX_ch.Checked = False And PGS_ch.Checked = True And ALL_ch.Checked = False Then : SQL2 = " AND (採購付款控制表.採購單號 LIKE N'PGS%')"
- ElseIf HX_ch.Checked = False And PGS_ch.Checked = False And ALL_ch.Checked = True Then : SQL2 = "" : End If
- If 年份_cb.Text = "ALL" Then : SQL_採購付款總表("%%") : Else : SQL_採購付款總表(年份_cb.Text) : End If
- da.Fill(ds1) : 總表_dgv.DataSource = ds1.Tables(0) : conn.Close()
- 總表_dgv.Columns(0).FillWeight = 35 : 總表_dgv.Columns(1).FillWeight = 230 : 總表_dgv.Columns(4).Visible = False
- For i As Integer = 0 To 總表_dgv.Rows.Count - 1 : 總表_dgv(0, i).Value = i + 1 : Next
- 總表_dgv.Columns(2).DefaultCellStyle.Format = "#,##0" : 總表_dgv.Columns(3).DefaultCellStyle.Format = "#,##0" : 總表_dgv.Columns(5).DefaultCellStyle.Format = "#,##0"
- 總表_dgv.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
- 總表_dgv.Columns(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
- 總表_dgv.Columns(5).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
- For i As Integer = 0 To 總表_dgv.Rows.Count - 1
- 總表_dgv(5, i).Value = 總表_dgv(2, i).Value - 總表_dgv(3, i).Value
- 合計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
- Next
- '-------------------------合計DGV變形----------------------------------------------------------------------------------------------------------------------
- If IsVerticalScrollBarVisible(總表_dgv) Then : 合計1_dgv.Size = New Size(總表_dgv.Size.Width - 17, 合計1_dgv.Size.Height)
- Else : 合計1_dgv.Size = New Size(總表_dgv.Size.Width, 合計1_dgv.Size.Height) : End If
- End Sub
- Private Sub Set_明細1()
- Dim ds2 As New DataSet : 合計2_dgv.DataSource = Nothing : ds2.Clear()
- 合計2_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
- 合計2_dgv.ColumnHeadersHeight = 40 : 合計2_dgv.RowTemplate.Height = 25
- 合計2_dgv.AllowUserToAddRows = False : 合計2_dgv.ColumnHeadersVisible = False : 合計2_dgv.Enabled = False
- SQL_採購付款清單1()
- da.Fill(ds2) : 合計2_dgv.DataSource = ds2.Tables(0) : conn.Close()
- 合計2_dgv.Columns(6).DefaultCellStyle.Format = "#,##0" : 合計2_dgv.Columns(7).DefaultCellStyle.Format = "#,##0"
- 合計2_dgv.Columns(11).DefaultCellStyle.Format = "#,##0" : 合計2_dgv.Columns(16).DefaultCellStyle.Format = "#,##0"
- 合計2_dgv.Columns(6).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
- 合計2_dgv.Columns(7).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
- 合計2_dgv.Columns(11).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
- 合計2_dgv.Columns(16).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
- 合計2_dgv.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
- 合計2_dgv.Columns(8).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
- 合計2_dgv.Columns(9).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
- 合計2_dgv.Columns(10).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
- 合計2_dgv.Columns(0).Visible = False : 合計2_dgv.Columns(1).FillWeight = 30 : 合計2_dgv.Columns(12).Visible = False : 合計2_dgv.Columns(13).Visible = False
- 合計2_dgv.Columns(14).Visible = False : 合計2_dgv.Columns(15).Visible = False : 合計2_dgv.Columns(4).Visible = False : 合計2_dgv.Columns(3).FillWeight = 160
- 合計2_dgv.Columns(5).FillWeight = 160 : 合計2_dgv.Columns(10).FillWeight = 160 : 合計2_dgv.Columns(17).Visible = False
- If 合計2_dgv.Rows.Count > 0 Then
- 合計2_dgv(6, 0).Value = 0 : 合計2_dgv(7, 0).Value = 0 : 合計2_dgv(11, 0).Value = 0 : 合計2_dgv(16, 0).Value = 0
- 合計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 = ""
- 合計2_dgv(10, 0).Value = ""
- End If
-
-
- Dim ds1 As New DataSet : 明細_dgv.DataSource = Nothing : ds1.Clear()
- 明細_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
- 明細_dgv.ColumnHeadersHeight = 40 : 明細_dgv.RowTemplate.Height = 25
- 明細_dgv.AllowUserToAddRows = False
- If HX_ch.Checked = True And PGS_ch.Checked = False And ALL_ch.Checked = False Then : SQL2 = " AND (採購付款控制表.採購單號 LIKE N'HX%')"
- ElseIf HX_ch.Checked = False And PGS_ch.Checked = True And ALL_ch.Checked = False Then : SQL2 = " AND (採購付款控制表.採購單號 LIKE N'PGS%')"
- ElseIf HX_ch.Checked = False And PGS_ch.Checked = False And ALL_ch.Checked = True Then : SQL2 = "" : End If
- If 年份_cb.Text = "ALL" Then : SQL_採購付款清單(供應商編碼_tb.Text, "%%") : Else : SQL_採購付款清單(供應商編碼_tb.Text, 年份_cb.Text) : End If
- da.Fill(ds1) : 明細_dgv.DataSource = ds1.Tables(0) : conn.Close()
- 明細_dgv.EditMode = DataGridViewEditMode.EditOnEnter
- 明細_dgv.Columns(1).ReadOnly = True : 明細_dgv.Columns(5).ReadOnly = True : 明細_dgv.Columns(6).ReadOnly = True : 明細_dgv.Columns(10).ReadOnly = True
- 明細_dgv.Columns(16).ReadOnly = True
- 明細_dgv.Columns(6).DefaultCellStyle.Format = "#,##0" : 明細_dgv.Columns(7).DefaultCellStyle.Format = "#,##0"
- 明細_dgv.Columns(11).DefaultCellStyle.Format = "#,##0" : 明細_dgv.Columns(16).DefaultCellStyle.Format = "#,##0"
- 明細_dgv.Columns(6).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
- 明細_dgv.Columns(7).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
- 明細_dgv.Columns(11).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
- 明細_dgv.Columns(16).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
- 明細_dgv.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
- 明細_dgv.Columns(8).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
- 明細_dgv.Columns(9).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
- 明細_dgv.Columns(10).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
- 明細_dgv.Columns(0).Visible = False : 明細_dgv.Columns(1).FillWeight = 30 : 明細_dgv.Columns(12).Visible = False : 明細_dgv.Columns(13).Visible = False
- 明細_dgv.Columns(14).Visible = False : 明細_dgv.Columns(15).Visible = False : 明細_dgv.Columns(4).Visible = False : 明細_dgv.Columns(3).FillWeight = 160
- 明細_dgv.Columns(5).FillWeight = 160 : 明細_dgv.Columns(10).FillWeight = 160 : 明細_dgv.Columns(17).Visible = False
- For j As Integer = 0 To 8 : 明細_dgv.Columns(j).DefaultCellStyle.BackColor = Color.AntiqueWhite : Next
- For j As Integer = 9 To 16 : 明細_dgv.Columns(j).DefaultCellStyle.BackColor = Color.PaleTurquoise : Next
- For i As Integer = 0 To 明細_dgv.Rows.Count - 1
- 明細_dgv(1, i).Value = i + 1
- If IsDBNull(明細_dgv("稅", i).Value) = True Then : 明細_dgv("稅", i).Value = False : End If
- If IsDBNull(明細_dgv("金額", i).Value) = True Then : 明細_dgv("金額", i).Value = 0 : End If
- If IsDBNull(明細_dgv("DISC", i).Value) = True Then : 明細_dgv("DISC", i).Value = 0 : End If
- If IsDBNull(明細_dgv(5, i).Value) = True Then : 明細_dgv(5, i).Value = 0 : End If
- If 明細_dgv("稅", i).Value = True Then
- 明細_dgv(6, i).Value = CDbl(明細_dgv("金額", i).Value.ToString) - (CDbl(明細_dgv("金額", i).Value.ToString) * CDbl(明細_dgv("DISC", i).Value) / 100) +
- ((CDbl(明細_dgv("金額", i).Value.ToString) - (CDbl(明細_dgv("金額", i).Value.ToString) * CDbl(明細_dgv("DISC", i).Value) / 100)) *
- CDbl(明細_dgv("PPN", i).Value.ToString) / 100)
- Else : 明細_dgv(6, i).Value = CDbl(明細_dgv("金額", i).Value.ToString) - (CDbl(明細_dgv("金額", i).Value.ToString) * CDbl(明細_dgv("DISC", i).Value) / 100) : End If
- If 登入人級別 = "00" Then
- If 明細_dgv(5, i).Value = "" Then : 明細_dgv(5, i).Value = 明細_dgv(4, i).Value.ToString : End If
- End If
-
- If CDbl(明細_dgv(7, i).Value.ToString) = 0 Then : 明細_dgv(7, i).Value = CDbl(明細_dgv(6, i).Value.ToString) : End If
- If i = 0 Then : 明細_dgv(16, i).Value = 明細_dgv(7, i).Value - 明細_dgv(11, i).Value
- Else : 明細_dgv(16, i).Value = 明細_dgv(7, i).Value - 明細_dgv(11, i).Value + 明細_dgv(16, i - 1).Value : End If
- If IsDBNull(明細_dgv(17, i).Value) = True Then : 明細_dgv(17, i).Value = "" : End If
- If 明細_dgv(17, i).Value.ToString = "" Then : 明細_dgv(10, i).Value = ""
- ElseIf CDbl(明細_dgv(17, i).Value.ToString) = 0 Then : 明細_dgv(10, i).Value = 系統語言字典("G000-379-" & 語言)
- ElseIf CDbl(明細_dgv(17, i).Value.ToString) = 1 Then : 明細_dgv(10, i).Value = 系統語言字典("G000-380-" & 語言)
- ElseIf CDbl(明細_dgv(17, i).Value.ToString) = 2 Then : 明細_dgv(10, i).Value = 系統語言字典("G000-381-" & 語言) : End If
- Next
- Dim 上一個歸零 As Boolean : Dim H As Integer : H = 明細_dgv.Rows.Count - 1
- For i As Integer = 0 To 明細_dgv.Rows.Count - 1
- If H = 明細_dgv.Rows.Count - 1 Then : Else
- If CLng(明細_dgv(16, H + 1).Value.ToString) = 0 Then : 上一個歸零 = True : Else : 上一個歸零 = False : End If
- If 上一個歸零 = True Then : 明細_dgv(16, H).Value = 0 : End If
- End If : H -= 1
- 合計2_dgv(6, 0).Value += 明細_dgv(6, i).Value : 合計2_dgv(7, 0).Value += 明細_dgv(7, i).Value
- 合計2_dgv(11, 0).Value += 明細_dgv(11, i).Value : 合計2_dgv(16, 0).Value = 明細_dgv(16, i).Value
- Next
- '-------------------------合計DGV變形----------------------------------------------------------------------------------------------------------------------
- If IsVerticalScrollBarVisible(明細_dgv) Then : 合計2_dgv.Size = New Size(明細_dgv.Size.Width - 17, 合計2_dgv.Size.Height)
- Else : 合計2_dgv.Size = New Size(明細_dgv.Size.Width, 合計2_dgv.Size.Height) : End If
- End Sub
- Private Sub Set_清單1()
- Dim ds1 As New DataSet : 已核准_dgv.DataSource = Nothing : ds1.Clear()
- 已核准_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
- 已核准_dgv.ColumnHeadersHeight = 40 : 已核准_dgv.RowTemplate.Height = 25
- 已核准_dgv.AllowUserToAddRows = False
- Dim 供應商編號 As String
- If 供應商_cb.Text = 對話框(8) Then : 供應商編號 = "%%" : Else
- For i As Integer = 0 To 供應商資料_dgv.Rows.Count - 1
- If 供應商_cb.Text = 供應商資料_dgv.Rows(i).Cells(1).Value.ToString Then
- 供應商編號 = 供應商資料_dgv.Rows(i).Cells(0).Value.ToString : Exit For
- End If
- Next
- End If
- SQL_採購單已核准(供應商編號)
- da.Fill(ds1) : 已核准_dgv.DataSource = ds1.Tables(0) : conn.Close()
- 已核准_dgv.Columns(1).FillWeight = 55 : 已核准_dgv.Columns(2).FillWeight = 40 : 已核准_dgv.Columns(3).Visible = False
- For i As Integer = 0 To 已核准_dgv.Rows.Count - 1
- If 已核准_dgv.Rows(i).Cells(1).Value.ToString = "" Or Strings.Left(已核准_dgv.Rows(i).Cells(1).Value.ToString, 2) = "NA" Then
- 已核准_dgv.Rows(i).Cells(1).Value = 已核准_dgv.Rows(i).Cells(2).Value.ToString
- End If
- Next
- End Sub
- Private Sub Set_供應商清單()
- Dim ds1 As New DataSet : 供應商資料_dgv.DataSource = Nothing : ds1.Clear()
- 供應商資料_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
- 供應商資料_dgv.ColumnHeadersHeight = 40 : 供應商資料_dgv.RowTemplate.Height = 30 : 供應商資料_dgv.AllowUserToAddRows = False
- If 公司類別_cb.Text = "PT" Then : SQL2 = " AND 公司名 LIKE N'PT%'"
- ElseIf 公司類別_cb.Text = "CV" Then : SQL2 = " AND 公司名 LIKE N'CV%'"
- ElseIf 公司類別_cb.Text = "UD" Then : SQL2 = " AND 公司名 LIKE N'UD%'"
- 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
- SQL_採購歷史檔案_供應商清單() : da.Fill(ds1) : 供應商資料_dgv.DataSource = ds1.Tables(0) : conn.Close()
- 供應商_cb.Items.Clear() : 供應商_cb.Items.Add(對話框(8))
- For i As Integer = 0 To 供應商資料_dgv.Rows.Count - 1 : 供應商_cb.Items.Add(供應商資料_dgv.Rows(i).Cells(1).Value.ToString) : Next
- End Sub
- Private Sub 公司類別_cb_SelectedIndexChanged(sender As Object, e As EventArgs) Handles 公司類別_cb.SelectedIndexChanged
- Set_供應商清單() : 供應商_cb.Text = ""
- End Sub
- Private Sub 年份_cb_SelectedIndexChanged(sender As Object, e As EventArgs) Handles 年份_cb.SelectedIndexChanged
- If 首次開啟 = False Then : Set_總表1() : Set_清單1() : End If
- End Sub
- Private Sub 年分清單()
- SQL_年份清單() : 年份_cb.Items.Clear() : 年份_cb.Items.Add("ALL") : While (dr.Read()) : 年份_cb.Items.Add(dr("年份")) : End While : conn.Close()
- End Sub
- Private Sub 帳務說明()
- 帳務說明_ch.Items.Clear() : 帳務說明_ch.Items.Add("NA")
- 帳務說明_ch.Items.Add(系統語言字典("G000-379-" & 語言)) : 帳務說明_ch.Items.Add(系統語言字典("G000-380-" & 語言))
- If 登入人級別 = "00" Then : 帳務說明_ch.Items.Add(系統語言字典("G000-381-" & 語言)) : End If
- End Sub
- Private Sub 語言轉換讀取()
- If 系統語言 = "繁體中文" Then : 語言 = "CH" : ElseIf 系統語言 = "English" Then : 語言 = "EN" : ElseIf 系統語言 = "Indonesian" Then : 語言 = "IN" : End If
- Me.Text = 系統語言字典("H001-291-" & 語言) : ToolTip1.ToolTipTitle = 系統語言字典("G000-108-" & 語言) : Label2.Text = 系統語言字典("H314-101-" & 語言)
- 關鍵字_lb.Text = 系統語言字典("H104-106-" & 語言) : 採購單資料_lb.Text = 系統語言字典("G000-392-" & 語言) : 對話框(8) = 系統語言字典("G000-263-" & 語言)
-
- 表頭(0) = 系統語言字典("H206-145-" & 語言) : 表頭(1) = 系統語言字典("H206-146-" & 語言) : 表頭(2) = 系統語言字典("H402-108-" & 語言) : 表頭(3) = 系統語言字典("H319-101-" & 語言)
- 表頭(4) = 系統語言字典("G000-384-" & 語言) : 表頭(5) = 系統語言字典("H319-100-" & 語言) : 表頭(6) = 系統語言字典("H299-109-" & 語言) : 表頭(7) = 系統語言字典("H304-103-" & 語言)
- 表頭(8) = 系統語言字典("H314-120-" & 語言) : 表頭(9) = 系統語言字典("H314-121-" & 語言) : 表頭(10) = 系統語言字典("H904-104-" & 語言) : 表頭(11) = 系統語言字典("G000-385-" & 語言)
- 表頭(12) = 系統語言字典("H204-112-" & 語言) : 表頭(13) = 系統語言字典("G000-386-" & 語言) : 表頭(14) = 系統語言字典("G000-387-" & 語言) : 表頭(15) = 系統語言字典("G000-388-" & 語言)
- 表頭(16) = 系統語言字典("G000-389-" & 語言) : 表頭(17) = 系統語言字典("G000-390-" & 語言) : 表頭(18) = 系統語言字典("H208-111-" & 語言) : 表頭(19) = 系統語言字典("G000-391-" & 語言)
- 表頭(20) = 系統語言字典("G000-393-" & 語言)
- End Sub
- Private Sub 新增_bt_MouseEnter(sender As Object, e As EventArgs) Handles 新增_bt.MouseEnter
- ToolTip1.SetToolTip(Me.新增_bt, 系統語言字典("H304-101-" & 語言))
- End Sub
- Private Sub 存檔_bt_MouseEnter(sender As Object, e As EventArgs) Handles 存檔_bt.MouseEnter
- ToolTip1.SetToolTip(Me.存檔_bt, 系統語言字典("H605-100-" & 語言))
- End Sub
- Private Sub 刪除_tb_MouseEnter(sender As Object, e As EventArgs) Handles 刪除_tb.MouseEnter
- ToolTip1.SetToolTip(Me.刪除_tb, 系統語言字典("G000-112-" & 語言))
- End Sub
- Private Sub 查詢_bt_MouseEnter(sender As Object, e As EventArgs) Handles 查詢_bt.MouseEnter
- ToolTip1.SetToolTip(Me.查詢_bt, 系統語言字典("H104-106-" & 語言))
- End Sub
- Private Sub 新增資料_bt_MouseEnter(sender As Object, e As EventArgs) Handles 新增資料_bt.MouseEnter
- ToolTip1.SetToolTip(Me.新增資料_bt, 系統語言字典("G000-383-" & 語言))
- End Sub
- Private Sub 新增空白_tb_MouseEnter(sender As Object, e As EventArgs) Handles 新增空白_tb.MouseEnter
- ToolTip1.SetToolTip(Me.新增空白_tb, 系統語言字典("H115-103-" & 語言))
- End Sub
- Private Sub 採購付款控制表_Load(sender As Object, e As EventArgs) Handles MyBase.Load
- Me.MdiParent = HX_PGS_ERP_SYS : Me.WindowState = 2 : Me.AutoScroll = True
- H(210) = True : 介面 = "H210" : 語言轉換讀取() : 顯示說明(999, False)
- Panel1.SendToBack() : 視窗3_pl.Visible = False : 首次開啟 = True
- If 登入人級別 = "00" Then : 補資料_bt.Visible = True : ALL_ch.Visible = True : Else : 補資料_bt.Visible = False : ALL_ch.Visible = False : End If
- End Sub
- Private Sub 採購付款控制表_Shown(sender As Object, e As EventArgs) Handles MyBase.Shown
- 計算分流01.Enabled = True
- End Sub
- Private Sub 計算分流01_Tick(sender As Object, e As EventArgs) Handles 計算分流01.Tick
- 帳務說明() : 年分清單() : 年份_cb.Text = Year(Today)
- Set_總表1() : 全部資料_cb.Checked = True : 公司類別_cb.SelectedIndex = 0 : 首次開啟 = False : 計算分流01.Enabled = False
- End Sub
- Private Sub 採購付款控制表_Closed(sender As Object, e As EventArgs) Handles MyBase.Closed
- H(210) = False : MyMod.虛擬桌面開啟()
- End Sub
- Private Sub 採購付款控制表_SizeChanged(sender As Object, e As EventArgs) Handles MyBase.SizeChanged
- MyMod.清單字體大小調整()
- 已核准_dgv.RowsDefaultCellStyle.Font = New System.Drawing.Font("微軟正黑體", HX_PGS_ERP_SYS.字體_NUD.Value)
- 明細_dgv.RowsDefaultCellStyle.Font = New System.Drawing.Font("微軟正黑體", HX_PGS_ERP_SYS.字體_NUD.Value)
- 總表_dgv.RowsDefaultCellStyle.Font = New System.Drawing.Font("微軟正黑體", HX_PGS_ERP_SYS.字體_NUD.Value)
- End Sub
- Private Sub 登入閒置控制_MouseMove(sender As Object, e As EventArgs) Handles MyBase.MouseMove
- timeNow = 0 : HX_PGS_ERP_SYS.Timer1.Enabled = False : HX_PGS_ERP_SYS.Timer1.Enabled = True
- End Sub
- Private Sub 登入閒置控制_KeyPress(sender As Object, e As EventArgs) Handles MyBase.KeyPress
- timeNow = 0 : HX_PGS_ERP_SYS.Timer1.Enabled = False : HX_PGS_ERP_SYS.Timer1.Enabled = True
- End Sub
- Private Sub 語轉扭_bt_Click(sender As Object, e As EventArgs) Handles 語轉扭_bt.Click
- 介面 = "H210" : 語言轉換讀取() : Set_總表1() : Set_清單1()
- End Sub
- Private Sub 已核准_dgv_RowPostPaint(ByVal sender As Object, ByVal e As DataGridViewRowPostPaintEventArgs) Handles 已核准_dgv.RowPostPaint
- Dim linePen As New Pen(Color.Blue, 2)
- If e.RowIndex = 已核准_dgv.Rows.Count - 1 Then : Exit Sub : Else
- If 已核准_dgv(0, e.RowIndex).Value.ToString <> 已核准_dgv(0, e.RowIndex + 1).Value.ToString Then
- Dim startX As Integer = IIf(已核准_dgv.RowHeadersVisible, 已核准_dgv.RowHeadersWidth, 0) : Dim startY As Integer = e.RowBounds.Top + e.RowBounds.Height - 1
- Dim endX As Integer = startX + 已核准_dgv.Columns.GetColumnsWidth(DataGridViewElementStates.Visible) - 已核准_dgv.HorizontalScrollingOffset
- e.Graphics.DrawLine(linePen, startX, startY, endX, startY) : Exit Sub
- End If
- End If
- End Sub
- Private Sub 已核准_dgv_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles 已核准_dgv.CellClick
- If e.RowIndex = -1 Then : Else
- 手動號碼_tb.Text = 已核准_dgv("手寫單號", e.RowIndex).Value.ToString : 供應商編碼_tb.Text = 已核准_dgv("供應商編號", e.RowIndex).Value.ToString
- 採購單號_tb.Text = 已核准_dgv("採購單號", e.RowIndex).Value.ToString : 年份_cb.Text = Strings.Mid(已核准_dgv("採購單號", e.RowIndex).Value.ToString, 7, 4)
- If 登入人級別 = "00" Then
- Dim A As String = Strings.Mid(已核准_dgv("採購單號", e.RowIndex).Value.ToString, 7, 4) & "/" &
- Strings.Mid(已核准_dgv("採購單號", e.RowIndex).Value.ToString, 11, 2) & "/25"
- Dim dateValue As DateTime = DateTime.Parse(A) : Dim newDate As DateTime = dateValue.AddMonths(+1) : A = newDate.ToString("yyyy/MM/dd") : 申請日期_dtp.Value = A
- End If
- End If
- End Sub
- Private Sub 總表_dgv_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles 總表_dgv.CellClick
- If e.RowIndex = -1 Then : Else
- 供應商_lb.Text = 總表_dgv(1, e.RowIndex).Value.ToString : 供應商編碼_tb.Text = 總表_dgv("供應商編號", e.RowIndex).Value.ToString : Set_明細1()
- End If
- End Sub
- Private Sub 明細_dgv_RowPostPaint(ByVal sender As Object, ByVal e As DataGridViewRowPostPaintEventArgs) Handles 明細_dgv.RowPostPaint
- Dim linePen As New Pen(Color.Blue, 2)
- If e.RowIndex = 明細_dgv.Rows.Count - 1 Then : Exit Sub : Else
- If 明細_dgv(8, e.RowIndex).Value.ToString <> 明細_dgv(8, e.RowIndex + 1).Value.ToString Then
- Dim startX As Integer = IIf(明細_dgv.RowHeadersVisible, 明細_dgv.RowHeadersWidth, 0) : Dim startY As Integer = e.RowBounds.Top + e.RowBounds.Height - 1
- Dim endX As Integer = startX + 明細_dgv.Columns.GetColumnsWidth(DataGridViewElementStates.Visible) - 明細_dgv.HorizontalScrollingOffset
- e.Graphics.DrawLine(linePen, startX, startY, endX, startY) : Exit Sub
- End If
- End If
- End Sub
- Private Sub 明細_dgv_CellContentCl(sender As Object, e As DataGridViewCellEventArgs) Handles 明細_dgv.CellEndEdit
- Try
- If e.RowIndex = -1 Then : Else
- 合計2_dgv(6, 0).Value = 0 : 合計2_dgv(7, 0).Value = 0 : 合計2_dgv(11, 0).Value = 0 : 合計2_dgv(16, 0).Value = 0
- For i As Integer = 0 To 明細_dgv.Rows.Count - 1
- If i = 0 Then : 明細_dgv(16, i).Value = 明細_dgv(7, i).Value - 明細_dgv(11, i).Value
- Else : 明細_dgv(16, i).Value = 明細_dgv(7, i).Value - 明細_dgv(11, i).Value + 明細_dgv(16, i - 1).Value : End If
- Next
- Dim 上一個歸零 As Boolean : Dim H As Integer : H = 明細_dgv.Rows.Count - 1
- For i As Integer = 0 To 明細_dgv.Rows.Count - 1
- If H = 明細_dgv.Rows.Count - 1 Then : Else
- If CLng(明細_dgv(16, H + 1).Value.ToString) = 0 Then : 上一個歸零 = True : Else : 上一個歸零 = False : End If
- If 上一個歸零 = True Then : 明細_dgv(16, H).Value = 0 : End If
- End If : H -= 1
- 合計2_dgv(6, 0).Value += 明細_dgv(6, i).Value : 合計2_dgv(7, 0).Value += 明細_dgv(7, i).Value
- 合計2_dgv(11, 0).Value += 明細_dgv(11, i).Value : 合計2_dgv(16, 0).Value = 明細_dgv(16, i).Value
- Next
- 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
- 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
- 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
- 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
- End If
- Catch ex As Exception : MGB(ex.Message, 1) : End Try
- End Sub
- Private Sub 明細_dgv_MouseUp(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles 明細_dgv.MouseUp
- If 明細_dgv.SelectedCells.Count > 0 Then
- Dim 位置 As Integer = 明細_dgv.SelectedCells(0).RowIndex : Dim 位置Y As Integer = 明細_dgv.SelectedCells(0).ColumnIndex
- 流水號_tb.Text = 明細_dgv(0, 位置).Value.ToString
- If 位置Y = 10 Then
- CB選擇清單(帳務說明_ch, 帳務說明_ch.Text)
- If 帳務說明_ch.SelectedIndex = 0 Then : 明細_dgv(17, 位置).Value = ""
- Else : 明細_dgv(17, 位置).Value = 帳務說明_ch.SelectedIndex - 1 : End If
- If 明細_dgv(17, 位置).Value.ToString = "" Then : 明細_dgv(10, 位置).Value = ""
- ElseIf CDbl(明細_dgv(17, 位置).Value.ToString) = 0 Then : 明細_dgv(10, 位置).Value = 系統語言字典("G000-379-" & 語言)
- ElseIf CDbl(明細_dgv(17, 位置).Value.ToString) = 1 Then : 明細_dgv(10, 位置).Value = 系統語言字典("G000-380-" & 語言)
- ElseIf CDbl(明細_dgv(17, 位置).Value.ToString) = 2 Then : 明細_dgv(10, 位置).Value = 系統語言字典("G000-381-" & 語言) : End If
- End If
- End If
- End Sub
- Private Sub 全部資料_cb_Click(sender As Object, e As EventArgs) Handles 全部資料_cb.Click
- Set_清單1()
- End Sub
- Private Sub 供應商_cb_Click(sender As Object, e As EventArgs) Handles 供應商_cb.Click
- CB選擇清單(供應商_cb, 供應商_cb.Text) : 供應商_lb.Text = 供應商_cb.Text : Set_清單1()
- End Sub
- Private Sub 新增_bt_Click(sender As Object, e As EventArgs) Handles 新增_bt.Click
- If 視窗3_pl.Visible = False Then
- 視窗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
- 視窗3_pl.BringToFront() : 供應商_cb.SelectedIndex = 0 : Set_清單1()
- Else : 視窗3_pl.Visible = False : End If
- End Sub
- Private Sub 新增資料_bt_Click(sender As Object, e As EventArgs) Handles 新增資料_bt.Click
- If 採購單號_tb.Text <> "" Then : SQL_採購付款採購單確認(採購單號_tb.Text)
- If dr.Read Then : MGB(系統語言字典("G000-376-" & 語言), 1) : Else : 訊息回應 = "" : INB(系統語言字典("G000-377-" & 語言))
- If 訊息回應 <> "" Then
- Dim 編號 As String : SQL_採購付款最後一筆資料()
- If dr.Read() Then : 編號 = dr("流水號").ToString : Else : 編號 = "ES00000000" : End If : conn.Close()
- Dim NUM1 As Integer = Double.Parse(Strings.Right(編號, 8)) + 1
- If NUM1 < 10 Then : 編號 = "ES" & "0000000" & NUM1 : ElseIf NUM1 > 9 And NUM1 < 100 Then : 編號 = "ES" & "000000" & NUM1
- ElseIf NUM1 > 99 And NUM1 < 1000 Then : 編號 = "ES" & "00000" & NUM1 : ElseIf NUM1 > 999 And NUM1 < 10000 Then : 編號 = "ES" & "0000" & NUM1
- ElseIf NUM1 > 9999 And NUM1 < 100000 Then : 編號 = "ES" & "000" & NUM1 : ElseIf NUM1 > 99999 And NUM1 < 1000000 Then : 編號 = "ES" & "00" & NUM1
- ElseIf NUM1 > 999999 And NUM1 < 10000000 Then : 編號 = "ES" & "0" & NUM1 : ElseIf NUM1 > 9999999 Then : 編號 = "ES" & NUM1 : End If
- 輸入日期.ShowDialog()
- SQL_採購付款資料新增(編號, 供應商編碼_tb.Text, Strings.Format(申請日期_dtp.Value, "yyyy/MM/dd"), 訊息回應, 採購單號_tb.Text, "0", PA, "", "", "0", 手動號碼_tb.Text)
- MGB(系統語言字典("G000-118-" & 語言), 1) : Set_總表1() : Set_明細1() : Set_清單1()
- Else : MGB(系統語言字典("G000-378-" & 語言), 1) : End If
- End If
- Else : MGB(系統語言字典("G000-117-" & 語言), 1) : End If
- End Sub
- Private Sub 新增空白_tb_Click(sender As Object, e As EventArgs) Handles 新增空白_tb.Click
- If 供應商編碼_tb.Text <> "" Then
- INB(系統語言字典("G000-377-" & 語言))
- If 訊息回應 <> "" Then
- Dim 編號 As String : SQL_採購付款最後一筆資料()
- If dr.Read() Then : 編號 = dr("流水號").ToString : Else : 編號 = "ES00000000" : End If : conn.Close()
- Dim NUM1 As Integer = Double.Parse(Strings.Right(編號, 8)) + 1
- If NUM1 < 10 Then : 編號 = "ES" & "0000000" & NUM1 : ElseIf NUM1 > 9 And NUM1 < 100 Then : 編號 = "ES" & "000000" & NUM1
- ElseIf NUM1 > 99 And NUM1 < 1000 Then : 編號 = "ES" & "00000" & NUM1 : ElseIf NUM1 > 999 And NUM1 < 10000 Then : 編號 = "ES" & "0000" & NUM1
- ElseIf NUM1 > 9999 And NUM1 < 100000 Then : 編號 = "ES" & "000" & NUM1 : ElseIf NUM1 > 99999 And NUM1 < 1000000 Then : 編號 = "ES" & "00" & NUM1
- ElseIf NUM1 > 999999 And NUM1 < 10000000 Then : 編號 = "ES" & "0" & NUM1 : ElseIf NUM1 > 9999999 Then : 編號 = "ES" & NUM1 : End If
- If HX_ch.Checked = True Then : 採購單號_tb.Text = "HX" : Else : 採購單號_tb.Text = "PGS" : End If
- 輸入日期.ShowDialog()
- SQL_採購付款資料新增(編號, 供應商編碼_tb.Text, Strings.Format(申請日期_dtp.Value, "yyyy/MM/dd"), 訊息回應, 採購單號_tb.Text, "0", PA, "", "", "0", 手動號碼_tb.Text)
- MGB(系統語言字典("G000-118-" & 語言), 1) : Set_總表1() : Set_明細1() : Set_清單1()
- Else : MGB(系統語言字典("G000-378-" & 語言), 1) : End If
- Else : MGB(系統語言字典("G000-117-" & 語言), 1) : End If
- End Sub
- Private Sub 存檔_bt_Click(sender As Object, e As EventArgs) Handles 存檔_bt.Click
- Dim 格式異常1 As Boolean = False : Dim 格式異常2 As Boolean = False
- For i As Integer = 0 To 明細_dgv.Rows.Count - 1
- Dim A As String = 明細_dgv(2, i).Value.ToString : Dim B As String = 明細_dgv(8, i).Value.ToString
- Dim format As String = "yyyy/MM/dd" : Dim parsedDate As DateTime
- If DateTime.TryParseExact(A, format, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, parsedDate) Then : Else
- 格式異常1 = True : Exit For
- End If
- If DateTime.TryParseExact(B, format, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, parsedDate) Then : Else
- 格式異常2 = True : Exit For
- End If
- Next
- If 格式異常1 = True Or 格式異常2 = True Then : MGB(系統語言字典("G000-119-" & 語言), 1)
- Else
- For i As Integer = 0 To 明細_dgv.Rows.Count - 1
- SQL_採購付款表修改(明細_dgv(0, i).Value.ToString, 明細_dgv(2, i).Value.ToString, 明細_dgv(3, i).Value.ToString, 明細_dgv(7, i).Value.ToString,
- 明細_dgv(8, i).Value.ToString, 明細_dgv(9, i).Value.ToString, 明細_dgv(17, i).Value.ToString, 明細_dgv(11, i).Value.ToString)
- Next : Set_總表1() : Set_明細1() : MGB(系統語言字典("G000-119-" & 語言), 1)
- End If
-
-
-
-
-
- End Sub
- Private Sub 刪除_tb_Click(sender As Object, e As EventArgs) Handles 刪除_tb.Click
- If 流水號_tb.Text <> "" Then : MGB(系統語言字典("G000-213-" & 語言), 2)
- If 訊息回應 = "YES" Then : SQL_採購付款表刪除(流水號_tb.Text) : Set_總表1() : Set_明細1() : MGB(系統語言字典("G000-120-" & 語言), 1) : 流水號_tb.Text = "" : End If
- End If
- End Sub
- Private Sub 補資料_bt_Click(sender As Object, e As EventArgs) Handles 補資料_bt.Click
- For i As Integer = 0 To 明細_dgv.Rows.Count - 1
- Dim A As String = 明細_dgv(2, i).Value.ToString
- Dim dateValue As DateTime = DateTime.Parse(A) ' 將字串轉換為 DateTime
- Dim lastDayOfMonth As DateTime = New DateTime(dateValue.Year, dateValue.Month, DateTime.DaysInMonth(dateValue.Year, dateValue.Month)) ' 計算當月最後一天
- 明細_dgv(8, i).Value = lastDayOfMonth.ToString("yyyy/MM/dd")
- Next
- End Sub
- Private Sub 查詢_bt_Click(sender As Object, e As EventArgs) Handles 查詢_bt.Click
- lastFoundRowIndex = -1 : lastFoundColIndex = -1 : 查詢DGV中的關鍵字_自動(已核准_dgv, 關鍵字_tb)
- If lastFoundRowIndex <> -1 Then
- 手動號碼_tb.Text = 已核准_dgv("手寫單號", lastFoundRowIndex).Value.ToString : 供應商編碼_tb.Text = 已核准_dgv("供應商編號", lastFoundRowIndex).Value.ToString
- 採購單號_tb.Text = 已核准_dgv("採購單號", lastFoundRowIndex).Value.ToString : 年份_cb.Text = Strings.Mid(已核准_dgv("採購單號", lastFoundRowIndex).Value.ToString, 7, 4)
- End If
- End Sub
- Private Sub 關鍵字_tb_TextChanged(sender As Object, e As EventArgs) Handles 關鍵字_tb.TextChanged
- lastFoundRowIndex = -1 : lastFoundColIndex = -1 : 查詢DGV中的關鍵字_自動(已核准_dgv, 關鍵字_tb)
- If lastFoundRowIndex <> -1 Then
- 手動號碼_tb.Text = 已核准_dgv("手寫單號", lastFoundRowIndex).Value.ToString : 供應商編碼_tb.Text = 已核准_dgv("供應商編號", lastFoundRowIndex).Value.ToString
- 採購單號_tb.Text = 已核准_dgv("採購單號", lastFoundRowIndex).Value.ToString : 年份_cb.Text = Strings.Mid(已核准_dgv("採購單號", lastFoundRowIndex).Value.ToString, 7, 4)
- End If
- End Sub
- Private Sub HX_ch_CheckedChanged(sender As Object, e As EventArgs) Handles HX_ch.Click
- 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()
- End Sub
- Private Sub PGS_ch_CheckedChanged(sender As Object, e As EventArgs) Handles PGS_ch.Click
- 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()
- End Sub
- Private Sub ALL_ch_CheckedChanged(sender As Object, e As EventArgs) Handles ALL_ch.Click
- 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()
- End Sub
-
-
- '----------------------自創視窗滑鼠拖曳功能---------------------------------------------------------------------------------------------------------
- Dim OldX, OldY As Long : Dim drag As Boolean
- Private Sub CanceL3_bt_Click(sender As Object, e As EventArgs) Handles CanceL3_bt.Click
- If 視窗3_pl.Visible = False Then : 視窗3_pl.Visible = True : 視窗3_pl.BringToFront() : Else
- 視窗3_pl.Visible = False : End If
- End Sub
- Private Sub 視窗3_pl_MouseDown(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles 視窗3_pl.MouseDown
- If e.Button = MouseButtons.Left Then : OldX = e.X : OldY = e.Y : drag = True : End If
- End Sub
- Private Sub 視窗3_pl_MouseMove(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles 視窗3_pl.MouseMove
- If drag Then : 視窗3_pl.Left = 視窗3_pl.Left + e.X - OldX : 視窗3_pl.Top = 視窗3_pl.Top + e.Y - OldY : End If
- End Sub
- Private Sub 視窗3_pl_MouseUp(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles 視窗3_pl.MouseUp
- drag = False
- End Sub
- Private Sub 模擬點擊第幾行(rowIndex As Integer)
- ' 確保 rowIndex 在合法範圍內
- If rowIndex >= 0 AndAlso rowIndex < 總表_dgv.Rows.Count Then
- ' 設置當前的單元格 (假設點擊第一列的單元格)
- 總表_dgv.CurrentCell = 總表_dgv.Rows(rowIndex).Cells(0)
-
- ' 觸發 CellClick 事件
- Dim args As New DataGridViewCellEventArgs(0, rowIndex)
- 總表_dgv_CellContentClick(總表_dgv, args)
- End If
- End Sub
- Private Sub 列印_bt_Click(sender As Object, e As EventArgs) Handles 列印_bt.Click
- xlApp = CType(CreateObject("Excel.Application"), Application)
- xlBook = xlApp.Workbooks.Add : xlApp.DisplayAlerts = True
- xlApp.Visible = True : xlApp.Application.WindowState = xlMaximized
-
- xlSheet = CType(xlBook.Sheets(1), Worksheet)
- xlSheet.Activate()
- xlSheet.Name = "GREND TOTAL"
- Dim title As String = ""
- If 系統語言 = "繁體中文" Then
- title = "採購單未付清單"
- ElseIf 系統語言 = "English" Then
- title = "Outstanding Purchase Orders"
- ElseIf 系統語言 = "Indonesian" Then
- title = "Daftar Pesanan Pembelian yang Belum Dibayar"
- End If
-
- If HX_ch.Checked = True Then
- xlSheet.Cells(2, 4) = 年份_cb.Text & "-HX" & title
- ElseIf PGS_ch.Checked = True Then
- xlSheet.Cells(2, 4) = 年份_cb.Text & "-PGS" & title
- ElseIf ALL_ch.Checked = True Then
- xlSheet.Cells(2, 4) = 年份_cb.Text & "-HX&PGS" & title
- End If
-
- Dim a As Integer = 4
- For i As Integer = 0 To 總表_dgv.Columns.Count - 1
- If i = 0 Or i = 1 Or i = 5 Then
- ' 將每個欄位的名稱放入 Excel 的第3行對應的單元格中
- xlSheet.Cells(3, a) = 總表_dgv.Columns(i).Name
- a += 1
- End If
- Next
- Dim b As Integer = 1
- Dim num As New List(Of Integer)
- For rowIndex As Integer = 0 To 總表_dgv.Rows.Count - 1
- a = 4 ' 重置 a 為 1,以便從第一列開始寫入數據
- If Math.Round(CDbl(總表_dgv(5, rowIndex).Value)) <> 0 Then
- num.Add(rowIndex)
- xlSheet.Cells(b + 3, a) = b.ToString()
- For colIndex As Integer = 0 To 總表_dgv.Columns.Count - 1
- If colIndex = 1 Then
- ' 將每個欄位的名稱放入 Excel 的第3行對應的單元格中
- xlSheet.Cells(b + 3, a + 1) = 總表_dgv.Rows(rowIndex).Cells(colIndex).Value
- End If
- If colIndex = 5 Then
- Dim cellValue1 As Double = Val(總表_dgv.Rows(rowIndex).Cells(colIndex).Value.ToString())
- xlSheet.Cells(b + 3, a + 2) = Math.Round(cellValue1).ToString("#,##0")
- End If
- Next
- b += 1
- End If
- Next
- Dim 總計 As Double = Val(合計1_dgv(5, 0).Value.ToString())
- xlSheet.Cells(b + 3, 4) = "GRAND TOTAL" : xlSheet.Cells(b + 3, 6) = Math.Round(總計).ToString("#,##0")
- N2 = b + 3
- AA(xlApp, xlSheet)
- xlSheet.Protect(Password:="0003065795", AllowFormattingCells:=False, AllowFormattingColumns:=False, AllowFormattingRows:=False)
- For Each index As Integer In num
- 模擬點擊第幾行(index)
- 新增工作表(總表_dgv.Rows(index).Cells(1).Value.ToString())
- Next
- Dim firstSheet As Worksheet
- firstSheet = xlBook.Worksheets(1)
- firstSheet.Select()
-
- Dim desktopPath As String = Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
- Dim savePath As String = System.IO.Path.Combine(desktopPath, "採購單未付清單.xlsx")
- ' 保存 Excel 文件
- If System.IO.File.Exists(savePath) Then
- Dim result As DialogResult = MessageBox.Show("檔案已存在,是否要取代?", "檔案已存在", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly)
-
- If result = DialogResult.Yes Then
- System.IO.File.Delete(savePath)
- xlBook.SaveAs(savePath)
- ElseIf result = DialogResult.No Then
- ' 使用者選擇了 "否",不保存檔案
- MessageBox.Show("檔案未保存", "操作取消", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly)
- xlBook.Close(False) ' 關閉檔案但不保存
- xlApp.Quit()
- ReleaseComObject(xlSheet)
- ReleaseComObject(xlBook)
- ReleaseComObject(xlApp)
- Exit Sub
- ElseIf result = DialogResult.Cancel Then
- ' 使用者選擇了 "取消",取消操作
- MessageBox.Show("操作已取消", "取消", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly)
- xlBook.Close(False) ' 關閉檔案但不保存
- xlApp.Quit()
- ReleaseComObject(xlSheet)
- ReleaseComObject(xlBook)
- ReleaseComObject(xlApp)
- Exit Sub
- End If
- Else
- ' 如果檔案不存在,直接保存
- xlBook.SaveAs(savePath)
- End If
- ' 關閉並釋放 Excel 資源
- xlBook.Close()
- xlApp.Quit()
- ReleaseComObject(xlSheet)
- ReleaseComObject(xlBook)
- ReleaseComObject(xlApp)
- MsgBox("文件已保存到桌面!!")
- End Sub
- Private Sub 新增工作表(名稱 As String)
- Dim num As New List(Of Integer) From {1, 2, 3, 5, 6, 7, 8, 9, 10, 11, 16}
- xlSheet = CType(xlBook.Worksheets.Add(), Worksheet)
- xlSheet.Name = 名稱
- xlSheet.Move(After:=xlBook.Worksheets(xlBook.Worksheets.Count))
- xlSheet.Cells(1, 1) = 名稱
- Dim columnIndex As Integer = 1 ' 用來追蹤 Excel 中的列位置
- For Each index As Integer In num
- xlSheet.Cells(2, columnIndex) = 明細_dgv.Columns(index).Name
- columnIndex += 1
- Next
- Dim a As Integer = 3
-
- For rowIndex As Integer = 0 To 明細_dgv.Rows.Count - 1
- columnIndex = 1
- For Each index As Integer In num
- If columnIndex = 5 Or columnIndex = 6 Or columnIndex = 10 Or columnIndex = 11 Then
- ' 將每個欄位的名稱放入 Excel 的第3行對應的單元格中
- Dim cellValue1 As Double = Val(明細_dgv.Rows(rowIndex).Cells(index).Value.ToString())
- xlSheet.Cells(a, columnIndex) = Math.Round(cellValue1).ToString("#,##0")
- Else
- xlSheet.Cells(a, columnIndex) = 明細_dgv.Rows(rowIndex).Cells(index).Value
- End If
- columnIndex += 1
- Next
- a += 1
- Next
- N2 = a
- Dim con1 As Double = Val(合計2_dgv(6, 0).Value.ToString())
- Dim con2 As Double = Val(合計2_dgv(7, 0).Value.ToString())
- Dim con3 As Double = Val(合計2_dgv(11, 0).Value.ToString())
- Dim con4 As Double = Val(合計2_dgv(16, 0).Value.ToString())
-
- xlSheet.Cells(a, 5) = Math.Round(con1).ToString("#,##0")
- xlSheet.Cells(a, 6) = Math.Round(con2).ToString("#,##0")
- xlSheet.Cells(a, 10) = Math.Round(con3).ToString("#,##0")
- xlSheet.Cells(a, 11) = Math.Round(con4).ToString("#,##0")
-
- BB(xlApp, xlSheet)
- xlSheet.Protect(Password:="0003065795", AllowFormattingCells:=False, AllowFormattingColumns:=False, AllowFormattingRows:=False)
- End Sub
- Private Sub ReleaseComObject(ByVal obj As Object)
- Try
- System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
- obj = Nothing
- Catch ex As Exception
- obj = Nothing
- Finally
- GC.Collect()
- End Try
- End Sub
- Private Sub BB(ByVal myExcel As Microsoft.Office.Interop.Excel.Application, ByVal xlSheet As Worksheet)
- xlSheet.Cells.Select()
- With myExcel.Selection.Font
- .Name = "微軟正黑體"
- .Size = 11
- .Strikethrough = False
- .Superscript = False
- .Subscript = False
- .OutlineFont = False
- .Shadow = False
- .Underline = xlUnderlineStyleNone
- .ThemeColor = xlThemeColorLight1
- .TintAndShade = 0
- .ThemeFont = xlThemeFontNone
- End With
-
- xlSheet.Range("1:1").Select()
- With myExcel.Selection.Font
- .Name = "微軟正黑體"
- .Size = 16
- .Strikethrough = False
- .Superscript = False
- .Subscript = False
- .OutlineFont = False
- .Shadow = False
- .Underline = xlUnderlineStyleNone
- .ThemeColor = xlThemeColorLight1
- .TintAndShade = 0
- .ThemeFont = xlThemeFontNone
- End With
-
- xlSheet.Range("2:2," & N2.ToString() & ":" & N2.ToString()).Select()
- With myExcel.Selection.Font
- .Name = "微軟正黑體"
- .Size = 14
- .Strikethrough = False
- .Superscript = False
- .Subscript = False
- .OutlineFont = False
- .Shadow = False
- .Underline = xlUnderlineStyleNone
- .ThemeColor = xlThemeColorLight1
- .TintAndShade = 0
- .ThemeFont = xlThemeFontNone
- End With
-
- xlSheet.Range("A1:K1").Select()
-
- With myExcel.Selection.Interior
- .Pattern = xlSolid
- .PatternColorIndex = xlAutomatic
- .Color = 5296274
- .TintAndShade = 0
- .PatternTintAndShade = 0
- End With
- myExcel.Selection.Font.Bold = True
- xlSheet.Range("A2:G2,A" & N2.ToString() & ":G" & N2.ToString()).Select()
-
- With myExcel.Selection.Interior
- .Pattern = xlSolid
- .PatternColorIndex = xlAutomatic
- .ThemeColor = xlThemeColorAccent4
- .TintAndShade = 0.399975585192419
- .PatternTintAndShade = 0
- End With
- myExcel.Selection.Font.Bold = True
- xlSheet.Range("H2:K2,H" & N2.ToString() & ":K" & N2.ToString()).Select()
-
- With myExcel.Selection.Interior
- .Pattern = xlSolid
- .PatternColorIndex = xlAutomatic
- .ThemeColor = xlThemeColorAccent1
- .TintAndShade = 0.599993896298105
- .PatternTintAndShade = 0
- End With
- myExcel.Selection.Font.Bold = True
-
- xlSheet.Range("A1:K1").Select()
- With myExcel.Selection
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlCenter
- .WrapText = False
- .Orientation = 0
- .AddIndent = False
- .IndentLevel = 0
- .ShrinkToFit = False
- .ReadingOrder = xlContext
- .MergeCells = False
- End With
- myExcel.Selection.Merge
-
- xlSheet.Range("A2:K2").Select()
- With myExcel.Selection
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlCenter
- .WrapText = False
- .Orientation = 0
- .AddIndent = False
- .IndentLevel = 0
- .ShrinkToFit = False
- .ReadingOrder = xlContext
- .MergeCells = False
- End With
- xlSheet.Range("A3:D" & N2.ToString() & ",G3:I" & N2.ToString()).Select()
- With myExcel.Selection
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlCenter
- .WrapText = False
- .Orientation = 0
- .AddIndent = False
- .IndentLevel = 0
- .ShrinkToFit = False
- .ReadingOrder = xlContext
- End With
- xlSheet.Range("E3:F" & N2.ToString() & ",J3:K" & N2.ToString()).Select()
- With myExcel.Selection
- .HorizontalAlignment = xlRight
- .VerticalAlignment = xlCenter
- .WrapText = False
- .Orientation = 0
- .AddIndent = False
- .IndentLevel = 0
- .ShrinkToFit = False
- .ReadingOrder = xlContext
- End With
-
- xlSheet.Rows(1).RowHeight = 45
- xlSheet.Rows(2).RowHeight = 36
- For rowIndex As Integer = 0 To N2
- xlSheet.Rows(3 + rowIndex).RowHeight = 27
- Next
- xlSheet.Columns("A:J").AutoFit()
-
- xlSheet.Range("A1:J" & N2.ToString).Select()
- myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
- myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
- With myExcel.Selection.Borders(xlEdgeLeft)
- .LineStyle = xlContinuous
- .ColorIndex = 0
- .TintAndShade = 0
- .Weight = xlThin
- End With
- With myExcel.Selection.Borders(xlEdgeTop)
- .LineStyle = xlContinuous
- .ColorIndex = 0
- .TintAndShade = 0
- .Weight = xlThin
- End With
- With myExcel.Selection.Borders(xlEdgeBottom)
- .LineStyle = xlContinuous
- .ColorIndex = 0
- .TintAndShade = 0
- .Weight = xlThin
- End With
- With myExcel.Selection.Borders(xlEdgeRight)
- .LineStyle = xlContinuous
- .ColorIndex = 0
- .TintAndShade = 0
- .Weight = xlThin
- End With
- With myExcel.Selection.Borders(xlInsideVertical)
- .LineStyle = xlContinuous
- .ColorIndex = 0
- .TintAndShade = 0
- .Weight = xlThin
- End With
- With myExcel.Selection.Borders(xlInsideHorizontal)
- .LineStyle = xlContinuous
- .ColorIndex = 0
- .TintAndShade = 0
- .Weight = xlThin
- End With
- End Sub
- Private Sub AA(ByVal myExcel As Microsoft.Office.Interop.Excel.Application, ByVal xlSheet As Worksheet)
- xlSheet.Cells.Select()
- With myExcel.Selection.Font
- .Name = "微軟正黑體"
- .Size = 11
- .Strikethrough = False
- .Superscript = False
- .Subscript = False
- .OutlineFont = False
- .Shadow = False
- .Underline = xlUnderlineStyleNone
- .ThemeColor = xlThemeColorLight1
- .TintAndShade = 0
- .ThemeFont = xlThemeFontNone
- End With
-
- xlSheet.Range("2:2,3:3," & N2.ToString() & ":" & N2.ToString()).Select()
- With myExcel.Selection.Font
- .Name = "微軟正黑體"
- .Size = 14
- .Strikethrough = False
- .Superscript = False
- .Subscript = False
- .OutlineFont = False
- .Shadow = False
- .Underline = xlUnderlineStyleNone
- .ThemeColor = xlThemeColorLight1
- .TintAndShade = 0
- .ThemeFont = xlThemeFontNone
- End With
-
- xlSheet.Range("D2:F3,D" & N2.ToString() & ":F" & N2.ToString()).Select()
-
- With myExcel.Selection.Interior
- .Pattern = xlSolid
- .PatternColorIndex = xlAutomatic
- .ThemeColor = xlThemeColorAccent2
- .TintAndShade = 0.399975585192419
- .PatternTintAndShade = 0
- End With
- myExcel.Selection.Font.Bold = True
-
- xlSheet.Range("D2:F2").Select()
-
- With myExcel.Selection
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlCenter
- .WrapText = False
- .Orientation = 0
- .AddIndent = False
- .IndentLevel = 0
- .ShrinkToFit = False
- .ReadingOrder = xlContext
- .MergeCells = False
- End With
- myExcel.Selection.Merge
- xlSheet.Range("D" & N2.ToString() & ":E" & N2.ToString()).Select()
-
- With myExcel.Selection
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlCenter
- .WrapText = False
- .Orientation = 0
- .AddIndent = False
- .IndentLevel = 0
- .ShrinkToFit = False
- .ReadingOrder = xlContext
- .MergeCells = False
- End With
- myExcel.Selection.Merge
-
- xlSheet.Range("F4:F" & N2.ToString()).Select()
- With myExcel.Selection
- .HorizontalAlignment = xlRight
- .VerticalAlignment = xlCenter
- .WrapText = False
- .Orientation = 0
- .AddIndent = False
- .IndentLevel = 0
- .ShrinkToFit = False
- .ReadingOrder = xlContext
- End With
-
- xlSheet.Range("D3:D" & (N2 - 1).ToString() & ",E3:E" & (N2 - 1).ToString() & ",F3").Select()
- With myExcel.Selection
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlCenter
- .WrapText = False
- .Orientation = 0
- .AddIndent = False
- .IndentLevel = 0
- .ShrinkToFit = False
- .ReadingOrder = xlContext
- End With
-
- xlSheet.Rows(2).RowHeight = 33
- xlSheet.Rows(3).RowHeight = 45
- For rowIndex As Integer = 0 To N2 - 3
- xlSheet.Rows(4 + rowIndex).RowHeight = 21
- Next
- xlSheet.Rows(N2).RowHeight = 33
-
- xlSheet.Columns("D:F").AutoFit()
-
- xlSheet.Range("D2:F" & N2.ToString).Select()
- myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
- myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
- With myExcel.Selection.Borders(xlEdgeLeft)
- .LineStyle = xlContinuous
- .ColorIndex = 0
- .TintAndShade = 0
- .Weight = xlThin
- End With
- With myExcel.Selection.Borders(xlEdgeTop)
- .LineStyle = xlContinuous
- .ColorIndex = 0
- .TintAndShade = 0
- .Weight = xlThin
- End With
- With myExcel.Selection.Borders(xlEdgeBottom)
- .LineStyle = xlContinuous
- .ColorIndex = 0
- .TintAndShade = 0
- .Weight = xlThin
- End With
- With myExcel.Selection.Borders(xlEdgeRight)
- .LineStyle = xlContinuous
- .ColorIndex = 0
- .TintAndShade = 0
- .Weight = xlThin
- End With
- With myExcel.Selection.Borders(xlInsideVertical)
- .LineStyle = xlContinuous
- .ColorIndex = 0
- .TintAndShade = 0
- .Weight = xlThin
- End With
- With myExcel.Selection.Borders(xlInsideHorizontal)
- .LineStyle = xlContinuous
- .ColorIndex = 0
- .TintAndShade = 0
- .Weight = xlThin
- End With
-
-
- End Sub
- Private Shared Function NewMethod(xlBook As Workbook) As Worksheet
- Return CType(xlBook.Worksheets.Add, Worksheet)
- End Function
- End Class
|