Dim dlg As new OpenFileDialog
Dim Dateda As Date
Dim Datexiao As Date
Dim Datezhongjian As Date
Dateda= "1900-01-01"
Datexiao=Dateda
Datezhongjian=Dateda
dlg.MultiSelect = True
If dlg.ShowDialog = DialogResult.OK Then
For Each f As String In dlg.FileNames
Dim Book As New XLS.Book(f)
Dim Sheet As XLS.Sheet = Book.Sheets(0)
Dim dic As new Dictionary(Of String, Integer)
For i As Integer = 0 To sheet.Cols.Count - 1
If sheet(0,i).Text <> Nothing
dic.Add(sheet(0,i).Text,i)
End If
Next
'以下代码是在execl表中找到最小日期和最大日期
Dim dic3 As new Dictionary(Of DataRow,Integer)
For n1 As Integer = 1 To Sheet.Rows.Count -1
If sheet(n1, dic("入住时间")).Text > "" Then
Dateda=(sheet(n1,dic("入住时间")).Text)
If Datexiao="1900-01-01" Then
'为了获得最小日期和最大日期
Datexiao=Dateda '初次设置日期
Datezhongjian=Dateda
Else
If Datexiao<=Dateda Then
If Dateda >= Datezhongjian Then
Datezhongjian=Dateda '如果dateda总是大于datezhongjian, 则让datezhongjian=dateda
Else
'出现dateda小于datezhognjian,则不变datezhongjian
End If
Else
Datexiao=Dateda '发现更小的日期了
End If
End If
End If
Next
messagebox.show("最小的日期是" & Datexiao & "最大的日期是" & Datezhongjian & ",系统会把这段日期的上次导入的客户资料删除.然后重新导入选择的番茄EXECL数据" )
DataTables("番茄导入客户资料").DeleteFor("[入住时间] <= #" & Datezhongjian & "# and [入住时间] >= #" & Datexiao & "#")
Dim dic2 As new Dictionary(Of DataRow,Integer)
For n As Integer = 1 To Sheet.Rows.Count -1
If sheet(n, dic("入住时间")).Text > "" Then
Dim filter As String = "入住时间 = #" & sheet(n, dic("入住时间")).Text & "# and 姓名 = '" & sheet(n, dic("姓名")).Text & "' and 房间号 = '" & sheet(n, dic("房间号")).Text & "'"
Dim dr As DataRow = DataTables("番茄导入客户资料").find(filter)
If dr Is Nothing Then dr = DataTables("番茄导入客户资料").AddNew
dic2.Add(dr,n)
End If
Next
For Each dr As DataRow In dic2.Keys
For Each c As String In dic.Keys
If DataTables("番茄导入客户资料").datacols.Contains(c) Then
dr(c) = sheet(dic2(dr), dic(c)).Text
End If
Next
Next
Next
End If
上述代码中红色部分应该有更简便的代码，我用的是笨方法。
既然把日期范围内的数据删掉了，那么红色下面的判断代码其实就没必要了？
[此贴子已经被作者于2015/8/31 15:22:11编辑过]
加好友 
发短信
Post By：2015/8/31 14:05:00 [只看该作者]
