Excel Template

This topic guides developers in creating template definitions for use with Microsoft Excel templates. A Logi template definition acts as a "blueprint", mapping how the Excel template will be filled with data by the Logi Server Engine. Filling an Excel template with data starts with the creation of the Excel template file; once it has been created, four more steps are required:

• Adding the Excel Template to Your Logi Application
• Creating a Template Definition
• Configuring the Data Ranges
• Working with Hierarchical Data

For information about how to call templates from within Logi applications, see Form-based Reporting.

For information about creating template files, see Create an Excel Template.

Adding the Excel Template to Your Logi Application

The term "template" is used frequently in this discussion, and to avoid confusion, let's clarify it:

• For this purpose, an Excel template file is an .xltx, .xltm, or .xlt file (depending on version) created with Microsoft Excel. It looks like the finished worksheet without any data and has cells reserved for the data. This is the target template. Use of other Excel file types are not recommended.
  
• A Logi Info Template definition is an .lgx file, similar to a report or process definition, created with Logi Studio. It's a set of instructions to the Logi Server Engine to retrieve data and map it into the cells in the Excel template file.

The rest of this topic assumes that you've already created your Excel template file. Excel template files are managed within your Logi application as support files.

When you create your Excel template file either save it to your Logi application's _SupportFiles folder, or:

1. In Logi Studio's Application Panel, select and right-click the Support Files folder.
2. In the pop-up menus that appear, select the Add and Existing File... items.
3. Browse to your Excel template (.xltx, .xltm, or .xlt) file and select it. Click OK to add it to the project. The file will be copied to the _SupportFiles folder within your Logi application folder.
   

Creating a Template Definition

The next step is to create a new Logi Template definition that maps the data into the Excel template file data ranges:

1. In the Application Panel, select and right-click the Templates folder.
2. In the popup menus that appear, select the Add and New Definition items.
3. A new definition named "newTemplate" will appear beneath the Templates folder, ready to be renamed, and will open in the Workspace Panel for editing. The actual file will be created in the _Definitions/_Templates folder in your application folder.
   

4. In the Workspace editor, add an Excel Template element to the definition, as shown above. Template definitions cannot utilize more than one Excel Template element.
5. Set the Excel Template File attribute to the full name of the .xltx, .xltm, or .xlt file you added as a Support File. You can choose .xltx and .xlt file names from the attribute value drop-down list but you will need to manually enter a .xltm file name.
6. The Template Output Mode attribute is optional; its default value is OneWorksheet. If this attribute is set to OneWorksheet, all rows returned by the top-most data layers are processed in a single worksheet; each Pattern Block element (discussed below) can specify a different worksheet in the template. If the attribute is set to OneWorksheetPerDataRow, each row from the top-most data layers is treated as a separate worksheet. Developers will find this mode useful for invoice-style reports.

Configuring the Data Ranges

The Pattern Block element gives developers the ability to define data ranges within the Excel template. A pattern block must correspond to one or more rows from the worksheet. Developers should keep the following guidelines in mind when creating template definitions:

• Two distinct pattern blocks cannot have overlapping ranges.
• Disposable rows must follow the corresponding pattern block.
• A sub-pattern block cannot extend beyond the boundaries of its parent block.
• A sub-pattern block's range must be less than its parent block range.

Now let's continue building the template definition:

1. Add a Pattern Block element to the template definition, beneath the Excel Template element, as shown above.
2. Set its First Row and Last Row attributes to values that correspond with row numbers from the Excel template.
   
Hint: A Pattern Block range is equal to the difference of the last row number minus the first row number. Make the First Row and Last Row attributes equal to specify one row.

The Disposable First Row and Disposable Last Row attributes are optional. If dynamic charts and formulas are present in the Excel template, developers will need to set both attributes to values that correspond with row numbers from the worksheet. A disposable range consists of extra rows added to the template in order to test a formula or chart range.

Test rows with dummy data cannot be part of the data range, because the Logi server engine extends the data range downward in certain scenarios. Without a method for declaring rows as "disposable", the Logi report server has no means of removing such rows from the final report. By specifying values for these attributes, developers can remove the unwanted, dummy rows from the final output.

If the Disposable First Row attribute is set but not the Disposable Last Row attribute, the Logi report server will use the Disposable First Row value for both attributes.

The Worksheet attribute is also optional and the default is the first worksheet. Each pattern block range resides on a single worksheet, however, you may specify any number of pattern block ranges using different worksheets.

The Pattern Block Cell Element

We've now specified the rows associated with the pattern block; next we need to specify the columns and map the data.

1. Beneath the Pattern Block element, add a datalayer element and configure its attributes appropriately, as shown above.
2. Beneath the Pattern Block element, add a Pattern Block Cell element.
3. Set its Excel Column attribute to a letter that corresponds with a column from the Excel template file.
4. Set the Pattern Block Row attribute to a row number within the pattern block range. This value is relative to the parent Pattern Block element, not to the worksheet row number.
5. The Adjust Column Width attribute is optional. Set this attribute to True to automatically adjust the column width to fit the data.
6. The Value attribute is optional and either tokens or static values can be used here.

Repeat the previous steps for each data item.

As shown above, left, we'll have three more Pattern Block Cell elements, one each for Company Name, Address, and City-State-Zip data. As shown above, right, the four Pattern Block Cell elements added in the sample definition above correspond to one cell within the block range. In this example, each cell in column C within the pattern block range will be filled with data. This is the technique that's used to map data to specific cells in the Excel template. Repeat as necessary to map all the data into the worksheet.

Working with Hierarchical Data

Logi Info supports hierarchical datasets within Excel templates. Hierarchical data presents parent-child relationships in the data and requires developers to specify sub-data ranges within an existing data range in the template definition.

Due to the dynamic nature of columns created by it, you cannot use a datalayer with a Crosstab Filter to export data through an Excel Template. If you'd like to create a crosstab table within an Excel Template, you may want to create a data sheet in the template file and then render an Excel Pivot table from the raw data. This will allow you to show crosstabbed data within your Excel file, using the Pivot Table from Excel.

The Subpattern Block element is used to specify a repeatable sub-range within a parent Pattern Block or another Subpattern Block element. Developers must add a Subdata Layer element with at least one Subdata Layer Relation Column element to establish the parent-child relationships with the query and sub-query. Here's how: |

1. Beneath the Pattern Block element, add a Subpattern Block element, as shown above.
2. Set its First Row and Last Row attributes to row numbers from the parent pattern block range.
3. Leave its Fill Mode blank, which defaults to Insert. The Logi Server Engine will insert new rows into the Excel template for each row of data returned by the data layer. The number of rows inserted is equal to the total number of rows in the pattern block range.
   
   Subpattern Block attribute values are always relative to the parent block and must reside on the same worksheet.
   
4. Beneath it, add a Subdata Layer element and beneath it a Subdata Layer Relation Column element.
5. Set the Subdata Layer Relation Column's Child Column and Parent Column attributes to a column that exists in both datasets, to establish the relationship.
   
   
   
6. Finally, add additional Pattern Block Cell elements, as shown above, to map the hierarchical child data into the Excel template where it's desired.

The Excel template shown above specifies the range of the pbCustomers Pattern Block as worksheet rows 8 - 17. The range of the spbOrders Subpattern Block is the last row of its parent block - in this case, its eighth row - through its 10th row. Any Pattern Block Cells that are children of the spbOrders Subpattern Block have row numbers relative to the parent sub-block. Since there is only one row in spbOrders, each Pattern Block Row attribute for any child Pattern Block Cell elements must have a value of 1.