HX-PGS-ERP-SYS-SIU/02、採購管理系統/採購報表/採購付款控制表.vb

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