Foxtable(狐表)用户栏目专家坐堂 → 转置后的临时表怎么用


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

主题:转置后的临时表怎么用

帅哥哟,离线,有人找我吗?
有点甜
  1楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


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

Dim g As New CrossTableBuilder("成绩转置", DataTables("成绩登记表"))
g.HGroups.AddDef("考试期数")
g.HGroups.AddDef("班级")
g.HGroups.AddDef("学号")
g.HGroups.AddDef("姓名")
g.VGroups.AddDef("课程名称")
g.Totals.AddDef("分数", "分数")
g.HorizontalTotal = True
g.Build()
MainTable = Tables("成绩转置")
Tables("成绩转置").grid.Cols("合计").caption = "总分"
Dim dic As new Dictionary(of String, String)
For Each c As Col In Tables("成绩转置").cols
dic.add(c.caption, c.name)
Next
'---------------------------------
Dim fsd As New List(of Integer)
For Each r1 As Row In Tables("自定义统计分数段").Rows
    If r1.Checked Then
        fsd.add(r1("分数段"))
    End If
Next
If fsd.count> 0 Then
    fsd.sort
    Dim dtb As New DataTableBuilder("统计")
    dtb.AddDef("班级", Gettype(String))
    dtb.AddDef("科目", Gettype(String), 32)
    For Each fs As Integer In fsd
        dtb.AddDef(fs & "分" & iif(fs = fsd(0), "以下","以上") , Gettype(Integer))
    Next
    dtb.Build()
End If

Dim flt As String
Dim dt As DataTable =DataTables("成绩转置")
Dim t1 As Table =Tables("统计")
t1.StopRedraw

Dim bjs As List(of String) = dt.GetUniqueValues("","班级")
For Each flt In bjs
    Dim f As New Filler
    f.SourceTable =DataTables("科目表")
    f.DataTable = t1.DataTable   
    f.Fill()
    t1.DataTable.ReplaceFor("班级",flt,"班级 Is null")
Next

For Each dc As Col In t1.Cols
    If dc.Index > 1 Then
        For Each dr As Row In t1.Rows
            flt = "班级 = '" & dr("班级") & "' And " & dic(dr("科目"))
            If dc.Index =2 Then
                flt+ = " < "  &  dc.name.split("分")(0)
            ElseIf dc.Index =Tables("统计").Cols.count -1 Then
                flt+ = " >= "  &  dc.name.split("分")(0)
            Else
                flt+ = " >= " &  dc.name.split("分")(0) & " And " & dic(dr("科目")) & " < " & t1.Cols(dc.Index+1).Name.split("分")(0)
            End If
            dr(dc.name) =dt.Compute("Count(姓名)",flt)
        Next
    End If
Next

t1.DataTable.SysStyles("EmptyArea").BackColor = Color.White
t1.ResumeRedraw
MainTable = t1


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


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


 回到顶部
帅哥哟,离线,有人找我吗?
有点甜
  3楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


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

以下是引用fytea在2018/10/30 17:52:00的发言:

合计我会做,怎样排名?

 

 

参考

 

http://www.foxtable.com/webhelp/scr/2382.htm

 


 回到顶部
帅哥哟,离线,有人找我吗?
有点甜
  4楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


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

参考

 

Dim g As New CrossTableBuilder("成绩转置", DataTables("成绩登记表"))
g.HGroups.AddDef("考试期数")
g.HGroups.AddDef("班级")
g.HGroups.AddDef("学号")
g.HGroups.AddDef("姓名")
g.VGroups.AddDef("课程名称")
g.Totals.AddDef("分数", "分数")
g.Totals.AddDef("排名")
g.HorizontalTotal = True
g.Build()
MainTable = Tables("成绩转置")

'获得所有班级名称,保存在集合中
Dim bjs As List(Of String()) = DataTables("成绩转置").GetValues("考试期数|班级")
DataTables("成绩转置").StopRedraw
For Each bj As String() In bjs
    '获得该班级的全部行,按总分降序排序
    For Each c As Col In Tables("成绩转置").Cols
        If c.Caption.Contains("分数") Then
            Dim fs As String = "分数_" & c.name.Split("_")(1)
            Dim pm As String = "排名_" & c.name.Split("_")(1)
            If c.Caption.Contains("合计") Then
                fs = "合计_分数"
                pm = "合计_排名"
            End If
            Dim drs As List(Of DataRow) = DataTables("成绩转置").Select("考试期数 = '" & bj(0) & "' and [班级] = '" & bj(1) & "'", fs & " DESC")
            For n As Integer = 0 To drs.Count - 1 '遍历所有行
                If n > 0 AndAlso drs(n)(fs) = drs(n-1)(fs) Then '如果总分和上一行相同
                    drs(n)(pm) = drs(n-1)(pm) '则排名等于上一行
                Else
                    drs(n)(pm) = n + 1 '设置排名
                End If
            Next
        End If
    Next
Next
DataTables("成绩转置").ResumeRedraw


 回到顶部
帅哥哟,离线,有人找我吗?
有点甜
  5楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


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

具体项目发上来测试

 回到顶部
帅哥哟,离线,有人找我吗?
有点甜
  6楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


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

Dim g As New CrossTableBuilder("成绩转置", DataTables("成绩登记表"))
g.HGroups.AddDef("考试期数")
g.HGroups.AddDef("年级")
g.HGroups.AddDef("班级")
g.HGroups.AddDef("学号")
g.HGroups.AddDef("姓名", "", "参考人数")
g.VGroups.AddDef("课程名称")
g.Totals.AddDef("分数", "分数")
g.HorizontalTotal = True
g.Build()
MainTable = Tables("成绩转置")
'Std:标准偏差
'StdPop:总体标准偏差
'Sum:累积值
'Var:方差
'VarPop:总体方差

'--------------------------
Dim dtb As New DataTableBuilder("统计")
dtb.AddDef("参考人数", Gettype(String))
dtb.AddDef("年级", Gettype(String))
dtb.AddDef("班级", Gettype(String))
dtb.AddDef("科目", Gettype(String), 32)
dtb.AddDef("最高分", Gettype(Double))
dtb.AddDef("最低分", Gettype(Double))
dtb.AddDef("平均分", Gettype(Double))
dtb.AddDef("标准偏差", Gettype(Double), 32)
dtb.AddDef("总体标准偏差", Gettype(Double))
dtb.AddDef("样本方差", Gettype(Double))
dtb.AddDef("总样本方差", Gettype(Double))

dtb.Build()
'--------
Dim dic As new Dictionary(of String, String)
For Each c As Col In Tables("成绩转置").cols
    dic.add(c.caption, c.name)
    '--------
Next
Dim exp,flt As String
Dim dt As DataTable =DataTables("成绩转置")
Dim t As Table = Tables("成绩转置")
Dim t1 As Table =Tables("统计")
't1.StopRedraw

Dim bjs As List(of String()) = dt.Getvalues("年级|班级")
For Each bj As String() In bjs
    Dim f As New Filler
    f.SourceTable =DataTables("科目表")
    f.DataTable = t1.DataTable
    f.Fill()
    t1.DataTable.ReplaceFor("班级",bj(1),"班级 Is null")
    t1.DataTable.ReplaceFor("年级",bj(0),"年级 Is null")
Next

For Each dc As Col In t1.Cols
    For Each dr As Row In t1.Rows
        flt = "班级 = '" & dr("班级") & "' and 年级 = '" & dr("年级") & "'"
        Select Case dc.name
            Case "最高分"
                exp = "Max(" & dic(dr("科目")) & ")"
                dr(dc.name) =dt.Compute(exp,flt)
            Case "最低分"
                exp = "Min(" & dic(dr("科目")) & ")"
                dr(dc.name) =dt.Compute(exp,flt)
            Case "平均分"
                exp = "Avg(" & dic(dr("科目")) & ")"
                dr(dc.name) =dt.Compute(exp,flt)
            Case "标准偏差"
                t.filter = flt
                Dim c1 = t.Cols(dic(dr("科目"))).Index
                dr(dc.name) = t.Aggregate(AggregateEnum.std, 0, c1, t.Rows.count-1, c1)
            Case "总体标准偏差"
                t.filter = flt
                Dim c1 = t.Cols(dic(dr("科目"))).Index
                dr(dc.name) = t.Aggregate(AggregateEnum.StdPop, 0, c1, t.Rows.count-1, c1)
                               
        End Select
    Next
Next

t1.DataTable.SysStyles("EmptyArea").BackColor = Color.White
t1.ResumeRedraw
MainTable = t1

 


 回到顶部
帅哥哟,离线,有人找我吗?
有点甜
  7楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


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

            Case "参考人数"
                exp = "count(" & dic(dr("科目")) & ")"
                dr(dc.name) =dt.Compute(exp,flt)

 回到顶部
帅哥哟,离线,有人找我吗?
有点甜
  8楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


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

以下是引用fytea在2018/11/1 19:28:00的发言:
建立的主窗口,启动后点击功能按钮,即退出窗口,怎么解决?

 

实例发上来测试。


 回到顶部
帅哥哟,离线,有人找我吗?
有点甜
  9楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


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

参考

 

    t1.DataTable.ReplaceFor("考试期数",bj(2),"考试期数 Is null")


 回到顶部
帅哥哟,离线,有人找我吗?
有点甜
  10楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


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

Dim g As New CrossTableBuilder("成绩转置1", DataTables("成绩登记表"))
g.HGroups.AddDef("考试期数")
g.HGroups.AddDef("年级")
g.HGroups.AddDef("班级")
g.HGroups.AddDef("学号")
g.HGroups.AddDef("姓名")
g.VGroups.AddDef("课程名称")
g.Totals.AddDef("分数", "分数")
g.HorizontalTotal = True
g.Build()
MainTable = Tables("成绩转置1")
Tables("成绩转置1").grid.Cols("合计").caption = "总分"
Dim dic As new Dictionary(of String, String)
For Each c As Col In Tables("成绩转置1").cols
    dic.add(c.caption, c.name)
Next
'---------------------------------
Dim fsd As New List(of Integer)

 

 


For Each r1 As Row In Tables("自定义统计分数段").Rows
   
    If r1.Checked = False Then
        MessageBox.Show("请定义统计分数段,并把无用的数据删除,否则系统将无法统计运行","提示")
        Return
    Else
        If r1.Checked Then
            fsd.add(r1("分数段"))
        End If
    End If
   
Next
If fsd.count> 0 Then
    fsd.sort
    Dim dtb As New DataTableBuilder("统计")
    dtb.AddDef("考试期数", Gettype(String))
    dtb.AddDef("年级", Gettype(String))
    dtb.AddDef("班级", Gettype(String))
    dtb.AddDef("科目", Gettype(String), 32)
    If fsd.count = 1 Then
        dtb.AddDef(fsd(0) & "分以下", Gettype(Integer))
        dtb.AddDef(fsd(0) & "分以上", Gettype(Integer))
    ElseIf fsd.count >= 2 Then
        dtb.AddDef(fsd(0) & "分以下", Gettype(Integer))
        For fs As Integer = 0 To fsd.count-2
            dtb.AddDef(fsd(fs) & "分-" & fsd(fs+1) & "分", Gettype(Integer))
        Next
        dtb.AddDef(fsd(fsd.count-1) & "分以上", Gettype(Integer))
    End If
    dtb.Build()
End If


Dim flt As String
Dim dt As DataTable =DataTables("成绩转置1")
Dim t1 As Table =Tables("统计")
t1.StopRedraw


'Dim bjs As List(of String) = dt.Getvalues("年级|班级|考试期数")
'dt.GetUniqueValues
'For Each flt In bjs
'Dim f As New Filler
'f.SourceTable =DataTables("科目表")
'f.DataTable = t1.DataTable
'f.Fill()
't1.DataTable.ReplaceFor("考试期数",flt,"考试期数 Is null")
't1.DataTable.ReplaceFor("班级",flt,"班级 Is null")
't1.DataTable.ReplaceFor("年级",flt,"年级 Is null")
'Next
Dim bjs As List(of String()) = dt.Getvalues("年级|班级|考试期数")
For Each bj As String() In bjs
    Dim f As New Filler
    f.SourceTable =DataTables("科目表")
    f.DataTable = t1.DataTable
    f.Fill()
    t1.DataTable.ReplaceFor("考试期数",bj(2),"考试期数 Is null")
    t1.DataTable.ReplaceFor("班级",bj(1),"班级 Is null")
    t1.DataTable.ReplaceFor("年级",bj(0),"年级 Is null")
Next

 


For Each dc As Col In t1.Cols
    If dc.Index > 1 Then
        For Each dr As Row In t1.Rows
            flt = "班级 = '" & dr("班级") & "' and 年级 = '" & dr("年级") & "' and 考试期数 = '" & dr("考试期数") & "'And " & dic(dr("科目"))
            If dc.Index = 4 Then
                flt+ = " < "  &  dc.name.split("分")(0)
            ElseIf dc.Index =Tables("统计").Cols.count -1 Then
                flt+ = " >= "  &  dc.name.split("分")(0)
            ElseIf dc.Index > 4
                flt+ = " >= " &  dc.name.split("分")(0) & " And " & dic(dr("科目")) & " < " & dc.Name.split("-")(1).split("分")(0)
            Else
                Continue For
            End If
            dr(dc.name) =dt.Compute("Count(姓名)",flt)
        Next
    End If
Next


t1.DataTable.SysStyles("EmptyArea").BackColor = Color.White
t1.ResumeRedraw
MainTable = t1
Tables("统计_Table2").DataSource = DataTables("统计")


 回到顶部
总数 55 1 2 3 4 5 6 下一页