Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

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.


advertisement
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.
FIRSTNAME LAST NAME TEAM G FG FT P
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.



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap