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 |
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
Lotus Notes View
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)
Wend
|
 |
report.Preview
End Sub
|
|
As one can see, only 5 specifications are needed to produce the report.
- First one, in the options section, we need to include the Lotusscript library ExcelReport.
- The library is developed in OO (Object Oriented) mode, so we have to declare an object of type ExcelReport, with a dim specification.
- An object is created with the new keyword, and passing the model file name and path as parameter
- For each Notes document to insert in the report, the method fillReportLine is called with the corresponding document as parameter
- 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 !
|
|
