How do I connect Java with MS Excel? Try Webservices with SOAP!

· MS Excel, Programming
Authors

It is possible to connect Java and MS Excel using various technologies. Some of theses technologies include COM and DCOM wrappers like Groovy Scriptom,  or J-Interop. Or other direct addin solutions like XLLoop. In this article, we want to follow a different paths: Webservices with SOAP. This connection type is designed for the internet with HTTP requests and wide support in Java and the .net world. This way, we can write an Excel Addin in C# and call Java objects from there.

If you want to create a webservice in Java, it it pretty simple. First, we construct the object, which we want to access from C#. Note, that the access from C# will work using the Windows Communication Foundation (WCF), which wraps the SOAP access and make the whole setup easy.

The following example is a Java class, which we want to access from C#:

package com.thetaris.MyWebService;
import java.util.HashMap;
import javax.jws.WebService;
import javax.jws.soap.SOAPBinding;
import javax.jws.soap.SOAPBinding.Style;

@WebService
@SOAPBinding(style=Style.RPC)

public class Calculator
{
  public long addValues(int val1, int val2)
  {
    return val1 + val2;
  }

  public double[] getList(int numberElements)
  {
    double[] res = new double[numberElements];
    for (int i = 0; i
    {
      res[i] = i*1.2;
    }
    return res;
  }
}

Then, we need a Java server, which can publish an object of the class above:

package com.thetaris.MyWebService;
import javax.xml.ws.Endpoint;

public class MyWebService {
 public static void main (String args[]) {
 Calculator server = new Calculator();
 Endpoint endpoint =
 Endpoint.publish("http://localhost:8080/calculator", server);
 }
}

After starting the Java server, you can test the function using your browser at
http://localhost:8080/calculator”
and you should be able to see the Webservice and a link to the Webservice Description WSDL.
Now, we can access this Webservice from C#. First, we need to create a new project in Visual Studio. Within this project, we run the SvcUtil command in order to generate the C# Stubs required. This can look like:


"c:\Program Files\Microsoft SDKs\Windows\v7.0A\bin\SvcUtil.exe" /language:cs /out:generatedProxy.cs /config:app.config http://localhost:8080/calculator?wsdl

This creates the files “generatedProxy.cs” and “app.config”, which you have to add to your Visual Studio project. Furthermore, add also a reference to “System.ServiceModel” to your Visual Studio project. Then, the following code will access the Webservice:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ServiceModel;

namespace WebServiceClientCSharp
{
  class Program
  {

    static void Main(string[] args)
    {
      CalculatorClient client = new CalculatorClient();
      Console.WriteLine("Result of 3 + 7 is " + client.addValues(3, 7));

      for (int j = 0; j < 100; j++)
      {
        double?[] res = client.getList(10);
        for (int i = 0; i < res.Length; i++)
        {
          Console.WriteLine("Element is " + res[i]);
        }
      }

      Console.WriteLine("Done.");
      Console.ReadLine();
    }
  }
}

Now, we can use NetOffice, VSTO (as Excel macro) or Excel DNA (as Excel UDF) for integrating C# and Excel. Then, we are done. We see that this is actually very simple. Different data types are possible, too. Some data type mappings are given here.

Update 2011-12-14: Instead of calling the “SvcUtil.exe” for generating the C# stubs, you can also use

"c:\Program Files\Microsoft SDKs\Windows\v7.0A\bin\wsdl.exe" /language:cs
/out:generatedProxy.cs http://localhost:8080/calculator?wsdl
which also creates the file “generatedProxy.cs” but eliminates the need for “app.config” and seems to be more compatible with the proposed Java SOAP server.

 

7 Comments

Comments RSS
    • Denver

      This is exactly what I was looking for. Thanks for writnig!

  1. Hello there, I discovered your blog by the use of Google at the same time as looking for a
    similar topic, your web site got here up, it appears to be like
    great. I’ve bookmarked it in my google bookmarks.
    Hello there, simply changed into alert to your blog via Google, and located that it is really informative. I’m gonna be
    careful for brussels. I will be grateful if you happen to proceed this in future.
    Numerous other folks will be benefited out of your writing.
    Cheers!

  2. stock market

    Hi there! I could have sworn I’ve visited this site before but after browsing through many
    of the articles I realized it’s new to me. Anyhow, I’m
    certainly delighted I discovered it and I’ll be bookmarking it and checking back regularly!

  3. Leonida

    Appreciating the persistence you put into your blog and in depth information you provide.
    It’s nice to come across a blog every once in a while that isn’t the same out of date rehashed
    information. Great read! I’ve saved your site and I’m
    adding your RSS feeds to my Google account.

  4. iphone store

    s surprize that customers lined up outside the Apple stores to
    get hands on their favourite phone. The i – Phone Apps Provides
    a large numbers of value added services to its customers.
    s of new features which will is great entertainment for the mobile phone
    users also it gives a new definition to smartphones altogether.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s