Foxtable(狐表) → 用户栏目 → 专家坐堂 → [求助]数据提取汇总问题


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

主题：[求助]数据提取汇总问题
帅哥哟，离线，有人找我吗？
cqlpjks
 小大  11楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：一尾狐 帖子：407 积分：3194 威望：0 精华：0 注册：2011/2/28 11:57:00 		  发帖心情 Post By：2020/12/21 15:22:00 [只看该作者]

那不合并横向合并单元格，这样：
图片点击可在新窗口打开查看此主题相关图片如下：汇总统计结果2.png
图片点击可在新窗口打开查看如何添加代码，请指教。谢谢！

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


加好友 发短信
等级：狐神 帖子：4628 积分：33772 威望：0 精华：0 注册：2008/8/31 22:44:00 		  发帖心情 Post By：2020/12/21 15:43:00 [只看该作者]

例如：
'''
Dim dws() As String = {"梁平中学","红旗中学","梁平一中","实中双桂","屏锦中学","袁驿中学","福禄中学"}
Dim jb As New SQLJoinTableBuilder("查询表1","成绩库")
jb.AddCols("年级","学科","Cstr(大袋40份) as " & dws(0) & "_大袋40份","大袋30份 as " & dws(0) & "_大袋30份","小袋5份 as " & dws(0) & "_小袋5份","[大袋40份] * 40 + [大袋30份] * 30 + [小袋5份] * 5 as " & dws(0) & "_合计")
jb.Filter = "年级='2018' and 单位='" & dws(0) & "'"
Dim d As fxDataSource = jb.BuildDataSource()
For i As Integer = 1 To dws.Length - 1
    Dim jb1 As New SQLJoinTableBuilder("查询表1","成绩库")
    jb1.AddCols("年级","学科","Cstr(大袋40份) as " & dws(i) & "_大袋40份","大袋30份 as " & dws(i) & "_大袋30份","小袋5份 as " & dws(i) & "_小袋5份","[大袋40份] * 40 + [大袋30份] * 30 + [小袋5份] * 5 as " & dws(i) & "_合计")
    jb1.Filter = "年级='2018' and 单位='" & dws(i) & "'"
    Dim d1 As fxDataSource = jb1.BuildDataSource()
    Dim Cols As String = "年级|学科"
    d.Combine(Cols.split("|"),d1,Cols.split("|"))
Next
d.Show("查询表1")
Tables("查询表1").AutoSizeCols
MainTable = Tables("查询表1")
Dim s() As Integer = {0,2,6,10,14,18,22,26}
Dim a() As String = {"a1","a2","a3","a4","a5","a6","a7","a8"}
Dim r As Row = Tables("查询表1").AddNew
For i As Integer = 0 To s.length - 1
    Tables("查询表1").Cols(s(i)).TextAlign = TextAlignEnum.Center
    r(Tables("查询表1").Cols(s(i)).Name) = a(i)
Next
Dim ii As Integer = Tables("查询表1").Rows.Count + 1
Dim tb As Table = Tables("查询表1")
tb.Grid.AllowMerging = C1.Win.C1FlexGrid.AllowMergingEnum.Custom
Dim rng As C1.Win.C1FlexGrid.CellRange = tb.Grid.GetCellRange(ii, 1, ii, 2)
tb.Grid.MergedRanges.Add(rng)
For Each i As Integer In s
    If i <> 0
        rng = tb.Grid.GetCellRange(ii, i+1, ii, i+4)
        tb.Grid.MergedRanges.Add(rng)
    End If
Next
 回到顶部
帅哥哟，离线，有人找我吗？
cqlpjks
 小大  13楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：一尾狐 帖子：407 积分：3194 威望：0 精华：0 注册：2011/2/28 11:57:00 		  发帖心情 Post By：2020/12/21 16:31:00 [只看该作者]

Dim a1s() As String = {"经办人","联系电话","分管领导","分管领导电话"}
For Each a1 As String In a1s    
    Dim s() As Integer = {0,2,6,10,14,18,22,26}
    Dim a() As String = {""& a1 &"","a2","a3","a4","a5","a6","a7","a8"}
    Dim r As Row = Tables("查询表1").AddNew
    For i As Integer = 0 To s.length - 1
        Tables("查询表1").Cols(s(i)).TextAlign = TextAlignEnum.Center
        r(Tables("查询表1").Cols(s(i)).Name) = a(i)
    Next
    Dim ii As Integer = Tables("查询表1").Rows.Count + 1
    Dim tb As Table = Tables("查询表1")
    tb.Grid.AllowMerging = C1.Win.C1FlexGrid.AllowMergingEnum.Custom
    Dim rng As C1.Win.C1FlexGrid.CellRange = tb.Grid.GetCellRange(ii, 1, ii, 2)
    tb.Grid.MergedRanges.Add(rng)
    For Each i As Integer In s
        If i <> 0
            rng = tb.Grid.GetCellRange(ii, i+1, ii, i+4)
            tb.Grid.MergedRanges.Add(rng)
        End If
    Next
Next
统计如下：
图片点击可在新窗口打开查看此主题相关图片如下：汇总统计结果3.png
图片点击可在新窗口打开查看
a2,a3,a4,a5,a6,a7,a8要分别是成绩库中对应的值，如何更改代码？请指教，谢谢！
 回到顶部
帅哥哟，离线，有人找我吗？
y2287958
 小大  14楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：狐神 帖子：4628 积分：33772 威望：0 精华：0 注册：2008/8/31 22:44:00 		  发帖心情 Post By：2020/12/21 16:38:00 [只看该作者]

无语了，2011年注册的小狐

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


加好友 发短信
等级：狐神 帖子：4628 积分：33772 威望：0 精华：0 注册：2008/8/31 22:44:00 		  发帖心情 Post By：2020/12/21 17:20:00 [只看该作者]

'''
Dim dws() As String = {"梁平中学","红旗中学","梁平一中","实中双桂","屏锦中学","袁驿中学","福禄中学"}
Dim jb As New SQLJoinTableBuilder("查询表1","成绩库")
jb.AddCols("年级","学科","Cstr(大袋40份) as " & dws(0) & "_大袋40份","大袋30份 as " & dws(0) & "_大袋30份","小袋5份 as " & dws(0) & "_小袋5份","[大袋40份] * 40 + [大袋30份] * 30 + [小袋5份] * 5 as " & dws(0) & "_合计")
jb.Filter = "年级='2018' and 单位='" & dws(0) & "'"
Dim d As fxDataSource = jb.BuildDataSource()
For i As Integer = 1 To dws.Length - 1
    Dim jb1 As New SQLJoinTableBuilder("查询表1","成绩库")
    jb1.AddCols("年级","学科","Cstr(大袋40份) as " & dws(i) & "_大袋40份","大袋30份 as " & dws(i) & "_大袋30份","小袋5份 as " & dws(i) & "_小袋5份","[大袋40份] * 40 + [大袋30份] * 30 + [小袋5份] * 5 as " & dws(i) & "_合计")
    jb1.Filter = "年级='2018' and 单位='" & dws(i) & "'"
    Dim d1 As fxDataSource = jb1.BuildDataSource()
    Dim Cols As String = "年级|学科"
    d.Combine(Cols.split("|"),d1,Cols.split("|"))
Next
d.Show("查询表1")

jb = New SQLJoinTableBuilder("查询表2","成绩库")
jb.AddCols("单位","经办人","联系电话","分管领导","分管领导电话","0 as 排序")
jb.Filter = "年级='2018' and 联系电话 is not null and 分管领导电话 is not null"
jb.Distinct = True
Dim dt As DataTable = jb.Build(True)
For Each dr As DataRow In dt.DataRows
    dr("排序") = Array.IndexOf(dws,dr("单位"))
Next

Dim s() As Integer = {0,2,6,10,14,18,22,26}
For i As Integer = 0 To s.length - 1
    Tables("查询表1").Cols(s(i)).TextAlign = TextAlignEnum.Center
Next
For i As Integer = 1 To dt.DataCols.Count - 2
    Dim lst As List(of String) = dt.GetValues(dt.DataCols(i).name,"","排序")
    Dim r As Row = Tables("查询表1").AddNew
    r(Tables("查询表1").Cols(s(0)).Name) = dt.DataCols(i).name
    For i1 As Integer = 1 To s.length - 1
        r(Tables("查询表1").Cols(s(i1)).Name) = lst(i1-1)
    Next
    Dim ii As Integer = Tables("查询表1").Rows.Count + 1
    Dim tb As Table = Tables("查询表1")
    tb.Grid.AllowMerging = C1.Win.C1FlexGrid.AllowMergingEnum.Custom
    Dim rng As C1.Win.C1FlexGrid.CellRange = tb.Grid.GetCellRange(ii, 1, ii, 2)
    tb.Grid.MergedRanges.Add(rng)
    For Each i2 As Integer In s
        If i2 <> 0
            rng = tb.Grid.GetCellRange(ii, i2+1, ii, i2+4)
            tb.Grid.MergedRanges.Add(rng)
        End If
    Next
Next
Tables("查询表1").AutoSizeCols
MainTable = Tables("查询表1")
 回到顶部
帅哥哟，离线，有人找我吗？
cqlpjks
 小大  16楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：一尾狐 帖子：407 积分：3194 威望：0 精华：0 注册：2011/2/28 11:57:00 		  发帖心情 Post By：2020/12/21 17:40:00 [只看该作者]

Dim dws() As String = {"梁平中学","红旗中学","梁平一中","实中双桂","屏锦中学","袁驿中学","福禄中学"}
Dim a1s() As String = {"经办人","联系电话","分管领导","分管领导电话"}
Dim jb As New SQLJoinTableBuilder("查询表1","成绩库")
jb.AddCols("年级","学科","Cstr(大袋40份) as " & dws(0) & "_大袋40份","大袋30份 as " & dws(0) & "_大袋30份","小袋5份 as " & dws(0) & "_小袋5份","[大袋40份] * 40 + [大袋30份] * 30 + [小袋5份] * 5 as " & dws(0) & "_合计")
jb.Filter = "年级='2018' and 单位='" & dws(0) & "'"
Dim d As fxDataSource = jb.BuildDataSource()
For i As Integer = 1 To dws.Length - 1
    Dim jb1 As New SQLJoinTableBuilder("查询表1","成绩库")
    jb1.AddCols("年级","学科","Cstr(大袋40份) as " & dws(i) & "_大袋40份","大袋30份 as " & dws(i) & "_大袋30份","小袋5份 as " & dws(i) & "_小袋5份","[大袋40份] * 40 + [大袋30份] * 30 + [小袋5份] * 5 as " & dws(i) & "_合计")
    jb1.Filter = "年级='2018' and 单位='" & dws(i) & "'"
    Dim d1 As fxDataSource = jb1.BuildDataSource()
    Dim Cols As String = "年级|学科"
    d.Combine(Cols.split("|"),d1,Cols.split("|"))
Next
d.Show("查询表1")
Tables("查询表1").AutoSizeCols
MainTable = Tables("查询表1")
For Each a1 As String In a1s
    Dim dr2 As DataRow = DataTables("成绩库").Find("单位 =  '梁平中学'  And 年级 = '2018' ")
    Dim dr3 As DataRow = DataTables("成绩库").Find("单位 =  '红旗中学'  And 年级 = '2018' ")
    Dim dr4 As DataRow = DataTables("成绩库").Find("单位 =  '梁平一中'  And 年级 = '2018' ")
    Dim dr5 As DataRow = DataTables("成绩库").Find("单位 =  '实中双桂'  And 年级 = '2018' ")
    Dim dr6 As DataRow = DataTables("成绩库").Find("单位 =  '屏锦中学'  And 年级 = '2018' ")
    Dim dr7 As DataRow = DataTables("成绩库").Find("单位 =  '袁驿中学'  And 年级 = '2018' ")
    Dim dr8 As DataRow = DataTables("成绩库").Find("单位 =  '福禄中学'  And 年级 = '2018' ")
    Dim a2 As String = dr2(""& a1 &"")
    Dim a3 As String = dr3(""& a1 &"")
    Dim a4 As String = dr4(""& a1 &"")
    Dim a5 As String = dr5(""& a1 &"")
    Dim a6 As String = dr6(""& a1 &"")
    Dim a7 As String = dr7(""& a1 &"")
    Dim a8 As String = dr8(""& a1 &"")
    Dim s() As Integer = {0,2,6,10,14,18,22,26}
    Dim a() As String = {""& a1 &"","" & a2 & "","" & a3 & "","" & a4 & "","" & a5 & "","" & a6 & "","" & a7 & "","" & a8 & ""}
    Dim r As Row = Tables("查询表1").AddNew
    For i As Integer = 0 To s.length - 1
        Tables("查询表1").Cols(s(i)).TextAlign = TextAlignEnum.Center
        r(Tables("查询表1").Cols(s(i)).Name) = a(i)
    Next
    Dim ii As Integer = Tables("查询表1").Rows.Count + 1
    Dim tb As Table = Tables("查询表1")
    tb.Grid.AllowMerging = C1.Win.C1FlexGrid.AllowMergingEnum.Custom
    Dim rng As C1.Win.C1FlexGrid.CellRange = tb.Grid.GetCellRange(ii, 1, ii, 2)
    tb.Grid.MergedRanges.Add(rng)
    For Each i As Integer In s
        If i <> 0
            rng = tb.Grid.GetCellRange(ii, i+1, ii, i+4)
            tb.Grid.MergedRanges.Add(rng)
        End If
    Next
Next
执行结果：
图片点击可在新窗口打开查看此主题相关图片如下：汇总统计结果4.png
图片点击可在新窗口打开查看
此代码已经解决上面的问题了，但代码有点复杂。如何简化代码中的红色部分？请指教，谢谢！
 回到顶部
帅哥哟，离线，有人找我吗？
cqlpjks
 小大  17楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：一尾狐 帖子：407 积分：3194 威望：0 精华：0 注册：2011/2/28 11:57:00 		  发帖心情 Post By：2020/12/21 17:44:00 [只看该作者]

谢谢！不好意思，刚才没看到回复，又发了一个请教。

 回到顶部
总数 17 上一页 1 2
RSS2.0 | Xhtml无图版 | Xslt无图版 Copyright © 2000 - 2018 foxtable.com Tel: 4000-810-820 粤ICP备11091905号
   Powered By Dvbbs Version 8.3.0
页面执行时间 0.04712 秒, 4 次数据查询