HSKIN-MIS-SYS
/
HSKIN-MIS-SYS-SIU
İzle 4
Yıldızla 0
Çatalla 0
Kod Sorunlar 0 Değişiklik İstekleri 0 Sürümler 0 Wiki Aktivite
Açıklama Yok
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
3 İşlemeler
1 Dal
Dal: master
Dallar Biçim İmleri
${ item.name }
Branş ${ searchTerm } oluştur
'master'den
${ noResults }
HSKIN-MIS-SYS-SIU/GRAMS-SYS/04、考勤管理/薪資表.vb

薪資表.vb 35KB
Kalıcı Bağlantı Geçmiş Ham

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347 
  1. Imports Microsoft.Office.Interop.Excel.XlUnderlineStyle

  2. Imports Microsoft.Office.Interop.Excel.Constants

  3. Imports Microsoft.Office.Interop.Excel.XlBordersIndex

  4. Imports Microsoft.Office.Interop.Excel.XlLineStyle

  5. Imports Microsoft.Office.Interop.Excel.XlBorderWeight

  6. Imports Microsoft.Office.Interop.Excel.XlThemeFont

  7. Imports Microsoft.Office.Interop.Excel.XlThemeColor

  8. Imports Microsoft.Office.Interop.Excel.XlWindowState

  9. Imports Microsoft.Office.Interop.Excel.XlPageOrientation

  10. Imports Microsoft.Office.Interop.Excel

  11. Public Class 薪資表

  12.     Dim 剛開啟 As Boolean : Dim 位置1 As Integer

  13.     Dim xlApp As Application : Dim xlBook As Workbook : Dim xlSheet As Worksheet

  14.     Private Sub 讀取打卡時間1()

  15.         Dim ds1, ds2 As New DataSet

  16.         月清單_dgv.DataSource = Nothing : ds1.Clear()

  17.         月清單_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing

  18.         月清單_dgv.ColumnHeadersHeight = 45 : 月清單_dgv.AllowUserToAddRows = False

  19.         SQL_薪資表讀取()

  20.         da.Fill(ds1) : 月清單_dgv.DataSource = ds1.Tables(0) : conn.Close()

  21. 

  22.         合計_dgv.DataSource = Nothing : ds2.Clear()

  23.         合計_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing

  24.         合計_dgv.ColumnHeadersHeight = 45 : 合計_dgv.AllowUserToAddRows = False : 合計_dgv.ColumnHeadersVisible = False

  25.         SQL_薪資表讀取_合計()

  26.         da.Fill(ds2) : 合計_dgv.DataSource = ds2.Tables(0) : conn.Close()

  27.         格式設定()

  28.     End Sub

  29.     Private Sub 格式設定()

  30.         月清單_dgv.Columns("項").FillWeight = 50 : 月清單_dgv.Columns("實上工時").FillWeight = 80 : 月清單_dgv.Columns("基本時薪").FillWeight = 80

  31.         月清單_dgv.Columns("主管加級").FillWeight = 90 : 月清單_dgv.Columns("職務加級").FillWeight = 90 : 月清單_dgv.Columns("婚假").FillWeight = 80

  32.         月清單_dgv.Columns("喪假").FillWeight = 80 : 月清單_dgv.Columns("其他說明").FillWeight = 150 : 月清單_dgv.Columns("其他").FillWeight = 80

  33.         月清單_dgv.Columns("未到離職").FillWeight = 80 : 月清單_dgv.Columns("一段加班1").FillWeight = 80 : 月清單_dgv.Columns("一段加班2").FillWeight = 80

  34.         月清單_dgv.Columns("二段加班1").FillWeight = 80 : 月清單_dgv.Columns("二段加班2").FillWeight = 80 : 月清單_dgv.Columns("國定假日1").FillWeight = 80

  35.         月清單_dgv.Columns("國定假日2").FillWeight = 80 : 月清單_dgv.Columns("勞保費").FillWeight = 80 : 月清單_dgv.Columns("健保費").FillWeight = 80

  36.         月清單_dgv.Columns("事假").FillWeight = 80 : 月清單_dgv.Columns("病假").FillWeight = 80 : 月清單_dgv.Columns("其他說明減").FillWeight = 150

  37.         月清單_dgv.Columns("其他減").FillWeight = 80 : 月清單_dgv.Columns("姓名").FillWeight = 80

  38. 

  39.         月清單_dgv.Columns("基本月薪").DefaultCellStyle.BackColor = Color.MistyRose : 月清單_dgv.Columns("主管加級").DefaultCellStyle.BackColor = Color.MistyRose

  40.         月清單_dgv.Columns("職務加級").DefaultCellStyle.BackColor = Color.MistyRose : 月清單_dgv.Columns("婚假").DefaultCellStyle.BackColor = Color.MistyRose

  41.         月清單_dgv.Columns("喪假").DefaultCellStyle.BackColor = Color.MistyRose : 月清單_dgv.Columns("其他").DefaultCellStyle.BackColor = Color.MistyRose

  42.         月清單_dgv.Columns("未到離職").DefaultCellStyle.BackColor = Color.MistyRose : 月清單_dgv.Columns("本薪").DefaultCellStyle.BackColor = Color.MistyRose

  43.         月清單_dgv.Columns("特休補錢").DefaultCellStyle.BackColor = Color.MistyRose

  44. 

  45.         月清單_dgv.Columns("一段加班1").DefaultCellStyle.BackColor = Color.LightSalmon : 月清單_dgv.Columns("一段加班2").DefaultCellStyle.BackColor = Color.LightSalmon

  46.         月清單_dgv.Columns("二段加班1").DefaultCellStyle.BackColor = Color.LightSalmon : 月清單_dgv.Columns("二段加班2").DefaultCellStyle.BackColor = Color.LightSalmon

  47.         月清單_dgv.Columns("國定假日1").DefaultCellStyle.BackColor = Color.LightSalmon : 月清單_dgv.Columns("國定假日2").DefaultCellStyle.BackColor = Color.LightSalmon

  48.         月清單_dgv.Columns("加班費1").DefaultCellStyle.BackColor = Color.LightSalmon : 月清單_dgv.Columns("加班費2").DefaultCellStyle.BackColor = Color.LightSalmon

  49.         月清單_dgv.Columns("應付小計1").DefaultCellStyle.BackColor = Color.LightSalmon : 月清單_dgv.Columns("應付小計2").DefaultCellStyle.BackColor = Color.LightSalmon

  50. 

  51.         月清單_dgv.Columns("勞保費").DefaultCellStyle.BackColor = Color.LightCyan : 月清單_dgv.Columns("健保費").DefaultCellStyle.BackColor = Color.LightCyan

  52.         月清單_dgv.Columns("事假").DefaultCellStyle.BackColor = Color.LightCyan : 月清單_dgv.Columns("病假").DefaultCellStyle.BackColor = Color.LightCyan

  53.         月清單_dgv.Columns("其他減").DefaultCellStyle.BackColor = Color.LightCyan : 月清單_dgv.Columns("減項小計1").DefaultCellStyle.BackColor = Color.LightCyan

  54. 

  55.         月清單_dgv.Columns("實付小計1").DefaultCellStyle.BackColor = Color.LightGreen : 月清單_dgv.Columns("實付小計2").DefaultCellStyle.BackColor = Color.LightGreen

  56. 

  57.         月清單_dgv.Columns("日期").Visible = False : 月清單_dgv.Columns("到職日").Visible = False : 月清單_dgv.Columns("全勤").Visible = False

  58.         月清單_dgv.Columns("月薪").Visible = False : 月清單_dgv.Columns("時薪").Visible = False : 月清單_dgv.Columns("特休未修").Visible = False

  59.         月清單_dgv.Columns("當月天數").Visible = False : 月清單_dgv.Columns("減項小計2").Visible = False : 月清單_dgv.Columns("實上基數").Visible = False

  60.         月清單_dgv.Columns("時薪特休").Visible = False

  61.         For i As Integer = 4 To 16

  62.             月清單_dgv.Columns(i).DefaultCellStyle.Format = "#,##0" : 月清單_dgv.Columns(i).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

  63.         Next

  64.         For i As Integer = 18 To 36

  65.             月清單_dgv.Columns(i).DefaultCellStyle.Format = "#,##0" : 月清單_dgv.Columns(i).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

  66.         Next

  67.         For i As Integer = 23 To 25

  68.             月清單_dgv.Columns(i).DefaultCellStyle.Format = "#,##0.0" : 月清單_dgv.Columns(i).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

  69.         Next

  70.         For i As Integer = 28 To 30

  71.             月清單_dgv.Columns(i).DefaultCellStyle.Format = "#,##0.0" : 月清單_dgv.Columns(i).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

  72.         Next

  73.         For i As Integer = 38 To 42

  74.             月清單_dgv.Columns(i).DefaultCellStyle.Format = "#,##0" : 月清單_dgv.Columns(i).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

  75.         Next

  76. 

  77.         If 彈性工時_cb.Checked = True Then

  78.             月清單_dgv.Columns("一段加班2").Visible = True : 月清單_dgv.Columns("二段加班2").Visible = True : 月清單_dgv.Columns("國定假日2").Visible = True

  79.             月清單_dgv.Columns("加班費2").Visible = True : 月清單_dgv.Columns("應付小計2").Visible = True : 月清單_dgv.Columns("實付小計2").Visible = True

  80. 

  81.             月清單_dgv.Columns("一段加班1").Visible = False : 月清單_dgv.Columns("二段加班1").Visible = False : 月清單_dgv.Columns("國定假日1").Visible = False

  82.             月清單_dgv.Columns("加班費1").Visible = False : 月清單_dgv.Columns("應付小計1").Visible = False : 月清單_dgv.Columns("實付小計1").Visible = False

  83.         Else

  84.             月清單_dgv.Columns("一段加班2").Visible = False : 月清單_dgv.Columns("二段加班2").Visible = False : 月清單_dgv.Columns("國定假日2").Visible = False

  85.             月清單_dgv.Columns("加班費2").Visible = False : 月清單_dgv.Columns("應付小計2").Visible = False : 月清單_dgv.Columns("實付小計2").Visible = False

  86. 

  87.             月清單_dgv.Columns("一段加班1").Visible = True : 月清單_dgv.Columns("二段加班1").Visible = True : 月清單_dgv.Columns("國定假日1").Visible = True

  88.             月清單_dgv.Columns("加班費1").Visible = True : 月清單_dgv.Columns("應付小計1").Visible = True : 月清單_dgv.Columns("實付小計1").Visible = True

  89.         End If

  90.         For i As Integer = 0 To 月清單_dgv.Rows.Count - 1

  91.             月清單_dgv("項", i).Value = i + 1

  92.             月清單_dgv("特休補錢", i).Value = 月清單_dgv("特休補錢", i).Value + 月清單_dgv("時薪特休", i).Value

  93.         Next

  94. 

  95.         合計_dgv.Columns("項").FillWeight = 50 : 合計_dgv.Columns("實上工時").FillWeight = 80 : 合計_dgv.Columns("基本時薪").FillWeight = 80

  96.         合計_dgv.Columns("主管加級").FillWeight = 90 : 合計_dgv.Columns("職務加級").FillWeight = 90 : 合計_dgv.Columns("婚假").FillWeight = 80

  97.         合計_dgv.Columns("喪假").FillWeight = 80 : 合計_dgv.Columns("其他說明").FillWeight = 150 : 合計_dgv.Columns("其他").FillWeight = 80

  98.         合計_dgv.Columns("未到離職").FillWeight = 80 : 合計_dgv.Columns("一段加班1").FillWeight = 80 : 合計_dgv.Columns("一段加班2").FillWeight = 80

  99.         合計_dgv.Columns("二段加班1").FillWeight = 80 : 合計_dgv.Columns("二段加班2").FillWeight = 80 : 合計_dgv.Columns("國定假日1").FillWeight = 80

  100.         合計_dgv.Columns("國定假日2").FillWeight = 80 : 合計_dgv.Columns("勞保費").FillWeight = 80 : 合計_dgv.Columns("健保費").FillWeight = 80

  101.         合計_dgv.Columns("事假").FillWeight = 80 : 合計_dgv.Columns("病假").FillWeight = 80 : 合計_dgv.Columns("其他說明減").FillWeight = 150

  102.         合計_dgv.Columns("其他減").FillWeight = 80 : 合計_dgv.Columns("姓名").FillWeight = 80

  103. 

  104.         合計_dgv.Columns("基本月薪").DefaultCellStyle.BackColor = Color.MistyRose : 合計_dgv.Columns("主管加級").DefaultCellStyle.BackColor = Color.MistyRose

  105.         合計_dgv.Columns("職務加級").DefaultCellStyle.BackColor = Color.MistyRose : 合計_dgv.Columns("婚假").DefaultCellStyle.BackColor = Color.MistyRose

  106.         合計_dgv.Columns("喪假").DefaultCellStyle.BackColor = Color.MistyRose : 合計_dgv.Columns("其他").DefaultCellStyle.BackColor = Color.MistyRose

  107.         合計_dgv.Columns("未到離職").DefaultCellStyle.BackColor = Color.MistyRose : 合計_dgv.Columns("本薪").DefaultCellStyle.BackColor = Color.MistyRose

  108.         合計_dgv.Columns("特休補錢").DefaultCellStyle.BackColor = Color.MistyRose

  109. 

  110.         合計_dgv.Columns("一段加班1").DefaultCellStyle.BackColor = Color.LightSalmon : 合計_dgv.Columns("一段加班2").DefaultCellStyle.BackColor = Color.LightSalmon

  111.         合計_dgv.Columns("二段加班1").DefaultCellStyle.BackColor = Color.LightSalmon : 合計_dgv.Columns("二段加班2").DefaultCellStyle.BackColor = Color.LightSalmon

  112.         合計_dgv.Columns("國定假日1").DefaultCellStyle.BackColor = Color.LightSalmon : 合計_dgv.Columns("國定假日2").DefaultCellStyle.BackColor = Color.LightSalmon

  113.         合計_dgv.Columns("加班費1").DefaultCellStyle.BackColor = Color.LightSalmon : 合計_dgv.Columns("加班費2").DefaultCellStyle.BackColor = Color.LightSalmon

  114.         合計_dgv.Columns("應付小計1").DefaultCellStyle.BackColor = Color.LightSalmon : 合計_dgv.Columns("應付小計2").DefaultCellStyle.BackColor = Color.LightSalmon

  115. 

  116.         合計_dgv.Columns("勞保費").DefaultCellStyle.BackColor = Color.LightCyan : 合計_dgv.Columns("健保費").DefaultCellStyle.BackColor = Color.LightCyan

  117.         合計_dgv.Columns("事假").DefaultCellStyle.BackColor = Color.LightCyan : 合計_dgv.Columns("病假").DefaultCellStyle.BackColor = Color.LightCyan

  118.         合計_dgv.Columns("其他減").DefaultCellStyle.BackColor = Color.LightCyan : 合計_dgv.Columns("減項小計1").DefaultCellStyle.BackColor = Color.LightCyan

  119. 

  120.         合計_dgv.Columns("實付小計1").DefaultCellStyle.BackColor = Color.LightGreen : 合計_dgv.Columns("實付小計2").DefaultCellStyle.BackColor = Color.LightGreen

  121. 

  122.         合計_dgv.Columns("日期").Visible = False : 合計_dgv.Columns("到職日").Visible = False : 合計_dgv.Columns("全勤").Visible = False

  123.         合計_dgv.Columns("月薪").Visible = False : 合計_dgv.Columns("時薪").Visible = False : 合計_dgv.Columns("特休未修").Visible = False

  124.         合計_dgv.Columns("當月天數").Visible = False : 合計_dgv.Columns("減項小計2").Visible = False : 合計_dgv.Columns("實上基數").Visible = False

  125.         合計_dgv.Columns("時薪特休").Visible = False

  126.         For i As Integer = 4 To 16

  127.             合計_dgv.Columns(i).DefaultCellStyle.Format = "#,##0" : 合計_dgv.Columns(i).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

  128.         Next

  129.         For i As Integer = 18 To 36

  130.             合計_dgv.Columns(i).DefaultCellStyle.Format = "#,##0" : 合計_dgv.Columns(i).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

  131.         Next

  132.         For i As Integer = 23 To 25

  133.             合計_dgv.Columns(i).DefaultCellStyle.Format = "#,##0.0" : 合計_dgv.Columns(i).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

  134.         Next

  135.         For i As Integer = 28 To 30

  136.             合計_dgv.Columns(i).DefaultCellStyle.Format = "#,##0.0" : 合計_dgv.Columns(i).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

  137.         Next

  138.         For i As Integer = 38 To 42

  139.             合計_dgv.Columns(i).DefaultCellStyle.Format = "#,##0" : 合計_dgv.Columns(i).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

  140.         Next

  141. 

  142.         If 彈性工時_cb.Checked = True Then

  143.             合計_dgv.Columns("一段加班2").Visible = True : 合計_dgv.Columns("二段加班2").Visible = True : 合計_dgv.Columns("國定假日2").Visible = True

  144.             合計_dgv.Columns("加班費2").Visible = True : 合計_dgv.Columns("應付小計2").Visible = True : 合計_dgv.Columns("實付小計2").Visible = True

  145. 

  146.             合計_dgv.Columns("一段加班1").Visible = False : 合計_dgv.Columns("二段加班1").Visible = False : 合計_dgv.Columns("國定假日1").Visible = False

  147.             合計_dgv.Columns("加班費1").Visible = False : 合計_dgv.Columns("應付小計1").Visible = False : 合計_dgv.Columns("實付小計1").Visible = False

  148.         Else

  149.             合計_dgv.Columns("一段加班2").Visible = False : 合計_dgv.Columns("二段加班2").Visible = False : 合計_dgv.Columns("國定假日2").Visible = False

  150.             合計_dgv.Columns("加班費2").Visible = False : 合計_dgv.Columns("應付小計2").Visible = False : 合計_dgv.Columns("實付小計2").Visible = False

  151. 

  152.             合計_dgv.Columns("一段加班1").Visible = True : 合計_dgv.Columns("二段加班1").Visible = True : 合計_dgv.Columns("國定假日1").Visible = True

  153.             合計_dgv.Columns("加班費1").Visible = True : 合計_dgv.Columns("應付小計1").Visible = True : 合計_dgv.Columns("實付小計1").Visible = True

  154.         End If

  155.         If 合計_dgv.Rows.Count > 0 Then

  156.             合計_dgv("項", 0).Value = "" : 合計_dgv("姓名", 0).Value = "" : 合計_dgv("實上工時", 0).Value = "0" : 合計_dgv("基本月薪", 0).Value = "0"

  157.             合計_dgv("基本時薪", 0).Value = "0" : 合計_dgv("主管加級", 0).Value = "0" : 合計_dgv("其他說明", 0).Value = "" : 合計_dgv("婚假", 0).Value = "0"

  158.             合計_dgv("職務加級", 0).Value = "0" : 合計_dgv("喪假", 0).Value = "0" : 合計_dgv("其他", 0).Value = "0" : 合計_dgv("未到離職", 0).Value = "0"

  159.             合計_dgv("本薪", 0).Value = "0" : 合計_dgv("一段加班1", 0).Value = "0" : 合計_dgv("一段加班2", 0).Value = "0" : 合計_dgv("二段加班1", 0).Value = "0"

  160.             合計_dgv("二段加班2", 0).Value = "0" : 合計_dgv("國定假日1", 0).Value = "0" : 合計_dgv("國定假日2", 0).Value = "0" : 合計_dgv("加班費1", 0).Value = "0"

  161.             合計_dgv("加班費2", 0).Value = "0" : 合計_dgv("應付小計1", 0).Value = "0" : 合計_dgv("應付小計2", 0).Value = "0" : 合計_dgv("其他說明減", 0).Value = ""

  162.             合計_dgv("勞保費", 0).Value = "0" : 合計_dgv("健保費", 0).Value = "0" : 合計_dgv("事假", 0).Value = "0" : 合計_dgv("病假", 0).Value = "0"

  163.             合計_dgv("其他減", 0).Value = "0" : 合計_dgv("減項小計1", 0).Value = "0" : 合計_dgv("實付小計1", 0).Value = "0" : 合計_dgv("實付小計2", 0).Value = "0"

  164.             For i As Integer = 0 To 月清單_dgv.Rows.Count - 1

  165.                 合計_dgv("主管加級", 0).Value += 月清單_dgv("主管加級", i).Value : 合計_dgv("職務加級", 0).Value += 月清單_dgv("職務加級", i).Value

  166.                 合計_dgv("婚假", 0).Value += 月清單_dgv("婚假", i).Value : 合計_dgv("喪假", 0).Value += 月清單_dgv("喪假", i).Value

  167.                 合計_dgv("其他", 0).Value += 月清單_dgv("其他", i).Value : 合計_dgv("未到離職", 0).Value += 月清單_dgv("未到離職", i).Value

  168.                 合計_dgv("本薪", 0).Value += 月清單_dgv("本薪", i).Value : 合計_dgv("加班費1", 0).Value += 月清單_dgv("加班費1", i).Value

  169.                 合計_dgv("加班費2", 0).Value += 月清單_dgv("加班費2", i).Value : 合計_dgv("應付小計1", 0).Value += 月清單_dgv("應付小計1", i).Value

  170.                 合計_dgv("應付小計2", 0).Value += 月清單_dgv("應付小計2", i).Value : 合計_dgv("勞保費", 0).Value += 月清單_dgv("勞保費", i).Value

  171.                 合計_dgv("健保費", 0).Value += 月清單_dgv("健保費", i).Value : 合計_dgv("事假", 0).Value += 月清單_dgv("事假", i).Value

  172.                 合計_dgv("病假", 0).Value += 月清單_dgv("病假", i).Value : 合計_dgv("其他減", 0).Value += 月清單_dgv("其他減", i).Value

  173.                 合計_dgv("減項小計1", 0).Value += 月清單_dgv("減項小計1", i).Value : 合計_dgv("實付小計1", 0).Value += 月清單_dgv("實付小計1", i).Value

  174.                 合計_dgv("實付小計2", 0).Value += 月清單_dgv("實付小計2", i).Value

  175.             Next

  176.         End If

  177.     End Sub

  178.     Private Sub 年份_下拉表單資料載入()

  179.         SQL_考勤明細表_年份_下拉()

  180.         年份_cb.Items.Clear()

  181.         While (dr.Read()) : 年份_cb.Items.Add(dr("年份")) : End While

  182.         conn.Close()

  183.     End Sub

  184.     Private Sub 薪資表_Load(sender As Object, e As EventArgs) Handles MyBase.Load

  185.         剛開啟 = True

  186.     End Sub

  187.     Private Sub 薪資表_Shown(sender As Object, e As EventArgs) Handles MyBase.Shown

  188.         年份_下拉表單資料載入()

  189.         年份_cb.Text = Year(Today) : 月份_cb.SelectedIndex = Month(Today) - 1

  190.         PA1 = CInt(年份_cb.Text) - 1911 & "-" & 月份_cb.Text

  191.         讀取打卡時間1() : 剛開啟 = False

  192.     End Sub

  193.     Private Sub R_bt_Click(sender As Object, e As EventArgs) Handles R_bt.Click

  194.         PA1 = CInt(年份_cb.Text) - 1911 & "-" & 月份_cb.Text : 讀取打卡時間1()

  195.     End Sub

  196.     Private Sub 年份_cb_SelectedIndexChanged(sender As Object, e As EventArgs) Handles 年份_cb.SelectedIndexChanged

  197.         If 剛開啟 = False Then : PA1 = CInt(年份_cb.Text) - 1911 & "-" & 月份_cb.Text : 讀取打卡時間1() : End If

  198.     End Sub

  199.     Private Sub 月份_cb_SelectedIndexChanged(sender As Object, e As EventArgs) Handles 月份_cb.SelectedIndexChanged

  200.         If 剛開啟 = False Then : PA1 = CInt(年份_cb.Text) - 1911 & "-" & 月份_cb.Text : 讀取打卡時間1() : End If

  201.     End Sub

  202.     Private Sub 彈性工時_cb_CheckedChanged(sender As Object, e As EventArgs) Handles 彈性工時_cb.Click

  203.         彈性工時_cb.Checked = True : 正常工時_ch.Checked = False : 格式設定()

  204.     End Sub

  205.     Private Sub 正常工時_ch_CheckedChanged(sender As Object, e As EventArgs) Handles 正常工時_ch.Click

  206.         彈性工時_cb.Checked = False : 正常工時_ch.Checked = True : 格式設定()

  207.     End Sub

  208.     Private Sub 列印_bt_Click(sender As Object, e As EventArgs) Handles 列印_bt.Click

  209.         xlApp = CType(CreateObject("Excel.Application"), Application)

  210.         xlBook = xlApp.Workbooks.Add

  211.         xlApp.DisplayAlerts = True

  212.         xlApp.Visible = True

  213.         xlApp.Application.WindowState = xlMaximized

  214. 

  215.         xlSheet = NewMethod(xlBook) : xlApp.Sheets(1).Select

  216.         xlApp.Sheets(1).Name = "薪資表" : xlBook.Activate() : xlSheet.Activate()

  217. 

  218.         xlSheet.Cells(1, 1) = "黃柏翰皮膚科診所薪資明細表" : xlSheet.Cells(2, 1) = CInt(年份_cb.Text) - 1911 & "年" & 月份_cb.Text & "月 - 全月"

  219.         xlSheet.Cells(3, 1) = "項" : xlSheet.Cells(3, 2) = "姓名" : xlSheet.Cells(3, 3) = "工時" : xlSheet.Cells(3, 4) = "基本月薪" : xlSheet.Cells(3, 5) = "基本時薪"

  220.         xlSheet.Cells(3, 6) = "主管加級" : xlSheet.Cells(3, 7) = "職務加級" : xlSheet.Cells(3, 8) = "特休" : xlSheet.Cells(3, 9) = "婚假" : xlSheet.Cells(3, 10) = "喪假"

  221.         xlSheet.Cells(3, 11) = "其他說明" : xlSheet.Cells(3, 12) = "其他" : xlSheet.Cells(3, 13) = "未到/離職" : xlSheet.Cells(3, 14) = "本薪" : xlSheet.Cells(3, 15) = "一段加班"

  222.         xlSheet.Cells(3, 16) = "二段加班" : xlSheet.Cells(3, 17) = "國定假日" : xlSheet.Cells(3, 18) = "加班費" : xlSheet.Cells(3, 19) = "應付小計" : xlSheet.Cells(3, 20) = "勞保費"

  223.         xlSheet.Cells(3, 21) = "健保費" : xlSheet.Cells(3, 22) = "事假" : xlSheet.Cells(3, 23) = "病假" : xlSheet.Cells(3, 24) = "其他說明" : xlSheet.Cells(3, 25) = "其他"

  224.         xlSheet.Cells(3, 26) = "減項小計" : xlSheet.Cells(3, 27) = "實付小計"

  225.         For i As Integer = 0 To 月清單_dgv.Rows.Count - 1

  226.             xlSheet.Cells(4 + i, 1) = 月清單_dgv("項", i).Value.ToString : xlSheet.Cells(4 + i, 2) = 月清單_dgv("姓名", i).Value.ToString

  227.             xlSheet.Cells(4 + i, 3) = 月清單_dgv("實上工時", i).Value.ToString : xlSheet.Cells(4 + i, 4) = 月清單_dgv("基本月薪", i).Value.ToString

  228.             xlSheet.Cells(4 + i, 5) = 月清單_dgv("基本時薪", i).Value.ToString : xlSheet.Cells(4 + i, 6) = 月清單_dgv("主管加級", i).Value.ToString

  229.             xlSheet.Cells(4 + i, 7) = 月清單_dgv("職務加級", i).Value.ToString : xlSheet.Cells(4 + i, 8) = 月清單_dgv("特休補錢", i).Value.ToString

  230.             xlSheet.Cells(4 + i, 9) = 月清單_dgv("婚假", i).Value.ToString : xlSheet.Cells(4 + i, 10) = 月清單_dgv("喪假", i).Value.ToString

  231.             xlSheet.Cells(4 + i, 11) = 月清單_dgv("其他說明", i).Value.ToString : xlSheet.Cells(4 + i, 12) = 月清單_dgv("其他", i).Value.ToString

  232.             xlSheet.Cells(4 + i, 13) = 月清單_dgv("未到離職", i).Value.ToString : xlSheet.Cells(4 + i, 14) = 月清單_dgv("本薪", i).Value.ToString

  233.             If 彈性工時_cb.Checked = True Then

  234.                 xlSheet.Cells(4 + i, 15) = 月清單_dgv("一段加班2", i).Value.ToString : xlSheet.Cells(4 + i, 16) = 月清單_dgv("二段加班2", i).Value.ToString

  235.                 xlSheet.Cells(4 + i, 17) = 月清單_dgv("國定假日2", i).Value.ToString : xlSheet.Cells(4 + i, 18) = 月清單_dgv("加班費2", i).Value.ToString

  236.                 xlSheet.Cells(4 + i, 19) = 月清單_dgv("應付小計2", i).Value.ToString : xlSheet.Cells(4 + i, 27) = 月清單_dgv("實付小計2", i).Value.ToString

  237.             Else

  238.                 xlSheet.Cells(4 + i, 15) = 月清單_dgv("一段加班1", i).Value.ToString : xlSheet.Cells(4 + i, 16) = 月清單_dgv("二段加班1", i).Value.ToString

  239.                 xlSheet.Cells(4 + i, 17) = 月清單_dgv("國定假日1", i).Value.ToString : xlSheet.Cells(4 + i, 18) = 月清單_dgv("加班費1", i).Value.ToString

  240.                 xlSheet.Cells(4 + i, 19) = 月清單_dgv("應付小計1", i).Value.ToString : xlSheet.Cells(4 + i, 27) = 月清單_dgv("實付小計1", i).Value.ToString

  241.             End If

  242.             xlSheet.Cells(4 + i, 20) = 月清單_dgv("勞保費", i).Value.ToString : xlSheet.Cells(4 + i, 21) = 月清單_dgv("健保費", i).Value.ToString

  243.             xlSheet.Cells(4 + i, 22) = 月清單_dgv("事假", i).Value.ToString : xlSheet.Cells(4 + i, 23) = 月清單_dgv("病假", i).Value.ToString

  244.             xlSheet.Cells(4 + i, 24) = 月清單_dgv("其他說明", i).Value.ToString : xlSheet.Cells(4 + i, 25) = 月清單_dgv("其他", i).Value.ToString

  245.             xlSheet.Cells(4 + i, 26) = 月清單_dgv("減項小計1", i).Value.ToString

  246.         Next : 位置1 = 4 + 月清單_dgv.Rows.Count - 1

  247. 

  248.         xlSheet.Cells(5 + 月清單_dgv.Rows.Count, 6) = 合計_dgv("主管加級", 0).Value.ToString : xlSheet.Cells(5 + 月清單_dgv.Rows.Count, 9) = 合計_dgv("婚假", 0).Value.ToString

  249.         xlSheet.Cells(5 + 月清單_dgv.Rows.Count, 7) = 合計_dgv("職務加級", 0).Value.ToString : xlSheet.Cells(5 + 月清單_dgv.Rows.Count, 26) = 合計_dgv("減項小計1", 0).Value.ToString

  250.         xlSheet.Cells(5 + 月清單_dgv.Rows.Count, 8) = 合計_dgv("特休補錢", 0).Value.ToString : xlSheet.Cells(5 + 月清單_dgv.Rows.Count, 10) = 合計_dgv("喪假", 0).Value.ToString

  251.         xlSheet.Cells(5 + 月清單_dgv.Rows.Count, 11) = 合計_dgv("其他說明", 0).Value.ToString : xlSheet.Cells(5 + 月清單_dgv.Rows.Count, 12) = 合計_dgv("其他", 0).Value.ToString

  252.         xlSheet.Cells(5 + 月清單_dgv.Rows.Count, 13) = 合計_dgv("未到離職", 0).Value.ToString : xlSheet.Cells(5 + 月清單_dgv.Rows.Count, 14) = 合計_dgv("本薪", 0).Value.ToString

  253.         xlSheet.Cells(5 + 月清單_dgv.Rows.Count, 20) = 合計_dgv("勞保費", 0).Value.ToString : xlSheet.Cells(5 + 月清單_dgv.Rows.Count, 21) = 合計_dgv("健保費", 0).Value.ToString

  254.         xlSheet.Cells(5 + 月清單_dgv.Rows.Count, 22) = 合計_dgv("事假", 0).Value.ToString : xlSheet.Cells(5 + 月清單_dgv.Rows.Count, 23) = 合計_dgv("病假", 0).Value.ToString

  255.         xlSheet.Cells(5 + 月清單_dgv.Rows.Count, 25) = 合計_dgv("其他", 0).Value.ToString

  256.         If 彈性工時_cb.Checked = True Then

  257.             xlSheet.Cells(5 + 月清單_dgv.Rows.Count, 18) = 合計_dgv("加班費2", 0).Value.ToString : xlSheet.Cells(5 + 月清單_dgv.Rows.Count, 19) = 合計_dgv("應付小計2", 0).Value.ToString

  258.             xlSheet.Cells(5 + 月清單_dgv.Rows.Count, 27) = 合計_dgv("實付小計2", 0).Value.ToString

  259.         Else

  260.             xlSheet.Cells(5 + 月清單_dgv.Rows.Count, 18) = 合計_dgv("加班費1", 0).Value.ToString : xlSheet.Cells(5 + 月清單_dgv.Rows.Count, 19) = 合計_dgv("應付小計1", 0).Value.ToString

  261.             xlSheet.Cells(5 + 月清單_dgv.Rows.Count, 27) = 合計_dgv("實付小計1", 0).Value.ToString

  262.         End If : AA(xlApp, xlSheet)

  263. 

  264.         xlSheet.PageSetup.PrintArea = "" : xlApp.Cells.Select() : xlSheet.Range("B1").Select() : xlApp.Application.WindowState = xlMinimized : MsgBox("列印完成")

  265.     End Sub

  266.     Private Shared Function NewMethod(xlBook As Workbook) As Worksheet

  267.         Return CType(xlBook.Worksheets.Add, Worksheet)

  268.     End Function

  269.     Private Sub AA(ByVal myExcel As Microsoft.Office.Interop.Excel.Application, ByVal xlSheet As Microsoft.Office.Interop.Excel.Worksheet)

  270.         xlSheet.Cells.Select()

  271.         With myExcel.Selection.Font : .Name = "微軟正黑體" : .Size = 10 : .Strikethrough = False : .Superscript = False : .Subscript = False : .OutlineFont = False

  272.             .Shadow = False : .Underline = xlUnderlineStyleNone : .ThemeColor = xlThemeColorLight1 : .TintAndShade = 0 : .ThemeFont = xlThemeFontNone : End With

  273.         xlSheet.Columns("A:A").Select : myExcel.Selection.ColumnWidth = 3

  274.         xlSheet.Columns("C:AA").Select : myExcel.Selection.Style = "Comma" : myExcel.Selection.NumberFormatLocal = "_-* #,##0_-;-* #,##0_-;_-* ""-""??_-;_-@_-"

  275.         xlSheet.Columns("B:AA").Select : myExcel.Columns("B:AA").EntireColumn.AutoFit

  276.         xlSheet.Rows("3:" & 位置1).Select : myExcel.Selection.RowHeight = 25

  277.         myExcel.Range("A3:AA" & 位置1).Select()

  278.         myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone

  279.         With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With

  280.         With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With

  281.         With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With

  282.         With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With

  283.         With myExcel.Selection.Borders(xlInsideVertical) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With

  284.         With myExcel.Selection.Borders(xlInsideHorizontal) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With

  285.         xlSheet.Rows(位置1 + 1 & ":" & 位置1 + 1).Select : myExcel.Selection.RowHeight = 5

  286.         myExcel.ActiveWindow.DisplayGridlines = False

  287.         xlSheet.Range("A3:A" & 位置1).Select()

  288.         With myExcel.Selection : .HorizontalAlignment = xlCenter : .VerticalAlignment = xlCenter : .WrapText = False : .Orientation = 0 : .AddIndent = False

  289.             .IndentLevel = 0 : .ShrinkToFit = False : .ReadingOrder = xlContext : .MergeCells = False : End With

  290.         xlSheet.Range("A3:AA3").Select() : myExcel.Application.WindowState = xlNormal

  291.         xlSheet.Range("C3:D" & 位置1).Select()

  292.         With myExcel.Selection.Interior : .Pattern = xlSolid : .PatternColorIndex = xlAutomatic : .ThemeColor = xlThemeColorAccent6 : .TintAndShade = 0.799981688894314

  293.             .PatternTintAndShade = 0 : End With

  294.         xlSheet.Range("F3:N3").Select()

  295.         With myExcel.Selection.Interior : .Pattern = xlSolid : .PatternColorIndex = xlAutomatic : .ThemeColor = xlThemeColorAccent1 : .TintAndShade = 0.799981688894314

  296.             .PatternTintAndShade = 0 : End With

  297.         xlSheet.Range("N4:N" & 位置1).Select()

  298.         With myExcel.Selection.Interior : .Pattern = xlSolid : .PatternColorIndex = xlAutomatic : .ThemeColor = xlThemeColorAccent1 : .TintAndShade = 0.799981688894314

  299.             .PatternTintAndShade = 0 : End With

  300.         xlSheet.Range("O3:S3").Select()

  301.         With myExcel.Selection.Interior : .Pattern = xlSolid : .PatternColorIndex = xlAutomatic : .ThemeColor = xlThemeColorAccent2 : .TintAndShade = 0.799981688894314

  302.             .PatternTintAndShade = 0 : End With

  303.         xlSheet.Range("S4:S" & 位置1).Select()

  304.         With myExcel.Selection.Interior : .Pattern = xlSolid : .PatternColorIndex = xlAutomatic : .ThemeColor = xlThemeColorAccent2 : .TintAndShade = 0.799981688894314

  305.             .PatternTintAndShade = 0 : End With

  306.         xlSheet.Range("T3:U" & 位置1).Select()

  307.         With myExcel.Selection.Interior : .Pattern = xlSolid : .PatternColorIndex = xlAutomatic : .ThemeColor = xlThemeColorDark2 : .TintAndShade = -0.0999786370433668

  308.             .PatternTintAndShade = 0 : End With

  309.         xlSheet.Range("V3:Z3").Select()

  310.         With myExcel.Selection.Interior : .Pattern = xlSolid : .PatternColorIndex = xlAutomatic : .ThemeColor = xlThemeColorAccent4 : .TintAndShade = 0.799981688894314

  311.             .PatternTintAndShade = 0 : End With

  312.         xlSheet.Range("Z4:Z" & 位置1).Select()

  313.         With myExcel.Selection.Interior : .Pattern = xlSolid : .PatternColorIndex = xlAutomatic : .ThemeColor = xlThemeColorAccent4 : .TintAndShade = 0.799981688894314

  314.             .PatternTintAndShade = 0 : End With

  315.         xlSheet.Range("AA3:AA" & 位置1 + 2).Select()

  316.         With myExcel.Selection.Font : .Color = -16776961 : .TintAndShade = 0 : End With

  317.         xlSheet.Range("A3:AA3").Select() : myExcel.Selection.Font.Bold = True

  318.         With myExcel.Selection : .HorizontalAlignment = xlCenter : .VerticalAlignment = xlCenter : .WrapText = False : .Orientation = 0 : .AddIndent = False : .IndentLevel = 0

  319.             .ShrinkToFit = False : .ReadingOrder = xlContext : .MergeCells = False : End With

  320.         xlSheet.Range("A2:AA2").Select()

  321.         With myExcel.Selection : .HorizontalAlignment = xlCenter : .VerticalAlignment = xlCenter : .WrapText = False : .Orientation = 0 : .AddIndent = False : .IndentLevel = 0

  322.             .ShrinkToFit = False : .ReadingOrder = xlContext : .MergeCells = False : End With : myExcel.Selection.Merge

  323.         With myExcel.Selection.Font : .Name = "微軟正黑體" : .Size = 14 : .Strikethrough = False : .Superscript = False : .Subscript = False : .OutlineFont = False

  324.             .Shadow = False : .Underline = xlUnderlineStyleNone : .ThemeColor = xlThemeColorLight1 : .TintAndShade = 0 : .ThemeFont = xlThemeFontNone : End With

  325.         xlSheet.Range("A1:AA1").Select()

  326.         With myExcel.Selection : .HorizontalAlignment = xlCenter : .VerticalAlignment = xlCenter : .WrapText = False : .Orientation = 0 : .AddIndent = False : .IndentLevel = 0

  327.             .ShrinkToFit = False : .ReadingOrder = xlContext : .MergeCells = False : End With : myExcel.Selection.Merge

  328.         With myExcel.Selection.Font : .Name = "微軟正黑體" : .Size = 18 : .Strikethrough = False : .Superscript = False : .Subscript = False : .OutlineFont = False

  329.             .Shadow = False : .Underline = xlUnderlineStyleNone : .ThemeColor = xlThemeColorLight1 : .TintAndShade = 0 : .ThemeFont = xlThemeFontNone : End With

  330.         myExcel.Selection.Font.Bold = True

  331.         xlSheet.Range("A3:AA3").Select()

  332.         myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone

  333.         With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With

  334.         With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With

  335.         With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With

  336.         With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With

  337.         With myExcel.Selection.Borders(xlInsideVertical) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With

  338.         myExcel.Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

  339.         xlSheet.Range("A4:AA" & 位置1 + 2).Select()

  340.         myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone

  341.         With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With

  342.         With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With

  343.         With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With

  344.         With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With

  345.         With myExcel.Selection.Borders(xlInsideVertical) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With

  346.     End Sub

  347. End Class