Login | Register   
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 3

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 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:


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:

<resource-ref id="ResourceRefDB2DS"> <description> </description> <res-ref-name>jdbc/DB2datasource</res-ref-name> <res-type>javax.sql.DataSource</res-type> <res-auth>Container</res-auth> <res-sharing-scope>Shareable</res-sharing-scope> </resource-ref>

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.

<?xml version="1.0" encoding="UTF-8"?> <webappbnd:WebAppBinding xmi:version="2.0" xmlns:xmi="http://www.omg.org/XMI" xmlns:webappbnd="webappbnd.xmi" xmi:id="WebAppBinding_1145216558244" virtualHostName="default_host"> <webapp href="WEB-INF/web.xml#WebApp_ID"/> <resRefBindings xmi:id="ResourceRefBinding_1145221898513" jndiName="jdbc/db2ds"> <bindingResourceRef href= "WEB-INF/web.xml#ResourceRef_1145221898513"/> </resRefBindings> </webappbnd:WebAppBinding>

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 + '\t'); } out.println();

Pay particular attention to how the preceding code prints each column name followed by a tab character (\t). The client side interprets the \t 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)+'\t'); } 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 (\t). 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();

Comment and Contribute






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