1. Developing Applications with Oracle ADF Desktop Integration
  2. Business Users Guide
  3. Using an Integrated Excel Workbook

27 Using an Integrated Excel Workbook

Describes common actions when using ADF Desktop Integration and integrated Excel workbooks.

  • 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.
  • To erase a value from a cell that is integrated with the web application, clear the cell value instead of deleting the Excel cell.
  • To prevent a changed row from being included in the Upload operation, double-click the upward pointing triangle in the Changed column to remove it. The data for that row will not be uploaded.
  • 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 invoke this double-click action by displaying a context menu from where you select Invoke Action. To display the context menu, right-click in the cell or, alternatively, select the cell and press Shift + F10 on your keyboard.
  • 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.
  • When you try to close the integrated Excel workbook, Microsoft Excel prompts you with a dialog to save the workbook even if you have not modified it after opening it. This behavior is expected because the add-in modifies an integrated Excel workbook each time you open it.
  • To clear all data from the workbook, click the Clear All Data button from the Workbook group of the runtime ribbon tab.

    This action logs you out of your current session and clears all data in the workbook. The add-in displays a prompt asking if you want to continue. Click Yes to continue.

  • To change the web app root for the current workbook, click the Edit Options button from the Workbook group of the runtime ribbon tab. The add-in displays a dialog that shows the current value of the WebAppRoot property and allows you to enter a new value.

    If you change this value and click OK, the add-in displays a prompt asking if you want to continue. Click Yes to continue. The add-in then logs you out of your current session and clears all data in the workbook.

Some common actions, such as inserting or deleting a row, and sorting data in ADF Table, are described in the subsequent sections.

How to Insert or Paste Rows in an ADF Table Component

To insert rows in the middle of an ADF Table component, insert a full row or rows in the worksheet, and add data in all mandatory columns. For information, see Inserting Data in an ADF Table Component.

Data that you manage in another Excel workbook (for example, a non-integrated Excel workbook) can also be pasted into an ADF Table component.

To paste data from another worksheet into ADF Table component rows:

  1. Arrange the data in the Excel workbook from which you plan to copy the data to match the layout of the ADF Table component in the integrated Excel workbook.

    For example, if the first column in the ADF Table component where you want to enter data is Column D, make Column D the first column where you arrange data in the Excel workbook. Also, make sure to provide data for all mandatory columns that the ADF Table component specifies.

  2. In the Excel workbook, copy the rows of data.

  3. To paste the copied rows into the middle of an ADF Table component:

    1. Select the entire row above which you want to paste the data from the Excel workbook.

    2. With the row selected, right-click and choose Insert Copied Cells.

    3. In the Insert Paste dialog that appears, select Shift cells down.

  4. To paste the copied rows after the last row of an ADF Table component:

    1. Select the entire row above which you want to paste the data from the Excel workbook.

    2. With the row selected, right-click and choose Insert Copied Cells.

To insert a row in an ADF Table component between the header and last row:

  1. In the ADF Table component, select the entire row above which you want to insert the new row.

  2. With the row selected, right-click and choose Insert.

    A new row is inserted above the selected row.

To insert rows in an ADF Table component after the last row:

  1. Type data in an empty row immediately after the last row in the ADF Table component.

    The ADF Table component automatically converts the edited row to a row in the ADF Table component.

Note:

  • If the ADF Table has no data rows, the first row under the column header row acts as a placeholder data row.

  • You cannot enter data directly under the table's data rows if ADF Desktop Integration worksheet protection is enabled.

How to Sort ADF Table Data in an Integrated Excel Workbook

To sort table data, choose Excel's Sort and Filter command.

To sort ADF Table data based on a particular column:

  1. Select the header, or any cell, of the column you want to sort.

  2. 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:

  1. Select any cell of the table.
  2. In the Editing group of the Home tab, click Sort and Filter, and choose Custom Sort.
  3. In the Sort dialog, add the columns, and their order preference. Ensure that the My data has headers checkbox is enabled.
  4. Click OK.

Note:

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.

How to Delete a Row in ADF Table of an Integrated Excel Workbook

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 information about row flagging, see Row Flagging in an ADF Table Component.

Note:

If your table does not contain a Flagged column, you will not be able to delete rows from that table.