This chapter describes how to insert and configure components (such as buttons, labels, input and output text, and list of values) that ADF Desktop Integration provides to allow end users to manage data retrieved from a Fusion web application, and how to display calculated data in these components using Excel formulae.
This chapter includes the following sections:
Section 6.1, "About ADF Desktop Integration Form-Type Components"
Section 6.9, "Displaying Tooltips in ADF Desktop Integration Form-Type Components"
Section 6.10, "Displaying Output from a Managed Bean in an ADF Component"
Section 6.11, "Displaying Concatenated or Calculated Data in Components"
The ADF Desktop Integration Form-type components allow end users to manage data retrieved from the Fusion web application in the integrated Excel workbook. Rather than expose an ADF Form component in the components palette described in Section 5.5, "Using the Components Palette," ADF Desktop Integration uses the following components to create form-type functionality in an integrated Excel workbook:
ADF Input Text
ADF Output Text
ADF Label
ADF List of Values
ADF Button
Figure 6-1 shows these components.
You can insert a form component or a binding in a merged cell, or merge cells after inserting the form component or binding, but you cannot insert multiple form components in a merged cell or merge cells that are occupied by different form components.
Before you insert a component in a merged cell, make a note of the following:
Drag-and-drop functionality is not supported for inserting component in a merged cell.
Do not merge a component cell with non-empty cells that are above or left to it. When two or more cells are merged, Excel keeps the data and style of the most upper-left cell and discards the data of the remaining cells. So, merging a component cell with a non-empty cell above or left to itself results in the component data being overwritten.
Do not merge an empty component cell that has no value or binding with empty cells above or left to it. Merging an empty component cell with empty cells above or left to itself results in the style of that component cell being overwritten.
ADF Buttons do not expand to the whole merged area automatically. You can edit the Position
and LowerRightCorner
properties of the button to resize it as needed.
The ADF Desktop Integration Form-type components are used to build forms in the integrated Excel workbook for user input, and output from the Fusion web application. As shown in Figure 6-2, the form-type components used in navigation form of EditWarehouses-DT.xlsx
enable end users to navigate and update data easily.
After you have added a component to the worksheet, you may find that you need to add functionality such as responding to events or end user actions. Following are links to other functionality that form components can use:
Displaying output from a managed bean: You can use ADF Label or ADF Output Text components to display output from a managed bean. For more information, see Section 6.10, "Displaying Output from a Managed Bean in an ADF Component."
Styles: You can configure the display of your form-type components using several predefined Excel styles. For more information, see Section 9.2, "Working with Styles."
EL Expressions: You can use EL expressions with form-type components. For more information, see Appendix B, "ADF Desktop Integration EL Expressions."
The ADF Button component renders a button in the Excel worksheet at runtime. End users click this button to invoke one or more actions specified by the ClickActionSet
group of properties.
The LowerRightCorner
and Position
properties determine the area that the button occupies on the Excel worksheet at runtime.
Figure 6-3 shows an ADF Button component with its property inspector. When an end user clicks the button at runtime, it invokes the array of actions specified by ClickActionSet
.
For more information about the properties of the ADF Button component, see Section A.11, "ADF Button Component Properties."
To insert an ADF Button component:
Open the integrated Excel workbook.
Select the cell in the Excel worksheet where you want to anchor the component.
In the components palette, select ADF Button and click Insert Component. Alternatively, in the Oracle ADF tab, select ADF Button from the Insert Component dropdown list.
Configure properties in the property inspector to determine the actions the component invokes at runtime in addition to the appearance, design, and layout of the component. Table 6-1 outlines some properties you must specify values for, and provides links to additional information.
Table 6-1 ADF Button component properties
For this property... | Specify... |
---|---|
|
A string or an EL expression that resolves to a label at runtime to indicate the purpose of the ADF Button component. The button label defaults to the action binding ID. The EL expression, if used, references a string key in the To include the ampersand ( |
|
Specify one or more actions in the |
Click OK.
Figure 6-4 shows an example of the ADF Button component (in red box) at runtime.
Notes:
If you change the view mode of the Excel worksheet to the Page Layout or Page Break mode, the ADF Button components may be rendered in an unexpected position. You must return back to Normal mode without saving the workbook, and then Run and stop the integrated Excel workbook to render the buttons to their original positions.
You can modify the properties of the component at a later time by selecting the cell in the worksheet. Click the ADF Button component to open its property inspector.
The ADF Button components are active at 100% zoom only, and are disabled when the end user zooms in or out on an integrated Excel worksheet.
To remove the component, use the Delete ribbon command. For more information, see Section 5.14, "Removing ADF Desktop Integration Components."
Tip:
In design mode, you can click the button, or press the spacebar when the button is in focus, to open the property inspector. The right-click context menu is disabled for a button.
If you want to add navigation buttons in your integrated Excel workbook to navigate to previous or next record, see Section 6.12, "Using Navigation Buttons."
The ADF Label component is a component that you can insert into the active worksheet to display a static string value. You specify a value in the input field for Label
in the property inspector or alternatively you invoke the expression builder to write an EL expression that resolves to a string at runtime. The retrieved string can be defined in a resource bundle or in an attribute control hint for an entity or view object. For example, the following EL expression resolves to the value of label of CountryId attribute binding at runtime:
#{bindings.CountryId.label}
The value that you specify for the Label
property in an ADF Label component or other Oracle ADF components is evaluated after the worksheet that hosts the Oracle ADF component is initialized (opened for the first time).
You can configure a number of properties for the component, such as style and position, in the worksheet using the property inspector.
Figure 6-5 shows an ADF Label component with its property inspector in the foreground. The ADF Label component references an EL expression that resolves to the label of CountryId attribute binding at runtime.
To insert an ADF Label component:
Open the integrated Excel workbook.
Select the cell in the Excel worksheet where you want to anchor the component.
In the components palette, select ADF Label and click Insert Component. Alternatively, in the Oracle ADF tab, select ADF Label from the Insert Component dropdown list
Configure properties in the property inspector to determine the appearance, design, and layout of the component.
Click OK.
Figure 6-6 shows an example of the ADF Label component (in red box) at runtime.
Note:
You can modify the properties of the component at a later time by selecting the cell in the worksheet that anchors the component and then displaying the property inspector. You can also right-click in the cell and choose Edit ADF Component Properties to open the property inspector.
To remove the component, use the Delete ribbon command. For more information, see Section 5.14, "Removing ADF Desktop Integration Components."
For more information about using labels in an integrated Excel workbook, see Section 9.4, "Using Labels in an Integrated Excel Workbook."
The ADF Input Text component is a component that you can insert into the active worksheet using the components palette. The active cell in the worksheet when you insert the component displays the current value from the component's binding after the worksheet DownSync
action is invoked. End users can edit this value at runtime. You configure the worksheet UpSync
action to transfer changes end users make to the value back to the Fusion web application and a Commit
action binding to commit the changes in the Fusion web application.
You can configure a number of properties for the component, such as its position, style and behavior when a user double-clicks the cell (DoubleClickActionSet
properties), in the worksheet using the property inspector. For more information about DoubleClickActionSet
, see Section 8.2, "Using Action Sets."
The ADF Table component can invoke this component as a subcomponent when you set values for the ADF Table component column's InsertComponent
or UpdateComponent
properties. In this context, the ADF Input Text component enables the end user to input data into the ADF Table component. For more information, see Section 7.7, "Configuring an ADF Table Component to Insert Data."
Figure 6-7 shows an ADF Input Text component with its property inspector in the foreground. The ADF Input Text component binds to the City
attribute binding in the Summit sample application for ADF Desktop Integration. The end user enters a city name in this component.
To insert an ADF Input Text component:
Open the integrated Excel workbook.
Select the cell in the Excel worksheet where you want to anchor the component.
In the components palette, select ADF Input Text and click Insert Component. Alternatively, in the Oracle ADF tab, select ADF Input Text from the Insert Component dropdown list
Configure properties in the property inspector to determine the appearance, layout, and behavior of the component. Table 6-2 outlines some properties that you must specify values for. For information about the component's other properties, see Section A.2, "ADF Input Text Component Properties."
Table 6-2 ADF Input Text component properties
For this property... | Specify... |
---|---|
|
An EL expression for the |
|
An EL expression that resolves to |
Click OK.
Note:
You can modify the properties of the component at a later time by selecting the cell in the worksheet that anchors the component and then displaying the property inspector. You can also right-click in the cell and choose Edit ADF Component Properties to open the property inspector.
To remove the component, use the Delete ribbon command. For more information, see Section 5.14, "Removing ADF Desktop Integration Components."
Figure 6-8 shows an example of the ADF Input Text component (in red box) at runtime.
The ADF Output Text component is a component that you can insert into the active worksheet using the components palette. The active cell in the worksheet when you insert the component displays the current value from the component's binding after you invoke the worksheet DownSync
action. The value the component displays is read-only. Changes that the end user makes to the value in the cell that anchors the component are ignored when changes are sent to the Fusion web application.
This component can also serve as a subcomponent for the ADF Table and ADF Read-only Table components. Columns in the ADF Table and ADF Read-only Table components can be configured to use the ADF Output Text component.
You can configure a number of properties for the component such as style, behavior when a user double-clicks the cell (DoubleClickActionSet
properties), and position, in the worksheet using the property inspector.
Figure 6-9 shows an ADF Output Text component with its property inspector in the foreground.
To insert an ADF Output Text component:
Open the integrated Excel workbook.
Select the cell in the Excel worksheet where you want to anchor the component.
In the components palette, select ADF Output Text, and click Insert Component. Alternatively, in the Oracle ADF tab, select ADF Output Text from the Insert Component dropdown list
Configure properties in the property inspector to determine the appearance, layout, and behavior of the component.
For example, you must write or specify an EL expression for the Value
property to determine what binding the ADF Output Text component references. For more information about the values that you specify for the properties of the ADF Output Text component, see Section A.3, "ADF Output Text Component Properties."
Click OK.
Note:
You can modify the properties of the component at a later time by selecting the cell in the worksheet that anchors the component and then displaying the property inspector. You can also right-click in the cell and choose Edit ADF Component Properties to open the property inspector.
To remove the component, use the Delete ribbon command. For more information, see Section 5.14, "Removing ADF Desktop Integration Components."
Figure 6-10 shows an example of the ADF Output Text component (in red box) at runtime.
The ADF List of Values component is a component that displays a dropdown menu in the Excel worksheet cell at runtime. It displays a maximum of 250 values at runtime. You can insert the List of Values component into a cell in the Excel worksheet.
You must specify a value for the ListID
property. The ListID
property references the list binding which populates the dropdown menu with a list of values at runtime after you invoke the worksheet DownSync
action.
Figure 6-11 shows an ADF List of Values component with its property inspector in the foreground. The ADF List of Values component references a list binding (RegionId
) that populates a dropdown menu in the Excel worksheet at runtime.
Notes:
You can display a dropdown menu in an ADF Table component's column by selecting TreeNodeList
or ModelDrivenColumnComponent
as the subcomponent to create when you specify a value for the TableColumn
array's InsertComponent
property. For more information, see Section 7.14, "Creating a List of Values in an ADF Table Component Column."
ADF List of Values components using date values are not supported.
To insert an ADF List of Values component:
Open the integrated Excel workbook.
Select the cell in the Excel worksheet where you want to anchor the component.
In the components palette, select ADF List of Values and click Insert Component. Alternatively, in the Oracle ADF tab, select ADF List of Values from the Insert Component dropdown list
Invoke the binding ID picker by clicking the browse (...) icon beside the input field for the ListID property and select a list binding that the page definition file exposes.
Configure other properties in the property inspector to determine the appearance, design, and layout of the component. For information about ADF List of Values component properties, see Section A.7, "ADF List of Values Component Properties."
Click OK.
Notes:
You can modify the properties of the component at a later time by selecting the cell in the worksheet that anchors the component and then displaying the property inspector. You can also right-click in the cell and choose Edit ADF Component Properties to open the property inspector.
To remove the component, use the Delete ribbon command. For more information, see Section 5.14, "Removing ADF Desktop Integration Components."
An Excel form cannot be configured to use ADF List of Values components that use model-driven list bindings if the form's bound iterator is expected to contain zero rows. As a workaround, you may configure the ADF List of Values component to use a dynamic list binding instead.
Using the ADF Image component, you can insert an image (for example, a company logo) in the integrated Excel worksheet. At design time, specify the URL of the image file in Source
, and the ADF Image component renders the image at runtime. The image is rendered at original size at runtime.
At runtime, when the image component is rendered, ADF Desktop Integration determines whether the Source
property value is an absolute URL or a relative URL. The source URL is considered to be absolute if it starts with http
and https
, the only supported schemes. If the URL is absolute, it is used as is to fetch the image and insert that image into the worksheet. If the URL is not absolute, the partial URI is assumed to be relative to the workbook's WebAppRoot
. In such a case, the WebAppRoot
value and the Source
value are concatenated to form the complete image URL. If the attempt to fetch and insert the image fails for any reason (such as, invalid URL), a log entry is created. The technical details regarding the failure are reported in the client logs. The worksheet initialization is not interrupted and there are no warnings or error messages presented to the end user. The end user sees the short description of the image in the cell location where the image would have been displayed.
Figure 6-12 shows an ADF Image component in EditCustomers-DT.xlsx
at design time.
To insert an ADF Image component:
Open the integrated Excel workbook.
Select the cell in the Excel worksheet where you want to anchor the component.
In the components palette, select ADF Image and click Insert Component. Alternatively, in the Oracle ADF tab, select ADF Image from the Insert Component dropdown list
Configure properties in the property inspector to determine the appearance, layout, and behavior of the component. Table 6-3 outlines some properties that you must specify values for. For information about the component's other properties, see Section A.8, "ADF Image Component Properties."
Table 6-3 ADF Image component properties
For this property... | Specify... |
---|---|
|
The URL of the image file. You can use absolute or relative URLs as the source of the image. If the URL is not absolute, the partial URI is assumed to be relative to the workbook's Examples:
Note that the For the list of supported image formats, see Microsoft Excel documentation. |
|
The String message as the alternate text of the image, if the image is not found or cannot be rendered. You can also specify an EL expression that resolves to the alternate text of the image component. Note that the |
Click OK.
Note:
You can modify the properties of the component at a later time by selecting the cell in the worksheet that anchors the component and then displaying the property inspector. You can also right-click in the cell and choose Edit ADF Component Properties to open the property inspector.
To remove the component, use the Delete ribbon command. For more information, see Section 5.14, "Removing ADF Desktop Integration Components."
Figure 6-13 shows an example of the ADF Image component at runtime.
Note:
If the worksheet is not protected, the end user may resize or move the image at runtime. Depending on the size of the image, it might appear over (and hide) other worksheet contents, including other ADF Desktop Integration components.
ADF Desktop Integration provides two error components, ADF Output Text (Worksheet Errors) and ADF Output Text (Table Errors) that you can use to display error details generated by the integrated Excel worksheet and the ADF Table component respectively.
Both error components use the ADF Output Text component to display the error message. The ADF Output Text (Worksheet Errors) component is automatically configured to display the worksheet errors, and the ADF Output Text (Table Errors) is automatically configured to display the table errors.
Open the integrated Excel workbook.
Select the cell in the Excel worksheet where you want to anchor the component.
In the components palette, select ADF Output Text (Worksheet Errors) or ADF Output Text (Table Errors), as desired, and click Insert Component.
If the current worksheet contains one, and only one, table component, the ADF Output Text (Table Errors) automatically displays all error messages of the table.
If the current worksheet contains multiple table components, you are prompted to choose a table before proceeding with the add component operation. Select the ID of the table to associate with the ADF Output Text (Table Errors) component in the Select Table dialog, and click OK. To insert the ADF Output Text (Table Errors) component, the worksheet must contain at least one ADF Table component.
Alternatively, in the Oracle ADF tab, select ADF Output Text (Worksheet Errors) or ADF Output Text (Table Errors) from the Insert Component dropdown list.
If required, configure component properties in the property inspector.
Click OK.
Note:
You can modify the properties of the component at a later time by selecting the cell in the worksheet that anchors the component and then displaying the property inspector. You can also right-click in the cell and choose Edit ADF Component Properties to open the property inspector.
To remove the component, use the Delete ribbon command. For more information, see Section 5.14, "Removing ADF Desktop Integration Components."
ADF Output Text (Worksheet Errors) Component
To display a worksheet's error message summaries generated by action sets invoked on a worksheet, the ADF Output Text (Worksheet Errors) component uses #{worksheet.errors}
as the EL expression set to the Value
property of the ADF Output Text component. At runtime, the previous error message summary is cleared (if one existed) when the action set starts the invocation. If no errors occur during invocation, the error message remains blank. If an error occurs, the ADF Output Text component displays the error message summary.
The ADF Output Text (Worksheet Errors) component is pre-configured to use the DisplayWorksheetErrors
action as a DoubleClickActionSet
item.
Figure 6-14 shows the ADF Output Text (Worksheet Errors) component at design-time.
At runtime, double-clicking the ADF Output Text component invokes the DisplayWorksheetErrors
action as shown in Figure 6-15.
For more information about the Worksheet's DisplayWorksheetErrors
action, see Section A.16, "Worksheet Actions and Properties."
ADF Output Text (Table Errors) Component
To allow the user to view details of errors that occur during table upload batch commit operations, the ADF Output Text (Table Errors) component uses #{components.
tableID
.errors}
as the EL expression set to the Value
property of the ADF Output Text component. At runtime, the previous error message summary is cleared (if one existed) when the action set starts the invocation. If no errors occur during invocation, the error message remains blank. If an error occurs, the ADF Output Text component displays the error message summary.
The ADF Output Text (Table Errors) component is pre-configured to use the ADF Table component's DisplayTableErrors
action as a DoubleClickActionSet
item.
Figure 6-16 shows the ADF Output Text (Table Errors) component at design-time.
At runtime, double-clicking the ADF Output Text component invokes the DisplayTableErrors
action as shown in Figure 6-17.
For more information about ADF Table component actions, see Section A.12, "ADF Table Component Properties and Actions."
Using tooltips, you can display a hint or an instruction text for the component. The tooltip is displayed in the Comment window of the cell that anchors the component.
If a component is created from a binding element, the tooltip is automatically set to the model-driven tooltip, else it is empty. Note that the tooltip is always initially empty for the ADF Label component, and is not applicable to the ADF Button component.
You can enter the hint or the instruction text in the Tooltip
property of the component. You can also specify an EL expression (including a resource expression) that evaluates to the tooltip text.
Note:
ADF Desktop Integration also supports toolTip
attribute control hint in EL expressions. The support is similar to the mandatory
control hint described in Table B-3 of Appendix B, "ADF Desktop Integration EL Expressions."
To add a tooltip to an inserted form-type component:
Open the integrated Excel workbook.
If the Form-type component has already been inserted in the Excel worksheet, select the component, and click Edit Properties in the Oracle ADF tab.
To insert a component to the worksheet, select the cell where you want to anchor the component. In the components palette or the bindings palette, select the Form-type component or the binding, and click Insert Component or Insert Binding.
In the Property Inspector, click the browse (...) icon of the Tooltip
property, and enter the tooltip message.
If a component is created from a binding element, the Tooltip
property would be set to the model-driven tooltip. If required, you can configure and change the tooltip message or the EL expression. The property would be empty if the component is not created from a binding element.
Figure 6-18 shows the Tooltip
property of an Input Text component.
Click OK.
Figure 6-19 shows the tooltip message of the Phone field of EditWarehouses-DT.xlsx
at the runtime. Notice the small red arrow at the top-right of the Input Text component. It indicates the component, or the cell, has a comment. Hover mouse pointer over the component to see the tooltip message.
The tooltips are rendered once only, and are not updated after a call to Worksheet.DownSync
.
At design-time, if you inserted a comment manually to a cell or a component, ADF Desktop Integration would override it at runtime. The old comment will be removed and replaced with the tooltip message of the component. When returning to design-time mode from test mode, all manually inserted Excel comments are removed as each component is rendered for its design-time appearance.
At runtime, if the Tooltip
property is non-empty, the expression is evaluated and the resulting text is trimmed of whitespace. If the final value is non-empty, it is inserted into the target cell as an Excel comment.
When a component is positioned on a merged range of cells, the tooltip appears on the top-right corner of the merged range.
You can also add tooltips to table columns (see Section 7.18.1, "What You May Need to Know About Tooltips for Table Columns") and Worksheet Ribbon commands (see Section 8.3.2, "How to Configure a Worksheet Command for the Runtime Ribbon Tab").
Notes:
Tooltips are not editable in a protected worksheet.
Ribbon command tooltips have a maximum size of 1024 characters. If a tooltip value exceeds that limit, only the first 1024 characters are shown.
If Excel Comments are disabled, tooltips for form components and table headers are not rendered.
Extensive usage of tooltips may impact runtime performance.
You can configure an ADF component to display output from a managed bean in your Fusion web application. Information about how to use managed beans in a Fusion web application can be found in the "Using a Managed Bean in a Fusion Web Application" section of the Developing Fusion Web Applications with Oracle Application Development Framework. You reference a managed bean in an integrated Excel workbook through an EL expression. Add a method action binding to the page definition file you associate with the Excel worksheet to retrieve the value of the managed bean and assign it to an attribute binding. Use an EL expression to retrieve the value of the attribute binding at runtime.
You write an EL expression for a property that supports EL expressions (for example, the Label
property).
It may be helpful to have an understanding of managed beans. For more information, see Section 6.10, "Displaying Output from a Managed Bean in an ADF Component."
You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 6.1.2, "Additional Functionality for ADF Desktop Integration Form-Type Components."
To display output from a managed bean:
Open the integrated Excel workbook.
Select the ADF component to display the output from the managed bean, and open its property inspector.
Figure 6-20 shows an example where an ADF Label component is configured to display the output from an attribute binding that has its value populated by an action binding.
Write an EL expression that gets the output from a managed bean at runtime.
The example in Figure 6-20 shows an EL expression that retrieves the value of a string key (excel.connectionPrefix
) from the res
resource bundle and the value of the loggedInUser
attribute binding. This attribute binding references the output from the managed bean.
Click OK.
The method action binding retrieves values from the managed bean and populates the attribute binding. The EL expression that you write retrieves the value from the attribute binding and displays it to the end user through the ADF component that you configured to display output. For example, the ADF Label component shown in design mode in Figure 6-21 displays a string similar to the following at runtime:
Connected as sking
In Figure 6-21, sking
is the user name of the user that is logged on to the Fusion web application through the integrated Excel workbook.
The ADF Desktop Integration module supports EL expressions within components that allow a single component to display data that is based on a calculation or concatenation of multiple binding expressions.
You write an EL expression for the Value
property of an Input Text or Output Text component.
Figure 6-22 shows an EL expression example where an ADF Output Text component is configured to display the margin between two fields: List Price and Cost Price.
It may be helpful to have an understanding of how to display concatenated or calculated data in ADF components. For more information, see Section 6.11, "Displaying Concatenated or Calculated Data in Components."
You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 6.1.2, "Additional Functionality for ADF Desktop Integration Form-Type Components."
To create an EL expression to display calculated data
Open the integrated Excel workbook.
Select the ADF Input Text or ADF Output Text component to display calculated data.
Open the property inspector and click the browse (...) icon of the Value
property.
Write an EL expression that gets the output from two, or more, expressions.
Example 6-1 shows an EL expression that calculates the difference between the values of two fields, List Price and Cost Price, and then divides it with value of Cost Price column to generate a margin.
Click OK.
For more information about EL expressions, see Appendix B, "ADF Desktop Integration EL Expressions."
Note:
If the Value
property of an ADF Input Text component contains a formula, the ADF Input Text component becomes read-only at runtime regardless of the value of the ReadOnly
property.
You can create navigation buttons (Next, Previous, First, and Last) to navigate from one record to another as shown in Figure 6-23. If the end user changes a record's data before navigating to another record, you can choose to save those changes or ignore them.
To save changes before navigating to another record, define the action sets of the button in the following order:
Worksheet.UpSync
Commit
Navigation action (for example, Next)
Worksheet.DownSync
Note:
If you omit the Commit
action from the action set, any pending changes to multiple records are lost when the end user's web application session ends.
To ignore changes before navigating to another record, define the action sets of the button in the following order:
Navigation action (for example, Next)
Worksheet.DownSync
Note:
If you define button actions to ignore changes, then it is the end user's responsibility to save changes before navigating to another record.