专家坐堂-[求助]Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

以文本方式查看主题

-  Foxtable(狐表)  (http://www.foxtable.com/bbs/index.asp)
--  专家坐堂  (http://www.foxtable.com/bbs/list.asp?boardid=2)
----  [求助]  (http://www.foxtable.com/bbs/dispbbs.asp?boardid=2&id=64775)

--  作者：yjd
--  发布时间：2015/3/1 20:10:00
--  [求助]
版主您好，我写的代码是想把“XLS”表中数据导入系统表中更新“账号","客户号","户名","账户性质","开户日期","存款余额","年日均余额”等列，"XLS"为最新数据，怎样写代码导入最新数据列，同时删除系统表中有而"XLS"没有“账号”列的行。谢谢。
If msgbox("导入更新可以导入客户基础数据和最新的存款余额和年日均余额,是否进行?",65,"提示") = 1 Then
Dim Book As New XLS.Book("D:\\khdaxt\\drgx\\dgdrgx.xls")
Dim Sheet As XLS.Sheet = Book.Sheets(0)
Tables("对公客户明细").StopRedraw()
Dim nms() As String = {"账号","客户号","户名","账户性质","开户日期","存款余额","年日均余额"}
For n As Integer = 0 To Sheet.Rows.Count -1
Dim bh As String = sheet(n,0).Text
Dim dr As DataRow = DataTables("对公客户明细").Find("账号 = \'" & bh & "\'")
If dr Is Nothing Then \'如果不存在同编号的订单
dr = DataTables("对公客户明细").AddNew()
End If
For m As Integer = 0 To nms.Length -1
dr(nms(m)) = trim(Sheet(n,m).Value)
Next
Next
Tables("对公客户明细").ResumeRedraw()
msgbox("客户数据导入更新完毕!",64,"提示")
End If

--  作者：有点甜
--  发布时间：2015/3/1 20:17:00
--

那你还不如删除所有表数据，再重新添加数据，效率绝对比你查找数据要来得高

If msgbox("导入更新可以导入客户基础数据和最新的存款余额和年日均余额,是否进行?",65,"提示") = 1 Then
    DataTables("对公客户明细").DataRows.Clear
    Dim Book As New XLS.Book("D:\\khdaxt\\drgx\\dgdrgx.xls")
    Dim Sheet As XLS.Sheet = Book.Sheets(0)
    DataTables("对公客户明细").StopRedraw()
    Dim nms() As String = {"账号","客户号","户名","账户性质","开户日期","存款余额","年日均余额"}
    For n As Integer = 0 To Sheet.Rows.Count -1
        Dim bh As String = sheet(n,0).Text
        \'Dim dr As DataRow = DataTables("对公客户明细").Find("账号 = \'" & bh & "\'")
        Dim dr As DataRow = DataTables("对公客户明细").AddNew
        If dr Is Nothing Then \'如果不存在同编号的订单
            dr = DataTables("对公客户明细").AddNew()
        End If
        For m As Integer = 0 To nms.Length -1
            dr(nms(m)) = trim(Sheet(n,m).Value)
        Next
    Next
    DataTables("对公客户明细").ResumeRedraw()
    msgbox("客户数据导入更新完毕!",64,"提示")
End If

--  作者：yjd
--  发布时间：2015/3/1 20:31:00
--
版主您好，我不能删除系统中数据，因为我只想更新每行中部分列数据，同时删除“XLS”表中有“账号”行而系统表中没有“账号”的行。谢谢

--  作者：有点甜
--  发布时间：2015/3/1 20:39:00
--
If msgbox("导入更新可以导入客户基础数据和最新的存款余额和年日均余额,是否进行?",65,"提示") = 1 Then
    Dim Book As New XLS.Book("D:\\khdaxt\\drgx\\dgdrgx.xls")
    Dim Sheet As XLS.Sheet = Book.Sheets(0)
    DataTables("对公客户明细").StopRedraw()
    Dim nms() As String = {"账号","客户号","户名","账户性质","开户日期","存款余额","年日均余额"}
    Dim zhs As String = ""
    For n As Integer = 0 To Sheet.Rows.Count -1

        Dim bh As String = sheet(n,0).Text
        zhs &= "\'" & bh & "\',"
        Dim dr As DataRow = DataTables("对公客户明细").Find("账号 = \'" & bh & "\'")
        If dr Is Nothing Then \'如果不存在同编号的订单
            dr = DataTables("对公客户明细").AddNew()
        End If
        For m As Integer = 0 To nms.Length -1
            dr(nms(m)) = trim(Sheet(n,m).Value)
        Next
    Next
    DataTables("对公客户明细").DeleteFor("账号 not in (" & zhs.Trim(",") & ")")
    DataTables("对公客户明细").ResumeRedraw()
    msgbox("客户数据导入更新完毕!",64,"提示")
End If

--  作者：yjd
--  发布时间：2015/3/1 21:02:00
--
谢谢版主，已解决！