以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.com/bbs/index.asp)
--  专家坐堂  (http://foxtable.com/bbs/list.asp?boardid=2)
----  [求助]导入数据删除重复  (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=125001)

--  作者:wumingrong1
--  发布时间:2018/9/18 10:27:00
--  [求助]导入数据删除重复

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

 

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

[此贴子已经被作者于2018/9/18 10:28:42编辑过]

--  作者:有点甜
--  发布时间:2018/9/18 10:55:00
--  

参考

 

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

 


--  作者:wumingrong1
--  发布时间:2018/9/18 11:38:00
--  

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

 

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


--  作者:有点甜
--  发布时间:2018/9/18 11:49:00
--  
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

--  作者:wumingrong1
--  发布时间:2018/10/24 9:20:00
--  

以下命令有两个问题:

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

[此贴子已经被作者于2018/10/24 9:20:04编辑过]

--  作者:有点甜
--  发布时间:2018/10/24 9:46:00
--  

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


--  作者:wumingrong1
--  发布时间:2018/11/29 12:56:00
--  

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


--  作者:有点甜
--  发布时间:2018/11/29 15:34:00
--  
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

--  作者:wumingrong1
--  发布时间:2018/11/29 16:19:00
--  

图片点击可在新窗口打开查看此主题相关图片如下:360截图20181129161821024.jpg
图片点击可在新窗口打开查看

--  作者:有点甜
--  发布时间:2018/11/29 16:28:00
--  

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