Jacob read Excel pivot tables

Code hosted in github

• Import from: jacob_excel_reader

Introduction

This article describes how to use Jacob for reading excel files in java. It also provides an example of how to run VBA functions in Java.

Background

Reading excel files is not trivial for those who try this for the first time. However after learning of how to do this using VBA or C# it is much easier to implement something similar using Jacob. This is obvious that just knowing Java is not enough to start working with COM objects.

Using the code

Prerequisites

Before using the code please make sure you have

• JRE 7 - 32 bits installed
• Eclipse with maven and egit plugins
• MS Office installed

How to run

Use the following argument list:

-Djava.library.path=${workspace_loc:jacob_excel_reader}\lib 

Run the ReadExcel class

A brief explanation of dispatch object

Dispatch is the class that holds the com object assigned to it. Its main functions are Call and Invoke.

Call is actually an Invoke for methods and properties:

public static Variant call(Dispatch dispatchTarget, String name, Object a1) {
    throwIfUnattachedDispatch(dispatchTarget);
    return callN(dispatchTarget, name, new Object[] { a1 });
}

public static Variant callN(Dispatch dispatchTarget, String name, Object[] args) {
    throwIfUnattachedDispatch(dispatchTarget);
    return invokev(dispatchTarget, name, Dispatch.Method | Dispatch.Get,
            VariantUtilities.objectsToVariants(args),
            new int[args.length]);
}

How to run VBA code

1. ScriptControl object

   ScriptControl is the com object that can execute vba statements:

   Java

   public static CellType getCellType(Dispatch cell, Dispatch excel){    
       System.runFinalizersOnExit(true); String lang = "VBScript";
       Dispatch sControl = new Dispatch("ScriptControl"); Dispatch.put(sControl, "Language", lang);
       Dispatch.put(sControl, "AllowUI", new Variant(true));
       errEvents te = new errEvents();
       DispatchEvents de = new DispatchEvents(sControl, te);
   
       String script = "Function CellType(c) : "
       + "    Application.Volatile" + " : "
       + "    Set c = c.Range(\"A1\")" + " : "
       + "    Select Case True" + " : "
       + "        Case IsEmpty(c): CellType = \"Blank\"" + " : "
       + "        Case Application.IsText(c): CellType = \"Text\"" + " : "
       + "        Case Application.IsLogical(c): CellType = \"Logical\"" + " : "
       + "        Case Application.IsErr(c): CellType = \"Error\"" + " : "
       + "        Case IsDate(c): CellType = \"Date\"" + " : "
       + "        Case IsNumeric(c): CellType = \"Value\"" + " : "
       + "    End Select" + " : "
       + " : End Function";
   
       Dispatch.call(sControl, "AddObject", new Object [] {"Application", excel}).toString();
       Dispatch.call(sControl, "AddObject", new Object [] {"testCell", cell}).toString();
       Dispatch.call(sControl, "AddCode", new Object [] {script}).toString();
   
       String str = Dispatch.call(sControl, "Eval", 
         new Object [] {"CellType(testCell)"}).toString();
   
       return CellType.fromString(str);
   }

2. How to run a method-member of a com object

   Java

   Dispatch rows;
   rows = Dispatch.invoke(rangeActiveX, "Rows", Dispatch.Get, new Object[] {  }, new int[1]).toDispatch();
   Dispatch.get(rows, "Count").getInt()

Which is equivalent to VBA:

Sheet.Range("A1").Rows.Count

Points of Interest

Intended to those who plan to develop bridges to COM components.

History

To be continued.