Project B: Build a Spreadsheet Generator

Project B Build a Spreadsheet Generator

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.

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.