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 components?formerly the domain of VB, C++ and .NET developers?to 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.

Something a Little More Meaningful
Say you want to use Excel as the presentation layer for some data in a database. You want to pull this through JDBC in Java, and present it in an Excel front end. This is nice because the use case for complex business logic is already present in an Excel front end, not to mention presentation and interactivity with other items in a workflow. Instead of trying to understand the business logic or analytics, you can take the original spreadsheet, and ‘fill in the gaps’ using automation in Java.

I’ve given you a simple example to demonstrate this: Included with the download for this article (see left column) are the SQL scripts to set up a simple MySQL database for collating the activities of a sales force. There are three tables:

• Sales has details on the sale itself including a textual description of the item, the quantity, and the price that it sold for, as well as who sold it and what district it sold in.
• People has the names and commission rates of the salespeople.
• Districts has the names and tax rates of the districts where sales occurred.

When generating a report, you’ll want to apply the following business logic to figure out the real numbers.

• The Gross Sale is the number of items times the price per item plus the relevant tax for the district.
• The Net Sale is this number less the before-tax commission taken by the salesperson.

Figure 2. Hello, Data: The Excel template for the front end needs to be populated with data.

These are quite simple and straightforward calculations that could be done in your Java code, but I’m using them here to demonstrate plugging the numbers into an Excel spreadsheet and letting Excel automate the calculations. For more complex cases, where detailed Excel formulae are being used, the principle is exactly the same.

In addition, the download includes the Excel spreadsheet shown in Figure 2. This is the sheet you will use as the ‘template’ to populate with the correct data.

Using JCOM, query the database for the total sales data, using the following SQL:

SELECT sales.id, sales.description, sales.quantity, sales.price, 
districts.districtname,districts.salestax,people.name,people.commissionFROM 'sales','districts','people'WHERE (sales.district = districts.id) AND (sales.salesperson = people.id)

Then, for each record, fill in the appropriate columns in the Excel spreadsheet using automation. The download includes the full code (shown also in Listing 2). In the following sections, I’ll step you through the automation part using JCOM.

In the first part of Listing 2, the aim is to get access to the range of cells that you want to amend. This takes a little bit of overhead to get used to.

• First you need to get an object representing Excel itself, which is very straightforward thanks to the JCOM helper classes.

  ExcelApplication excel = new ExcelApplication(rm);

• Next, you want to get access to the workbooks collection. You want to open your template workbook (at C:sales.xls for this example) and open it into the workbooks collection.

  ExcelWorkbooks xlBooks = excel.Workbooks();

• Next, you want to open your workbook and get a reference to its collection of worksheets.

  ExcelWorkbook xlBook = xlBooks.Open("c:\sales.xls");ExcelWorksheets xlSheets = xlBook.Worksheets();

• Finally, you want to get the first sheet in this collection and define your working range to be all cells on this sheet.

  ExcelWorksheet xlSheet = xlSheets.Item(1);ExcelRange xlRange = xlSheet.Cells();

Once you’ve done this, your xlRange object will allow you to place values and formulae within your cells. This is done by looping through the recordset that is returned from the SQL command issued to the database, and row-by-row, column-by-column, inserting the values from the recordset into the spreadsheet using the xlRange.Item(nRow,nCol).Value(“whatever”) syntax, where ‘whatever’ is derived from the recordset. You derive it by using rs.getObject(i).toString to pull the value for column ‘i’ out of the current row. The current row is incremented by rs.movenext() until you reach the end of the recordset.

You may have noticed a strange-looking function call in Listing 2:

nCol = getExcelColumn(i);

This is a simple helper function that matches the column numbers from the recordset to where the data is supposed to be in Excel. For example, if you look at the SQL statement above again, the commission rate will be the eighth column returned. On the spreadsheet, this is supposed to be in column number 13. This function handles the conversion in this case from one to the other. A more complex application could use named ranges in Excel to match columns, but that is beyond the scope of this article.

Finally, the application enters the formulae into the appropriate cells. It uses the R1C1 notation of Excel to do this, so that consistent strings are derived to describe each formula. In Excel, say you want a formula that adds the value of the ‘A’ column to the ‘B’ column on row 1, and enters the result into the ‘C’ column. To do this you would enter the formula ‘=A1+B1’ into the C column. Should you now want the same formula for row 2, you can copy and paste the formula, and it would automatically update itself to ‘=A2+B2’.

To output these values from the sample program requires a bit of string manipulation, but if you use the ‘R1C1’ relative indexing it is very straightforward. In this case, when you enter the formula into C1, you don’t give the ‘=A1+B1’ reference, you instead give the ‘=RC[-2]+RC[-1]’ reference, which reads ‘Add the value of the column that is two columns back to the value of the column that is one column back.’

When you move to different rows, the value of this formula doesn’t change, and therefore it makes it easy to output the value from a program. That is precisely what I’ve done in these formulae for calculating the total sale, the sales tax, the gross sale, and the net sale, as described above:

  // Pull in the formulae.  xlRange.Item(nRow,7).Value("=RC[-2]*RC[-1]");  xlRange.Item(nRow,9).Value("=RC[-2]*RC[-1]/100");  xlRange.Item(nRow,10).Value("=RC[-3]+RC[-1]");  xlRange.Item(nRow,11).Value("=RC[-4]*((100-RC[2])/100)");

Running this code will give you the spreadsheet shown in Figure 3.

Figure 3. Formulaic: Excel is showing the output of the database query.

Note also that it is better engineering practice in most cases not to have the formulae within the application, as formulae can change over time, and you don't want to have to change your code whenever someone changes a formula. The way around this is to have each new row made as a copy of an existing row that contains the formulae. The template spreadsheet (sales.xls) contains dummy data and the formulae in the second column (the first column contains the column headings). So, when you fill in every row, you can copy the contents of this row before you fill it in with the database values. That way, the formulae come into the new row with the copy, and you don't have any of the business logic in your Java code.

To do this, take a look at the salesReport2.java file (also included in the download), and you will see that instead of the lines that pull in the formulae shown earlier, you instead have this following code at the top of your while loop:

        if(nRow>1)         {          String strDest="A"+(nRow+1);          ExcelRange xlRange2Copy = xlSheet.Range("A2:M2");          xlRange2Copy.Copy(xlSheet.Range(strDest));        }

For every row except the first (you don't need to do it for the first row as all the information is already in sales.xls), this code will take the cells from A2 to M2 and copy them into the row indicated by nRow+1 where nRow is the number of the present row. You add 1 to it to take into account the column headings, which are stored in the first row.

This way you'll get the same results as earlier, but not have to carry the business logic for the calculations along in your Java code?Excel will handle it cleanly for you.

Going Beyond Excel
Remember JCOM is all about COM and not just Excel. Using this toolkit you can control just about any COM object from within Java. Excel is generally the most commonly controlled one, and as such there are nice helper classes to make it easier, but all COM components are available. So controlling other application such as Word or Powerpoint is straightforward too.

Here's an example using Word:

IDispatch wdApp = new IDispatch(rm, "Word.Application");wdApp.put("Visible", new Boolean(true));

As there is no direct helper class for Word, everything is done through the IDispatch object (which is exactly what the underlying helper classes do for Excel). The code above will launch a copy of MS Word and make it available for automation through the wdApp object. You can then play with Word using method such as the 'put' (for setting properties) or 'method' (for calling methods). For example the code above sets the Visible property to true, making the Word app visible.

If you're going to be bound to Windows, there is a wealth of COM components out there to expand your horizons beyond what Java currently offers. Hardware control through the serial or parallel ports can be a snap using the right drivers and now you can do it in Java. And that's just the beginning. With JCOM in your toolbox, the sky is the limit.