Introduction to Excel Templates
An Excel template is a report layout designed in Microsoft Excel for formatting your enterprise reporting data in Excel spreadsheets.
Excel templates provide a set of special features for mapping data to worksheets and for performing additional processing to control how the data is output to Excel workbooks.
This introduction includes the following topics:
Features of Excel Templates
With Excel templates you can format the data in different ways.
-
Define the format for the data in Excel output.
-
Split hierarchical data across multiple sheets and dynamically name the sheets.
-
Create sheets of data that have master-detail relationships.
-
Use native XSL functions in the data to manipulate it prior to rendering.
-
Use native Excel functionality.
Limitations of Excel Templates
The following are limitations of Excel templates.
-
For reports that split the data into multiple sheets, images aren't supported. If the template sheet includes images, when the data is split into multiple sheets, the images are displayed only on the first sheet.
-
Publisher provides an add-in to Microsoft Excel to facilitate the insertion of fields and repeating groups. More complex designs require manual coding. Some features require the use of XSL and XSL Transformation (XSLT) specifications.
-
The Excel template limits the maximum number of active cells to 100,000 in an Excel workbook.
-
The Template Builder for Excel limits the number of Excel template fields per Excel template to 990.
-
When you use an Excel template, you can't use conditional formatting based on the generated data to change the background color of a cell in the report output.
Prerequisites
To design Excel templates, you must meet certain prerequisites.
- You must have Microsoft Excel 2003 or later installed. The template file must be saved as Excel 97-2003 Workbook binary format (*.xls).
If you're using a version of Excel later than Excel 2003 to create your template and save it as Excel 97-2003, ensure that you don’t use any features of the later version that aren't supported in Excel 97-2003. For example, Excel 2003 allows only three conditional formatting rules per cell, but Excel 2007 allows more. If you apply more than three conditional formatting rules to a cell, only three are applied. Excel 2007 also provides color support not provided in Excel 2003.
- To use some of the advanced features, you must have knowledge of XSL.
- The data model must be created in Publisher with sample data available.
Desktop Tools for Excel Templates
Publisher provides a downloadable add-in to Excel that provides these features.
-
Connects directly to the Publisher server to load sample data and upload and download templates
-
Inserts data field mappings to the template
-
Inserts repeating group mappings to the template
-
Provides a field browser to review all inserted code and to edit or delete mappings
-
Previews the template using the sample data or live data when in connected mode
Install the Template Builder for Excel
The Template Builder for Excel is installed automatically when you install the Oracle BI Publisher Desktop tools.
The tools can be downloaded from the Home page of Oracle Analytics Publisher as follows:
Under the Get Started region, select the Oracle BI Publisher Desktop option (32bit Office or 64bit Office) appropriate for your version of Microsoft Office.
The Excel Template Builder isn’t compatible with the (deprecated) Analyzer for Excel. If you have the Analyzer for Excel installed from a previous version, the Publisher Tools installer detects its presence and halts the installation. You must remove the Analyzer for Excel before installing the Oracle BI Publisher Desktop. The Excel Template Builder includes a feature to import Analyzer for Excel templates to the Excel template format.