自动生成多层表头
在编码生成Excel工作表(Sheet)的时候,如果遇到多层表头,这对于大多数人是一个难题。
Table有一个CreateSheetHeader方法,可以为Sheet自动生成表头,包括多层表头。
语法:
CreateSheetHeader(Sheet, StartRow, StartCol, VisibleOnly)
Sheet: 工作表
StartRow: 可选参数,表头在工作表中的起始行,默认为0。
StartCol: 可选参数,表头在工作表中的起始列,默认为0。
VisibleOnly:可选参数,是否只包括可见列,默认为True。
示例一
假定有下面这样一个表:
执行代码:
Dim
Book
As New
XLS.Book
Dim Sheet As
XLS.Sheet
= Book.Sheets(0)
Tables("产品").CreateSheetHeader(Sheet)
'生成表头
Book.Save("c:\reports\test.xls")
即可在工作表中生成下图所示的多层表头:
示例二
如果导出具有多层表头的Table的数据,可以参看下面的代码:
Dim
tbl
As Table
= Tables("产品")
Dim hdr
As Integer
= tbl.HeaderRows
'获得表头的层数
Dim cnt
As Integer
Dim
Book
As
New
XLS.Book
Dim Sheet
As XLS.Sheet
= Book.Sheets(0)
tbl.CreateSheetHeader(Sheet)
'生成表头
For c
As Integer
= 0
To tbl.Cols.Count
- 1
If tbl.Cols(c).Visible
Then
For
r
As
Integer = 0
to
tbl.Rows.Count
- 1
sheet(r +
hdr,cnt).value = tbl(r,c)
Next
cnt = cnt +
1
End If
Next
Book.Save("c:\reports\test.xls")
Dim Proc
As New
Process
Proc.File =
"c:\reports\test.xls"
Proc.Start()
这一段代码只导出可见列,如果是导出所有列,代码会更加简单:
Dim
tbl
As Table
= Tables("产品")
Dim hdr
As Integer
= tbl.HeaderRows
'获得表头的层数
Dim Book
As New
XLS.Book
Dim Sheet
As XLS.Sheet
= Book.Sheets(0)
tbl.CreateSheetHeader(Sheet,0,0,False)
'生成表头
For c
As Integer
= 0
To tbl.Cols.Count
- 1
For r
As Integer
= 0
to tbl.Rows.Count
- 1
sheet(r
+ hdr,c).value
= tbl(r,c)
Next
Next
Book.Save("c:\reports\test.xls")
Dim Proc
As New
Process
Proc.File
=
"c:\reports\test.xls"
Proc.Start()
本页地址:http://www.foxtable.com/webhelp/topics/1971.htm