自动生成多层表头

在编码生成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