HSKIN-MIS-SYS-LEEHSIEHJUI/GRAMS-SYS/05、業務管理/業績獎金計算表_新.vb

Imports System.IO
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.XlPageOrientation
Imports Microsoft.Office.Interop.Excel
Public Class 業績獎金計算表_新
    Dim 位置1 As Integer
    Dim xlApp As Application : Dim xlBook As Workbook : Dim xlSheet As 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_客戶交易明細表()
        Dim ds As New DataSet
        交易明細_dgv.DataSource = Nothing : ds.Clear()
        交易明細_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
        交易明細_dgv.ColumnHeadersHeight = 40 : 交易明細_dgv.AllowUserToAddRows = False
        SQL_客戶交易明細表_清單新(月份_cb.Text)
        da.Fill(ds) : 交易明細_dgv.DataSource = ds.Tables(0) : conn.Close()

        交易明細_dgv.Columns(0).Visible = False : 交易明細_dgv.Columns(2).Visible = False : 交易明細_dgv.Columns(4).Visible = False : 交易明細_dgv.Columns(5).Visible = False
        交易明細_dgv.Columns(19).Visible = False
        交易明細_dgv.Columns(20).FillWeight = 70 : 交易明細_dgv.Columns(6).FillWeight = 300 : 交易明細_dgv.Columns(12).FillWeight = 100
        For i As Integer = 0 To 交易明細_dgv.Columns.Count - 1 : 交易明細_dgv.Columns(i).ReadOnly = True : Next

        交易明細_dgv.Columns(7).DefaultCellStyle.Format = "#,##0" : 交易明細_dgv.Columns(8).DefaultCellStyle.Format = "#,##0"
        交易明細_dgv.Columns(9).DefaultCellStyle.Format = "#,##0" : 交易明細_dgv.Columns(10).DefaultCellStyle.Format = "#,##0.0"
        交易明細_dgv.Columns(11).DefaultCellStyle.Format = "#,##0" : 交易明細_dgv.Columns(12).DefaultCellStyle.Format = "#,##0"
        交易明細_dgv.Columns(7).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        交易明細_dgv.Columns(8).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        交易明細_dgv.Columns(9).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        交易明細_dgv.Columns(10).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        交易明細_dgv.Columns(11).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        交易明細_dgv.Columns(12).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        交易明細_dgv.Columns(13).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        交易明細_dgv.Columns("折比").Visible = False : 交易明細_dgv.Columns("折讓").Visible = False

        Dim ds2 As New DataSet
        人員_dgv.DataSource = Nothing : ds2.Clear()
        人員_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
        人員_dgv.ColumnHeadersHeight = 40 : 人員_dgv.AllowUserToAddRows = False
        SQL_獎金計算_人員()
        da.Fill(ds2) : 人員_dgv.DataSource = ds2.Tables(0) : conn.Close()

        Dim ds1 As New DataSet
        獎金計算_dgv.DataSource = Nothing : ds1.Clear()
        獎金計算_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
        獎金計算_dgv.ColumnHeadersHeight = 40 : 獎金計算_dgv.AllowUserToAddRows = False
        SQL_獎金計算_清單新(月份_cb.Text)
        da.Fill(ds1) : 獎金計算_dgv.DataSource = ds1.Tables(0) : conn.Close()
        For I As Integer = 1 To 獎金計算_dgv.Columns.Count - 1
            獎金計算_dgv.Columns(I).DefaultCellStyle.Format = "#,##0" : 獎金計算_dgv.Columns(I).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        Next

        Dim ds3 As New DataSet
        合計_dgv.DataSource = Nothing : ds3.Clear()
        合計_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
        合計_dgv.ColumnHeadersHeight = 50 : 合計_dgv.AllowUserToAddRows = False : 合計_dgv.RowTemplate.Height = 25
        合計_dgv.ScrollBars = System.Windows.Forms.ScrollBars.None : 合計_dgv.ColumnHeadersVisible = False
        SQL_獎金計算_清單新_和(月份_cb.Text)
        da.Fill(ds3) : 合計_dgv.DataSource = ds3.Tables(0) : conn.Close()
        For I As Integer = 1 To 合計_dgv.Columns.Count - 1
            合計_dgv.Columns(I).DefaultCellStyle.Format = "#,##0" : 合計_dgv.Columns(I).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        Next
        合計_dgv("產品業績", 0).Value = 0 : 合計_dgv("藥品業績", 0).Value = 0 : 合計_dgv("雷射銷售", 0).Value = 0
        合計_dgv("雷射排程", 0).Value = 0 : 合計_dgv("雷射操作", 0).Value = 0 : 合計_dgv("植髮排程", 0).Value = 0
        合計_dgv("手術排程", 0).Value = 0 : 合計_dgv("手術操作", 0).Value = 0 : 合計_dgv("美療業績", 0).Value = 0
        合計_dgv("海菲秀業績", 0).Value = 0
        合計_dgv("產品獎金", 0).Value = 0 : 合計_dgv("藥品獎金", 0).Value = 0 : 合計_dgv("雷銷獎金", 0).Value = 0
        合計_dgv("雷排獎金", 0).Value = 0 : 合計_dgv("雷操獎金", 0).Value = 0 : 合計_dgv("植排獎金", 0).Value = 0
        合計_dgv("手排獎金", 0).Value = 0 : 合計_dgv("手操獎金", 0).Value = 0 : 合計_dgv("美療獎金", 0).Value = 0
        合計_dgv("海菲秀獎金", 0).Value = 0 : 合計_dgv("總獎金", 0).Value = 0 : 合計_dgv("人員", 0).Value = "合計"

        '-------剃除員購-------------------------------------------------------------------------------------------------------------------------------
        For i As Integer = 0 To 交易明細_dgv.Rows.Count - 1
            For J As Integer = 0 To 人員_dgv.Rows.Count - 1
                If Strings.Left(交易明細_dgv("客戶名稱", i).Value.ToString, 3) = Strings.Left(人員_dgv("員工", J).Value.ToString, 3) Then
                    交易明細_dgv("備註", i).Value = "員購"
                    交易明細_dgv("小記", i).Value = 0 : 交易明細_dgv("業績", i).Value = 0
                End If
            Next
            If 交易明細_dgv("使用狀態", i).Value.ToString = "" Then : 交易明細_dgv("預售業績", i).Value = "0" : Else
                If (交易明細_dgv("新類別", i).Value.ToString = "雷射" Or 交易明細_dgv("新類別", i).Value.ToString = "治療") Then
                    Dim input As String = 交易明細_dgv("使用狀態", i).Value.ToString ' 測試字串  使用狀態
                    Dim parts() As String = input.Split("/"c)
                    If parts.Length = 2 AndAlso parts(0) <> parts(1) Then
                        Dim 數字A As Integer = 交易明細_dgv("業績", i).Value.ToString
                        Dim 字串A As String = 交易明細_dgv("使用狀態", i).Value.ToString
                        Dim parts1() As String = 字串A.Split("/"c)
                        Dim 分子 As Integer : Dim 分母 As Integer
                        If Integer.TryParse(parts1(0), 分子) AndAlso Integer.TryParse(parts1(1), 分母) Then                        ' 嘗試轉換為數字
                            If 分母 <> 0 Then : Dim 答案A As Double = 數字A * (分子 / 分母)
                                交易明細_dgv("預售業績", i).Value = Strings.Format(交易明細_dgv("業績", i).Value - 答案A, "#,##0")
                            Else : MsgBox("錯誤：分母不能為零") : End If
                        Else : MsgBox("錯誤：字串格式不正確") : End If
                        交易明細_dgv("備註", i).Value = "預購療程"
                    Else : 交易明細_dgv("預售業績", i).Value = "0" : End If

                Else : 交易明細_dgv("預售業績", i).Value = "0" : End If
            End If
            If (交易明細_dgv("新類別", i).Value.ToString = "雷射" Or 交易明細_dgv("新類別", i).Value.ToString = "治療") Then
                交易明細_dgv("操作人員", i).Value = 交易明細_dgv("產品銷售", i).Value.ToString
                If 交易明細_dgv("使用狀態", i).Value.ToString = "" Then
                    交易明細_dgv("產品銷售", i).Value = ""
                Else
                    Dim 字串A As String = 交易明細_dgv("使用狀態", i).Value.ToString
                    Dim parts1() As String = 字串A.Split("/"c)
                    Dim 分子 As Integer : Dim 分母 As Integer
                    If Integer.TryParse(parts1(0), 分子) AndAlso Integer.TryParse(parts1(1), 分母) Then
                        If 分子 = 分母 Then : 交易明細_dgv("產品銷售", i).Value = "" : Else
                            Dim AA As String = 交易明細_dgv("產品銷售", i).Value
                        End If
                    Else : MsgBox("錯誤：分母不能為零") : End If
                End If
            End If
        Next

        Dim inputStr As String = 月份_cb.Text  ' 例如輸入的字串
        Dim threshold As Integer = 11212   ' 目標：113 年 07 月
        Dim 產品銷售 As String : Dim 排程人員 As String : Dim 操作人員 As String
        For i As Integer = 0 To 獎金計算_dgv.Rows.Count - 1
            獎金計算_dgv("產品業績", i).Value = 0 : 獎金計算_dgv("藥品業績", i).Value = 0 : 獎金計算_dgv("雷射銷售", i).Value = 0
            獎金計算_dgv("雷射排程", i).Value = 0 : 獎金計算_dgv("雷射操作", i).Value = 0 : 獎金計算_dgv("植髮排程", i).Value = 0
            獎金計算_dgv("手術排程", i).Value = 0 : 獎金計算_dgv("手術操作", i).Value = 0 : 獎金計算_dgv("美療業績", i).Value = 0
            獎金計算_dgv("海菲秀業績", i).Value = 0
            獎金計算_dgv("產品獎金", i).Value = 0 : 獎金計算_dgv("藥品獎金", i).Value = 0 : 獎金計算_dgv("雷銷獎金", i).Value = 0
            獎金計算_dgv("雷排獎金", i).Value = 0 : 獎金計算_dgv("雷操獎金", i).Value = 0 : 獎金計算_dgv("植排獎金", i).Value = 0
            獎金計算_dgv("手排獎金", i).Value = 0 : 獎金計算_dgv("手操獎金", i).Value = 0 : 獎金計算_dgv("美療獎金", i).Value = 0
            獎金計算_dgv("海菲秀獎金", i).Value = 0 : 獎金計算_dgv("總獎金", i).Value = 0
            For ii As Integer = 0 To 交易明細_dgv.Rows.Count - 1
                If CInt(inputStr) > threshold Then
                    產品銷售 = 交易明細_dgv("產品銷售", ii).Value.ToString.Replace(" ", "")
                    排程人員 = 交易明細_dgv("排程人員", ii).Value.ToString.Replace(" ", "")
                    操作人員 = 交易明細_dgv("操作人員", ii).Value.ToString.Replace(" ", "")
                Else
                    產品銷售 = Strings.Mid(交易明細_dgv("產品銷售", ii).Value.ToString, 4, 5)
                    排程人員 = Strings.Mid(交易明細_dgv("排程人員", ii).Value.ToString, 4, 5)
                    操作人員 = Strings.Mid(交易明細_dgv("操作人員", ii).Value.ToString, 4, 5)
                End If
                '------一般產品，人工皮-------------------------------------------------------------------------------------------
                If 獎金計算_dgv("人員", i).Value.ToString = 產品銷售 And
                  (交易明細_dgv("新類別", ii).Value.ToString = "一般產品" Or 交易明細_dgv("新類別", ii).Value.ToString = "人工皮") Then
                    獎金計算_dgv("產品業績", i).Value = 獎金計算_dgv("產品業績", i).Value + 交易明細_dgv("小記", ii).Value
                    '---------------獎金計算---------------
                    If 獎金計算_dgv("產品業績", i).Value > 產品標準_nud.Value Then
                        獎金計算_dgv("產品獎金", i).Value = 獎金計算_dgv("產品業績", i).Value / 100 * 產品標後_nud.Value
                    Else
                        獎金計算_dgv("產品獎金", i).Value = 獎金計算_dgv("產品業績", i).Value / 100 * 產品標前_nud.Value
                    End If
                End If
                '------藥品-------------------------------------------------------------------------------------------
                If 獎金計算_dgv("人員", i).Value.ToString = 產品銷售 And 交易明細_dgv("新類別", ii).Value.ToString = "藥品" Then
                    獎金計算_dgv("藥品業績", i).Value = 獎金計算_dgv("藥品業績", i).Value + 交易明細_dgv("小記", ii).Value
                    '---------------獎金計算---------------
                    獎金計算_dgv("藥品獎金", i).Value = 獎金計算_dgv("藥品業績", i).Value / 100 * 藥品標準_nud.Value
                End If
                '------雷射、治療-------------------------------------------------------------------------------------------
                If 獎金計算_dgv("人員", i).Value.ToString = 產品銷售 Then
                    If (交易明細_dgv("新類別", ii).Value.ToString = "雷射" Or 交易明細_dgv("新類別", ii).Value.ToString = "治療") Then
                        If 交易明細_dgv("備註", ii).Value.ToString = "預購療程" Then
                            獎金計算_dgv("雷射銷售", i).Value = 獎金計算_dgv("雷射銷售", i).Value + CInt(交易明細_dgv("預售業績", ii).Value.ToString)
                            '---------------獎金計算---------------
                            If 獎金計算_dgv("雷射銷售", i).Value > 雷售標準_nud.Value Then
                                獎金計算_dgv("雷銷獎金", i).Value = 獎金計算_dgv("雷射銷售", i).Value / 100 * 雷售標後_nud.Value
                            Else
                                獎金計算_dgv("雷銷獎金", i).Value = 獎金計算_dgv("雷射銷售", i).Value / 100 * 雷售標前_nud.Value
                            End If
                        End If
                    End If
                End If
                If 獎金計算_dgv("人員", i).Value.ToString = 排程人員 And
                  (交易明細_dgv("新類別", ii).Value.ToString = "雷射" Or 交易明細_dgv("新類別", ii).Value.ToString = "治療") Then
                    獎金計算_dgv("雷射排程", i).Value = 獎金計算_dgv("雷射排程", i).Value + 交易明細_dgv("業績", ii).Value
                    '---------------獎金計算---------------
                    If 獎金計算_dgv("雷射排程", i).Value > 雷排標準_nud.Value Then
                        獎金計算_dgv("雷排獎金", i).Value = 獎金計算_dgv("雷射排程", i).Value / 100 * 雷排標後_nud.Value
                    Else
                        獎金計算_dgv("雷排獎金", i).Value = 獎金計算_dgv("雷射排程", i).Value / 100 * 雷排標前_nud.Value
                    End If
                End If
                If 獎金計算_dgv("人員", i).Value.ToString = 操作人員 And
                  (交易明細_dgv("新類別", ii).Value.ToString = "雷射" Or 交易明細_dgv("新類別", ii).Value.ToString = "治療") Then
                    獎金計算_dgv("雷射操作", i).Value = 獎金計算_dgv("雷射操作", i).Value + 交易明細_dgv("業績", ii).Value
                    '---------------獎金計算---------------
                    獎金計算_dgv("雷操獎金", i).Value = 獎金計算_dgv("雷射操作", i).Value / 100 * 雷操標準_nud.Value
                End If
                '------植髮/電波-------------------------------------------------------------------------------------------
                If 獎金計算_dgv("人員", i).Value.ToString = 排程人員 And
                  (交易明細_dgv("新類別", ii).Value.ToString = "植髮" Or 交易明細_dgv("新類別", ii).Value.ToString = "電波") And
                   交易明細_dgv("備註", ii).Value.ToString <> "預購療程" Then
                    獎金計算_dgv("植髮排程", i).Value = 獎金計算_dgv("植髮排程", i).Value + 交易明細_dgv("業績", ii).Value
                    '---------------獎金計算---------------
                    獎金計算_dgv("植排獎金", i).Value = 獎金計算_dgv("植髮排程", i).Value / 100 * 植髮標準_nud.Value
                End If
                '------手術-------------------------------------------------------------------------------------------
                If 獎金計算_dgv("人員", i).Value.ToString = 排程人員 And
                  交易明細_dgv("新類別", ii).Value.ToString = "手術" Then
                    獎金計算_dgv("手術排程", i).Value = 獎金計算_dgv("手術排程", i).Value + 交易明細_dgv("業績", ii).Value
                    '---------------獎金計算---------------
                    獎金計算_dgv("手排獎金", i).Value = 獎金計算_dgv("手術排程", i).Value / 100 * 手排標準_nud.Value
                End If
                If 獎金計算_dgv("人員", i).Value.ToString = 操作人員 And
                   交易明細_dgv("新類別", ii).Value.ToString = "手術" Then
                    獎金計算_dgv("手術操作", i).Value = 獎金計算_dgv("手術操作", i).Value + 交易明細_dgv("業績", ii).Value
                    '---------------獎金計算---------------
                    獎金計算_dgv("手操獎金", i).Value = 獎金計算_dgv("手術操作", i).Value / 100 * 手操標準_nud.Value
                End If
                '------美療業績-------------------------------------------------------------------------------------------
                If 獎金計算_dgv("人員", i).Value.ToString = 操作人員 And
                    交易明細_dgv("新類別", ii).Value.ToString = "美療" Then
                    獎金計算_dgv("美療業績", i).Value = 獎金計算_dgv("美療業績", i).Value + 交易明細_dgv("業績", ii).Value
                    '---------------獎金計算---------------
                    獎金計算_dgv("美療獎金", i).Value = 獎金計算_dgv("美療業績", i).Value / 100 * 美療標準_nud.Value
                End If
                '------海菲秀業績-------------------------------------------------------------------------------------------
                If 獎金計算_dgv("人員", i).Value.ToString = 操作人員 And
                    交易明細_dgv("新類別", ii).Value.ToString = "海菲秀" Then
                    獎金計算_dgv("海菲秀業績", i).Value = 獎金計算_dgv("海菲秀業績", i).Value + 交易明細_dgv("業績", ii).Value
                    '---------------獎金計算---------------
                    獎金計算_dgv("海菲秀獎金", i).Value = 獎金計算_dgv("海菲秀業績", i).Value / 100 * 海菲標準_nud.Value
                End If
            Next
            獎金計算_dgv("總獎金", i).Value = 獎金計算_dgv("產品獎金", i).Value + 獎金計算_dgv("藥品獎金", i).Value + 獎金計算_dgv("雷銷獎金", i).Value +
                                              獎金計算_dgv("雷排獎金", i).Value + 獎金計算_dgv("雷操獎金", i).Value + 獎金計算_dgv("植排獎金", i).Value +
                                              獎金計算_dgv("手排獎金", i).Value + 獎金計算_dgv("手操獎金", i).Value + 獎金計算_dgv("美療獎金", i).Value +
                                              獎金計算_dgv("海菲秀獎金", i).Value

            合計_dgv("產品業績", 0).Value += 獎金計算_dgv("產品業績", i).Value : 合計_dgv("藥品業績", 0).Value += 獎金計算_dgv("藥品業績", i).Value
            合計_dgv("雷射銷售", 0).Value += 獎金計算_dgv("雷射銷售", i).Value : 合計_dgv("雷射排程", 0).Value += 獎金計算_dgv("雷射排程", i).Value
            合計_dgv("雷射操作", 0).Value += 獎金計算_dgv("雷射操作", i).Value : 合計_dgv("植髮排程", 0).Value += 獎金計算_dgv("植髮排程", i).Value
            合計_dgv("手術排程", 0).Value += 獎金計算_dgv("手術排程", i).Value : 合計_dgv("手術操作", 0).Value += 獎金計算_dgv("手術操作", i).Value
            合計_dgv("美療業績", 0).Value += 獎金計算_dgv("美療業績", i).Value : 合計_dgv("海菲秀業績", 0).Value += 獎金計算_dgv("海菲秀業績", i).Value
            合計_dgv("產品獎金", 0).Value += 獎金計算_dgv("產品獎金", i).Value : 合計_dgv("藥品獎金", 0).Value += 獎金計算_dgv("藥品獎金", i).Value
            合計_dgv("雷銷獎金", 0).Value += 獎金計算_dgv("雷銷獎金", i).Value : 合計_dgv("雷排獎金", 0).Value += 獎金計算_dgv("雷排獎金", i).Value
            合計_dgv("雷操獎金", 0).Value += 獎金計算_dgv("雷操獎金", i).Value : 合計_dgv("植排獎金", 0).Value += 獎金計算_dgv("植排獎金", i).Value
            合計_dgv("手排獎金", 0).Value += 獎金計算_dgv("手排獎金", i).Value : 合計_dgv("手操獎金", 0).Value += 獎金計算_dgv("手操獎金", i).Value
            合計_dgv("美療獎金", 0).Value += 獎金計算_dgv("美療獎金", i).Value : 合計_dgv("海菲秀獎金", 0).Value += 獎金計算_dgv("海菲秀獎金", i).Value
            合計_dgv("總獎金", 0).Value += 獎金計算_dgv("總獎金", i).Value
        Next
        獎金計算_dgv.Columns(1).DefaultCellStyle.BackColor = Color.LightGreen : 獎金計算_dgv.Columns(2).DefaultCellStyle.BackColor = Color.LightGreen
        獎金計算_dgv.Columns(3).DefaultCellStyle.BackColor = Color.LightGreen : 獎金計算_dgv.Columns(4).DefaultCellStyle.BackColor = Color.LightGreen
        獎金計算_dgv.Columns(5).DefaultCellStyle.BackColor = Color.LightGreen : 獎金計算_dgv.Columns(6).DefaultCellStyle.BackColor = Color.LightGreen
        獎金計算_dgv.Columns(7).DefaultCellStyle.BackColor = Color.LightGreen : 獎金計算_dgv.Columns(8).DefaultCellStyle.BackColor = Color.LightGreen
        獎金計算_dgv.Columns(9).DefaultCellStyle.BackColor = Color.LightGreen : 獎金計算_dgv.Columns(10).DefaultCellStyle.BackColor = Color.LightGreen

        獎金計算_dgv.Columns(11).DefaultCellStyle.BackColor = Color.LightBlue : 獎金計算_dgv.Columns(12).DefaultCellStyle.BackColor = Color.LightBlue
        獎金計算_dgv.Columns(13).DefaultCellStyle.BackColor = Color.LightBlue : 獎金計算_dgv.Columns(14).DefaultCellStyle.BackColor = Color.LightBlue
        獎金計算_dgv.Columns(15).DefaultCellStyle.BackColor = Color.LightBlue : 獎金計算_dgv.Columns(16).DefaultCellStyle.BackColor = Color.LightBlue
        獎金計算_dgv.Columns(17).DefaultCellStyle.BackColor = Color.LightBlue : 獎金計算_dgv.Columns(18).DefaultCellStyle.BackColor = Color.LightBlue
        獎金計算_dgv.Columns(19).DefaultCellStyle.BackColor = Color.LightBlue : 獎金計算_dgv.Columns(20).DefaultCellStyle.BackColor = Color.LightBlue

        銷售額_lb.Text = "0"
        For i As Integer = 0 To 交易明細_dgv.Rows.Count - 1
            If 交易明細_dgv("數量", i).Value = 0 Then
                交易明細_dgv("單價", i).Value = 0
            Else
                交易明細_dgv("單價", i).Value = 交易明細_dgv("小記", i).Value / 交易明細_dgv("數量", i).Value
            End If
            銷售額_lb.Text = CInt(銷售額_lb.Text) + 交易明細_dgv("小記", i).Value
        Next : 銷售額_lb.Text = Strings.Format(CInt(銷售額_lb.Text), "#,##0")

        For i As Integer = 0 To 獎金計算_dgv.Rows.Count - 1
            If 獎金計算_dgv("人員", i).Value.ToString = "" Then : 獎金計算_dgv("人員", i).Value = "無指定" : End If
        Next
        '-------------------------合計DGV變形----------------------------------------------------------------------------------------------------------------------
        If IsVerticalScrollBarVisible(獎金計算_dgv) Then : 合計_dgv.Size = New Size(獎金計算_dgv.Size.Width - 17, 合計_dgv.Size.Height)
        Else : 合計_dgv.Size = New Size(獎金計算_dgv.Size.Width, 合計_dgv.Size.Height) : End If
    End Sub
    Private Sub SET_使用者下拉清單載入()
        SQL2 = " WHERE (銷售日期 BETWEEN '11212' AND '15001') "
        SQL_客戶交易明細表_月份() : 月份_cb.Items.Clear()
        While (dr.Read()) : 月份_cb.Items.Add(dr("月份")) : End While : conn.Close()
    End Sub
    Private Sub 業績獎金計算表_新_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.MdiParent = GRAMS_SYS : Me.WindowState = 2 : Me.AutoScroll = True
        交易明細_dgv.BringToFront()
    End Sub
    Private Sub 業績獎金計算表_新_Shown(sender As Object, e As EventArgs) Handles MyBase.Shown
        SET_使用者下拉清單載入() : 月份_cb.SelectedIndex = 月份_cb.Items.Count - 1
        SET_客戶交易明細表()
    End Sub
    Private Sub 交易明細_dgv_RowPostPaint(ByVal sender As Object, ByVal e As DataGridViewRowPostPaintEventArgs) Handles 交易明細_dgv.RowPostPaint
        Dim linePen As New Pen(Color.Red, 2)
        If e.RowIndex = 交易明細_dgv.Rows.Count - 1 Then : Exit Sub : Else
            If 交易明細_dgv("客戶編號", e.RowIndex).Value.ToString <> 交易明細_dgv("客戶編號", 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_MouseUp(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles 交易明細_dgv.MouseUp
        If 交易明細_dgv.Rows.Count = 0 Then : Else
            Dim 位置1 As Integer = 交易明細_dgv.SelectedCells(0).ColumnIndex : Dim 位置2 As Integer = 交易明細_dgv.SelectedCells(0).RowIndex
            If 位置1 = 14 Then
                Dim result As DialogResult = MessageBox.Show("請確定選中的資料行是否要加入 [預購療程] 的備註?" &
                                                    vbCrLf & "請注意 [預購療程] 會引響該筆資料的計算!!" &
                                                    vbCrLf & "全部操作完後要記得存檔!!", "請選擇", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
                If result = DialogResult.Yes Then
                    交易明細_dgv.Rows(位置2).Cells(位置1).Value = "預購療程"
                Else
                    交易明細_dgv.Rows(位置2).Cells(位置1).Value = ""
                End If
            End If
        End If
    End Sub
    Private Sub 月份_cb_SelectedIndexChanged(sender As Object, e As EventArgs) Handles 月份_cb.SelectedIndexChanged
        SET_客戶交易明細表()
    End Sub
    Private Sub 重新計算_bt_Click(sender As Object, e As EventArgs) Handles 重新計算_bt.Click
        SET_客戶交易明細表()
    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 = NewMethod(xlBook) : xlApp.Sheets(1).Select
        xlApp.Sheets(1).Name = "業績明細" : xlBook.Activate() : xlSheet.Activate()

        xlSheet.Cells(1, 1) = "黃柏翰皮膚科診所業績資料明細" : xlSheet.Cells(2, 1) = 月份_cb.Text & " - 全月"
        xlSheet.Cells(3, 1) = "流水號" : xlSheet.Cells(3, 2) = "銷售日期" : xlSheet.Cells(3, 3) = "客戶編號" : xlSheet.Cells(3, 4) = "客戶名稱" : xlSheet.Cells(3, 5) = "生日"
        xlSheet.Cells(3, 6) = "品項" : xlSheet.Cells(3, 7) = "品名" : xlSheet.Cells(3, 8) = "單價" : xlSheet.Cells(3, 9) = "數量"
        xlSheet.Cells(3, 10) = "小記" : xlSheet.Cells(3, 11) = "業績" : xlSheet.Cells(3, 12) = "預售業績" : xlSheet.Cells(3, 13) = "備註" : xlSheet.Cells(3, 14) = "醫師"
        xlSheet.Cells(3, 15) = "排程人員" : xlSheet.Cells(3, 16) = "操作人員" : xlSheet.Cells(3, 17) = "產品銷售" : xlSheet.Cells(3, 18) = "收款人員" : xlSheet.Cells(3, 19) = "使用狀態"
        xlSheet.Cells(3, 20) = "新類別"
        For i As Integer = 0 To 交易明細_dgv.Rows.Count - 1
            xlSheet.Cells(4 + i, 1) = 交易明細_dgv("流水號", i).Value.ToString : xlSheet.Cells(4 + i, 2) = 交易明細_dgv("銷售日期", i).Value.ToString
            xlSheet.Cells(4 + i, 3) = 交易明細_dgv("客戶編號", i).Value.ToString : xlSheet.Cells(4 + i, 4) = 交易明細_dgv("客戶名稱", i).Value.ToString
            xlSheet.Cells(4 + i, 5) = 交易明細_dgv("生日", i).Value.ToString : xlSheet.Cells(4 + i, 6) = 交易明細_dgv("品項", i).Value.ToString
            xlSheet.Cells(4 + i, 7) = 交易明細_dgv("品名", i).Value.ToString : xlSheet.Cells(4 + i, 8) = 交易明細_dgv("單價", i).Value.ToString
            xlSheet.Cells(4 + i, 9) = 交易明細_dgv("數量", i).Value.ToString : xlSheet.Cells(4 + i, 10) = 交易明細_dgv("小記", i).Value.ToString
            xlSheet.Cells(4 + i, 11) = 交易明細_dgv("業績", i).Value.ToString : xlSheet.Cells(4 + i, 12) = 交易明細_dgv("預售業績", i).Value.ToString
            xlSheet.Cells(4 + i, 13) = 交易明細_dgv("備註", i).Value.ToString : xlSheet.Cells(4 + i, 14) = 交易明細_dgv("醫師", i).Value.ToString
            xlSheet.Cells(4 + i, 15) = 交易明細_dgv("排程人員", i).Value.ToString : xlSheet.Cells(4 + i, 16) = 交易明細_dgv("操作人員", i).Value.ToString
            xlSheet.Cells(4 + i, 17) = 交易明細_dgv("產品銷售", i).Value.ToString : xlSheet.Cells(4 + i, 18) = 交易明細_dgv("收款人員", i).Value.ToString
            xlSheet.Cells(4 + i, 19) = "'" & 交易明細_dgv("使用狀態", i).Value.ToString : xlSheet.Cells(4 + i, 20) = 交易明細_dgv("新類別", i).Value.ToString
        Next : 位置1 = 4 + 交易明細_dgv.Rows.Count - 1 : AA(xlApp, xlSheet)
        xlSheet.PageSetup.PrintArea = "" : xlApp.Cells.Select() : xlSheet.Range("B1").Select() : xlApp.Application.WindowState = xlMinimized : MsgBox("列印完成")
    End Sub
    Private Shared Function NewMethod(xlBook As Workbook) As Worksheet
        Return CType(xlBook.Worksheets.Add, Worksheet)
    End Function
    Private Sub AA(ByVal myExcel As Microsoft.Office.Interop.Excel.Application, ByVal xlSheet As Microsoft.Office.Interop.Excel.Worksheet)
        xlSheet.Cells.Select()
        With myExcel.Selection.Font : .Name = "微軟正黑體" : .Size = 12 : .Strikethrough = False : .Superscript = False : .Subscript = False : .OutlineFont = False
            .Shadow = False : .Underline = xlUnderlineStyleNone : .ThemeColor = xlThemeColorLight1 : .TintAndShade = 0 : .ThemeFont = xlThemeFontNone
        End With
        xlSheet.Columns("A:A").Select : myExcel.Selection.ColumnWidth = 13 : xlSheet.Columns("C:C").Select : myExcel.Selection.ColumnWidth = 13
        xlSheet.Columns("D:D").Select : xlSheet.Columns("D:D").EntireColumn.AutoFit : xlSheet.Columns("E:E").Select : xlSheet.Columns("E:E").EntireColumn.AutoFit
        xlSheet.Columns("F:F").Select : xlSheet.Columns("F:F").EntireColumn.AutoFit : xlSheet.Columns("G:G").Select : xlSheet.Columns("G:G").EntireColumn.AutoFit
        xlSheet.Columns("H:H").Select : myExcel.Selection.Style = "Comma"
        myExcel.Selection.NumberFormatLocal = "_-* #,##0_-;-* #,##0_-;_-* ""-""??_-;_-@_-" : xlSheet.Columns("H:H").EntireColumn.AutoFit
        xlSheet.Columns("I:I").Select : myExcel.Selection.Style = "Comma"
        myExcel.Selection.NumberFormatLocal = "_-* #,##0_-;-* #,##0_-;_-* ""-""??_-;_-@_-" : xlSheet.Columns("I:I").EntireColumn.AutoFit
        xlSheet.Columns("J:J").Select : myExcel.Selection.Style = "Comma"
        myExcel.Selection.NumberFormatLocal = "_-* #,##0_-;-* #,##0_-;_-* ""-""??_-;_-@_-" : xlSheet.Columns("J:J").EntireColumn.AutoFit
        xlSheet.Columns("K:K").Select : myExcel.Selection.Style = "Comma"
        myExcel.Selection.NumberFormatLocal = "_-* #,##0_-;-* #,##0_-;_-* ""-""??_-;_-@_-" : xlSheet.Columns("K:K").EntireColumn.AutoFit
        xlSheet.Columns("L:L").Select : myExcel.Selection.Style = "Comma"
        myExcel.Selection.NumberFormatLocal = "_-* #,##0_-;-* #,##0_-;_-* ""-""??_-;_-@_-" : xlSheet.Columns("L:L").EntireColumn.AutoFit
        xlSheet.Columns("M:M").EntireColumn.AutoFit : xlSheet.Columns("N:N").EntireColumn.AutoFit : xlSheet.Columns("O:O").EntireColumn.AutoFit
        xlSheet.Columns("P:P").EntireColumn.AutoFit : xlSheet.Columns("Q:Q").EntireColumn.AutoFit : xlSheet.Columns("R:R").Select : myExcel.Selection.EntireColumn.Hidden = True
        xlSheet.Columns("S:S").EntireColumn.AutoFit : xlSheet.Columns("T:T").Select : xlSheet.Columns("T:T").EntireColumn.AutoFit
        xlSheet.Range("A1:T1").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:T2").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.Rows("3:3").Select : myExcel.Selection.RowHeight = 35
        With myExcel.Selection : .HorizontalAlignment = xlGeneral : .VerticalAlignment = xlTop : .WrapText = False : .Orientation = 0 : .AddIndent = False
            .IndentLevel = 0 : .ShrinkToFit = False : .ReadingOrder = xlContext : .MergeCells = False
        End With : myExcel.Selection.AutoFilter
        xlSheet.Rows("4:4").Select : myExcel.ActiveWindow.FreezePanes = True
        xlSheet.Range("A3:T3").Select()
        With myExcel.Selection.Interior : .Pattern = xlSolid : .PatternColorIndex = xlAutomatic : .ThemeColor = xlThemeColorLight2 : .TintAndShade = 0.899990844447157
            .PatternTintAndShade = 0
        End With
        xlSheet.Range("A3:T520").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
        xlSheet.Range("B4").Select()
    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 = NewMethod(xlBook) : xlApp.Sheets(1).Select
        xlApp.Sheets(1).Name = "獎金簽收單" : xlBook.Activate() : xlSheet.Activate()
        Dim 位置 As Integer = 0
        For i As Integer = 0 To 獎金計算_dgv.Rows.Count - 1
            If 獎金計算_dgv("人員", i).Value.ToString = "黃柏翰" Or 獎金計算_dgv("人員", i).Value.ToString = "無指定" Then : Else
                xlSheet.Cells(2 + 位置, 2) = "獎金月份" : xlSheet.Cells(2 + 位置, 3) = Strings.Left(月份_cb.Text, 3) & "年" & Strings.Right(月份_cb.Text, 2) & "月"
                xlSheet.Cells(4 + 位置, 2) = "姓名" : xlSheet.Cells(4 + 位置, 3) = 獎金計算_dgv("人員", i).Value.ToString
                xlSheet.Cells(6 + 位置, 2) = "個人應發總獎金 " & vbCrLf & "(含預購獎金)" : xlSheet.Cells(6 + 位置, 3) = 獎金計算_dgv("總獎金", i).Value.ToString
                xlSheet.Cells(8 + 位置, 2) = "備註" : xlSheet.Cells(10 + 位置, 2) = "領款人簽收" : xlSheet.Cells(12 + 位置, 2) = "診所留存"
                xlSheet.Cells(2 + 位置, 4) = "獎金明細如下:"
                xlSheet.Cells(3 + 位置, 4) = "產品銷售" : xlSheet.Cells(3 + 位置, 5) = 獎金計算_dgv("產品獎金", i).Value.ToString
                xlSheet.Cells(4 + 位置, 4) = "藥品銷售" : xlSheet.Cells(4 + 位置, 5) = 獎金計算_dgv("藥品獎金", i).Value.ToString
                xlSheet.Cells(5 + 位置, 4) = "一般雷射排程" : xlSheet.Cells(5 + 位置, 5) = 獎金計算_dgv("雷排獎金", i).Value.ToString
                xlSheet.Cells(6 + 位置, 4) = "手術排程" : xlSheet.Cells(6 + 位置, 5) = 獎金計算_dgv("手排獎金", i).Value.ToString
                xlSheet.Cells(7 + 位置, 4) = "美療" : xlSheet.Cells(7 + 位置, 5) = 獎金計算_dgv("美療獎金", i).Value.ToString
                xlSheet.Cells(8 + 位置, 4) = "海菲秀" : xlSheet.Cells(8 + 位置, 5) = 獎金計算_dgv("海菲秀獎金", i).Value.ToString
                xlSheet.Cells(9 + 位置, 4) = "一般雷射操作" : xlSheet.Cells(9 + 位置, 5) = 獎金計算_dgv("雷操獎金", i).Value.ToString
                xlSheet.Cells(10 + 位置, 4) = "手術操作" : xlSheet.Cells(10 + 位置, 5) = 獎金計算_dgv("手操獎金", i).Value.ToString
                xlSheet.Cells(11 + 位置, 4) = "預售一般雷射" : xlSheet.Cells(11 + 位置, 5) = 獎金計算_dgv("雷銷獎金", i).Value.ToString

                xlSheet.Cells(2 + 位置, 8) = "獎金月份" : xlSheet.Cells(2 + 位置, 9) = Strings.Left(月份_cb.Text, 3) & "年" & Strings.Right(月份_cb.Text, 2) & "月"
                xlSheet.Cells(4 + 位置, 8) = "姓名" : xlSheet.Cells(4 + 位置, 9) = 獎金計算_dgv("人員", i).Value.ToString
                xlSheet.Cells(6 + 位置, 8) = "個人應發總獎金 " & vbCrLf & "(含預購獎金)" : xlSheet.Cells(6 + 位置, 9) = 獎金計算_dgv("總獎金", i).Value.ToString
                xlSheet.Cells(8 + 位置, 8) = "備註" : xlSheet.Cells(12 + 位置, 8) = "個人留存"
                xlSheet.Cells(2 + 位置, 10) = "獎金明細如下:"
                xlSheet.Cells(3 + 位置, 10) = "產品銷售" : xlSheet.Cells(3 + 位置, 11) = 獎金計算_dgv("產品獎金", i).Value.ToString
                xlSheet.Cells(4 + 位置, 10) = "藥品銷售" : xlSheet.Cells(4 + 位置, 11) = 獎金計算_dgv("藥品獎金", i).Value.ToString
                xlSheet.Cells(5 + 位置, 10) = "一般雷射排程" : xlSheet.Cells(5 + 位置, 11) = 獎金計算_dgv("雷排獎金", i).Value.ToString
                xlSheet.Cells(6 + 位置, 10) = "手術排程" : xlSheet.Cells(6 + 位置, 11) = 獎金計算_dgv("手排獎金", i).Value.ToString
                xlSheet.Cells(7 + 位置, 10) = "美療" : xlSheet.Cells(7 + 位置, 11) = 獎金計算_dgv("美療獎金", i).Value.ToString
                xlSheet.Cells(8 + 位置, 10) = "海菲秀" : xlSheet.Cells(8 + 位置, 11) = 獎金計算_dgv("海菲秀獎金", i).Value.ToString
                xlSheet.Cells(9 + 位置, 10) = "一般雷射操作" : xlSheet.Cells(9 + 位置, 11) = 獎金計算_dgv("雷操獎金", i).Value.ToString
                xlSheet.Cells(10 + 位置, 10) = "手術操作" : xlSheet.Cells(10 + 位置, 11) = 獎金計算_dgv("手操獎金", i).Value.ToString
                xlSheet.Cells(11 + 位置, 10) = "預售一般雷射" : xlSheet.Cells(11 + 位置, 11) = 獎金計算_dgv("雷銷獎金", i).Value.ToString
                位置 += 12
            End If
        Next : BB(xlApp, xlSheet)
        xlSheet.PageSetup.PrintArea = "" : xlApp.Cells.Select() : xlSheet.Range("B1").Select() : xlApp.Application.WindowState = xlMinimized : MsgBox("列印完成")
    End Sub
    Private Sub BB(ByVal myExcel As Microsoft.Office.Interop.Excel.Application, ByVal xlSheet As Microsoft.Office.Interop.Excel.Worksheet)
        xlSheet.Cells.Select()
        With myExcel.Selection.Font : .Name = "微軟正黑體" : .Size = 12 : .Strikethrough = False : .Superscript = False : .Subscript = False : .OutlineFont = False
            .Shadow = False : .Underline = xlUnderlineStyleNone : .ThemeColor = xlThemeColorLight1 : .TintAndShade = 0 : .ThemeFont = xlThemeFontNone
        End With
        xlSheet.Columns("A:A").Select : myExcel.Selection.ColumnWidth = 3 : xlSheet.Columns("B:B").Select : myExcel.Selection.ColumnWidth = 35
        xlSheet.Columns("C:C").Select : myExcel.Selection.ColumnWidth = 22.75 : xlSheet.Columns("D:D").Select : myExcel.Selection.ColumnWidth = 28.38
        xlSheet.Columns("E:E").Select : myExcel.Selection.ColumnWidth = 18.75 : xlSheet.Columns("F:G").Select : myExcel.Selection.ColumnWidth = 3
        xlSheet.Columns("H:H").Select : myExcel.Selection.ColumnWidth = 35 : xlSheet.Columns("I:I").Select : myExcel.Selection.ColumnWidth = 22.75
        xlSheet.Columns("J:J").Select : myExcel.Selection.ColumnWidth = 28.38 : xlSheet.Columns("K:K").Select : myExcel.Selection.ColumnWidth = 18.75
        xlSheet.Columns("L:L").Select : myExcel.Selection.ColumnWidth = 3
        xlSheet.Columns("B:B").Select : myExcel.Selection.Font.Bold = True
        With myExcel.Selection.Font : .Name = "微軟正黑體" : .Size = 24 : .Strikethrough = False : .Superscript = False : .Subscript = False : .OutlineFont = False
            .Shadow = False : .Underline = xlUnderlineStyleNone : .ThemeColor = xlThemeColorLight1 : .TintAndShade = 0 : .ThemeFont = xlThemeFontNone
        End With
        xlSheet.Columns("C:C").Select
        With myExcel.Selection : .HorizontalAlignment = xlCenter : .VerticalAlignment = xlCenter : .WrapText = False : .Orientation = 0 : .AddIndent = False
            .IndentLevel = 0 : .ShrinkToFit = False : .ReadingOrder = xlContext : .MergeCells = False
        End With
        With myExcel.Selection.Font : .Name = "微軟正黑體" : .Size = 24 : .Strikethrough = False : .Superscript = False : .Subscript = False : .OutlineFont = False
            .Shadow = False : .Underline = xlUnderlineStyleNone : .ThemeColor = xlThemeColorLight1 : .TintAndShade = 0 : .ThemeFont = xlThemeFontNone
        End With
        xlSheet.Columns("D:D").Select
        With myExcel.Selection.Font : .Name = "微軟正黑體" : .Size = 24 : .Strikethrough = False : .Superscript = False : .Subscript = False : .OutlineFont = False
            .Shadow = False : .Underline = xlUnderlineStyleNone : .ThemeColor = xlThemeColorLight1 : .TintAndShade = 0 : .ThemeFont = xlThemeFontNone
        End With : myExcel.Selection.Font.Bold = True
        xlSheet.Columns("E:E").Select
        With myExcel.Selection.Font : .Name = "微軟正黑體" : .Size = 24 : .Strikethrough = False : .Superscript = False : .Subscript = False : .OutlineFont = False
            .Shadow = False : .Underline = xlUnderlineStyleNone : .ThemeColor = xlThemeColorLight1 : .TintAndShade = 0 : .ThemeFont = xlThemeFontNone
        End With
        xlSheet.Columns("H:H").Select
        With myExcel.Selection.Font : .Name = "微軟正黑體" : .Size = 24 : .Strikethrough = False : .Superscript = False : .Subscript = False : .OutlineFont = False
            .Shadow = False : .Underline = xlUnderlineStyleNone : .ThemeColor = xlThemeColorLight1 : .TintAndShade = 0 : .ThemeFont = xlThemeFontNone
        End With : myExcel.Selection.Font.Bold = True
        xlSheet.Columns("I:I").Select
        With myExcel.Selection.Font : .Name = "微軟正黑體" : .Size = 24 : .Strikethrough = False : .Superscript = False : .Subscript = False : .OutlineFont = False
            .Shadow = False : .Underline = xlUnderlineStyleNone : .ThemeColor = xlThemeColorLight1 : .TintAndShade = 0 : .ThemeFont = xlThemeFontNone
        End With
        With myExcel.Selection : .HorizontalAlignment = xlCenter : .VerticalAlignment = xlCenter : .WrapText = False : .Orientation = 0 : .AddIndent = False
            .IndentLevel = 0 : .ShrinkToFit = False : .ReadingOrder = xlContext : .MergeCells = False
        End With
        xlSheet.Columns("J:J").Select : myExcel.Selection.Font.Bold = True
        With myExcel.Selection.Font : .Name = "微軟正黑體" : .Size = 24 : .Strikethrough = False : .Superscript = False : .Subscript = False : .OutlineFont = False
            .Shadow = False : .Underline = xlUnderlineStyleNone : .ThemeColor = xlThemeColorLight1 : .TintAndShade = 0 : .ThemeFont = xlThemeFontNone
        End With
        xlSheet.Columns("K:K").Select
        With myExcel.Selection.Font : .Name = "微軟正黑體" : .Size = 24 : .Strikethrough = False : .Superscript = False : .Subscript = False : .OutlineFont = False
            .Shadow = False : .Underline = xlUnderlineStyleNone : .ThemeColor = xlThemeColorLight1 : .TintAndShade = 0 : .ThemeFont = xlThemeFontNone
        End With

        Dim 位置 As Integer = 0 : Dim 人數 As Integer
        For i As Integer = 0 To 獎金計算_dgv.Rows.Count - 1
            If 獎金計算_dgv("人員", i).Value.ToString = "黃柏翰" Or 獎金計算_dgv("人員", i).Value.ToString = "無指定" Then : Else
                xlSheet.Rows(1 + 位置 & ":" & 1 + 位置).Select : myExcel.Selection.RowHeight = 30 : xlSheet.Rows(2 + 位置 & ":" & 10 + 位置).Select : myExcel.Selection.RowHeight = 30.75
                xlSheet.Rows(11 + 位置 & ":" & 11 + 位置).Select : myExcel.Selection.RowHeight = 31.5 : xlSheet.Rows(12 + 位置 & ":" & 12 + 位置).Select : myExcel.Selection.RowHeight = 30.75
                xlSheet.Range("B" & 2 + 位置 & ":B" & 3 + 位置).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
                With myExcel.Selection : .HorizontalAlignment = xlLeft : .VerticalAlignment = xlCenter : .WrapText = False : .Orientation = 0 : .AddIndent = False
                    .IndentLevel = 0 : .ShrinkToFit = False : .ReadingOrder = xlContext : .MergeCells = True : End With
                xlSheet.Range("B" & 4 + 位置 & ":B" & 5 + 位置).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
                With myExcel.Selection : .HorizontalAlignment = xlLeft : .VerticalAlignment = xlCenter : .WrapText = False : .Orientation = 0 : .AddIndent = False
                    .IndentLevel = 0 : .ShrinkToFit = False : .ReadingOrder = xlContext : .MergeCells = True : End With
                xlSheet.Range("B" & 6 + 位置 & ":B" & 7 + 位置).Select()
                With myExcel.Selection : .HorizontalAlignment = xlCenter : .VerticalAlignment = xlCenter : .Orientation = 0 : .AddIndent = False : .IndentLevel = 0
                    .ShrinkToFit = False : .ReadingOrder = xlContext : .MergeCells = False : End With : myExcel.Selection.Merge
                With myExcel.Selection : .HorizontalAlignment = xlLeft : .VerticalAlignment = xlCenter : .WrapText = True : .Orientation = 0 : .AddIndent = False
                    .IndentLevel = 0 : .ShrinkToFit = False : .ReadingOrder = xlContext : .MergeCells = True : End With
                xlSheet.Range("B" & 8 + 位置 & ":B" & 9 + 位置).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
                With myExcel.Selection : .HorizontalAlignment = xlLeft : .VerticalAlignment = xlCenter : .WrapText = False : .Orientation = 0 : .AddIndent = False
                    .IndentLevel = 0 : .ShrinkToFit = False : .ReadingOrder = xlContext : .MergeCells = True : End With
                xlSheet.Range("B" & 10 + 位置 & ":B" & 11 + 位置).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
                With myExcel.Selection : .HorizontalAlignment = xlLeft : .VerticalAlignment = xlCenter : .WrapText = False : .Orientation = 0 : .AddIndent = False
                    .IndentLevel = 0 : .ShrinkToFit = False : .ReadingOrder = xlContext : .MergeCells = True : End With
                xlSheet.Range("C" & 2 + 位置 & ":C" & 3 + 位置).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("C" & 4 + 位置 & ":C" & 5 + 位置).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("C" & 6 + 位置 & ":C" & 7 + 位置).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("C" & 8 + 位置 & ":C" & 9 + 位置).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("C" & 10 + 位置 & ":C" & 11 + 位置).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("E" & 2 + 位置 & ":E" & 11 + 位置).Select()
                With myExcel.Selection.Font : .Name = "微軟正黑體" : .Size = 24 : .Strikethrough = False : .Superscript = False : .Subscript = False : .OutlineFont = False
                    .Shadow = False : .Underline = xlUnderlineStyleNone : .ThemeColor = xlThemeColorLight1 : .TintAndShade = 0 : .ThemeFont = xlThemeFontNone : End With
                xlSheet.Range("H" & 2 + 位置 & ":H" & 3 + 位置).Select()
                With myExcel.Selection : .HorizontalAlignment = xlCenter : .VerticalAlignment = xlCenter : .WrapText = False : .Orientation = 0 : .IndentLevel = 0
                    .ShrinkToFit = False : .ReadingOrder = xlContext : .MergeCells = False : End With : myExcel.Selection.Merge
                With myExcel.Selection : .HorizontalAlignment = xlLeft : .VerticalAlignment = xlCenter : .WrapText = False : .Orientation = 0 : .AddIndent = False
                    .IndentLevel = 0 : .ShrinkToFit = False : .ReadingOrder = xlContext : .MergeCells = True : End With
                xlSheet.Range("H" & 4 + 位置 & ":H" & 5 + 位置).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
                With myExcel.Selection : .HorizontalAlignment = xlLeft : .VerticalAlignment = xlCenter : .WrapText = False : .Orientation = 0 : .AddIndent = False
                    .IndentLevel = 0 : .ShrinkToFit = False : .ReadingOrder = xlContext : .MergeCells = True : End With
                xlSheet.Range("H" & 6 + 位置 & ":H" & 7 + 位置).Select()
                With myExcel.Selection : .HorizontalAlignment = xlCenter : .VerticalAlignment = xlCenter : .Orientation = 0 : .AddIndent = False : .IndentLevel = 0
                    .ShrinkToFit = False : .ReadingOrder = xlContext : .MergeCells = False : End With : myExcel.Selection.Merge
                With myExcel.Selection : .HorizontalAlignment = xlLeft : .VerticalAlignment = xlCenter : .WrapText = True : .Orientation = 0 : .AddIndent = False
                    .IndentLevel = 0 : .ShrinkToFit = False : .ReadingOrder = xlContext : .MergeCells = True : End With
                xlSheet.Range("H" & 8 + 位置 & ":H" & 11 + 位置).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
                With myExcel.Selection : .HorizontalAlignment = xlLeft : .VerticalAlignment = xlCenter : .WrapText = False : .Orientation = 0 : .AddIndent = False
                    .IndentLevel = 0 : .ShrinkToFit = False : .ReadingOrder = xlContext : .MergeCells = True : End With
                xlSheet.Range("I" & 2 + 位置 & ":I" & 3 + 位置).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("I" & 4 + 位置 & ":I" & 5 + 位置).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("I" & 6 + 位置 & ":I" & 7 + 位置).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("I" & 8 + 位置 & ":I" & 11 + 位置).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("B" & 2 + 位置 & ":E" & 11 + 位置).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 = xlMedium : End With
                With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
                With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
                With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
                With myExcel.Selection.Borders(xlInsideVertical) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
                With myExcel.Selection.Borders(xlInsideHorizontal) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
                xlSheet.Range("H" & 2 + 位置 & ":K" & 11 + 位置).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 = xlMedium : End With
                With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
                With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
                With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
                With myExcel.Selection.Borders(xlInsideVertical) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
                With myExcel.Selection.Borders(xlInsideHorizontal) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
                xlSheet.Range("A" & 1 + 位置 & ":F" & 12 + 位置).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 = xlMedium : End With
                With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
                With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
                With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
                xlSheet.Range("G" & 1 + 位置 & ":L" & 12 + 位置).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 = xlMedium : End With
                With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
                With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
                With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With

                xlSheet.Range("C" & 6 + 位置 & " :C" & 7 + 位置).Select() : myExcel.Selection.NumberFormatLocal = "#,##0"
                xlSheet.Range("E" & 3 + 位置 & ":E" & 11 + 位置).Select() : myExcel.Selection.NumberFormatLocal = "#,##0"
                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("I" & 6 + 位置 & ":I" & 7 + 位置).Select() : myExcel.Selection.NumberFormatLocal = "#,##0"
                xlSheet.Range("K" & 3 + 位置 & ":K" & 11 + 位置).Select() : myExcel.Selection.NumberFormatLocal = "#,##0"
                With myExcel.Selection : .HorizontalAlignment = xlCenter : .VerticalAlignment = xlCenter : .WrapText = False : .Orientation = 0 : .AddIndent = False
                    .IndentLevel = 0 : .ShrinkToFit = False : .ReadingOrder = xlContext : .MergeCells = False : End With
                位置 += 12 : 人數 += 1
            End If
        Next
        Dim rowNumber As Integer
        If 人數 > 4 Then : rowNumber = 49 : Dim rng As Microsoft.Office.Interop.Excel.Range = xlSheet.Rows(rowNumber) : xlSheet.HPageBreaks.Add(rng) : End If
        If 人數 > 8 Then : rowNumber = 97 : Dim rng As Microsoft.Office.Interop.Excel.Range = xlSheet.Rows(rowNumber) : xlSheet.HPageBreaks.Add(rng) : End If
        If 人數 > 12 Then : rowNumber = 145 : Dim rng As Microsoft.Office.Interop.Excel.Range = xlSheet.Rows(rowNumber) : xlSheet.HPageBreaks.Add(rng) : End If
        myExcel.Application.PrintCommunication = False
        With myExcel.ActiveSheet.PageSetup : .PrintTitleRows = "" : .PrintTitleColumns = "" : End With
        myExcel.Application.PrintCommunication = True : myExcel.ActiveSheet.PageSetup.PrintArea = "" : myExcel.Application.PrintCommunication = False
        With myExcel.ActiveSheet.PageSetup
            .LeftHeader = "" : .CenterHeader = "" : .RightHeader = "" : .LeftFooter = "" : .CenterFooter = "" : .RightFooter = ""
            .LeftMargin = myExcel.Application.InchesToPoints(0.25) : .RightMargin = myExcel.Application.InchesToPoints(0.25) : .TopMargin = myExcel.Application.InchesToPoints(0.75)
            .BottomMargin = myExcel.Application.InchesToPoints(0.75) : .HeaderMargin = myExcel.Application.InchesToPoints(0.3) : .FooterMargin = myExcel.Application.InchesToPoints(0.3)
            .PrintHeadings = False : .PrintGridlines = False : .PrintQuality = 1200 : .CenterHorizontally = False : .CenterVertically = False : .Orientation = xlPortrait
            .Draft = False : .FirstPageNumber = xlAutomatic : .BlackAndWhite = False : .Zoom = False : .FitToPagesWide = 1 : .FitToPagesTall = 0
            .OddAndEvenPagesHeaderFooter = False : .DifferentFirstPageHeaderFooter = False : .ScaleWithDocHeaderFooter = True : .AlignMarginsHeaderFooter = True
            .EvenPage.LeftHeader.Text = "" : .EvenPage.CenterHeader.Text = "" : .EvenPage.RightHeader.Text = "" : .EvenPage.LeftFooter.Text = "" : .EvenPage.CenterFooter.Text = ""
            .EvenPage.RightFooter.Text = "" : .FirstPage.LeftHeader.Text = "" : .FirstPage.CenterHeader.Text = "" : .FirstPage.RightHeader.Text = "" : .FirstPage.LeftFooter.Text = ""
            .FirstPage.CenterFooter.Text = "" : .FirstPage.RightFooter.Text = ""
        End With : myExcel.Application.PrintCommunication = True
    End Sub
End Class