以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.net/bbs/index.asp)
--  专家坐堂  (http://foxtable.net/bbs/list.asp?boardid=2)
----  [求助]Excel表格数据导入前的校验怎么写?  (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=102671)

--  作者:浙江仔
--  发布时间:2017/6/23 15:50:00
--  [求助]Excel表格数据导入前的校验怎么写?
之前用了这个过滤,现在数据量大了,不好处理。
Dim dt As DataTable
Dim cmd As New SQLCommand
cmd.C
cmd.CommandText = "SEL ECT bmID,papernum From {Person_info} where county = \'" & Vars("V_county") & "\' "
dt=cmd.Execu teReader

Dim ids As String
For Each dr As DataRow In dt.DataRows
ids = ids & ",\'" & dr("papernum") & "\'"
Next
ids= ids.Trim(",")
mg.Filter =  "身份证号码 not In (" & ids & ")"

搜索了一个新的办法,但是不知道过滤条件怎么加了?

Dim dlg As New OpenFileDialog
dlg.Filter = "Excel文件|*.xls;*.xlsx"
If dlg.ShowDialog =DialogResult.OK Then
    Dim t As Table = Tables("Person_info")
    t.StopRedraw()
    Dim Book As New XLS.Book(dlg.FileName)
    Dim Sheet As XLS.Sheet = Book.Sheets(0)
    Dim newcount As Integer = 0
    Dim oldcount As Integer = 0
    
    Dim nms1() As String = {"姓名","性别","学历","身份证号码","所在乡镇","单位","手机号码"}  \'导入的数据表
    Dim nms2() As String = {"name","sex","xueli","papernum","town","bmunit","phone"}    
    
    For n As Integer = 1 To Sheet.Rows.Count -1
        
        Dim r As Row = Tables("Person_info").AddNew()
        For i As Integer = 0 To sheet.Cols.Count -1
            Dim idx As Integer = array.indexof(nms1, sheet(0,i).Text)
            If idx >= 0 Then
                r(nms2(idx)) = sheet(n, i).Text
            End If
        Next
    Next
    t.ResumeRedraw()
End If

如果在导入Excel数据表的时候,要校验身份证号码,如果存在,这条数据就不导入,应该怎么改写呢?


望指点,谢谢


--  作者:wyz20130512
--  发布时间:2017/6/23 16:27:00
--  
把Excel文件上传看看。
--  作者:浙江仔
--  发布时间:2017/6/23 16:40:00
--  
 下载信息  [文件大小:   下载次数: ]
图片点击可在新窗口打开查看点击浏览该文件:工作簿1.rar


--  作者:有点蓝
--  发布时间:2017/6/23 17:48:00
--  
For n As Integer = 1 To Sheet.Rows.Count -1
    Dim dr As DataRow = DataTables("Person_info").Find("papernum=\'" & sheet(0,3) & "\'")
    If dr Is Nothing Then
        dr = DataTables("Person_info").AddNew()
        For i As Integer = 0 To sheet.Cols.Count -1
            Dim idx As Integer = array.indexof(nms1, sheet(0,i).Text)
            If idx >= 0 Then
                r(nms2(idx)) = sheet(n, i).Text
            End If
        Next
    End If
Next