RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Using a Java Servlet to Generate Up-To-Date Microsoft Excel Spreadsheets  : Page 2

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.

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:

   db2=> create table stats (
     firstname varchar(40) not null, 
     lastname varchar(40) not null,
     team varchar(40) not null,
     G int not null, 
     FG int not null, 
     FT int not null, 
     P int not null)
For this experiment, you need to populate the STATS table with some sample data. For demonstration purposes Table 1 shows five fictional players and their associated fictional data:

Table 1. The STATS table with demo data.
Bicky Bhogal Ft Worth Computer Junkies 80 584 288 2,652
Deep Mathroo Sunnyvale Flying Gutis 75 665 394 1,964
Paul Mathroo Fremont Chip Pushers 76 334 250 2,600
Amar Dilon Tyson Ambulance Chasers 65 516 454 2,557
Meet Feona ACC Historians 80 602 517 1,836

The following statement inserts the first row into the database.

   insert into stats values ('Bicky','Bhogal', 
      'Ft Worth Computer Junkies',80,584,288,2652)
Go ahead and issue a similar statement for the other rows in the STATS table, using the appropriate data, until you've entered all the data in Table 1.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date