Imports Excel = Microsoft.Office.Interop.Excel
Public Class 匯入EXCEL
    Dim app As Excel.Application
    Dim book As Excel.Workbook
    Dim sheet As Excel.Worksheet
    Dim range As Excel.Range
    Dim dtable As DataTable = New DataTable()
    Dim OpenFileDialog As New OpenFileDialog
    Dim ii As Integer = 1 : Dim 全選_index As Integer = 1 : Dim 取消全選_index As Integer = 1
    Dim 首次開啟 As Boolean = False
    Dim EDR As Integer = 0 : Dim ESTR As String : Dim EDR1 As Integer = 0

    Private Sub 確認選擇月份()
        紀錄Sheet年月_LBox.Items.Clear() : 紀錄選擇月份_LBox.Items.Clear()
        For Each E_node As TreeNode In 選擇_TreeView.Nodes
            If E_node.Checked = True Then
                紀錄Sheet年月_LBox.Items.Add(E_node.Text)
                紀錄選擇月份_LBox.Items.Add(ii)
            End If
            If E_node.Nodes.Count > 0 Then
                SelCheckNode(E_node)
            End If
            ii = ii + 1
        Next
        ii = 1
    End Sub
    Private Sub SelCheckNode(ByVal Val_node As TreeNode)
        For Each E_node As TreeNode In Val_node.Nodes
            If E_node.Checked = True Then
                紀錄Sheet年月_LBox.Items.Add(E_node.Text)
                紀錄選擇月份_LBox.Items.Add(ii)
            End If
            If E_node.Nodes.Count > 0 Then
                SelCheckNode(E_node)
            End If
            ii = ii + 1
        Next
    End Sub
    Private Sub 匯入EXCEL_人事資料讀取(ByVal NoColumns As Integer)
        ConnOpen() : SQL1 = "SELECT 流水號, 身分證, 舊編, 薪資帳號 FROM 人事資料表 WHERE (姓名 = N'" & Me.薪資匯入EXCEL_dgv(7, NoColumns).Value.ToString & "')"
        CmdSet_For_dr()
        dr.Read()
        If Me.薪資匯入EXCEL_dgv(7, NoColumns).Value.ToString <> Nothing And dr.HasRows = False Then
            人事流水號_tb.Text = ""
            MessageBox.Show(Me.薪資匯入EXCEL_dgv(7, NoColumns).Value.ToString & " ,此人員不在人事資料中 !!!" + vbCrLf + "請追加人事資料", "重要訊息", MessageBoxButtons.OK, MessageBoxIcon.Warning)
        ElseIf dr.HasRows = True Then
            人事流水號_tb.Text = dr("流水號").ToString()
        Else
            人事流水號_tb.Text = ""
        End If
        If dr.HasRows = False Then
            身分證_tb.Text = ""
        Else
            身分證_tb.Text = dr("身分證").ToString()
        End If
        If dr.HasRows = False Then
            薪資帳號_tb.Text = ""
        Else
            薪資帳號_tb.Text = dr("薪資帳號").ToString()
        End If
        conn.Close()
    End Sub
    Private Sub 匯入_SQL_薪資轉帳表()
        Dim 薪資轉帳資料存在判斷 As Boolean
        匯入Excel_PBar.Maximum = 薪資匯入EXCEL_dgv.Rows.Count - 1
        For k As Integer = 0 To 薪資匯入EXCEL_dgv.Rows.Count - 1 '--------------------自動取得DGV 欄位數
            匯入EXCEL_人事資料讀取(k) ''--------------------由人事資料表讀出 (人事流水號, 身分證, 舊編, 薪資帳號)
            薪資轉帳資料存在判斷 = 薪資轉帳表_資料重複判斷(k)
            If 薪資轉帳資料存在判斷 = True Then
                SQL_薪資轉帳表_流水號_最後一筆資料() '--------------------自動取得新流水號
                If dr.Read() Then : EDR = Double.Parse(Strings.Right(dr("流水號").ToString, 9)) : Else : EDR = 0 : End If
                conn.Close() : EDR += 1
                If EDR < 10 Then : ESTR = "M" & "00000000" & EDR
                ElseIf EDR > 9 And EDR < 100 Then : ESTR = "M" & "0000000" & EDR
                ElseIf EDR > 99 And EDR < 1000 Then : ESTR = "M" & "000000" & EDR
                ElseIf EDR > 999 And EDR < 10000 Then : ESTR = "M" & "00000" & EDR
                ElseIf EDR > 9999 And EDR < 100000 Then : ESTR = "M" & "0000" & EDR
                ElseIf EDR > 99999 And EDR < 100000 Then : ESTR = "M" & "000" & EDR
                ElseIf EDR > 999999 And EDR < 1000000 Then : ESTR = "M" & "00" & EDR
                ElseIf EDR > 9999999 And EDR < 10000000 Then : ESTR = "M" & "0" & EDR
                ElseIf EDR > 99999999 Then : ESTR = "M" & EDR
                End If : 流水號_tb.Text = ESTR
                SQL_薪資轉帳表_匯入EXCEL_新增(k) '--------------------薪資轉帳表中寫入一筆資料
            Else
                MessageBox.Show(薪資匯入EXCEL_dgv(7, k).Value.ToString & ",此人員" & 薪資匯入EXCEL_dgv(8, k).Value.ToString & "薪資資料已存在", "匯入訊息", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End If
            If 匯入Excel_PBar.Value < 匯入Excel_PBar.Maximum Then : 匯入Excel_PBar.Value += 匯入Excel_PBar.Step : End If
        Next
    End Sub
    Private Function 薪資轉帳表_資料重複判斷(ByVal NoColumns As Integer) As Boolean
        Dim Val_return As Boolean
        ConnOpen()
        SQL1 = "SELECT      薪資轉帳表.流水號, 人事資料表.姓名, 薪資轉帳表.結算日期, 薪資轉帳表.人事資料流水號
                FROM        薪資轉帳表  LEFT OUTER JOIN
                            人事資料表 ON 薪資轉帳表.人事資料流水號 = 人事資料表.流水號
                WHERE      (薪資轉帳表.結算日期 NOT LIKE '')  
                           AND (薪資轉帳表.結算日期 LIKE '" & 薪資匯入EXCEL_dgv(8, NoColumns).Value.ToString & "%" & "')
                           AND (薪資轉帳表.人事資料流水號 LIKE '" & 人事流水號_tb.Text & "')
               ORDER BY    薪資轉帳表.流水號"
        'AND (薪資轉帳表.結算日期 LIKE '" & Format(匯入EXCEL_dgv(8, NoColumns + 1).Value.ToString, "yyyy/MM/dd") & "%" & "')
        CmdSet_For_dr()
        dr.Read()
        If dr.HasRows = False Then
            Val_return = True
        ElseIf dr.HasRows = True Then
            Val_return = False
        End If
        conn.Close()
        Return Val_return
    End Function

    Private Sub 匯入EXCEL_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        匯入EXCEL_bt.Enabled = True
        薪資匯入EXCEL_dgv.DataSource = Nothing
        薪資匯入EXCEL_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
        薪資匯入EXCEL_dgv.ColumnHeadersHeight = 40
        薪資匯入EXCEL_dgv.AllowUserToAddRows = False
        匯入Excel_PBar.Maximum = 100 : 匯入Excel_PBar.Minimum = 0 : 匯入Excel_PBar.Value = 0

        OpenFileDialog1.FileName = ""
        OpenFileDialog1.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.Desktop

        If (OpenFileDialog1.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then
            Dim FileName As String = OpenFileDialog1.FileName

            '在程式開始執行路徑取得 .xlsx
            app = New Excel.Application()
            app.DisplayAlerts = True
            app.Visible = False
            book = app.Workbooks.Open(FileName)
            sheet = book.Sheets(2)
            range = sheet.Cells(20, 10)
            sheet.Activate()

            Dim sh() As String
            Dim i As Integer
            i = -1
            Dim Val_sheet As Excel.Worksheet
            選擇_TreeView.Nodes.Clear()
            For Each Val_sheet In book.Worksheets
                '在這您還可以加入您的需要,例如s1.name的名子有那些字再計算,本例是計算所有的
                i = i + 1
                ReDim Preserve sh(i)
                sh(i) = Val_sheet.Name
                選擇_TreeView.Nodes.Add(sh(i))
            Next
            NAR(sheet) '關閉 Excel 工作表  
            book.Close(False)
            NAR(book) '關閉 Excel 工作簿  
            app.Quit() '關閉 Excel 
            NAR(app) ''關閉 Excel 應用程式 
        End If
    End Sub
    Private Sub 匯入EXCEL_Closed(sender As Object, e As EventArgs) Handles MyBase.Closed
        薪資匯入EXCEL_dgv.DataSource = Nothing : dtable.Clear()
    End Sub
    Private Sub NAR(ByVal o As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
        Catch
        Finally
            o = Nothing
        End Try
    End Sub
    Private Sub 匯入EXCEL_bt_Click(sender As Object, e As EventArgs) Handles 匯入EXCEL_bt.Click
        確認選擇月份()
        dtable.Clear()
        薪資匯入EXCEL_dgv.Columns.Clear()
        If 首次開啟 = False Then
            dtable.Clear()
            薪資匯入EXCEL_dgv.Columns.Clear()
            dtable.Columns.Add("編號", System.Type.GetType("System.Int32"))
            dtable.Columns.Add("帳號", System.Type.GetType("System.String"))
            dtable.Columns.Add("身份證字號", System.Type.GetType("System.String"))
            dtable.Columns.Add("獎金", System.Type.GetType("System.Int32"))
            dtable.Columns.Add("薪資", System.Type.GetType("System.Int32"))
            dtable.Columns.Add("領現", System.Type.GetType("System.Int32"))
            dtable.Columns.Add("合計", System.Type.GetType("System.Int32"))
            dtable.Columns.Add("姓名", System.Type.GetType("System.String"))
            dtable.Columns.Add("結算日期", System.Type.GetType("System.String"))
            dtable.Columns.Add("給付方式", System.Type.GetType("System.String"))
        End If
        '   在程式開始執行路徑取得 .xlsx
        Dim FileName As String = OpenFileDialog1.FileName
        app = New Excel.Application()
        app = CreateObject("Excel.Application")
        app.DisplayAlerts = False
        app.Visible = True
        book = app.Workbooks.Open(FileName)

        Dim dtrow As DataRow
        Dim j As Integer = 0
        For Each Items As Integer In 紀錄選擇月份_LBox.Items
            sheet = book.Sheets(Items)
            sheet.Activate()
            Dim rowNo As Int32 = 4
            Do
                If sheet.Cells(rowNo, 1).Value = Nothing Then
                    Do
                        If sheet.Cells(rowNo + 1, 7).Value = Nothing Then
                            Exit Do
                        Else
                            dtrow = dtable.NewRow()
                            dtrow("姓名") = sheet.Cells(rowNo + 1, 7).Value.ToString
                            If sheet.Cells(rowNo + 1, 6).Value <> Nothing Then
                                dtrow("領現") = sheet.Cells(rowNo + 1, 6).Value
                            Else
                                dtrow("領現") = 0
                            End If
                            Dim str_a As String = (191100 + 紀錄Sheet年月_LBox.Items(j)).ToString
                            dtrow("結算日期") = str_a.Substring(0, 4) & "/" & str_a.Substring(4, 2) & "/25"
                            dtrow("給付方式") = "領現"
                            dtable.Rows.Add(dtrow)
                            rowNo = rowNo + 1
                        End If
                    Loop
                    Exit Do
                Else
                    dtrow = dtable.NewRow()
                    dtrow("編號") = sheet.Cells(rowNo, 1).Value
                    dtrow("帳號") = sheet.Cells(rowNo, 2).Value.ToString
                    dtrow("身份證字號") = sheet.Cells(rowNo, 3).Value.ToString
                    dtrow("姓名") = sheet.Cells(rowNo, 7).Value.ToString
                    If sheet.Cells(rowNo, 4).Value <> Nothing Then
                        dtrow("獎金") = sheet.Cells(rowNo, 4).Value
                    Else
                        dtrow("獎金") = 0
                    End If
                    If sheet.Cells(rowNo, 5).Value <> Nothing Then
                        dtrow("薪資") = sheet.Cells(rowNo, 5).Value
                    Else
                        dtrow("薪資") = 0
                    End If
                    If sheet.Cells(rowNo, 6).Value <> Nothing Then
                        dtrow("合計") = sheet.Cells(rowNo, 6).Value
                    Else
                        dtrow("合計") = 0
                    End If

                    Dim str_a As String = (191100 + 紀錄Sheet年月_LBox.Items(j)).ToString
                    dtrow("結算日期") = str_a.Substring(0, 4) & "/" & str_a.Substring(4, 2) & "/25"
                    dtrow("給付方式") = "帳號匯款"
                    dtable.Rows.Add(dtrow)
                    rowNo = rowNo + 1
                End If
            Loop
            j = j + 1
            dtable.AcceptChanges()
        Next
        首次開啟 = True
        薪資匯入EXCEL_dgv.DataSource = dtable
        匯入_SQL_薪資轉帳表()
        NAR(sheet) '關閉 Excel 工作表  
        book.Close(False)
        NAR(book) '關閉 Excel 工作簿  
        app.Quit() '關閉 Excel 
        NAR(app) ''關閉 Excel 應用程式 
        MessageBox.Show("匯入完成 !!!", "匯入訊息", MessageBoxButtons.OK, MessageBoxIcon.Information)
        匯入EXCEL_bt.Enabled = False
        Me.Close()
    End Sub
    Private Sub 全選_bt_Click(sender As Object, e As EventArgs) Handles 全選_bt.Click
        For Each E_node As TreeNode In 選擇_TreeView.Nodes
            E_node.Checked = True
            If E_node.Nodes.Count > 0 Then
                SelAllCheck(E_node)
            End If
            全選_index = 全選_index + 1
        Next
    End Sub
    Private Sub SelAllCheck(ByVal Val_node As TreeNode)
        For Each E_node As TreeNode In Val_node.Nodes
            E_node.Checked = True
            If E_node.Nodes.Count > 0 Then
                SelAllCheck(E_node)
            End If
            全選_index = 全選_index + 1
        Next
    End Sub
    Private Sub 取消全選_bt_Click(sender As Object, e As EventArgs) Handles 取消全選_bt.Click
        For Each E_node As TreeNode In 選擇_TreeView.Nodes
            E_node.Checked = False
            If E_node.Nodes.Count > 0 Then
                CalAllCheck(E_node)
            End If
            取消全選_index = 取消全選_index + 1
        Next
    End Sub
    Private Sub CalAllCheck(ByVal Val_node As TreeNode)
        For Each E_node As TreeNode In Val_node.Nodes
            E_node.Checked = False
            If E_node.Nodes.Count > 0 Then
                CalAllCheck(E_node)
            End If
            取消全選_index = 取消全選_index + 1
        Next
    End Sub
End Class