SOAP to Microsoft® Excel Using J-Integra®®

This example shows you how to use the IBM Web Services toolkit (WSTK) to take advantage of the J-Integra®® Java-COM bridge. In this example we create a Java™ Web service that uses Excel to perform some calculations. Then we create a Java client application that uses SOAP to communicate with our Web service.

If you want to try out this example, you should first download and install the IBM WSTK, J-Integra®, and a JDK. Note also that a large quantity of Java files are automatically generated from Excel in this example; compiling them takes quite a long time, and should probably only be attempted on a machine with more than 32 MB of memory.

This example will require access to a machine that is running Windows NT or Windows 95/98, and has Excel installed on it. This machine will generate the pure Java proxies which can then be used to access Excel from any machine that supports a standard Java Virtual Machine (JVM). This proxy generation step need only be done once.

Before running this example you should successfully run the Calculator example that comes with the IBM WSTK. This will ensure that your environment settings are correct and you have all the necessary files.

The steps involved

  1. Configure your environment

  2. Generate the Java proxies for Excel

  3. Compile the Excel Web Service and the Calculator Web Service client

  4. Deploying the Example in Embedded WebSphere
  5. Running the Calculator Web Service client in the Embedded WebSphere

  6. Deploying and Running the Example in TomCat

  7. Library Files

Configure Your Environment

This example assumes that you have installed

We will create the Excel proxy files in the D:\Pure directory. Create that directory, and create an excel sub-directory under it. Update your PATH environment variable to include the JDK and the J-Integra® bin directories, and update your CLASSPATH environment variable to include the J-Integra® runtime by entering the commands

SET PATH=%PATH%;D:\jdk1.3.1\bin;D:\jintegra\bin;
SET CLASSPATH=.;D:\jdk1.3.1\jre\lib\rt.jar;D:\jdk1.3.1\lib\tools.jar;D:\jintegra\lib\jintegra.jar;

Generate the Java Proxies for Excel

Type libraries contain information about COM components. We will be using the information contained in the Excel type library to generate pure Java proxies which can be used to access Excel from a standard JVM.

  1. Start the com2java tool by entering the command

    com2java

    If you get an error about the command not being found, check that you set your PATH environment variable correctly above.

  2. Click the "..." button next to the Typelib field to select the Excel Type Library, which is located in

    D:\Program Files\Microsoft Office\Office\EXCEL8.OLB

    Note You may have a different version of this COM component on your system.

  3. Enter D:\pure into the Output Dir field
  4. Once you have selected the type library, enter excel in the Java package text box.

  5. Click the button to generate the following the proxy files will be generated.


  6. Take a look at some of the generated files to get a feel for what is being generated.

    You will notice that comments are included in the generated files in order to make them easier to understand. You may wish to use the javadoc documentation generation tool on the files (the comments are compatible with that tool).

  7. It would be much easier if we create a .jar file to put all the generated files into just one file. In order to package them into a .jar file, while you are in D:\Pure sub-directory, issue the command

    jar cvf excel.jar excel/*.class
  8. The resultant file is excel.jar. You need to update your CLASSPATH environment variable to include this .jar file with the command

    SET CLASSPATH=.;D:\jdk1.3.1\jre\lib\rt.jar;D:\jdk1.3.1\lib\tools.jar;D:\jintegra\lib\jintegra.jar;D:\Pure\excel.jar;

Compile the Excel Web Service and the Calculator Web Service Client

In this example, we use a Java class called ExcelCalculatorWebService.java, which performs some calculations (add, subtract, multiply, divide) on the received parameters and returns the result to the requester. To do its job, this class creates an Excel COM control and asks that object to actually perform the operation. We will expose the services provided by this class as Web services, using the SOAP protocol. The client of these Web services is a Java program called Calculator.java (this file is included with the Apache SOAP samples).
  1. Create the file ExcelCalculatorWebService.java, by cutting and pasting the following code from your Web browser. This file is included in your jintegra/examples/SOAP-COM directory when you download J-Integra®.

  2. Here is the file.

    /*
     * Intrinsyc Software Inc.
     *
     * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
     * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
     * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
     * DISCLAIMED.  IN NO EVENT SHALL THE INTRINSYC SOFTWARE INC. OR
     * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
     * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
     * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
     * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
     * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
     * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
     * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
     * SUCH DAMAGE.
     * ====================================================================
     *
     */
    
    package samples.excelwebservice;
    
    import java.io.*;
    import excel.*;
    import javax.servlet.*;
    import javax.servlet.http.*;
    import com.linar.jintegra.AuthInfo;
    import com.linar.jintegra.Cleaner;
    
     /* 
     * ExcelCalculatorWebService is a Java Web Service that performs some calculations
     * on behalf of its client. It exposes four functions (minus, plus, times, and divide).
     * each one of these functions accepts two float parameters as input and return a float as
     * the output. In order to perform its job, ExcelCalculatorWebService first creates a 
     * Microsoft Excel object. Inside this objec it creates a worksheet and sets the
     * value of the first two cell to the input values. Based on the requested operation,
     * it sets the formula of the third cell and then it asks Excel object to calculate the
     * value of the third cell and returns the result to the client.
     */
    public class ExcelCalculatorWebService
    {
      public ExcelCalculatorWebService()	{
      }
      public double minus(double x, double y) {
        return calculate("=A1-B1", x, y);
      }
      public double plus(double x, double y)  {
        return calculate("=A1+B1", x, y);
      }
      public double times(double x, double y) {
        return calculate("=A1*B1", x, y);
      }
      public double divide(double x, double y)  {
        if (0.0 != y)
        {
          return calculate("=A1/B1", x, y);
        }
        return 0.0;
      }
      private double calculate(String action, double x, double y) {
        Double v1 = new Double(x);
        Double v2 = new Double(y);
        double result = 0.0;
        try {
        // May want to consider using setThreadDefault(...)
        // AuthInfo.setDefault("Domain", "username", "password");
    
        // Create an instance of Excel.Application on the local host
        Cleaner.trackObjectsInCurrentThread(); 
        excel.Application app = new excel.Application();
        app.setVisible(true);   // Nice to see what is happening
    
        // Use Excel objects to get at a range in the displayed Worksheet
        excel.Workbooks workbooks = app.getWorkbooks();
        excel.Workbook workbook   = workbooks.add(null);
        excel.Sheets worksheets   = workbook.getWorksheets();
        excel.Worksheet sheet     = new excel.Worksheet(worksheets.add(null, null, null, null));
        excel.Range range         = sheet.getRange("A1:B1", null);
    
        Object[] values = {v1, v2 };
        range.setValue(values); //Use setValue2 for Excel XP/2003
        range   = sheet.getRange("C1", null);
        range.setValue(action); //Use setValue2 for Excel XP/2003
        Object res = range.getValue(); //Use getValue2 for Excel XP/2003
        if ((null != res) && (res instanceof Double)) {
          result = ((Double)res).doubleValue();
        }
        // Release the objects -- Garbage collection would also automatically release them eventually
        workbook.close(new Boolean(false), null, null); // False == don't prompt to save changes
        app.quit();
        //Removes the COM object
        Cleaner.releaseAllInCurrentThread(); 
    
      } catch(Exception e)  {
    		System.out.println("ExcelCalculatorWebService.calculate - ex: " + ex.toString());
      }
        return result;
      }
    }
    

  3. If you are running under Windows 98 or Windows 95, then you will need to modify the example slightly. Replace the initial section with the following code. We start up a Worksheet rather than the Application. (We get the Application property using late binding since a Worksheet started this way only seems to support IDispatch):

  4. Worksheet tmpSheet = new Worksheet();
    Application app = new Application(tmpSheet.getPropertyByName("Application"));
    
    // Use Excel objects to get at a range in the displayed Worksheet
    ...

  5. In the D:\Pure directory, create a directory called samples, and under it create a sub-directory called excelwebservice.

  6. Copy ExcelCalculatorWebService.java into the D:\Pure\samples\excelwebservice directory.

  7. To set the environment variables used by WSTK, go to the WSTK bin directory and enter the command

    wstkenv 
  8. We must set the CLASSPATH and PATH environment variables in order to compile ExcelCalculatorWebService. We need to run javac to compile the file. This program is in the bin directory of your JDK. Go to the D:\Pure directory and enter the following command to set the PATH

    set PATH=%PATH%;D:\jdk1.3.1\bin; 

  9. By running wstkenv, WSTK creates an environment variable called WSTK_CP that includes the path to all library files related to deploying a Web service. We must add this variable to our CLASSPATH. We must also add the path to jintegra.jar and excel.jar to the CLASSPATH. Enter the following command to set the CLASSPATH
set CLASSPATH=%CLASSPATH%;%WSTK_CP%;D:\jintegra\lib\jintegra.jar;D:\Pure\excel.jar;
  1. Now enter the command
    javac samples/excelwebservice/ExcelCalculatorWebService.java

    Note If you get a message from the Java compiler about not finding some of the files related to J-integera, Excel, or WSTK, make sure that jintegra.jar and excel.jar and WSTK_CP are in your CLASSPATH.

  2. Although our example is just one file, it is easier if we put it in a .jar file and add it to our CLASSPATH. While you are in D:\Pure, enter the command

    jar cvf excelwebservice.jar samples/excelwebservice/*.class
  3. Create the file Calculator.java by cutting and pasting the following code from your Web browser. This file is included in your jintegra/examples/SOAP-COM directory when you download J-Integra®.

  4. Here is the file.

    /* 
     * The Apache Software License, Version 1.1 
     * 
     * 
     * Copyright (c) 2000-2003 The Apache Software Foundation.  All rights  
     * reserved. 
     * 
     * Redistribution and use in source and binary forms, with or without 
     * modification, are permitted provided that the following conditions 
     * are met: 
     * 
     * 1. Redistributions of source code must retain the above copyright 
     *    notice, this list of conditions and the following disclaimer.  
     * 
     * 2. Redistributions in binary form must reproduce the above copyright 
     *    notice, this list of conditions and the following disclaimer in 
     *    the documentation and/or other materials provided with the 
     *    distribution. 
     * 
     * 3. The end-user documentation included with the redistribution, 
     *    if any, must include the following acknowledgment:   
     *       "This product includes software developed by the 
     *        Apache Software Foundation (http://www.apache.org/)." 
     *    Alternately, this acknowledgment may appear in the software itself, 
     *    if and wherever such third-party acknowledgments normally appear. 
     * 
     * 4. The names "SOAP" and "Apache Software Foundation" must 
     *    not be used to endorse or promote products derived from this 
     *    software without prior written permission. For written  
     *    permission, please contact apache@apache.org. 
     * 
     * 5. Products derived from this software may not be called "Apache", 
     *    nor may "Apache" appear in their name, without prior written 
     *    permission of the Apache Software Foundation. 
     * 
     * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED 
     * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES 
     * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE 
     * DISCLAIMED.  IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR 
     * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, 
     * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT 
     * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF 
     * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND 
     * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, 
     * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT 
     * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF 
     * SUCH DAMAGE. 
     * ==================================================================== 
     * 
     * This software consists of voluntary contributions made by many 
     * individuals on behalf of the Apache Software Foundation and was 
     * originally based on software copyright (c) 2000, International 
     * Business Machines, Inc., http://www.apache.org.  For more 
     * information on the Apache Software Foundation, please see 
     * <http://www.apache.org/>. 
     */ 
     
    package samples.excelwebservice; 
     
    import java.io.*; 
    import java.net.*; 
    import java.util.*; 
    import java.awt.*; 
    import java.awt.event.*; 
     
    import org.apache.soap.util.xml.*; 
    import org.apache.soap.*; 
    import org.apache.soap.rpc.*; 
     
    /** 
     * This silly little calculator talks to a SOAP calculator service to 
     * do the real operations.  
     */ 
    public class Calculator extends Frame { 
      String encodingStyleURI; 
      URL url; 
      TextField ef = new TextField(); 
      int state = 0; // 0 => got 0 args, 1 => got 1 arg 
      double arg1 = Double.NaN; 
      boolean dotpressed = false; 
      String lastop; 
      Label status; 
      boolean freshstart = true; 
     
      public Calculator(String title) { 
        super(title); 
     
        addWindowListener(new WindowAdapter() { 
          public void windowClosing(WindowEvent e) { 
            System.exit(0); 
          } 
        }); 
     
        ef.setEditable(false); 
        add("North", ef); 
     
        Panel p = new Panel(); 
        p.setLayout(new GridLayout(-1, 4, 5, 5)); 
        String bs[] = {"7", "8", "9", "/",  
          "4", "5", "6", "*", 
          "1", "2", "3", "-", 
          "0", "+/-", ".", "+"}; 
        for (int i = 0; i < bs.length; i++) { 
          Button b = new Button(" " + bs[i] + " "); 
          ActionListener al = null; 
          if ((i != 3) && (i != 7) && (i != 11) && (i != 13) && (i != 14) && 
            (i != 15)) { 
            // digit pressed 
            b.setActionCommand(bs[i]); 
            al = new ActionListener() { 
              public void actionPerformed(ActionEvent e) { 
                if (freshstart) { 
                  ef.setText(""); 
                  freshstart = false; 
                } 
                Button bb = (Button) e.getSource(); 
                ef.setText(ef.getText() + bb.getActionCommand()); 
                status.setText(""); 
              } 
            }; 
          } else if (i == 14) { 
            // '.' pressed 
            al = new ActionListener() { 
              public void actionPerformed(ActionEvent e) { 
                if (freshstart) { 
                  ef.setText(""); 
                  freshstart = false; 
                } 
                status.setText(""); 
                if (dotpressed) { 
                  return; 
                } else { 
                  dotpressed = true; 
                  ef.setText(ef.getText() + '.'); 
                } 
              } 
            }; 
          } else if (i == 13) { 
            // +/- pressed 
            al = new ActionListener() { 
              public void actionPerformed(ActionEvent e) { 
                if (freshstart) { 
                  ef.setText(""); 
                  freshstart = false; 
                } 
                String t = ef.getText(); 
                if (t.charAt(0) == '-') { 
                  ef.setText(t.substring(1)); 
                } else { 
                  ef.setText('-' + t); 
                } 
                status.setText(""); 
              } 
            }; 
          } else { 
            // operation 
            String ac = null; 
            if (bs[i].equals("/")) { 
              ac = "divide"; 
            } else if (bs[i].equals("*")) { 
              ac = "times"; 
            } else if (bs[i].equals("-")) { 
              ac = "minus"; 
            } else { 
              ac = "plus"; 
            } 
            b.setActionCommand(ac); 
            al = new ActionListener() { 
              public void actionPerformed(ActionEvent e) { 
                Button bb = (Button) e.getSource(); 
                double arg = stringToDouble(ef.getText()); 
                if (state == 0) { 
                  arg1 = arg; 
                  lastop = bb.getActionCommand(); 
                  freshstart = true; 
                  state = 1; 
                } else { 
                  try { 
                    status.setText("Working .."); 
                    arg1 = doOp(lastop, arg1, arg); 
                    lastop = bb.getActionCommand(); 
                    ef.setText("" + arg1); 
                    freshstart = true; 
                    status.setText(""); 
                  } catch (SOAPException e2) { 
                    status.setText("Ouch, excepted: " + e2.getMessage()); 
                    e2.printStackTrace(); 
                  } 
                } 
              } 
            }; 
          } 
          b.addActionListener(al); 
          p.add(b); 
        } 
     
        add("Center", p); 
        add("South", status = new Label("Ready ..")); 
     
        pack(); 
        show(); 
      } 
     
      private double doOp(String op, double arg1, double arg2) 
        throws SOAPException { 
        // Build the call. 
        Call call = new Call(); 
        call.setTargetObjectURI("urn:excelcalculatorwebservice"); 
        call.setMethodName(op); 
        call.setEncodingStyleURI(encodingStyleURI); 
        Vector params = new Vector(); 
        params.addElement(new Parameter("arg1", double.class, 
                                        new Double(arg1), null)); 
        params.addElement(new Parameter("arg2", double.class, 
                                        new Double(arg2), null)); 
        call.setParams(params); 
         
        // make the call: note that the action URI is empty because the  
        // XML-SOAP rpc router does not need this. This may change in the 
        // future. 
        Response resp = call.invoke(/* router URL */ url, /* actionURI */ ""); 
         
        // Check the response. 
        if (resp.generatedFault()) { 
          Fault fault = resp.getFault(); 
          System.out.println("Ouch, the call failed: "); 
          System.out.println("  Fault Code   = " + fault.getFaultCode()); 
          System.out.println("  Fault String = " + fault.getFaultString()); 
          return Double.NaN; 
        } else { 
          Parameter result = resp.getReturnValue(); 
          return ((Double) result.getValue()).doubleValue(); 
        } 
      } 
     
      private double stringToDouble(String s) { 
        // try as a double, float or by appending a ".0" to it 
        try { 
          return Double.valueOf(s).doubleValue(); 
        } catch (NumberFormatException e1) { 
          try { 
            return Float.valueOf(s).floatValue() * 1.0; 
          } catch (NumberFormatException e2) { 
            if (s.indexOf(".") == -1) { 
              return stringToDouble(s + '.' + '0'); 
            } else { 
              return Double.NaN; 
            } 
          } 
        } 
      } 
     
      public static void main(String[] args) throws Exception { 
        int maxargs = 2; 
        if (args.length != (maxargs - 1) 
          && (args.length != maxargs || !args[0].startsWith("-"))) { 
          System.err.println("Usage: java " + Calculator.class.getName() + 
                             " [-encodingStyleURI] SOAP-router-URL"); 
          System.exit(1); 
        } 
     
        Calculator c = new Calculator("XML-SOAP Excel Calculator"); 
     
        int offset = maxargs - args.length; 
        c.encodingStyleURI = (args.length == maxargs) 
          ? args[0].substring(1) 
          : Constants.NS_URI_SOAP_ENC; 
        c.url = new URL(args[1 - offset]); 
      } 
    }

    Note This code is the same example that is included in Apache SOAP samples—we have only changed the package name and the URN of the target Web service.

  5. Copy Calculator.java into D:\Pure\samples\excelwebservice.

  6. While you are in D:\Pure, enter the command

    javac samples/excelwebservice/Calculator.java

    Note You must setup the PATH, and CLASSPATH environment variables before running this command. Click here to learn how to set these variables.

  7. Now proceed to deployment:

    Deploying the Example in Embedded WebSphere

    Deploying and Running the Example in TomCat