Part 3: Developing a More Refined ADF Desktop Integration-enabled Excel Workbook
In Part 2 you created a basic Excel workbook, integrated with a Fusion Web application. In this part of the tutorial you add detail to that workbook to create one that is more functional.
show more or lessRead more...

The EditDept_LOV workbook contains a list of values field and a table that has insert/update/delete functionality. You also add menu items to the Excel ribbon in the workbook.

Step 1: Creating the EditDept_LOV Workbook
  1. Right-click the ViewController project in the Navigator and select New... from the context menu. In the New Gallery expand the Client Tier node if it is not already expanded, and select ADF Desktop Integration. Microsoft Excel Workbook should be automatically highlighted in the Items pane, so click OK to select it.

    alt text
  2. This first step - creating the EditDept_LOV workbook - involves exactly the same tasks as for creating the previous worksheet. Show more or lessRead more...

    You create a new ADF desktop integration-enabled workbook.

    You open the workbook in Excel.

    You choose a page definition for the worksheet.

    You add a binding to the worksheet.

    You choose a component to add to the worksheet.

  3. Name the workbook EditDept_LOV_src.xlsx and click OK.

    alt text
  4. In the Application Navigator, double-click EditDept_LOV_src.xlsx to open Excel.

    alt text
  5. Excel opens, loading Oracle ADF Desktop Integration. In the Page Definition dialog, select the ADFdi_view_EditTable_LOVPageDef page definition.

    alt text
  6. The ADFdi task pane displays in Excel, presenting the available bindings from the page definition.

  7. The page definition selected in the previous step (ADFdi_view_EditTable_LOVPageDef) contains several different types of bindings, in addition to the tree binding that you used in the previous part of this tutorial when creating the DepartmentsList workbook.Show more or lessRead more...

    Notice the different bindings in the screenshot of the data bindings for the EditTable_LOV.jspx page: the action bindings represent the button actions that the JSF page contains.

  8. When the worksheet displays, select a cell and in the Bindings tab, select DepartmentsView1 and click Insert Binding.

    alt text
  9. In the Select Component dialog, select ADF Table. Click OK.

    alt text
  10. In the Insert Component: ADF Table dialog, click OK to accept default values.

    alt text
  11. The Excel worksheet should look like this:

    alt text

    Notice that the first three columns are automatically generated in the context of an editable table for information purposes.

  12. Click the Save icon to save the workbook.

Step 2: Setting Workbook and Table Properties
  1. Click the Workbook Properties link in the Oracle ADF tab of the Excel ribbon.

    alt text
  2. In the Edit Workbook Properties, next to the WebAppRoot field, enter http://127.0.0.1:7101/ADFdi_BC as before, and click OK. This is the URL for the Fusion Web application with which the workbook is integrated.

    alt text
  3. In the Excel worksheet, select the top left cell of your table (i.e. B2), and click the properties icon in the ADF Components group of the Oracle ADF tab in the Excel ribbon.

    alt text
  4. In the Edit Component: ADF Table dialog, expand the RowActions node and click the more button next to the DeleteRowActionID field.

    alt text
  5. In the Select Binding dialog, select Delete (action) and click OK.

    alt text

  6. The RowActions set of properties defines the behavior of rows in the table in the worksheet. Show more or lessRead more...

    For example selecting Delete in this step defines the action binding that should be invoked for each flagged row during DeleteFlaggedRows. The steps that follow define behaviors for other rows in the worksheet.
  7. Repeat the previous step for the InsertBeforeRowActionID field and choose the CreateInsert (action). Click OK.

    alt text
  8. In the RowActions section, set the following additional values:

    DeleteRowEnabled True
    InsertRowEnabled True
    UpdateRowEnabled True

    The RowActions properties should look as follows:

    alt text
  9. In the BatchOptions section, set the CommitBatchActionID to Commit and click OK.

    alt text
  10. In the Edit Component dialog, click the more button next to the Columns field.

    alt text
  11. Click the Add button to add a new column, then click the more button next to the HeaderLabel field.

    alt text
  12. In the Edit Expression dialog, expand the bindings > DepartmentsView1 > DepartmentsView > hints > LocationId > label field, click the Insert Into Expression button.

    alt text

    Click OK.

  13. You have just created a new column in the worksheet and bound it to the "LocationId" label. Show more or lessRead more... However you already have a data bound LocationId column in the table... The reason for creating the new column is so that you can add list of values functionality to it. You do this in the steps that follow. Then you delete the original LocationId column.
  14. Click the more button next to the UpdateComponent field.

    alt text
  15. In the Select subcomponent to create dialog, select TreeNodeList and click OK.

    alt text
  16. Expand the UpdateComponent node and click the more button next to the List field.

    alt text
  17. In the Select Tree Binding Attribute, select LocationId and click OK.

    alt text
  18. Select column 6 and click the Remove button to remove the original LocationId column and replace it with this new column that supports a list of values.

    alt text
  19. Select column number 3 (DepartmentId) and expand the UpdateComponent node. Change the ReadOnly property to True.

    alt text


  20. The application that you are designing requires that users should not be allowed to change Department Ids. Show more or lessRead more... To implement this requirement you set the ReadOnly property to True (which you have just done in this step) and you also apply a style to the field as a visual indicator to users that they cannot update the DepartmentId field.
  21. With column number 3 (DepartmentId) still selected, click the more button in the CellStyleName field.

    alt text
  22. In the Edit Expression dialog, expand the Styles node.

    alt text
  23. Select _ADFDI_TableCellROStyle (this is the style that visually indicates that a column is read-only) and click Insert Into Expression.

    alt text

    Click OK and OK again to exit the Edit Component dialog.

  24. Save your Excel workbook.

Step 3: Defining a Query Ribbon Command
In this step you add a command item to the Excel ribbon at the top of the worksheet. Show more or lessRead more... In the DepartmentsList worksheet you specified that data should be downloaded to the worksheet at startup. Here you create a ribbon command that a user must click to populate the worksheet table with data.
  1. In the Oracle ADF tab, click the Worksheet Properties button. Locate Ribbon Commands and click the more button.

    alt text
  2. In the WorksheetMenuItem Collection Editor click Add.

    alt text
  3. In the right-hand pane, click the more button next to the Actions field.

    alt text
  4. In the Action Collection Editor dialog, click the Add button and select ADFmAction from the drop down list.

    alt text
  5. In the ADFmAction properties, click the more button next to the ActionID field.

    alt text
  6. In the Select Binding dialog, select Execute and click OK.

    alt text
  7. Back in the Action Collection Editor, in the Design > Annotation field, type Run query .

    alt text
  8. In the Action Collection Editor dialog, click the Add button and select ComponentAction from the drop down list.

    alt text
  9. Click the more button at the right-hand end of the Action field.

    alt text
  10. In the Choose Component Action dialog, select Download and click OK.

    alt text
  11. Then, back in the Action Collection Editor, in the Design > Annotation field, type Download and click OK.

    alt text
  12. Back in the WorksheetMenuItem Collection Editor, type Query in the Label field.

    alt text

    Click OK and OK again.

  13. "Query" is the label for the command item that appears on the Excel ribbon.Show more or lessRead more... When the user clicks the Query command item, data is downloaded to the table in the worksheet.
  14. Save your Excel workbook.

Step 4: Defining a Delete Rows Ribbon Command
    In this step you add another command item to the Excel ribbon at the top of the worksheet.Show more or lessRead more... This time the ribbon command deletes flagged rows in the table and requests confirmation from the user before performing the action.
  1. In the Oracle ADF tab, click the Worksheet Properties button. Locate Ribbon Commands and click the more button.

    alt text
  2. In the WorksheetMenuItem Collection Editor click Add.

    alt text
  3. In the right-hand pane, click the more button next to the Actions field.

    alt text
  4. Click the Add button and from the drop down list select Confirmation.

    alt text
  5. In the Data section, enter the following values:

    CancelButtonLabel Cancel
    OKButtonLabel Yes
    Prompt Are you sure you want to delete these rows?
    Title Delete Confirmation

    alt text
  6. Click the Add button and from the drop down list select ComponentAction.

    alt text
  7. In the ComponentAction properties, click the more button next to the Action field.

    alt text
  8. In the Choose Component Action, select DeleteFlaggedRows.

    alt text
  9. Click OK and OK again.

  10. In the WorksheetMenuItem Collection Editor, type Delete Flagged in the Label field.

    alt text

    Click OK.

Step 5: Defining an Upload Ribbon Command

    In this step you add another command item to the Excel ribbon at the top of the worksheet.Show more or lessRead more... This time the command item uploads data to the table.
  1. In the Oracle ADF tab, click the Worksheet Properties button. Locate Ribbon Commands and click the more button.

    alt text
  2. In the WorksheetMenuItem Collection Editor click Add.

    alt text
  3. In the right-hand pane, click the more button next to the Actions field.

    alt text
  4. In the Action Collection Editor dialog, click the Add button and select ComponentAction from the drop down list.

    alt text
  5. In the ComponentAction properties, click the more button next to the Action field.

    alt text
  6. In the Choose Component Action dialog, select Upload. Click OK.

    alt text

    Click OK again.

  7. In theWorksheetMenuItem Collection Editor, type Upload in the Label field and click OK.

    Click OK again.

  8. Save the workbook.

Step 6: Testing the EditDept_LOV Workbook
  1. Click the Oracle ADF tab in the Excel ribbon.

    alt text
  2. Click the Run button in the Test group.

  3. The workbook loads, displaying the ADF table, not yet populated with data.

    alt text
  4. Click the MyWorkbook tab, displayed in the Excel ribbon to the right of the Oracle ADF tab.

    alt text
  5. Notice the three command items that you have just created, in the Worksheet group.
    Click the Query button.

    alt text
  6. Retrieved data are then displayed in the worksheet.

    alt text
  7. Notice that the DepartmentId column is shaded in a different color from the other columns.Show more or lessRead more... This is the read-only style that you applied to the DepartmentId column as a visual indicator to the user that the column cannot be updated. Visual clues like this can greatly enhance the usability of an application.
  8. At the bottom of the worksheet, after the last row, insert a new row, by right-clicking in an empty row and choosing Insert from the context menu. Type a new department name and select a city from the drop down list.

    alt text
  9. Notice that the new row is automatically flagged in the Changed column.

    alt text
  10. For an existing row, try changing the location from the drop down list.

    alt text
  11. Click the newly-created Upload command item in the Excel ribbon to save the changes.

    alt text
  12. On the Upload Options dialog check the Download all rows after successful upload checkbox.

    alt text
  13. Notice that the new row has been inserted, a Department Id has been generated, and the flags have been removed.

  14. Click in the Flagged column for the row you just inserted.

    alt text
  15. Click the Delete Flagged button.

    alt text
  16. In the Delete Confirmation dialog you created previously, click Yes.

    alt text
  17. The worksheet is updated.

    alt text
  18. Click the Stop button, to return to design mode.

    alt text

    You have created a fully-functioning workbook that is integrated with a Fusion Web application, and which allows you to insert, update and delete data from the database. The next part of this tutorial guides you through publishing your workbooks.


Bookmark Print Expand all | Hide all
Back to top

Did you find this page helpful?



Copyright © 2011, Oracle. All rights reserved.