How to Use Obba for a Link between Java and Excel

This post shows the step by step guide on how to use Obba. Obba is a Java object handler for excel spreadsheet applications. Obba documentations are very good for the intermediate or advanced level. For the beginner or novice, we provide some useful examples for more clear understanding.

Introduction

We try to create objects for Java classes and call its member functions in Excel using Obba.

For this purpose, 1) we make a sample Java code and export it as a jar file and 2) use this jar file in Excel through Obba.

Installing Obba

If Obba was not installed previously, download and install it with default setting.

obba.info (http://obba.info/)

Sample Java Class

At first, we make a Java file which has a class definition with its members and member functions. The object generated from this class is used in Excel through Obba.

For this end, open Eclipse, create new Java Project, and add new Class. Names for our project and class are aObba and CObba respectively. Of course, you can change these names to your favorite names.

For the clear understanding, it doesn’t suffice to treat with only simple calculations. So we consider not only simple scalar examples but also vector or array related examples.

We write the following sample code to CObba.java file.

• CObba.java

//=========================================================================#
// Financial Econometrics & Derivatives, ML/DL using R, Python, Tensorflow  
// by Sang-Heon Lee 
//
// https://shleeai.blogspot.com
//-------------------------------------------------------------------------#
// How to use Obba (connection between Java and Excel)
//=========================================================================#
package aObba;
 
public class CObba {
 
    public static double func1(double a) {return (a*a);}
    public static double func2(double a, double b) {return (a*b);}
    public static double[] func3(double[] a, double[] b) {
            
        double[] c = new double[a.length];
        for(int i=0;i<a.length;i++) {c[i] = a[i]*b[i];}
        return (c);
    }
    
    public static void main(String[] args) {
        
        // scalar input & scalar output
        double a = func1(7);
        
        // scalar input & scalar output
        double b = func2(7,8);
        
        // array input & array output
        double[] c = {1,2,3,4,5,6,7,8,9,10};
        double[] d = {2,3,4,5,6,7,8,9,10,11};
        double[] e = func3(c,d);
        
        // print results
        System.out.println("func1 = " + a);
        System.out.println("func2 = " + b);
        System.out.println("func3 = ");
        for(int i=0;i<e.length;i++) {
            System.out.println(e[i]);
        }
    }
}

Running CObba.java file results in the following correct outputs.

Eventually, our purpose is to call Java functions such as func1(), func2(), func3() and to see the above same results in Excel.

Export as a JAR file

Obba extracts information regarding classes from JAR file. Therefore, we should make a JAR file for our class.

Using Export menu in Eclipse, we can make the JAR file (aObba.jar) as the following figure.

Obba Commands in Excel

Now it is time to use Excel. In Excel file, write the following Obba commands to each cell as formula and save it to the same folder in which JAR file is located.

C2  = OBADDJAR("aObba.jar",TRUE)
C5  = OBMAKE("Obba1","aObba.CObba")
F10 = OBGET(OBCALL("",$C$5,"func1",
                  OBMAKE("","double",C10)))
F15 = OBGET(OBCALL("",$C$5,"func2",
                  OBMAKE("","double",C15),
                  OBMAKE("","double",D15)))
F20 ={TRANSPOSE(
         OBGET(OBCALL("",$C$5,"func3",
         OBMAKE("","double[]",C20:C29),
         OBMAKE("","double[]",D20:D29))))}

Using the above Obba commands, we can see the following Excel file with same results.

Seeing the above figure and its formula in each cell, we can easily figure out the meaning of Obba commands.

1. In C2, find and ready to use aObba.jar
2. In C5, make an Obba1 object as an instance of class aObba.CObba.
3. In F10, call a func1 (OBCALL) with single scalar input (OBMAKE) and get a result (OBGET).
4. In F15, call a func2 (OBCALL) with two scalar inputs (OBMAKE) and get a result (OBGET).
5. In F20, call a func3 (OBCALL) with two array inputs (OBMAKE) and get a result (OBGET) and the transpose for the vertical representation.

In particular, formula in cell F20 should be executed by using Ctrl+Shift+Enter since func3 function returns a vector or array,

Final Thought

The bottom line is that after finding a JAR file and making an instance of its class, use OBCALL for calling a function, use OBMAKE for passing input arguments, and use OBGET for getting a result.

From this post, we can learn how to use Obba with some examples of variety ,which are intended for some general usage.

Originally posted on SHLee AI Financial Model blog.