用 LotusScript 实现 Excel 报表的自动生成和操作

最新推荐文章于 2020-12-08 18:24:40 发布

cuinaozun7188

最新推荐文章于 2020-12-08 18:24:40 发布

阅读量367

点赞数

文章标签：数据库 java javascript ViewUI

Lotus Notes 是 Windows® 和 Macintosh® 计算机上功能强大且用途广泛的软件，在企业内部的办公应用领域，扮演了十分重要的角色。而 Excel® 表格也是企业办公领域的流行工具之一，经常作为统计数据的报表使用。Lotus Domino Designer 当中的 LotusScript. 语言提供了丰富的编程功能，可以在 Notes 数据库当中实现对 Excel 文件的自动生成、操作和发送等等功能。本文结合本人在开发过程当中的实际经验，以一个具体的应用模块为例，介绍在 Lotus 平台下，用 LotusScript. 语言实现 Excel 报表操作功能的原理、方法和一些实用技巧。

Lotus Notes 应用开发概念简介

Lotus Notes 是大型企业内部办公协作的首选工具之一，除了众所周知的电子邮件收发功能以外，Notes 也是一个强大的应用程序运行平台，可以通过运行各种 Notes 应用来实现各种各样的功能。

Notes 客户端上运行的应用被称为 Notes 数据库，如同网页浏览器（如Firefox）通过打开不同的 URL 访问功能各异的网络应用（网页）一样，Notes 客户端的强大功能，正在于通过打开不同的 Notes 数据库，从而实现千变万化的业务功能。从物理上看，Notes 数据库很简单，就是一个后缀名为“nsf”的文件，Notes 客户端只要打开本地或者远程服务器上的 nsf 文件，就可以访问这个 Notes 数据库了。从逻辑上看，Notes 数据库文件将数据库的设计和数据都集中在一起，便于访问和维护。

要理解 Notes 数据库的逻辑结构，就要理解 Notes 数据库独特的数据库类型－“文档数据库”。一提到“数据库”，人们都会想起通用的关系型数据库，其实，很多其他形式的数据库，如“文档数据库”也非常的有价值，便于实现某些特定的功能。文档数据库的概念很简单，就是在数据库当中没有关系型数据库那样复杂的结构和严格的规定，数据按照简单的“文档”形式来存储，一个“文档”当中包含很多内容字段，文档之间的关联不是很紧密。除开数据存储以外，Notes 数据库当中还储存了一些操作数据的应用程序，包括脚本程序、图形界面设计、数据表单设计等等。有了这些设计，Notes 数据库就不仅仅具有简单的数据存储功能，还可以实现丰富的应用逻辑功能了。关系型数据库虽然很通用，可以存储任何结构的数据，但是在存储结构不是很复杂，数据间关联不是很紧密的情况下，文档数据库的形式就更加容易设计和修改；而这种将数据和设计放在一个文件当中的形式，也更加便于部署和掌握。

一个很容易想到的文档数据库的例子就是电子邮件信箱：在邮箱里，每一封邮件都可以被认为是一个独立的文档，有着类似的结构和字段，而且相互之间关联程度不大。如果用关系型数据库来记录邮件内容，在开发过程中必须遵循很多的限制和约束，而且对于归类、排序、自动设置属性等等邮件文档的常用功能，必须按照关系型数据库的规范编写比较复杂的应用程序来实现。有了文档数据库，事情就变得简单了，由于文档的共同特性，文档数据库一般都有通用的归类、排序等功能，几乎不用写代码，就可以实现这些常规的操作功能。而且，当我们要迁移邮箱内容的时候，只要拷贝邮箱的 nsf 文件就可以了，不会像关系数据库那样需要复杂的导入导出和配置过程。所以，Lotus Notes 当中应用最为广泛的，就是邮件数据库，它充分体现了文档数据库的特点，另外，个人通讯录、日程安排管理、公司内部的文档管理系统等等企业办公协作应用，也可以用文档数据库很好的实现。

用 Domino Designer 开发简单脚本程序

开发一个 Notes 数据库应用，有专门的工具，那就是 Domino Designer。Domino Designer 是一种应用程序开发软件，一般可以跟 Lotus Notes 客户端一起安装。应用程序开发人员和 Web 站点设计人员通过它可以创建安全的、能够通过 Lotus Notes 或 Web 浏览器访问的协作应用程序。开发人员很容易使用表单、视图、网页、框架集、集成的即时消息、XML、Java、JavaScript. 等来创建核心业务解决方案。

除开这些功能以外，Domino Designer 还提供了一种简单易用的脚本语言－“LotusScript” 来实现一些高级的应用功能。这种语言语法类似于 Basic 语言，可以调用 Domino 提供的丰富的函数库实现很多文档操作和处理功能。

使用 Domino Designer 开发文档数据库的方法很简单，只要运行 Domino Designer，创建或者打开一个 “nsf” 文件就可以开始开发了，如下图所示。

图1. Domino Designer 开发项目导航
Domino Designer开发项目导航

图中可见，Domino Designer 提供了丰富的开发功能，在这里我们重点介绍脚本程序的开发。脚本程序开发主要在上图中的“共享代码”项目当中的“代理”和“Script. 库”当中实现。“代理”就是一个个独立的脚本程序，多用 LotusScript. 来实现，可以定时触发，也可以用一定的事件触发；“Script. 库”是一个用户自定义的脚本库，用 LotusScript. 等方式写好的共享脚本就放在这里，由代理来调用。另外，脚本编程还常常用到视图。视图可以被理解成一个“搜索器”，每一个视图规定了一定的搜索条件，返回一个文档的集合。脚本程序当中，经常掉用视图来得到一定条件的文档，进而进行处理，就如同关系型数据库应用当中频繁使用 “select” 语句得到数据集一样。

点击“代理”导航项，弹出了如下图所示的新建代理设置界面。

图2. 代理开发简要示意图

如图，在 1 附近位置可以确定代理的名称，这里定为“AgentTest”。然后，我们在 2 附近的位置可以确定代理运行的方式，在“按事件”和“按日程安排”当中选择，这里，我们选择按照事件来触发，而且指定是在菜单当中选择一个菜单项来触发。随后，我们在 3 附近的位置指定脚本语言，这里，我们使用 LotusScript。选定了脚本语言，我们就可以看到图中 4 附近的位置出现了 LotusScript. 语言的基本结构，其中在（Options）当中可以指定语言执行特性，例如是否允许使用未定义的变量等；在（Declarations）里面，我们可以指定一些引用脚本，定义一些全局变量，这里相当于 C 语言的头文件；在 Initialize 函数当中，是这个脚本的主要执行逻辑，这个函数相当于 C 语言的 main 函数，是执行的入口点；在 Terminate 函数当中，我们可以指定一些脚本执行完毕以后的资源释放工作。另外，如同 C 语言一样，这里也可以定义局部函数。

例如，我们在 Initialize 函数里面写下如下简单的语句：

Sub Initialize
'定义界面工作空间
Dim workspace As New NotesUIWorkspace
'弹出一个确认对话框
Call workspace.Prompt(PROMPT_OK, "Say Hello", "Hello")
End Sub

然后，保存这个代理，用 Notes 打开这个数据库，由于我们刚才选择了用“操作”菜单来触发这个代理，所以我们发现在这个数据库的“操作”菜单下面有一个菜单项就是用这个代理的名称来命名的。我们点击这个 “AgentTest” 项目以后，就会看到代理运行的结果了。如下图。

图3. 简单代理运行

用 LotusScript. 访问 Excel

LotusScript. 除开可以访问 Notes 数据库当中的资源以外，还可以访问某些外部的文件资源。Excel? 是 Office? 家族当中一个重要成员，在数据记录和统计领域十分常用，其本身也是一款数据库软件。在 LotusScript. 当中，实现了一些 OLE 接口来访问和操作 Excel，从而可以在 Notes 数据库当中实现对 Excel 文件的操作和访问。

在 LotusScript. 当中，提供了访问 Excel 文件，以及其他外部资源的方法，开发人员只需要简单的调用一些脚本函数，就可以方便的实现自动操作 Excel 的功能。下面，本文结合一个实例，一步一步的介绍用 LotusScript. 访问和操作 Excel 的方法。

例子描述

现在我们考虑一个简单的应用实例：有一个存放普通文档的数据库，不断会有人更新和添加一些文档在里面，现在需要给这个数据库添加一个代理程序，让它定期自动运行，例如一个礼拜运行一次，将这个礼拜当中新添加的文档的摘要内容取出来放到一个 Excel 文件里面，然后对这个报表进行单元格的自动对齐等操作，最后将这个 Excel 文件以电子邮件附件的形式发送到特定的若干地址当中去。

打开一个 Excel 对象

运行这样的程序，必须在机器上安装 Excel，这样 LotusScript. 才可以操作 Excel。这里，Notes 数据库只需要在一台机器（服务器）上运行即可，也就是说，只需要在服务器上面装上 Excel 程序就可以了。

要打开一个 Excel 对象，需要用到 LotusScript. 里面的 CreateObject 方法，这个方法打开一个 OLE 对象，我们可以在方法调用的时候指定对象类型：

xlApp As Variant
'创建一个新的 Excel 应用实例，对应一个 Excel 文件
Set xlApp = CreateObject("Excel.application")
'在这个 Excel 文件当中添加一个 Sheet
xlApp.Workbooks.Add
xlApp.Visible = True

LotusScript. 是一种语法比较松散的脚本语言，类似于 VB，为了避免代码的逻辑混乱，我们可以利用 LotusScript. 里面的面向对象的特性，将处理 Excel 操作的代码统一写到一个类里面，这样既便于维护，也可以最大程度的避免代码冗余，提高代码的重用性。

一般的，我们用 Script. 库共享代码的方式来实现一个类：在 Domino Designer 的 “Script库” 里面点击“新建 LotusScript. 库”就可以新建一个空的共享脚本，我们定名字为 “ExcelUtil”，希望在里面存放所有需要的 Excel 操作代码；然后，我们在（Declarations）方法里面写下全局的类定义如下：

Class ExcelReport
	Private xlApp As Variant
	'其他变量……
	Sub new()
		'创建一个新的 Excel 应用实例，对应一个 Excel 文件
		Set xlApp = CreateObject("Excel.application")
		'在这个 Excel 文件当中添加一个 Sheet
		xlApp.Workbooks.Add
		xlApp.Visible = True
	End Sub

	'其他方法可以往下继续添加……
End Class

我们可以用类似 Java 的语言思想来看待这段类定义的代码：这个类的名称叫做 ExcelReport，意为一个类的实例对应于一个 Excel 文件，其中私有的变量 xlApp 对应 Excel 文件，构造函数 new() 则实现了 Excel 对象和 Sheet 的初始化。我们以后更多的操作方法可以追加在后面，实现更多的功能。

有了这个共享的类，我们就可以考虑实现业务逻辑了。在 Domino Designer 的“代理”里面，按照本文前面提到的方式，我们创建一个简单的代理 “ReportGenerator”，然后，在（Options）方法里面，我们引入那个共享的类：

Use "ExcelUtil"

这样就可以在这个代理里面使用共享的 ExcelReport 类了。随后，我们在 Initialize 方法里面通过 ExcelUtil 里面的类定义并初始化一个 Excel 对象：

'定义 ExcelReport 类的实例，表示一个 Excel 对象
Dim report As ExcelReport

'调用构造函数，初始化
Set report = New ExcelReport

操作 Excel 对象

对 Excel 对象的简单操作主要是通过调用上述 ExcelReport 类当中的 xlApp 变量的特定方法来实现的。

我们都知道，Excel 文件的基本数据单元就是一个个的“单元格”，由于所有单元格默认都是为空且存在的，所以对单元格没有“添加”和“删除”操作（要删除一个单元格的内容，只要写入一个空字符串即可）。所以，简单说来，操作 Excel 文件，就是对单元格的定位与读写，而不是“增删改”模式。要定位一个单元格，只要知道 “sheet”、“row” 和 “column” 三个参数就可以了；而简单说来，单元格的内容可以统一认为是一个字符串。下面的函数实现了对一个单元格的读写：

Function insertData(intSheet As Integer,row As Integer,column As Integer,value As String)
	'1.定位单元格，在第一个 Excel 文件的第 intSheet 个（从1开始）
	'  sheet 里面的行列号为 row 和 column 的单元格
	'2.用字符串 value 来填充单元格
	xlApp.Workbooks(1).Worksheets( intSheet ).Cells( row , column ).Value = value
End Function
	
Function getData( intSheet As Integer , row As Integer , column As Integer ) As String
	'得到第一个 Excel 文件的第 intSheet 个（从1开始）sheet 里面的行列号为 row 和 column 的单元格的值
	getData = xlApp.Workbooks(1).Worksheets( intSheet ).Cells( row , column ).Value
End Function

上述代码很简单，仅仅调用了一句 xlApp 对应 OLE 对象的方法就实现了定位与读写。当然，我们还需要对以上方法添加错误处理代码，具体关于错误处理的内容介绍超出了本文的范围，请参考 LotusScript. 的相关资料。

此外，我们应该注意到，通过上述方法创建的 Excel 对象还是在内存当中的，并没有保存为文件，我们需要添加一个保存文件的方法 “saveFile” 来保存它。最后，我们还应该为这个 ExcelReport 类实现一个退出的方法 “doQuit”，用来关闭和释放 Excel 对象的资源。

这样，我们就得到了如下完整的 ExcelReport 类：

Class ExcelReport
	Private xlApp As Variant	
	
	Sub new()
		'创建一个新的 Excel 应用实例，对应一个 Excel 文件
		Set xlApp = CreateObject("Excel.application")
		'在这个 Excel 文件当中添加一个 Sheet
		xlApp.Workbooks.Add
		xlApp.Visible = True
	End Sub	
	
	Function saveFile(strFilePath As String)
		'保存 Excel 文件到硬盘指定位置
		xlApp.ActiveWorkbook.SaveAs( strFilePath )
	End Function	
	
	Function insertData(intSheet As Integer,row As Integer,column As Integer,value As String)
		On Error Goto err_hdl
		'1.定位单元格，在第一个 Excel 文件的第 intSheet 个（从1开始）sheet 里面的
		'  行列号为 row 和 column 的单元格
		'2.用字符串 value 来填充单元格
		xlApp.Workbooks(1).Worksheets( intSheet ).Cells( row , column ).Value = value
		Exit Function		
err_hdl:		
		Print Error$ + "in cls: ExcelReport , method: insertData , at line " + Cstr( Erl )
		Exit Function
	End Function
	
	Function getData( intSheet As Integer , row As Integer , column As Integer ) As String
		On Error Goto err_hdl
		'得到第一个 Excel 文件的第 intSheet 个（从1开始）sheet 里面的行列号为 row 和 column 的单元格的值
		getData = xlApp.Workbooks(1).Worksheets( intSheet ).Cells( row , column ).Value
		Exit Function		
err_hdl:
		Print Error$ + "in cls: ExcelReport , method: getData , at line " + Cstr( Erl )
		getData = ""
		Exit Function
	End Function
	
	Function doQuit
		'关闭资源
		xlApp.Quit
		'资源释放
		Set xlApp = Nothing
	End Function	
	
End Class

业务逻辑实现

在业务逻辑方面，首先，我们需要在这个 Notes 数据库里面新建一个视图（NotesView），名为 “byTime”，按照文档的创建时间从晚到早的排序所有的文档，这个视图的创建很简单，不用任何代码；随后，我们可以借助这个视图来得到按照创建时间排序的文档（NotesDocument），从最近的文件开始遍历，对所有一周以内创建的文档进行处理，将它们的内容写入新建好的 Excel 对象的对应单元格；完成以后将这个文件保存在硬盘，然后发送给指定的地址列表当中；最后在本地删除这个文件。这个简单业务逻辑的代码如下（关于 NotesView 和 NotesDocument 的概念和具体用法请参考相关资料）：

Sub Initialize
	
	'定义 ExcelReport 类的实例，表示一个 Excel 对象
	Dim report As ExcelReport
	Dim session As New NotesSession
	Dim db As NotesDatabase
	Dim view As NotesView
	Dim doc As NotesDocument	
	Dim iRow As Integer
	Dim author As String
	
	
	
	'调用构造函数，初始化
	Set report = New ExcelReport
	
	'写入 Excel 标题行（第一行）
	Call report.insertData(1,1,1,"创建时间")
	Call report.insertData(1,1,2,"题目")
	Call report.insertData(1,1,3,"作者")	
	
	'得到按照日期排序的视图
	Set db = session.CurrentDatabase
	Set view = db.GetView("byTime")
	
	Set doc = view.GetFirstDocument
	iRow = 2
	
	While Not(doc Is Nothing)
		'按照创建日期排序，处理一周以内的所有文档
		If (doc.Created > Today-7 ) Then 
			'用 Cstr 函数转换时间到字符串
			Call report.insertData(1,iRow,1,Cstr(doc.Created))
			'GetItemValue 返回的是一个字符串数组，我们要其中的第一个
			Call report.insertData(1,iRow,2,doc.GetItemValue("Subject")(0))
			'从 From 当中得到作者名字，然后转成简称
			author = doc.GetItemValue("From")(0)
			Call report.insertData(1,iRow,3,session.CreateName(author).Abbreviated)
			'找到下一个文档
			Set doc = view.GetNextDocument(doc)
			iRow = iRow + 1
		Else
			'发现不是本周内的文档，退出循环
			Goto BreakLoop
		End If
		Set doc = view.GetNextDocument(doc)
	Wend	
BreakLoop:
	
	'保存文件
	Call report.saveFile ("C:\Docs Report This Week.xls")
	'释放资源
	Call report.doQuit
	'发送邮件
	Call	SendMail("Rui R Hu/China/IBM","C:\Docs Report This Week.xls")
	'还可以发送更多地址......
	
	'删除本地文件
	Kill "C:\Docs Report This Week.xls"
End Sub

用邮件发送报表

其中，代码用到了一个自定义的方法 “SendMail”，它可以向一个地址发送一封带附件的邮件，两个参数分别是收件人地址和附件文件地址。代码如下（代码具体的解释已经超出了本文的讨论范围，请参考相关资料）：

Sub SendMail(target As String,attachment As String)
	Dim session As New NotesSession
	Dim db As NotesDatabase
	Dim doc As NotesDocument
	Dim ritme As NotesRichTextItem
	
	Set db = session.CurrentDatabase
	Set doc = New NotesDocument( db )
	doc.Form. = "Memo"
	doc.SendTo = target
	doc.Subject = "Here's the document you wanted"
	Set ritem = doc.CreateRichTextItem("Attachment")
	ritem.EmbedObject EMBED_ATTACHMENT, "", attachment
	Call doc.Send( False )	
End Sub

设置自动运行

写好了代码，我们就可以让这个代理运行了，不同于本文前面提到的简单例子，这里，我们希望让这个代理在每周五下午运行，生成一周的报表发送给一些收件人，设置如下图所示：

图4. 定时运行代理

这样，每周五下午，相关收件人就会收到一封信，其中的附件就是一个 Excel 报表，如下图所示：

图5. 生成的 Excel 报告

用 Excel 宏取得更多操作代码

读者一定对上图当中的 Excel 报告很不满意，因为这个 Excel 报表格式很难看，需要读者手工在每一列的分界处双击一下，来对齐单元格内容。有没有办法可以自动实现这个烦人的操作呢？

答案当然是肯定的，而且我们可以不用参考任何书籍或者资料就开发出需要的代码。

一般地，关于 Excel 报表的更多操作，我们可以通过翻阅参考资料来学习更多功能代码的实现方法，就如同其他技术的学习一样。不过，现在我们有另一种更加方便、巧妙和快速的方法可以得到我们需要的操作代码，那就是利用 Excel 的宏。在相关参考资料不太充足的情况下，这个方法尤为有用。

我们就拿当前需要的“对齐单元格”的需求为例子，看看如何用宏来取得代码。如下图所示：

图6. 用 Excel 宏取得代码

首先，我们打开 Excel 程序，新建一个文件，点击“工具”-“宏”-“录制新宏”，开始录制新的宏。

然后，我们只要简单的在 B 列和 C 列中间双击一下对其单元格，就可以按“停止录制”的按钮以停止录制了。

最后，我们打开“工具”-“宏”-“Visual Basic编辑器”，见到在“模块1”当中，有一个 VBA 的函数。这个函数正是我们刚才录制的宏操作，当中只有一句代码，就是对其单元格的代码。

这样，我们就取得了这个操作的 VBA 代码。在 “ExcelReport” 类当中，我们“照葫芦画瓢”地添加这个功能的方法如下：

'对齐单元格，col 表示列名称，接受 “A” “B” 等列名
Function autoFit(intSheet As Integer, col As String)
	xlApp.Workbooks(1).Worksheets(intSheet).Columns(col+":"+col).EntireColumn.AutoFit
End Function

在业务逻辑代码里面，我们在数据全部填好以后，加上以下三句话，就可以自动对齐单元格了：

Call report.autoFit(1,"A")
Call report.autoFit(1,"B")
Call report.autoFit(1,"C")

这样，我们就可以得到格式整齐的 Excel 报表了。更重要的是，我们也了解了，如果我们想要在我们的代码当中实现一个 Excel 的高级功能，只要简单地用宏来录制相关操作，得到相应的 VBA 代码，就可以快速地在 “ExcelReport” 类里面添加相应的方法了。

调试代码

有了宏的录制，我们就可以“挖掘”更加丰富的Excel高级功能了，不过，这种“照葫芦画瓢”的代码生成方式也不是非常的可靠，可能出错，这时，代码的调试就可以发挥作用了。

同很多语言开发平台一样，Lotus Domino Designer 也支持 LotusScript. 等语言的调试功能。调试的具体用法很简单：首先，在 Lotus Notes 当中将“文件”-“工具”-“调试 LotusScript” 勾上，开始调试模式。然后，在 Notes 当中执行任何 LotusScript. 代码都会进入调试模式。在调试模式当中，我们可以单步前进、用双击的方法设置断点、观察变量等等。一切调试功能都类似于一般的高级语言一样，主要的区别就在于，调试一定是从运行的第一句代码开始停顿，断点只能在调试模式才可以设置。

图7. 调试模式