Place
the cursor over this icon to load and view all the screenshots for this tutorial.
(Caution: This action loads all screenshots simultaneously, so response time
may be slow depending on your Internet connection.)
Note: Alternatively, you can place the cursor over
an individual icon in the following steps to load and view only the screenshot
associated with that step. You can hide an individual screenshot by clicking
it.
Overview
This tutorial guides you through the
use of JDeveloper and ADF Desktop Integration (ADFdi) to develop Excel worksheets that use ADF binding
components.
You start with a predefined JDeveloper
application with the Business Components and Page Definitions bindings that are required
for the Excel workbook. You define security for your application. You open an
ADFdi compatible workbook and use the ADF mapping to create ADFdi components,
including a read-only table. You then specify Download table functionality in
Excel. Next, using another page definition, you define a worksheet with LOV
and Insert/Update functionality.
You run the JDeveloper application to
deploy it in the Weblogic server, then you execute the application designed
in the Excel workbook.
Have access to or have installed Oracle
JDeveloper 11g Production. You can download it from
Oracle Technology
Network. Install it into a directory of your choice, which in this
tutorial is referred to as JDEVELOPER_HOME
2.
Have access to or have installed
Microsoft Office 2007.
3.
Right-click the following link
and select Save Link As... and save the ADFdi_BC.zip
file in a directory of your choice. (i.e. Temp).
4.
Unzip the ADFdi_BC.zip file in a directory
of your choice.
To install the ADFdi component to JDeveloper, perform the
following steps:
1.
Open Windows Explorer and locate the <JDEVELOPER_HOME>
folder.
2.
Expand the <JDEVELOPER_HOME>\jdeveloper\adfdi\bin\excel\client
folder and double-click the setup.exe file.
3.
The Microsoft Office Customization Installer window displays as
the install process starts. The first operation proceeds during pre-installation
checks.
4.
Installing the ADF Desktop Integration starts. Notice that depending
on software already installed, you might be prompted to install other
mandatory prerequisites, and in some cases to reboot your computer.
Upload a Startup Fusion Web Application that includes Model
and ViewController components.
1.
Start JDeveloper by selecting Start
> All Programs > Oracle Fusion Middleware > JDeveloper Studio
11.1.1.1.0
If the Migrate User Settings dialog box opens, click
NO.
If prompted for a User Role, choose Default.
Close the Tip of the Day window. The
JDeveloper IDE should now be displayed.
2.
Click the Open Application link (or Select File
| Open from the main menu).
3.
In the Open dialog, open the folder where you've unzipped the ADFdi_BC.zip
file, and select the ADFdi_BC.jws file.
Click Open. If prompted to migrate the application from
a previous version accept to proceed.
4.
Once loaded up, you should see two projects in the Application Navigator.
The Application Navigator should look like the following:
5.
Right-click the ViewController project and select
Project Properties from context.
6.
In the Project Properties dialog, select the Technology Scope node
and in the Available Project Technologies pane, using
the move button
shuttle the following technology in the Selected pane:
ADF Desktop Integration
Click OK. Your ViewController project is now ready to
support ADF Desktop Integration.
7.
Click Save All
icon on the JDeveloper menu bar, or select File | Save All from the
menu.
Because ADFdi requires using an authentication step to run
an ADFdi workbook, you need to secure the application. To secure the application,
perform the following steps:
1.
In order to be able to run the ADFdi in Excel, your application
needs to be secured. In the main menu select Application | Secure
--> Configure ADF Security.
2.
In the Enable ADF Security dialog, select ADF Authentication.
Click Next.
3.
In the Select Authentication type page, select HTTP Basic Authentication.
Click Next, then Next again.
4.
On the Summary page, click Finish.
5.
Click OK on the Security Infrastructure Created information
window.
6.
The Application Navigator should now look like this:
Notice the weblogic.xml entry in the Application Navigator
holding the security-role-assignment properties.
7.
Click Save All
icon on the JDeveloper menu bar, or select File | Save All from the
menu.
To create the DepartmentsList worksheet, perform the following
steps:
1.
Using Windows Explorer, open the folder holding your application i.e.:
d:\ADFDI\ADFdi_BC\ViewController \public_html and create
a New folder named Excel.
2.
Copy the file named adfdi-client.xlsx located in the
<JDEVELOPER_HOME>\jdeveloper\adfdi\bin\excel\client folder.
Paste it in the Excel folder you just created.
3.
Rename the Book1.xlsx to DepartmentsList.xlsx.
4.
Copy again the Book1.xlsx file in the Excel directory and rename it
EditDept_LOV (we will need it for a later use).
5.
Back in JDeveloper, select the ViewController node
and click the Refresh
icon.
6.
Both files, DepartmentsList.xlsx and EditDept_LOV.xlsx
now appear in the Application Navigator.
In the Page Definition dialog, select the ADFdi_view_DepartmentPagePageDef
page definition.
Click OK.
4.
The Document Actions window appears in Excel, presenting
the available bindings from the page definition.
5.
Select a cell in the workbook, then in the Bindings
tab, select DepartmentsView1 and click Insert
Binding.
6.
In the Select Component dialog, select ADF Read-only Table.
Click OK.
7.
In the Insert Component: ADF Read-only Table dialog, click OK
to accept default values.
8.
The Excel workbook should look like this:
9.
Click the Save icon to save the workbook.
Notice that when saving, you might be warned with the following message:
"Privacy warning: This document contains macros, ActiveX
Controls, XML expansion pack..."
To suppress this warning, invoke Excel Options -> Trust Center
-> Trust Center Settings... -> Privacy Options
and uncheck the check box "Remove Personal
information from file properties on save".
10.
Back into JDeveloper, right click the ViewController project
and select Project Properties from context.
11.
In the Project Properties dialog, select the Java EE Application
node and type ADFdi_BC in both fields: Java
EE Web Application Name and Java EE Web Context Root.
Click OK.
12.
Return to Excel and click the Workbook Properties link
in the Document Actions pane.
13.
In the Edit Workbook Properties, next to the WebAppRoot field, enter
http://127.0.0.1:7101/ADFdi_BC
14.
Next to the WebPagesFolder click the more button .
In the Browse For Folder dialog, select ADFdi_BC | ViewController
| public_html.
Click OK.
15.
Expand the Login section, and click the ProtectedWebPage
field. Click the more button
in the ProtectedWebPage field.
16.
In the WebPagesFolder, click Yes on the warning window.
17.
In the Browse For Folder dialog, select ADFdi_BC | ViewController
| public_html.
Click OK.
18.
In the Select Page dialog, /faces is already specified
as the Page Prefix; set Choose Page to /StarterPage.jspx
Click OK. The StarterPage is just a blank page used
to launch the application.
19.
The Workbook Properties should look like this:
Click OK to close the Edit Workbook Properties dialog.
20.
In the Document Actions pane, click the Worksheet Properties
link.
21.
Next to the Events field, click the more button .
22.
In the WorksheeetEvent Collection Editor dialog, click the Add
button to add a Startup member. Expand the ActionSet
node and click the more button
next to the Actions field.
23.
In the Action Collection Editor dialog, click the Add
button and select ComponentAction from the drop down
list.
24.
Click the more button
next to the Action field.
25.
In the Choose Component Action dialog, select Download.
Click OK.
Notice that the Table ID (ROT813992144) is different in your case.
26.
In the WorksheeetEvent Collection Editor dialog, in the Actions
| Annotation field, type DownLoad_Data.
Click OK. Back in the Edit Worksheet Properties dialog,
click OK.
27.
Click the Add-Ins button to display the ADFdi custom
toolbar.
28.
Since the workbook you've built was based on the Book1.xlsx
converted worksheet, and that each ADFdi worksheet needs a unique identifier,
select the Tools --> Reset WorkbookID option to create
a new identifier.
29.
In the Reset WorkbookID warning dialog, click Yes
to reset the ID.
On the Reset WorkbookID information dialog, click OK.
Back in JDeveloper, right click the StarterPage.jspx node
and select Run from context.
2.
Wait until the Weblogic server has started and opened a browser window.
On the Authentication Required dialog, enter weblogic/weblogic1
for User name and Password fields.
Click OK.
3.
The starter page is then loaded, displaying the Starter Page text.
4.
Now that your page has been deployed in the Weblogic server, you can
run the Excel workbook. Switch back to JDeveloper. Double-click the DepartmentList.xlsx
node to open Microsoft Excel.
5.
The workbook is loaded in the page. Click the Add-Ins
button.
6.
In the Custom Toolbars, click the Run icon.
7.
In the Login warning, click Yes to launch the connection.
8.
In the Connect dialog, enter weblogic/weblogic1 for
User name and Password.
9.
The list of Department is returned in the worksheet.
10.
You may need to rearrange the worksheet cells for a better layout.
11.
Click the Stop button in the ADFdi toolbar.
12.
Close the Excel application without saving. You've
successfully created a basic Excel worksheet that automatically downloads
data from a database table.
In the Page Definition dialog, select the ADFdi_view_EditTable_LOVPageDef
page definition.
Click OK.
4.
The Document Actions window appears in Excel, presenting
the available bindings from the page definition.
5.
Select a cell in the workbook, then in the Bindings
tab, select DepartmentsView1 and click Insert
Binding.
6.
In the Select Component dialog, select ADF Table.
Click OK.
7.
In the Insert Component: ADF Table dialog, click OK
to accept default values.
8.
The Excel workbook should look like this:
Notice that the first three columns are automatically generated in the
context of an editable table for information purposes.
9.
Click the Add-Ins button to display the ADFdi custom
toolbar.
10.
Because this workbook is also based on the Book1.xlsx
converted worksheet, and that each ADFdi worksheet needs a unique identifier,
select the Tools --> Reset WorkbookID option to create
a new identifier.
11.
In the Reset WorkbookID warning dialog, click Yes
to reset the ID.
On the Reset WorkbookID information dialog, click OK.
Notice that the Workbook properties to be defined here for
the EditDept_LOV workbook are the same as the ones previously defined for the
DepartmentsList.xlsx workbook. You could avoid repeating these steps by saving
with another name the DepartmentsList.xlsx file just after having defined the
workbookbook properties.
To define the workbook properties, perform the following steps:
1.
Click the Workbook Properties link in the Document
Actions pane.
2.
In the Edit Workbook Properties, next to the WebAppRoot field,
enter http://127.0.0.1:7101/ADFdi_BC
3.
Next to the WebPagesFolder click the more button .
In the Browse For Folder dialog, select ADFdi_BC | ViewController
| public_html.
Click OK.
4.
Expand the Login section, and click the ProtectedWebPage
field. Click the more button
in the ProtectedWebPage field.
5.
In the WebPagesFolder, click Yes on the warning window.
6.
In the Browse For Folder dialog, select ADFdi_BC | ViewController
| public_html.
Click OK.
7.
In the Select Page dialog, /faces is specified as
the Page Prefix; set Choose Page to /StarterPage.jspx
Click OK.
8.
The Workbook Properties should look like this:
Click OK to close the Edit Workbook Properties dialog.
To define the Table properties, perform the following steps.
1.
In the Excel worksheet, select the most top left cell of your table
(i.e. B2), and click the properties icon
in the Custom Toolbars.
2.
In the Edit Component: ADF Table dialog, next to the Unique
Attribute field, click the more button .
3.
In the Select Tree Binding Attribute dialog, select DepartmentsView1
(tree) | DepartmentsView | DepartmentId.
Click OK.
4.
Expand the RowActions node and click the more button
next to the DeleteRowActionId field.
In the Select Binding dialog, select the Delete action.
Click OK.
5.
Repeat the previous step for the InsertBeforeRowActionId
field and choose the CreateInsert action.
6.
In the RowActions section, set the following values:
DeleteRowEnabled
True
InsertRowEnabled
True
UpdateRowEnabled
True
The RowActions properties should look like the following:
7.
In the BatchOptions section, set the CommitBatchActionID
to Commit.
8.
In the Edit Component dialog, click the more button
next to the Columns
field.
9.
Click the Add button to add a new column, then click
the more button
next to the HeaderLabel field.
10.
In the Edit Expression dialog, expand the bindings | Departments1View1
| Departments1View | hints | LocationId | label field, click
the Insert Into Expression button.
Click OK.
11.
Click the more button
next to the UpdateComponent field.
12.
In the Select subcomponent to create dialog, select TreeNodeList
and click OK.
13.
Expand the UpdateComponent node and click the more
button next to
the List field.
14.
In the Select Tree Binding Attribute, select LocationId
and click OK.
15.
Select column 6 and click the Remove
button to remove the original LocationId column and replace it with this
new column supporting the LOV functionality.
16.
Select column number 3 (DepartmentId) and expand the
UpdateComponent node. Change the ReadOnly
property to True.
Click OK. Click OK to exit the Edit
Component dialog.
Instead of automatically downloading data when running the
worksheet, we are now going to add a button that performs the Query action.
To define the button properties, perform the following steps.
1.
In the Document Actions pane, click the Components
tab, then select a cell in the worksheet (i.e. A1), click the ADF
button component and press Insert Component.
2.
In the Insert Component for the ADF button, expand the ActionOptions
node and next to the Actions field, click the more
button .
3.
In the Action Collection Editor dialog, click the Add
button and select ADFmAction from the drop down list.
4.
Click the more button
next to the ActionId.
5.
In the Select Binding dialog, select Execute.
Click OK.
6.
Click OK, then back in the Action Collection Editor,
in the Design | Annotation field, type Execute.
7.
In the Action Collection Editor dialog, click the Add
button and select ComponentAction from the drop down
list.
8.
Click the more button
next to the Action.
9.
In the Choose Component Action dialog, select Download.
Click OK.
10.
Then back in the Action Collection Editor, in the Design |
Annotation field, type Download.
Click OK.
11.
Back in the Insert Component: ADF Button, type Query
in the Label field.
To define the properties for a Delete Rows button with a validation
dialog, perform the following steps.
1.
Select a cell in the worksheet, where you want to position the button.
(i.e. C1) Click the ADF Button in the
Components pane, then click the Insert Component button.
2.
In the Insert Component, expand ClickActionSet | ActionOptions,
and click the more button
next to the Actions field.
3.
Click the Add button and from the drop down list select
Confirmation
4.
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
5.
Click the Add button and from the drop down list select
ComponentAction
6.
In the ComponentAction properties, click the more
button next to
the Action field.
7.
In the Choose Component Action, select DeleteFlaggedRows.
Click OK. Click OK again.
8.
In the Insert Component: ADF Button, type Delete Flagged in
the Label field.
To define the properties for an Upload button that commits
changes into the database, perform the following steps.
1.
Select a cell in the worksheet, where you want to position the button.
(i.e. E1) Click the ADF Button in the
Components pane, then click the Insert Component button.
2.
In the Insert Component, expand ClickActionSet | ActionOptions,
and click the more button
next to the Actions field.
3.
Click the Add button and from the drop down list select
ComponentAction
4.
In the ComponentAction properties, click the more
button next to
the Action field.
5.
In the Choose Component Action, select Upload.
Click OK. Click OK again.
6.
In the Insert Component: ADF Button, type Upload in
the Label field.
Click OK.
7.
The worksheet should look like the following:
Click OK again.
8.
Click the Save icon to save the workbook. Exit
from Excel.
The same way you created ADF buttons for Query, Delete, and
Upload functionalities you can create Menu items to the worksheet. To create
a menu option for the Query action, perform the following steps:
1.
In the Document Actions pane, click the Worksheet Properties
link.
2.
In the Edit Worksheet Properties, click the more
button next to the MenuItems field.
3.
In the WorksheetMenuItem Collection Editor, click the Add
button to add a WorksheetMenuItem.
4.
Expand the SelectActionSet | ActionOptions nodes and
click the more button
next to the Actions field.
5.
In the Action Collection Editor dialog, click the Add
button and select ADFmAction from the drop down list.
6.
Click the more button
next to the ActionId.
7.
In the Select Binding dialog, select Execute.
Click OK.
8.
Click OK, then back in the Action Collection Editor,
in the Design | Annotation field, type Execute.
9.
In the Action Collection Editor dialog, click the Add
button and select ComponentAction from the drop down
list.
10.
Click the more button
next to the Action.
11.
In the Choose Component Action dialog, select Download.
Click OK.
12.
Then back in the Action Collection Editor, in the Design |
Annotation field, type Download.
Click OK.
13.
Back in the WorlsheetMenuItem Collection Editor, type Query
in the Label field.
Click OK. Then OK again.
14.
Click the Save button, then click the Run
icon.
15.
When prompted for Login, click Yes.
16.
On the Connect dialog, enter the default User name and Password (weblogic/weblogic1).
17.
Click the Oracle ADF 11g Desktop Integration menu
option, then select the Query option.
18.
Data are then displayed the same way as using the Query
button.
You could repeat the same steps for each of the other buttons to create
other menu item entries.
To publish a workbook, perform the following steps.
1.
Back in JDeveloper, double click the DepartmentList.xlsx node
to open it in Excel.
2.
Click the Office Button.
3.
In the Office dialog, click the Excel Options button.
4.
In the Excel Options, click the Trust Center button.
5.
In the Trust Center page, click the Trust Center Settings
button.
6.
In the Trust Center Settings, click the Trusted Locations tab.
7.
Select the Allow Trusted Locations on my network check
box and click the Add New Location button.
8.
In the Microsoft Office Trusted Location dialog, enter the URL of the
WebAppRoot for example: http://localhost:7101/ADFdi_BC.
Include the Subfolders option.
Click OK. Then OK again to exit from
Excel Options.
9.
In the DepartmentList workbook, select the Add-ins
menu option, then click the Publish icon in the ADFdi
toolbar.
10.
The Publish Workbook dialog opens up on the current Excel folder. Click
the create directory icon to create a specific location
for your published workbooks.
11.
Type Deployed_XLS as the name of the folder, then
click the Open button.
12.
Type DepartmentList_dpd as the file name and click
Save.
13.
On the Publish Workbook dialog, click Yes.
14.
Click OK on the Publish Workbook information dialog.
15.
Click the Save icon
to save your worksheet definition.
16.
Exit from Excel. Your workbook is now published and
ready to be deployed with your application to Weblogic server.
You've successfully completed this introduction to ADFdi.
Bookmark
shuttle the following technology in the Selected pane:
icon on the JDeveloper menu bar, or select File | Save All from the
menu.
icon. 
.
In the Browse For Folder dialog, select ADFdi_BC | ViewController
| public_html.
.
to save the workbook. 
in the Custom Toolbars. 
