Guidelines for Designing RTF Templates for Microsoft Excel 2007 Output

This section describes report features specific to designing RTF templates for Excel 2007 output (.xlsx).

It includes the following topics:

Creating Multiple Sheets

By default, page breaks and section breaks specified in the RTF template create a new sheet in the output Excel workbook. You can control whether page breaks create a new sheet using the property xlsx-page-break-as-new-sheet. Set this property to false when you do not want page breaks in the RTF template to generate new sheets in the Excel workbook. A similar control does not exist for section breaks; that is, a section break in the template will always create a new sheet in the Excel workbook output.

For information on setting properties in an RTF template, see Setting Properties.

Specifying a Sheet Name

You can specify which sheet to use in an input data phrase.

To specify a sheet name, use the following command in the template:

<?spreadsheet-sheet-name: xpath-expression?>

where xpath-expression is an XPath expression or a string constant.

For example, assume your template uses input data as follows:

 <?xml version="1.0" encoding="UTF-8" ?> 
   <ROWSET>
    <ROW>
      <CUSTOMER_NAME>Vgpsuwo Fjprpit</CUSTOMER_NAME> 
      <CUSTOMER_NUMBER>7795</CUSTOMER_NUMBER> 
      <YEAR>2005</YEAR> 
      <BRAND>MSPKID</BRAND> 
      <DIVISION>UWGLERXM</DIVISION> 
      <STATE>LD</STATE> 
      <INVOICE_DATE>2004-12-07T07:13:15.379-08:00</INVOICE_DATE> 
      <INVOICE_NO>806356</INVOICE_NO> 
      <INVOICE_AMOUNT>8181.704554734346</INVOICE_AMOUNT> 
    </ROW>
...
</ROWSET>

To generate a sheet name that shows the YEAR and STATE from the data (for example, "2005 ID") enter the following in a BI Publisher field in your template:

<?spreadsheet-sheet-name: {concat(.//YEAR, ' ',.//STATE)}?>

Ensure that your expression generates unique sheet names within the workbook.

Specifying Number and Date Formatting

For Excel 2007 output format, BI Publisher does not apply any formatting for number and date.

BI Publisher saves the formatting mask and the actual value (date or number) into the XLSX output file. The formatting is handled by Microsoft Excel. For example:

If the Microsoft Windows Region and Language of the client computer is set to English (United States), then the numbers and dates are formatted in en-US locale in the Excel 2007 output file.
If the Microsoft Windows Region and Language of the client computer is set to French (France), then the numbers and dates in the same Excel 2007 output file are formatted in fr-FR locale.

Note also that Microsoft Excel 2007 output does not support some Oracle format masks. See Using the Oracle Format Mask for more information.