Project B: Build a Spreadsheet Generator

Project B Build a Spreadsheet Generator

Difficulty Level:	Easy
Completion Time:	1 hour
Project Materials:	1 Form, 1 View, 1 Shared Action Button, 1 LotusScript Library
Languages Used:	LotusScript
Possible Usages:	Add to any Lotus Notes database to create reports

 

Architecture

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

Note

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.

Note

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.

Tip

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.

Declarations

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
 '------------------------------
 x=0
 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$)
 x=0
 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)
 Next

 '------------------------------
 '---- Create the spreadsheet file object
 '------------------------------
 Set file = CreateObject("Excel.Application")
 file.Visible = False
 file.DisplayAlerts = False
 file.Workbooks.Add
 Set wksSheet = file.Worksheets.Add
 wksSheet.name = Sheetname
 file.Worksheets("Sheet1").Delete
 file.Worksheets("Sheet2").Delete
 file.Worksheets("Sheet3").Delete
Set wksSheet = file.Worksheets( Sheetname )
wksSheet.Select

'------------------------------
'---- Set the column width for first 26 columns
'------------------------------
For x=0 To cntr
wksSheet.columns(Alphabet(x)).Columnwidth=Cint(colWidth(x)+5)
Next
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 )
wksSheet.Select
For x=0 To cntr
 cell$ = Alphabet(x) + Cstr( row )
 file.Range( cell$ ).Select
 file.Activecell.FormulaR1C1 = Column(x)
Next
row = 2

' Create the data rows
While Not(doc Is Nothing)
 Set wksSheet = file.Worksheets( Sheetname )
 wksSheet.Select
 '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))
 Next
 Set doc = view.GetNextDocument( doc )
 row = row + 1
 Wend
 Print "Data load complete."

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

 ' 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

oops:
 Msgbox "Error" & Str(Err) & ": " & Error$
 file.activeworkbook.close
 Kill Filename+".xls"

End Sub

Note

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
 Next
 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	Type	Default Value Formula	Remarks
Name	Text, Editable
Address	Text, Editable
Phone	Text, Editable
Email	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.