- Foxtable(狐表) (http://foxtable.net/bbs/index.asp)
-- 专家坐堂 (http://foxtable.net/bbs/list.asp?boardid=2)
---- 移动开发中 获取统计数据页面费时，有没有办法提高效率呢？ (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=160947)
-- 发布时间：2021/2/27 11:28:00
-- 移动开发中 获取统计数据页面费时，有没有办法提高效率呢？
移动开发中 获取统计数据页面费时，且在打开页面时，服务器端会宕机，有没有办法提高效率呢？
页面代码如下：
Dim e As RequestEventArgs = args(0)
Dim wb As New WeUI
Dim cmd111 As New SQLCommand
cmd111.C \'记得设置数据源名称
cmd111.CommandText = "Sel ect * From {表彰}"
Dim dt As DataTable = cmd111.ExecuteReader
Dim zbmcs As List(Of String)
zbmcs=dt.GetValues("登记单位")
wb.AddPageTitle("","pageheader","表彰数据","专为管理信息化设计")
With wb.AddPanelGroup("","pg1","【" & e.Cookies("单位名称") & "】全部表彰数据")
Dim txt11 As String = "【个人表彰】" & dt.sqlcompute("count(获奖者)"," 表彰类别=\'个人表彰\'") & "人,【集体表彰】 " & dt.sqlcompute("count(获奖者)","表彰类别=\'集体表彰\'") & " 人."
Dim txt111 As String ="【国家级】" & dt.sqlcompute("count(获奖者)","获奖等级=\'国家级\'") & "人,"
Dim txt211 As String ="【省部级】" & dt.sqlcompute("count(获奖者)","获奖等级=\'省部级\'") & "人,"
Dim txt311 As String ="【地州级】" & dt.sqlcompute("count(获奖者)","获奖等级=\'地州级\'") & "人,"
Dim txt411 As String ="【县市级】" & dt.sqlcompute("count(获奖者)","获奖等级=\'县市级\'") & "人,"
With .Add("pn1","【表彰概况】",txt11 & txt111 & txt211 & txt311 & txt411,"")
.Badge= dt.sqlcompute("count(获奖者)")
End With
Dim txt1 As String ="【国家级】" & dt.sqlcompute("count(获奖者)","表彰类别=\'个人表彰\'and 获奖等级=\'国家级\'") & "人,"
Dim txt2 As String ="【省部级】" & dt.sqlcompute("count(获奖者)","表彰类别=\'个人表彰\'and 获奖等级=\'省部级\'") & "人,"
Dim txt3 As String ="【地州级】" & dt.sqlcompute("count(获奖者)","表彰类别=\'个人表彰\'and 获奖等级=\'地州级\'") & "人,"
Dim txt4 As String ="【县市级】" & dt.sqlcompute("count(获奖者)","表彰类别=\'个人表彰\'and 获奖等级=\'县市级\'") & "人,"
With .Add("pn1","【个人表彰】", txt1 & txt2 & txt3 & txt4,"")
.Badge= dt.sqlcompute("count(获奖者)","表彰类别=\'个人表彰\'")
End With
Dim txt12 As String ="【国家级】" & dt.sqlcompute("count(获奖者)","表彰类别=\'集体表彰\'and 获奖等级=\'国家级\'") & "人,"
Dim txt22 As String ="【省部级】" & dt.sqlcompute("count(获奖者)","表彰类别=\'集体表彰\'and 获奖等级=\'省部级\'") & "人,"
Dim txt32 As String ="【地州级】" & dt.sqlcompute("count(获奖者)","表彰类别=\'集体表彰\'and 获奖等级=\'地州级\'") & "人,"
Dim txt42 As String ="【县市级】" & dt.sqlcompute("count(获奖者)","表彰类别=\'集体表彰\'and 获奖等级=\'县市级\'") & "人,"
With .Add("pn1","【集体表彰】", txt12 & txt22 & txt32 & txt42,"")
.Badge= dt.sqlcompute("count(获奖者)","表彰类别=\'集体表彰\'")
End With
End With
For i As Integer = 0 To zbmcs.Count -1
With wb.AddPanelGroup("",zbmcs(i),"【" & zbmcs(i) & "】表彰情况:")
Dim txt11 As String = "【个人表彰】" & dt.sqlcompute("count(获奖者)"," 表彰类别=\'个人表彰\'and 登记单位=\'" & zbmcs(i) & "\'") & "人,【集体表彰】 " & dt.sqlcompute("count(获奖者)","表彰类别=\'集体表彰\'and 登记单位=\'" & zbmcs(i) & "\'") & " 人."
Dim txt111 As String ="【国家级】" & dt.sqlcompute("count(获奖者)","获奖等级=\'国家级\'and 登记单位=\'" & zbmcs(i) & "\'") & "人,"
Dim txt211 As String ="【省部级】" & dt.sqlcompute("count(获奖者)","获奖等级=\'省部级\'and 登记单位=\'" & zbmcs(i) & "\'") & "人,"
Dim txt311 As String ="【地州级】" & dt.sqlcompute("count(获奖者)","获奖等级=\'地州级\'and 登记单位=\'" & zbmcs(i) & "\'") & "人,"
Dim txt411 As String ="【县市级】" & dt.sqlcompute("count(获奖者)","获奖等级=\'县市级\'and 登记单位=\'" & zbmcs(i) & "\'") & "人,"
With .Add("pn1","【表彰概况】",txt11 & txt111 & txt211 & txt311 & txt411,"")
.Badge= dt.sqlcompute("count(获奖者)","登记单位=\'" & zbmcs(i) & "\'")
End With
Dim txt1 As String ="【国家级】" & dt.sqlcompute("count(获奖者)","表彰类别=\'个人表彰\'and 获奖等级=\'国家级\'and 登记单位=\'" & zbmcs(i) & "\'") & "人,"
Dim txt2 As String ="【省部级】" & dt.sqlcompute("count(获奖者)","表彰类别=\'个人表彰\'and 获奖等级=\'省部级\'and 登记单位=\'" & zbmcs(i) & "\'") & "人,"
Dim txt3 As String ="【地州级】" & dt.sqlcompute("count(获奖者)","表彰类别=\'个人表彰\'and 获奖等级=\'地州级\'and 登记单位=\'" & zbmcs(i) & "\'") & "人,"
Dim txt4 As String ="【县市级】" & dt.sqlcompute("count(获奖者)","表彰类别=\'个人表彰\'and 获奖等级=\'县市级\'and 登记单位=\'" & zbmcs(i) & "\'") & "人,"
With .Add("pn1","【个人表彰】", txt1 & txt2 & txt3 & txt4,"")
.Badge= dt.sqlcompute("count(获奖者)","表彰类别=\'个人表彰\'and 登记单位=\'" & zbmcs(i) & "\'")
End With
Dim txt122 As String ="【国家级】" & dt.sqlcompute("count(获奖者)","表彰类别=\'集体表彰\'and 获奖等级=\'国家级\'and 登记单位=\'" & zbmcs(i) & "\'") & "人,"
Dim txt222 As String ="【省部级】" & dt.sqlcompute("count(获奖者)","表彰类别=\'集体表彰\'and 获奖等级=\'省部级\'and 登记单位=\'" & zbmcs(i) & "\'") & "人,"
Dim txt322 As String ="【地州级】" & dt.sqlcompute("count(获奖者)","表彰类别=\'集体表彰\'and 获奖等级=\'地州级\'and 登记单位=\'" & zbmcs(i) & "\'") & "人,"
Dim txt422 As String ="【县市级】" & dt.sqlcompute("count(获奖者)","表彰类别=\'集体表彰\'and 获奖等级=\'县市级\'and 登记单位=\'" & zbmcs(i) & "\'") & "人,"
With .Add("pn1","【集体表彰】", txt122 & txt222 & txt322 & txt422,"")
.Badge= dt.sqlcompute("count(获奖者)","表彰类别=\'集体表彰\'and 登记单位=\'" & zbmcs(i) & "\'")
End With
End With
Next
With wb.AddButtonGroup("","btg2", False)
.Add("btn2", "返回首页","", "default.htm")
End With
e.WriteString(wb.Build) \'生成网页
-- 发布时间：2021/2/27 11:44:00
--
先对需要的结果做个分组统计：http://www.foxtable.com/webhelp/topics/0677.htm
然后根据统计结果查询输出
-- 发布时间：2021/2/27 11:52:00
-- 回复：（有点蓝）先对需要的结果做个分组统计：http:/...
不得行哦 我所有的字段都是字符 不是数字
\'定义一个GroupTableBuilder,名称为"统计表1",对订单表进行统计.
Dim b As New GroupTableBuilder("统计表1",DataTables("表彰"))
b.Groups.AddDef("登记单位") \'根据产品分组
b.Totals.AddDef("获奖者") \'对数量进行统计
b.Build \'生成统计表
MainTable = Tables("统计表1") \'打开生成的统计表
运行会提示如下错误：
.NET Framework 版本：4.0.30319.42000
Foxtable 版本：2020.5.29.8
错误所在事件：
详细错误信息：
从字符串“付国瑾”到类型“Decimal”的转换无效。
-- 发布时间：2021/2/27 14:01:00
--
在Totals集合中添加一个统计列的语法如下：
Totals.AddDef(Name)
Totals.AddDef(Name, NewName)
Totals.AddDef(Name, NewName, NewCaption)
Totals.AddDef(Name, Aggregate)
Totals.AddDef(Name, Aggregate, NewName)
Totals.AddDef(Name, Aggregate, NewName, NewCaption)
参数说明
Name: 字符型，指定统计列。
NewName: 字符型，用于给统计列指定一个新的名称，这个名称在统计表中显示。
NewCaption: 字符型，用于给统计列设置标题。
Aggregate： AggregateEnum枚举型，用于指定统计类型，默认是求和。
AggregateEnum型枚举的可选值：
Average：平均值
Count： 记录数
Max： 最大值
Min： 最小值
Sum： 累积值
b.Totals.AddDef("获奖者",AggregateEnum.Count")
-- 发布时间：2021/2/27 14:12:00
-- 回复：（有点蓝）在Totals集合中添加一个统计列的语法...
谢谢老师 现在可以筛选出数量了
Dim b As New GroupTableBuilder("统计表1",DataTables("表彰"))
b.Groups.AddDef("登记单位") \'根据产品分组
b.Filter = "[表彰类别] = \'个人表彰\' and [获奖等级]=‘国家级’ " \'仅统计客户为CS01的记录
b.Totals.AddDef("获奖者",AggregateEnum.Count) \'对数量进行统计
b.Filter = "[表彰类别] = \'集体表彰\' and [获奖等级]=‘国家级’ " \'仅统计客户为CS01的记录
b.Totals.AddDef("获奖者",AggregateEnum.Count) \'对数量进行统计
b.Build \'生成统计表
MainTable = Tables("统计表1") \'打开生成的统计表
按照上述写法 代码只认最后一个filter条件
有没有办法生成类似的效果呢
根据表彰类别=个人表彰 获奖等级=国家级 省部级 地州级 县市级
表彰类别=集体表彰 获奖等级=国家级 省部级 地州级 县市级
此主题相关图片如下：11.png
[此贴子已经被作者于2021/2/27 14:13:42编辑过]
-- 发布时间：2021/2/27 14:40:00
--
使用交叉统计
Dim b As New CrossTableBuilder("统计表1",DataTables("表彰"))
b.HGroups.AddDef("登记单位") \'添加客户列用于水平分组
b.VGroups.AddDef("表彰类别") \'添加产品列用于垂直分组
b.VGroups.AddDef("获奖等级") \'添加产品列用于垂直分组
b.Totals.AddDef("获奖者",AggregateEnum.Count) \'添加数量列用于统计
b.Build \'生成统计表
Maintable = Tables("统计表1") \'打开生成的统计表
b.VGroups.AddDef("表彰类别") \'添加产品列用于垂直分组
b.VGroups.AddDef("获奖等级") \'添加产品列用于垂直分组
b.Totals.AddDef("获奖者",AggregateEnum.Count) \'添加数量列用于统计
b.Build \'生成统计表
Maintable = Tables("统计表1") \'打开生成的统计表
-- 发布时间：2021/2/27 22:00:00
-- 回复：（有点蓝）使用交叉统计Dim b As&nbs...
谢谢老师
Dim b As New CrossTableBuilder("统计表1",DataTables("表彰"))
b.HGroups.AddDef("登记单位") \'添加客户列用于水平分组
b.VGroups.AddDef("表彰类别") \'添加产品列用于垂直分组
b.VGroups.AddDef("获奖等级") \'添加产品列用于垂直分组
b.Totals.AddDef("获奖者",AggregateEnum.Count) \'添加数量列用于统计
b.HorizontalTotal = True \'水平汇总
b.VerticalTotal= True \'垂直汇总
b.Build \'生成统计表
MainTable = Tables("统计表1") \'打开生成的统计表他
Tables("统计表1") .SaveExcel("C:\\test.xls","表彰统计")
运行得到
此主题相关图片如下：11.png
1、有没有办法在绿色框里添加一个列呢 ？单独合计个人表彰 总数 集体表彰总数
2有没有办法在导出的excel表中添加内外边框线 这样更加美观些
-- 发布时间：2021/2/28 20:51:00
--
1、生成统计表后自己添加合计表达式列，如：http://www.foxtable.com/webhelp/topics/3227.htm