以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.net/bbs/index.asp)
--  专家坐堂  (http://foxtable.net/bbs/list.asp?boardid=2)
----  填充数据  (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=184641)
--  作者：朱女士
--  发布时间：2022/12/19 14:57:00
--  填充数据
老师：您好！
我有一个成本计算表，如下图，经过计算的数据按表的格式，总计部分填进去了，下面各组的就是填不进去，麻烦帮看一下代码，谢谢！

图片点击可在新窗口打开查看此主题相关图片如下：7ee2350d0cbf4a341993e0eceefa47d.jpg
图片点击可在新窗口打开查看



Dim dtp1 As WinForm.DateTimePicker = e.Form.Controls("DateTimePicker1")
Dim dtp2 As WinForm.DateTimePicker = e.Form.Controls("DateTimePicker2")
Dim y1 As Integer = year(dtp1.value)
Dim m1 As Integer = month(dtp1.value)
Dim d1 As Integer = day(dtp1.value)
Dim y2 As Integer = year(dtp2.value)
Dim m2 As Integer = month(dtp2.value)
Dim d2 As Integer = day(dtp2.value)
Dim dt1 As New Date(y1, m1, d1)
Dim dt2 As New Date(y2, m2, d2)

Dim s1, s2, s3, s4 As String
Dim k1, k2, k3, k4 As Integer
If m1 <= 9 Then
    s1 = str(y1) + "0" + str(m1)
Else
    s1 = str(y1) + str(m1)
End If

k1 = val(s1)

If m2 <= 9 Then
    s2 = str(y2) + "0" + str(m2)
Else
    s2 = str(y2) + str(m2)
End If
k2 = val(s2)

s3 = str(y1) + "12"
k3 = val(s3)
s4 = str(y2) + "01"
k4 = val(s4)


\'加载数据


DataTables("abb生产表").LoadFilter = ""
DataTables("abb生产表").Load
DataTables("abb费用计算表").LoadFilter = ""
DataTables("abb费用计算表").Load

\'生成表

Dim dtb As New DataTableBuilder("abb业务成本计算表")
dtb.AddDef("序号", GetType(String), 4)
dtb.AddDef("班组", GetType(String), 12)
dtb.AddDef("项目名称", GetType(String), 16)
dtb.AddDef("选月合计", GetType(Double))
Dim v, zd1 As String
Dim i As Integer

If y2 - y1 = 0 Then
    For i = k1 To k2
        v = str(i)
        zd1 = "月份" & v
        dtb.AddDef(zd1, GetType(Double))
    Next
Else If y2 - y1 >= 1
    For i = k1 To k3
        v = str(i)
        zd1 = "月份" & v
        dtb.AddDef(zd1, GetType(Double))
    Next
    For i = k4 To k2
        v = str(i)
        zd1 = "月份" & v
        dtb.AddDef(zd1, GetType(Double))
        
    Next
End If
dtb.Build()
\' 填入项目
Dim Arys() As String

\'定义班组集合存入
Dim bzs As New List(Of String)
bzs.Add("总计")
bzs.Add("撑条组")
bzs.Add("导线夹组")
bzs.Add("垫块组")
bzs.Add("器身组")
Arys = bzs.ToArray() \'将集合转换为数组

Dim xms As New List(Of String)
xms.Add("数量")
xms.Add("重量")
xms.Add("产值")
xms.Add("费用总额")
xms.Add("总工资额")
xms.Add("其中车间工资")
xms.Add("其中管理工资")
xms.Add("社保总额")
xms.Add("车间社保额")
xms.Add("管理社保额")
xms.Add("税金总额")
xms.Add("房租总额")
xms.Add("总费用占产值比")
xms.Add("总工资占产值比")
xms.Add("车间工资占产值比")
xms.Add("管理工资占产值比")
xms.Add("总社保占产值比")
xms.Add("车间社保占产值比")
xms.Add("管理社保占产值比")
xms.Add("税金占产值比")
xms.Add("房租占产值比")
arys = xms.ToArray()
\'定义内容
Dim n As Integer = 0
For Each bz As String In bzs 
    For Each xmm As String In xms
        Dim dr As DataRow = DataTables("abb业务成本计算表").AddNew()
        n = n + 1
        dr("序号") = n 
        dr("班组") = bz 
        dr("项目名称") = xmm
        
    Next
Next
\'计算数据

\'合计列
Dim tnm As Integer = DataTables("abb生产表").Compute("Sum(完成数量)", "[实际完成日期]>= #" & dt1 & "# and [实际完成日期]<= #" & dt2 & "#") \'合计数量
Dim tgh As Double = DataTables("abb生产表").Compute("Sum(完成重量)", "[实际完成日期]>= #" & dt1 & "# and [实际完成日期]<= #" & dt2 & "#") \'合计重量
Dim tcz As Double = DataTables("abb生产表").Compute("Sum(含税金额)", "[实际完成日期]>= #" & dt1 & "# and [实际完成日期]<= #" & dt2 & "#") \'合计产值
Dim tgz1 As Double = DataTables("abb费用计算表").Compute("Sum(金额)", "[日期]>= #" & dt1 & "# and [日期]<= #" & dt2 & "# and [二级代码]=\'a010\'") \'车间工资额
Dim tgz2 As Double = DataTables("abb费用计算表").Compute("Sum(金额)", "[日期]>= #" & dt1 & "# and [日期]<= #" & dt2 & "# and [二级代码]=\'b010\'") \'管理工资额
Dim tgz As Double = tgz1 + tgz2

Dim tsb1 As Double = DataTables("abb费用计算表").Compute("Sum(金额)", "[日期]>= #" & dt1 & "# and [日期]<= #" & dt2 & "# and [二级代码]=\'a090\'") \'车间社保额
Dim tsb2 As Double = DataTables("abb费用计算表").Compute("Sum(金额)", "[日期]>= #" & dt1 & "# and [日期]<= #" & dt2 & "# and [二级代码]=\'b011\'") \'管理社保额
Dim tsb As Double = tsb1 + tsb2

Dim tsj As Double = DataTables("abb费用计算表").Compute("Sum(金额)", "[日期]>= #" & dt1 & "# and [日期]<= #" & dt2 & "# and [二级代码]=\'a100\'") \'税金
Dim tfz As Double = DataTables("abb费用计算表").Compute("Sum(金额)", "[日期]>= #" & dt1 & "# and [日期]<= #" & dt2 & "# and [二级代码]=\'b100\'") \' 房租
Dim tfy As Double = DataTables("abb费用计算表").Compute("Sum(金额)", "[日期]>= #" & dt1 & "# and [日期]<= #" & dt2 & "#") \' 费用总额

Dim tgzzb As Double = round2(tgz / tcz , 2)
Dim tgzzb1 As Double = round2(tgz1 / tcz, 2)
Dim tgzzb2 As Double = Round2( tgz2 / tcz, 2)
Dim tsbzb As Double = round2(tsb / tcz, 2)
Dim tsbzb1 As Double = round2(tsb1 / tcz, 2)
Dim tsbzb2 As Double = round2(tsb2 / tcz, 2)
Dim tsjzb As Double = round2(tsj / tcz, 2)
Dim tfzzb As Double = round2(tfz / tcz, 2)
Dim tfyzb As Double = round2(tfy / tcz, 2)
Dim zdd1 As String = "选月合计"
For Each wr As DataRow In DataTables("abb业务成本计算表").DataRows
    
    If wr("班组") = "总计" And wr("项目名称") = "数量" Then
        wr(zdd1) = tnm
    End If
    If wr("班组") = "总计" And wr("项目名称") = "重量" Then
        wr(zdd1) = tgh
    End If
    If wr("班组") = "总计" And wr("项目名称") = "产值" Then
        wr(zdd1) = tcz
    End If
    If wr("班组") = "总计" And wr("项目名称") = "费用总额" Then
        wr(zdd1) = tfy
    End If
    If wr("班组") = "总计" And wr("项目名称") = "总工资额" Then
        wr(zdd1) = tgz
    End If
    If wr("班组") = "总计" And wr("项目名称") = "其中车间工资" Then
        wr(zdd1) = tgz1
    End If
    If wr("班组") = "总计" And wr("项目名称") = "其中管理工资" Then
        wr(zdd1) = tgz2
    End If
    
    If wr("班组") = "总计" And wr("项目名称") = "社保总额" Then
        wr(zdd1) = tsb
    End If
    If wr("班组") = "总计" And wr("项目名称") = "车间社保额" Then
        wr(zdd1) = tsb1
    End If
    
    If wr("班组") = "总计" And wr("项目名称") = "管理社保额" Then
        wr(zdd1) = tsb2
    End If
    
    If wr("班组") = "总计" And wr("项目名称") = "税金总额" Then
        wr(zdd1) = tsj
    End If
    
    If wr("班组") = "总计" And wr("项目名称") = "房租总额" Then
        wr(zdd1) = tfz
    End If
    
    If wr("班组") = "总计" And wr("项目名称") = "总费用占产值比" Then
        wr(zdd1) = tfyzb
    End If
    
    If wr("班组") = "总计" And wr("项目名称") = "总工资占产值比" Then
        wr(zdd1) = tgzzb
    End If
    
    
    If wr("班组") = "总计" And wr("项目名称") = "车间工资占产值比" Then
        wr(zdd1) = tgzzb1
    End If
    
    If wr("班组") = "总计" And wr("项目名称") = "管理工资占产值比" Then
        wr(zdd1) = tgzzb2
    End If
    
    
    If wr("班组") = "总计" And wr("项目名称") = "总社保占产值比" Then
        wr(zdd1) = tsbzb
    End If
    
    If wr("班组") = "总计" And wr("项目名称") = "车间社保占产值比" Then
        wr(zdd1) = tsbzb1
    End If
    
    If wr("班组") = "总计" And wr("项目名称") = "管理社保占产值比" Then
        wr(zdd1) = tsbzb2
    End If
    
    If wr("班组") = "总计" And wr("项目名称") = "税金占产值比" Then
        wr(zdd1) = tsjzb
    End If
    If wr("班组") = "总计" And wr("项目名称") = "房租占产值比" Then
        wr(zdd1) = tfzzb
    End If
Next 

\'Dim Vas As New List(Of Double)

\'vas.Add(tnm)
\'vas.Add(tgh)
\'vas.Add(tcz)
\'vas.Add(tfy)
\'vas.Add(tgz)
\'vas.Add(tgz1)
\'vas.Add(tgz2)
\'vas.Add(tsb)
\'vas.Add(tsb1)
\'vas.Add(tsb2)
\'vas.Add(tsj)
\'vas.Add(tfz)

\'vas.Add(tfyzb)
\'vas.Add(tgzzb)
\'vas.Add(tgzzb1)
\'vas.Add(tgzzb2)
\'vas.Add(tsbzb)
\'vas.Add(tsbzb1)
\'vas.Add(tsbzb2)
\'vas.Add(tsjzb)
\'vas.Add(tfzzb)

\'计算各组总计数据
Dim zbs As New List(Of String)
zbs.Add("撑条组")
zbs.Add("导线夹组")
zbs.Add("垫块组")
zbs.Add("器身组")
Dim drs As List(Of DataRow)

For Each gp As String In zbs
    
    Dim ztnm As Integer = DataTables("abb生产表").Compute("Sum(完成数量)", "[实际完成日期]>= #" & dt1 & "# and [实际完成日期]<= #" & dt2 & "# and [工作组]=\'" & gp & "\'") \'合计数量
    Dim ztgh As Double = DataTables("abb生产表").Compute("Sum(完成重量)", "[实际完成日期] >= #" & dt1 & "# and [实际完成日期]<= #" & dt2 & "# and [工作组]=\'" & gp & "\'") \'合计重量
    Dim ztcz As Double = DataTables("abb生产表").Compute("Sum(含税金额)", "[实际完成日期]>= #" & dt1 & "# and [实际完成日期]<= #" & dt2 & "# and [工作组]=\'" & gp & "\'") \'合计产值
    Dim ztgz1 As Double = DataTables("abb费用计算表").Compute("Sum(金额)", "[日期]>= #" & dt1 & "# and [日期]<= #" & dt2 & "# and [二级代码]=\'a010\' and [工作组]=\'" & gp & "\'") \'车间工资额
    Dim ztgz2 As Double = DataTables("abb费用计算表").Compute("Sum(金额)", "[日期]>= #" & dt1 & "# and [日期]<= #" & dt2 & "# and [二级代码]=\'b010\' and [工作组]=\'" & gp & "\'") \'管理工资额
    Dim ztgz As Double = ztgz1 + ztgz2
    
    Dim ztsb1 As Double = DataTables("abb费用计算表").Compute("Sum(金额)", "[日期]>= #" & dt1 & "# and [日期]<= #" & dt2 & "# and [二级代码]=\'a090\' and [工作组]=\'" & gp & "\'") \'车间社保额
    Dim ztsb2 As Double = DataTables("abb费用计算表").Compute("Sum(金额)", "[日期]>= #" & dt1 & "# and [日期]<= #" & dt2 & "# and [二级代码]=\'b011\' and [工作组]=\'" & gp & "\'") \'管理社保额
    Dim ztsb As Double = ztsb1 + ztsb2
    
    Dim ztsj As Double = DataTables("abb费用计算表").Compute("Sum(金额)", "[日期]>= #" & dt1 & "# and [日期]<= #" & dt2 & "# and [二级代码]=\'a100\' and [工作组]=\'" & gp & "\'") \'税金
    Dim ztfz As Double = DataTables("abb费用计算表").Compute("Sum(金额)", "[日期]>= #" & dt1 & "# and [日期]<= #" & dt2 & "# and [二级代码]=\'b100\' and [工作组]=\'" & gp & "\'") \' 房租
    Dim ztfy As Double = DataTables("abb费用计算表").Compute("Sum(金额)", "[日期]>= #" & dt1 & "# and [日期]<= #" & dt2 & "# and [工作组]=\'" & gp & "\'") \' 费用总额
    
    Dim ztgzzb As Double = ztgz / ztcz 
    Dim ztgzzb1 As Double = ztgz1 / ztcz
    Dim ztgzzb2 As Double = ztgz2 / ztcz
    Dim ztsbzb As Double = ztsb / ztcz
    Dim ztsbzb1 As Double = ztsb1 / ztcz
    Dim ztsbzb2 As Double = ztsb2 / ztcz
    Dim ztsjzb As Double = ztsj / ztcz
    Dim ztfzzb As Double = ztfz / ztcz
    Dim ztfyzb As Double = ztfy / ztcz
    
  drs = DataTables("abb业务成本计算表").Select("[班组]=\'" & gp & "\'")
   For Each dr As DataRow In drs 
       MessageBox.Show(dr("项目名称"))
      If dr("班组") = "\'" & gp & "\'" And dr("项目名称") = "数量" Then
            dr(zdd1) = ztnm
          
        End If
        If dr("班组") = "\'" & gp & "\'" And dr("项目名称") = "重量" Then
            dr(zdd1) = ztgh
        End If
        If dr("班组") = "\'" & gp & "\'" And dr("项目名称") = "产值" Then
            dr(zdd1) = ztcz
        End If
        If dr("班组") = "\'" & gp & "\'" And dr("项目名称") = "费用总额" Then
            dr(zdd1) = ztfy
        End If
        If dr("班组") = "\'" & gp & "\'" And dr("项目名称") = "总工资额" Then
            dr(zdd1) = ztgz
        End If
        If dr("班组") = "\'" & gp & "\'" And dr("项目名称") = "其中车间工资" Then
            dr(zdd1) = ztgz1
        End If
        If dr("班组") = "\'" & gp & "\'" And dr("项目名称") = "其中管理工资" Then
            dr(zdd1) = ztgz2
        End If
        
        If dr("班组") = "\'" & gp & "\'" And dr("项目名称") = "社保总额" Then
            dr(zdd1) = ztsb
        End If
        If dr("班组") = "\'" & gp & "\'" And dr("项目名称") = "车间社保额" Then
            dr(zdd1) = ztsb1
        End If
        
        If dr("班组") = "\'" & gp & "\'" And dr("项目名称") = "管理社保额" Then
            dr(zdd1) = ztsb2
        End If
        
        If dr("班组") = "\'" & gp & "\'" And dr("项目名称") = "税金总额" Then
            dr(zdd1) = ztsj
        End If
        
        If dr("班组") = "\'" & gp & "\'" And dr("项目名称") = "房租总额" Then
            dr(zdd1) = ztfz
        End If
        
        If dr("班组") = "\'" & gp & "\'" And dr("项目名称") = "总费用占产值比" Then
            dr(zdd1) = ztfyzb
        End If
        
        If dr("班组") = "\'" & gp & "\'" And dr("项目名称") = "总工资占产值比" Then
            dr(zdd1) = ztgzzb
        End If
        
        
        If dr("班组") = "\'" & gp & "\'" And dr("项目名称") = "车间工资占产值比" Then
            dr(zdd1) = ztgzzb1
        End If
        
        If dr("班组") = "\'" & gp & "\'" And dr("项目名称") = "管理工资占产值比" Then
            dr(zdd1) = ztgzzb2
        End If
        
        
        If dr("班组") = "\'" & gp & "\'" And dr("项目名称") = "总社保占产值比" Then
            dr(zdd1) = ztsbzb
        End If
        
        If dr("班组") = "\'" & gp & "\'" And dr("项目名称") = "车间社保占产值比" Then
            dr(zdd1) = ztsbzb1
        End If
        
        If dr("班组") = "\'" & gp & "\'" And dr("项目名称") = "管理社保占产值比" Then
            dr(zdd1) = ztsbzb2
        End If
        
        If dr("班组") = "\'" & gp & "\'" And dr("项目名称") = "税金占产值比" Then
            dr(zdd1) = ztsjzb
        End If
        If dr("班组") = "\'" & gp & "\'" And dr("项目名称") = "房租占产值比" Then
            dr(zdd1) = ztfzzb
        End If
    Next
    
Next
 
--  作者：有点蓝
--  发布时间：2022/12/19 15:04:00
--  
If dr("班组") = "\'" & gp & "\'" And dr("项目名称") = "数量" Then
改为
If dr("班组") = gp And dr("项目名称") = "数量" Then

不是任何地方都需要加单引号的，一般表达式才需要