多栏式会计账 - 专家坐堂 - Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

打开生成的统计表1的表结构看看交叉统计生成的统计表真正列名是怎么样的，显示的都是标题

我明白你的意思了，不存在借方金额名称，你写的借方金额应是借方合计吧？

[此贴子已经被作者于2021/6/3 17:28:44编辑过]

是列名：借方金额_1、借方金额_2、...

不对，以管理费用下的二级科目名称为列名，有多少个二级科目就设多少列，所以我总感觉你的代码不对。

添加的动态列名在贷方合计列后，前面的列都是固定的。

没看懂您的意思。自己的业务自己处理吧

红色标注代码报错，这用循环不适当，但动态添加的列名就是管理费用二级科目的名称，这里应怎么写？

Dim b As New CrossTableBuilder("统计表1",DataTables("财务数据源"))

b.HGroups.AddDef("日期",DateGroupEnum.Year,"年")

b.HGroups.AddDef("日期","月")

b.HGroups.AddDef("字号")

b.HGroups.AddDef("凭证号")

b.HGroups.AddDef("摘要")

b.VGroups.AddDef("二级科目")

b.Totals.AddDef("借方金额")

b.Build()

Tables("多栏式明细账_Table1").DataSource = b.BuildDataSource

Dim a As Table = Tables("多栏式明细账_Table1")

Dim g As Subtotalgroup

a.SubtotalGroups.Clear()

Dim s As String = ""

Dim nms As List(Of String) = DataTables("财务数据源").SQLGetValues("[二级科目]","[会计科目辅助列] ='" & e.form.controls("combobox1").items(0) & "' And [日期] >= #" & e.Form.Controls("StartDate").Value & "# And [日期] <= #" & e.Form.Controls("EndDate").Value & "#" ,"二级科目")

For Each nm As String In nms

s = s & ","" & nm & """

g = New Subtotalgroup

g.GroupOn = "月"

g.TotalOn = s

g.OrderByTotal = True

g.Caption = "本月发生额"

a.SubtotalGroups.Add(g)

g = New Subtotalgroup

g.GroupOn = "月"

g.Caption = "{0}月"

a.SubtotalGroups.Add(g)

a.Sort = "年,月"

a.Subtotal(True)

Dim r As Row

For i As Integer = 0 To a.Rows.Count(True) - 1

r = a.Rows(i,True)

If r.IsGroup AndAlso r("年") Like "*月" '如果是分组行

Dim f As String = "年 = " & a.Rows(i-2,True)("年") & " And 月 <= " & r("年").trim("月")

r("年") = "本年累计"

For Each c As Col In a.Cols

If c.Name = nm Then

r(c.Name) = a.Compute("Sum(" & c.Name & ")",f)

End If

Dim ckb As DataTable = a.DataTable

If ckb.DataCols.Contains("借或贷") = False And ckb.DataCols.Contains("借方合计") = False And ckb.DataCols.Contains("贷方合计") = False Then

ckb.DataCols.add("借或贷", Gettype(String))

ckb.DataCols.add("借方合计", Gettype(Double))

ckb.DataCols.add("贷方合计", Gettype(Double))

End If

ckb.DataCols("借方合计").SetFormat("#,###.##")

ckb.DataCols("贷方合计").SetFormat("#,###.##")

a.SetHeaderRowHeight(45)

Dim drs As List(Of DataRow) = ckb.Select("[凭证号] Is Not Null")

For i As Integer = 1 To drs.count - 1

Dim Sum As Integer

For Each nm As String In nms

Sum = Sum + drs(i)(nm)

drs(i)("借或贷") = "借"

drs(i)("借方合计") = Sum

drs(drs.count - 1)("贷方合计") = DataTables("财务数据源").Compute("Sum(贷方金额)","[会计科目辅助列] ='" & e.form.controls("combobox1").items(0) & "' And [日期] >= #" & e.Form.Controls("StartDate").Value & "# And [日期] <= #" & e.Form.Controls("EndDate").Value & "#" ,"二级科目"))

[此贴子已经被作者于2021/6/4 13:48:14编辑过]

到命令窗口单独执行下面代码，然后打开统计表1，打开表结构：http://www.foxtable.com/webhelp/topics/0193.htm，看看统计表真正的列名和标题名。不出意外，借方金额_1、借方金额_2、...才是列名，二级科目都是标题名，代码处理要使用列名

Dim b As New CrossTableBuilder("统计表1",DataTables("财务数据源"))

b.HGroups.AddDef("日期",DateGroupEnum.Year,"年")

b.HGroups.AddDef("日期","月")

b.HGroups.AddDef("字号")

b.HGroups.AddDef("凭证号")

b.HGroups.AddDef("摘要")

b.VGroups.AddDef("二级科目")

b.Totals.AddDef("借方金额")

b.Build()

此主题相关图片如下：20210623.png

我想把由统计表生成的统计列自动行宽，即与S集合对应数组名称的列自动列宽，我这样写得出截图的效果，要控制的列并没平均自动列宽。

Dim s As String = ""

For Each c As Col In .Cols

If c.name = "年" OrElse c.name = "月" OrElse c.name = "日" OrElse c.name = "字号" OrElse c.name = "凭证号" OrElse c.name = "摘要" OrElse c.name = "合计" Then

e.Cancel = True

Else

s = s & "," & c.name

End If

Dim ct As WinForm.Table = e.Form.Controls("Table1")

Dim t As Table = ct.Table

Dim str As String

Dim w As Integer = 0

For Each c As Col In t.Cols

If c.Visible Then

If s.Contains(c.Name) Then

str = str & "|" & c.Name & "|[width]"

Else

If c.name = "年" OrElse c.name = "月" OrElse c.name = "日" OrElse c.name = "字号" OrElse c.name = "凭证号" Then

str = str & "|" & c.Name & "|35"

w + = 35

ElseIf c.Name = "摘要" Then

str = str & "|" & c.Name & "|300"

w + = 300

ElseIf c.Width = -1 Then

str = str & "|" & c.Name & "|60"

w + = 60

End If

Dim w2 As Integer = (ct.Width - w) / s.Count

str = str.trim("|").Replace("[width]",w2)

t.SetColVisibleWidth(str)

完整的代码改为这样也一样效果：

With Tables("多栏式明细账_Table1")

For i As Integer = .Rows.count - 1 To 0 Step -1

.Rows(i).Delete

Dim g As Subtotalgroup

.SubtotalGroups.Clear()

Dim cmd As New SQLCommand

Dim dt As DataTable

cmd.CommandText = "Select 日期,字号,凭证号,摘要,二级科目,借方金额 fr om {财务数据源} Where [日期] >= #" & e.Form.Controls("StartDate").Value & "# And [日期] <= #" & e.Form.Controls("EndDate").Value & "# And [一级科目] = '" & e.Form.Controls("combobox1").Value & "'"

dt = cmd.ExecuteReader()

Dim b As New CrossTableBuilder("统计表1",dt)

b.HGroups.AddDef("日期",DateGroupEnum.Year,"年")

b.HGroups.AddDef("日期",DateGroupEnum.Month,"月")

b.HGroups.AddDef("日期",DateGroupEnum.Day,"日")

b.HGroups.AddDef("字号")

b.HGroups.AddDef("凭证号")

b.HGroups.AddDef("摘要")

b.VGroups.AddDef("二级科目","{0}")

b.Totals.AddDef("借方金额","借方金额")

b.HorizontalTotal = True

b.OrderByTotal = True

b.Build(True)

.DataSource = b.BuildDataSource

Dim s As String = ""

For Each c As Col In .Cols

If c.Name Like "借方金额_*" Then

s = s & "," & c.name

End If

MessageBox.Show(s)

g = New Subtotalgroup

g.GroupOn = "月"

g.TotalOn = s.Trim(",")

g.Caption = "本月发生额"

.SubtotalGroups.Add(g)

g = New Subtotalgroup

g.GroupOn = "月"

g.Caption = "{0}月"

.SubtotalGroups.Add(g)

.Sort = "年,月"

.Subtotal(True)

Dim r As Row

For i As Integer = 0 To .Rows.Count(True) - 1

r = .Rows(i,True)

If r.IsGroup AndAlso r("年") Like "*月" '如果是分组行

Dim f As String = "年 = " & .Rows(i-2,True)("年") & " And 月 <= " & r("年").trim("月")

r("年") = "本年累计"

For Each c As Col In .Cols

If c.name = c.Name Like "借方金额_*" Then

r(c.name) = .Compute("Sum(" & c.name & ")",f)

End If

Dim ct As WinForm.Table = e.Form.Controls("Table1")

Dim t As Table = ct.Table

Dim str As String

Dim w As Integer = 0

For Each c As Col In t.Cols

If c.Visible Then

If s.Trim(",").Contains(c.Name) Then

str = str & "|" & c.Name & "|[width]"

Else

If c.name = "年" OrElse c.name = "月" OrElse c.name = "日" OrElse c.name = "字号" OrElse c.name = "凭证号" Then

str = str & "|" & c.Name & "|35"

w + = 35

ElseIf c.Name = "摘要" Then

str = str & "|" & c.Name & "|300"

w + = 300

ElseIf c.Width = -1 Then

str = str & "|" & c.Name & "|60"

w + = 60

End If

Dim w2 As Integer = (ct.Width - w) / s.Trim(",").Count

str = str.trim("|").Replace("[width]",w2)

t.SetColVisibleWidth(str)

End With

主题：多栏式会计账