- Foxtable(狐表) (http://foxtable.com/bbs/index.asp)
-- 专家坐堂 (http://foxtable.com/bbs/list.asp?boardid=2)
---- 关于垂直表和水平表的转换 (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=78445)
-- 发布时间：2015/12/9 13:37:00
-- 关于垂直表和水平表的转换
我想实现从11那样的表转换成22那样的表，在转换过程中，如何把11表中的日期转换成22中的分月份呢
此主题相关图片如下：22.jpg
Dim dtb As New DataTableBuilder("损益进度(总体)")
dtb.AddDef("所属单位", Gettype(String), 8)
dtb.AddDef("一级科目", Gettype(String), 10)
dtb.AddDef("日期分月如何表示?", Gettype(Double))
dtb.Build()
此主题相关图片如下：11.jpg
此主题相关图片如下：11.jpgDim kms() As String = {"利润_本月","营业费用_本月","其他支出_本月"}
For Each dr1 As DataRow In DataTables("利润表附表").DataRows
For Each km As String In kms
Dim dr2 As DataRow = DataTables("损益进度(总体)").AddNew()
dr2("所属单位") = dr1("所属单位")
dr2("一级科目") = km
dr2("日期分月如何表示?") = dr1(km)
Next
Next
MainTable = Tables("损益进度(总体)")
-- 发布时间：2015/12/9 14:11:00
--
没理解你什么意思
Dim dtb As New DataTableBuilder("损益进度(总体)")
dtb.AddDef("所属单位", Gettype(String), 8)
dtb.AddDef("一级科目", Gettype(String), 10)
dtb.AddDef("利润_一月", Gettype(Double))
dtb.AddDef("利润_二月", Gettype(Double))
dtb.Build()
-- 发布时间：2015/12/9 14:12:00
--
http://www.foxtable.com/bbs/dispbbs.asp?BoardID=2&ID=68546&skin=0
-- 发布时间：2015/12/9 14:20:00
--
好，先研究一下
-- 发布时间：2015/12/9 15:53:00
--
研究了一下，感觉简单的还能弄得明白，自己这表相对复杂了点，转换前后的统计列都是多列的，而且要求转换后的数据具有汇总性，只能求助了。
如项目中，将“利润表附表”转换为“损益进度”表
-- 发布时间：2015/12/9 16:50:00
--
Dim dtb As New DataTableBuilder("统计表", "损益进度(总体)")
dtb.AddDef("所属单位", Gettype(String), 8)
dtb.AddDef("一级科目", Gettype(String), 10)
Dim dt As DataTable = DataTables("利润表附表")
Dim mind As Date = dt.compute("min(日期)")
Dim maxd As Date = dt.compute("max(日期)")
mind = new Date(mind.year, mind.Month, 1)
maxd = new Date(maxd.year, maxd.Month, 1)
Do While mind <= maxd
dtb.AddDef(Format(mind, "j_yyyy-MM-01"), Gettype(Double), "",Format(mind, "yyyy年MM月"))
mind = mind.AddMonths(1)
Loop
dtb.AddDef("合计", Gettype(Double))
dtb.Build()
Dim t As Table = Tables("统计表")
Dim kms() As String = {"利润_本月","主营业务收入_本月"}
Dim kmsz() As String = {"利润","主营业务收入"}
For Each ary As String In dt.GetValues("所属单位")
For i As Integer = 0 To kms.length-1
Dim km As String = kms(i)
Dim kmz As String = kmsz(i)
Dim nr As Row = t.addnew
nr("所属单位") = ary
nr("一级科目") = kmz
Dim sum As Double = 0
For Each c As Col In t.Cols
If c.name Like "j_*" Then
Dim d As Date = c.name.split("_")(1)
nr(c.name) = dt.compute("sum(" & km & ")", "所属单位 = \'" & ary & "\' and 日期>=#" & d & "# and 日期<#" & d.AddMonths(1) & "#")
sum += nr(c.name)
End If
Next
nr("合计") = sum
Next
Next
MainTable = t
-- 发布时间：2015/12/9 16:52:00
--
好复杂啊，研究研究，谢谢
-- 发布时间：2015/12/9 22:00:00
--
如我上传的项目中，如果我的“损益进度"再增加一列”部门“，我又弄不来了，而且我希望生成的”损益进度“能够按照”科目“汇总，好难啊。
我现在是通过增加一个中间的附表解决的，我通过将科目转换成一列形成一个附表，再对这个附表进行二次交叉汇总，从而实现我上面的需求。
不过还是想学学一次解决的办法。
“损益进度"再增加一列”部门“，并按照”科目“汇总。再求代码
-- 发布时间：2015/12/9 22:05:00
--
Dim dtb As New DataTableBuilder("统计表", "损益进度(总体)")
dtb.AddDef("所属单位", Gettype(String), 8)
dtb.AddDef("部门", Gettype(String), 20)
dtb.AddDef("一级科目", Gettype(String), 10)
Dim dt As DataTable = DataTables("利润表附表")
Dim mind As Date = dt.compute("min(日期)")
Dim maxd As Date = dt.compute("max(日期)")
mind = new Date(mind.year, mind.Month, 1)
maxd = new Date(maxd.year, maxd.Month, 1)
Do While mind <= maxd
dtb.AddDef(Format(mind, "j_yyyy-MM-01"), Gettype(Double), "",Format(mind, "yyyy年MM月"))
mind = mind.AddMonths(1)
Loop
dtb.AddDef("合计", Gettype(Double))
dtb.Build()
Dim t As Table = Tables("统计表")
Dim kms() As String = {"利润_本月","主营业务收入_本月"}
Dim kmsz() As String = {"利润","主营业务收入"}
For Each ary() As String In dt.GetValues("所属单位|部门")
For i As Integer = 0 To kms.length-1
Dim km As String = kms(i)
Dim kmz As String = kmsz(i)
Dim nr As Row = t.addnew
nr("所属单位") = ary(0)
nr("部门") = ary(1)
nr("一级科目") = kmz
Dim sum As Double = 0
For Each c As Col In t.Cols
If c.name Like "j_*" Then
Dim d As Date = c.name.split("_")(1)
nr(c.name) = dt.compute("sum(" & km & ")", "所属单位 = \'" & ary(0) & "\' and 部门 = \'" & ary(1) & "\' and 日期>=#" & d & "# and 日期<#" & d.AddMonths(1) & "#")
sum += nr(c.name)
End If
Next
nr("合计") = sum
Next
Next
MainTable = t
-- 发布时间：2015/12/10 9:00:00
--
有点明白了，谢谢