With Tables("多栏式明细账_Table1")
For i As Integer = .Rows.count - 1 To 0 Step -1
.Rows(i).Delete
Next
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.Build(True)
.DataSource = b.BuildDataSource
Dim ckb As DataTable = e.Form.Controls("Table1").Table.DataTable
If ckb.DataCols.Contains("借方金额") = False And ckb.DataCols.Contains("贷方金额") = False Then
ckb.DataCols.add("借方金额", Gettype(Double))
ckb.DataCols.add("贷方金额", Gettype(Double))
End If
DataTables("多栏式明细账_Table1").DataCols("借方金额").SetFormat("#,###.##")
DataTables("多栏式明细账_Table1").DataCols("贷方金额").SetFormat("#,###.##")
For Each dr As DataRow In DataTables("多栏式明细账_Table1").DataRows
Dim z As Double = 0
For Each c As Col In .Cols
Dim a As Double
If c.name = "年" OrElse 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
a = iif(dr.IsNull(c.name),0,dr(c.name))
z = z + a
End If
Next
dr("借方金额") = z
Next
Dim das As List(Of DataRow) = DataTables("多栏式明细账_Table1").Select("[年] Is Not Null And [月] Is Not Null And [日] Is Not Null")
For Each da As DataRow In das
Dim d As Date = New Date(da("年"),da("月"),da("日"))
If da.IsNull("年") = False And da.IsNull("月") = False And da.IsNull("日") = False And da.IsNull("借方金额") Then
da("贷方金额") = DataTables("财务数据源").Compute("Sum(贷方金额)","[日期] = #" & d & "# And [会计科目辅助列] = '管理费用' And [借或贷] = '贷'")
End If
Next
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 = "摘要" Then
e.Cancel = True
Else
s = s & "," & c.name
DataTables("多栏式明细账_Table1").DataCols(c.name).SetFormat("#,###.##")
End If
Next
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 = "年" OrElse c.name = "月" OrElse c.name = "日" OrElse c.name = "字号" OrElse c.name = "凭证号" OrElse c.name = "摘要" Then
e.Cancel = True
Else
r(c.name) = .Compute("Sum(" & c.name & ")",f)
End If
Next
End If
Next
Dim sa As String = "年|35|月|30|日|30|字号|35|凭证号|35|摘要|250|[其它]"
Dim ct As WinForm.Table = e.Form.Controls("Table1")
Dim t As Table = ct.Table
Dim str As String
Dim w As Integer = 0
Dim w2 As Integer = (ct.Width - 35 * 3 - 30 * 2 - 250) / (t.cols.Count - 6)
If w2 > 0 Then
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 = "摘要" Then
e.Cancel = True
Else
str = str & "|" & c.name & "|" & w2
End If
Next
sa = sa.replace("[其它]",str.trim("|"))
t.SetColVisibleWidth(sa)
End If
.AutoSizeRows()
End With
加好友 
发短信
Post By：2021/6/23 23:26:00 [显示全部帖子]
此主题相关图片如下：20210623.png
