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 sheet.

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 are using a version later than Excel 2003 to create your template and then save as Excel 97-2003, ensure that you don’t use any features of the later version that are not 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.

Supported Output

Excel templates generate Excel binary (.xls) output only.

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.

Sample Excel Templates

The Template Builder installation includes sample Excel templates.

To access the samples from a Windows desktop:

  • Click Start, Programs, Oracle BI Publisher Desktop, Samples, then Excel.

    This action launches the folder that contains the Excel sample templates.