# Excel Client Sample

1.0 Introduction
2.0 Software Requirements
3.0 Directory Structure
4.0 Configuration
5.0 Run Sample

## 1.0 Introduction

This sample implements a real-world interoperability scenario between Metro and .NET 3.0 using a subset of Health Care System. The sample shows how an Excel 2007 spreadsheet can invoke a Secure and Reliable Metro endpoint. A complete description of the scenario is available here.

The source code for this sample is available in "`wsit/wsit/samples/excelclient`" directory. It can be checked out using the command:

`cvs -d :pserver:yourid@cvs.dev.java.net:/cvs co wsit/wsit/samples/excelclient`

## 2.0 Software Requirements

### 2.1 Platforms

This sample is tested on Windows Vista and Windows XP + SP2 by installing the Metro endpoint and Excel 2007 client on the same machine. However the Metro endpoint can be installed on any of the GlassFish supported platforms (Solaris Sparc, Solaris X86, Windows, Linux and Mac OS). The .NET service reference needs to be regenerated in that case and the instructions for the same are given below.

### 2.2 Java

1. JDK 1.5.0 U12 or later
2. NetBeans 5.5.1
3. GlassFish v2

### 2.3 .NET

Install the software in the exact order listed below.

1. Windows Vista Ultimate or Windows XP + SP2
2. Microsoft Office 2007 Professional
3. Only on Windows XP
   1. .NET Framework 3.0. Note: .NET 3.0 is built into Windows Vista so no need for separate install.
4. Microsoft Visual Studio 2005 Professional Edition
5. Only on Windows Vista
   1. Microsoft Visual Studio 2005 Professional Edition SP1
   2. Microsoft Visual Studio 2005 Professional Edition SP1 Update
6. Microsoft Visual Studio Tools for Office 2007 Second Edition
7. Visual Studio 2005 Extensions for .NET 3.0 Framework (WCF & WPF), Nov 2006 CTP

## 3.0 Directory Structure

- service: Metro endpoint
- ExcelAddIn: Excel 2007 AddIn as Visual Studio Solution
- ExcelClient.xlsm: Excel spreadsheet
- etc/certs: Certificates for .NET client
- sts: Will be used in future to add STS functionality

## 4.0 Configuration

### 4.1 Metro Endpoint

1. Set JAVA_HOME to the installation directory of JDK.
2. Install the latest GlassFish v2 promoted build (tested with RC4), say in AS_HOME.
3. Download and run copyv3 script to update the key and trust stores in Glassfish
   1. Set AS_HOME to the GlassFish installation directory.
   2. Invoke "`ant`" in the expanded "`copyv3`" directory.
4. In NetBeans IDE, add GlassFish as a "`Server`" in the "`Runtime`" tab.
5. In NetBeans IDE, select "`File`" menu, "`Open Project`" and in the "`Open Project Dialog`" navigate to the "`wsit/wsit/samples/excelclient`" directory, select the "`service`" folder and click
   "`Open Project Folder`" button.
   1. Select the Project, Right-click and select "`Properties`".
   2. Click on "`Run`" node and ensure that the recently added GlassFish is chosen as the Server.
6. Update key and trust store locations
   1. In NetBeans IDE, expand "Web Services" node, right-click on "`WSITEndpoint`" node, select "`Edit Web Service Attributes`".
   2. Click on "Keystore..." button and change the value of "`Location`" to the location of "`keystore.jks`" file in your GlassFish installation directory. Click on "`OK`" button.
   3. Click on "Truststore..." button and change the value of "`Location`" to the location of "`truststore.jks`" file in your GlassFish installation directory. Click on "`OK`" button.
7. Start GlassFish by going to the `Runtime` tab, select GlassFish server instance, right-click and then selecting "`Start`". It's important to start GlassFish this way otherwise database instance needs to be explicitly started using `AS_HOME/bin/asadmin start-database`.
8. Add a new user to be used for the sample by giving the following command
   `AS_HOME/bin/asadmin create-file-user wsit` (use "wsit" as a password)
9. Configure the Database
   1. Login to GlassFish admin console at: http://localhost:4848 (4848 is the default port).
   2. In the admin console, create a database connection pool
      1. In the admin console, expand "Resources", "JDBC".
      2. Select "Connection Pools" and click "New".
      3. Enter the values as
         1. Name="wsitPool"
         2. Resource Type="javax.sql.DataSource"
         3. Database Vendor="JavaDB"
      4. Click "Next"
      5. Scroll to the bottom, enter the following values in Additional properties
         1. ConnectionAttributes=";create=true"
         2. DatabaseName="WsitDemoDB"
         3. User="WSIT"
         4. Password="WSIT"
      6. Click "Finish"
   3. Create JDBC resource
      1. Select "JDBC Resources" (right above "Connection Pool")
      2. Click "New"
      3. Enter the following values
         1. JNDI Name="jdbc/wsitSample"
         2. PoolName="wsitPool"
      4. Click "OK"
      5. Click on "Save" button.
10. Deploy the project
    1. In NetBeans IDE, select the Project, right-click and select "Deploy Project".
11. Initialize the Database
    1. In the Runtime tab of NetBeans IDE, right-click on Databases and select "New Connection ...".
    2. Expand Databases, create a new connection using "`Java DB (Network)`" for "`Name`" and "`jdbc:derby://localhost:1527/WsitDemoDB`" for "`Database URL`".
    3. Enter "WSIT" as the value of "User Name" and "Password".
    4. Select "OK" to accept the changes.
    5. Right-select on the newly created connection and select "Execute Command ...". Initialize the tables by giving the following query:
       insert into "MAXID" values ('PATIENTID',1);
       insert into "MAXID" values ('DIAGID',1);
12. Verify the deployment
    1. The endpoint is hosted at http://localhost:8080/service/WSITEndpointService?wsdl. Clicking on the endpoint URL should display the WSDL in your browser.
    2. Running the project from NetBeans IDE shows the following welcome message in the browser window:
       Welcome!
       This web application hosts Metro and .NET interoperability demo. If you are using the default host and port numbers of GlassFish then the endpoint WSDL can be viewed here.

### 4.2 .NET client

1. Start Visual Studio 2005 Professional Edition.
2. Open the Solution in `ExcelAddIn` directory.
3. If the Metro endpoint is hosted on a machine different than localhost then you may need to regenerate the Service Reference. This can be achieved using the following steps:
   1. In the Visual Studio, delete "`app.config`" and "`localhost.map`" files from the "`ExcelAddIn1`" solution in the "`Solution Explorer`".
   2. Right select on "`ExcelAddIn1`" and choose "`Add Service Reference...`".
   3. Enter the URL of the WSDL in "`Service URI`" and take the default value of "`Service reference name`".
   4. Click on "`OK`".
4. If you regenerate the Service Reference then add the following fragment in the generated `app.config` at `//configuration/system.serviceModel/client/endpoint`:
   `<identity>
   <dns value="xwssecurityserver"/>
   </identity>`

   The updated fragment will look like:

   `<client>
   <endpoint address="http://localhost:8080/service/WSITEndpointService"
   binding="customBinding" bindingConfiguration="WSITEndpointPortBinding"
   contract="localhost.WSITEndpoint" name="WSITEndpointPort" >
   <identity>
   <dns value="xwssecurityserver"/>
   </identity>
   </endpoint>
   </client>`

5. Click on "Build", "Build Solution".
6. Register Certificates
   1. Export wssip cert out from GlassFish Trust store by invoking the following command:
      `cd %AS_HOME%\domains\domain1\config
      keytool -export -alias wssip -file wssip.cer -keystore cacerts.jks`
      Enter "`changeit`" on the password prompt. If you get an error "`Alias <wssip> does not exist`" then you probably did not do step #3 in section 4.1.
   2. Execute "`mmc.exe`" from a command shell with administrator privileges.. This will open Microsoft Management Console.
   3. Go to "`File`", "`Add/Remove Snap-in...`".
   4. Select the "`Certificates`" snap-in and click "`Add >`" button.
   5. Select "`Computer Account`", "`Local Computer`". Click "`Finish`" and then "`OK`".
   6. In the left pane, expand "`Certificates (Local Computer)`" and select "`Trusted Root Certification Authorities`" node. Right-click and select "`All Tasks`", "`Import ...`".
   7. Click on "`Next`", "`Browse`", and import the certificates mentioned below (all certificates are in "`wsit\wsit\samples\excelclient\etc\certs`" directory). You may have to change the filter in bottom right corner to "`Personal Information Exchange (*.pfx, *.p12)`" to view `.pfx` certificates.
      1. `ca.pfx (`password is "`password`")
      2. `root.pfx (`password is "`password`")
      3. `xwss-server.cer`
      4. `wssip.cer` exported from GlassFish
7. Grant full trust permissions to Excel add-in
   1. Open Windows SDK command prompt
   2. Give the following command:
      `caspol -u -ag All_Code -url C:\<FolderName>\* FullTrust -n "<Name>" -d "<Description>"
      `
      where `<FolderName>` is complete path to `ExcelAddIn` folder,
      `<Name>` is "`WSITEndpoint`",
      `<Description>` is "`JavaOne 2007 WSIT Demo`".
      An example usage is:
      `C:\Program Files\Microsoft SDKs\Windows\v6.0>caspol -u -ag All_Code -url "C:\workspaces\wsit\wsit\samples\ExcelAddIn\*" FullTrust -n "WSITEndpoint" -d "JavaOne 2007 WSIT Demo"
      Microsoft (R) .NET Framework CasPol 2.0.50727.312
      Copyright (c) Microsoft Corporation. All rights reserved.
      The operation you are performing will alter security policy.
      Are you sure you want to perform this operation? (yes/no)
      y
      Added union code group with "-url" membership condition to the User level.
      Success`

## 5.0 Run Sample

1. Open Excel spreadsheet by double click on the file "ExcelClient.xlsm". If Macros are disabled then a security warning is displayed at the top of spreadsheet. Click on "Options ..." and select "Enable this content ...". You MUST see a window stating "WSIT Endpoint add-in activated successfully". If not, then one of the .NET components is not correctly installed. If it displays correctly, click on OK to dismiss this window.
2. Enter data in "Firstname, "Last name", "Date of Birth" and "SSN" and click on "Save".
3. A window will pop up asking for username and password. `wsit`/`wsit` is the correct username/password. Enter an incorrect username/password and then another window will pop up showing the error message. Click "Retry" and then enter correct username/password.
4. The output window in NetBeans IDE shows the message exchange flow between the Excel client and the Metro endpoint.

Last Updated: August 30, 2007 04:06 PM