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,
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.
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:
ExcelRange xlRange2Copy = xlSheet.Range("A2:M2");
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 codeExcel will handle it cleanly for you.