以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.com/bbs/index.asp)
--  专家坐堂  (http://foxtable.com/bbs/list.asp?boardid=2)
----  不重复导入  (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=131561)

--  作者:zhuxinhui
--  发布时间:2019/3/1 12:08:00
--  不重复导入
Dim book As NEW xls.book("G:\\现金银行.XLS")
Dim Sheet As xls.Sheet = BOOK.Sheets(0)
For i As Integer = 1 To Sheet.Rows.Count-1
    Dim b1 As String = sheet(i,1).text
    Dim b2 As String = sheet(i,2).text
    Dim b3 As String = sheet(i,3).text
    Dim b4 As String = sheet(i,4).text
    Dim b5 As String = sheet(i,5).text
    Dim b7 As String = sheet(i,7).text
    Dim b8 As String = sheet(i,8).text


 If DataTables("现金银行").find("类别名称 = \'"& b1 &"\'AND 日期 = #"& b2 &"# AND 摘要 = \'"& b3 &"\'AND 收入 = \'"& b4 &"\' And 支出 = \'"& b5 &"\' And 对方帐户 = \'"& b7 &"\' And 会计年度 = \'"& b8 &"\'") Is Nothing Then
    Dim dr As Row = Tables("现金银行").AddNew()
    dr("类别名称") = "基本帐户"
    dr("日期") = sheet(i,2).text
    dr("摘要") = sheet(i,3).text
    dr("收入") = sheet(i,4).text
    dr("支出") = sheet(i,5).text
    dr("对方帐户") = sheet(i,7).text
    dr("会计年度") = sheet(i,8).text
 End If
Next

怎么这句不可以起到防重复导入的功能的:If DataTables("现金银行").find("类别名称 = \'"& b1 &"\'AND 日期 = #"& b2 &"# AND 摘要 = \'"& b3 &"\'AND 收入 = \'"& b4 &"\' And 支出 = \'"& b5 &"\' And 对方帐户 = \'"& b7 &"\' And 会计年度 = \'"& b8 &"\'") Is Nothing Then
现在按一次就重复导入一次,想导入一次后,第二次只导入不得重复的值


如果是单列判断,这样又可以
 Dim b1 As String = sheet(i,9).text
 If DataTables("现金银行").find("重复判定 = \'"& b1&"\'") Is Nothing Then




--  作者:有点甜
--  发布时间:2019/3/1 12:26:00
--  

请做一个具体的例子发上来测试。

 

不然,请看看这样合成条件 http://www.foxtable.com/bbs/dispbbs.asp?BoardID=2&replyID=879031&ID=131505&skin=1

 


--  作者:zhuxinhui
--  发布时间:2019/3/1 14:14:00
--  
电子表放D盘
 下载信息  [文件大小:   下载次数: ]
图片点击可在新窗口打开查看点击浏览该文件:新建文件夹.rar


--  作者:zhuxinhui
--  发布时间:2019/3/1 14:15:00
--  
现在按一次导入一次,我想导入不重复的数值


--  作者:有点甜
--  发布时间:2019/3/1 14:26:00
--  
Dim book As NEW xls.book("C:\\Users\\dv rrxaI600 007\\Downloads\\新建文件夹\\新建文件夹\\现金银行.XLS")
Dim Sheet As xls.Sheet = BOOK.Sheets(0)
For i As Integer = 1 To Sheet.Rows.Count-1
    Dim b1 As String = sheet(i,1).text
    Dim b2 As String = sheet(i,2).text
    Dim b3 As String = sheet(i,3).text
    Dim b4 As String = sheet(i,4).text
    Dim b5 As String = sheet(i,5).text
    Dim b7 As String = sheet(i,7).text
    Dim b8 As String = sheet(i,8).text
    Dim filter As String = "1=1"
    If b1 = Nothing Then
        filter &= " and 类别名称 = \'基本帐户\'"
    Else
        filter &= " and 类别名称 = \'"& b1 &"\'"
    End If
   
    If b2 = Nothing Then
        filter &= " and 日期 is null"
    Else
        filter &= " and 日期 = #"& b2 &"#"
    End If
    If b3 = Nothing Then
        filter &= " and 摘要 is null"
    Else
        filter &= " and 摘要 = \'"& b3 &"\'"
    End If
    If b4 = Nothing Then
        filter &= " and 收入 is null"
    Else
        filter &= " and 收入 = \'"& b4 &"\'"
    End If
    If b5 = Nothing Then
        filter &= " and 支出 is null"
    Else
        filter &= " and 支出 = \'"& b5 &"\'"
    End If
   
    If b7 = Nothing Then
        filter &= " and 对方帐户 is null"
    Else
        filter &= " and 对方帐户 = \'"& b7 &"\'"
    End If
    If b8 = Nothing Then
        filter &= " and 会计年度 is null"
    Else
        filter &= " and 会计年度 = \'"& b8 &"\'"
    End If
    If DataTables("现金银行").find(filter) Is Nothing Then
        Dim dr As Row = Tables("现金银行").AddNew()
        dr("类别名称") = "基本帐户"
        dr("日期") = sheet(i,2).text
        dr("摘要") = sheet(i,3).text
        dr("收入") = sheet(i,4).text
        dr("支出") = sheet(i,5).text
        dr("对方帐户") = sheet(i,7).text
        dr("会计年度") = sheet(i,8).text
    End If
Next

--  作者:zhuxinhui
--  发布时间:2019/3/2 10:18:00
--  
这句如何写成是提取当前电子表的表名
If b1 = Nothing Then
        filter &= " and 类别名称 = \'基本帐户\'"
    Else
现在问题又来了,如果一个工作簿有N个格式相同的表,类别名称值为提取当前表的表名,这句如何写成是提取当前电子表的表名,
 
Dim book As NEW xls.book("C:\\Users\\dv rrxaI600 007\\Downloads\\新建文件夹\\新建文件夹\\现金银行.XLS")
For L As Integer = 0 To 100
  Dim Sheet As xls.Sheet = BOOK.Sheets(L)
  For i As Integer = 1 To Sheet.Rows.Count-1
    Dim b1 As String = sheet(i,1).text
    Dim b2 As String = sheet(i,2).text
    Dim b3 As String = sheet(i,3).text
    Dim b4 As String = sheet(i,4).text
    Dim b5 As String = sheet(i,5).text
    Dim b7 As String = sheet(i,7).text
    Dim b8 As String = sheet(i,8).text
    Dim filter As String = "1=1"
    If b1 = Nothing Then
        filter &= " and 类别名称 = \'基本帐户\'"
    Else
        filter &= " and 类别名称 = \'"& b1 &"\'"
    End If
    
    If b2 = Nothing Then
        filter &= " and 日期 is null"
    Else
        filter &= " and 日期 = #"& b2 &"#"
    End If
    If b3 = Nothing Then
        filter &= " and 摘要 is null"
    Else
        filter &= " and 摘要 = \'"& b3 &"\'"
    End If
    If b4 = Nothing Then
        filter &= " and 收入 is null"
    Else
        filter &= " and 收入 = \'"& b4 &"\'"
    End If
    If b5 = Nothing Then
        filter &= " and 支出 is null"
    Else
        filter &= " and 支出 = \'"& b5 &"\'"
    End If
    
    If b7 = Nothing Then
        filter &= " and 对方帐户 is null"
    Else
        filter &= " and 对方帐户 = \'"& b7 &"\'"
    End If
    If b8 = Nothing Then
        filter &= " and 会计年度 is null"
    Else
        filter &= " and 会计年度 = \'"& b8 &"\'"
    End If
    If DataTables("现金银行").find(filter) Is Nothing Then
        Dim dr As Row = Tables("现金银行").AddNew()
        dr("类别名称") = "基本帐户"
        dr("日期") = sheet(i,2).text
        dr("摘要") = sheet(i,3).text
        dr("收入") = sheet(i,4).text
        dr("支出") = sheet(i,5).text
        dr("对方帐户") = sheet(i,7).text
        dr("会计年度") = sheet(i,8).text
    End If
  Next
Next


--  作者:有点蓝
--  发布时间:2019/3/2 10:53:00
--  
dr("类别名称") = sheet.name
--  作者:zhuxinhui
--  发布时间:2019/3/2 11:19:00
--  
完美解决,多谢有点蓝老师


--  作者:zhuxinhui
--  发布时间:2019/3/2 15:18:00
--  
For L As Integer = 0 To 100
这句如何改成从0到工作簿中工作表的最大个数

--  作者:zhuxinhui
--  发布时间:2019/3/2 15:21:00
--  
100超出了范围,提示出来
图片点击可在新窗口打开查看此主题相关图片如下:qq截图20190302152012.gif
图片点击可在新窗口打开查看