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

以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.com/bbs/index.asp)
--  专家坐堂  (http://foxtable.com/bbs/list.asp?boardid=2)
----  生成多成表头  (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=147365)

--  作者：刘林
--  发布时间：2020/3/14 21:55:00
--  生成多成表头

g.VerticalTotal=True
Dim dt1 As DataTable = g.Build(True)
Dim Book As New XLS.Book \'定义一个Excel工作簿
Dim Sheet As XLS.Sheet = Book.Sheets(0) \'引用工作簿的第一个工作表
For c As Integer = 0 To dt1.DataCols.Count-1
Sheet(0, c).Value = dt1.DataCols(c).name
Next
For r As Integer = 0 To dt1.DataRows.Count - 1 \'填入数据
For c As Integer = 0 To dt1.DataCols.Count-1
Sheet(r +1, c).Value = dt1.datarows(r)(dt1.DataCols(c).name)
Next
Next
Book.Save("c:\\人数统计.xls")

我想在手机端上导出表，想先生成表，再用a标签打开文件这样来做，现在有个问题请教1：dt1是多层表头，如何生成多成表头EXCel

--  作者：sloyy
--  发布时间：2020/3/14 23:41:00
--
没看懂,在哪里生成表?
自动生成多层表头看帮助
http://www.foxtable.com/webhelp/topics/1971.htm

--  作者：刘林
--  发布时间：2020/3/15 8:35:00
--
后台数据生成临时表导出excle但有多层表头

用dt1.CreateSheetHeader(Sheet) \'生成表头

提示不是datable的成员

[此贴子已经被作者于2020/3/15 8:37:10编辑过]

--  作者：有点蓝
--  发布时间：2020/3/15 20:36:00
--
这个是table才有的方法。这种情况只能是自己判断列是否是多层表头，然后手工添加多行标题行，并对不是多层表头的进行合并

类似
Dim i As Integer = 0
For c As Integer = 0 To dt1.DataCols.Count-1
Dim ar() As String = dt1.DataCols(c).name.split("_")
If ar.length > i Then i=ar.length
For j As Integer = 0 To ar.length - 1
Sheet(j, c).Value = ar(j)
Next
Next
For r As Integer = 0 To dt1.DataRows.Count - 1 \'填入数据
For c As Integer = 0 To dt1.DataCols.Count-1
Sheet(r+i +1, c).Value = dt1.datarows(r)(dt1.DataCols(c).name)
Next
Next
Book.Save("c:\\人数统计.xls")

合并单元格:http://www.foxtable.com/webhelp/topics/1153.htm

[此贴子已经被作者于2020/3/15 20:37:37编辑过]

--  作者：刘林
--  发布时间：2020/3/15 21:00:00
--
老师：如果是固定的列，可以用合并单元格方式来硬干，但我的dt1生成的列是动态的，生成的EXCEL列也是动态的，这怎么合并

If e.PostValues("统计分组")="学校"
gp.Groups.AddDef("学校")
End If
If e.PostValues("统计分组")="年级"
gp.Groups.AddDef("学校")
gp.Groups.AddDef("年级")
End If
If e.PostValues("统计分组")="班级"
gp.Groups.AddDef("学校")
gp.Groups.AddDef("年级")
gp.Groups.AddDef("班级")
End If
gp.Groups.AddDef("审核锁定","是否审核")
If e.PostValues("bjrs")="on"
gp.Totals.AddDef("姓名",AggregateEnum.Count,"学生人数")
End If
If e.PostValues("xsxb")="on"
gp.Totals.AddExp("性别_男","case when 性别=\'男\' then 1 else 0 end")
gp.Totals.AddExp("性别_女","case when 性别=\'女\' then 1 else 0 end")
End If
If e.PostValues("lsqk")="on"
gp.Totals.AddExp("留守情况_非留守","case when 留守情况=\'非留守\' then 1 else 0 end")
gp.Totals.AddExp("留守情况_单亲留守","case when 留守情况=\'单亲留守\' then 1 else 0 end")
gp.Totals.AddExp("留守情况_双亲留守","case when 留守情况=\'双亲留守\' then 1 else 0 end")
End If
If e.PostValues("jdfs")="on"
gp.Totals.AddExp("就读方式_住校","case when 就读方式=\'住校\' then 1 else 0 end")
gp.Totals.AddExp("就读方式_走校","case when 就读方式=\'走校\' then 1 else 0 end")
End If
If e.PostValues("jcqk")="on"
gp.Totals.AddExp("就餐情况_早餐","case when 就餐情况=\'早餐\' then 1 else 0 end")
gp.Totals.AddExp("就餐情况_午餐","case when 就餐情况=\'午餐\' then 1 else 0 end")
gp.Totals.AddExp("就餐情况_晚餐","case when 就餐情况=\'晚餐\' then 1 else 0 end")
gp.Totals.AddExp("就餐情况_早午餐","case when 就餐情况=\'早午餐\' then 1 else 0 end")
gp.Totals.AddExp("就餐情况_早晚餐","case when 就餐情况=\'早晚餐\' then 1 else 0 end")
gp.Totals.AddExp("就餐情况_午晚餐","case when 就餐情况=\'午晚餐\' then 1 else 0 end")
gp.Totals.AddExp("就餐情况_早午晚餐","case when 就餐情况=\'早午晚餐\' then 1 else 0 end")
End If
If e.PostValues("jkzk")="on"
gp.Totals.AddExp("残疾人数","case when 健康状况<>\'健康或良好\' then 1 else 0 end")
End If
If e.PostValues("jdlk")="on"
gp.Totals.AddExp("建档立卡","case when 贫困建档=\'是\' then 1 else 0 end")
End If
If e.PostValues("mzqk")="on"
gp.Totals.AddExp("少数民族","case when 民族<>\'汉族\' then 1 else 0 end")
End If
If e.PostValues("zzmm")="on"
gp.Totals.AddExp("政治面貌_群众","case when 政治面貌=\'群众\' then 1 else 0 end")
gp.Totals.AddExp("政治面貌_团员","case when 政治面貌=\'共且青团员\'then 1 else 0 end")
End If
If e.PostValues("fkxz")="on"
gp.Totals.AddExp("户口性质_农村","case when 户口性质=\'农村\' then 1 else 0 end")
gp.Totals.AddExp("户口性质_城镇","case when 户口性质=\'城镇\'then 1 else 0 end")
End If
gp.VerticalTotal=True
Dim dt1 As DataTable = gp.Build(True)
For Each r As DataRow In dt1.DataRows
For Each c As DataCol In dt1.DataCols
If c.IsNumeric AndAlso r(c.Name)=0
r(c.name)=Nothing
End If
Next
Next
Dim Book As New XLS.Book \'定义一个Excel工作簿
Dim Sheet As XLS.Sheet = Book.Sheets(0) \'引用工作簿的第一个工作表
For c As Integer = 0 To dt1.DataCols.Count-1
Sheet(0, c).Value = dt1.DataCols(c).name
Next
For r As Integer = 0 To dt1.DataRows.Count - 1 \'填入数据
For c As Integer = 0 To dt1.DataCols.Count-1
If dt1.DataCols(c).IsNumeric AndAlso dt1.datarows(r)(dt1.DataCols(c).name)=0
Sheet(r +1, c).Value=Nothing
Else
Sheet(r +1, c).Value = dt1.datarows(r)(dt1.DataCols(c).name)
End If
Next
Next
Dim fl As String ="C:\\web\\tb\\tb" & e.Cookies("userid") & "人数统表.xls"
Book.Save(fl)
With wb.AddActionSheet("","menu") \'设计菜单
.Add("mnuload", "下载表格").Attribute=" & e.Cookies("userid") & "人数统表.xls\'"""
.Add("mnuCancel","取消","",True)
End With

--  作者：有点蓝
--  发布时间：2020/3/15 23:09:00
--
例如

Dim i As Integer = 0
Dim lst As new List(of Integer)
For c As Integer = 0 To dt1.DataCols.Count-1
Dim ar() As String = dt1.DataCols(c).name.split("_")
If ar.Length = 1 Then
lst.Add(c)
Sheet(0, c).Value = ar(0)
Else
If ar.length > i Then i=ar.length
For j As Integer = 0 To ar.length - 1
Sheet(j, c).Value = ar(j)
Next
End If
Next

For Each k As Integer In lst
Sheet.MergeCell(0,k,i,1)
Next
For r As Integer = 0 To dt1.DataRows.Count - 1 \'填入数据
For c As Integer = 0 To dt1.DataCols.Count-1
Sheet(r+i +1, c).Value = dt1.datarows(r)(dt1.DataCols(c).name)
Next
Next

--  作者：刘林
--  发布时间：2020/3/16 7:40:00
--
谢谢老师，我打印下代码码一个一个研究，基本懂起这个意思，这个还是针对一列多行合并可以，我的实例中存在多行多列合并，且是不确定的，想自己研究一下，功力不够还是做不起，请老师指导，谢谢
此主题相关图片如下：qq图片20200316073632.png

[此贴子已经被作者于2020/3/16 7:40:31编辑过]

--  作者：刘林
--  发布时间：2020/3/16 7:46:00
--
比如上面第1行3列，到1行16列应合并，第2行15列到3行15列也应合并，我试了下找到相同的内容就合并，但越整越乱，还是只有麻烦老师指导再研究学习

--  作者：有点蓝
--  发布时间：2020/3/16 8:55:00
--
自己写代码生成的表格，也就是说列名是已知的，并不是不确定的，如下所示，这种只能是自己数数哪些单元格是需要合并的，如第1行3列，到1行16列，写死代码好了
If e.PostValues("jcqk")="on"
gp.Totals.AddExp("就餐情况_早餐","case when 就餐情况=\'早餐\' then 1 else 0 end")
gp.Totals.AddExp("就餐情况_午餐","case when 就餐情况=\'午餐\' then 1 else 0 end")
gp.Totals.AddExp("就餐情况_晚餐","case when 就餐情况=\'晚餐\' then 1 else 0 end")
gp.Totals.AddExp("就餐情况_早午餐","case when 就餐情况=\'早午餐\' then 1 else 0 end")
gp.Totals.AddExp("就餐情况_早晚餐","case when 就餐情况=\'早晚餐\' then 1 else 0 end")
gp.Totals.AddExp("就餐情况_午晚餐","case when 就餐情况=\'午晚餐\' then 1 else 0 end")
gp.Totals.AddExp("就餐情况_早午晚餐","case when 就餐情况=\'早午晚餐\' then 1 else 0 end")
End If

如果不写死，看看标题有多少行，每一行都遍历所有列，如果单元格内容相同的就把索引记录下来，然后在合并即可

--  作者：刘林
--  发布时间：2020/3/16 11:57:00
--
因为考虑到手机显示列多了不方便，所以我是做的选择性显示列，所以有的表写不死，遍历我只有试下看整得好不