Thierry Seunevel Thierry 
 Tech Corner       
  Home   Missions Tech corner Download Resume Contact
Home > Tech Corner > Excel Report

ExcelReport : Using Excel to print from Lotus Notes (6)

. Principle
. 1st example
. Excel Model
. ExcelReport
. More
. New with V1
Model File Location
In the simple example shown above, the model file was located on disk.

In the real world a better solution is to hold it as an attachment to a Notes Document. It can then be deployed through replication or database copy, be independent of the file system and avoid any problem due to the names of network drives.

If the model is attached to a Notes document the file must be first detached on disk in a temporary location, on the local drive or on the network before calling the new method of the ExcelReport object.

The new method is then called with the temporary file name as parameter.

Data Exchange
The ExcelReport class allows much more than simple reporting.

For instance, in a quotation tracking application, a new quotation document can be created, and an Excel empty file is created from a list of models, completed with data coming from the tracking system and saved on a shared disk drive, which is recorded in the NotesDocument.

Once the quotation has been completed in Excel, an action taken from the initial Notes document can instanciate an ExcelReport object from the completed file, reads predefined named cells and fills corresponding Notes fields, then attach the Excel file to the Notes document, and if needed, delete it from disk.
Creating in memory fields
If values to be used on a report are neither in a document field, nor in a view column, and cannot be computed through an Excel formula, it is possible to compute it in Notes, and assign its value to a newly created Notes field in the Notes document.
The document is then used to call the fillReportLine or initHeader methods.
As long as the Notes document is not saved, the new field is discarded.
Document cloning
In some cases you have to use several documents of the same kind (same form) to fill different parts of an Excel report.

For instance, in a report, it can be necessary to show an invoicing address and a delivery address in page mode (in the header of the report).
If the 2 addresses come from 2 different document based on the same form, they share field names and it is not possible to define twice the same names on different places in the Excel model.

A workaround is to create new cloned documents with renamed fields.
You can write a Lotusscript function which receives a source document and a string prefix and returns a new document where all the field are renamed using the prefix.
This document can then be used to call the initHeader method.
The second address is cloned with a different prefix and sent to the initReport method.
The cloned documents are not saved, and are discarded when the script ends.

    Top  Top  Previous  ExcelReport | New with V1 Next

©  Thierry Seunevel (2004)