Understand Excel Template
Similar to RTF template design, Excel template design follows the paradigm of mapping fields from the XML data to positions in the Excel worksheet.
Excel templates make use of features of Excel in conjunction with special Publisher syntax to achieve this mapping. In addition to direct mapping of data elements, Excel templates support more complex formatting instructions by defining the cell ranges and the commands in a separate worksheet designated to contain these commands. This sheet is called the XDO_METADATA sheet.
Map Data Fields and Groups
Excel templates use named cells and groups of cells to enable Publisher to insert data elements.
Cells are named using Publisher syntax to establish the mapping back to the XML data. The cell names are also used to establish a mapping within the template between the named cell and calculations and formatting instructions defined on the XDO_METADATA sheet.
The template content and layout must correspond to the content and hierarchy of the XML data file used as input to the report. Each group of repeating elements in the template must correspond to a parent-child relationship in the XML file. If the data isn't structured to match the desired layout in Excel, you can regroup the data using XSLT preprocessing or the grouping functions. However, for the best performance and least complexity it's recommended that the data model be designed with the report layout in mind.
Use Excel Defined Names for Mapping
Publisher uses the Excel defined names feature to identify data fields and repeating elements.
A defined name in Excel is a name that represents a cell, range of cells, formula, or constant value.
The Template Builder for Excel automatically creates the defined names when you use it to insert fields and repeating groups. You can also insert the defined names manually. The defined names used in the Excel template must use the syntax described in this chapter and follow the Microsoft guidelines described in the Microsoft Excel help document. Note that Publisher defined names are within the scope of the template sheet.
When you create an Excel Template manually (that is, NOT using the Publisher Desktop Excel Template Builder), you must provide default values for all marked up cells XDO_?. The default values must match to the data type of the report data XML file. Without default values for the XDO_? cells, the output cells generated from those template cells may lose formatting and the result is unpredictable. If you use Publisher Desktop to create an Excel Template, the default values are automatically supplied with the first row of sample data in the report data file.
Use "XDO_" Prefix to Create Defined Names
The Publisher defined names are Excel defined names identified by the prefix "XDO_".
Creating the defined name with the Publisher code in the template creates the connection between the position of the code in the template and the XML data elements, and also maintains the ability to dynamically grow data ranges in the output reports, so that these data ranges can be referenced by other formula calculations, charts, and macros.
Use Native Excel Functions with the "XDO_" Defined Names
You can use the XDO_ defined names in Excel native formulas as long as the defined names are used in a simple table.
When a report is generated, Publisher automatically adjusts the region ranges for those named regions so that the formulas calculate correctly.
However, if you create nested groups in the template, then the cells generated in the final report within the grouping can no longer be properly associated to the correct name. In this case, the use of XDO_ defined names with native Excel functions cannot be supported.
About the XDO_METADATA Sheet
Each Excel template requires a sheet within the template workbook called "XDO_METADATA".
Publisher uses this sheet in the template in the following ways:
-
To identify the template as an Excel template.
-
To insert the code for the field and group mappings you create with the Template Builder.
As the template designer, you also use this sheet to specify more advanced calculations and processing instructions to perform on fields or groups in the template. Publisher provides a set of functions to provide specific report features. Other formatting and calculations can be expressed in XSLT.
Create the XDO_METADATA Sheet
When you begin the design of a new Excel template using the Template Builder, the first time you use one of the Insert functions the Template Builder automatically creates a hidden XDO_METADATA sheet. A message informs you that the sheet has been created.
Publisher creates the sheet as a hidden sheet. Use the Excel Unhide command to view and edit the XDO_METADATA sheet.
Format of the XDO_METADATA Sheet
The XDO_METADATA sheet is created with the format shown in this figure. The format consists of two sections: the header section and the data constraints section. Both sections are required.
In the header section, all the entries in column A must be listed, but a value is required for only one: Template Type, as shown. The entries in Column A are:
-
Version
-
ARU-dbdrv
-
Extractor Version
-
Template Code
-
Template Type
-
Preprocess XSLT File
-
Last Modified Date
-
Last Modified By
The Data Constraints section is used to specify the data field mappings and other processing instructions. Details are provided in the following sections.
Enable Excel Template Scalability
Enable Excel template scalability to process large data to output reports in Excel format.
If you try to publish reports with large amounts of data as Excel spreadsheets, you might encounter memory issues because the limit of an Excel sheet is 65536 rows. If you enable an Excel template to scale, that template divides a large amount of data into multiple sheets, which helps to avoid memory issues. You can enable Excel template scalability at the system level, the report level, or the Excel template level. The template level setting overrides the report level setting, and the report level setting overrides the system level setting. To ensure backward compatibility, Excel template scalability is set to false by default.
An Excel template that’s enabled to scale does the following to avoid memory issues:
- Flows data into multiple sheets when the data size is more than 65536 rows in a table.
- Flushes memory of every N rows after those N rows are processed for rendering the Excel report.
By default, the flush cell size = 3000 *100 (rows * columns). N = 3000 * 100 /Actual_columns_in_your_ Excel_template_sheet
You can override this flush cell size by specifying the flush cell size (XDO_FLUSH_CELLSIZE_? flush_cell_size) below the "Data Constraints:" line in the XDO_METADATA sheet in the Excel template. An XDO_GROUP table might not work properly if the final report size of an XDO_GROUP table is greater than the flush cell size.
- Releases the memory used for each sheet after processing the data in the sheet.
Enable Excel Template Scalability at the Template Level
You can enable scalability in an Excel template to avoid running out of memory while publishing large amounts of data to an Excel spreadsheet.
- Open the Excel template.
- Select the XDO_METADATA sheet in the Excel template.
- Below the "Data Constraints:" line, enter
XDO_SCALABLE_?in Column A, and typetruein column B.
Enable Excel Template Scalability at the System Level
As an administrator, you can set a runtime property to enable scalability for all Excel templates.
- As an administrator, navigate to the Runtime Configuration page.
- Scroll down to view the Excel template properties.
- Set the Enable Scalable Mode property to true.
Enable Excel Template Scalability at the Report Level
As a report author, you can set a report level property to enable scalability for the Excel template used by the report.
- Open the report for edit.
- Click Properties to open the Report Properties dialog.
- Click the Formatting tab and scroll down to view the Excel template properties.
- Set the Enable Scalable Mode property to true.