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

ind out how to create simple Excel spreadsheets dynamically using a Java servlet.

Spreadsheets play a vital role in the enterprise. Business analysts and management typically use the data in spreadsheets to make critical business decisions. The DevX article “Learn to Read and Write Microsoft Excel Documents with Jakarta’s POI” shows you how to read and write Microsoft Excel documents using Apache Jakarta’s POI. In this article, I’ll show you how you can create simple Microsoft Excel spreadsheets dynamically on a J2EE Web application server. Accordingly, Web site visitors will be able to visit a URL and have the most up-to-date spreadsheet data available to them via their Web browsers. I’ll show you how to create your spreadsheets in a Java servlet engine such as that of IBM WebSphere Application Server Version 6 without having to use a facility such as POI.

The Setup
A picture speaks a thousand words, so for the sake of brevity, here’s a pictorial representation of what I’ll be building. As Figure 1 shows, this article uses IBM WebSphere Application Server Version 6 for the J2EE application server.

 
Figure 1. Topological Overview: A Topological Overview of the Project Setup
Author’s Note: The servlet code used in this article can be used with different application servers, but doing things such as setting up a data source will vary depending on which one you use. I’ll leave it up to you to do the research to “port” the approach shown here to different application servers.

This application uses IBM Rational Application Developer Version 6, which I’ll call Application Developer for the remainder of this article. Application Developer is an IDE with a built-in WebSphere Application Server Version 6 test environment. Consequently, you can test, develop, and deploy you entire application within Application Developer. You can download a trial version to test Application Developer for yourself.

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.

The SpreadSheet Servlet
You can download the sample project with all its related source code as an enterprise application archive (.EAR file). The workhorse that creates the spreadsheets and looks up data from the database is the doGet() method contained in the SpreadSheetServlet.java class file. Here’s how the doGet method creates Microsoft Excel spreadsheets programmatically.

To create a Microsoft Excel spreadsheet on the application server in a servlet you first set the content type returned by the servlet in the HttpServletResponse object:

   response.setContentType("application/vnd.ms-excel");

In this case, the content type you want to return is an Excel spreadsheet. Specify the type programmatically as shown in the preceding code. Note that this differs from the default content type of text/html, which is what servlets commonly return; but the text/html content type produces HTML content that a Web browser can display.

Now that you’ve set the content type in the HttpServletResponse object, call the getWriter() method to return a PrintWriter object that can send character text to the client:

   PrintWriter out = response.getWriter();
 
Figure 2. Create a Data Source: The figure shows the process of creating a data source to access the DB2 backend.

Next the servlet locates the DataSource object via a JNDI lookup:

   // look up data source and get connection   DataSource ds = (DataSource)       ctx.lookup("java:comp/env/jdbc/DB2datasource");

To access the database from WebSphere Application Server, you need to use a javax.sql.DataSource. Create a data source named jdbc/db2ds as shown in Figure 2.

You might have noticed that the servlet looks for a data source named java:comp/env/jdbc/DB2datasource but the JNDI name was defined as jdbc/db2ds. That’s because the servlet code uses a resource reference that provides a level of indirection to the actual JNDI name. You define this resource reference in the Web deployment descriptor file (web.xml). The relevant portion of the reference definition is shown below:

                                 jdbc/DB2datasource         javax.sql.DataSource         Container         Shareable      

In WebSphere Application Server, the deployment descriptor works in tandem with the binding file ibm-web-bnd.xmi to map the resource reference to the actual JNDI name defined through the administrative console.

                               

After looking up the DataSource, you obtain a connection and issue a query against the database using that connection:

   Connection connection = ds.getConnection();   Statement stmt = connection.createStatement();   ResultSet resultSet = stmt.executeQuery(      "SELECT * from STATS");

The ResultSet object returned by the preceding code contains a lot of metadata about the data it houses. You can discover this metadata through the ResultSetMetaData object which you get by calling the ResultSet object’s getMetaData method. For example, you can gather the column names and column sizes from the ResultSet object. If you’re interested in learning more about JDBC metadata, see the DevX article: “Gather Crucial JDBC Connection Information with Database Metadata.”

For example, suppose you didn’t know the column names for the data in Table 1. To find out, the code below uses the ResultSetMetaData object to dynamically discover them. The sample code uses this technique to create the column headers in the spreadsheet produced by the servlet:

   // extract column names and print as column headers   ResultSetMetaData resultSetMetaData =       resultSet.getMetaData();   int numColumns = resultSetMetaData.getColumnCount();   for (int i=1; i<(numColumns+1); i++)   {      String columnName =         resultSetMetaData.getColumnName(i);      out.print(columnName + '	');   }   out.println();

Pay particular attention to how the preceding code prints each column name followed by a tab character ( ). The client side interprets the characters as new column signifiers. When all the columns are complete, the out.println(); command tells the client to start a new spreadsheet row.

After printing the column names, you move on to printing the data housed in the ResultSet object by cycling through the object using a while loop as shown below.

   // print data to spreadsheet   while (resultSet.next())    {   for (int i=1; i<(numColumns+1); i++)      {         out.print(resultSet.getString(i)+'	');      }         out.println();   }

For each row in the ResultSet, the code runs a nested for loop, printing the contents to the PrintWriter object using the same approach used when printing the column names?each column's data is separated by a tab character ( ). Again, as each row is completed, the out.println(); command tells the clients to start a new Excel spreadsheet row.

To finish, the doGet servlet call performs some housekeeping, cleaning up the database ResultSet, and closing the connection:

   // cleanup   resultSet.close();   stmt.close();   connection.close();

Seeing the Results in Excel
To see the SpreadSheetServlet servlet in action, all you have to do is access the servlet from a Web browser. Obviously, the machine that is accessing the servlet needs to have Microsoft Excel installed so that it can interpret the Excel content when the servlet responds.

The URL you use to access the servlet depends on the context root that you define for the project in your EAR application deployment descriptor (application.xml). Note the element in the code below:

                SpreadSheetEAR                  SpreadsheetWebProject.war                    SpreadsheetWebProject             

The URL also depends on the tag you define in the Web deployment descriptor, web.xml, as specified by the tag:

               SpreadsheetServlet         /SpreadsheetServlet      

So using the example data above, you'd access the servlet using the address:

   http://:      /SpreadSheetWebProject/SpreadSheetServlet

The servlet, displayed in Internet Explorer, is shown in Figure 3.

 
Figure 3. Sample Spreadsheet: The figure shows the Microsoft Excel spreadsheet produced by accessing the SpreadSheetServlet.

To make sure the Excel spreadsheets being produced by the servlet are truly up-to-date, you can issue an UPDATE statement using the DB2 command line processor to update the player's statistics.

In this article you've seen how to create simple spreadsheets in a Java servlet for consumption by a Web browser. Such a facility enables your Web site visitors to view current data from your database in a familiar format: a Microsoft Excel spreadsheet. You might use such dynamically-created spreadsheets to inform clients about current inventory, or to provide your accounting department with current database data.

If you've ever tried it, teaching a non-techie how to issue an SQL query can be quite a painful experience. Letting the bean counters in your enterprise do their bean counting using a familiar tool such as Microsoft Excel is a much more efficient approach.

This approach has its limitations, though. You do not have the fine-grained power to create spreadsheets like the ones you can produce using Apache Jakarta POI. If you need to create spreadsheets where you can manipulate fonts and specific fields, you'll be happier with an architecture that uses Apache POI on your application server. See the article "Learn to Read and Write Microsoft Excel Documents with Jakarta's POI" for more on the POI project.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a

chrome os developer mode

How to Turn on Chrome OS Developer Mode

Google’s Chrome OS is a popular operating system that is widely used on Chromebooks and other devices. While it is designed to be simple and user-friendly, there are times when users may want to access additional features and functionality. One way to do this is by turning on Chrome OS