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.
Table 1. Population Estimates for Most Populous States
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).
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|