ust about every time you write any kind of application that renders data in a table-like manner in HTML or in any of the myriad of Java table controls, the requirement usually comes in (at the last minute) to have 'Export to Excel' functionality. Then the headache begins. How do you do it? Render in HTML tables that can be handled in Office 2003? Sorry, no good! You also have to support Office 97!
So you go out and find a toolkit to meet the current requirements and then more requirements come in. 'Can you do it in Word? How about Powerpoint? Oh, and can it use the modem to dial out to a remote server and post data there? What do you mean Java can't do that? Java can do anything.'
Thanks to frameworks that bridge Java and COM, you can be the ultimate Yes-man when these situations crop up. A Java-COM bridge lets you manipulate Windows componentsformerly the domain of VB, C++ and .NET developersto your heart's content. And you can migrate away from end-to-end COM systems by implementing a Java front end that talks to DCOM back ends. By the end of this article you'll be ploughing through one of the more impressive Java-to-COM bridges: the imaginatively named JCom.
Getting Started with Excel Basics
To get started, download the API from Sourceforge. This contains all the source code for the Java classes that JCom uses (as expected) as well as the C++ code and compiled DLL that JCom uses to marshal between Java and COM. Put this DLL in your (java home)/bin/ directory or you will have problems. Also, the JAVA_HOME environment variable needs to be properly set or, again, you may have problems getting the code to run. Most of the documentation for JCom is currently in Japanese, but the translation effort is ongoing and hence will improve over time.
Once you have it downloaded and installed, use the code in Listing 1 to give it a whirl. It will create the JCom interface to Excel, and fill the ubiquitous 'Hello World' into the first cell. You can see the result in Figure 1. JCom, while being a generic COM library, has a bunch of helper classes for Excel, as Excel is probably the most commonly automated COM application. These helper classes, which can save a lot of time, are part of what makes JCOM one of the better class libraries.
|Figure 1. Hello, Excel: Your First COM automation from Java uses Excel.|
If you've ever used VB or VBA to automate Excel, then Listing 1
will look very familiar to you. The ExcelApplication class exposes a Workbooks()
property that enumerates the currently open workbooks in Excel or allows you to add or remove workbooks from a running copy of Excel. You add with the .Add()
method, and this returns a reference to a workbook.
A workbook is what holds the input data in Excel. Workbooks compose the basis of an XLS file. A workbook is made up of a number of worksheets (in Figure 1, there are three, called Sheet1, Sheet2, and Sheet3). The worksheet is an enumeration exposed through the Item property. You can access a specific worksheet using this property. In Figure 1, Sheet 1 is the first sheet, so you can get a reference to it using xlSheets.Item(1).
Once you have a sheet, you can manipulate the data on it using ranges. A range is a cell or number of cells. So, for example, the cell A1 is referenced as the range (1,1). The Value property can then be assigned to load data into that cell.