9 Configuring the Appearance of an Integrated Excel Workbook

This chapter describes how you configure the appearance of an integrated Excel workbook using styles that the Oracle ADF Desktop Integration module defined and that you define in Excel. The chapter also discusses how you can use EL expressions to dynamically apply styles to Oracle ADF components in a workbook at runtime.

This chapter includes the following sections:

9.1 Introduction to Configuring the Appearance of an Integrated Excel Workbook

You can configure the appearance of an integrated Excel workbook using both Excel functionality and Oracle ADF functionality. Configuring the appearance of a workbook may make the workbook more usable for end users. For example, applying a particular style to cells that render ADF Output Text components at runtime may indicate to end users that the cell is read-only. You may also want to configure the appearance of an integrated Excel workbook so that it aligns with your company's style sheet or the color scheme of the Fusion web application that the Excel workbook integrates with.

The Oracle ADF Desktop Integration module provides a number of predefined Excel styles to apply to the Oracle ADF Desktop Integration components you configure in a workbook. You may want to define additional styles to meet the needs of your desktop integration project. If you do, familiarize yourself with the formats in an Excel workbook that render differently depending on the locale, region, and language.

Once you have decided what styles to apply to the Oracle ADF Desktop Integration components at runtime, you write EL expressions to associate a style with a component. The Oracle ADF Desktop Integration component properties that include StyleName in their name take an EL expression as a value. The ADF Label component and the Label property of other ADF components also support EL expressions. These EL expressions can retrieve the values of string keys defined in resource bundles or the values of attribute control hints defined in your Fusion web application.

Finally, in addition to styles that allow you to configure the appearance of an integrated Excel workbook, the Oracle ADF Desktop Integration module provides a collection of properties (BrandingItems) that enable you to brand your integrated Excel workbook with application name, application version details, and copyright information.

9.2 Working with Styles

The Oracle ADF Desktop Integration module provides a mechanism to apply Excel-defined styles to some of the Oracle ADF components at runtime. The Oracle ADF components that support the application of styles have properties with StyleName in their name. For example, the column properties of the ADF Table and ADF Read-only Table components both support the properties HeaderStyleName and CellStyleName that determine styles to apply at runtime.

Predefined Styles in the Oracle ADF Desktop Integration Module

Many properties have default values that are drawn from a predefined list of Oracle ADF Desktop Integration module styles. For example, the HeaderStyleName property's default value is _ADFDI_HeaderStyle, one of the predefined styles in the Oracle ADF Desktop Integration module. The Oracle ADF Desktop Integration module automatically adds these predefined styles to a converted Excel workbook at design time.

The following is the list of predefined styles:

  • _ADFDI_FormBottomStyle

  • _ADFDI_FormDoubleClickCellStyle

  • _ADFDI_FormTopStyle

  • _ADFDI_HeaderStyle

  • _ADFDI_InputTextStyle

  • _ADFDI_LabelStyle

  • _ADFDI_OutputTextStyle

  • _ADFDI_ReadOnlyTableStyle

  • _ADFDI_TableCellROStyle

  • _ADFDI_TableCellStyle

  • _ADFDI_TableChangedColumnStyle

  • _ADFDI_TableDoubleClickCellStyle

  • _ADFDI_TableFlagColumnStyle

  • _ADFDI_TriangleHeaderStyle

You can merge these styles and other styles that you define yourself from an integrated Excel workbook into another Excel workbook that you intend to integrate with a Fusion web application. You may need to create additional styles for use in your Excel workbook. For example, to add a date-specific formatting, you can duplicate _ADFDI_TableCellStyle, call it MyTableCellDateStyle, and add your date-specific formatting.

Note:

In a cell formatted with a text style, if you wish to enter a numerical or date value and want Excel to retain the format of data while uploading, add an apostrophe symbol (') before entering the value. The apostrophe symbol acts as an escape character and is not displayed with the value.

For more information about creating new styles and merging styles into a workbook, see Excel's documentation.

Excel's Date Formats and Microsoft Windows' Regional and Language Options

A number of formats in the Date category of Number styles that Excel can apply to cells change if a user changes the locale of the local machine using the Regional and Language Options dialog box that is accessible from the Microsoft Windows Control Panel. The * character precedes these formats in the Type list. Figure 9-1 shows an example of a Date type that formats dates in a cell using French (France) conventions.

Figure 9-1 Date Formats in Excel

Date Formats in Excel

If an end user changes the regional options of a machine to use English (United States), as illustrated in Figure 9-2, the cells that are formatted with the style in Figure 9-1 use the English (United States) conventions.

Figure 9-2 Regional and Language Options in Excel

Regional and Language Options in Excel

9.2.1 How to Apply a Style to an Oracle ADF Component

To apply a style to an Oracle ADF component, use the property inspector to set values for properties with StyleName in their name.

To apply a style:

  1. In the integrated Excel workbook, select the cell that references the Oracle ADF component you want to modify and then click the property inspector button in the Oracle ADF Desktop Integration Designer toolbar.

    For example, select a cell that references an ADF Table component.

  2. Click Columns and then the value for the column in the array of columns where you want to modify the format of cells at runtime.

  3. Select the CellStyleName property and click the ellipsis button (...) to display the Edit Expression dialog box.

  4. Expand the Styles node and select the style that you want to apply to cells in the column at runtime.

    For example, apply a currency-based style (Currency[0] style) to the Cost Price databound column of Currency type. Applying the Currency[0] style rather than a general style to a databound column of Currency type results in runtime data (price values) appearing as values rounded off to zero decimal places rather than a regular value (with two decimal places).

  5. Click Insert Into Expression to insert Currency[0] into the Expression field.

    Figure 9-3 shows the Edit Expression dialog box.

    Figure 9-3 Edit Expression Dialog Box Applying a Style

    The Edit Expression dialog box applies a style.

  6. Click OK.

9.2.2 What Happens at Runtime When a Style Is Applied to an Oracle ADF Component

The EL expression that you entered as a value for the property with StyleName in its name is evaluated at runtime. If it corresponds to one of the predefined styles or one that you defined, the style is applied to the Oracle ADF component that you set the property for.

If a cell that references an Oracle ADF component has a style applied to it that differs from the style defined in the properties of the Oracle ADF component, the Oracle ADF component overwrites the existing style at runtime and applies the style defined by its properties.

For example, Figure 9-4 shows the runtime values of the Cost Price column after the Currency[0] style is applied, overriding the default TableCellCurrency style.

Figure 9-4 Runtime Values After Applying Another Style

Runtime values of a column after applying another Style

9.3 Applying Styles Dynamically Using EL Expressions

Oracle ADF component properties that include StyleName in their name can take an EL expression as a value. The EL expressions that you write can resolve to a named Excel style at runtime that is applied to the Oracle ADF component. The EL expressions that you write are Excel formulas that may include Oracle ADF data binding expressions. The Oracle ADF Desktop Integration module does not evaluate or apply results when a user navigates between cells or during upload.

The following examples show different contexts where you can use EL expressions to determine the behavior and appearance of Oracle ADF components at runtime. Example 9-1 applies a style dynamically during download. If the status value for binding is Closed, apply a read-only style (MyReadOnlyStyle). Otherwise apply another style (MyReadWriteStyle).

Example 9-1 Applying a Style Dynamically During Download

=IF("#{bindings.Status}" = "Closed", "MyReadOnlyStyle", "MyReadWriteStyle")

Example 9-2 uses a mixture of Excel formulas and ADF binding expressions to handle errors and type conversion.

Example 9-2 EL Expressions to Handle Errors and Type Conversion

=IF(ISERROR(VALUE("#{bindings.DealSize}")), "BlackStyle", IF(VALUE("#{bindings.DealSize}") > 300, "RedStyle", "BlackStyle"))

9.3.1 What Happens at Runtime When an EL Expression Is Evaluated

When evaluating EL expressions at runtime, the Oracle ADF Desktop Integration module determines the value that the EL expression references. It then replaces the EL expression in the Excel formula with the value. In Example 9-1, the Oracle ADF Desktop Integration module first determines that value of the EL expression, #{bindings.Status}, in the following Excel formula:

=IF("#{bindings.Status}" = "Closed", "MyReadOnlyStyle", "MyReadWriteStyle")

It then replaces the EL expression with the runtime value, as in the following example, where the expression evaluated to Closed:

=IF("Closed" = "Closed", "MyReadOnlyStyle", "MyReadWriteStyle")

Excel evaluates the formula and, in this example, applies the MyReadOnlyStyle style.

9.3.2 How to Write an EL Expression That Applies a Style at Runtime

You write EL expressions for the Oracle ADF component properties that support EL expressions in the Edit Expression dialog box that is accessible from the Oracle ADF component's property inspector. Figure 9-5 displays an Edit Expression dialog box launched from the property inspector window of an ADF Button component.

Figure 9-5 Edit Expression Dialog Box

EL Edit Expression dialog box

To write an EL expression that applies a style at runtime:

  1. Select a cell in the Excel worksheet that references the Oracle ADF component for which you want to write an EL expression.

  2. Click the property inspector button in the Oracle ADF Desktop Integration Designer toolbar to display the property inspector.

  3. Select the property in the property inspector with which you want to associate an EL expression and click the ellipsis button (...) to display the Edit Expression dialog box.

    Note:

    The Edit Expression dialog box appears only if the Oracle ADF component that you selected in Step 1 supports EL expressions. Depending on the context, the ellipsis button (...) can launch other editors such as the Action Collection Editor.

    The Edit Expression dialog box, as illustrated in Figure 9-5, displays a hierarchical list of the Oracle ADF components, bindings, styles, resources, and Excel functions that you can reference in EL expressions. For more information about the syntax of EL expressions that you enter in this dialog box, see Appendix B, "Oracle ADF Desktop Integration EL Expressions".

9.3.3 What You May Need to Know About EL Expressions That Apply Styles

Note the following points when writing EL expressions that apply styles at runtime.

How the Oracle ADF Desktop Integration Module Applies Styles

EL expressions that evaluate to styles are applied when:

  • An ADF Table component invokes its Download or DownloadForInsert actions

  • Rows are inserted into an ADF Table component

  • A worksheet invokes its DownSync action

EL expressions that evaluate to styles are not applied when:

  • An ADF Table component invokes its RowDownSync action

  • An end user edits the format properties of a cell

    Note also that an EL expression that evaluates to a style is not reevaluated when an end user edits a cell's value.

  • The runtime value of an EL expression does not match a style defined in the end user's integrated Excel workbook

    In this scenario the style formats of the targeted cells do not change. Instead, they retain their existing style formats. If you configured client-side logging, the Oracle ADF Desktop Integration module generates an entry in the log file when an EL expression evaluates to a style that is not defined in the end user's integrated Excel workbook. For more information about client-side logging, see Section C.3, "Generating Log Files for an Integrated Excel Workbook".

9.3.4 How to Add a Hyperlink in an Integrated Excel Workbook

Use the following syntax when writing an EL expression that invokes the HYPERLINK Excel function:

=T("=HYPERLINK(""link_location"",""friendly_name"")")

For example, the following EL expression uses HYPERLINK function to navigate to http://www.oracle.com when end user clicks the component.

=T("=HYPERLINK(""http://www.oracle.com"", ""#{bindings.ProductId.inputValue}"")")

If you write an EL expression using the HYPERLINK function, it is recommended that you select the Locked checkbox in the Protection tab of the Format Cells dialog box for the custom style that you apply to prevent error messages appearing.

For more information about the HYPERLINK Excel function, see Section 8.9, "Configuring a Cell to Display a Dynamically Generated Hyperlink" and Microsoft Excel's documentation. For more information about the use of EL expressions in an integrated Excel workbook, see Appendix B, "Oracle ADF Desktop Integration EL Expressions".

9.4 Using Labels in an Integrated Excel Workbook

Use labels to provide end users with information about how they use the functionality in an integrated Excel workbook. You can write EL expressions that retrieve the value of string keys defined in a resource bundle or that retrieve the values of attribute control hints. An integrated Excel workbook evaluates the value of a Label property only when the workbook is initialized.

Retrieving the Values of String Keys from a Resource Bundle

Figure 9-6 shows a portion of the design time view of the EditPriceList-DT.xlsx workbook in the Master Price List module. It shows examples of ADF Label components and ADF Button components that have EL expressions specified for their Label properties.

Figure 9-6 Design Time View of an ADF Label Component and an ADF Button Component with Label Property

Design-time view of label properties

At runtime, these EL expressions resolve to string keys defined in the res resource bundle that is registered with the Master Price List module. Figure 9-7 shows the corresponding runtime view of the ADF Label component and ADF Button component illustrated in design mode in Figure 9-6.

Figure 9-7 Runtime View of an ADF Label Component and an ADF Button Component with Label Property

Runtime view of label properties

For information about referencing string keys from a resource bundle, see Section 10.2, "Using Resource Bundles in an Integrated Excel Workbook".

Retrieving the Values of Attribute Control Hints

In addition to string keys from resource bundles, the ADF Label component and the Label property of other ADF components can reference attribute control hints that you define for entity objects and view objects in your JDeveloper project. Figure 9-8 shows the expression builder for the Product Name column in the EditPriceList-DT.xlsx workbook's ADF Table component. The expression builder contains an EL expression for the HeaderLabel property of the ProductName column that retrieves the value (Product Name) defined for an attribute control hint at runtime.

Figure 9-8 EL Expression That Retrieves the Value of an Attribute Control Hint for a Label Property

EL expression to retrieve an attribute control hint

Attribute control hints can be configured for both view objects and entity objects. Information about how to add an attribute control hint to an entity object can be found in the "Defining Attribute Control Hints for Entity Objects" section of the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework. Information about how to add an attribute control hint to a view object can be found in the "Defining Attribute Control Hints for View Objects" section of the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.

How an Integrated Excel Workbook Evaluates a Label Property

An integrated Excel workbook evaluates the Label properties of ADF components when the workbook is initialized after you or an end user open the workbook for the first time. The integrated Excel workbook saves the retrieved values for the Label properties when the workbook itself is saved to a directory on the machine.

The retrieved values for the Label properties do not get refreshed during invocation of actions such as the worksheet's DownSync action or the ADF Table component's Download action. You indirectly refresh the retrieved values of the Label properties if you invoke the workbook actions ClearAllData or EditOptions described in Table A-17.

9.5 Using Styles to Make Integrated Excel Workbooks Usable

It is good practice to provide end users of integrated Excel workbooks with information that helps them understand how to use the ADF components that you provide to integrate with a Fusion web application. You can do this by:

  • Providing end users with instructions on how to use Oracle ADF components such as ADF Button and ADF Input Text components.

    The ADF Label component and the Label property of other ADF components is useful for this task, as you can write labels that instruct an end user on how to use the component.

  • Apply styles that indicate if an ADF component is read-only or read-write.

Using ADF Label Components to Make an Integrated Excel Workbook Usable

You can use ADF Label components to provide end users of an integrated Excel workbook information about how to use other ADF components in the workbook. For example, many forms, by convention, use an * to indicate to end users that they must enter a value in an input field. Figure 9-9 shows three ADF Input Text components with ADF Label components in adjoining cells. Each ADF Label component references an EL expression that retrieves the value of a string key from a resource bundle at runtime. Each string key includes the * character to indicate to end users that they must supply a value.

Figure 9-9 ADF Label Components Providing End-User Instruction

ADF Label Components Providing End-User Instruction

For information about using resource bundles, see Section 10.2, "Using Resource Bundles in an Integrated Excel Workbook".

About the Read-Only Property in an Integrated Excel Workbook

Note the following points about the read-only property in an integrated Excel workbook:

  • The ADF Output Text and ADF Label components do not have read-only properties. However, both components have implied read-only behavior. In addition, end users can enter values in the cells that host ADF Output Text components and temporarily change the values that appear in these cells. The Oracle ADF Desktop Integration module ignores these changes when uploading from the worksheet and overwrites them when it downloads data from the Fusion web application.

  • The ADF Input Text component, ADF Desktop Integration List of Values component, and TreeNodeList subcomponent each have a read-only property (ReadOnly).

    Note:

    If you specify an Excel formula in the Value property of an ADF Input Text component, the component behaves as if its ReadOnly property were True. The component ignores the actual value of the ReadOnly property.

  • Excel's Protect Sheet and Lock Cell features are not compatible with Oracle ADF Desktop Integration.

To prevent end-user confusion, apply styles to components, such as the ADF Output Text component, that indicate to end users whether a component is read-only or can be edited. By default, the ADF Output Text component uses the predefined style, _ADFDI_OutputTextStyle. You can define your own styles and apply them to components as described in this chapter.

For more information about the properties that Oracle ADF Desktop Integration module components support, see Appendix A, "Oracle ADF Desktop Integration Component Properties and Actions".

9.6 Branding Your Integrated Excel Workbook

The Oracle ADF Desktop Integration module provides a number of features that you can configure to brand your integrated Excel workbook with information such as application name, version information, and copyright information. You can use the workbook BrandingItems group of properties to associate this information with an integrated Excel workbook. You must configure a toolbar menu as described in Section 8.3, "Creating Menu Items" so that an end user can view this branding information by clicking a menu item that invokes the ViewAboutDialog workbook action at runtime. For more information about workbook actions, see Table A-17.

You can also define string keys in a resource bundle to define information, such as titles, in one location that can then be used in multiple locations in an integrated Excel workbook at runtime when EL expressions retrieve the values of these string keys. For information about defining string keys, see Section 10.2, "Using Resource Bundles in an Integrated Excel Workbook".

9.6.1 How to Brand an Integrated Excel Workbook

You define values for the workbook BrandingItems group of properties.

To brand an integrated Excel workbook:

  1. In the Oracle ADF Desktop Integration Designer, click Workbook Properties to display the Edit Workbook Properties dialog box.

  2. Click the ellipsis button (...) beside the input field for BrandingItems to invoke the NameValuePair Collection Editor.

  3. Click Add and specify values for the new element as follows:

    • Name

      Specify the name of the branding item that you want to define.

    • Value

      Specify a literal string or click the ellipsis button (...) to invoke the expression builder and write an EL expression that retrieves a value at runtime.

      Note:

      BrandingItems is a workbook property, so you cannot specify an EL expression to retrieve a value associated with a specific worksheet or to retrieve a binding from a specific page definition file.

    Figure 9-10 shows the design time view of branding items in the Master Price List module.

    Figure 9-10 Design Time View of Branding Items in the Master Price List Module

    Design-time View of Branding Items in the MPL Module

  4. Click OK.

9.6.2 What Happens at Runtime to the Branding Items in an Integrated Excel Workbook

At runtime, the name/value pairs that you define for the BrandingItems group of properties appear in a dialog box that the end user invokes from the About menu item of the Oracle ADF toolbar that you configure to appear as described in Section 8.3, "Creating Menu Items". Figure 9-11 shows the runtime view of branding items in the Master Price List module's EditPriceList-DT.xlsx workbook.

Figure 9-11 Runtime View of Branding Items in the Master Price List Module

Runtime View of Branding Items in the MPL Module

9.7 Changing an Integrated Excel Workbook at Runtime

Once you publish and deploy a finalized integrated Excel workbook, as described in Chapter 14, " Deploying Your Integrated Excel Workbook", end users can make the following changes to a workbook at runtime:

  • Delete a column from an ADF Table or ADF Read-only Table component

  • Drag and drop cells to move ADF components other than an ADF Button component

  • Insert new rows into an ADF Table component

  • Change the order of columns in an ADF Table or ADF Read-only Table component

  • Insert non-integrated columns between the columns of an ADF Table or ADF Read-only Table component

However, some changes to a workbook at runtime can corrupt the integration and are not supported. For example, you must not delete or move the first column of the ADF Table or ADF Read-only Table component at runtime. For more information about what changes are not allowed at runtime, see Section I.3, "Limitations of Integrated Excel Workbook at Runtime".