XML Publiser For Excel Template

1、XML Publisher定义数据

2、XML Publisher定义模板

模板类型选择Microsoft Excel，默认输出类型选择Excel，上传.xls模板

3、定义并发程序

4、定义请求

请求输出格式选择XML

5、制作Excel模板(97-2003格式保存)

http://docs.oracle.com/cd/E21764_01/bi.1111/e13881/T527073T571887.htm

Creating Excel Templates

This chapter covers the following topics:

• Introduction
• Concepts
• Building a Simple Template
• Formatting Dates
• Defining BI Publisher Functions
• Preprocessing the Data Using an XSL Transformation (XSLT) File
• Using the Template Viewer to Debug a Template

Introduction

An Excel template is a report layout that you design in Microsoft Excel for retrieving and formatting your enterprise reporting data in Excel. Excel templates provide a set of special features for mapping data to worksheets and for performing additional processing to control how your data is output to Excel workbooks.

Features of Excel Templates

With Excel templates you can:

• Define the structure for your 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 your 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 are not supported. If the template sheet includes images, when the data is split into multiple sheets, the images will show only on the first sheet.

• There is no tool to facilitate the markup of the template with BI Publisher tags; all tags must be manually coded. Some features require the use of XSL and XSL Transformation (XSLT) specifications

Prerequisites

Following are prerequisites for designing Excel templates:

• Microsoft Excel 2003 or later. The template file must be saved as Excel 97-2003 Workbook binary format (*.xls).

• To use some of the advanced features, the report designer will need knowledge of XSL and XSLT.

• The report data model has been created.

Supported Output

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

Desktop Tools

BI Publisher provides a downloadable add-in to Excel that enables you to preview your template with sample data. This facilitates design by enabling you to test and edit your template without having to upload it to the BI Publisher catalog first.

The Template Builder for Excel is installed automatically when you install the Template Builder for Word. The tools can be downloaded from the Home page of Oracle Business Intelligence Publisher or Oracle Business Intelligence Enterprise Edition, as follows:

Under the Get Started region, click Download BI Publisher Tools.

Sample Excel Templates

The Template Builder includes sample Excel templates.

To access the samples from a Windows desktop:

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

This will launch the folder that contains the Excel sample templates.

Concepts

Similar to RTF template design, Excel template design follows the paradigm of mapping fields from your XML data to positions in the Excel worksheet. Excel templates make use of features of Excel in conjunction with special BI Publisher syntax to achieve this mapping. In addition to direct mapping of data elements, Excel templates also utilize a special sheet (the XDO_METADATA sheet) to specify and map more complex formatting instructions.

Identifying Data Field Placeholders and Groups

Excel templates use named cells and groups of cells to enable BI Publisher to insert data elements. Cells are named using BI 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 that are defined on the XDO_METADATA sheet.

Your template content and layout must correspond to the content and hierarchy of the XML data file used as input to your report. Each group of repeating elements in your template must correspond to a parent-child relationship in the XML file. If your data is not structured to match the desired layout in Excel it is possible to regroup the data using XSLT preprocessing or the grouping functions. However, for the best performance and least complexity it is recommended that the data model be designed with the report layout in mind.

Note: See Preprocessing the Data Using an XSL Transformation (XSLT) File and Grouping Functions for more information about these options.

Use of Excel Defined Names

The Excel defined names feature is used to identify data fields an