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

àì夳堔傛蜴生んèń 2022-01-13 07:55 233阅读 0赞

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（六）之为自定义对象模拟事件》][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()})

[33. _LotusScript]: http://blog.csdn.net/starrow/article/details/9223177