In a previous installment, I wrote about the problematic of effectively piloting Microsoft Word from a Java application. The solution was a little bit clumsy but it worked fine for the needs I had at that time. The idea was to create a Word template and to define bookmarks within the document that would act as anchors that could be referenced from within my Java application. Then, Java calls would be forwarded to Word using some sort of native Word wrapper accessible as a Java proxy. That's it... A couple minutes and I could generate Word documents.

Lately, I was in need of a similar solution for the Microsoft Excel application. I investigated Apache's POI project, which seemed fine at the first glance. The only problems with POI were that:

1. you don't get any COM bridge between Java and Excel, and thus, you cannot stream data to Excel on the fly;
2. you get OutOfMemoryErrors when the Excel document you want to build is too big because of the size of the POI object tree.

Another solution was to build a CSV file using string concatenation and then open it in Excel. I also rejected this option because of its lack of flexibility. Besides, I also wanted to generate charts on the fly using the data I was stuffing into that Excel file.

I found a cool and very powerful, yet commercial and somewhat costly, solution, called Intrinsyc's J-Integra for COM. You might wonder, "why the hell is he talking about a $200 product"? Well, $200 is not that much once you discover all the things you can do with the product. Another important thing: this is not an advertisement message, I don't have anything to do with Intrinsyc. I'm just reporting my experience. Anyway... Let's get to the real stuff...

With J-Integra's com2java utility, you can create a Java API from a COM type library that will allow you to access any COM components, such as Word, Excel and Powerpoint to cite a few. When running the com2java utility, you first select the COM type library you would like to pilot through Java and then you run the utility to generate Java proxies for the selected application. The result is a bunch of Java classes and interfaces that mimic the state, behavior and data structures that make up the chosen COM type library. For instance, when considering Excel, you get interfaces and proxy classes, such as Sheets, Charts, Workbook, Worksheet, Axis, etc... When you are done with that one-time proxy generation, you just put them on the classpath together with J-Integra's runtime and you are good to go.

Within your Java application, you can then use the generated proxies to pilot Excel and carry out any operation that can be performed using the Excel application itself since the proxies have been automatically generated from the Excel COM components, and thus, provide all functionalities available in Excel. Concretely, you can:

• create, rename, delete workbooks;
• create, rename, move, delete worksheets;
• create charts;
• handle data ranges;
• change the font, the borders, the background color, the cell size, etc;
• create sophisticated data tables;
• create formulas and perform computations;
• register event listeners to be informed of Excel events;
• select regions and ranges;
• etc.

You can read the whole documentation here.

Moreover, one big plus of J-Integra's COM bridge solution over Apache's POI API is that the user can interact with the Excel file while it is being generated, which means that you can get user input in real-time and handle it accordingly in your Java business logic.

For instance, initiating an interaction with Excel is as simple as the following code piece:

  excel.Application app = new excel.Application();
  excelApp.setVisible(true);

Then, you can get workbooks and create a new worksheet with the following code:

  Workbooks workbooks = app.getWorkbooks();
  Workbook workbook   = workbooks.add(null);
  Sheets worksheets   = workbook.getWorksheets();
  Worksheet sheet     = new Worksheet(worksheets.add(null, null, null, null));

Once you have a handle on the worksheet, you can easily populate values within a given range as denoted by the following code:

  Range range = sheet.getRange("B2:D4", null);
  Object[][] newValues = {
    { "Hello",           new Boolean(true),  new Double(Math.PI)},
    { new Date(),        new Long(1234567),  new Float(Math.E)},
    { new Boolean(false), "World",            new Date()}
  };
  range.setValue2(newValues);

Finally, once you are done populating your sheet, you just close the workbook and clean the environment:

  workbook.close(new Boolean(false), null, null);
  app.quit();
  com.linar.jintegra.Cleaner.releaseAll();

That's as easy as it gets, isn't it? The biggest negative point I can mention is that 80% of the method parameters in the generated Java proxy classes are of type java.lang.Object and the provided documentation doesn't say much as to what kind of object a given method is expecting as argument. To illustrate this, let's take the method we used above to add a new worksheet.
worksheets.add(null, null, null, null);
In the Worksheets class, the signature of this method is:
public Object add(Object arg1, Object arg2, Object arg3, Object arg4);
Not really self-explanatory as you can see...

If you stick with the most commonly used functionalities, you are fine as the examples provided in the documentation are good enough. But as soon as you delve into more complicated matters, such as charting, then you are in trouble. Personally, I had to browse through their knowledge base and look for articles and white papers. I also had to swim across the 9,000+ messages available in their Yahoo discussion group in order to dig out some basic information. Plus, icing on the cake, by decompiling the generated proxies I could finally infer what a given method was expecting... Not really developer-friendly, I admit, but in a matter of one day, I could create very nice and dynamic charts in Excel.

To conclude, J-Integra brings you the full power of COM right into the Java world. I would also like to mention that J-Integra not only enables Java-to-COM (i.e., C++, ATL, MFC, VB) communications but also COM-to-Java interactions, which means that your C++ and VB applications can talk to your Java programs, such as servlets, EJBs, JMS topics and queues, etc. As you can see, J-Integra allows one to join the best of both worlds into a single and homogeneous whole, which can thus benefit from COM performance and Java flexibility and elegance. Don't throw POI out of the window, though, that API is true gold as well and will probably fit many of your needs.