'加载Excel数据

    Private Function LoadExcelData(ByVal Path As String)

        txtMsg.Text = "Excel数据导入中..."

        Dim msgError As String = ""

        Dim miss As Object = System.Reflection.Missing.Value

        Dim wss As Excel.Sheets

        Dim ws As Excel.Worksheet = Nothing

        Dim excelApp As Excel.Application = New Excel.Application

        Dim wbs As Excel.Workbooks = excelApp.Workbooks

        Dim wb As Excel.Workbook = wbs.Open(Path, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss)

        Dim sheetName As String

        Dim dr As DataRow '创建行

        dt.Columns.Add("SheetName")

        dt.Columns.Add("P/ONO")

        dt.Columns.Add("Size1")

        dt.Columns.Add("Size2")

        dt.Columns.Add("Size3")

        dt.Columns.Add("Quantity")

        Try

            excelApp.Visible = False

            wss = wbs(1).Worksheets

            For k As Integer = 1 To wss.Count '得到不同的表

                ws = wss.Item(k) '得到不同表

                Dim PONO As String

                Dim rowNum As Integer = ws.UsedRange.Cells.Rows.Count '行数

                Dim colNum As Integer = ws.UsedRange.Cells.Columns.Count '列数

                sheetName = ws.Name '得到表名

                For i As Integer = 1 To rowNum  '循环行

                    Dim Flag As String = ws.Range(ws.Cells(i, 1), ws.Cells(i, 1)).Text.ToString().Trim() 'i=28

                    If Flag = "Vendor P/O No. :" Then

                        PONO = ws.Range(ws.Cells(i, 3), ws.Cells(i, 3)).Text.ToString().Trim() '得到PONO

                    End If

                    '-------------------    ------------------------------------------------

                    If Flag = "Size" Then  '表示找到Size的时候

                        Dim Size1RowValue As String  'Size1列值

                        Dim Size2RowValue As String  'Size2列值

                        Dim Size3RowValue As String  'Size3列值

                        Dim QuantityValue As String 'Quanty数量

                        For j As Integer = 1 To colNum '列数

                            If ws.Range(ws.Cells(i, j + 1), ws.Cells(i, j + 1)).Text.ToString().Trim() <> "" Then

                                Size1RowValue = ws.Range(ws.Cells(i, j + 1), ws.Cells(i, j + 1)).Text.ToString().Trim() 'XS S M L XL EL

                            End If

                            '---------Size2Value----------------------

                            If ws.Range(ws.Cells(i + 1, 1), ws.Cells(i + 1, 1)).Text.ToString().Trim() <> "Quantity" And ws.Range(ws.Cells(i + 1, j + 1), ws.Cells(i + 1, j + 1)).Text.ToString().Trim() <> "" Then

                                Size2RowValue = ws.Range(ws.Cells(i + 1, j + 1), ws.Cells(i + 1, j + 1)).Text.ToString().Trim() '表示Size2的值

                            End If

                            '--------Size3Value-----------------------

                            If ws.Range(ws.Cells(i + 2, 1), ws.Cells(i + 2, 1)).Text.ToString().Trim() <> "Quantity" And ws.Range(ws.Cells(i + 2, j + 1), ws.Cells(i + 2, j + 1)).Text.ToString().Trim() <> "" Then

                                Size3RowValue = ws.Range(ws.Cells(i + 2, j + 1), ws.Cells(i + 2, j + 1)).Text.ToString().Trim() '表示Size3的值

                            End If

                            '-------------------QuantityValue------------

                            For h As Integer = i To rowNum '要找到Quantity

                                If ws.Range(ws.Cells(h, 1), ws.Cells(h, 1)).Text.ToString().Trim() = "Quantity" Then

                                    QuantityValue = ws.Range(ws.Cells(h, j + 1), ws.Cells(h, j + 1)).Text.ToString().Trim() '得到相应的数量

                                    Exit For '找到之后退出

                                End If

                            Next

                            dr = dt.NewRow '创建行

                            If QuantityValue = "" Then

                                QuantityValue = Nothing

                            End If

                            dr("P/ONO") = PONO

                            dr("SheetName") = sheetName 'execl表名

                            dr("Size1") = Size1RowValue '即一行的值

                            dr("Size2") = Size2RowValue '即二行的值

                            dr("Size3") = Size3RowValue '即三行的值

                            dr("Quantity") = QuantityValue '即数量值

                            If Size1RowValue <> Nothing And Size2RowValue <> " " And Size3RowValue <> " " Then

                                dt.Rows.Add(dr)

                            End If

                            Size1RowValue = Nothing

                            Size2RowValue = Nothing

                            Size3RowValue = Nothing

                            QuantityValue = Nothing

                        Next

                    End If

                Next

            Next

            dgvSizeLabel.DataSource = dt

            dgvSizeLabel.DataBind()

            If dt.Rows.Count > 0 Then

                txtMsg.Text = "Excel数据导入成功"

            End If

        Catch ex As Exception

            'Throw ex

            txtMsg.Text = "读取Excel发生错误"

            'dgvSizeLabel.DataSource = ds.Tables("temp")

        End Try

        ''wb.Close(True)

        ''wbs.Close()

        ''excelApp.Workbooks.Close()

        'excelApp.Workbooks.Close()

        'excelApp.Quit()

        'System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)

        'GC.Collect()

        NAR(sheetName)

        wb.Close(False)

        NAR(wbs)

        NAR(wb)

        excelApp.Quit()

        NAR(excelApp)

    End Function