 Thierry
Seunevel |
Tech Corner |
Home > Tech Corner > Excel Report
ExcelReport : Using Excel to print from Lotus Notes (3)
|
|
| | Substitution modes |
Two substitution modes are possible, and can be used in the same report.
-
A page mode, where field values from a Notes document are used to fill specific cells on the report.
In this mode, only one Notes document of a given form is used to fill the Excel file.
If several Notes documents are used, they have to be of different forms. If field names from several documents are the same, the value will come from the first document where the field is not empty.
- A line mode, where field values from each Notes document are used to insert a new line in the Excel report.
This two modes can be used in the same report.
Data shared by the whole report can be used to fill the header part of each page in page mode, while data coming from different documents is used to insert lines in the detail part of the report.
In page mode, the Excel named cells are used to fill the data.
In line mode, the names are used in an initialization process and converted into the relative column position in the line range. It is this relative column position which is used in the substitution process.
|
| Excel model |
The output relies on the content of the Excel file used as model.
Multiple Excel Sheets.
An Excel file can include several sheets.
The preView and printOut methods have to be used with a specific sheet which can be selected explicitly using a property (sheet property) or defaults to the first sheet in the workbook.
The named range can be located on any sheet of the workbook. This gives the possibility to use a sheet to manage data exchange, and to display or print an other sheet where data can be derived from the first one.
Defining the current report line.
The range to be used to collect data from each detail document in line mode must have to be defined using the LineField settable property, or the default REPORTLINE is used.
In case where you only use the page mode, give the value *NONE to the LineField property, otherwise you will receive an execution error (Object not set or the like).
The range defining the report line typically contains other named columns which will collect the data coming from the Notes Document.
The Excel names can be the same than the Notes field, or, if the document is red from a Notes View, the column number.
Columns can be specified from the visible columns, or all the defined columns. In the first case, use vCol.x to name the cell, in the second one, use aCol.x. x goes from 1 to the number of (visible) columns.
The field names are analyzed in the initialization process, and then it is the relative position of the different cells which is used to merge data in the Excel file.
The model must include 3 detail lines. The first and third lines are used to define specific formatting for the first and last lines of the report, the second one is the model of the current line, and is the one that has to be defined in the ReportLine property.
The report line range can include columns out of the print range. This cells can receive a name and can collect data from Notes document, or an Excel formula. The values in this cells can be used to compute totals, averages or anything else. Cells outside the ReportLine range are not inserted in the final report.
|
|
|
|
| © Thierry Seunevel (2004) |
www.seusoft.com |
|
|
|
|
