39. 面向对象的LotusScript（十一）之导出Excel（三）

淩亂°似流年 2022-01-10 05:03 298阅读 0赞

我们再来看一个模版型的报表的实例。

选择报表类型，输入参数：

![SouthEast][]

产生的报表：

![SouthEast 1][]

![SouthEast 2][]

预先设计好的Excel模版以附件形式保存在配置文档中：

![SouthEast 3][]

![SouthEast 4][]

![SouthEast 5][]

继承了Report基类的子类Report2的代码如下：

Public Class Report2 As Report
    	Private intYear As Integer 
    	Private intMonth As Integer 
    	Private VIEW_REPORT As String
    	Private offices As NArray
    	Private acctTypes As NArray 
    
    	Sub New(intYear As Integer, intMonth As Integer), Report()
    		'设置报表的类型为模版型
    me.reportType=REPORT_TYPE_TEMPLATE
    '设定模版的名称
    		me.Template="Report2"
    		'接受用户选择的参数
    		me.intYear=intYear
    		me.intMonth=intMonth
    		'指定要从其中取数据的视图
    		me.VIEW_REPORT="vwRecord4Report2"
    'Offices excluding SC & DG
    		'初始化报表所需的办公室和科目等数据
    		Set offices = New NArray(-1)
    		Call offices.Import("03,04,02,05,01,08", ",")
    		
    		Set acctTypes = New NArray(-1)
    		With acctTypes
    			Call .Add("Fringe")
    			Call .Add("Misc")
    			Call .Add("Outside Services")
    			Call .Add("Rent")
    			Call .Add("Repair/Maintenance")
    			Call .Add("Tax")
    			Call .Add("Travel")
    			Call .Add("Utilities")
    			Call .Add("Wages")
    		End With
    		
    	End Sub
    	
    	%REM
    		Function ExportData
    		Description: Comments for Function
    	%END REM
    	Private Function ExportData()
    		'设置writer的模式为列模式
    		writer.Mode=writer.Mode_column
    		Call writer.MoveTo(5, 1)
    		'将月份数字转换成英文的月份名称
    		Dim converter As New Monthconverter()
    		writer.WriteCell("Y-T-D as of " & converter.Getname(me.intMonth) & " " & me.intYear)
    		
    		'Dim view As NotesView 
    		'Set view=db.Getview("vwRecord4Report2")
    		'05 ,06,03,04,02,05,01,08
    		'将writer移动到指定的区域
    		Call writer.MoveTo(40, 2)
    		'设置writer换列时的上界
    		writer.TopBound=40
    		'调用方法WriteTable()写报表的一部分
    		Call me.WriteTable(me.intYear, "Actual")
    		
    		Call writer.MoveTo(56, 2)
    		writer.TopBound=56
    		Call me.WriteTable(me.intYear-1, "Actual")
    		
    		Call writer.MoveTo(87, 2)
    		writer.TopBound=87
    		Call me.WriteTable(me.intYear, "Budget")
    	End Function
    	
    	%REM
    		Function WriteTable
    		Description: Write a table region in the report
    	%END REM
    	Private Function WriteTable(intYear As Integer, amtType As String )
    		Dim keys(4) As String
    		keys(0)=CStr(intYear)
    		keys(1)=amtType
    		Dim total As Double
    		
    		'Write values of SC & DG
    		'Dim total2 As Variant
    		ForAll t In acctTypes.container
    			keys(3)=t
    			keys(2)="05 "
    			total=me.GetYTDAmount(keys)
    			
    			keys(2)="06"
    			total=total+me.GetYTDAmount(keys)
    			writer.WriteCell(total)
    		End ForAll
    		'Write values of other offices
    		ForAll o In offices.container
    			Call writer.NextColumn()
    			ForAll t In acctTypes.container
    				keys(2)=o
    				keys(3)=t
    				total=me.GetYTDAmount(keys)
    				writer.WriteCell(total)
    			End ForAll
    			
    		End ForAll
    	End Function
    	
    	%REM
    		Function GetYTDAmount
    		Description: Get the YTD amount in accordance with the conditions
    		in the provided keys
    	%END REM
    	Private Function GetYTDAmount(keys As Variant) As Double
    		Dim total As Double, amt As Variant
    		Dim i As Integer
    		For i=1 To me.intMonth
    			keys(4)=CStr(i)
    			amt=GetTotal(me.VIEW_REPORT, keys, 0, "")
    			If Not IsEmpty(amt) Then
    				total=total+amt
    			End If
    		Next
    		GetYTDAmount=total
    	End Function
    End Class

这个报表里有很多个表格区域，有些需要写入数据，有些则通过预设的公式计算出结果。在上面的代码中，ExportData()方法使用专门的写Excel工作表的类SheetWriter（将在下一篇文章中详细介绍）的实例writer，移动到各个表格区域，再调用WriteTable()方法写入数据。而WriteTable()方法利用在[23. 在LotusScript中利用视图的合计功能求和][23. _LotusScript]一文中介绍过的GetTotal()函数读取视图的汇总列以获得报表所需的统计数据。代码里还应用了[20. 面向对象的LotusScript（三）之NArray][20. _LotusScript_NArray]里介绍的Narray和[21. 面向对象的LotusScript（四）之MonthConverter][21. _LotusScript_MonthConverter]介绍的MonthConverter。

[SouthEast]: /images/20220109/04d08489b8724dd4952e7112341a1b42.png
[SouthEast 1]: /images/20220109/4dc24ca249bf47218972bcc9c5dfff10.png
[SouthEast 2]: /images/20220109/1dc00ecdb0764043b7994844972e87a8.png
[SouthEast 3]: /images/20220109/c2d550e05d5e4a34b63cbfc3c2601ba4.png
[SouthEast 4]: /images/20220109/df198ddd9408482ab60d4cbc16f96195.png
[SouthEast 5]: /images/20220109/fb55569d3e734f49b89abe939c89bdbd.png
[23. _LotusScript]: http://blog.csdn.net/starrow/article/details/8479816
[20. _LotusScript_NArray]: http://blog.csdn.net/starrow/article/details/8463725
[21. _LotusScript_MonthConverter]: http://blog.csdn.net/starrow/article/details/8467570