> Client Application Developer’s Guide > Using Excel to Access Data Services

Client Application Developer’s Guide

     Previous  Next    Open TOC in new window    View as PDF - New Window  Get Adobe Reader - New Window
Content starts here

Using Excel to Access Data Services

This chapter introduces the AquaLogic Data Services Platform™ Excel Add-in, and describes how to install and use the add-in to access data service operations.

The chapter covers the following topics:

 

Introducing the Excel Add-in

You can use the AquaLogic Data Services Platform Excel Add-in to invoke data service operations from Microsoft® Excel®. This has multiple uses, including the following:

 

Installing the Excel Add-in

This section describes the ALDSP Excel Add-in system requirements and how to install the add-in.

System Requirements

The ALDSP Excel Add-in adds the following system requirements:

Installation Instructions

The Data Services Platform Excel Add-in is provided with Data Services Platform as a separate installation program. The Excel Add-in installs on your local machine and is accessible from Excel as a menu item.

Preparing To Install

Before installing the Excel Add-in, uninstall any previous versions of the add-in by clicking Start Arrow symbol All Programs Arrow symbol BEA AquaLogic Data Services Platform Excel Add-In 3.0 Arrow symbol Unload Add-in from Excel.

Installing the Add-in

This section describes how to install the AquaLogic Data Services Platform Excel Add-in.

Note: Microsoft .NET with SP1 is required for the ALDSP Excel Add-in installation. If Microsoft .NET Framework is not installed on your system, or you have .NET 1.1 or earlier without SP1, the Excel Add-in prompts you to confirm its installation from the Microsoft Web site. Once the correct .NET install is complete, the system proceeds with the ALDSP Excel Add-in installation. (The installation of .NET could take up to 10 minutes for download and configuration.)

To install the Excel Add-in, complete the following steps:

  1. Launch the Excel plug-in installation program by double-clicking the following file:
    2. aldsp_excel_addin_300_win32.exe

    You can find this file in the following directory: 

    <bea_home>/aldsp_3.0/excel-addin

    where <bea_home> is the location of the BEA installation on your system. The installation program displays the welcome screen shown in Figure 6-1.

    Figure 6-1 Excel Add-in Installation


    Excel Add-in Installation

  2. Click Next to continue.
  3. If you do not already have Microsoft .NET Framework 1.1 installed on your system, you will need to install it. You can do this using the Excel Add-in installation dialog.
  4. Determine the user of the application. (Typically anyone with access to your system would be able to use the Add-in.)
  5. Determine the location of the Add-in.

    6. By default the Add-in is installed in the following directory: 

    C:\Program Files\BEA\AquaLogic Data Services Platform Excel Add-in
  6. Complete the installation, and optionally launch Excel.

    7. The ALDSP Excel Add-in adds a new item, My Data, to the Excel main menu.

Accessing Excel Add-in Documentation

After you have completed the installation, you can reference the Excel Add-in documentation, available in the following document: 

AquaLogic Data Excel User Guide.pdf

This file is, by default, located in the following directory: 

C:\Program Files\BEA\AquaLogic Data Services Platform Excel Add-in\Documentation

The documentation includes the following topics:

Tip: Information on using the Add-in is also available from the Excel MyData menu option (see Figure 6-2).

 

Preparing to Use the Excel Add-in

Before accessing data service operations using Excel, you must determine the WSDL URL for the web service.

To prepare to use the Excel Add-in, complete the following steps:

  1. Create a Web Service Map for the dataspace you want to access using Excel, if one does not already exist.

    2. To create a Web Service Map, do the following:

    1. Launch the ALDSP Data Services Studio.
    2. Right-click the dataspace in the Project Explorer and choose New Arrow symbol Web Service Map.
    3. Type a file name for the map and click Finish.
    4. Drag-and-drop the services you want to include in the newly-created map.
  2. Set the listen address of the WebLogic server to the host name or IP address of the machine hosting the web services.

    3. The Excel Add-in requires that the host or IP address specified in /definitions/service/
    port/soap:address/@location     in the WSDL file match the host name or IP address in the WSDL URL.

    To set the listen address of the server, do the following:

    1. Using ALDSP Data Services Studio, double-click the server and click the Open WebLogic Server Admin Console link.
    2. Log in to the WebLogic Admin Console and click Servers in the Environment section.
    3. Click the server in the list.
    4. Click the Lock & Edit button.
    5. Type the host name or IP address of the machine hosting the web services in the Listen Address field.
    6. Click Save.
    7. Click the Activate Changes button.
    8. Restart the server.
  3. Using ALDSP Data Services Studio, right-click the Web Service Map in the Project Explorer and choose Copy WSDL URL.

    4. You can paste the WSDL URL into the Web Services Setup dialog, as explained in the section Accessing the Data Service Using Excel.

Alternatively, you can manually set the host name or IP address of the server in the WSDL URL when specifying the value in Microsoft Excel.

To determine the host name or IP address of the server, do the following:

  1. Using ALDSP Data Services Studio, right-click the Web Service Map for the dataspace in the Project Explorer and choose View WSDL. The application displays the WSDL file.
  2. Right-click in the WSDL file and choose Properties. A dialog appears showing the WSDL URL, among other information.

    3. Use the host name or IP address that appears in the WSDL URL when specifying the value in Microsoft Excel

 

Accessing the Data Service Using Excel

This section describes how to access a data service operation using Excel.

Note: This assumes that you have installed the Excel Add-in on a local copy of Excel. For more information about installing the Excel Add-in, see Installing the Excel Add-in.

To access a data service using Excel, complete the following steps:

  1. Launch Microsoft Excel and choose My Data Arrow symbolWeb Services Setup from the menu, as shown in Figure 6-2.
    2. Figure 6-2 Setting Up a Web Service for the Excel Add-in


    Setting Up a Web Service for the Excel Add-in

  2. Click New in the Web Service Definitions dialog.
  3. Specify the WSDL information by completing the following:
    1. Type a name (alias) for the WSDL in the top left field.

      2. The example in Figure 6-3 uses retailWS as the name.

    2. Paste the WSDL URL in the top right field.

      3. This is the WSDL URL that you copied by right-clicking Web Service Map in the ALDSP Data Services Studio Project Explorer and choosing Copy WSDL URL, as described in Preparing to Use the Excel Add-in.

    3. Click OK.
      4. Figure 6-3 Entering a WSDL Location and an Alias Name for the New Web Service


      Entering a WSDL Location and an Alias Name for the New Web Service

  4. Double-click the alias name of the new service to open the operation editor, as shown in Figure 6-4.
    5. Figure 6-4 Web Service Operation Editor in Excel Add-in


    Web Service Operation Editor in Excel Add-in

  5. Choose the operation using the Operation drop-down menu.

    6. In the example shown in Figure 6-4, the parameterized getCustomerByCustID operation (which is also the name of the underlying data service function) is selected.

  6. Click the Set Input tab.
  7. Expand the operation by clicking the + symbol to the left of the entry.
  8. Select the appropriate input parameter.

    9. In the example shown in Figure 6-5, the custID input parameter is selected.

    Figure 6-5 Selecting an Input Parameter


    Selecting an Input Parameter

  9. Drag the input parameter icon to the spreadsheet. A label and input field appears in the spreadsheet. If you mouse over the input field, the full parameter path is displayed.
  10. Move the cursor to another field in the spreadsheet.
  11. In the Excel Add-in Web service dialog, click the Set Output tab.
  12. Expand the layers of the operation by clicking the + symbol to display the data elements that the operation can retrieve, as shown in Figure 6-6.
    13. Figure 6-6 Selecting a Field for Display in Excel


    Selecting a Field for Display in Excel

  13. Drag the required output elements to the spreadsheet.
  14. In the spreadsheet, type a valid value in the input cell.

    15. For example, you could type CUSTOMER2 as the customer ID value.

  15. Press Enter.
  16. Choose My Data Arrow symbol Refresh Web Service Data Arrow symbol <web_service> from the menu (or right-click on any field in the spreadsheet to access the option).
  17. View and optionally reformat or rearrange the resulting information

    18. Figure 6-7 shows an example of formatted results.

    Figure 6-7 Formatted Results


    Formatted Results


  Back to Top       Previous  Next