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 and repeating elements. A defined name in Excel is a name that represents a cell, range of cells, formula, or constant value.

Tip: To learn more about defined names and their usage in Microsoft Excel 2007, see the Microsoft help topic: "Define and use names in formulas."

The defined names used in your Excel template must use the syntax described in this chapter, as well as follow the Microsoft guidelines described in the Microsoft Excel help document. Note that BI Publisher defined names are within the scope of the template sheet.

About the XDO_ Defined Names

The BI Publisher defined names are Excel defined names identified by the prefix "XDO_". Marking up the placeholders in the template files creates the connection between the position of the placeholders 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.

Using Native Excel Functions

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, BI Publisher will automatically adjust the region ranges for those named regions so that the formulas calculate correctly.

However, if you create nested groups in your template, 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". Use this sheet to identify your template to BI Publisher as an Excel template. This sheet is also used to specify calculations and processing instructions to perform on fields or groups in the template. BI Publisher provides a set of functions to provide specific report features. Other formatting and calculations can be expressed in XSLT.

It is recommended that you hide the XDO_METADATA sheet before you upload your completed template to the BI Publisher catalog. This will prevent report consumers from seeing it in the final report output.

Note: For more information see Format of the XDO_METADATA Sheet and Defining BI Publisher Functions.

Building a Simple Template

This section will demonstrate the concepts of Excel templates by walking through the steps to create a simple Excel template and testing it with the Excel Template Builder. This procedure will follow these steps:

1. Obtain sample XML data from your data model.

2. Open the BlankExcelTemplate.xls file and save as your template name.

3. Design the layout in Excel.

4. Assign the BI Publisher defined names.

5. Prepare the XDO_METADATA sheet.

6. Test the template using the desktop Excel Template Builder.

Step 1: Obtain sample XML data from your data model

You need sample data in order to know your field names and the hierarchical relationships to properly mark up the template. For information on saving sample data from your report data model, see the topic "Testing Data Models and Generating Sample Data" in the Oracle Fusion Middleware Data Modeling Guide for Oracle Business Intelligence Publisher.

If you do not have access to the report data model, but you can access the report, you can alternatively save sample data from the report viewer. To save data from the report viewer:

1. In the BI Publisher catalog, navigate to the report.

2. Click Open to run the report in the report viewer.

3. Click the Actions menu, then click Export, then click Data. You will be prompted to save the XML file.

4. Save the file to a local directory.

The sample data for this example is a list of employees by department. Note that employees are grouped and listed under the department.

<?xml version="1.0" encoding="UTF-8"?>
<! - Generated by Oracle BI Publisher 11.1.1.4.0 - >
<DATA>
 <DEPT>
   <DEPARTMENT_ID>20</DEPARTMENT_ID>
   <DEPARTMENT_NAME>Marketing</DEPARTMENT_NAME>
     <EMPS>
       <EMPLOYEE_ID>201</EMPLOYEE_ID>
       <EMP_NAME>Michael Hartstein</EMP_NAME>
       <EMAIL>MHARTSTE</EMAIL>
       <PHONE_NUMBER>515.123.5555</PHONE_NUMBER>
       <HIRE_DATE>1996-02-17T00:00:00.000+00:00</HIRE_DATE>
       <SALARY>13000</SALARY>
     </EMPS>
     <EMPS>
       <EMPLOYEE_ID>202</EMPLOYEE_ID>
       <EMP_NAME>Pat Fay</EMP_NAME>
       <EMAIL>PFAY</EMAIL>
       <PHONE_NUMBER>603.123.6666</PHONE_NUMBER>
       <HIRE_DATE>1997-08-17T00:00:00.000+00:00</HIRE_DATE>
       <SALARY>6000</SALARY>
     </EMPS>
  </DEPT>
<DEPT>
...
...
</DEPT>
</DATA>

Step 2: Open the BlankExcelTemplate.xls file and save as your template name

Note: It is recommended that you install the Template Builder for Excel. For information on downloading the tool, see Desktop Tools.

The Template Builder installation includes a set of sample Excel templates, including a sample blank Excel template called BlankExcelTemplate.xls. This template file contains a blank Sheet1 and the XDO_METADATA sheet. It is recommended that you either start with this provided template or copy the XDO_METADATA sheet into your own Excel workbook.

Tip: If you are building a new template from an existing template, be sure to clear any existing defined names in the template sheet.

To open the BlankExcelTemplate.xls:

1. From a Windows desktop, click Start, then Programs, then Oracle BI Publisher Desktop, then Samples, then Excel.

2. In the Excel Templates sample folder, double-click BlankExcelTemplate.xls to open it.

3. Save the file as your selected name in the Microsoft Excel 97-2003 Workbook format (*.xls).

Step 3: Design the layout in Excel

In Excel, determine how you want to render the data and create a sample design, as shown in the following figure:

The design shows a department name and a row for each employee within the department. You can apply Excel formatting to the design, such as font style, shading, and alignment. Note that this layout includes a total field. The value for this field is not available in the data and will require a calculation.

Step 4: Assign the BI Publisher defined names

To code this design as a template, mark up the cells with the XDO_ defined names to map them to data elements. The cells must be named according to the following format:

• Data elements: XDO_?element_name?

  where

  XDO_ is the required prefix and

  ?element_name? is either:

  • the XML tag name from your data delimited by "?

    "

  • a unique name that you will use to map a derived value to the cell

  For example: XDO_?EMPLOYEE_ID?

• Data groups: XDO_GROUP_?group_name?

  where

  XDO_GROUP_ is the required prefix and

  • ?group_name? is the XML tag name for the parent element in your XML data delimited by "?

    ".

  • a unique name that you will use to define a derived grouping logic

  For example: XDO_GROUP_?DEPT?

  Note that the question mark delimiter, the group_name, and the element_name are case sensitive.

Applying a Defined Name to a Cell

1. Click the cell in the Excel worksheet.

2. Click the Name box at the left end of the formula bar. The default name will display in the Name box. By default, all cells are named according to position, for example: A8.

3. In the Name box, enter the name using the XDO_ prefix and the tag name from your data. For example: XDO_?EMP_NAME?

4. Press Enter.

   The following figure shows the defined name for the Employee Name field entered in the Name box:

   

5. Repeat for each of the following data fields: DEPARTMENT_NAME, EMPLOYEE_ID, EMAIL, PHONE_NUMBER, and SALARY.

   Tip: If you navigate out of the Name box without pressing Enter, the name you entered will not be maintained.

   You cannot edit the Name box while you are editing the cell contents.

   The name cannot be more than 255 characters in length.

6. For the total salary field, a calculation will be mapped to that cell. For now, name that cell XDO_?TOTAL_SALARY?. The calculation will be added later.

After you have entered all the fields, you can review the names and make any corrections or edits using the Name Manager feature of Excel. Access the Name Manager from the Formulas tab in Excel as shown:

After you have named all the cells for this example, the Name Manager dialog will appear as shown:

You can review all your entries and update any errors through this dialog.

Understanding Groups

A group is a set of data that repeats for each occurrence of a particular element. In the sample template design, there are two groups:

• For each occurrence of the <EMPS> element, , the employee’s data (name, e-mail, telephone, salary) will display in the worksheet.

• For each occurrence of the <DEPT> element, the department name and the list of employees belonging to that department will display.

In other words, the employees are "grouped" by department and each employee’s data is "grouped" by the employee element. To achieve this in the final report, add grouping tags around the cells that are to repeat for each grouping element.

Note that your data must be structured according to the groups you want to create in your template. The structure of the data for this example

<DATA> 
   <DEPT>
      <EMPS>

establishes the grouping desired for the report.

To Create Groups in the Template

1. Highlight the cells that make up the group. In this example the cells are A8 - E8.

2. Click the Name box at the left end of the formula bar and enter the name using the XDO_GROUP_ prefix and the tag name for the group from your data. For example: XDO_GROUP_?EMPS?

3. Press Enter.

The following figure shows the XDO_GROUP_ defined named entered for the Employees group. Note that just the row of employee data is highlighted. Do not highlight the headers. Note also that the total cell XDO_?TOTAL_SALARY? is not highlighted.

To define the department group, include the department name cell and all the employee fields beneath it (A5-E9) as shown in the following figure:

Enter the name for this group as: XDO_GROUP_?DEPT? to match the group in the data. Note that the XDO_?

TOTAL_SALARY? cell is included in the department group to ensure it repeats at the department level.

Step 5: Prepare the XDO_METADATA Sheet

BI Publisher requires the presence of a sheet called "XDO_METADATA" to process the template. This sheet must follow the specifications defined here.

Format of the XDO_METADATA Sheet

The XDO_METADATA sheet must have the format shown in the following 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 Data Constraints section does not require any content, but also must be present as shown.

This procedure describes how to set up the sheet for this sample Excel template to run. For the detailed description of the functionality provided by the XDO_METADATA sheet see Defining BI Publisher Functions.

Creating the XDO_METADATA Sheet

If you copied the XDO_METADATA sheet in Step 2, skip this section and proceed to Adding the Calculation for the XDO_?TOTAL_SALARY?

Field; otherwise, set up the hidden sheet as follows:

1. Create a new sheet in your Excel Workbook and name it "XDO_METADATA".

2. Create the header section by entering the following variable names in column A, one per row, starting with row 1:

   • Version

   • ARU-dbdrv

   • Extractor Version

   • Template Code

   • Template Type

   • Preprocess XSLT File

   • Last Modified Date

   • Last Modified By

3. Skip a row and enter "Data Constraints" in column A of row 10.

4. In the header region, for the variable "Template Type" enter the value: TYPE_EXCEL_TEMPLATE

Adding the Calculation for the XDO_?TOTAL_SALARY? Field

Earlier in this procedure you assigned the defined named XDO_?TOTAL_SALARY?

to the cell that is to display the total salaries listed in the SALARY column. In this step, you will add the calculation to the Data Constraints section of the XDO_METADATA sheet and map the calculation to the XDO_?TOTAL_SALARY? field.

1. In the Data Constraints section, in Column A, enter the defined name of the cell: XDO_?TOTAL_SALARY?

2. In Column B enter the calculation as an XPATH function. To calculate the sum of the SALARY element for all employees in the group, enter the following: <?

   sum(.//SALARY)?>

The completed XDO_METADATA sheet is shown in the following figure:

Step 6: Test the template

If you have installed the Template Builder for Excel, the BI Publisher tab will appear on the ribbon menu as shown in the following figure:

To preview your report using sample data:

1. Click Sample XML. You will be prompted to select the sample data file.

2. Click Preview.

   The sample data will be applied to your template and the output document will be opened in a new workbook. The following figure shows the preview of the template with the sample data:

   

Formatting Dates

Excel cannot recognize canonical date format. If the date format in your XML data is in canonical format, that is, YYYY-MM-DDThh:mm:ss+HH:MM, you must apply a function to display it properly.

One option to display your date is to use the Excel REPLACE and SUBSTITUTE functions. This option will retain the full date and timestamp. If you only require the date portion in your data (YYY-MM-DD), another option is to use the DATEVALUE function. The following example shows how to use both options.

Example: Formatting a Canonical Date in Excel

Using the Employee by Department template and data from the first example, assume you want to add the HIRE_DATE element to the layout to and display the date as shown in Column E of the following figure:

To format the date as shown above, follow these steps:

1. Copy and paste a sample value for HIRE_DATE from the XML data into the cell that is to display the HIRE_DATE field. For example:

   Copy and paste

   1996-02-03T00:00:00.000-07:00

   into the E8 cell.

2. Assign the cell the defined name XDO_?

   HIRE_DATE? to map it to the HIRE_DATE element in the data, as shown in the following figure:

   

   If you do nothing else, the HIRE_DATE value will display as shown. To format the date as "3-Feb-96", you must apply a function to that field and display the results in a new field.

3. Insert a new Hire Date column. This will now be column F as shown in the following figure:

   

4. In the new Hire Date cell (F8), enter one of the following Excel functions:

   • To retain the full date and timestamp, enter:

     =--REPLACE(SUBSTITUTE(E8,"T"," "),LEN(E8)-6,6,"")

   • To retain only the date portion (YYY-MM-DD), enter:

     DATEVALUE(LEFT(E8,10))

   After you enter the function, it will populate the F8 cell as shown in the following figure:

   

5. Apply formatting to the cell.

   Right-click the F8 cell. From the menu, select Format Cells. In the Format Cells dialog, select Date and the desired format, as shown in the following figure:

   

   The sample data in the F8 cell now displays as 3-Feb-96.

6. Finally, hide the E column, so that report consumers will not see the canonical date that is converted.

   

Defining BI Publisher Functions

BI Publisher provides a set of functions to achieve additional reporting functionality. You define these functions in the Data Constraints region of the XDO_METADATA sheet.

The functions make use of Columns A, B, and C in the XDO_METADATA sheet as follows:

Use Column A to declare the function or to specify the defined name of the object to which to map the results of a calculation or XSL evaluation.

Use Column B to enter the special XDO-XSL syntax to describe how to control the data constraints for the XDO function, or the XSL syntax that describes the special constraint to apply to the XDO_ named elements.

Use Column C to specify additional instructions for a few functions.

The functions are described in the following three sections:

• Reporting Functions

• Formatting Functions That Rely on Specific Data Attribute Values

• Grouping Functions

Reporting Functions

The following functions can be added to your template using the commands shown and a combination of BI Publisher syntax and XSL. A summary list of the commands is shown in the following table. See the corresponding section for details on usage.

 

Function	Commands
Split the report data into multiple sheets	XDO_SHEET_? with XDO_SHEET_NAME_?
Define a parameter	XDO_PARAM_?n?
Define a link	XDO_LINK_?link object name?
Import a subtemplate	XDO_SUBTEMPLATE_?n?
Reference Java extension libraries	XDO_EXT_? n?

Splitting the Report into Multiple Sheets

Note: Images are not supported across multiple sheets. If the template sheet includes images, when the data is split into multiple sheets, the images will show only on the first sheet.

Use the this set of commands to define the logic to split the report data into multiple sheets:

• Use XDO_SHEET_?

  to define the logic by which to split the data onto a new sheet.

• Use XDO_SHEET_NAME_? to specify the naming convention for each sheet.

 

Column A Entry	Column B Entry	Column C Entry
XDO_SHEET_?	<?xsl_evaluation to split the data? > Example: <? .//DEPT?>	n/a
XDO_SHEET_NAME_?	<?xsl_expression to name the sheet? > Example: <? concat(.//DEPARTMENT_NAME,'-',count(.//EMP_NAME))?>	(Optional) <? original sheet name?> Example: <?Sheet3?>

XDO_SHEET_? must refer to an existing high-level node in your XML data. The example <?.//DEPT?> will create a new sheet for each occurrence of <DEPT> in the data.

If your data is flat you cannot use this command unless you first preprocess the data to create the desired hierarchy. To preprocess the data, define the transformation in an XSLT file, then specify this file in the Preprocess XSLT File field of the header section of the XDO _METADATA sheet. For more information, see Preprocessing the Data Using an XSL Transformation (XSLT) File.

Use XDO_SHEET_NAME_? to define the name to apply to the sheets. In Column B enter the XSL expression to derive the new sheet name. The expression can reference a value for an element or attribute in the XML data, or you can use the string operation on those elements to define your final sheet name. This example:

<?concat(.//DEPARTMENT_NAME,'-',count(.//EMP_NAME))?>

will name each sheet using the value of DEPARTMENT_NAME concatenated with "-" and the count of employees in the DEPT group.

The original sheet name entry in Column C tells BI Publisher on which sheet to begin the specified sheet naming. If this parameter is not entered, BI Publisher will apply the naming to the first sheet in the workbook that contains XDO_ names. You would need to enter this parameter if, for example, you have a report that contains summary data in the first two worksheets and the burst data should begin on Sheet3. In this case, you would enter <?SHEET3?

> in Column C.

Example: Splitting the data into multiple sheets

Using the employee data shown in the previous example. This example will:

• Create a new worksheet for each department

• Name each worksheet the name of the department with the number of employees in the department, for example: Sales-21.

1. Enter the defined names for each cell of employee data and create the group for the repeating employee data:

   

   Note: Do not create the grouping around the department because the data will be split by department.

2. Enter the following in the Data Constraints section of the XDO_METADATA sheet:

   Column A Entry	Column B Entry
   XDO_SHEET_?	<?.//DEPT? >
   XDO_SHEET_NAME_?	<? concat(.//DEPARTMENT_NAME,'-',count(.//EMP_NAME))? >

The entries are shown in the following figure:

The following figure shows the generated report. Each department data now displays on its own sheet, which shows the naming convention specified:

 

 

注：假设仅仅是单纯显示数据。不须要XDO_MATEDATA页，仅仅须要做好命名管理就可以