Foxtable(狐表)用户栏目专家坐堂 → 多栏式会计账


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

主题:多栏式会计账

美女呀,离线,留言给我吧!
采菊东篱下
  21楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:八尾狐 帖子:1881 积分:10374 威望:0 精华:0 注册:2019/4/6 8:45:00
  发帖心情 Post By:2021/7/4 20:58:00 [显示全部帖子]


图片点击可在新窗口打开查看此主题相关图片如下:qq图片20210704210632.png
图片点击可在新窗口打开查看


图片点击可在新窗口打开查看此主题相关图片如下:qq图片20210704204645.png
图片点击可在新窗口打开查看
不要红色标注的条件,表中多了借方金额_1这列,加了红色标注的列,没了贷方金额,这样写数据都有了,但不好看,最好把贷方金额放到最后一列,代码就要如32楼那样写,由于把贷方没二级科目,因此表中同样多了借方金额_1列,请教应如何完善这代码呢?
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 日期,字号,凭证号,摘要,二级科目,借方金额, - 贷方金额 AS 贷方金额 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.HGroups.AddDef("贷方金额")
    b.VGroups.AddDef("二级科目","{0}")
    b.Totals.AddDef("借方金额")
    b.Filter = "[二级科目] Is Not Null"
    b.Build(True)
    .DataSource = b.BuildDataSource
    Dim ckb As DataTable = e.Form.Controls("Table1").Table.DataTable
    If ckb.DataCols.Contains("借方金额") = False Then
        ckb.DataCols.add("借方金额", Gettype(Double))
    End If
    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 = "借方金额" 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 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
[此贴子已经被作者于2021/7/4 21:07:32编辑过]

 回到顶部
美女呀,离线,留言给我吧!
采菊东篱下
  22楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:八尾狐 帖子:1881 积分:10374 威望:0 精华:0 注册:2019/4/6 8:45:00
  发帖心情 Post By:2021/7/4 21:30:00 [显示全部帖子]

隐藏不了,还是显示。
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.HGroups.AddDef("贷方金额")
    b.VGroups.AddDef("二级科目","{0}")
    b.Totals.AddDef("借方金额")
    'b.Filter = "[二级科目] Is Not Null"
    b.Build(True)
    .DataSource = b.BuildDataSource
    .Cols("借方金额_1").Visible = False
    Dim ckb As DataTable = e.Form.Controls("Table1").Table.DataTable
    If ckb.DataCols.Contains("借方金额") = False Then
        ckb.DataCols.add("借方金额", Gettype(Double))
    End If
    DataTables("多栏式明细账_Table1").DataCols("借方金额").SetFormat("#,###.##")
    .Cols("贷方金额").Move(.Cols.count - 1)
    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 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

 回到顶部
美女呀,离线,留言给我吧!
采菊东篱下
  23楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:八尾狐 帖子:1881 积分:10374 威望:0 精华:0 注册:2019/4/6 8:45:00
  发帖心情 Post By:2021/7/4 21:34:00 [显示全部帖子]


图片点击可在新窗口打开查看此主题相关图片如下:效果20210704.png
图片点击可在新窗口打开查看
现在的效果是这样的。

 回到顶部
美女呀,离线,留言给我吧!
采菊东篱下
  24楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:八尾狐 帖子:1881 积分:10374 威望:0 精华:0 注册:2019/4/6 8:45:00
  发帖心情 Post By:2021/7/4 21:48:00 [显示全部帖子]

.Cols(6).Visible = False
这样写也不行,它是动态生成的列名,我也不知是什么。

 回到顶部
美女呀,离线,留言给我吧!
采菊东篱下
  25楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:八尾狐 帖子:1881 积分:10374 威望:0 精华:0 注册:2019/4/6 8:45:00
  发帖心情 Post By:2021/7/4 22:24:00 [显示全部帖子]

这样写一样不能隐藏
    For Each c As Col In .Cols
        If c.Name Like "借方金额_*" Then
            .Cols(c.Name).Visible = False
        End If
    Next

 回到顶部
美女呀,离线,留言给我吧!
采菊东篱下
  26楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:八尾狐 帖子:1881 积分:10374 威望:0 精华:0 注册:2019/4/6 8:45:00
  发帖心情 Post By:2021/7/4 22:53:00 [显示全部帖子]

怎么弄都无法隐藏
    For Each c As Col In .Cols
        For c1 As Integer = 0 To .Cols.Count - 1
            If .Cols(c1).Caption = "借方金额_1" Then
                .Cols(c.name).Visible = False
            End If
        Next
    Next

 回到顶部
美女呀,离线,留言给我吧!
采菊东篱下
  27楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:八尾狐 帖子:1881 积分:10374 威望:0 精华:0 注册:2019/4/6 8:45:00
  发帖心情 Post By:2021/7/4 23:33:00 [显示全部帖子]

行了,谢谢。图片点击可在新窗口打开查看

 回到顶部
总数 28 上一页 1 2 3