You don't have to automate Excel or use Jakarta to create simple Excel spreadsheets. Find out how to create them dynamically using a Java servlet.

by Kulvir Singh Bhogal

Apr 28, 2006

Page 2 of 4

The Sample Application
In this application, a client hits the application server by accessing a servlet. This servlet takes the client's request and returns a simple Microsoft Excel spreadsheet. The servlet retrieves the spreadsheet data from a database backend; I'm using IBM DB2 UDB Version 8.2. (download a trial version here), but you can use any database you like as long as you have a JDBC driver and you define the data source properly for your particular database.

This DB2 back end provides the spreadsheet with data. Because the servlet creates the spreadsheet dynamically, when the database data changes, the spreadsheet that the servlet creates also changes to reflect the new data.

Setting Up the DB2 Backend
For demonstration purposes, the database contains statistics for some fictional basketball players. More specifically, the database table stores player data, including games played (G), field goals made (FG), free throws made (FT), and total points scored (P). Table 1 shows the columns in this abbreviated form. The Excel spreadsheets produced from the database data will relay these statistics to Web site visitors.

From the DB2 Command Line Processor, issue the following statement, which creates a database named balldb:

db2 => create database balldb

Next, connect to the database with your user name and password. In my case, both username and password are db2admin:

db2=> connect to balldb user db2admin using db2admin

Next, create a table named STATS to house the statistical basketball data: