Thierry Seunevel Thierry 
 Tech Corner       
  Home   Missions Tech corner Download Resume Contact
Home > Tech Corner > Excel Report Version FR
Version 1
May 2005
ExcelReport : Using Excel to print from Lotus Notes (1)

Printing high quality report is the less satisfactory function of the Notes/Domino platform.
Using Excel is a way to bypass this issue, and there are many examples of this use.
The Lotusscript library described here brings an original and effective solution to the use of Excel from Notes
. Principle
. 1st example
. Excel model
. ExcelReport
. More
. New with V1
A Notes tool to edit any item in any document
See details
All the examples that I have seen on paper or online started from a new empty Excel file, and used OLE verbs to fill the file. That's an heavy duty.
The basic idea behind my Lotusscript class is to start from an Excel file which is used as a model.

This file contains the look of the target report: constant texts, column headers, borders, fonts and background colors can be specified with the easiest tool to create an Excel file: the Excel program.
If you plan to sum columns, compute averages and so on, it's the purpose of Excel to do that for you.
As the purpose of such an Excel report is to receive data from Notes, I chose an easy way to assign the destination cell for each Notes field: using the name of the source Notes item for the target Excel cell.
A first example
To show all this in action, here comes a simple example.
The first figure shows a Notes View of a jazz CD database, followed by the same documents in a Excel layout than can be previewed or printed.

The Excel model is shown below

Example Lotus Notes View

Lotus Notes View

Example Excel Report from Notes

Excel file created with the data of the preceding View

The Lotusscript Agent
The last piece is the Lotusscript agent used to produce the Excel report, and is shown here.

In the Options section :
Option Public
Option Explicit
Use "ExcelReport_1"
In the Initialize subprocedure :
Sub Initialize
Dim session As New NotesSession
Dim db As NotesDatabase
Dim vw As NotesView
Dim doc As NotesDocument
Dim report As ExcelReport								
Set db = session.currentDatabase
Set vw = db.getView("cdbytitre")
Set report = New ExcelReport("l:\notes\modeles\discotk.xls")  	
Set doc = vw.getFirstDocument
While Not doc Is Nothing
	report.fillReportLine doc	
	Set doc = vw.getNextDocument(doc)
End Sub
As one can see, only 5 specifications are needed to produce the report.

  1. First one, in the options section, we need to include the Lotusscript library ExcelReport.

  2. The library is developed in OO (Object Oriented) mode, so we have to declare an object of type ExcelReport, with a dim specification.

  3. An object is created with the new keyword, and passing the model file name and path as parameter

  4. For each Notes document to insert in the report, the method fillReportLine is called with the corresponding document as parameter

  5. When all documents have been processed, the preview method is called. This is the same as the Excel preview function. The report is displayed on screen, and can be printed.
    We could have used printOut and or saveAs methods to print the report, and/or save it to disk.

That's all !

    Top  Top    | 1st example Next

©  Thierry Seunevel (2004)