35. 面向对象的LotusScript（七）之导入Excel

Microsoft Office是应用最广泛的办公软件，绝大多数公司的必备软件。其中的Excel操作直观方便，很多公司都有大量数据以Excel文件的格式保存。一个LotusNotes应用在很多情况下都会需要导入Excel文件，比如程序初始化的时候导入历史数据，批量导入配置数据，定期导入人工输入或从其他系统导出的Excel文件。导入Excel文件的功能，从原理上说并不复杂，就是读取Excel工作表中的一行数据，保存为一个Notes文档。应用面向对象的思想，加上良好的设计，可以写出一个具有很好通用性，使用方便的导入Excel文件的类ExcelImporter。

看一个调用这个类的实例：

Public Function ImportDim 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 ImportSet importer=New ExcelImporter()Call importer.AddEventHandler("QuerySaveDoc", {Use"LocalLib":SaveRecord})Call importer.Import("fmRecord")End FunctionPublic Function SaveRecord()On Error GoTo EHDim sheet As VariantSet sheet=importer.GetSheet()Dim rowNum As IntegerrowNum=importer.GetRowNum()Dim doc As NotesDocument 'current imported documentSet doc=importer.GetCurrentDoc()'from code to descDim account As String, desc As Variantaccount=sheet.Cells(rowNum, 4).ValueCall doc.Replaceitemvalue("Account", CStr(account))desc=DBLookUp("vwAccount", account, 1, "")If Not IsEmpty(desc) ThenCall doc.Replaceitemvalue("Description", desc)End IfExit FunctionEH:MsgBox GetErrorMsg()Exit Function End Function

下面给出这个ExcelImporter类的代码：

Class ExcelImporter As EventPublisherPrivate session As NotesSessionPrivate ws As NotesUIWorkspacePrivate db As NotesDatabasePrivate doc As NotesDocument 'current imported documentPrivate xlFileName As StringPrivate xlApp As VariantPrivate xlWork As VariantPrivate xlSheet As VariantPrivate docForm As StringPrivate colNum As Integer'column numbers in the imported Excel filePrivate rowNum As Integer'current row number in the worksheet Private ImportTime As StringPublic Debug As BooleanSub New()Set session = New NotesSessionSet ws = New NotesUIWorkspaceSet db = session.CurrentDatabasecolNum = 0'import from the 3rd line. The first two lines are the column title and the corresponding field name.rowNum = 3xlFileName = ""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 IntegerIf Not Debug Then On Error GoTo QuitAppImport = FalseMe.docForm = DocFormIf Not CreateExcelObject() Then Exit FunctionCall GetColumns()Call ImportData()Import = TruexlApp.QuitPrint "导入完成"Exit FunctionQuitApp:MsgBox GetErrorMsgIf Not (xlApp Is Nothing) Then xlApp.QuitExit FunctionEnd Function'Create an Excel COM object.Private Function CreateExcelObject() As BooleanCreateExcelObject = FalseDim result As Variantresult=ws.Openfiledialog(False, "Lotus Notes", "Excel files|*.xlsx|Excel 97-2003 files|*.xls")If IsEmpty(result) Then Exit Functionme.xlFileName=result(0)Print "正在初始化 Excel对象..."Set xlApp = CreateObject("Excel.Application")If xlApp Is Nothing Then Error 6503,"创建 Excel.Application对象失败，请确认是否安装Excel。"xlApp.Visible = FalseSet xlWork = xlApp.Workbooks.Open( xlFileName )Set xlSheet = xlWork.ActiveSheetCreateExcelObject = TrueEnd Function'Calculate the numbers of the effective (non-empty) columns in the Excel worksheet. Private Function GetColumnsWhile xlSheet.Cells(1, 1+colNum).Value><""colNum=colNum+1WendEnd 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 IntegerBlankRow = 0While (BlankRow < 10)Print "正在处理第 " & rowNum & "行"If xlSheet.Cells(rowNum,1).Value = "" ThenBlankRow = BlankRow + 1ElseBlankRow = 0 Call ImportLine()End IfrowNum = rowNum + 1WendEnd Function'Import one line of data.Private Function ImportLine()Call OnEvent("QueryCreateDoc")If (Not EventResult) Then Exit FunctionSet doc = db.CreateDocumentdoc.Form = Me.docFormDim Field As String, value As VariantDim i As IntegerFor i = 1 To colNum'value=xlSheet.Cells(rowNum,i).Value Call doc.ReplaceItemValue( xlSheet.Cells(2, i).Value, xlSheet.Cells(rowNum,i).Value )Nextdoc.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%REMProperty method. Get the current row in the Excel worksheet.%END REMPublic Function GetRowNum() As IntegerGetRowNum=rowNumEnd Function%REMProperty method. Get the current Excel worksheet.%END REMPublic Function GetSheet() As VariantSet GetSheet=me.xlSheetEnd Function%REMProperty method. Get the current document.%END REMPublic Function GetCurrentDoc() As NotesDocumentSet GetCurrentDoc=me.docEnd FunctionEnd Class

可以看出，ExcelImporter类还模拟了QueryCreateDoc事件。因为有些时候，我们还要判断Excel工作表里某一行的数据是否有效，以决定是否创建一个文档。这时候就可以注册QueryCreateDoc事件的响应程序，当其返回真时才创建文档。

Call importer.AddEventHandler("QueryCreateDoc", {Use"LocalLib":EventResult=CheckRecord()})