专家坐堂-[求助]专业报表_汇总模式代码Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.net/bbs/index.asp)
--  专家坐堂  (http://foxtable.net/bbs/list.asp?boardid=2)
----  [求助]专业报表_汇总模式代码  (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=139900)

--  作者：hanzhang98
--  发布时间：2019/8/24 16:55:00
--  [求助]专业报表_汇总模式代码
老师好. 如何将如下设置数值显示格式的代码增加到帮助示例关于"分组统计-- 汇总模式" 的代码中,  . 谢谢.

IF 统计数值 <> 0 Then \'黄底色部分不会写,请老师帮助指导
  rt.Rows(单元格).Text = Format(hqnj ,"#,##0.00")
  rt.Rows(单元格).Style.Font = New Font("宋体", 8)
  rt.Rows(单元格).Style.TextAlignHorz = prt.AlignHorzEnum.Center
Else
  rt.Rows(单元格).Text = Format(hqnj ," -")
End If

以下是帮助示例中关于"分组统计-- 汇总模式" 的代码:

Dim doc As New PrintDoc
Dim rt As New prt.RenderTable
Dim tb as Table = Tables("订单")
Dim ColNames As String() = New String(){"产品", "客户","单价", "数量", "金额"}
rt.Style.GridLines.All = New prt.Linedef(Color.Gray)
rt.CellStyle.Spacing.All = 1
For c As integer = 0 To ColNames.Length -1
rt.Cells(0,c).Text = ColNames(c)
rt.Cols(c).Width = tb.Cols(ColNames(c)).PrintWidth
rt.Cells(1, c).Text = "[Fields!" & ColNames(c) & ".Value]"
Next
rt.Cells(2,0).Text ="[Fields!客户.Value]小计"
rt.Cells(2,0).SpanCols = 3
rt.Cells(2,3).Text = "[Aggregates!数量小计2.Value]"
rt.Cells(2,4).Text = "[Aggregates!金额小计2.Value]"
rt.Cells(3,0).Text ="[Fields!产品.Value]小计"
rt.Cells(3,0).SpanCols = 3
rt.Cells(3,3).Text = "[Aggregates!数量小计1.Value]"
rt.Cells(3,4).Text = "[Aggregates!金额小计1.Value]"
rt.Cells(4,0).Text ="总计"
rt.Cells(4,0).SpanCols = 3
rt.Cells(4,3).Text = "[Aggregates!数量总计.Value]"
rt.Cells(4,4).Text = "[Aggregates!金额总计.Value]"
rt.Rows(0).Style.TextAlignVert = prt.AlignVertEnum.Center
rt.RowGroups(0,1).Header = prt.TableHeaderEnum.All
With rt.RowGroups(1,3)
.DataBinding.DataSource = BindTables("订单")
.DataBinding.Grouping.Expressions.Add("Fields!产品.Value")
doc.DataSchema.Aggregates.Add(New prt.DataBinding.Aggregate("数量小计1", "Fields!数量.Value", .DataBinding, 1, 0))
doc.DataSchema.Aggregates.Add(New prt.DataBinding.Aggregate("金额小计1", "Fields!金额.Value", .DataBinding, 1, 0))
doc.DataSchema.Aggregates.Add(New prt.DataBinding.Aggregate("数量总计", "Fields!数量.Value", .DataBinding, 0, 0))
doc.DataSchema.Aggregates.Add(New prt.DataBinding.Aggregate("金额总计", "Fields!金额.Value", .DataBinding, 0, 0))
End With
With rt.RowGroups(1,2)
.DataBinding.DataSource = BindTables("订单")
.DataBinding.Grouping.Expressions.Add("Fields!客户.Value")
doc.DataSchema.Aggregates.Add(New prt.DataBinding.Aggregate("数量小计2", "Fields!数量.Value", .DataBinding, 1, 0))
doc.DataSchema.Aggregates.Add(New prt.DataBinding.Aggregate("金额小计2", "Fields!金额.Value", .DataBinding, 1, 0))
End With
rt.RowGroups(1,1).DataBinding.DataSource = BindTables("订单")
doc.Body.Children.Add(rt)
doc.Preview()

[此贴子已经被作者于2019/8/24 17:02:15编辑过]

--  作者：有点蓝
--  发布时间：2019/8/24 17:12:00
--
这种模式不支持使用格式，没有办法。

--  作者：hanzhang98
--  发布时间：2019/8/24 17:21:00
--

以下是引用有点蓝在2019/8/24 17:12:00的发言：
这种模式不支持使用格式，没有办法。
谢谢老师. 请教老师. 能达到这种汇总统计效果,又能设置格式的代码如何写, 还请老师帮助指导.谢谢.

--  作者：有点蓝
--  发布时间：2019/8/24 17:38:00
--
参考：http://www.foxtable.com/webhelp/topics/2239.htm
[此贴子已经被作者于2019/8/24 17:38:25编辑过]

--  作者：hanzhang98
--  发布时间：2019/8/24 17:50:00
--
老师好. 如下参考：http://www.foxtable.com/webhelp/topics/2239.htm 帮助示例代码中,如何实现 " 先按 <客户>分组进行小计, 再按<产品>分组进行合计, 最后才是总计" . 请老师指导. 谢谢.

Dim doc As New PrintDoc
Dim rt As New Prt.RenderTable
Dim rx As prt.RenderText
Dim cnt As Integer
Dim tbl As Table = Tables("订单")
Dim drs As List(Of DataRow)
Dim pds As List(Of String) = tbl.DataTable.GetValues("产品",tbl.Filter)
rt.Style.GridLines.All = New prt.LineDef(0.3,Color.LightGray)
rt.CellStyle.Spacing.All = 1
rt.Style.Font = tbl.Font
For c As Integer = 0 To tbl.Cols.Count - 1 \'生成列标题
rt.Cells(0,c).Text = tbl.Cols(c).Name
rt.Cols(c).Width = tbl.Cols(c).PrintWidth
Next
For Each pd As String In pds \'分产品打印
If tbl.Filter > "" Then
drs = tbl.DataTable.Select("产品 = \'" & pd & "\' And " & tbl.Filter)
Else
drs = tbl.DataTable.Select("产品 = \'" & pd & "\'" )
End If
For Each dr As DataRow In drs \'逐行打印此产品的订单
cnt = rt.Rows.Count
For c As Integer = 0 To tbl.Cols.Count - 1
rt.Cells(cnt, c).Text = dr(tbl.Cols(c).Name)
Next
Next
cnt = rt.Rows.Count
rt.Cells(cnt, 0).Text = "小计 " & pd \'打印产品的小计
rt.Cells(cnt,5).Text = tbl.compute("Sum(数量)", "产品 = \'" & pd & "\'")
Next
cnt = rt.Rows.Count
rt.Cells(cnt, 0).Text = "合计 " \'打印合计
rt.Cells(cnt, 5).Text = tbl.compute("Sum(数量)")
doc.Body.Children.Add(rt)
doc.preview()

--  作者：有点蓝
--  发布时间：2019/8/25 20:33:00
--
外面套多一次循环：

Dim pds2 As List(Of String) = tbl.DataTable.GetValues("客户",tbl.Filter)

For Each pd2 As String In pds2 \'分客户打印
对客户的遍历处理
Dim pds As List(Of String) = tbl.DataTable.GetValues("产品",tbl.Filter & " and 客户=\'" & pd2 & "\'")
For Each pd As String In pds \'分产品打印

--  作者：hanzhang98
--  发布时间：2019/8/26 11:10:00
--
老师好。错误提示“未知编译错误”。请老师帮助指正。谢谢。
Dim doc As New PrintDoc
Dim rt As New Prt.RenderTable
Dim rx As prt.RenderText
Dim cnt As Integer
Dim tbl As Table = Tables("订单")
Dim drs As List(Of DataRow)

Dim pds As List(Of String) = tbl.DataTable.GetValues("产品",tbl.Filter)
Dim pds2 As List(Of String) = tbl.DataTable.GetValues("客户",tbl.Filter)

rt.Style.GridLines.All = New prt.LineDef(0.3,Color.LightGray)
rt.CellStyle.Spacing.All = 1
rt.Style.Font = tbl.Font
For c As Integer = 0 To tbl.Cols.Count - 1 \'生成列标题
rt.Cells(0,c).Text = tbl.Cols(c).Name
rt.Cols(c).Width = tbl.Cols(c).PrintWidth
Next

For Each pd2 As String In pds2 \'分客户打印
If tbl.Filter > "" Then
drs = tbl.DataTable.Select("客户 = \'" & pd2 & "\' And " & tbl.Filter)
Else
drs = tbl.DataTable.Select("客户 = \'" & pd2 & "\'" )
End If
cnt = rt.Rows.Count
rt.Cells(cnt, 0).Text = pd2 & " 小计" \'打印客户的小计
rt.Cells(cnt,5).Text = tbl.compute("Sum(数量)", "产品 = \'" & pd2 & "\'")

Dim pds As List(Of String) = tbl.DataTable.GetValues("产品",tbl.Filter & " and 客户=\'" & pd2 & "\'") \'对客户的遍历处理
For Each pd As String In pds \'分产品打印
If tbl.Filter > "" Then
drs = tbl.DataTable.Select("产品 = \'" & pd & "\' And " & tbl.Filter)
Else
drs = tbl.DataTable.Select("产品 = \'" & pd & "\'" )
End If
cnt = rt.Rows.Count
rt.Cells(cnt, 0).Text = pd & " 合计" \'打印产品的合计
rt.Cells(cnt,5).Text = tbl.compute("Sum(数量)", "产品 = \'" & pd & "\'")
Next
Next
cnt = rt.Rows.Count
rt.Cells(cnt, 0).Text = "总计 " \'打印总计
rt.Cells(cnt, 5).Text = tbl.compute("Sum(数量)")
doc.Body.Children.Add(rt)
doc.preview()

[此贴子已经被作者于2019/8/26 11:13:51编辑过]

--  作者：有点蓝
--  发布时间：2019/8/26 11:35:00
--

For Each pd2 As String In pds2 \'分客户打印
  pds = tbl.DataTable.GetValues("产品",tbl.Filter & " and 客户=\'" & pd2 & "\'") \'对客户的遍历处理
For Each pd As String In pds \'分产品打印
dim filter = ""
If tbl.Filter > "" Then
  filter = "客户=\'" & pd2 & "\' and 产品 = \'" & pd & "\' And " & tbl.Filter
Else
  filter = "客户=\'" & pd2 & "\' and 产品 = \'" & pd & "\'"
End If
drs = tbl.DataTable.Select(filter )
cnt = rt.Rows.Count
rt.Cells(cnt, 0).Text = pd & " 合计" \'打印产品的合计
rt.Cells(cnt,5).Text = tbl.compute("Sum(数量)", filter )
Next
cnt = rt.Rows.Count
rt.Cells(cnt, 0).Text = pd2 & " 小计" \'打印客户的小计
rt.Cells(cnt,5).Text = tbl.compute("Sum(数量)", "客户 = \'" & pd2 & "\'" & IIF(tbl.Filter > ""," and ","") & tbl.Filter)
Next

--  作者：hanzhang98
--  发布时间：2019/8/26 14:54:00
--
老师好。错误提示【语法错误：“And” 运算符前缺少操作数】

pds = tbl.DataTable.GetValues("产品",tbl.Filter & " and 客户=\'" & pd2 & "\'") \'对客户的遍历处理

[此贴子已经被作者于2019/8/26 18:31:46编辑过]

--  作者：有点蓝
--  发布时间：2019/8/26 15:41:00
--
这个表达式合成的问题自己查看处理即可

msgbox(filter )

msgbox("客户 = \'" & pd2 & "\'" & IIF(tbl.Filter > ""," and ","") & tbl.Filter )