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 考勤明細表 ReadOnly ds As New DataSet : ReadOnly ds1 As New DataSet : ReadOnly ds2 As New DataSet : ReadOnly ds3 As New DataSet : ReadOnly ds4 As New DataSet : ReadOnly ds5 As New DataSet ReadOnly ds6 As New DataSet : ReadOnly ds7 As New DataSet : ReadOnly ds8 As New DataSet Dim 編號, 到職日 As String Dim xlApp As Application : Dim xlBook As Workbook : Dim xlSheet As Worksheet Dim T, S, F, M, T1, S1, TT, SS, FF, MM, FFF, MMM, 平日班, 假日班, 假加, 假加一, 假加二, 假加時1, 假加分1, 假加時2, 假加分2, 國假, 國假1, 國假2, 平日加, 平日加1, 平日加2, 正常時, 正常時1, 正常時2, 正常時3, 正常時4, 加班時, 加班時1, 加班時2, 加班時3, 加班時4, 加班時5, 加班時6, 加班時7, 加班時8, 加班時9, 加班時10, 加班時11, 加班時12, 加班時13, 加班時14, 加班時15, 加班時16, 加班時17, 加班時18, 加班時19, 加班時20, 加班時21, 加班時22, 加班時23, 加班時24, 加班時25, 加班時26, 加班時27, 加班時28, 加班時29, 當天總分, 彈性扣時, 請假日 As Double Private Sub 讀取人員資料表() 人員_dgv.DataSource = Nothing : ds.Clear() 人員_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing 人員_dgv.ColumnHeadersHeight = 25 人員_dgv.AllowUserToAddRows = False SQL_考勤明細表_人員() da.Fill(ds) : 人員_dgv.DataSource = ds.Tables(0) : conn.Close() 人員_dgv.Columns(0).FillWeight = 70 : 人員_dgv.Columns(1).Visible = False : 人員_dgv.Columns(2).FillWeight = 100 : 人員_dgv.Columns(3).Visible = False 人員_dgv.Columns(4).Visible = False : 人員_dgv.Columns(5).Visible = False : 人員_dgv.Columns(6).Visible = False : 人員_dgv.Columns(7).Visible = False 人員_dgv.Columns(8).Visible = False : 人員_dgv.Columns(9).Visible = False : 人員_dgv.Columns("到職日").Visible = False For i As Integer = 0 To 人員_dgv.Rows.Count - 1 人員_dgv.Rows(i).Cells("No.").Value = i + 1 Next End Sub Private Sub Set_考勤明細() 月清單_dgv.DataSource = Nothing : ds1.Clear() 月清單_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing 月清單_dgv.ColumnHeadersHeight = 40 月清單_dgv.AllowUserToAddRows = False If CC(16) = True Then : 月清單_dgv.RowTemplate.Height = 25 : Else : 月清單_dgv.RowTemplate.Height = 23 : End If PA = 編號 : PA1 = 年份_cb.Text : PA2 = 月份_cb.Text : SQL_考勤明細表_月份考勤() da.Fill(ds1) : 月清單_dgv.DataSource = ds1.Tables(0) : conn.Close() 月清單_dgv.Columns(0).FillWeight = 100 : 月清單_dgv.Columns(1).FillWeight = 60 : 月清單_dgv.Columns(2).FillWeight = 90 : 月清單_dgv.Columns(3).FillWeight = 100 月清單_dgv.Columns(4).FillWeight = 80 : 月清單_dgv.Columns(5).Visible = False : 月清單_dgv.Columns(6).Visible = False : 月清單_dgv.Columns(7).Visible = False 月清單_dgv.Columns(8).FillWeight = 80 : 月清單_dgv.Columns(9).FillWeight = 80 : 月清單_dgv.Columns(10).FillWeight = 60 : 月清單_dgv.Columns(11).FillWeight = 80 月清單_dgv.Columns(12).FillWeight = 80 : 月清單_dgv.Columns(13).FillWeight = 60 : 月清單_dgv.Columns(14).FillWeight = 80 : 月清單_dgv.Columns(15).FillWeight = 80 月清單_dgv.Columns(16).FillWeight = 60 : 月清單_dgv.Columns(17).FillWeight = 60 : 月清單_dgv.Columns(18).Visible = False : 月清單_dgv.Columns(19).FillWeight = 60 月清單_dgv.Columns(20).FillWeight = 60 : 月清單_dgv.Columns(21).FillWeight = 40 : 月清單_dgv.Columns(22).FillWeight = 40 : 月清單_dgv.Columns(23).FillWeight = 100 月清單_dgv.Columns(24).FillWeight = 80 : 月清單_dgv.Columns(25).FillWeight = 150 : 月清單_dgv.Columns("休息1").Visible = False : 月清單_dgv.Columns("休息2").Visible = False 月清單_dgv.Columns("半天").Visible = False : 月清單_dgv.Columns("班別").Visible = False : 月清單_dgv.Columns("最後下診").Visible = False 月清單_dgv.Columns("申請時數").Visible = False 讀取打卡時間1() : 讀取打卡時間2() Dim S2, F2 As String For I As Integer = 0 To 月清單_dgv.Rows.Count - 1 MyModule1.進度條() If 月清單_dgv.Rows(I).Cells("星期").Value.ToString <> "星期六" And 月清單_dgv.Rows(I).Cells("星期").Value.ToString <> "星期日" And 月清單_dgv.Rows(I).Cells("休假類別").Value.ToString <> "國定假日" And 月清單_dgv.Rows(I).Cells("申請狀態").Value.ToString = "" Then 月清單_dgv.Rows(I).Cells("申請狀態").Value = "." End If 月清單_dgv.Rows(I).Cells("一段工時").Value = "" : 月清單_dgv.Rows(I).Cells("二段工時").Value = "" : 月清單_dgv.Rows(I).Cells("加班工時").Value = "" 月清單_dgv.Rows(I).Cells("休息1").Value = "" : 月清單_dgv.Rows(I).Cells("休息2").Value = "" : 月清單_dgv.Rows(I).Cells("一段加班").Value = "" 月清單_dgv.Rows(I).Cells("二段加班").Value = "" : 月清單_dgv.Rows(I).Cells("加班下班").Value = "" : 月清單_dgv.Rows(I).Cells("加班上班").Value = "" 月清單_dgv.Rows(I).Cells("二段下班").Value = "" : 月清單_dgv.Rows(I).Cells("二段上班").Value = "" : 月清單_dgv.Rows(I).Cells("一段下班").Value = "" If IsDBNull(月清單_dgv.Rows(I).Cells("半天").Value) = True Then : 月清單_dgv.Rows(I).Cells("半天").Value = False : End If If IsDBNull(月清單_dgv.Rows(I).Cells("班別").Value) = True Then : 月清單_dgv.Rows(I).Cells("班別").Value = "" : End If If 月清單_dgv.Rows(I).Cells("班別").Value.ToString = "休" Then : Else : 月清單_dgv.Rows(I).Cells("班別").Value = Strings.Right(月清單_dgv.Rows(I).Cells("班別").Value, 2) : End If '--------------------------讀入資料------------------------------------------------------------------------------------------------------------------------------------ PA3 = Val(Strings.Right(月清單_dgv.Rows(I).Cells("日期").Value, 2)) PA4 = 月清單_dgv.Rows(I).Cells("日期").Value.ToString If 時間讀取1_dgv.Rows.Count > 0 Then For II As Integer = 0 To 時間讀取1_dgv.Rows.Count - 1 If PA4 = 時間讀取1_dgv.Rows(II).Cells("日期").Value.ToString Then 月清單_dgv.Rows(I).Cells("一段上班").Value = 時間讀取1_dgv.Rows(II).Cells("打卡時間").Value.ToString : Exit For Else : 月清單_dgv.Rows(I).Cells("一段上班").Value = "" End If Next For II As Integer = 0 To 班別_dgv.Rows.Count - 1 If 月清單_dgv.Rows(I).Cells("一段上班").Value.ToString <> "" Then If FormatDateTime(班別_dgv.Rows(II).Cells("時段1").Value.ToString, DateFormat.ShortTime) < FormatDateTime(月清單_dgv.Rows(I).Cells("一段上班").Value.ToString, DateFormat.ShortTime) And FormatDateTime(班別_dgv.Rows(II).Cells("時段2").Value.ToString, DateFormat.ShortTime) > FormatDateTime(月清單_dgv.Rows(I).Cells("一段上班").Value.ToString, DateFormat.ShortTime) Then 月清單_dgv.Rows(I).Cells("班別判斷").Value = 班別_dgv.Rows(II).Cells("時段").Value.ToString 月清單_dgv.Rows(I).Cells("休息1").Value = 班別_dgv.Rows(II).Cells("一段休息").Value.ToString 月清單_dgv.Rows(I).Cells("休息2").Value = 班別_dgv.Rows(II).Cells("二段休息").Value.ToString DTPS(0) = 班別_dgv.Rows(II).Cells("時段2").Value.ToString : DTPS(1) = 班別_dgv.Rows(II).Cells("時段4").Value.ToString() DTPS(2) = 班別_dgv.Rows(II).Cells("時段4").Value.ToString : DTPS(3) = 班別_dgv.Rows(II).Cells("時段6").Value.ToString() DTPS(4) = 班別_dgv.Rows(II).Cells("時段6").Value.ToString Exit For End If End If If II = 班別_dgv.Rows.Count - 1 Then For III As Integer = 0 To 班別_dgv.Rows.Count - 1 If Val(Strings.Left(月清單_dgv.Rows(I).Cells("一段上班").Value.ToString, 2)) <= Val(Strings.Left(班別_dgv.Rows(III).Cells("時段").Value.ToString, 2)) Then 月清單_dgv.Rows(I).Cells("班別判斷").Value = 班別_dgv.Rows(III).Cells("時段").Value.ToString 月清單_dgv.Rows(I).Cells("休息1").Value = 班別_dgv.Rows(II).Cells("一段休息").Value.ToString 月清單_dgv.Rows(I).Cells("休息2").Value = 班別_dgv.Rows(II).Cells("二段休息").Value.ToString DTPS(0) = 班別_dgv.Rows(III).Cells("時段2").Value.ToString : DTPS(1) = 班別_dgv.Rows(III).Cells("時段4").Value.ToString() DTPS(2) = 班別_dgv.Rows(III).Cells("時段4").Value.ToString : DTPS(3) = 班別_dgv.Rows(III).Cells("時段6").Value.ToString() DTPS(4) = 班別_dgv.Rows(III).Cells("時段6").Value.ToString Exit For End If 月清單_dgv.Rows(I).Cells("班別判斷").Value = "打卡異常" Next End If Next Else 月清單_dgv.Rows(I).Cells("一段上班").Value = "" : 月清單_dgv.Rows(I).Cells("班別判斷").Value = "" End If 月清單_dgv.Rows(I).Cells("一段下班").Value = "" : 月清單_dgv.Rows(I).Cells("二段上班").Value = "" Dim SD(2), 位置, 數量 As Integer : 數量 = 0 For II As Integer = 0 To 時間讀取2_dgv.Rows.Count - 1 If PA4 = 時間讀取2_dgv.Rows(II).Cells("日期").Value.ToString Then If 數量 = 0 Then ElseIf 數量 = 1 Then 月清單_dgv.Rows(I).Cells("一段下班").Value = 時間讀取2_dgv.Rows(II).Cells("打卡時間").Value ElseIf 數量 = 2 Then 月清單_dgv.Rows(I).Cells("二段上班").Value = 時間讀取2_dgv.Rows(II).Cells("打卡時間").Value ElseIf 數量 = 3 Then 月清單_dgv.Rows(I).Cells("二段下班").Value = 時間讀取2_dgv.Rows(II).Cells("打卡時間").Value ElseIf 數量 = 4 Then 月清單_dgv.Rows(I).Cells("加班上班").Value = 時間讀取2_dgv.Rows(II).Cells("打卡時間").Value ElseIf 數量 = 5 Then 月清單_dgv.Rows(I).Cells("加班下班").Value = 時間讀取2_dgv.Rows(II).Cells("打卡時間").Value End If : 數量 += 1 End If Next '--------------------------打卡位置調整------------------------------------------------------------------------------------------------------------------------------------ If 月清單_dgv.Rows(I).Cells("一段下班").Value.ToString = 月清單_dgv.Rows(I).Cells("一段上班").Value.ToString Then 月清單_dgv.Rows(I).Cells("一段下班").Value = "" End If For d As Integer = 1 To 4 If 月清單_dgv.Rows(I).Cells("加班上班").Value.ToString = "" And 月清單_dgv.Rows(I).Cells("加班下班").Value.ToString <> "" Then 月清單_dgv.Rows(I).Cells("加班上班").Value = 月清單_dgv.Rows(I).Cells("加班下班").Value.ToString : 月清單_dgv.Rows(I).Cells("加班下班").Value = "" End If If 月清單_dgv.Rows(I).Cells("二段下班").Value.ToString = "" And 月清單_dgv.Rows(I).Cells("加班上班").Value.ToString <> "" Then 月清單_dgv.Rows(I).Cells("二段下班").Value = 月清單_dgv.Rows(I).Cells("加班上班").Value.ToString : 月清單_dgv.Rows(I).Cells("加班上班").Value = "" End If If 月清單_dgv.Rows(I).Cells("二段上班").Value.ToString = "" And 月清單_dgv.Rows(I).Cells("二段下班").Value.ToString <> "" Then 月清單_dgv.Rows(I).Cells("二段上班").Value = 月清單_dgv.Rows(I).Cells("二段下班").Value.ToString : 月清單_dgv.Rows(I).Cells("二段下班").Value = "" End If If 月清單_dgv.Rows(I).Cells("一段下班").Value.ToString = "" And 月清單_dgv.Rows(I).Cells("二段上班").Value.ToString <> "" Then 月清單_dgv.Rows(I).Cells("一段下班").Value = 月清單_dgv.Rows(I).Cells("二段上班").Value.ToString : 月清單_dgv.Rows(I).Cells("二段上班").Value = "" End If Next '--------------------------工時計算------------------------------------------------------------------------------------------------------------------------------------ Dim QASW1 As Integer : Dim QASW2 As Integer : Dim QASW3 As Integer Dim DD As String '-如果下班時間超過晚上12點時的分段計算------------------------------------------ If Strings.Left(月清單_dgv.Rows(I).Cells("一段下班").Value.ToString, 2) = "00" Or Strings.Left(月清單_dgv.Rows(I).Cells("一段下班").Value.ToString, 2) = "01" Or Strings.Left(月清單_dgv.Rows(I).Cells("一段下班").Value.ToString, 2) = "02" Then DD = "23:59:59" : Dim TSFM As Integer = DateDiff("s", "00:00:00", 月清單_dgv.Rows(I).Cells("一段下班").Value) : QASW1 = TSFM Else : DD = 月清單_dgv.Rows(I).Cells("一段下班").Value.ToString : QASW1 = 0 : End If '-第一段上班時間正常打卡計算------------------------------------------ If 月清單_dgv.Rows(I).Cells("一段上班").Value.ToString <> "" And DD <> "" Then Dim TSFM As Integer = DateDiff("s", 月清單_dgv.Rows(I).Cells("一段上班").Value.ToString, DD) TSFM += QASW1 If 時薪制_ch.Checked = False Then If 月清單_dgv.Rows(I).Cells("半天").Value = False Then '-第二段上下班都沒打卡，但是累計工時大於4小時(14400)，先扣除一小時休息，第一段工時等於4小時，在把時數放到第二段工時------------------------------------------ If (月清單_dgv.Rows(I).Cells("二段上班").Value.ToString = "" Or 月清單_dgv.Rows(I).Cells("二段下班").Value = "") And TSFM > 14400 Then QASW2 = TSFM - 14400 - (Val(月清單_dgv.Rows(I).Cells("休息1").Value) * 3600) If QASW2 < 0 Then : QASW2 = 0 : End If TSFM = 14400 '-如果加班上下班都沒打卡，但扣除第一段時數的累計工時還是大於4小時(14400)，先扣除一小時休息，第二段工時等於4小時，在把時數放到加班段工時------------------------------------------ If (月清單_dgv.Rows(I).Cells("加班上班").Value.ToString = "" Or 月清單_dgv.Rows(I).Cells("加班下班").Value = "") And QASW2 > 14400 Then QASW3 = QASW2 - 14400 - (Val(月清單_dgv.Rows(I).Cells("休息2").Value) * 3600) If QASW3 < 0 Then : QASW3 = 0 : End If QASW2 = 14400 T = QASW3 \ 86400 : T1 = QASW3 Mod 86400 : S = T1 \ 3600 : S1 = T1 Mod 3600 : F = S1 \ 60 : M = S1 Mod 60 S2 = T * 24 + S : If M > 30 Then : F += 1 : End If : F2 = F 月清單_dgv.Rows(I).Cells("加班工時").Value = S2 & " : " & F2 End If T = QASW2 \ 86400 : T1 = QASW2 Mod 86400 : S = T1 \ 3600 : S1 = T1 Mod 3600 : F = S1 \ 60 : M = S1 Mod 60 S2 = T * 24 + S : If M > 30 Then : F += 1 : End If : F2 = F 月清單_dgv.Rows(I).Cells("二段工時").Value = S2 & " : " & F2 End If Else If (月清單_dgv.Rows(I).Cells("二段上班").Value.ToString = "" Or 月清單_dgv.Rows(I).Cells("二段下班").Value = "") And TSFM > 14400 Then QASW2 = TSFM - 14400 If QASW2 < 0 Then : QASW2 = 0 : End If TSFM = 14400 '-如果加班上下班都沒打卡，但扣除第一段時數的累計工時還是大於4小時(14400)，先扣除一小時休息，第二段工時等於4小時，在把時數放到加班段工時------------------------------------------ If (月清單_dgv.Rows(I).Cells("加班上班").Value.ToString = "" Or 月清單_dgv.Rows(I).Cells("加班下班").Value = "") And QASW2 > 14400 Then QASW3 = QASW2 - 14400 If QASW3 < 0 Then : QASW3 = 0 : End If QASW2 = 14400 T = QASW3 \ 86400 : T1 = QASW3 Mod 86400 : S = T1 \ 3600 : S1 = T1 Mod 3600 : F = S1 \ 60 : M = S1 Mod 60 S2 = T * 24 + S : If M > 30 Then : F += 1 : End If : F2 = F 月清單_dgv.Rows(I).Cells("加班工時").Value = S2 & " : " & F2 End If T = QASW2 \ 86400 : T1 = QASW2 Mod 86400 : S = T1 \ 3600 : S1 = T1 Mod 3600 : F = S1 \ 60 : M = S1 Mod 60 S2 = T * 24 + S : If M > 30 Then : F += 1 : End If : F2 = F 月清單_dgv.Rows(I).Cells("二段工時").Value = S2 & " : " & F2 End If End If Else If (月清單_dgv.Rows(I).Cells("二段上班").Value.ToString = "" Or 月清單_dgv.Rows(I).Cells("二段下班").Value = "") And TSFM > 14400 Then QASW2 = TSFM - 14400 If QASW2 < 0 Then : QASW2 = 0 : End If TSFM = 14400 If (月清單_dgv.Rows(I).Cells("加班上班").Value.ToString = "" Or 月清單_dgv.Rows(I).Cells("加班下班").Value = "") And QASW2 > 14400 Then QASW3 = QASW2 - 14400 If QASW3 < 0 Then : QASW3 = 0 : End If QASW2 = 14400 T = QASW3 \ 86400 : T1 = QASW3 Mod 86400 : S = T1 \ 3600 : S1 = T1 Mod 3600 : F = S1 \ 60 : M = S1 Mod 60 S2 = T * 24 + S : If M > 30 Then : F += 1 : End If : F2 = F 月清單_dgv.Rows(I).Cells("加班工時").Value = S2 & " : " & F2 End If T = QASW2 \ 86400 : T1 = QASW2 Mod 86400 : S = T1 \ 3600 : S1 = T1 Mod 3600 : F = S1 \ 60 : M = S1 Mod 60 S2 = T * 24 + S : If M > 30 Then : F += 1 : End If : F2 = F 月清單_dgv.Rows(I).Cells("二段工時").Value = S2 & " : " & F2 End If End If T = TSFM \ 86400 : T1 = TSFM Mod 86400 : S = T1 \ 3600 : S1 = T1 Mod 3600 : F = S1 \ 60 : M = S1 Mod 60 S2 = T * 24 + S : If M > 30 Then : F += 1 : End If : F2 = F 月清單_dgv.Rows(I).Cells("一段工時").Value = S2 & " : " & F2 End If '-如果下班時間超過晚上12點時的分段計算------------------------------------------ If Strings.Left(月清單_dgv.Rows(I).Cells("二段下班").Value.ToString, 2) = "00" Or Strings.Left(月清單_dgv.Rows(I).Cells("二段下班").Value.ToString, 2) = "01" Or Strings.Left(月清單_dgv.Rows(I).Cells("二段下班").Value.ToString, 2) = "02" Then DD = "23:59:59" : Dim TSFM As Integer = DateDiff("s", "00:00:00", 月清單_dgv.Rows(I).Cells("二段下班").Value) : QASW1 = TSFM Else : DD = 月清單_dgv.Rows(I).Cells("二段下班").Value.ToString : QASW1 = 0 : End If '-第二段上班時間正常打卡計算------------------------------------------ If 月清單_dgv.Rows(I).Cells("二段上班").Value.ToString <> "" And DD <> "" Then Dim TSFM As Integer = DateDiff("s", 月清單_dgv.Rows(I).Cells("二段上班").Value.ToString, DD) TSFM += QASW1 T = TSFM \ 86400 : T1 = TSFM Mod 86400 : S = T1 \ 3600 : S1 = T1 Mod 3600 : F = S1 \ 60 : M = S1 Mod 60 S2 = T * 24 + S : If M > 30 Then : F += 1 : End If : F2 = F 月清單_dgv.Rows(I).Cells("二段工時").Value = S2 & " : " & F2 End If '-如果下班時間超過晚上12點時的分段計算------------------------------------------ If Strings.Left(月清單_dgv.Rows(I).Cells("加班下班").Value.ToString, 2) = "00" Then DD = "23:59:59" : Dim TSFM As Integer = DateDiff("s", "00:00:00", 月清單_dgv.Rows(I).Cells("二段下班").Value) : QASW1 = TSFM Else : DD = 月清單_dgv.Rows(I).Cells("加班下班").Value.ToString : QASW1 = 0 : End If '-加班上班時間正常打卡計算------------------------------------------ If 月清單_dgv.Rows(I).Cells("加班上班").Value.ToString <> "" And DD <> "" Then Dim TSFM As Integer = DateDiff("s", 月清單_dgv.Rows(I).Cells("加班上班").Value.ToString, DD) TSFM += QASW1 T = TSFM \ 86400 : T1 = TSFM Mod 86400 : S = T1 \ 3600 : S1 = T1 Mod 3600 : F = S1 \ 60 : M = S1 Mod 60 S2 = T * 24 + S : If M > 30 Then : F += 1 : End If : F2 = F 月清單_dgv.Rows(I).Cells("加班工時").Value = S2 & " : " & F2 End If 當天總分 = (Val(Strings.Left(月清單_dgv.Rows(I).Cells("一段工時").Value.ToString, 2)) + Val(Strings.Left(月清單_dgv.Rows(I).Cells("二段工時").Value.ToString, 2)) + Val(Strings.Left(月清單_dgv.Rows(I).Cells("加班工時").Value.ToString, 2))) * 60 + (Val(Strings.Right(月清單_dgv.Rows(I).Cells("一段工時").Value.ToString, 2)) + Val(Strings.Right(月清單_dgv.Rows(I).Cells("二段工時").Value.ToString, 2)) + Val(Strings.Right(月清單_dgv.Rows(I).Cells("加班工時").Value.ToString, 2))) If 當天總分 <= 240 Then 月清單_dgv.Rows(I).Cells("一段工時").Value = 當天總分 \ 60 & " : " & 當天總分 Mod 60 月清單_dgv.Rows(I).Cells("二段工時").Value = "" : 月清單_dgv.Rows(I).Cells("加班工時").Value = "" ElseIf 當天總分 > 240 Then 月清單_dgv.Rows(I).Cells("一段工時").Value = "4 : 0" 月清單_dgv.Rows(I).Cells("二段工時").Value = (當天總分 - 240) \ 60 & " : " & (當天總分 - 240) Mod 60 If (當天總分 - 240) <= 240 Then : Else 月清單_dgv.Rows(I).Cells("二段工時").Value = "4 : 0" 月清單_dgv.Rows(I).Cells("一段加班").Value = (當天總分 - 240 - 240) \ 60 & " : " & (當天總分 - 240 - 240) Mod 60 If (當天總分 - 240 - 240) <= 120 Then : Else 月清單_dgv.Rows(I).Cells("一段加班").Value = "2 : 0" 月清單_dgv.Rows(I).Cells("二段加班").Value = (當天總分 - 240 - 240 - 120) \ 60 & " : " & (當天總分 - 240 - 240 - 120) Mod 60 If (當天總分 - 240 - 240 - 120) <= 120 Then : Else 月清單_dgv.Rows(I).Cells("二段加班").Value = "2 : 0" End If End If End If End If If 月清單_dgv.Rows(I).Cells("一段工時").Value.ToString = "0 : 0" Then 月清單_dgv.Rows(I).Cells("一段工時").Value = "" End If '--------------------------假日標色------------------------------------------------------------------------------------------------------------------------------------ If 月清單_dgv.Rows(I).Cells("星期").Value.ToString = "星期六" Then : 月清單_dgv.Rows(I).DefaultCellStyle.BackColor = Color.LightPink ElseIf 月清單_dgv.Rows(I).Cells("星期").Value.ToString = "星期日" Then : 月清單_dgv.Rows(I).DefaultCellStyle.BackColor = Color.LightPink ElseIf 月清單_dgv.Rows(I).Cells("休假類別").Value.ToString = "國定假日" Then : 月清單_dgv.Rows(I).DefaultCellStyle.BackColor = Color.LightPink ElseIf 月清單_dgv.Rows(I).Cells("休假類別").Value.ToString <> "" Then : 月清單_dgv.Rows(I).DefaultCellStyle.BackColor = Color.LightYellow End If '--------------------------加班未申請去除------------------------------------------------------------------------------------------------------------------------------------ If 月清單_dgv.Rows(I).Cells("申請狀態").Value.ToString <> "已核准" And 月清單_dgv.Rows(I).Cells("星期").Value.ToString <> "星期六" And 月清單_dgv.Rows(I).Cells("星期").Value.ToString <> "星期日" And 月清單_dgv.Rows(I).Cells("休假類別").Value.ToString <> "國定假日" And 月清單_dgv.Rows(I).Cells("申請狀態").Value.ToString <> "." Then 月清單_dgv.Rows(I).Cells("一段加班").Value = "" : 月清單_dgv.Rows(I).Cells("二段加班").Value = "" End If Next 平日班 = 0 : 正常時1 = 0 : 正常時2 = 0 : 正常時3 = 0 : 正常時4 = 0 : 加班時9 = 0 : 加班時10 = 0 : 加班時11 = 0 : 加班時12 = 0 : 加班時8 = 0 : 加班時21 = 0 加班時17 = 0 : 加班時18 = 0 : 加班時19 = 0 : 加班時20 = 0 : 加班時23 = 0 : 加班時24 = 0 : 加班時25 = 0 : 加班時26 = 0 : 假日班 = 0 : 加班時27 = 0 : 請假日 = 0 For I As Integer = 0 To 月清單_dgv.Rows.Count - 1 If 月清單_dgv.Rows(I).Cells("休假類別").Value.ToString <> "國定假日" And 月清單_dgv.Rows(I).Cells("休假類別").Value.ToString <> "" And 月清單_dgv.Rows(I).Cells("休假類別").Value.ToString <> "特休" And 月清單_dgv.Rows(I).Cells("休假類別").Value.ToString <> "未到" And 月清單_dgv.Rows(I).Cells("休假類別").Value.ToString <> "離職" Then 請假日 += 1 End If '---------------------------平日 If 月清單_dgv.Rows(I).Cells("星期").Value.ToString <> "星期六" And 月清單_dgv.Rows(I).Cells("星期").Value.ToString <> "星期日" And 月清單_dgv.Rows(I).Cells("休假類別").Value.ToString <> "國定假日" And 月清單_dgv.Rows(I).Cells("休假類別").Value.ToString = "" Then 加班時27 += 1 If 月清單_dgv.Rows(I).Cells("一段工時").Value.ToString <> "" Then 平日班 += 1 正常時1 += Val(Strings.Left(月清單_dgv.Rows(I).Cells("一段工時").Value.ToString, 2)) : 正常時2 += Val(Strings.Right(月清單_dgv.Rows(I).Cells("一段工時").Value.ToString, 2)) End If If 月清單_dgv.Rows(I).Cells("二段工時").Value.ToString <> "" Then 正常時3 += Val(Strings.Left(月清單_dgv.Rows(I).Cells("二段工時").Value.ToString, 2)) : 正常時4 += Val(Strings.Right(月清單_dgv.Rows(I).Cells("二段工時").Value.ToString, 2)) End If If 月清單_dgv.Rows(I).Cells("一段加班").Value.ToString <> "" Then 加班時9 += Val(Strings.Left(月清單_dgv.Rows(I).Cells("一段加班").Value.ToString, 2)) : 加班時10 += Val(Strings.Right(月清單_dgv.Rows(I).Cells("一段加班").Value.ToString, 2)) End If If 月清單_dgv.Rows(I).Cells("二段加班").Value.ToString <> "" Then 加班時11 += Val(Strings.Left(月清單_dgv.Rows(I).Cells("二段加班").Value.ToString, 2)) : 加班時12 += Val(Strings.Right(月清單_dgv.Rows(I).Cells("二段加班").Value.ToString, 2)) End If End If '---------------------------星期六 If 月清單_dgv.Rows(I).Cells("星期").Value.ToString = "星期六" Then If 月清單_dgv.Rows(I).Cells("一段工時").Value.ToString <> "" And 月清單_dgv.Rows(I).Cells("一段工時").Value.ToString <> "0 : 0" Then 假日班 += 1 加班時3 = Val(Strings.Left(月清單_dgv.Rows(I).Cells("一段工時").Value.ToString, 2)) : 加班時4 = Val(Strings.Right(月清單_dgv.Rows(I).Cells("一段工時").Value.ToString, 2)) 加班時7 = 加班時3 + (加班時4 / 60) If 加班時7 > 2 Then 加班時8 += 2 : 加班時21 += (加班時7 - 2) Else 加班時8 += 加班時7 End If End If If 月清單_dgv.Rows(I).Cells("二段工時").Value.ToString <> "" And 月清單_dgv.Rows(I).Cells("二段工時").Value.ToString <> "0 : 0" Then 加班時5 = Val(Strings.Left(月清單_dgv.Rows(I).Cells("二段工時").Value.ToString, 2)) : 加班時6 = Val(Strings.Right(月清單_dgv.Rows(I).Cells("二段工時").Value.ToString, 2)) 加班時21 += 加班時5 + (加班時6 / 60) End If If 月清單_dgv.Rows(I).Cells("一段加班").Value.ToString <> "" And 月清單_dgv.Rows(I).Cells("一段加班").Value.ToString <> "0 : 0" Then 加班時13 = Val(Strings.Left(月清單_dgv.Rows(I).Cells("一段加班").Value.ToString, 2)) : 加班時14 = Val(Strings.Right(月清單_dgv.Rows(I).Cells("一段加班").Value.ToString, 2)) 加班時21 += (加班時13 + (加班時14 / 60)) End If If 月清單_dgv.Rows(I).Cells("二段加班").Value.ToString <> "" And 月清單_dgv.Rows(I).Cells("二段加班").Value.ToString <> "0 : 0" Then 加班時15 = Val(Strings.Left(月清單_dgv.Rows(I).Cells("二段加班").Value.ToString, 2)) : 加班時16 = Val(Strings.Right(月清單_dgv.Rows(I).Cells("二段加班").Value.ToString, 2)) 加班時21 += (加班時15 + (加班時16 / 60)) End If End If '--------------------------星期日或國定假日 If 月清單_dgv.Rows(I).Cells("星期").Value.ToString = "星期日" Or 月清單_dgv.Rows(I).Cells("休假類別").Value.ToString = "國定假日" Then If 月清單_dgv.Rows(I).Cells("一段工時").Value.ToString <> "" And 月清單_dgv.Rows(I).Cells("一段工時").Value.ToString <> "0 : 0" Then 假日班 += 1 加班時17 += Val(Strings.Left(月清單_dgv.Rows(I).Cells("一段工時").Value.ToString, 2)) : 加班時18 += Val(Strings.Right(月清單_dgv.Rows(I).Cells("一段工時").Value.ToString, 2)) End If If 月清單_dgv.Rows(I).Cells("二段工時").Value.ToString <> "" And 月清單_dgv.Rows(I).Cells("二段工時").Value.ToString <> "0 : 0" Then 加班時19 += Val(Strings.Left(月清單_dgv.Rows(I).Cells("二段工時").Value.ToString, 2)) : 加班時20 += Val(Strings.Right(月清單_dgv.Rows(I).Cells("二段工時").Value.ToString, 2)) End If If 月清單_dgv.Rows(I).Cells("一段加班").Value.ToString <> "" And 月清單_dgv.Rows(I).Cells("一段加班").Value.ToString <> "0 : 0" Then 月清單_dgv.Rows(I).Cells("一段加班").Value = "0 : 0" 加班時23 += Val(Strings.Left(月清單_dgv.Rows(I).Cells("一段加班").Value.ToString, 2)) : 加班時24 += Val(Strings.Right(月清單_dgv.Rows(I).Cells("一段加班").Value.ToString, 2)) End If If 月清單_dgv.Rows(I).Cells("二段加班").Value.ToString <> "" And 月清單_dgv.Rows(I).Cells("二段加班").Value.ToString <> "0 : 0" Then 月清單_dgv.Rows(I).Cells("二段加班").Value = "0 : 0" 加班時25 += Val(Strings.Left(月清單_dgv.Rows(I).Cells("二段加班").Value.ToString, 2)) : 加班時26 += Val(Strings.Right(月清單_dgv.Rows(I).Cells("二段加班").Value.ToString, 2)) End If End If Next FF = (正常時2 + 正常時4) / 60 正常時 = 正常時1 + 正常時3 + FF 當月平日天_tb.Text = 平日班 + 假日班 : 當月假日天_tb.Text = 請假日 常日總_tb.Text = Strings.Format(正常時, "#,##0.00") 當月平日一段_tb.Text = Strings.Format((加班時9 + (加班時10 / 60)), "#,##0.00") 當月平日二段_tb.Text = Strings.Format((加班時11 + (加班時12 / 60)), "#,##0.00") 一段假日加_tb.Text = Strings.Format(加班時8, "#,##0.00") 二段假日加_tb.Text = Strings.Format(加班時21, "#,##0.00") 國假日加_tb.Text = Strings.Format(加班時17 + 加班時19 + 加班時23 + 加班時25 + (加班時18 + 加班時20 + 加班時24 + 加班時26) / 60, "#,##0.00") Set_時薪制() : Set_彈性工時() 加班總_tb.Text = Strings.Format((加班時9 + (加班時10 / 60)) + (加班時11 + (加班時12 / 60)) + 加班時8 + 加班時21 + (加班時17 + 加班時19 + 加班時23 + 加班時25 + (加班時18 + 加班時20 + 加班時24 + 加班時26) / 60), "#,##0.00") 特修補錢_tb.Text = "0" : 未到離職_tb.Text = "0" : 事假_tb.Text = "0" : 婚假_tb.Text = "0" : 喪假_tb.Text = "0" : 特休_tb.Text = "0" : 特休未休_nud.Value = 0 薪資計算() End Sub Private Sub 薪資計算() If CC(16) = True Then If 時薪制_ch.Checked = True Then 月薪_tb.Text = Strings.Format((CDbl(時薪_tb.Text) * CDbl(常日總_tb.Text)), "#,##0.0") Else 時薪_tb.Text = Strings.Format((CDbl(月薪_tb.Text) / 30 / 8), "#,##0.0") End If Dim ds As New DataSet 假期統計_dgv.DataSource = Nothing : ds.Clear() 假期統計_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing 假期統計_dgv.ColumnHeadersHeight = 25 假期統計_dgv.AllowUserToAddRows = False PA = 編號 : PA1 = 年份_cb.Text & "/" & 月份_cb.Text : SQL_休假統計1() da.Fill(ds) : 假期統計_dgv.DataSource = ds.Tables(0) : conn.Close() Dim 到離, 事假, 病假, 特休, 婚假, 喪假 As Double : 到離 = 0 : 事假 = 0 : 病假 = 0 : 特休 = 0 : 婚假 = 0 : 喪假 = 0 For I As Integer = 0 To 假期統計_dgv.Rows.Count - 1 If 假期統計_dgv.Rows(I).Cells(0).Value.ToString = "未到" Or 假期統計_dgv.Rows(I).Cells(0).Value.ToString = "離職" Then 到離 += 假期統計_dgv.Rows(I).Cells("天數").Value.ToString End If If 假期統計_dgv.Rows(I).Cells(0).Value.ToString = "事假" Then : 事假 += 假期統計_dgv.Rows(I).Cells("總時數").Value.ToString : End If If 假期統計_dgv.Rows(I).Cells(0).Value.ToString = "病假" Then : 病假 += 假期統計_dgv.Rows(I).Cells("總時數").Value.ToString : End If If 假期統計_dgv.Rows(I).Cells(0).Value.ToString = "特休" Then : 特休 += 假期統計_dgv.Rows(I).Cells("總時數").Value.ToString : End If If 假期統計_dgv.Rows(I).Cells(0).Value.ToString = "婚假" Then : 婚假 += 假期統計_dgv.Rows(I).Cells("總時數").Value.ToString : End If If 假期統計_dgv.Rows(I).Cells(0).Value.ToString = "喪假" Then : 喪假 += 假期統計_dgv.Rows(I).Cells("總時數").Value.ToString : End If Next 病假_tb.Text = Strings.Format((病假 * CDbl(時薪_tb.Text) / 2), "#,##0.0") If 時薪制_ch.Checked = False Then 未到離職_tb.Text = Strings.Format((到離 * 8 * CDbl(時薪_tb.Text)), "#,##0.0") 事假_tb.Text = Strings.Format((事假 * CDbl(時薪_tb.Text)), "#,##0.0") Else 婚假_tb.Text = Strings.Format((婚假 * CDbl(時薪_tb.Text)), "#,##0.0") 喪假_tb.Text = Strings.Format((喪假 * CDbl(時薪_tb.Text)), "#,##0.0") 特休_tb.Text = Strings.Format((特休 * CDbl(時薪_tb.Text)), "#,##0.0") End If '--------------------------第一種應付小計計算--------------------------------------------------- 第1段加班費1_tb.Text = Strings.Format(((CDbl(當月平日一段_tb.Text) + CDbl(一段假日加_tb.Text)) * CDbl(時薪_tb.Text) * 1.34), "#,##0.0") 第2段加班費1_tb.Text = Strings.Format(((CDbl(當月平日二段_tb.Text) + CDbl(二段假日加_tb.Text)) * CDbl(時薪_tb.Text) * 1.67), "#,##0.0") 第3段加班費1_tb.Text = Strings.Format((CDbl(國假日加_tb.Text) * CDbl(時薪_tb.Text) * 2), "#,##0.0") 加班費1_tb.Text = Strings.Format((CDbl(第1段加班費1_tb.Text) + CDbl(第2段加班費1_tb.Text) + CDbl(第3段加班費1_tb.Text)), "#,##0.0") If 時薪制_ch.Checked = False Then 應付小計1_tb.Text = Strings.Format((CDbl(月薪_tb.Text) + CDbl(加班費1_tb.Text) + CDbl(主管加級_tb.Text) + CDbl(職務加級_tb.Text) + CDbl(特修補錢_tb.Text) + 其他加_nud.Value - CDbl(未到離職_tb.Text)), "#,##0.0") Else 應付小計1_tb.Text = Strings.Format((CDbl(月薪_tb.Text) + CDbl(加班費1_tb.Text) + CDbl(主管加級_tb.Text) + CDbl(職務加級_tb.Text) + CDbl(特修補錢_tb.Text) + (CDbl(病假_tb.Text) * 2) + CDbl(婚假_tb.Text) + CDbl(喪假_tb.Text) + CDbl(特休_tb.Text) + 其他加_nud.Value - CDbl(未到離職_tb.Text)), "#,##0.0") End If '--------------------------第二種應付小計計算----------------------------------------------------- 第1段加班費2_tb.Text = Strings.Format(((CDbl(當月平日一段1_tb.Text) + CDbl(一段假日加1_tb.Text)) * CDbl(時薪_tb.Text) * 1.34), "#,##0.0") 第2段加班費2_tb.Text = Strings.Format(((CDbl(當月平日二段1_tb.Text) + CDbl(二段假日加1_tb.Text)) * CDbl(時薪_tb.Text) * 1.67), "#,##0.0") 第3段加班費2_tb.Text = Strings.Format((CDbl(國假日加1_tb.Text) * CDbl(時薪_tb.Text) * 2), "#,##0.0") 加班費2_tb.Text = Strings.Format((CDbl(第1段加班費2_tb.Text) + CDbl(第2段加班費2_tb.Text) + CDbl(第3段加班費2_tb.Text)), "#,##0.0") If 時薪制_ch.Checked = False Then 應付小計2_tb.Text = Strings.Format((CDbl(月薪_tb.Text) + CDbl(加班費2_tb.Text) + CDbl(主管加級_tb.Text) + CDbl(職務加級_tb.Text) + CDbl(特修補錢_tb.Text) + 其他加_nud.Value - CDbl(未到離職_tb.Text)), "#,##0.0") Else 應付小計2_tb.Text = Strings.Format((CDbl(月薪_tb.Text) + CDbl(加班費2_tb.Text) + CDbl(主管加級_tb.Text) + CDbl(職務加級_tb.Text) + CDbl(特修補錢_tb.Text) + (CDbl(病假_tb.Text) * 2) + CDbl(婚假_tb.Text) + CDbl(喪假_tb.Text) + CDbl(特休_tb.Text) + 其他加_nud.Value - CDbl(未到離職_tb.Text)), "#,##0.0") End If '--------------------------第一種實付小計計算--------------------------------------------------- If 時薪制_ch.Checked = False Then 實付小計1_tb.Text = Strings.Format((CDbl(應付小計1_tb.Text) - CDbl(健保費_tb.Text) - CDbl(勞保費_tb.Text) - CDbl(病假_tb.Text) - CDbl(事假_tb.Text) - 其他減_nud.Value), "#,##0.0") Else 實付小計1_tb.Text = Strings.Format((CDbl(應付小計1_tb.Text) - CDbl(健保費_tb.Text) - CDbl(勞保費_tb.Text) - CDbl(病假_tb.Text) - 其他減_nud.Value), "#,##0.0") End If '--------------------------第二種實付小計計算--------------------------------------------------- If 時薪制_ch.Checked = False Then 實付小計2_tb.Text = Strings.Format((CDbl(應付小計2_tb.Text) - CDbl(健保費_tb.Text) - CDbl(勞保費_tb.Text) - CDbl(病假_tb.Text) - CDbl(事假_tb.Text) - 其他減_nud.Value), "#,##0.0") Else 實付小計2_tb.Text = Strings.Format((CDbl(應付小計2_tb.Text) - CDbl(健保費_tb.Text) - CDbl(勞保費_tb.Text) - CDbl(病假_tb.Text) - 其他減_nud.Value), "#,##0.0") End If End If End Sub Private Sub 特休未休_NUD_ValueChanged(sender As Object, e As EventArgs) Handles 特休未休_nud.ValueChanged 特修補錢_tb.Text = Strings.Format((特休未休_nud.Value * 8 * CDbl(時薪_tb.Text)), "#,##0.0") : 薪資計算() End Sub Private Sub 其他_nud_ValueChanged(sender As Object, e As EventArgs) Handles 其他減_nud.ValueChanged 薪資計算() End Sub Private Sub 時薪彈性_bt_Click(sender As Object, e As EventArgs) Handles 時薪彈性_bt.Click 當月應上天數_tb.Text = 加班時27 : 上班總工時_tb.Text = Strings.Format(加班時27 * 8, "#,##0.00") : 應上未上時數_tb.Text = Strings.Format((加班時27 * 8) - 正常時, "#,##0.00") Set_彈性工時() 特修補錢_tb.Text = "0" : 未到離職_tb.Text = "0" : 事假_tb.Text = "0" : 婚假_tb.Text = "0" : 喪假_tb.Text = "0" : 特休_tb.Text = "0" : 特休未休_nud.Value = 0 薪資計算() End Sub Private Sub Set_時薪制() If 時薪制_ch.Checked = False Then 當月應上天數_tb.Text = 加班時27 上班總工時_tb.Text = Strings.Format(加班時27 * 8, "#,##0.00") 應上未上時數_tb.Text = Strings.Format((加班時27 * 8) - 正常時, "#,##0.00") Else 當月應上天數_tb.Text = "0" : 上班總工時_tb.Text = "0.00" : 應上未上時數_tb.Text = "0.00" End If End Sub Private Sub Set_彈性工時() If 彈性工時_ch.Checked = False Then 當月平日一段1_tb.Text = 當月平日一段_tb.Text : 當月平日二段1_tb.Text = 當月平日二段_tb.Text : 一段假日加1_tb.Text = 一段假日加_tb.Text 二段假日加1_tb.Text = 二段假日加_tb.Text : 國假日加1_tb.Text = 國假日加_tb.Text Else 彈性扣時 = 應上未上時數_tb.Text If Val(國假日加_tb.Text) >= 彈性扣時 Then 國假日加1_tb.Text = Strings.Format(Val(國假日加_tb.Text) - 彈性扣時, "#,##0.00") 當月平日一段1_tb.Text = 當月平日一段_tb.Text : 當月平日二段1_tb.Text = 當月平日二段_tb.Text : 一段假日加1_tb.Text = 一段假日加_tb.Text 二段假日加1_tb.Text = 二段假日加_tb.Text ElseIf Val(國假日加_tb.Text) < 彈性扣時 Then 彈性扣時 -= Val(國假日加_tb.Text) : 國假日加1_tb.Text = "0.00" If Val(二段假日加_tb.Text) >= 彈性扣時 Then 二段假日加1_tb.Text = Strings.Format(Val(二段假日加_tb.Text) - 彈性扣時, "#,##0.00") 當月平日一段1_tb.Text = 當月平日一段_tb.Text : 當月平日二段1_tb.Text = 當月平日二段_tb.Text : 一段假日加1_tb.Text = 一段假日加_tb.Text ElseIf Val(二段假日加_tb.Text) < 彈性扣時 Then 彈性扣時 -= Val(二段假日加_tb.Text) : 二段假日加1_tb.Text = "0.00" If Val(當月平日二段_tb.Text) >= 彈性扣時 Then 當月平日二段1_tb.Text = Strings.Format(Val(當月平日二段_tb.Text) - 彈性扣時, "#,##0.00") 當月平日一段1_tb.Text = 當月平日一段_tb.Text : 一段假日加1_tb.Text = 一段假日加_tb.Text ElseIf Val(當月平日二段_tb.Text) < 彈性扣時 Then 彈性扣時 -= Val(當月平日二段_tb.Text) : 當月平日二段1_tb.Text = "0.00" If Val(一段假日加_tb.Text) >= 彈性扣時 Then 一段假日加1_tb.Text = Strings.Format(Val(一段假日加_tb.Text) - 彈性扣時, "#,##0.00") 當月平日一段1_tb.Text = 當月平日一段_tb.Text ElseIf Val(一段假日加_tb.Text) < 彈性扣時 Then 彈性扣時 -= Val(一段假日加_tb.Text) : 一段假日加1_tb.Text = "0.00" If Val(當月平日一段_tb.Text) >= 彈性扣時 Then 當月平日一段1_tb.Text = Strings.Format(Val(當月平日一段_tb.Text) - 彈性扣時, "#,##0.00") ElseIf Val(當月平日一段_tb.Text) < 彈性扣時 Then 當月平日一段1_tb.Text = "0.00" End If End If End If End If End If End If End Sub Private Sub Set_考勤流水() 考勤流水_dgv.DataSource = Nothing : ds5.Clear() 考勤流水_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing 考勤流水_dgv.ColumnHeadersHeight = 25 考勤流水_dgv.AllowUserToAddRows = False SQL_考勤明細表_考勤流水() da.Fill(ds5) : 考勤流水_dgv.DataSource = ds5.Tables(0) : conn.Close() 考勤流水_dgv.Columns(0).FillWeight = 30 : 考勤流水_dgv.Columns(1).FillWeight = 30 : 考勤流水_dgv.Columns(2).FillWeight = 100 For i As Integer = 0 To 考勤流水_dgv.Rows.Count - 1 考勤流水_dgv.Rows(i).Cells("No.").Value = i + 1 Next End Sub Private Sub 讀取班別() 班別_dgv.DataSource = Nothing : ds7.Clear() 班別_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing 班別_dgv.ColumnHeadersHeight = 25 班別_dgv.AllowUserToAddRows = False SQL_考勤明細表_班別() da.Fill(ds7) : 班別_dgv.DataSource = ds7.Tables(0) : conn.Close() 班別_dgv.Columns(0).FillWeight = 80 : 班別_dgv.Columns(1).FillWeight = 130 : 班別_dgv.Columns(2).FillWeight = 130 : 班別_dgv.Columns(3).FillWeight = 100 班別_dgv.Columns(4).FillWeight = 130 : 班別_dgv.Columns(5).FillWeight = 100 : 班別_dgv.Columns(6).FillWeight = 100 : 班別_dgv.Columns(7).FillWeight = 100 班別_dgv.Columns(8).FillWeight = 100 : 班別_dgv.Columns(9).Visible = False : 班別_dgv.Columns(10).FillWeight = 100 : 班別_dgv.Columns(11).Visible = False 班別_dgv.Columns(12).FillWeight = 100 : 班別_dgv.Columns(13).Visible = False : 班別_dgv.Columns(14).Visible = False End Sub Private Sub 讀取打卡時間1() 時間讀取1_dgv.DataSource = Nothing : ds2.Clear() 時間讀取1_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing 時間讀取1_dgv.ColumnHeadersHeight = 25 時間讀取1_dgv.AllowUserToAddRows = False SQL_考勤明細表_打卡讀取_上午上班() da.Fill(ds2) : 時間讀取1_dgv.DataSource = ds2.Tables(0) : conn.Close() End Sub Private Sub 讀取打卡時間2() 時間讀取2_dgv.DataSource = Nothing : ds3.Clear() 時間讀取2_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing 時間讀取2_dgv.ColumnHeadersHeight = 25 時間讀取2_dgv.AllowUserToAddRows = False SQL_考勤明細表_打卡讀取_中午下上班() da.Fill(ds3) : 時間讀取2_dgv.DataSource = ds3.Tables(0) : conn.Close() End Sub Private Sub 讀取打卡時間3() 時間讀取3_dgv.DataSource = Nothing : ds4.Clear() 時間讀取3_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing 時間讀取3_dgv.ColumnHeadersHeight = 25 時間讀取3_dgv.AllowUserToAddRows = False SQL_考勤明細表_打卡讀取_晚上下上班() da.Fill(ds4) : 時間讀取3_dgv.DataSource = ds4.Tables(0) : conn.Close() End Sub Private Sub 讀取打卡時間4() 時間讀取4_dgv.DataSource = Nothing : ds6.Clear() 時間讀取4_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing 時間讀取4_dgv.ColumnHeadersHeight = 25 時間讀取4_dgv.AllowUserToAddRows = False SQL_考勤明細表_打卡讀取_加班下班() da.Fill(ds6) : 時間讀取4_dgv.DataSource = ds6.Tables(0) : conn.Close() End Sub Private Sub 讀取日期() 日期_dgv.DataSource = Nothing : ds8.Clear() 日期_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing 日期_dgv.ColumnHeadersHeight = 25 日期_dgv.AllowUserToAddRows = False SQL_讀取日期() da.Fill(ds8) : 日期_dgv.DataSource = ds8.Tables(0) : conn.Close() End Sub Private Sub 年份_下拉表單資料載入() 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 年份_下拉表單資料載入() : 讀取班別() 年份_cb.Text = Year(Today) : 月份_cb.SelectedIndex = Month(Today) - 1 PA1 = 年份_cb.Text : PA2 = 月份_cb.Text 讀取日期() If CC(16) = False Then 考勤資料編輯_bt.Enabled = False : 時薪制_ch.Enabled = False : 彈性工時_ch.Enabled = False If gUserName = "測試號" Then : 人員_dgv.Enabled = True : Else : 人員_dgv.Enabled = False : End If Panel5.Visible = False 姓名_tb.Text = gUserName : 資料數 = 31 For i As Integer = 0 To 人員_dgv.Rows.Count - 1 : If 姓名_tb.Text = 人員_dgv("姓名", i).Value.ToString Then : 時薪制_ch.Checked = 人員_dgv("CC18", i).Value : End If : Next SQL_考勤明細表_讀取編號() : If dr.Read() Then : 編號 = dr("DIN") : End If Set_考勤明細() : PA = 編號 : PA1 = 年份_cb.Text : PA2 = 月份_cb.Text : Set_考勤流水() : GRAMS_SYS.ToolStripProgressBar1.Value = 0 : 最終數 = 0 月清單_dgv.Location = New System.Drawing.Point(91, 33) : 月清單_dgv.Size = New Size(1395, 790) Else 月清單_dgv.Location = New System.Drawing.Point(420, 2) : 月清單_dgv.Size = New Size(1066, 821) End If End Sub Private Sub 人員_dgv_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles 人員_dgv.CellClick If e.RowIndex = -1 Then : Else 編號 = 人員_dgv("編號", e.RowIndex).Value.ToString : 姓名_tb.Text = 人員_dgv("姓名", e.RowIndex).Value.ToString 時薪制_ch.Checked = 人員_dgv("CC18", e.RowIndex).Value : 到職日 = 人員_dgv("到職日", e.RowIndex).Value.ToString If IsDBNull(人員_dgv("月薪", e.RowIndex).Value) Then : 人員_dgv("月薪", e.RowIndex).Value = "0" : End If If IsDBNull(人員_dgv("時薪", e.RowIndex).Value) Then : 人員_dgv("時薪", e.RowIndex).Value = "0" : End If If IsDBNull(人員_dgv("主管加級", e.RowIndex).Value) Then : 人員_dgv("主管加級", e.RowIndex).Value = "0" : End If If IsDBNull(人員_dgv("職務加級", e.RowIndex).Value) Then : 人員_dgv("職務加級", e.RowIndex).Value = "0" : End If If IsDBNull(人員_dgv("健保費", e.RowIndex).Value) Then : 人員_dgv("健保費", e.RowIndex).Value = "0" : End If If IsDBNull(人員_dgv("勞保費", e.RowIndex).Value) Then : 人員_dgv("勞保費", e.RowIndex).Value = "0" : End If 月薪_tb.Text = Strings.Format(CDbl(人員_dgv("月薪", e.RowIndex).Value.ToString), "#,##0.0") 時薪_tb.Text = Strings.Format(CDbl(人員_dgv("時薪", e.RowIndex).Value.ToString), "#,##0.0") 主管加級_tb.Text = Strings.Format(CDbl(人員_dgv("主管加級", e.RowIndex).Value.ToString), "#,##0.0") 職務加級_tb.Text = Strings.Format(CDbl(人員_dgv("職務加級", e.RowIndex).Value.ToString), "#,##0.0") 健保費_tb.Text = Strings.Format(CDbl(人員_dgv("健保費", e.RowIndex).Value.ToString), "#,##0.0") 勞保費_tb.Text = Strings.Format(CDbl(人員_dgv("勞保費", e.RowIndex).Value.ToString), "#,##0.0") 資料數 = 31 : Set_考勤明細() : PA = 編號 : PA1 = 年份_cb.Text : PA2 = 月份_cb.Text : Set_考勤流水() : GRAMS_SYS.ToolStripProgressBar1.Value = 0 : 最終數 = 0 End If End Sub Private Sub 考勤流水_dgv_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles 考勤流水_dgv.CellClick If e.RowIndex = -1 Then : Else ID_tb.Text = 考勤流水_dgv("ID", e.RowIndex).Value.ToString : 時間_tb.Text = 考勤流水_dgv("打卡時間", e.RowIndex).Value.ToString PA6 = 考勤流水_dgv("打卡時間", e.RowIndex).Value.ToString : PA3 = 考勤流水_dgv("ID", e.RowIndex).Value.ToString End If End Sub Private Sub 月清單_dgv_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles 月清單_dgv.CellClick If e.RowIndex = -1 Then : Else ID_tb.Text = "" : 時間_tb.Text = "" '-如果下班時間超過晚上12點時的分段計算------------------------------------------ If Strings.Left(月清單_dgv(e.ColumnIndex, e.RowIndex).Value.ToString, 2) = "00" Or Strings.Left(月清單_dgv(e.ColumnIndex, e.RowIndex).Value.ToString, 2) = "01" Or Strings.Left(月清單_dgv(e.ColumnIndex, e.RowIndex).Value.ToString, 2) = "02" Then 時間_tb.Text = Strings.Left(月清單_dgv("日期", e.RowIndex).Value, 4) & "-" & Strings.Mid(月清單_dgv("日期", e.RowIndex).Value, 6, 2) & "-" & Strings.Right(月清單_dgv("日期", e.RowIndex).Value, 2) Dim dateString As String = 時間_tb.Text : Dim parsedDate As DateTime If DateTime.TryParseExact(dateString, "yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, parsedDate) Then parsedDate = parsedDate.AddDays(1) : 時間_tb.Text = parsedDate.ToString("yyyy-MM-dd") End If Else 時間_tb.Text = Strings.Left(月清單_dgv("日期", e.RowIndex).Value, 4) & "-" & Strings.Mid(月清單_dgv("日期", e.RowIndex).Value, 6, 2) & "-" & Strings.Right(月清單_dgv("日期", e.RowIndex).Value, 2) End If If e.ColumnIndex = 8 Or e.ColumnIndex = 9 Or e.ColumnIndex = 11 Or e.ColumnIndex = 12 Or e.ColumnIndex = 14 Or e.ColumnIndex = 15 Then If 月清單_dgv(e.ColumnIndex, e.RowIndex).Value <> "" Then 時間_tb.Text = 時間_tb.Text & " " & 月清單_dgv(e.ColumnIndex, e.RowIndex).Value For i As Integer = 0 To 考勤流水_dgv.Rows.Count - 1 If Len(時間_tb.Text) > 11 Then If 時間_tb.Text = 考勤流水_dgv("打卡時間", i).Value.ToString Then ID_tb.Text = 考勤流水_dgv("ID", i).Value : PA3 = 考勤流水_dgv("ID", i).Value : PA6 = 考勤流水_dgv("打卡時間", i).Value.ToString : Exit For End If ElseIf Len(時間_tb.Text) = 11 Then If Strings.Left(時間_tb.Text, 10) = Strings.Left(考勤流水_dgv("打卡時間", i).Value.ToString, 10) Then ID_tb.Text = 考勤流水_dgv("ID", i).Value : PA3 = 考勤流水_dgv("ID", i).Value : PA6 = 考勤流水_dgv("打卡時間", i).Value.ToString : Exit For End If End If Next If ID_tb.Text = "" Then Dim A As String = 時間_tb.Text : Dim format1 As String = "yyyy-MM-dd HH:mm:ss" : Dim format2 As String = "yyyy-MM-dd" : Dim parsedDate As DateTime If DateTime.TryParseExact(A, format1, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, parsedDate) Then ElseIf DateTime.TryParseExact(A, format2, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, parsedDate) Then If e.ColumnIndex = 8 Then : PA6 = 時間_tb.Text & " 08:00:00" ElseIf e.ColumnIndex = 9 Then : PA6 = 時間_tb.Text & " 12:00:00" ElseIf e.ColumnIndex = 11 Then : PA6 = 時間_tb.Text & " 13:00:00" ElseIf e.ColumnIndex = 12 Then : PA6 = 時間_tb.Text & " 17:00:00" ElseIf e.ColumnIndex = 14 Then : PA6 = 時間_tb.Text & " 18:00:00" ElseIf e.ColumnIndex = 15 Then : PA6 = 時間_tb.Text & " 20:00:00" End If : PA3 = "" Else : MsgBox("資料有問題，請通知系統管理員 [ " & 時間_tb.Text & " ]") : End If End If Else If ID_tb.Text = "" Then Dim A As String = 時間_tb.Text : Dim format1 As String = "yyyy-MM-dd HH:mm:ss" : Dim format2 As String = "yyyy-MM-dd" : Dim parsedDate As DateTime If DateTime.TryParseExact(A, format1, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, parsedDate) Then ElseIf DateTime.TryParseExact(A, format2, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, parsedDate) Then If e.ColumnIndex = 8 Then : PA6 = 時間_tb.Text & " 08:00:00" ElseIf e.ColumnIndex = 9 Then : PA6 = 時間_tb.Text & " 12:00:00" ElseIf e.ColumnIndex = 11 Then : PA6 = 時間_tb.Text & " 13:00:00" ElseIf e.ColumnIndex = 12 Then : PA6 = 時間_tb.Text & " 17:00:00" ElseIf e.ColumnIndex = 14 Then : PA6 = 時間_tb.Text & " 18:00:00" ElseIf e.ColumnIndex = 15 Then : PA6 = 時間_tb.Text & " 20:00:00" End If : PA3 = "" Else : MsgBox("資料有問題，請通知系統管理員 [ " & 時間_tb.Text & " ]") : End If End If End If 考勤明細表_編輯.時間_dtp.Value = PA6 : 考勤明細表_編輯.姓名_tb.Text = 姓名_tb.Text 考勤明細表_編輯.ID_lb.Text = PA3 : 考勤明細表_編輯.ID1_lb.Text = 編號 Else 時間_tb.Text = "" : ID_tb.Text = "" End If End If : 考勤明細表_編輯.BringToFront() End Sub Private Sub 月清單_dgv_RowPostPaint(ByVal sender As Object, ByVal e As DataGridViewRowPostPaintEventArgs) Handles 月清單_dgv.RowPostPaint For I As Integer = 0 To 月清單_dgv.Rows.Count - 1 If 月清單_dgv.Rows(I).Cells("星期").Value.ToString = "星期六" Then : 月清單_dgv.Rows(I).DefaultCellStyle.BackColor = Color.LightPink ElseIf 月清單_dgv.Rows(I).Cells("星期").Value.ToString = "星期日" Then : 月清單_dgv.Rows(I).DefaultCellStyle.BackColor = Color.LightPink ElseIf 月清單_dgv.Rows(I).Cells("休假類別").Value.ToString = "國定假日" Then : 月清單_dgv.Rows(I).DefaultCellStyle.BackColor = Color.LightPink ElseIf 月清單_dgv.Rows(I).Cells("休假類別").Value.ToString <> "" Then : 月清單_dgv.Rows(I).DefaultCellStyle.BackColor = Color.LightYellow End If Next 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 Strings.Left(考勤流水_dgv("打卡時間", e.RowIndex).Value.ToString, 10) <> Strings.Left(考勤流水_dgv("打卡時間", e.RowIndex + 1).Value.ToString, 10) 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 月份_cb_SelectedIndexChanged(sender As Object, e As EventArgs) Handles 月份_cb.SelectedIndexChanged PA1 = 年份_cb.Text : PA2 = 月份_cb.Text : 讀取人員資料表() If 編號 <> "" Then 資料數 = 31 : Set_考勤明細() : GRAMS_SYS.ToolStripProgressBar1.Value = 0 : 最終數 = 0 讀取日期() End If End Sub Private Sub 考勤資料編輯_bt_Click(sender As Object, e As EventArgs) Handles 考勤資料編輯_bt.Click If PA6 <> "" Then PA4 = PA6 : PA = 年份_cb.Text : PA1 = 月份_cb.Text : PA2 = Strings.Mid(PA6, 9, 2) PA5 = 姓名_tb.Text : PA7 = 編號 : 考勤明細表_編輯.Show() : 考勤重讀() Else MsgBox("請選擇右下方的時間，做為修改的依據!!") End If End Sub Private Sub 考勤重讀() PA = 編號 : PA1 = 年份_cb.Text : PA2 = 月份_cb.Text Set_考勤流水() : 資料數 = 31 : Set_考勤明細() : GRAMS_SYS.ToolStripProgressBar1.Value = 0 : 最終數 = 0 End Sub Private Sub R_bt_Click(sender As Object, e As EventArgs) Handles R_bt.Click 考勤重讀() End Sub Private Sub 排班資料_bt_Click(sender As Object, e As EventArgs) Handles 排班資料_bt.Click PA = 年份_cb.Text : PA1 = 月份_cb.Text : PA2 = 姓名_tb.Text : 個人班表.Close() : 個人班表.Show() End Sub Private Sub 時薪制_ch_CheckedChanged(sender As Object, e As EventArgs) Handles 時薪制_ch.Click Set_時薪制() End Sub Private Sub 彈性工時_ch_CheckedChanged(sender As Object, e As EventArgs) Handles 彈性工時_ch.Click Set_彈性工時() End Sub Private Sub 薪資表存檔_bt_Click(sender As Object, e As EventArgs) Handles 薪資表存檔_bt.Click If 姓名_tb.Text = "" Then : MsgBox("沒有資料無法存檔!!") : Else Dim 流水號 As String = "" : Dim 流水 As Integer : Dim 薪資 As Double PA1 = CInt(年份_cb.Text) - 1911 & "-" & 月份_cb.Text : PA2 = 到職日 : PA3 = 姓名_tb.Text : PA4 = 月清單_dgv.Rows.Count PA5 = 當月平日天_tb.Text : PA6 = 常日總_tb.Text : PA7 = 月薪_tb.Text : PA8 = 時薪_tb.Text : PA9 = "0" : PA10 = 主管加級_tb.Text : PA11 = 職務加級_tb.Text PA12 = 特休未休_nud.Value : PA13 = 特修補錢_tb.Text : PA14 = 特休_tb.Text : PA15 = 婚假_tb.Text : PA16 = 喪假_tb.Text PA17 = 其他加項_tb.Text : PA18 = 其他加_nud.Value : PA19 = 未到離職_tb.Text 薪資 = CDbl(月薪_tb.Text) + CDbl(主管加級_tb.Text) + CDbl(職務加級_tb.Text) + CDbl(特修補錢_tb.Text) + CDbl(特休_tb.Text) + CDbl(婚假_tb.Text) + CDbl(喪假_tb.Text) + 其他加_nud.Value - CDbl(未到離職_tb.Text) If 時薪制_ch.Checked = False Then : PA20 = 薪資 : PA21 = "0" : PA22 = 薪資 : Else : PA20 = "0" : PA21 = 薪資 : PA22 = 薪資 : End If PA23 = CDbl(當月平日一段_tb.Text) + CDbl(一段假日加_tb.Text) : PA24 = CDbl(當月平日二段_tb.Text) + CDbl(二段假日加_tb.Text) : PA25 = 國假日加_tb.Text PA26 = 加班費1_tb.Text : PA27 = 應付小計1_tb.Text PA28 = CDbl(當月平日一段1_tb.Text) + CDbl(一段假日加1_tb.Text) : PA29 = CDbl(當月平日二段1_tb.Text) + CDbl(二段假日加1_tb.Text) : PA30 = 國假日加1_tb.Text PA31 = 加班費2_tb.Text : PA32 = 應付小計2_tb.Text PA33 = 勞保費_tb.Text : PA34 = 健保費_tb.Text : PA35 = 事假_tb.Text : PA36 = 病假_tb.Text : PA37 = 其他減項_tb.Text : PA38 = 其他減_nud.Value PA39 = CDbl(勞保費_tb.Text) + CDbl(健保費_tb.Text) + CDbl(事假_tb.Text) + CDbl(病假_tb.Text) + 其他減_nud.Value : PA40 = "0" PA41 = 實付小計1_tb.Text : PA42 = 實付小計2_tb.Text SQL_薪資表確認() If dr.Read() Then PA = dr("流水號").ToString : SQL_薪資表修改() : MsgBox("薪資資料修改完成!!") Else SQL_薪資表流水號() If dr.Read() Then : 流水 = Double.Parse(Strings.Right(dr("流水號").ToString, 8)) : Else : 流水 = 0 : End If : conn.Close() : 流水 += 1 If 流水 < 10 Then : 流水號 = "PO" & "0000000" & 流水 ElseIf 流水 > 9 And 流水 < 100 Then : 流水號 = "PO" & "000000" & 流水 ElseIf 流水 > 99 And 流水 < 1000 Then : 流水號 = "PO" & "00000" & 流水 ElseIf 流水 > 999 And 流水 < 10000 Then : 流水號 = "PO" & "0000" & 流水 ElseIf 流水 > 9999 And 流水 < 100000 Then : 流水號 = "PO" & "000" & 流水 ElseIf 流水 > 99999 And 流水 < 1000000 Then : 流水號 = "PO" & "00" & 流水 ElseIf 流水 > 999999 And 流水 < 10000000 Then : 流水號 = "PO" & "0" & 流水 ElseIf 流水 > 9999999 Then : 流水號 = "PO" & 流水 : End If : PA = 流水號 SQL_薪資表新增() : MsgBox("薪資資料新增完成!!") End If End If End Sub Private Sub 列印工時表_bt_Click(sender As Object, e As EventArgs) Handles 列印工時表_bt.Click If 姓名_tb.Text = "" Then : PA = "全體" : Else : 考勤表列印選擇.ShowDialog() : End If xlApp = CType(CreateObject("Excel.Application"), Application) xlBook = xlApp.Workbooks.Add xlApp.DisplayAlerts = True xlApp.Visible = True xlApp.Application.WindowState = xlMaximized If PA = "單人" Then 列印工時表() : xlSheet.PageSetup.PrintArea = "" : xlApp.Cells.Select() : xlSheet.Range("B1").Select() : xlApp.Application.WindowState = xlMinimized : MsgBox("考勤表列印完成!!") Else For I As Integer = 0 To 人員_dgv.Rows.Count - 1 編號 = 人員_dgv("編號", I).Value.ToString : 姓名_tb.Text = 人員_dgv("姓名", I).Value.ToString : 時薪制_ch.Checked = 人員_dgv("CC18", I).Value 到職日 = 人員_dgv("到職日", I).Value.ToString If 人員_dgv("月薪", I).Value.ToString = "" Then : 月薪_tb.Text = "0" : Else : 月薪_tb.Text = Strings.Format(CDbl(人員_dgv("月薪", I).Value.ToString), "#,##0.0") : End If If 人員_dgv("時薪", I).Value.ToString = "" Then : 時薪_tb.Text = "0" : Else : 時薪_tb.Text = Strings.Format(CDbl(人員_dgv("時薪", I).Value.ToString), "#,##0.0") : End If If 人員_dgv("時薪", I).Value.ToString = "" Then : 主管加級_tb.Text = "0" : Else : 主管加級_tb.Text = Strings.Format(CDbl(人員_dgv("主管加級", I).Value.ToString), "#,##0.0") : End If If 人員_dgv("時薪", I).Value.ToString = "" Then : 職務加級_tb.Text = "0" : Else : 職務加級_tb.Text = Strings.Format(CDbl(人員_dgv("職務加級", I).Value.ToString), "#,##0.0") : End If If 人員_dgv("時薪", I).Value.ToString = "" Then : 健保費_tb.Text = "0" : Else : 健保費_tb.Text = Strings.Format(CDbl(人員_dgv("健保費", I).Value.ToString), "#,##0.0") : End If If 人員_dgv("時薪", I).Value.ToString = "" Then : 勞保費_tb.Text = "0" : Else : 勞保費_tb.Text = Strings.Format(CDbl(人員_dgv("勞保費", I).Value.ToString), "#,##0.0") : End If 資料數 = 31 : Set_考勤明細() : PA = 編號 : PA1 = 年份_cb.Text : PA2 = 月份_cb.Text : Set_考勤流水() : GRAMS_SYS.ToolStripProgressBar1.Value = 0 : 最終數 = 0 列印工時表() Next xlSheet.PageSetup.PrintArea = "" : xlApp.Cells.Select() : xlSheet.Range("B1").Select() : xlApp.Application.WindowState = xlMinimized : MsgBox("全體考勤表列印完成!!") End If End Sub Private Sub 列印工時表() xlSheet = NewMethod(xlBook) : xlApp.Sheets(1).Select xlApp.Sheets(1).Name = 姓名_tb.Text : xlBook.Activate() : xlSheet.Activate() xlSheet.Cells(1, 1) = "黃柏翰皮膚科診所考勤明細表" : xlSheet.Cells(2, 1) = CInt(年份_cb.Text) - 1911 & "年" & 月份_cb.Text & "月 - " & 姓名_tb.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) = "加班內容" For I As Integer = 0 To 月清單_dgv.Rows.Count - 1 xlSheet.Cells(4 + I, 1) = 月清單_dgv.Rows(I).Cells("日期").Value.ToString : xlSheet.Cells(4 + I, 2) = 月清單_dgv.Rows(I).Cells("星期").Value.ToString xlSheet.Cells(4 + I, 3) = 月清單_dgv.Rows(I).Cells("休假類別").Value.ToString : xlSheet.Cells(4 + I, 4) = 月清單_dgv.Rows(I).Cells("假期內容").Value.ToString xlSheet.Cells(4 + I, 5) = 月清單_dgv.Rows(I).Cells("申請時數").Value.ToString : xlSheet.Cells(4 + I, 6) = 月清單_dgv.Rows(I).Cells("一段上班").Value.ToString xlSheet.Cells(4 + I, 7) = 月清單_dgv.Rows(I).Cells("一段下班").Value.ToString : xlSheet.Cells(4 + I, 8) = 月清單_dgv.Rows(I).Cells("二段上班").Value.ToString xlSheet.Cells(4 + I, 9) = 月清單_dgv.Rows(I).Cells("二段下班").Value.ToString : xlSheet.Cells(4 + I, 10) = 月清單_dgv.Rows(I).Cells("加班上班").Value.ToString xlSheet.Cells(4 + I, 11) = 月清單_dgv.Rows(I).Cells("加班下班").Value.ToString : xlSheet.Cells(4 + I, 12) = 月清單_dgv.Rows(I).Cells("一段工時").Value.ToString xlSheet.Cells(4 + I, 13) = 月清單_dgv.Rows(I).Cells("二段工時").Value.ToString : xlSheet.Cells(4 + I, 14) = 月清單_dgv.Rows(I).Cells("一段加班").Value.ToString xlSheet.Cells(4 + I, 15) = 月清單_dgv.Rows(I).Cells("二段加班").Value.ToString : xlSheet.Cells(4 + I, 16) = 月清單_dgv.Rows(I).Cells("申請狀態").Value.ToString xlSheet.Cells(4 + I, 17) = 月清單_dgv.Rows(I).Cells("加班狀態").Value.ToString Next : AA(xlApp, xlSheet) 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 = 10 : .Strikethrough = False : .Superscript = False : .Subscript = False : .OutlineFont = False .Shadow = False : .Underline = xlUnderlineStyleNone : .ThemeColor = xlThemeColorLight1 : .TintAndShade = 0 : .ThemeFont = xlThemeFontNone : End With xlSheet.Range("A3:Q36").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.Columns("A:A").Select : myExcel.Selection.ColumnWidth = 10 xlSheet.Columns("B:Q").Select : myExcel.Columns("B:Q").EntireColumn.AutoFit xlSheet.Rows("3:34").Select : myExcel.Selection.RowHeight = 20 xlSheet.Rows("4:4").Select : myExcel.ActiveWindow.FreezePanes = True xlSheet.Range("A3:Q34").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 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 = xlThin : End With xlSheet.Range("A3:Q3").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 = xlThin : End With myExcel.Selection.Borders(xlInsideHorizontal).LineStyle = xlNone With myExcel.Selection.Interior : .Pattern = xlSolid : .PatternColorIndex = xlAutomatic : .ThemeColor = xlThemeColorAccent1 : .TintAndShade = 0.799981688894314 .PatternTintAndShade = 0 : End With For I As Integer = 4 To 36 If Strings.Left(xlSheet.Cells(I, 2).value, 3) = "星期六" Or Strings.Left(xlSheet.Cells(I, 2).value, 3) = "星期日" Then xlSheet.Range("A" & I & ":Q" & I).Select() With myExcel.Selection.Interior : .Pattern = xlSolid : .PatternColorIndex = xlAutomatic : .ThemeColor = xlThemeColorAccent2 : .TintAndShade = 0.799981688894314 .PatternTintAndShade = 0 : End With End If Next xlSheet.Range("A2:Q2").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.Font : .Name = "微軟正黑體" : .Size = 12 : .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.Range("A1:Q1").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.Font : .Name = "微軟正黑體" : .Size = 16 : .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.Range("Q4").Select() : myExcel.ActiveWindow.DisplayGridlines = False End Sub End Class