Dim dlg As New OpenFileDialog
dlg.MultiSelect = True
If dlg.ShowDialog = DialogResult.OK Then
OutPut.Show("你选择了" & dlg.FileNames.Length & "个文件, 分别是:")
DataTables("明细表").ResumeRedraw
DataTables("明细表").StopRedraw
For Each fl As String In dlg.FileNames
Dim Book As New XLS.Book(fl)
'注意以下数组中列名称的顺序,必须和Excel表中的列顺序一致
Dim nms() As String ={"卡号","持卡人","交易时间","交易类型","金额","商品", "数量","单价","奖励积分","余额","地点"}
'注意下面的循环变量从1开始,而不是从0开始,因为Excel表的第一行是标题
Dim Sheet As XLS.Sheet = Book.Sheets(0)
For n As Integer = 4 To Sheet.Rows.Count -1
Dim kh As String = sheet(n,0).text
If kh <> "小计" AndAlso kh <> "总计" Then
Dim sj As String = sheet(n,2).Text
Dim d As Date = Nothing
Date.TryParse(sj, d)
Dim fdr As DataRow
If d <> Nothing Then
fdr = DataTables("明细表").Find("交易时间 > #" & d.AddMinutes(-1) & "# and 交易时间 < #" & d.AddMinutes(1) & "#")
End If
If d <> Nothing Then
fdr = DataTables("明细表").Find("交易时间 = #" & d & "#")
Else
Continue For
End If
If fdr Is Nothing Then '如果不存在同编号的订单
Dim r As Row = Tables("明细表").AddNew()
For m As Integer = 0 To nms.Length - 1
If r.Table.Cols(nms(m)).IsDate Then
Date.TryParse(Sheet(n,m).Value, d)
r(nms(m)) = d
ElseIf r.Table.Cols(nms(m)).IsNumeric Then
r(nms(m)) = val(Sheet(n,m).Value)
Else
r(nms(m)) = Sheet(n,m).Value
End If
Next
End If
End If
Next
Next
Dim lst1 As List(Of String) = DataTables("销售卡信息").GetValues("持卡人","所属部门='采购'")
For Each r As Row In Tables("明细表").rows
r("周六周日") = Nothing
Dim sw As Integer
If lst1.Contains(r("持卡人")) Then
sw = 1
Else
sw = 6
End If
If cdate(r("交易时间")).DayOfWeek = 0 OrElse cdate(r("交易时间")).DayOfWeek = sw Then
r("周六周日") = format(cdate(r("交易时间")), "dddd")
For Each dr As DataRow In DataTables("调休表").Select("")
For i As Integer = 1 To 4
Dim cname As String = "调为工作日" & i
If dr(cname) <> Nothing AndAlso cdate(r("交易时间")).Date = dr(cname) Then
r("周六周日") = Nothing
goto label2
End If
Next
Next
label2:
Else
For Each dr As DataRow In DataTables("调休表").Select("")
For i As Integer = 1 To 7
Dim cname As String = "调为休假日" & i
If dr(cname) <> Nothing AndAlso cdate(r("交易时间")).Date = dr(cname) Then
r("周六周日") = dr("调休原因")
goto label1
End If
Next
Next
label1:
End If
Next
For Each kh As String In DataTables("明细表").GetValues("卡号")
Dim drs = DataTables("明细表").Select("卡号='" & kh & "' and 交易类型='购买'", "交易时间")
For i As Integer = 1 To drs.count-1
Dim sp As TimeSpan = drs(i)("交易时间") - drs(i-1)("交易时间")
If sp.TotalHours <= 1 Then
drs(i)("一小时内") = True
drs(i)("三小时内") = Nothing
drs(i)("一天内") = Nothing
ElseIf sp.TotalHours <= 3 Then
drs(i)("一小时内") = Nothing
drs(i)("三小时内") = True
drs(i)("一天内") = Nothing
ElseIf sp.TotalHours <= 24 Then
drs(i)("一小时内") = Nothing
drs(i)("三小时内") = Nothing
drs(i)("一天内") = True
End If
Next
Next
DataTables("明细表").ResumeRedraw
End If
加好友 
发短信
Post By：2019/9/22 20:26:00 [只看该作者]
