© 2002 International Business Machines Corporation. All rights reserved.

Introduction

Let's face it. Not everyone in the world is SQL-savvy. As a consultant, I tend to deal with the full spectrum of job titles at my client organizations. On one end of the spectrum we have tech-head software engineers that can probably take a look at binary code and troubleshoot a software bug; on the other end, we might have business experts who know the ins and outs of the business, but don't know anything about programming. In this article, we'll address the needs of the latter.

The business need

I have been on some locations where the customer would like to see what is in their IBM® DB2® Universal Database^TM, but do not know how to or do not want to use the SQL command line. They want to know if there is a utility that can go through their DB2 database tables and produce Microsoft® Excel® spreadsheets that contain the data in the database. The business expert can then use Microsoft Excel for data manipulation (such as totals, averages, and so on) and for producing reports.

What you'll learn

In this article, you'll learn how to build an application that will allow for the transfer of DB2 data to Microsoft Excel spreadsheets. We'll tap into the offerings of JDBC^TM to provide a no-cost solution. We'll also use the assistance of an open source offering of the Apache Software Foundation.

Back to top

Apache's POI project and HSSF

The Apache Software Foundation has taken on a project known as POI, which stands for Poor Obfuscation Implementation. This project provides Java^TM APIs for manipulating file formats that are based on Microsoft's OLE 2 Compound Document format. The project is divided into several components, including one called HSSF, which writes Excel files. This is the component that we will use. You can read more about the overall structure of the POI project on the POI website at http://jakarta.apache.org/poi/index.html.

HSSF stands for Horrible Spreadsheet Format. I don't believe any further comment on that is necessary.

The HSSF component lets programmers read and write to Microsoft Excel 97-2002 files (in the BIFF8 file format) using Java. In particular, you'll be using the org.apache.poi.hssf.usermodel package in the POI jar file. You can obtain the JAR file at http://jakarta.apache.org/builds/jakarta-poi/. At the time of writing of this article, Apache was at version 1.5.1 of the POI project. The contributors of the Apache Jakarta project are constantly updating their code to better serve the open source community, so you might see a newer version. In this article, we use some of the basic offerings of the POI API, so you should be okay if you use a newer version.

What you can do with HSSF

HSSF lets you manipulate Excel files extensively. Not only can you simply write numeric and string cell values (which is what I show you in this article), you can go as far as to define row and column sizes, and format a cell's style (make text bold or italicized, add borders, etc.).

Back to top

Installing and running the application

The sample code provided here reads data from DB2 tables and writes to a chosen Excel file. It would be a good idea to look over the code as I describe it to get a good grasp of how it works.

Ensure that your environment is set up as follows:

• You are running DB2 7.2 in a Microsoft Windows® environment. Although I built my application on DB2 7.2, you should also be able to run this with DB2 8.1.
• The db2java.zip and the POI jar file are added to your Java class path.
  • db2java.zip contains the JDBC API that we will need to interact with your DB2 database. You must make sure that you are using JDBC level 2. If you are not, you should run the usejdbc2.bat file located in your <DB2INSTALLDIR>\SQLLIB\java12 directory.
  • The POI jar file contains the Apache POI package (org.apache.poi.hssf.usermodel) you will use to produce Microsoft Excel files.

I developed this application using IBM WebSphere® Studio Application Developer Version 5.0. It is a good idea to use an IDE like WebSphere Studio to work with the DB2Excel application at first so that you can step through the code to get a proper understanding of how it works. The main class of the application you will need to run is located in DB2Excel.java.

Back to top

Stepping through the application

The first thing you must do when you run DB2Excel is log in, as seen in Figure 1. You do this by interacting with the DB2Excel GUI. The GUI of DB2Excel was designed using Java Swing. The login portion of the application is handled by the code of LoginDialog.java and LoginService.java. The class SQLFacade.java is used throughout the application to hide the programmer from the intricacies of using the JDBC API. If the login properties specified by a user are valid, then an SQLFacade Object is instantiated. Note that the sample application assumes that the database you are trying to connect to is either local or cataloged locally. This SQLFacade object is then used to interact with the DB2 database data.

After you have successfully logged in, you will be provided with a screen from which you can choose a table from the tables that are available to the current connection, as seen in Figure 2.

To gather the list of available tables, we tap into the metadata of our Connection object. You can see this in the getTablesFromDb() function of the SQLFacade class, as is shown in Listing 1:

 
 
/** 
*  Queries the database for the tables created by the user 
*/ 
public Object[] getTablesFromDb() throws SQLException 
{ 
	DatabaseMetaData md = connection.getMetaData(); 
	String types[] = {"TABLE"}; 
	ResultSet set = md.getTables(null, null, null, types); 
	List list = new ArrayList(); 
	list.add(NO_TABLE); 
	while (set.next()) 
	{ 
		list.add(set.getObject(3)); 
	} 
	set.close(); 
	if (list.size() > 0) 
		return list.toArray(); 
	else return null; 
} 

The user must choose a table from the pulldown and then left click on the Load Table button, which loads the table data into memory. The user can preview the data in the chosen table, as seen in Figure 3.

You can find the code that powers the table data load process in the ResultSetTableModel class in the reloadTableModel method shown in Listing 2.

/** 
*  reloads the TableModel with the contents of specified tableName 
* @param tableName table with which to reload the TableModel 
* @exception SQLException if a database error occurs 
* @exception ClassNotFoundException 
*/ 
public void reloadTableModel(String tableName) 
throws SQLException, ClassNotFoundException { 
	ResultSet rs = null; 
	this.tableName = tableName; 
	try 
	{ 
		rs = sqlFacade.executeQuery("SELECT * from " + tableName); 
		clearAll(); 
		updateColumnModel(rs.getMetaData()); 
		while (rs.next()) 
		{ 
			ArrayList list = new ArrayList(); 
			for (int i = 1; i <= colCount; i++) 
			{ 
				Object o = rs.getObject(i); 
				list.add(o); 
			} 
			result.add(list); 
			rowCount++; 
		} 
			fireTableStructureChanged(); 
		} 
		finally 
		{ 
			if (rs != null) 
			{ 
				try 
				{ 
					rs.close(); 
				} 
				catch (SQLException se) 
				{ 
					System.out.println
					 ("An error has occurred: " + se); 
			} 
		} 
	} 
} 

You will notice that a list of lists is used as the data structure to house the DB2 table data temporarily in memory.

After a table has been loaded into memory, the user can click the Create My Spreadsheet button to set the wheels in motion to create a Microsoft Excel spreadsheet from the current table data.

The user is provided with a Save dialog, as seen in Figure 4.

After the user chooses a destination file, control is passed to the ExcelFileGenerator class. It is in this class that we do the dirty work of creating our Excel Spreadsheet. In particular, the run() method is where things really happen.

A threaded model is used simply to show a progress bar to the user as the Microsoft Excel file is in the process of being generated (in other words, eye candy).

In the interest of saving space, the entire run() method listing is not included in this article, so let's focus on the important steps of the method to get a good grasp on how we create our Excel spreadsheet:

1. We instantiate a FileOutStream object using the name we specified in the Save dialog.

   FileOutputStream fileOut = newFileOutputStream(this.fileName);

   
   
2. We then use the HSSF API for the first time and create a workbook that contains a spreadsheet of the name of our source table.

   HSSFWorkbook wb = new HSSFWorkBook(); HSSFSheet sheet1 = wb.createSheet(this.tableName);

   
   
3. Next we create a row in the spreadsheet and populate this row with the column names of our table.

   HSSFRow row = sheet1.createRow((short)0); List columnNames = tableModel.getColumnNames(); for (int i=0; i<columnNames.size(); i++) { row.createCell((short)i).setCellValue((String)columnNames.get(i)); }

   
   
4. Moving along through the code, we then populate the spreadsheet with our table data. The code extracted from the run() method below is what is used to do this. The rest of the code you see is mostly GUI-related.

   int rowCount= this.tableModel.getRowCount(); for (int i=0; i<rowCount) { List tableData = this.tableModel.getRow(i); row = sheet1.createRow((short)(i+1)); for (int j=0; j<tableData.size(); j++) { String columnData = (String)tableData.get(j); Row.createCell((short)j).setCellvalue(columnData); } }

   
   
5. To wrap things up, we close our workbook and our FileOutPutStream object:

   wb.write(fileOut); fileOut.close();

   
   

Back to top

The fruits of your labor

As mentioned before, a progress bar (shown in Figure 5) is shown to users to let them know that the Excel file is being generated.

When all is said and done, you should see an Excel file written to the destination you specified. Figure 6 shows a screenshot of the resulting Excel file for the data I had in my sample database table.

Back to top

Conclusion

In this article, you learned how to use the offerings of Apache's POI to build an application that can read from your DB2 tables and write to Microsoft Excel spreadsheets. POI is yet another representative example of how open source offerings can be tapped into to create some valuable, powerful applications for your enterprise operation that work synergistically with your commercial applications. Feel free to modify the DB2Excel application to fit your particular organization's needs.

Back to top

Download

Name	Size	Download method
db2excel.zip	15KB	FTP | HTTP

Information about download methods

About the author

Kulvir Singh Bhogal works as an IBM consultant, devising and implementing Java-centric solutions at customer sites across the nation. You can reach Kulvir at kbhogal@us.ibm.com.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Apache's POI project and HSSF
• Installing and running the application
• Stepping through the application
• The fruits of your labor
• Conclusion
• Download
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers