Project B: Build a Spreadsheet Generator

Project B Build a Spreadsheet Generator

Difficulty Level:


Completion Time:

1 hour

Project Materials:

1 Form, 1 View, 1 Shared Action Button, 1 LotusScript Library

Languages Used:


Possible Usages:

Add to any Lotus Notes database to create reports



This project contains two Domino design elementsa shared action button and a LotusScript library. When built, these two design elements can be added to any Lotus Notes database to generate Microsoft Excel spreadsheets. Although this project is not a "Reference Library" application, you may want to include it in a reference or any database.

The first design element is a shared action button. This button triggers the PromptUser subroutine and asks the user to select a view to be used as the basis for the spreadsheet. Users have the option to select a view from the dropdown list or to cancel the transaction.

The second design element, the LotusScript library, contains all subroutines and functions required to produce a spreadsheet. This library consists of four subroutines and one function. The combination of these design elements performs the following tasks.

  1. Scan the database and build a list of views
  2. Prompt the user to select the view to be used as the basis for the spreadsheet
  3. Scan the selected view to retrieve the view name, column titles, field values, and column widths
  4. Use the view parameters to the build the spreadsheet
  5. Generate a unique file name based on the date and time stamp
  6. Create the spreadsheet object
  7. Format the cells of the spreadsheet by setting the font type and style
  8. Parse the documents displayed in the view and add them to the spreadsheet
  9. Close the spreadsheet
  10. Email the spreadsheet to the user


This script library is designed to work with columns that are set to a field value. All columns that contain a formula or simple action are ignored. It's important to note that the spreadsheet could include columns containing a formula and could also build graphs. However, this requires more advanced programming to generate. The point of this exercise is to generate a simple spreadsheet and to illustrate how the code can be applied to a database.


This script library requires the Microsoft Excel product to be installed on the user's workstation. The user will receive an error if Microsoft Excel is not installed.


Create the Database

To start this project, launch the Lotus Domino Designer client and create a blank database. When the Designer client is running, select the File > Database > New menu options (see Figure 10.9). Specify an application title and file name. Be sure to select -Blank- as the template type.

Figure 10.9. New Database dialog


Create the Spreadsheet Script Library

The LotusScript library will hold a number of common subroutines used to generate a Microsoft Excel spreadsheet based on a view in the Lotus Notes database.


The following code is available in the developer's toolbox. Simply open the "Project Library" database in your Lotus Notes client and navigate to the "Script Library" section for the appropriate project. After you locate the project, copy and paste the code into the Designer client for the current project. Be sure the programmer's pane has focus before pasting the code. Alternatively, you can elect to manually type the code. You can then save and close the library. When prompted for a name, specify ReportLibrary.

To create the library, select the Create > Design > Script Library > LotusScript Library menu options.


Locate the "(Declarations)" section and add the following global statements in the Programmer's pane. These objects will be used throughout the library subroutines and functions.

Dim s As NotesSession
Dim db As NotesDatabase
Dim view As NotesView


Initialize Subroutine

The following statements are used to initialize the session and database objects. Add these statements in the Initialize section of the Programmer's pane.

Sub Initialize

 Set s = New NotesSession
 Set db = s.CurrentDatabase

End Sub


PromptUser Subroutine

The PromptUser subroutine displays a list of views in the Notes application database to the user. The user then selects a view to be used as the basis for the spreadsheet. Type the following in the Programmer's pane.

Sub PromptUser

 Dim w As New NotesUiWorkspace
 Dim view As NotesView
 Dim x As Integer
 Dim result as String

 ' Build an array of views in the database
 If Not Isempty (db.Views) Then
 Forall v In db.Views
 Redim Preserve myList(x) As String
 myList(x) = v.Name
 x = x + 1
 End Forall
 End If

 ' Ask the user to select a view to generate the spreadsheet
 result$ = w.Prompt( PROMPT_OKCANCELCOMBO, +_
 "Make your choice","Select a view to build the spreadsheet",+_
 "Select View", myList)
 If result$ <> "" Then
 Print "Found view: " + result$
 Call GenerateReport ( result$ )
 End If
 Print "Complete"

End Sub


GenerateReport Subroutine

This subroutine will parse the design elements in the selected view into arrays. This subroutine builds an array of column titles, column widths, and field values. It also counts the total number of columns and identifies the view name. These arrays and data values are then passed to another subroutineCreateSpreadsheetto build the spreadsheet file. Insert the following in the Programmer's pane.

Sub GenerateReport (result As String)

 '---- Build data arrays for the selected view
 Dim x As Integer
 Set view = db.GetView(result$)
 Forall c In view.Columns
 If c.IsField Then

 ' Build an array of column names
 Redim Preserve ColumnName(x) As String
 ColumnName(x) = c.Title

 ' Build an array of column Widths
 Redim Preserve ColumnWidth(x) As Variant
 ColumnWidth(x) = c.width

 ' Build an array of column field names
 Redim Preserve FieldName(x) As String
 FieldName(x) = c.ItemName

 x = x + 1
 End If
 End Forall

 '---- Create spreadsheet based on the selected view.
 '---- Parm1 - array of all valid fields in the view
 '---- Parm2 - array of the column titles
 '---- Parm3 - array of the column widths
 '---- Parm4 - the name of the view
 '---- Parm5 - total number of columns in the view
 Call CreateSpreadsheet ( FieldName, ColumnName, _
 ColumnWidth, view.Name, x-1 )

End Sub


CreateSpreadsheet Subroutine

The CreateSpreadsheet subroutine is used to build the Microsoft Excel spreadsheet. This subroutine requires five parametersan array of field names, an array of column titles, an array of column widths, a view name, and the total number columns in the view. These parameters define the content and layout of the spreadsheet. Type the following in the Programmer's pane.

Sub CreateSpreadsheet (field As Variant, column As Variant, ColWidth As Variant,
Sheetname As String, cntr As Integer)

 On Error Goto oops
 Dim file As Variant
 Dim wksSheet As Variant
 Dim filename As String
 Dim alphabet(25) As String
 Dim cell As String
 Dim value As String
 Dim doc As NotesDocument
 Dim row As Long
 Dim x As Integer
 Dim n As Integer

 '---- Build the filename for the spreadsheet
 Dim theDate As String
 Dim theTime As String
 theDate = removeString ( Format(Date$, "Medium Date"), "-")
 theTime = removeString ( Format(Time$, "Long Time"), ":")
 theTime = removeString ( theTime, " ")
 Filename = "C:Report" + "_" + theDate + "_" + theTime
 Print "Building file: " + Filename

 '---- Build an array of the alphabet
 For n = 65 To 90
 'Print "Letter" + Cstr(n-65) + " = " + Chr$(n)
 alphabet(n-65) = Chr$(n)

 '---- Create the spreadsheet file object
 Set file = CreateObject("Excel.Application")
 file.Visible = False
 file.DisplayAlerts = False
 Set wksSheet = file.Worksheets.Add = Sheetname
Set wksSheet = file.Worksheets( Sheetname )

'---- Set the column width for first 26 columns
For x=0 To cntr
Print "Set the default column width complete."

'---- Set font style for spreadsheet
With file.Range("A:Z")
 .WrapText = True
 .Font.Name = "Arial"
 .Font.FontStyle = "Regular"
 .Font.Size = 8
End With
Print "Set the font style complete."

'---- Set font style for header row
With file.Range("A1:Z1")
 .WrapText = True
 .Font.Name = "Arial"
 .Font.FontStyle = "Bold"
 .Font.Size = 8
End With
Print "Spreadsheet initialized."

'---- Load the spreadsheet with data
Print "Starting data load into spreadsheet. Please be patient"
Set view = db.GetView( SheetName )
Set doc = view.GetFirstDocument
row = 1

' Create the column title row
Set wksSheet = file.Worksheets( Sheetname )
For x=0 To cntr
 cell$ = Alphabet(x) + Cstr( row )
 file.Range( cell$ ).Select
 file.Activecell.FormulaR1C1 = Column(x)
row = 2

' Create the data rows
While Not(doc Is Nothing)
 Set wksSheet = file.Worksheets( Sheetname )
 'Loop through each column and add data to the row
 For x=0 To cntr
 cell$ = Alphabet(x) + Cstr( row )
 file.Range( cell$ ).Select
 file.Activecell.FormulaR1C1 =doc.GetItemValue(field(x))
 Set doc = view.GetNextDocument( doc )
 row = row + 1
 Print "Data load complete."

 '---- Save, close and email file to the person
 file.activeworkbook.saveas Filename

 ' Comment out the following line to skip sending the email
 SendReport ( Filename )
 Print "Report sent."

 ' Comment out the following line to save file to the harddrive
 Kill Filename+".xls"

 Set file = Nothing
 Exit Sub

 Msgbox "Error" & Str(Err) & ": " & Error$
 Kill Filename+".xls"

End Sub


By default, this subroutine will email the spreadsheet to the user. However, if you prefer that the files be saved to the user's hard drive, comment out the following three statements: SendReport ( Filename ), Print "Report sent.", and Kill Filename+".xls. This will cause the files to be saved to the user's hard drive in the C: directory.


RemoveString Function

This function removes all instances of a specific character from the target object string. This function checks each character in the string. If the current character does not match the search string, then the character is added to a temporary variable. If the character does match, then the character is skipped. The result is a rebuilt string that is returned to the calling subroutine. Insert the following in the Programmer's pane.

Function RemoveString (object As String, SearchString As String) As Variant

 Dim tempString As String
 Dim j as Integer
 tempString = ""
 For j% = 1 To Len(object)
 If Mid$(object, j%, 1) <> SearchString Then
 tempString = tempString + Mid$(object, j%, 1)
 End If
 RemoveString = tempString

End Function


SendReport Subroutine

The SendReport subroutine is used to create an email, attach the spreadsheet file, and send it to the person who generated the report. Type the following in the Programmer's pane.

Sub SendReport (filename As String)

 Dim PersonName As New NotesName(s.UserName)
 Dim rtitem As NotesRichTextItem
 Dim object As NotesEmbeddedObject
 Dim doc As NotesDocument
 Set doc = New NotesDocument(db)
 doc.Form = "Memo"
 doc.SendTo = PersonName.Abbreviated
 doc.Subject = "Report - " + filename
 Set rtitem = New NotesRichTextItem(doc, "Body")
 Call rtitem.AddNewline(1)
 Call rtitem.AppendText("Attached below is the requested report. ")
 Call rtitem.AddNewline(2)
 Set object = rtitem.EmbedObject (EMBED_ATTACHMENT, "", Filename+".xls")
 doc.Send False
 Msgbox "The requested report has been sent to you.", 0, "Success"

End Sub

Save and close the LotusScript library. When prompted, name the library ReportLibrary.

Create the Generate Report Shared Action

The shared action button prompts the user to select the view to be used to build the spreadsheet. To create the button, select the Create > Design > Shared Action menu options and name the button Generate Report. Close the properties dialog.

Next, change the Language Selector from Formula to LotusScript. Locate the (Options) section and insert the following in the Programmer's pane.

Use "ReportLibrary"

Add the following in the Click event.

Sub Click(Source As Button)
 Call PromptUser
End Sub

Save and close the button when complete.

Create the Contact Form

This form is being created for illustration purposes to demonstrate the GenerateReport subroutine. However, this form is not required to actually implement the spreadsheet script library. This form includes four fieldsname, address, phone, and email.

To create the form, select the Create > Design > Form menu options. Give the form a descriptive title at the top of the formsuch as Contactand add the following text field descriptions down the left side of the form.

  • Name:
  • Address:
  • Phone:
  • Email:

Next, create the following fields using the Create > Field menu options. Be sure to set the data type, formula, and other attributes for each field on the form using the properties dialog box and/or Programmer's pane.

Field Name


Default Value Formula



Text, Editable



Text, Editable



Text, Editable



Text, Editable


Select the File > Save menu options to save the file. When prompted, name the form Contact | Contact. Close the form after the file has been saved.

Create the Contact View

This view will be used as the basis to demonstrate the GenerateReport functionality and is included for illustration purposes.

By default, a view called (untitled) is automatically created when the database is first created. To configure this view, navigate to Views in the Design pane and double-click on the view called "(untitled)". When the view is displayed, the Designer client will immediately display the properties dialog for the view. Specify Contacts as the view name and alias in tab 1. Close the properties dialog.

Next, click on the header for the predefined column and delete the column. To build the view, select the Create > Append New Column menu options to add four columns to the view. For each column, switch the Language Selector to Field. Set column one through four to the following field values:

  • Name
  • Address
  • Phone
  • Email

After the column value is specified, select Design > Column Properties. Click on the column header and specify a title in the properties dialog for each column.

Finally, select the Create > Action > Insert Shared Action menu options and insert the Generate Report shared action button. Save and close the view.

Congratulations! You have completed the project.

Add a couple contact documents and give the button a try. Note: You must have Microsoft Excel installed on your workstation in order for the report generator to work.

An Introduction to the Lotus Domino Tool Suite

Getting Started with Designer

Navigating the Domino Designer Workspace

Domino Design Elements

An Introduction to Formula Language

An Introduction to LotusScript

Fundamentals of a Notes Application

Calendar Applications

Collaborative Applications

Reference Library Applications

Workflow Applications

Web Applications

Design Enhancements Using LotusScript

Design Enhancements Using Formula Language

View Enhancements

Sample Agents

Miscellaneous Enhancements and Tips for Domino Databases

Data Management


Application Deployment and Maintenance


Appendix A. Online Project Files and Sample Applications

Appendix B. IBM® Lotus® Notes® and Domino®Whats Next?

show all menu

Lotus Notes Developer's Toolbox(c) Tips for Rapid and Successful Deployment
Lotus Notes Developers Toolbox: Tips for Rapid and Successful Deployment
ISBN: 0132214482
EAN: 2147483647
Year: N/A
Pages: 293
Authors: Mark Elliott
Similar book on Amazon © 2008-2017.
If you may any questions please contact us: