主题：execl导入合并的问题

 execl表中有 “入住时间，姓名，房间号，手机“ 等字段。

我需要定期导入到远程的mssql 导入“客户资料“表中。导入的时候，如果有同样的数据行存在，就跳过。 

问题1：数据行并没有唯一的ID字段。  只能依据（判断） 入住时间，姓名，房间号等信息完全一致的数据行跳过。

怎样写代码呢？
貌似能参考以下的帖子代码，下面的代码执行后，有重复数据。
且我看有些地方看不太懂， 哪位老师可否注释以下代码，并且修改成我需要解决的问题。


---------------------------------------------------------------------------------------------------------
看到以前的帖子 http://www.foxtable.com/bbs/dispbbs.asp?BoardID=2&ID=72804&skin=0


参考代码

Dim dlg As new OpenFileDialog
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
        For  n As Integer = 1 To Sheet.Rows.Count -1
            Dim dr As DataRow =  DataTables("表A").AddNew()
            For Each c As String In dic.Keys
                If DataTables("表A").datacols.Contains(c) Then
                    dr(c) = sheet(n, dic(c)).Text
                End If
            Next
        Next
    Next
   
End If

Dim dlg As new OpenFileDialog
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
        For  n As Integer = 1 To Sheet.Rows.Count -1
            Dim filter As String = "入住时间 = #" & sheet(n, dic("入住时间")).Text & "# and 姓名 = '" & sheet(n, dic("姓名")).Text & "' and 房间号 = '" & sheet(n, dic("房间号")).Text & "'"
            Dim dr As DataRow =  DataTables("表A").find(filter)
            If dr Is Nothing Then dr = DataTables("表A").AddNew
            For Each c As String In dic.Keys
                If DataTables("表A").datacols.Contains(c) Then
                    dr(c) = sheet(n, dic(c)).Text
                End If
            Next
        Next
    Next
   
End If

依照上述代码执行后，运行后，有 20-30秒时间，之后出现错误


.NET Framework 版本：2.0.50727.3655
Foxtable 版本：2014.11.11.1
错误所在事件：窗口,浏览主界面,Button79,Click
详细错误信息：
The expression contains invalid date constant '## and 姓名 = '' and 房间号 = '''.


貌似  "入住时间 = #" & sheet(n, dic("入住时间")).Text & "#   导致的空值

请教： sheet(n,dic("入住时间").text 是什么意思？

Dim dlg As new OpenFileDialog
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
        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("表A").find(filter)
                If dr Is Nothing Then dr = DataTables("表A").AddNew
                For Each c As String In dic.Keys
                    If DataTables("表A").datacols.Contains(c) Then
                        dr(c) = sheet(n, dic(c)).Text
                    End If
                Next
            End If
        Next
    Next
   
End If

 我只能说，论坛技术支持很强大，而且神速。

还有个愿望。上述代码正常运行，但耗费时间较多，能否进一步优化速度？

Dim dlg As new OpenFileDialog
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
        Dim tdic As new Dictionary(of DataRow, Integer)
        Dim tls As new List(of 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("表A").find(filter)
               
                If dr Is Nothing Then
                    tls.add(n)
                Else
                    tdic.add(dr, n)
                End If
               
            End If
        Next
        For Each key As DataRow In tdic.Keys
            For Each c As String In dic.Keys
                If DataTables("表A").datacols.Contains(c) Then
                    key(c) = sheet(tdic(key), dic(c)).Text
                End If
            Next
        Next
        For Each i As Integer In tls
            Dim dr = DataTables("表A").AddNew
            For Each c As String In dic.Keys
                If DataTables("表A").datacols.Contains(c) Then
                    dr(c) = sheet(i, dic(c)).Text
                End If
            Next
        Next
    Next
   
End If

 有点蓝和大红袍的代码都很快。

 出现新情况了。

导入的execl　是一段日期的数据，　比如２０１５年　７月１５日－８月２３日期间的数据，　　我需要在导入数据前，把原来的表里，７月１５日－８月２３日期间的所有数据都删掉。

问题１：怎么获得EXECL表里的这个日期段？　　

日期是连续的。　　execl的入住时间这列里有很多重复的日期，　只需要知道最小日期和最大日期，或者查询都有哪些日期，不知道怎么写代码。


问题２：怎样删掉表里的这个日期段的数据。　　

DataTables("导入客户资料").DeleteFor("[入住时间] <＝ #日期２#　and [入住时间] >＝ #日期1#")

delete 表名　where 条件语句

这两种删除有什么区别吗？

删掉原来表里的这些日期的数据，就不用比较是否存在同日期、同姓名、同房间号的数据了。　效率应该更高了。

这段代码怎么改写呢？

1、循环每一行，获取日期比较

 

2、DataTables("导入客户资料").DeleteFor("[入住时间] <＝ #" & 日期1 & "#　and [入住时间] >＝ #" & 日期2 & "#")