JDBC-ODBC Bridge Driver Enables Spreadsheet-as-database Interaction

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.

State	Population
California	35,116,033
Texas	21,779,893
New York	19,157,532
Florida	16,713,149

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