Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


JDBC-ODBC Bridge Driver Enables Spreadsheet-as-database Interaction : Page 2

Java APIs such as Apache Jakarta POI allow applications to programmatically modify Microsoft Excel spreadsheets but only on a cell-by-cell basis. Using JDBC in conjunction with ODBC, Java programmers can interact with Excel spreadsheets as if they were databases.


WEBINAR: On-demand Event

Unleash Your DevOps Strategy by Synchronizing Application and Database Changes REGISTER >

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).

Click to enlarge
Figure 8: Map Spreadsheet to the SQL World

Figure 9 shows the execution of your application:

Click to enlarge
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.

Comment and Contribute






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



Thanks for your registration, follow us on our social networks to keep up-to-date