ava programmers who use JDBC may presume that the technology is restricted to interaction with database products such as IBM DB2 Universal Database, Informix, Oracle, or Microsoft SQL Server. This article illustrates how JDBC can be used in conjunction with ODBC to interact with Microsoft Excel spreadsheets as if they were databases.

Programming interfaces such as Apache Jakarta POI allow a Java application to programmatically modify Excel spreadsheets (see “Learn to Read and Write Microsoft Excel Documents with Jakarta’s POI“). Unfortunately, such APIs allow only cell-by-cell interaction with spreadsheets. They do not enable interacting with a spreadsheet as if it were a database, which utilizes the power of SQL. By tapping into the JDBC-ODBC bridge driver, you can work with spreadsheets as databases.

Setting Up Your Excel Spreadsheet as an ODBC Source
Start off by creating a simple Excel spreadsheet with which you will interact. Table 1 shows the structure you want to create. It contains population estimate data for the four most populous states in the US as estimated by the U.S. Census Bureau as of July 1, 2002.

Figure 1 shows a screenshot of the spreadsheet you will need to create. I named the spreadsheet “statepopulation.xls.” You can also download the spreadsheet.

Figure 1: The statepopulation.xls Spreadsheet

Next, you need to register your spreadsheet as an ODBC Data Source. These instructions assume that you are using Microsoft Windows 2000, but registering ODBC Data Sources in other Microsoft Windows operating systems should be quite similar.

Open the Windows Control Panel. Next, open up Administrative Tools. Double click on the Data Sources (ODBC) icon (see Figure 2).

Figure 2: The Data Sources Administrative Tool

In the User DSN tab, choose the Excel files option and click Add (see Figure 3).

Figure 3: Add an Excel File Data Source

In the subsequent driver selection page, choose the “Microsoft Excel Driver” option and click Finish (see Figure 4).

Figure 4: Choose the Microsoft Excel Driver

Next, you will be presented with a window in which you will select the Excel file that you want to setup as an ODBC source. Choose the Select Workbook button (see Figure 5) and choose the spreadsheet you created (statepopulation.xls).

Figure 5: Select the Workbook You Want to Setup as a Data Source

If you plan on using JDBC to write to the Excel file, you must deselect the Read Only option.

You should be returned to the ODBC Microsoft Excel Setup window. When you click the Options>> button, you see a Rows to Scan option. Since you have only a few records, you don’t need to increase the number.

Go ahead and name your Data Source Name as ExcelJDBCTest. You can include a description (see Figure 6).

Figure 6: Specifying a Name for the Data Source

In the ODBC Data Source Administrator screen shown in Figure 7, you should see the ODBC Data Source you just created.

Figure 7: Confirm the Data Source Was Established

Interact with Your Spreadsheet (with Java)
Now that you have created your ODBC source, it’s time to interact with it. The following class, InteractWithExcel.java, reads from your spreadsheet and displays the records to standard out:

import java.io.*;import java.net.*;import java.sql.*;import java.util.*;public class InteractWithExcel {	static final String DRIVER_NAME = "sun.jdbc.odbc.JdbcOdbcDriver";	static final String DATABASE_URL = "jdbc:odbc:EXCELJDBCTest";	public static void main(String[] args) {		try {			Class.forName(DRIVER_NAME);			Connection conn = null;			conn = DriverManager.getConnection(DATABASE_URL);			Statement stmt = conn.createStatement();			ResultSet rs =				stmt.executeQuery("select State,Population from [Sheet1$]");			while (rs.next()) {				String state = rs.getString(1);				int population = rs.getInt(2);				System.out.println(state + " - " + population);			}			rs.close();			stmt.close();		} catch (ClassNotFoundException cnfe) {			System.err.println("ClassNotFoundException Was Thrown");			cnfe.printStackTrace();		} catch (SQLException sqle) {			System.err.println("SQLException Was Thrown");			sqle.printStackTrace();		}	}}

Note that the Driver you use is sun.jdbc.odbc.JdbcOdbcDriver, the JDBC-ODBC bridge driver. You don’t have to download anything to use it. The driver is built into the JDK.

Another important point is the EXCELJDBCTest portion of the string of the database URL you specify, jdbc:odbc:EXCELJDBCTest, is the name of the data source you specified earlier.

In order to perform a query, you have to have a database table with which to interact. But your spreadsheet contains sheets and not tables. However, the JDBC-ODBC bridge driver respects sheets as tables. This can be seen in the following statement:

stmt.executeQuery("select State,Population from [Sheet1$]");

You specify a table name of “[Sheet1$]” since your sheet name in Excel is (by default) “Sheet1” (see Figure 8). Note that the sheet name must be enclosed in brackets and end in a $. If, for example, your sheet name was MySheetName, your corresponding table would be [MySheetName$]. Your table grabs the column names from the first row of your spreadsheet (i.e., State and Population).

Figure 8: Map Spreadsheet to the SQL World

Figure 9 shows the execution of your application:

Figure 9: A Successful Run of the Program

You can change your query to: select sum(Population) from [MySheetName$] to realize the power of your setup. Go ahead and try this. You should see that executing the query in your application returns the expected result of 92766607, the sum of the four population numbers.

We can also write to your spreadsheet using a statement like the following:

int result = stmt.executeUpdate("insert into [Sheet1$] values('Illinois',12600620)");

This statement would add a new entry to your table, namely Illinois with a population of 12600620.

Spreadsheets as Databases
In this article, you learned how to interact with Excel spreadsheets as if they were database tables. You facilitated this ability by first registering a spreadsheet as an ODBC Data Source. You could then use the JDBC-ODBC bridge driver to interact with your spreadsheet using SQL.

With your newly learned technique, you can use the inherent power of SQL to read from and write to your spreadsheets. Leveraging SQL’s built-in functions adds to any Java programmer’s arsenal. However, you should not consider using Microsoft Excel for the database functionality of your enterprise. Use a much more scalable database solution (e.g., IBM DB2 UDB, Oracle Database, Microsoft SQL Server, etc) for housing your enterprise data.