Foxtable(狐表) → 用户栏目 → 专家坐堂 → 如何筛选比对两个月的数据的变化情况


  共有7036人关注过本帖树形打印复制链接

主题：如何筛选比对两个月的数据的变化情况
帅哥哟，离线，有人找我吗？
有点甜
 小大  1楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：版主 帖子：85326 积分：427815 威望：0 精华：5 注册：2012/10/18 22:13:00 		  发帖心情 Post By：2014/1/7 23:41:00 [显示全部帖子]

 上传一个例子吧，简单方便快捷。

 回到顶部
帅哥哟，离线，有人找我吗？
有点甜
 小大  2楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：版主 帖子：85326 积分：427815 威望：0 精华：5 注册：2012/10/18 22:13:00 		  发帖心情 Post By：2014/1/8 23:51:00 [显示全部帖子]

 感觉还是挺不容易的……

Dim t As Table = Tables("比对表")
Dim d1 As String = "201401"
Dim d2 As String = "201402"
Dim cls() As String = {"职务", "职称", "学科", "工资", "奖金", "合计", "考核"}

Dim cs1 As C1.Win.C1FlexGrid.CellStyle = t.grid.Styles.Add("cs1")
Dim cs2 As C1.Win.C1FlexGrid.CellStyle = t.grid.Styles.Add("cs2")
Dim cs3 As C1.Win.C1FlexGrid.CellStyle = t.grid.Styles.Add("cs3")
cs1.backcolor = Color.red
cs2.backcolor = Color.blue
cs3.backcolor = Color.green

Dim filter1 As String = "年月 = '" & d1 & "'"
Dim filter2 As String = "年月 = '" & d2 & "'"

t.Filter = filter1 & " or " & filter2
t.Sort = "姓名,年月"

Dim r1, r2 As Row
For i As Integer = 0 To t.Rows.Count - 2
    r1 = t.Rows(i)
    r2 = t.Rows(i+1)
    
    If r1("姓名") = r2("姓名") Then '对比
        For Each c As String In cls
            If r1(c) <> r2(c) Then
                t.Grid.SetCellStyle(i+2,t.Cols(c).Index+1,cs3)
            End If
        Next
        i += 1
    Else
        If r1("年月") = d1 Then '减少
            For j As Integer = 2 To t.Cols.Count - 1
                t.Grid.SetCellStyle(i+1,j+1,cs2)
            Next
        Else If r1("年月") = d2 Then '新增
            For j As Integer = 2 To t.Cols.Count - 1
                t.Grid.SetCellStyle(i+1,j+1,cs1)
            Next
        End If
    End If
Next

r1 = t.Rows(t.Rows.Count-2)
r2 = t.Rows(t.Rows.Count-1)

If r1("姓名") = r2("姓名") Then '对比
    For Each c As String In cls
        If r1(c) <> r2(c) Then
            t.Grid.SetCellStyle(t.Rows.Count,t.Cols(c).Index+1,cs3)
        End If
    Next
Else
    If r2("年月") = d1 Then '减少
        For j As Integer = 2 To t.Cols.Count - 1
            t.Grid.SetCellStyle(t.Rows.count,j+1,cs2)
        Next
    Else If r2("年月") = d2 Then '新增
        For j As Integer = 2 To t.Cols.Count - 1
            t.Grid.SetCellStyle(t.Rows.Count,j+1,cs1)
        Next
    End If
End If
[此贴子已经被作者于2014-1-8 23:55:19编辑过]
 回到顶部
帅哥哟，离线，有人找我吗？
有点甜
 小大  3楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：版主 帖子：85326 积分：427815 威望：0 精华：5 注册：2012/10/18 22:13:00 		  发帖心情 Post By：2014/1/9 22:37:00 [显示全部帖子]

 简单改一下，其实你只要修改前面的4行代码就可以使用了

Dim t As Table = Tables("比对表")
Dim d1 As String = "201401"
Dim d2 As String = "201402"
Dim cls() As String = {"职务", "职称", "学科", "工资", "奖金", "合计"}

Dim delCount As Integer = 0
Dim cs1 As C1.Win.C1FlexGrid.CellStyle = t.grid.Styles.Add("cs1")
Dim cs2 As C1.Win.C1FlexGrid.CellStyle = t.grid.Styles.Add("cs2")
Dim cs3 As C1.Win.C1FlexGrid.CellStyle = t.grid.Styles.Add("cs3")
cs1.backcolor = Color.red
cs2.backcolor = Color.blue
cs3.backcolor = Color.green

Dim filter1 As String = "年月 = '" & d1 & "'"
Dim filter2 As String = "年月 = '" & d2 & "'"

t.Filter = filter1 & " or " & filter2
t.Sort = "姓名,年月"

Dim r1, r2 As Row
Dim i As Integer = 0
Do While i < t.Rows.Count - 1
    r1 = t.Rows(i)
    r2 = t.Rows(i+1)
    
    If r1("姓名") = r2("姓名") Then '对比
        Dim changed As Boolean = False
        For Each c As String In cls
            If r1(c) <> r2(c) Then
                t.Grid.SetCellStyle(i+2,t.Cols(c).Index+1,cs3)
                changed = True
            End If
        Next
        If changed = False Then
            r1.Delete
            r2.Delete
            delcount += 2
        Else
            i += 2
        End If
    Else
        If r1("年月") = d1 Then '减少
            For j As Integer = 2 To t.Cols.Count - 1
                t.Grid.SetCellStyle(i+1,j+1,cs2)
            Next
        Else If r1("年月") = d2 Then '新增
            For j As Integer = 2 To t.Cols.Count - 1
                t.Grid.SetCellStyle(i+1,j+1,cs1)
            Next
        End If
        i += 1
    End If
Loop

r1 = t.Rows(t.Rows.Count-2)
r2 = t.Rows(t.Rows.Count-1)

If r1("姓名") = r2("姓名") Then '对比
    For Each c As String In cls
        If r1(c) <> r2(c) Then
            t.Grid.SetCellStyle(t.Rows.Count,t.Cols(c).Index+1,cs3)
        End If
    Next
Else
    If r2("年月") = d1 Then '减少
        For j As Integer = 2 To t.Cols.Count - 1
            t.Grid.SetCellStyle(t.Rows.count,j+1,cs2)
        Next
    Else If r2("年月") = d2 Then '新增
        For j As Integer = 2 To t.Cols.Count - 1
            t.Grid.SetCellStyle(t.Rows.Count,j+1,cs1)
        Next
    End If
End If
 回到顶部
帅哥哟，离线，有人找我吗？
有点甜
 小大  4楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：版主 帖子：85326 积分：427815 威望：0 精华：5 注册：2012/10/18 22:13:00 		  发帖心情 Post By：2014/1/12 19:49:00 [显示全部帖子]

 9楼比较好。改了一下之前的代码

Dim t As Table = Tables("备份")
Dim d1 As String = "201401"
Dim d2 As String = "201402"
Dim cls() As String = {"职务_评定", "职称", "学科", "工资", "奖金", "合计"}

Dim delCount As Integer = 0
Dim cs1 As C1.Win.C1FlexGrid.CellStyle = t.grid.Styles.Add("cs1")
Dim cs2 As C1.Win.C1FlexGrid.CellStyle = t.grid.Styles.Add("cs2")
Dim cs3 As C1.Win.C1FlexGrid.CellStyle = t.grid.Styles.Add("cs3")
cs1.backcolor = Color.red
cs2.backcolor = Color.blue
cs3.backcolor = Color.green

Dim filter1 As String = "年月 = '" & d1 & "'"
Dim filter2 As String = "年月 = '" & d2 & "'"

t.Filter = filter1 & " or " & filter2
t.Sort = "姓名,年月"

Dim r1, r2 As Row
Dim i As Integer = 0
Do While i < t.Rows.Count - 1
    r1 = t.Rows(i)
    r2 = t.Rows(i+1)
    
    If r1("姓名") = r2("姓名") Then '对比
        Dim changed As Boolean = False
        For Each c As String In cls
            If r1(c) <> r2(c) Then
                t.Grid.SetCellStyle(i+t.HeaderRows,t.Cols(c).Index+1,cs3)
                changed = True
            End If
        Next
        If changed = False Then
            r1.Delete
            r2.Delete
            delcount += 2
        Else
            i += 2
        End If
    Else
        If r1("年月") = d1 Then '减少
            For j As Integer = 2 To t.Cols.Count - 1
                t.Grid.SetCellStyle(i+t.HeaderRows,j+1,cs2)
            Next
        Else If r1("年月") = d2 Then '新增
            For j As Integer = 2 To t.Cols.Count - 1
                t.Grid.SetCellStyle(i+t.HeaderRows,j+1,cs1)
            Next
        End If
        i += 1
    End If
Loop

r1 = t.Rows(t.Rows.Count-2)
r2 = t.Rows(t.Rows.Count-1)

If r1("姓名") = r2("姓名") Then '对比
    For Each c As String In cls
        If r1(c) <> r2(c) Then
            t.Grid.SetCellStyle(t.Rows.Count+t.HeaderRows-1,t.Cols(c).Index+1,cs3)
        End If
    Next
Else
    If r2("年月") = d1 Then '减少
        For j As Integer = 2 To t.Cols.Count - 1
            t.Grid.SetCellStyle(t.Rows.count+t.HeaderRows-1,j+1,cs2)
        Next
    Else If r2("年月") = d2 Then '新增
        For j As Integer = 2 To t.Cols.Count - 1
            t.Grid.SetCellStyle(t.Rows.Count+t.HeaderRows-1,j+1,cs1)
        Next
    End If
End If
 回到顶部
帅哥哟，离线，有人找我吗？
有点甜
 小大  5楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：版主 帖子：85326 积分：427815 威望：0 精华：5 注册：2012/10/18 22:13:00 		  发帖心情 Post By：2014/1/12 23:28:00 [显示全部帖子]

 如下代码

Dim t As Table = Tables("备份")
Dim d1 As String = "201401"
Dim d2 As String = "201402"
Dim cls() As String = {"职务_评定", "职称", "学科", "工资", "奖金", "合计"}

Dim delCount As Integer = 0
Dim cs1 As C1.Win.C1FlexGrid.CellStyle = t.grid.Styles.Add("cs1")
Dim cs2 As C1.Win.C1FlexGrid.CellStyle = t.grid.Styles.Add("cs2")
Dim cs3 As C1.Win.C1FlexGrid.CellStyle = t.grid.Styles.Add("cs3")
Dim cs4 As C1.Win.C1FlexGrid.CellStyle = t.grid.Styles.Add("cs4")
cs1.backcolor = Color.red
cs2.backcolor = Color.blue
cs3.backcolor = Color.green
cs4.backcolor = Color.yellow

Dim filter1 As String = "年月 = '" & d1 & "'"
Dim filter2 As String = "年月 = '" & d2 & "'"

t.Filter = filter1 & " or " & filter2
t.Sort = "姓名,年月"

Dim r1, r2 As Row
Dim i As Integer = 0
Do While i < t.Rows.Count - 1
    r1 = t.Rows(i)
    r2 = t.Rows(i+1)
    
    If r1("姓名") = r2("姓名") Then '对比
        Dim changed As Boolean = False
        For Each c As String In cls
            If r1(c) <> r2(c) Then
                t.Grid.SetCellStyle(i+t.HeaderRows,t.Cols(c).Index+1,cs4)
                t.Grid.SetCellStyle(i+t.HeaderRows+1,t.Cols(c).Index+1,cs3)
                changed = True
            End If
        Next
        If changed = False Then
            r1.Delete
            r2.Delete
            delcount += 2
        Else
            i += 2
        End If
    Else
        If r1("年月") = d1 Then '减少
            For j As Integer = 2 To t.Cols.Count - 1
                t.Grid.SetCellStyle(i+t.HeaderRows,j+1,cs2)
            Next
        Else If r1("年月") = d2 Then '新增
            For j As Integer = 2 To t.Cols.Count - 1
                t.Grid.SetCellStyle(i+t.HeaderRows,j+1,cs1)
            Next
        End If
        i += 1
    End If
Loop

If t.Rows.Count > 1 Then
    r1 = t.Rows(t.Rows.Count-2)
    r2 = t.Rows(t.Rows.Count-1)
    
    If r1("姓名") = r2("姓名") Then '对比
        For Each c As String In cls
            If r1(c) <> r2(c) Then
                t.Grid.SetCellStyle(t.Rows.Count+t.HeaderRows-2,t.Cols(c).Index+1,cs4)
                t.Grid.SetCellStyle(t.Rows.Count+t.HeaderRows-1,t.Cols(c).Index+1,cs3)
            End If
        Next
    End If
Else If t.Rows.count > 0 Then
    r2 = t.Rows(t.Rows.Count-1)
    If r2("年月") = d1 Then '减少
        For j As Integer = 2 To t.Cols.Count - 1
            t.Grid.SetCellStyle(t.Rows.count+t.HeaderRows-1,j+1,cs2)
        Next
    Else If r2("年月") = d2 Then '新增
        For j As Integer = 2 To t.Cols.Count - 1
            t.Grid.SetCellStyle(t.Rows.Count+t.HeaderRows-1,j+1,cs1)
        Next
    End If
End If
 回到顶部
RSS2.0 | Xhtml无图版 | Xslt无图版 Copyright © 2000 - 2018 foxtable.com Tel: 4000-810-820 粤ICP备11091905号
   Powered By Dvbbs Version 8.3.0
页面执行时间 0.05469 秒, 5 次数据查询