Microsoft Office是应用最广泛的办公软件，绝大多数公司的必备软件。其中的Excel操作直观方便，很多公司都有大量数据以Excel文件的格式保存。一个LotusNotes应用在很多情况下都会需要导入Excel文件，比如程序初始化的时候导入历史数据，批量导入配置数据，定期导入人工输入或从其他系统导出的Excel文件。导入Excel文件的功能，从原理上说并不复杂，就是读取Excel工作表中的一行数据，保存为一个Notes文档。应用面向对象的思想，加上良好的设计，可以写出一个具有很好通用性，使用方便的导入Excel文件的类ExcelImporter。
看一个调用这个类的实例：
Public Function Import
Dim importer As New ExcelImporter()
Call importer.Import("fmRecord")
End Function
只需要创建一个ExcelImporter类的实例，然后调用Import()方法就完成了导入。唯一需要传入的参数是创建文档所用的表单名。文档中的字段和工作表列的对应关系这种元数据，可以不同的方式获得。一种方案是导入时指定一个视图，该视图的每一列的值都源自一个字段，而没有常数和公式等情况。工作表的列和视图列一一对应。Excel某一列的值就保存到视图对应列所关联的字段。第二种方案是将这种对应关系显式写在Excel中。一般工作表中的第一行是列标题，我们就在第二行里填写每一列对应的字段名。两相比较，创建和调整一个视图更为麻烦，还会消耗索引资源，所以ExcelImporter类采用了第二种方案。
有些情况下，Notes文档中的某些字段值不能简单复制工作表对应的单元格的数据，需要根据其他字段或者配置文档计算。这些应具体情况而变的代码，不能写在ExcelImporter类里，否则它就丧失了通用性。这种情况同样可以应用《33. 面向对象的LotusScript（六）之为自定义对象模拟事件》里面设计的事件机制来解决。为此，ExcelImporter类继承了EventPublisher类，调用它的代码注册一个函数，响应保存文档前触发的事件，在这个函数中可以完成对当前文档任意字段的任意计算。下面的例子中SaveRecord()函数通过调用ExcelImporter实例的多个方法，获的当前的工作表、行数和文档等信息，然后计算Description字段的值。LocalLib是这段代码所在的脚本库的名称。
Public Function Import
Set importer=New ExcelImporter()
Call importer.AddEventHandler("QuerySaveDoc", {Use"LocalLib":SaveRecord})
Call importer.Import("fmRecord")
End Function
Public Function SaveRecord()
On Error GoTo EH
Dim sheet As Variant
Set sheet=importer.GetSheet()
Dim rowNum As Integer
rowNum=importer.GetRowNum()
Dim doc As NotesDocument 'current imported document
Set doc=importer.GetCurrentDoc()
'from code to desc
Dim account As String, desc As Variant
account=sheet.Cells(rowNum, 4).Value
Call doc.Replaceitemvalue("Account", CStr(account))
desc=DBLookUp("vwAccount", account, 1, "")
If Not IsEmpty(desc) Then
Call doc.Replaceitemvalue("Description", desc)
End If
Exit Function
EH:
MsgBox GetErrorMsg()
Exit Function
End Function
下面给出这个ExcelImporter类的代码：
Class ExcelImporter As EventPublisher
Private session As NotesSession
Private ws As NotesUIWorkspace
Private db As NotesDatabase
Private doc As NotesDocument 'current imported document
Private xlFileName As String
Private xlApp As Variant
Private xlWork As Variant
Private xlSheet As Variant
Private docForm As String
Private colNum As Integer 'column numbers in the imported Excel file
Private rowNum As Integer 'current row number in the worksheet
Private ImportTime As String
Public Debug As Boolean
Sub New()
Set session = New NotesSession
Set ws = New NotesUIWorkspace
Set db = session.CurrentDatabase
colNum = 0
'import from the 3rd line. The first two lines are the column title and the corresponding field name.
rowNum = 3
xlFileName = ""
ImportTime = Format( Now(), "yyyy-mm-dd hh:mm:ss" ) 'The time used to mark the imported documents.
End Sub
'The entry function. docForm is the form used to create documents.
Public Function Import(docForm As String) As Integer
If Not Debug Then On Error GoTo QuitApp
Import = False
Me.docForm = DocForm
If Not CreateExcelObject() Then Exit Function
Call GetColumns()
Call ImportData()
Import = True
xlApp.Quit
Print "导入完成"
Exit Function
QuitApp:
MsgBox GetErrorMsg
If Not (xlApp Is Nothing) Then xlApp.Quit
Exit Function
End Function
'Create an Excel COM object.
Private Function CreateExcelObject() As Boolean
CreateExcelObject = False
Dim result As Variant
result=ws.Openfiledialog(False, "Lotus Notes", "Excel files|*.xlsx|Excel 97-2003 files|*.xls")
If IsEmpty(result) Then Exit Function
me.xlFileName=result(0)
Print "正在初始化 Excel对象..."
Set xlApp = CreateObject("Excel.Application")
If xlApp Is Nothing Then Error 6503,"创建 Excel.Application对象失败，请确认是否安装Excel。"
xlApp.Visible = False
Set xlWork = xlApp.Workbooks.Open( xlFileName )
Set xlSheet = xlWork.ActiveSheet
CreateExcelObject = True
End Function
'Calculate the numbers of the effective (non-empty) columns in the Excel worksheet.
Private Function GetColumns
While xlSheet.Cells(1, 1+colNum).Value><""
colNum=colNum+1
Wend
End Function
'Import the data. 10 continual rows which are empty in the first column are treated as the end of the file.
Private Function ImportData()
Dim BlankRow As Integer
BlankRow = 0
While (BlankRow < 10)
Print "正在处理第 " & rowNum & "行"
If xlSheet.Cells(rowNum,1).Value = "" Then
BlankRow = BlankRow + 1
Else
BlankRow = 0
Call ImportLine()
End If
rowNum = rowNum + 1
Wend
End Function
'Import one line of data.
Private Function ImportLine()
Call OnEvent("QueryCreateDoc")
If (Not EventResult) Then Exit Function
Set doc = db.CreateDocument
doc.Form = Me.docForm
Dim Field As String, value As Variant
Dim i As Integer
For i = 1 To colNum
'value=xlSheet.Cells(rowNum,i).Value
Call doc.ReplaceItemValue( xlSheet.Cells(2, i).Value, xlSheet.Cells(rowNum,i).Value )
Next
doc.ImportTime = ImportTime 'Mark all the document with the preset time.
Call doc.ComputeWithForm( False, False )
Call OnEvent("QuerySaveDoc")
Call doc.Save( True, False )
End Function
%REM
Property method. Get the current row in the Excel worksheet.
%END REM
Public Function GetRowNum() As Integer
GetRowNum=rowNum
End Function
%REM
Property method. Get the current Excel worksheet.
%END REM
Public Function GetSheet() As Variant
Set GetSheet=me.xlSheet
End Function
%REM
Property method. Get the current document.
%END REM
Public Function GetCurrentDoc() As NotesDocument
Set GetCurrentDoc=me.doc
End Function
End Class
可以看出，ExcelImporter类还模拟了QueryCreateDoc事件。因为有些时候，我们还要判断Excel工作表里某一行的数据是否有效，以决定是否创建一个文档。这时候就可以注册QueryCreateDoc事件的响应程序，当其返回真时才创建文档。
Call importer.AddEventHandler("QueryCreateDoc", {Use"LocalLib":EventResult=CheckRecord()})