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


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

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


加好友 发短信
等级：狐神 帖子：4723 积分：34352 威望：0 精华：0 注册：2008/8/31 22:44:00 		  发帖心情 Post By：2020/12/21 12:31:00 [显示全部帖子]

要是我选择这么干。
'''
Dim dws() As String = {"梁平中学","红旗中学","梁平一中","实中双桂","屏锦中学","袁驿中学","福禄中学"}
Dim jb As New SQLJoinTableBuilder("查询表1","成绩库")
jb.AddCols("年级","学科","大袋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("年级","学科","大袋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")
 回到顶部
帅哥哟，离线，有人找我吗？
y2287958
 小大  2楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：狐神 帖子：4723 积分：34352 威望：0 精华：0 注册：2008/8/31 22:44:00 		  发帖心情 Post By：2020/12/21 14:02:00 [显示全部帖子]

新增记录，合并单元格

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


加好友 发短信
等级：狐神 帖子：4723 积分：34352 威望：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
 回到顶部
帅哥哟，离线，有人找我吗？
y2287958
 小大  4楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级：狐神 帖子：4723 积分：34352 威望：0 精华：0 注册：2008/8/31 22:44:00 		  发帖心情 Post By：2020/12/21 16:38:00 [显示全部帖子]

无语了，2011年注册的小狐

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


加好友 发短信
等级：狐神 帖子：4723 积分：34352 威望：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")
 回到顶部
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 次数据查询