现在创建一个视图操作名为 Generate Chart1，功能是统计各个年龄段（20 以下，20~29,30~39,40~49,50~59,60 及以上）的人数的百分比并以 Chart1.xls 为模板生成 Excel 饼图，如果想生成不同的图表，只需要将模板中的图表格式类型改变并保存即可。代码如下：
Sub Click(Source As Button)
'定义一个数组来保存各个年龄段的人数
Dim countArr(5) As Integer
Dim s As New NotesSession
Dim ws As New NotesUIWorkspace
Dim db As NotesDatabase
Set db = s.CurrentDatabase
Dim vw As NotesView
Set vw = db.GetView("ExcelTest")
Dim doc As NotesDocument
Set doc = vw.GetFirstDocument
'计算各个年龄段人数
While Not doc Is Nothing
age% = Cint(doc.Age(0))
If age%<20 Then
countArr(0) = countArr(0) + 1
Elseif age%>=20 And age%<30 Then
countArr(1) = countArr(1) + 1
Elseif age%>=30 And age%<40 Then
countArr(2) = countArr(2) + 1
Elseif age%>=40 And age%<50 Then
countArr(3) = countArr(3) + 1
Elseif age%>=50 And age%<60 Then
countArr(4) = countArr(4) + 1
Else
countArr(5) = countArr(5) + 1
End If
Set doc = vw.GetNextDocument(doc)
Wend
'生成Excel图表
Call generateExcelChart1(countArr, "C:\Chart1.xls")
Dim uiChartDoc As NotesUIDocument
Set uiChartDoc = ws.ComposeDocument( "", "", "Chart" )
uiChartDoc.GotoField("Body")
'将生成的Excel图表粘贴到一个文档中
Call uiChartDoc.Paste
End Sub
其中生成 Excel 图表的过程 generateExcelChart1() 代码如下：
Sub generateExcelChart1(countArr As Variant, excelFileName As String)
'定义Excel相关变量
Dim excelApplication As Variant
Dim excelWorkbook As Variant
Dim excelSheet As Variant
'创建Excel对象
Set excelApplication = CreateObject("Excel.Application")
'将Excel程序设置为不可见
excelApplication.Visible = False
'打开模版文件
Set excelWorkbook = excelApplication.Workbooks.Open(excelFileName)
Set excelSheet = excelWorkbook.Worksheets("Sheet1")
'为图表填充源数据
excelSheet.Cells(2,2) = countArr(0)
excelSheet.Cells(2,3) = countArr(1)
excelSheet.Cells(2,4) = countArr(2)
excelSheet.Cells(2,5) = countArr(3)
excelSheet.Cells(2,6) = countArr(4)
excelSheet.Cells(2,7) = countArr(5)
'将生成的图表复制到剪贴板
excelSheet.ChartObjects(1).Chart.ChartArea.Copy
'不保存退出Excel应用程序
excelWorkbook.Close False
excelApplication.Quit
End Sub
