Editor's note: Know a lot about this topic? Want to share your expertise? Participate in the IBM Lotus software wiki program today.
Most Lotus Notes developers are familiar with the limitations of publishing in Lotus Notes. Developers frequently need to create mail merges with data in Lotus Notes applications, create reports with specific formatting, or enable extra functionality, such as a DataPilot table. Lotus Notes does not natively support these features.
Beginning with Lotus Notes 8, the IBM Lotus Symphony productivity suite is included as an optional no-charge application that can be installed with the Lotus Notes client. It includes these applications:
- Lotus Symphony Documents, a word processor
- Lotus Symphony Spreadsheets, a spreadsheet program
- Lotus Symphony Presentations, a presentation program
As a Lotus Notes developer, you can develop publishing solutions that take advantage of the Lotus Symphony toolkit for Lotus Notes. This toolkit offers capabilities for creating solutions that publish information stored in Lotus Notes databases. The toolkit is supported in Lotus Notes 8.5.1 and later. The focus of this article is publishing with Lotus Symphony Documents and Lotus Symphony Spreadsheets.
This article includes snippets of code examples and provides an example database that you can download. The example Lotus Notes application includes the following agents:
- Creating a Lotus Symphony Document. A simple agent made of sample code to generate a Lotus Symphony document. This agent also includes helpful tips on creating and working with documents in a hidden manner.
- Creating a Lotus Symphony Spreadsheet. A simple agent made of sample code to generate a Lotus Symphony spreadsheet.
- Sample Document Report. An agent that provides an elaborate example working with a Lotus Symphony document, creating a report based on Lotus Notes data.
- Sample Spreadsheet Report. An agent that provides a more elaborate example working with a Lotus Symphony spreadsheet, creating a DataPilot table based on Lotus Notes data.
Requirements and limitations
To use the code presented in this article, you must have the Lotus Symphony embedded feature enabled. This step is required because the samples are written as user-triggered agents that run on the local system.
Additionally, this toolkit is supported only on Microsoft® Windows® and Linux®.
Finally, Lotus Symphony supports the following file formats:
- Open Document Format (ODF) 1.1 read/write
- Microsoft 97-2003 read/write
- Microsoft 2007 DOCX read
- Lotus SmartSuite® read
- Comma-separated value (CSV) / Tab-separated value (TSV) read
This article assumes that you are familiar with LotusScript and the concept of object models.
This section focuses on the basics of creating handles to Lotus Symphony applications and their associated objects. Sample code is provided for reference, and complete samples are available in the sample application.
Creating references to Lotus Symphony applications
The first step in sharing data between Lotus Notes and other applications is to get a reference to the target application. Although creating a reference in a simple application is not necessary, including a reference to the Lotus Symphony LSS file allows you to use constants associated with common activities, such as setting font formats, indicating chart types, and setting file formats.
You need to dimension the object variables to represent the Lotus Symphony applications and object types by creating a reference to the Lotus Symphony application:
'The following line creates a handle to the
‘Symphony application object.
Dim symphony As New SymphonyApplication
At this point, you can begin controlling that application through LotusScript, performing such actions as creating or opening documents, passing text, and formatting that text. See listing 1.
Listing 1. Create a reference to the documents and a new document
‘These next lines will represent the ‘Documents collection and a Document object Dim symDocuments As SymphonyDocuments Dim symDocument As SymphonyDocument
Here is a list of the three Lotus Symphony application object classes that you might want to utilize:
Creating a Lotus Symphony document
Continuing from the previous code snippet, you can pass text to the Lotus Symphony document using LotusScript. First, however, you need to get a reference to a new document as shown in listing 2.
Listing 2. Get a reference to a new document
‘The following lines create a handle to the ‘Symphony documents collection, and create a ‘new document Set symDocuments = symphony.Documents Set symDocument = symDocuments.AddDocument()
In the second line, you navigate down the Lotus Symphony object model from the application object to the collection of documents in that application. From there, you use the Add method, which creates a document and returns a reference to it.
To view the object model of a Lotus Symphony document or application, you can open a document and choose Tools - Macros - Macro from the menu. The window shown in figure 1 displays.
Figure 1. Macro window
When you click the Edit button, the BASIC Editor window shown in figure 2 displays.
Figure 2. Lotus Symphony BASIC Editor window
Next, click the Object Catalog button, which is highlighted in figure 3.
Figure 3. Object Catalog button
The Object Organizer window displays, as shown in figure 4.
Figure 4. Objects window
The window shows a list of all existing objects in a hierarchical representation. Double-clicking a list entry opens its subordinate objects.
Another option is to use the Lotus Domino Classes reference tab, shown in figure 5. This tab is accessible when editing a LotusScript agent in IBM Lotus Domino Designer.
Figure 5. Reference tab in Lotus Domino Designer showing Lotus Symphony classes
Using the select method
The SymphonyDocument object contains many of the methods that you want to review. Before you begin typing text into the document, however, you need to think about where the cursor is. Even though you are performing actions on the document programmatically, Lotus Symphony behaves as if a person were manually interacting with it.
For example, when you create a Lotus Symphony document in Lotus Notes, the cursor displays at the top of the page. The same thing occurs when you create a document programmatically. Therefore, let’s take a look at using the Selection.Range object:
Dim txtRange As SymphonyTextRange
Set txtRange = symDocument.Selection.Range
These two lines of code provide a reference to a SymphonyTextRange object. Next, you can use the Text property to enter text into the document.
The Selection object includes the Text property, which is used to pass a string of characters to the document at the current range:
txtRange.Text = "Hello, world!"
This code passes the ubiquitous "Hello, world!" text string to the active document at the current cursor location.
When you want to format text in a Lotus Symphony document, you select the text and then apply your formatting changes. For example, you might want to apply a bold typeface to the "Hello, world!" string that you just entered:
txtRange.Font.Bold = True
This code applies bold formatting to the currently select range.
Creating a Lotus Symphony spreadsheet
You can create a Lotus Symphony spreadsheet using similar code as shown in listing 3.
Listing 3. Creating a Lotus Symphony spreadsheet
(Options) %Include "symphonylsx.lss" Dim symphony As New SymphonyApplication Dim symSheeets As SymphonySpreadsheets Dim symSheet As SymphonySpreadsheet Set symSheets = symphony.Spreadsheets() Set symSheet = symDocuments.AddSpreadsheet()
To summarize, you follow a process beginning with the highest level of the object model (the SymphonyApplication object) and work your way down. Using Variant variables, you get references to different objects that you use frequently. Review the complete code in listing 4. You can copy and paste this code into an agent and step through it with the LotusScript debugger to see it in action.
Listing 4. Complete code for review
Dim symphony As New SymphonyApplication Dim symDocuments As SymphonyDocuments Dim symDocument As SymphonyDocument Set symDocuments = symphony.Documents Set symDocument = symDocuments.AddDocument() Dim txtRange As SymphonyTextRange Set txtRange = symDocument.Selection.Range txtRange.Text = "This is my first Symphony document!" txtRange.Font.Bold = True Set txtRange = symDocument.Paragraphs.Item(2).Range txtRange.Font.Bold = False txtRange.Text = "This is great!"
Creating a report Using Lotus Symphony Documents
Consider this scenario: You work as a program manager, and you use a Lotus Notes application to track the projects in your program. You regularly need to review information about one or more projects and their associated costs.
Creating the document report
You are familiar with working with the Lotus Notes objects and the Lotus Symphony objects available through the toolkit. You can create an agent with code similar to the code in listing 5, which creates the desired report. The sample database contains a view called All Projects and the sample agent, Sample Document Report.
Listing 5. Sample code to create a report in a Lotus Symphony document
%REM Agent 3) Sample Document Report Created Jul 23, 2009 by Symphony Developer/Symphony Developer Description: Comments for Agent %END REM Option Public Option Declare %Include "symphonylsx.lss" Sub Initialize() 'Declare the Notes object variables Dim sess As New NotesSession Dim db As NotesDatabase Dim v As NotesView Dim vnav As NotesViewNavigator Dim entry As NotesViewEntry Dim doc As NotesDocument Dim colCosts As NotesDocumentCollection Dim cost As NotesDocument Dim mgr As String Dim planned As Currency Dim actual As Currency Dim variance As Currency Dim cuser As NotesName Set db = sess.Currentdatabase Set v = db.GetView("All Projects") Set vnav = v.Createviewnav() Set entry = vnav.Getfirstdocument() If entry Is Nothing Then MsgBox "No documents found", , "Exiting" Exit sub End If 'Declare the Symphony object variables Dim symphony As New SymphonyApplication Dim rptDoc As SymphonyDocument %REM Generally, I would create the document in "invisible" mode. To see this is action, uncomment the following line and comment the line after the next REM block. %END REM 'Set rptDoc = symphony.Documents.AddDocument("", False, False) %REM I typically use the following line during development/debugging, then switch to the line above for production. %END REM Set rptDoc = symphony.Documents.AddDocument() Dim txtRange As SymphonyTextRange Set txtRange = rptDoc.Selection.Range txtRange.Text = "Project Summary Report" txtRange.Horialignment = SYMPHONY_TEXT_HORI_ALIGN_CENTER txtRange.Font.Bold = True txtRange.Font.Height = 16 Call txtRange.InsertBreak(txtRange.End, SYMPHONY_TEXT_BREAK_PARAGRAPH) Set txtRange = rptDoc.Paragraphs.Item(2).Range txtRange.Text = "prepared " & CStr(Today) Call txtRange.InsertBreak(txtRange.End, SYMPHONY_TEXT_BREAK_APPEND_PARAGRAPH) Set txtRange = rptDoc.Paragraphs.Item(3).Range Set cuser = New NotesName(sess.Commonusername) txtRange.Text = "by " & cuser.Common Call txtRange.InsertBreak(txtRange.End, SYMPHONY_TEXT_BREAK_APPEND_PARAGRAPH) Call txtRange.InsertBreak(txtRange.End, SYMPHONY_TEXT_BREAK_APPEND_PARAGRAPH) Set txtRange = rptDoc.Paragraphs.Item(5).Range txtRange.Horialignment = SYMPHONY_TEXT_HORI_ALIGN_LEFT txtRange.Font.Bold = False txtRange.Font.Height = 12 'Begin walking the documents in the view, and passing the 'values into the Symphony document. Do Until entry Is Nothing Set doc = entry.Document Set txtRange = rptDoc.Paragraphs.Item(rptDoc.Paragraphs.Count).Range.End Select Case doc.Form(0) Case "ProjectSummary" 'Print desired information for Project documents If (mgr = "") Or (mgr <> doc.PrjMgr(0)) Then mgr = doc.PrjMgr(0) Call txtRange.InsertBreak(txtRange.End, _ SYMPHONY_TEXT_BREAK_APPEND_PARAGRAPH) Set txtRange = rptDoc.Paragraphs.Item(rptDoc.Paragraphs.Count).Range.End txtRange.Text = "Projects managed by " & mgr Call txtRange.InsertBreak(txtRange.End, _ SYMPHONY_TEXT_BREAK_APPEND_PARAGRAPH) txtRange.Font.Bold = True End If Call txtRange.InsertBreak(txtRange.End, _ SYMPHONY_TEXT_BREAK_APPEND_PARAGRAPH) Set txtRange = rptDoc.Paragraphs.Item(rptDoc.Paragraphs.Count).Range.End txtRange.Text = doc.PrjName(0) & " - " & doc.PrjStatus(0) Call txtRange.InsertBreak(txtRange.End, _ SYMPHONY_TEXT_BREAK_APPEND_PARAGRAPH) txtRange.Font.Italic = True Case "Task" 'Print desired information for Task documents Set txtRange = rptDoc.Paragraphs.Item(rptDoc.Paragraphs.Count).Range.End txtRange.Text = "- " & doc.TaskDesc(0) Call txtRange.InsertBreak(txtRange.End, _ SYMPHONY_TEXT_BREAK_APPEND_PARAGRAPH) 'Calculate and print desired information for Cost documents planned = 0 actual = 0 variance = 0 Set colCosts = doc.Responses() If colCosts.Count > 0 Then Set cost = colCosts.Getfirstdocument() Do Until cost Is Nothing If IsNumeric(cost.CapPln(0)) Then planned = planned + cost.CapPln(0) End If If IsNumeric(cost.ExpPln(0)) Then planned = planned + cost.ExpPln(0) End If If IsNumeric(cost.CapAct(0)) Then actual = actual + cost.CapAct(0) End If If IsNumeric(cost.ExpAct(0)) Then actual = actual + cost.ExpAct(0) End If Set cost = colCosts.Getnextdocument(cost) Loop variance = planned - actual End If Set txtRange = rptDoc.Paragraphs.Item(rptDoc.Paragraphs.Count).Range.End txtRange.Text = " Planned: " & Format$(planned, "$#,##0") & _ " Actual: " & Format$(actual, "$#,##0") & _ " Variance: " & Format$(variance, "$#,##0") Call txtRange.InsertBreak(txtRange.End, _ SYMPHONY_TEXT_BREAK_APPEND_PARAGRAPH) End Select Set entry = vnav.Getnextdocument(entry) Loop 'Clean up Print "Cleaning up..." Set txtRange = Nothing Set rptDoc = Nothing Set symphony = Nothing Set cost = Nothing Set colCosts = Nothing Set doc = Nothing Set entry = Nothing Set vnav = Nothing Set v = Nothing Set db = Nothing Set sess = Nothing MsgBox "Complete", , "You report is now complete." End Sub
Creating a report using Lotus Symphony Spreadsheets
Consider this scenario: You are a sales director for a small company, and you would like to review your recent sales using a variety of perspectives. A Lotus Notes database contains the orders for the past month.
Creating the spreadsheet report
As in the previous example, you can use NotesViewNavigator to access the desired data in Lotus Notes and then enter the data in the Lotus Symphony spreadsheet. The unique aspect in this case is that you also use a DataPilot table to display and analyze the data.
The DataPilot feature is a powerful tool for quickly combining, comparing, and analyzing large amounts of data. The data can be arranged, rearranged, or summarized according to different points of view. For more details on DataPilot tables, refer to the IBM Lotus Symphony Information Center.
The sample database contains a view called Orders and the sample agent, Sample Spreadsheet Report.
Listing 6 shows the code that you can use to create a report in the Lotus Symphony spreadsheet.
Listing 6. Sample code to create a report in a Lotus Symphony spreadsheet
%REM Agent 4) Sample Spreadsheet Report Created Jul 23, 2009 by Symphony Developer/Symphony Developer Description: Comments for Agent %END REM Option Public Option Declare %Include "symphonylsx.lss" Sub Initialize() 'Declare the Notes object variables Dim sess As New NotesSession Dim db As NotesDatabase Dim v As NotesView Dim vnav As NotesViewNavigator Dim entry As NotesViewEntry Dim row As long 'These lines obtain a handle to the 'Orders' view, 'and the first entry in it. Set db = sess.Currentdatabase Set v = db.GetView("Orders") Set vnav = v.Createviewnav() Set entry = vnav.Getfirstdocument() If entry Is Nothing Then MsgBox "No documents found", , "Exiting" Exit Sub End If 'Declare the Symphony object variables Dim symphony As New SymphonyApplication Dim rptSht As SymphonySpreadsheet Dim dataSheet As Symphonysheet Dim dpSheet As Symphonysheet 'Variables below are for the DataPilot Dim dataPilotTables As SymphonyDataPilotTables Dim dataPilotTable As SymphonyDataPilotTable Dim src As SymphonyRange Dim dst As SymphonyRange %REM Generally, I would create the document in "invisible" mode. To see this is action, uncomment the following line and comment the line after the next REM block. %END REM 'Set rptSht = symphony.Spreadsheets.AddSpreadsheet("", False, False) %REM I typically use the following line during development/debugging, then switch to the line above for production. %END REM Set rptSht = symphony.Spreadsheets.Addspreadsheet("") %REM The next few lines of code create and name 2 sheets. The first sheet is called 'Data' and will contain the actual data used as the source for the second sheet. The second sheet will be used to display and manipulate the DataPilot. %END REM Set dataSheet = rptSht.Activesheet dataSheet.Name = "Data" Call rptSht.Sheets.Addto(dataSheet, 1, False) Set dpSheet = rptSht.Sheets.Item(2) dpSheet.Name = "DataPilot" 'The following lines create the header row in the source table. With dataSheet .Cells(1, 1).Text = "Rep" .Cells(1, 2).Text = "Customer" .Cells(1, 3).Text = "Region" .Cells(1, 4).Text = "Product" .Cells(1, 5).Text = "Num Units" .Cells(1, 6).Text = "Price" .Cells(1, 7).Text = "Total" .Range("A1:G1").Font.Bold = True End With ' The next lines pass the data from Notes into the spreadsheet. row = 1 Do Until entry Is Nothing row = row + 1 dataSheet.Cells(row, 1).Text = entry.Columnvalues(0) dataSheet.Cells(row, 2).Text = entry.Columnvalues(1) dataSheet.Cells(row, 3).Text = entry.Columnvalues(2) dataSheet.Cells(row, 4).Text = entry.Columnvalues(3) dataSheet.Cells(row, 5).Value = entry.Columnvalues(4) dataSheet.Cells(row, 6).Value = entry.Columnvalues(5) dataSheet.Cells(row, 7).Value = entry.Columnvalues(6) Set entry = vnav.Getnextdocument(entry) Loop 'Finally, these lines create the DataPilot. Set dataPilotTables = dpSheet.DataPilotTables Set src = dataSheet.Range("A1:G" & CStr(row)) Set dst = dpSheet.Cells(1, 1) Set dataPilotTable = dataPilotTables.Add(src,"DPT_ORDERS",dst) Dim rowFieldsString(0) As String rowFieldsString(0) = "Rep" Dim colFieldsString(0) As String colFieldsString(0) = "Region" Dim dataFieldsString(0) As String dataFieldsString(0) = "Total" Dim pageFieldsString(0) As String pageFieldsString(0) = "Product" Call dataPilotTable.AddFields(rowFieldsString, colFieldsString, _ dataFieldsString, pageFieldsString, SYMPHONY_GENERAL_FUNCTION_SUM) End Sub
The IBM Lotus Symphony toolkit is a powerful tool that offers the experienced LotusScript developer a short learning curve to develop a wide range of publishing solutions. As you have learned from this article, you can publish content from Lotus Notes databases into a variety of formats. You can generate reports in Lotus Symphony documents, and you can perform analysis or even create charts using Lotus Symphony spreadsheets.
With technical review by Paul Bastide, Jin Hua Chen, Da Li Yin, and Linda Li.
- Participate in the discussion forum.
- Refer to the IBM Lotus Symphony Information Center. This site is updated regularly to reflect product updates.
- Refer to the IBM Lotus Symphony page of IBM Lotus developerWorks®. It includes great information and many helpful links, including a link to the Lotus Symphony Developers Toolkit.
- Read the latest information here and contribute to the Lotus Symphony Wiki.
- Learn more about OpenOffice.org.