Lotus Script中List简化重复查询操作(开发进阶)

Published by admin on 07月 31, 2010

查询一张Excel表格中多列姓名，看是否在数据库中存在。但仔细会发现，有大量姓名是重复的，如果频繁查询数据库那么效率一定会比较低。如何采用方法使用List不断地保存查询的信息，如果List中不存在信息，则向数据库中查询。

Dim uname As String

Dim unameList List As Boolean //定义列表其中每一个Key对应的Value是布尔类型的数据

……

If Not Iselement(unameList(uname)) Then unameList(uname)=isExistPeople(uname)

//查询对应的uname是否在列表中，如果不存在则添加

excelsheet.Cells(row,index).Font.Bold=Not unameList(uname)   //获取uname键值存储的数据

      

       说明：程序示例中，表格从第四行开始第一列、六到十一列都是需要查询的姓名。查不出的姓名将用粗体标记。

Sub Click(Source As Button)

       Dim workspace As New NotesUIWorkspace      

       Dim files As Variant      

       Dim excelApplication As Variant

       Dim excelsheet As Variant

       Dim excelWorkbook As Variant   

       Dim view As notesview

       Dim nav As NotesViewNavigator

       Dim entry As NotesViewEntry

       Dim row As Integer

       Dim uname As String

       Dim unameList List As Boolean

       Dim index As Integer    

       files=workspace.OpenFileDialog(False,“打开能力评估对应关系表核实姓名“,“Excel file|*.xls”,“用户桌面目录“,“能力评估对应关系表.xls”)

       writeRow=0

       If files(0)=“” Then

              Exit Sub

       Else       

              Set excelApplication=CreateObject(“Excel.Application”)

              Set excelWorkbook =excelApplication.Workbooks.Open(files(0))

              Set excelsheet =excelWorkbook.WorkSheets(“能力对应关系表“)     

              row=4

              ‘1  被评估人  6~11  评估人1-6     

              Do

                     uname=Trim(Cstr(excelSheet.Cells(row,1).Value))

                     If uname=“” Then Exit Do

                     If Not Iselement(unameList(uname)) Then                              

                            unameList(uname) =isExistPeople(uname)                        

                     End If                         

                     excelsheet.Cells(row,1).Font.Bold=Not unameList(uname)

                     For index=6 To 11

                            uname=Trim(Cstr(excelSheet.Cells(row,index).Value) )                   

                            If Not Iselement(unameList(uname)) Then                              

                                   unameList(uname) =isExistPeople(uname)                               

                            End If

                            excelsheet.Cells(row,index).Font.Bold=Not unameList(uname)

                     Next      

                     row=row+1

              Loop

             

              excelWorkbook.save

              excelWorkbook.close

              excelApplication.Quit

              Set excelApplication=Nothing

              Msgbox “已检查“+Cstr(row-3)+“行数据“

       End If

End Sub

Function isExistPeople(peopleName As String) As Boolean 

       Dim namesDb As NotesDatabase

       Dim session As NotesSession

       Dim servername As String

       Dim NamesDir As String

       Dim peopleView As NotesView

       Dim peopleDoc As NotesDocument

       servername = session.currentdatabase.Server

       NamesDir= “names20071116.nsf”

       Set namesDb = Session.GetDatabase(servername,NamesDir)

       If namesDb.IsOpen Then     

              Set peopleView = namesDb.GetView(“People”)

              Set peopleDoc = peopleView.GetDocumentByKey(peopleName,True)                    

              isExistPeople= Not (peopleDoc Is Nothing )       

       Else

              isExistPeople=False

              Exit Function

       End If

       Set peopleDoc =Nothing

End Function