主题：[求助]导入数据删除重复

使用 Syscmd.Table.QuickMerge() 命令进行数据导入；想要实现：

 

在导入新数据到【计次工单表】数据表时、判断准备导入的数据中【工单号】是否与数据库中已有的数据中【工单号】是否存在相同；如果有相同的话、就不导入该条数据（即删除要导入的相同数据）。我的命令该怎么写？写在那里？

参考

 

http://www.foxtable.com/bbs/dispbbs.asp?BoardID=2&ID=101056&skin=0

 

如果想实现：如果存在相同的【工单号】、那么替换其中对应的【主题】【完成时间】这两个字段；命令该怎么修改？

 

Dim dlg As New OpenFileDialog
dlg.Filter = "Excel文件|*.xls;*.xlsx"
If dlg.ShowDialog =DialogResult.OK Then
    Dim t As Table = Tables("计次工作清单表")
    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
    For n As Integer = 1 To Sheet.Rows.Count -1
        Dim r As DataRow = t.DataTable.sqlFind("工单号 = '" & sheet(n, 0).text & "'")
        If r Is Nothing Then
            r = t.DataTable.AddNew()
            newcount += 1
        Else
            oldcount += 1
        End If
        For i As Integer = 0 To sheet.Cols.Count -1
            Dim cname As String = sheet(0, i).text
            If t.Cols.Contains(cname) Then
                r(cname) = sheet(n, i).Text
Else

            End If
        Next
    Next
    msgbox("新增" & newcount & "    " & "更新旧数据" & oldcount)
    t.ResumeRedraw()
End If

Dim dlg As New OpenFileDialog
dlg.Filter = "Excel文件|*.xls;*.xlsx"
If dlg.ShowDialog =DialogResult.OK Then
    Dim t As Table = Tables("计次工作清单表")
    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
    For n As Integer = 1 To Sheet.Rows.Count -1
        Dim r As DataRow = t.DataTable.sqlFind("工单号 = '" & sheet(n, 0).text & "'")
        If r Is Nothing Then
            r = t.DataTable.AddNew()
            newcount += 1
            For i As Integer = 0 To sheet.Cols.Count -1
                Dim cname As String = sheet(0, i).text
                If t.Cols.Contains(cname) Then
                    r(cname) = sheet(n, i).Text
                Else
                   
                End If
            Next
        Else
            msgbox("已存在")
            r("某列") = sheet(n, 1).text
            r("某列") = sheet(n, 2).text
            oldcount += 1
        End If
    Next
    msgbox("新增" & newcount & "    " & "更新旧数据" & oldcount)
    t.ResumeRedraw()
End If

以下命令有两个问题：

1、【计次工作清单表】是没有进行数据加载的表，想直接通过对数据库中有符合条件的内容在数据库中进行更新、更新后直接对数据库进行保存。我的命令该如何修改？（红色字体）

2、r("紧急程度") = sheet(n, 2).text 修改的是读取的数据表的第三列内容、我想改为类似r("紧急程度") = sheet("紧急程度").text这样知道列名的可以吗？

 

Dim dlg As New OpenFileDialog
dlg.Filter = "Excel文件|*.xls;*.xlsx"
If dlg.ShowDialog =DialogResult.OK Then
    Dim t As Table = Tables("计次工作清单表")
    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
    For n As Integer = 1 To Sheet.Rows.Count -1
        Dim r As DataRow = t.DataTable.sqlFind("工单号 = '" & sheet(n, 0).text & "'")
        If r IsNot Nothing Then
            newcount += 1
            r("工单当前状态") = sheet(n, 1).text
            r("紧急程度") = sheet(n, 2).text
            oldcount += 1
            msgbox("更新数据:" & newcount )
            t.ResumeRedraw()
            DataTables("计次工作清单表").Save()
        Else
           
        End If
    Next
Else
   
End If

1、

 

DataTables("计次工作清单表").Save()

 

改成

 

r.save()

 

2、循环每一列，获取第一行的列名

 

Dim dic As new Dictionary(of String, Integer)

For i As Integer = 0 To sheet.Cols.Count -1
    Dim cname As String = sheet(0, i).text
    If dic.ContainsKey(cname) = false then

       dic.add(cname, i)

    End If

Next

 

需要使用的时候，写，如

 

r("紧急程度") = sheet(n, dic("紧急程度")).text

1、使用以下命令导入数据，其中有一个字段【创单时间】为日期格式；系统中设置格式为：DateTime，导入的数据表格式为：2018/11/29  8:57:36，为什么导入数据后变成：2018/11/29  00:00。是哪里出错啦？

 

2、在导入数据的过程中、希望有一个进度条来显示导入数据的进度；一下命令该怎么修改？

 

 

Dim dlg As New OpenFileDialog
dlg.Filter = "Excel文件|*.xls;*.xlsx"
If dlg.ShowDialog =DialogResult.OK Then
    Dim t As Table = Tables("计次工作清单表")
    t.StopRedraw() '停止绘制数据表
    Dim Book As New XLS.Book(dlg.FileName)
    Dim Sheet As XLS.Sheet = Book.Sheets(0) 'Sheet 读取XLS表的第一个工作簿
    Dim newcount As Integer = 0 'newcount等于0
    Dim oldcount As Integer = 0
    Dim notnewcount As Integer = 0
    Dim notnewcount1 As String = "重复工单号:"
   
   
    '读取数据表的列名
    Dim dic As new Dictionary(of String, Integer)
    For i As Integer = 0 To sheet.Cols.Count -1
        Dim cname As String = sheet(0, i).text
        If dic.ContainsKey(cname) = False Then
            dic.add(cname, i)
        Else
        End If
    Next
    '结束
   
    For n As Integer = 1 To Sheet.Rows.Count -1
        Dim r As DataRow = t.DataTable.Find("工单号 = '" & sheet(n, dic("工单号")).text & "'")
        If r Is Nothing Then
            newcount += 1
            r = t.DataTable.AddNew()
        Else
            notnewcount += 1
            notnewcount1 &= vbcrlf & vbcrlf & r("工单号")
        End If
       
        oldcount += 1
        '新增行中加入工单内容
        For i As Integer = 0 To sheet.Cols.Count -1
            Dim cname As String = sheet(0, i).text
            If t.Cols.Contains(cname) Then
                r(cname) = sheet(n, i).Text
            Else
            End If
        Next
        '结束
       
       
    Next
    msgbox("总数据 : " & oldcount &"条 ;  新增数据 : " & newcount &" 条 ; 重复工单数量: " & notnewcount & "条"  & vbcrlf & notnewcount1 ) ' 显示newcount数值
    ClipBoard.SetText(notnewcount1)
    t.ResumeRedraw() '重新绘制数据表
Else
End If

Dim dlg As New OpenFileDialog
dlg.Filter = "Excel文件|*.xls;*.xlsx"
If dlg.ShowDialog =DialogResult.OK Then
    Dim t As Table = Tables("计次工作清单表")
    t.StopRedraw() '停止绘制数据表
    Dim Book As New XLS.Book(dlg.FileName)
    Dim Sheet As XLS.Sheet = Book.Sheets(0) 'Sheet 读取XLS表的第一个工作簿
   
    For r As Integer = 0 To sheet.Rows.count-1
        For i As Integer = 0 To sheet.Cols.Count -1
            Dim Style As XLS.Style = Book.NewStyle() '定义新样式
            style.Format = ""
            sheet(r,i).style = style
        Next
    Next
    book.save("d:\abc.xlsx")
    book = New XLS.Book("d:\abc.xlsx")
    sheet = Book.Sheets(0) 'Sheet 读取XLS表的第一个工作簿
   
   
    Dim newcount As Integer = 0 'newcount等于0
    Dim oldcount As Integer = 0
    Dim notnewcount As Integer = 0
    Dim notnewcount1 As String = "重复工单号:"
   
   
    '读取数据表的列名
    Dim dic As new Dictionary(of String, Integer)
    For i As Integer = 0 To sheet.Cols.Count -1
        Dim cname As String = sheet(0, i).text
        If dic.ContainsKey(cname) = False Then
            dic.add(cname, i)
        Else
        End If
    Next
    '结束
   
    For n As Integer = 1 To Sheet.Rows.Count -1
        Dim r As DataRow = t.DataTable.Find("工单号 = '" & sheet(n, dic("工单号")).text & "'")
        If r Is Nothing Then
            newcount += 1
            r = t.DataTable.AddNew()
        Else
            notnewcount += 1
            notnewcount1 &= vbcrlf & vbcrlf & r("工单号")
        End If
       
        oldcount += 1
        '新增行中加入工单内容
        For i As Integer = 0 To sheet.Cols.Count -1
            Dim cname As String = sheet(0, i).text
            If t.Cols.Contains(cname) Then
                If t.Cols(cname).IsDate Then
                    r(cname) = DateTime.FromOADate(sheet(n,i).value)
                Else
                    r(cname) = sheet(n, i).Text
                End If
            Else
            End If
        Next
        '结束
       
       
    Next
    msgbox("总数据 : " & oldcount &"条 ;  新增数据 : " & newcount &" 条 ; 重复工单数量: " & notnewcount & "条"  & vbcrlf & notnewcount1 ) ' 显示newcount数值
    ClipBoard.SetText(notnewcount1)
    t.ResumeRedraw() '重新绘制数据表
Else
End If

此主题相关图片如下：360截图20181129161821024.jpg

For i As Integer = 0 To sheet.Cols.Count -1
    Dim cname As String = sheet(0, i).text
    If t.Cols.Contains(cname) Then       
        If sheet(n, i).Text <> Nothing Then                       
            If t.Cols(cname).IsDate Then
                r(cname) = DateTime.FromOADate(sheet(n,i).value)
            Else
                r(cname) = sheet(n, i).Text
            End If   

        Else

            r(cname) = nothing
        End If
    Else
    End If
Next