This appendix describes the actions the end user would be performing while using your application and integrated Excel workbook, such as installing runtime edition of ADF Desktop Integration, importing data from non-integrated Excel workbook, making changes in the workbook at runtime, and handling time zone conversion of date-time values in the workbook.
The actions described in this appendix assume that you have developed a functioning Fusion web application.
This appendix includes the following sections:
Section H.1, "Installing, Upgrading, and Removing the Runtime Edition of ADF Desktop Integration"
Section H.2, "Importing Data from a Non-Integrated Excel Worksheet"
Section H.4, "Editing an Integrated Excel Workbook at Runtime"
Section H.5, "Limitations of an Integrated Excel Workbook at Runtime"
End users can install the runtime edition of ADF Desktop Integration using the client installer.
When the ADF Desktop Integration client installer runs, it verifies whether the required software is installed on the system. For more information about the required software, see the following:
Section 3.2, "Required Oracle ADF Modules and Third-Party Software"
Section 3.3, "Configuring Excel to work with ADF Desktop Integration"
You cannot install the Runtime edition of ADF Desktop Integration from JDeveloper.
JDeveloper is not required to install the runtime edition of ADF Desktop Integration.
To install the runtime edition, the client installer must be made available to the end user on the end user's local system. Ask your system administrator to make the client installer files available to the end user.
To install the Runtime edition of ADF Desktop Integration:
Navigate to the
\oracle_common\modules\oracle.adf.desktopintegration_12.1.3 directory, where
MW_HOME is the Middleware Home directory.
Extract the contents of
adfdi-excel-runtime-client-installer.zip to a temporary directory on the end user's local file system (for example,
Note that the fully qualified path of the temporary directory must be less than 248 characters.
setup.exe file located in the extracted directory of the
Figure H-1 shows an example of the
setup.exe file location on a local system, and the installation of runtime edition.
Follow the instructions that appear in the dialog boxes launched by
setup.exe to successfully install the required components.
If prompted, click Yes to restart the system and complete the setup of ADF Desktop Integration.
After you install the runtime edition of ADF Desktop Integration, do not delete the directory where you copied the client installer files. You can delete the files after removing the runtime edition of ADF Desktop Integration from the system.
Use the Microsoft Windows Control Panel to remove the runtime edition of ADF Desktop Integration from the system.
To remove the ADF Desktop Integration add-in:
Click the Windows Start button, and then choose Control Panel.
In the Control Panel, select and open Programs and Features.
Select the Oracle ADF Desktop Integration Runtime add-in for Excel entry in the Uninstall or change a program window, and click Uninstall.
To upgrade the runtime edition of ADF Desktop Integration, uninstall the old client version, and install the new version. For more information about uninstalling the runtime edition, see Section H.1.2, "How to Remove the Runtime Edition of ADF Desktop Integration," and for more information about installing the runtime edition, see Section H.1.1, "How to Install Runtime Edition of ADF Desktop Integration."
If the runtime edition of ADF Desktop Integration is installed from a shared file server or a web server, by default, Excel checks the server for ADF Desktop Integration updates when it is launched and if 24 hours have elapsed since the last check for updates. If you do not wish to wait 24 hours and want to check the update manually, you can do so from the Microsoft Windows Control Panel.
To upgrade the Runtime edition of ADF Desktop Integration manually:
Click the Windows Start button, and then choose Control Panel.
In the Control Panel, select and open Programs and Features.
Select the Oracle ADF Desktop Integration Runtime Add-in for Excel entry in the Uninstall or change a program window.
With the entry selected, click the Update Information link.
If the runtime edition of ADF Desktop Integration is installed from a web server, open the Update Information link in Internet Explorer to run the installer. Other browsers are not supported.
Follow the instructions that appear in the dialog boxes to successfully upgrade ADF Desktop Integration.
End users who use the ADF Table component in an integrated Excel workbook to upload large batches of data rows to the Fusion web application can prepare these rows of data in a non-integrated Excel worksheet. They can then insert the data into the ADF Table component prior to invoking the ADF Table component's
To prepare data in a non-integrated Excel workbook:
Arrange the layout of data in a non-integrated Excel worksheet to match the layout of the ADF Table component in the integrated Excel workbook.
For example, if an ADF Table component contains columns such as
Description, reproduce this layout in the non-integrated Excel worksheet.
Copy the column headers from the ADF Table component to the non-integrated Excel worksheet.
Use functionality of Excel to import the rows of data into the non-integrated Excel worksheet in rows under the columns arranged in Step 1.
Row values that will be inserted into ADF Table component columns that use the TreeNodeList subcomponent must match a choice from the list of values.
Copy an ADF Table component row from the integrated Excel workbook to another worksheet of the same workbook, as the proper constraints will be defined for such a row and can be reproduced.
To insert data into the ADF Table component from a non-integrated Excel workbook:
In the ADF Table component, highlight n existing downloaded rows or new rows at the end of the ADF Table component where n is the number of rows to insert.
In the non-integrated Excel worksheet, select the cells to insert into the rows of the ADF Table component created in Step 2.
Select the cells in the non-integrated Excel worksheet and not the rows or columns.
In the Excel Ribbon, choose Home > Copy.
In the ADF Table component, select the upper left corner cell of the rows inserted in Step 2.
In the Excel Ribbon, choose Home > Paste.
End users can now invoke the ADF Table component's
Upload action using whatever functionality you configured for them as described in Section 7.8, "Configuring an ADF Component to Upload Changes from an ADF Table Component."
If the Fusion web application that you integrate an Excel workbook with uses a security mechanism, such as single sign-on, personally identifying information may be stored in cookies on the system where the end user accesses the integrated Excel workbook. End users can remove this information using Microsoft Internet Explorer. End users must log out and close all integrated Excel workbooks to invalidate all active cookie-based web sessions.
For information about removing personal information, see Microsoft Internet Explorer documentation.
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 more information about what changes are not allowed at runtime, see Section H.5, "Limitations of an Integrated Excel Workbook at Runtime."
There are some known limitations on changing ADF Desktop Integration components at runtime.
Moving a column in ADF Table and ADF Read-only Table components – If the end user moves a column (for example, using native Excel cut-insert operations) to the right of the last table column, the column is considered to be outside of the table boundaries. ADF Desktop Integration will no longer recognize the column as being part of the table component during subsequent table operations.
Instead, to change the last column of a table component at runtime, the end user can perform either of the following actions:
Move all columns that are to the right of the desired last column to the left of the desired last column.
First, move the desired last column to the immediate left of the current last column, and then move the current last column to the left of the desired last column.
Similar actions should be performed to move a column so that it becomes the left-most column of the table.
Deleting an integrated Excel worksheet – If the end user deletes an integrated Excel worksheet, ADF Desktop Integration generates an exception when the end user tries to save the integrated Excel workbook.
To resolve the problem, the end user must close and reopen the workbook without saving changes.
Excel's Conditional Formatting feature cannot be used effectively with ADF Desktop Integration table components.
The ADF Button components are disabled when the end user zooms in or out on an integrated Excel worksheet. The ADF Button components are active at 100% zoom only.
Due to native Excel behavior, sorting a dependent List of Values column at runtime does not sort the cell list validations properly.
End users who are new to the ADF Desktop Integration technology and integrated Excel workbook must be made aware of the following common actions:
To download all rows after uploading the changed data, ensure that Download all rows after successful upload checkbox is selected in Upload Options dialog box.
Before uploading the changes, ensure that the Changed column of all modified rows is marked with an upward pointing triangle. A double-click on the upward pointing triangle character removes it, and the data of the relevant row is not uploaded.
To have Excel retain the format of a numeric or date value in a cell formatted with a text style while uploading data, add an apostrophe symbol (') before entering the value. The apostrophe symbol acts as an escape character and is not displayed with the value.
Do not delete, edit, or clear any cells in the Key column of the table. Any change to these values can lead to upload failures and data corruption.
Do not change Excel's settings for Protect Sheet or Protect Workbook. These settings are available in the Changes group of the Review tab.
To erase a value from a cell that is integrated with the web application, clear the cell value instead of deleting the Excel cell.
If the Fusion web application is running on the
https protocol and you have not installed the security certificate on the client, the integrated Excel workbook gives an error on login and the connection is not established. To establish a connection, you must install the security certificate. If you cannot install the certificate from Excel, open Internet Explorer and navigate to the same website. You will be prompted to install the certificate.
Some ADF components may have cells that are configured to respond to a double-click to perform some action. For example, the Status column cells of the ADF Table component. You can also right-click in these cells and select Invoke Action.
Some common actions, such as inserting or deleting a row, and sorting data in ADF Table, are described in the subsequent sections.
To insert a row in an ADF Table component, insert a full row in the worksheet, and add data in all mandatory columns. For more information, see Section 7.7, "Configuring an ADF Table Component to Insert Data."
An ADF Table, with one or more data rows, does not have a placeholder row after the last row of the table. Hence, any data inserted in the cells of the workbook after the last row of the table is not uploaded. To upload data of the row after the last row, you must insert a full Excel row.
To insert a row in an ADF Table component:
In the ADF Table component, click the row header, and select the entire row above which you want to insert the new row.
With the row selected, right-click and choose Insert.
A new row is inserted above the selected row.
Insert a full Excel row between the table headers and the last row of the table. Do not insert the new row after the last row of the table.
If the ADF Table has no data rows, the first row of the table acts as a placeholder row.
To sort table data, choose Excel's Sort and Filter command.
To sort ADF Table data based on a particular column:
Select the header, or any cell, of the column you want to sort.
In the Editing group of the Home tab, click Sort and Filter. Choose the desired sort order from the dropdown list options.
To sort table data based on multiple columns:
Select any cell of the table.
In the Editing group of the Home tab, click Sort and Filter, and choose Custom Sort.
In the Sort dialog, add the columns, and their order preference. Ensure that the My data has headers checkbox is enabled.
While sorting the columns in an ADF Table component, ensure that you always choose Expand the selection in the Sort Warning dialog, when prompted, in order to maintain the integrity of the data in all the table rows.
Clearing the cell values of a row does not remove the row, and deleting the row from the Excel worksheet does not delete the row from the web application.
To delete a row in an ADF Table component, flag the row by double-clicking the respective cell of the Flagged column, and click the respective delete button. For more information about row flagging, see Section 7.11.2, "Row Flagging in an ADF Table Component."
If your table does not contain a Flagged column, you will not be able to delete rows from that table.
You can configure integrated Excel workbooks to retrieve, edit, and submit data values that represent dates and times. As Excel does not provide native support for managing date or time data when the system time zone changes, ADF Desktop Integration tracks and detects the time zone changes for a workbook. It informs the end user about the time zone update when the workbook is opened, and then converts the date-time data of the workbook to the current time zone setting of the system.
For example, an end user in Arizona (GMT -07:00) downloads the data from the server to the integrated Excel workbook, edits the date-time data in the workbook, saves the data, but does not upload it. Later, the end user travels to Seoul and changes the time zone preference of the computer to GMT +09:00. When the workbook is opened after changing to the Seoul time zone, the end user receives a message, and then all date-time data values in the ADF components are converted from GMT -07:00 (Arizona) to GMT +09:00 (Seoul).