'加载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