Update Spreadsheet Using JDBC-ODBC Bridge

Sun JDBC-ODBC bridge driver provides a convenient way to interact a Spreadsheet as a relational database. We can manipulate a Spreadsheet like we do to a database.

1. Code snippet to update a spreadsheet:

a. Update

String updateSql = "update [qas test$] set URL='http://www.mytest.com/possible' where Title Like '%A multiline button is possible%' ";

int updateSuccess = stmnt.executeUpdate(updateSql);

b. Insertion

String insertSql = "Insert into [qas test$] (Title, URL, Month, Year) values ('Test More Is Better', 'http://www.mytest.com/testMore', 'June', 2008 )";

int insertSuccess = stmnt.executeUpdate(insertSql);

2. Don't forget to set 'readOnly=false' when creating connection to a Spreadsheet:

c = DriverManager.getConnection( "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=C:/Documents and Settings/tangk1.FIC/Desktop/qa.xls; readOnly= false");

I run into the following exception fist time I wrote the code:

[Microsoft][ODBC Excel Driver] Operation must use an updateable query. at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6998) at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7155) at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:3143) at sun.jdbc.odbc.JdbcOdbcStatement.execute2(JdbcOdbcStatement.java:440) at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(JdbcOdbcStatement.java:321) at nwag.eFileProject.ExcelReader.main(ExcelReader.java:53)

Then I figured out the connection is read only by default if we do not set "readOnly=true", because the 'readOnly' property specifies if transactions in this connection are ready only. To update a Spreadsheet, we have to explicitly set 'readOnly=true'.

3. A complete sample code to update a spreadsheet:

import java.sql.Connection; import java.sql.SQLException;
import java.sql.Statement; import java.sql.ResultSet;
import java.sql.DriverManager;

public class ExcelReader {
public static void main( String [] args ) throws ClassNotFoundException, SQLException {

Connection c = null;
Statement stmnt = null;
ResultSet rs = null;

try {
Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
c = DriverManager.getConnection( "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=C:/Documents and Settings/tangk1.FIC/Desktop/qa.xls; readOnly= false");
stmnt = c.createStatement();

String updateSql = "update [qas test$] set URL='http://www.mytest.com/possible' where Title Like '%A multiline button is possible%' ";
String insertSql = "Insert into [qas test$] (Title, URL, Month, Year) values ('Test More Is Better', 'http://www.mytest.com/testMore', 'June', 2008 )";
String query = "select * from [qas test$] where Year=2008 ";

int updateSuccess = stmnt.executeUpdate(updateSql);
int insertSuccess = stmnt.executeUpdate(insertSql);
rs = stmnt.executeQuery( query );

while( rs.next()) {

String title = rs.getString("Title");
String url = rs.getString( "URL" );
String month = rs.getString("Month");
String year = rs.getString("Year");
System.out.println( title + " URL : " + url + ", Year " + year + ", Month " + month );
}
} catch( Exception e ) {
e.printStackTrace();
} finally{
if (rs != null)
rs.close();
if (stmnt != null)
stmnt.close();
if (c != null)
c.close();
}
}
}
}
}

The Spreadsheet used in this example can be found from here.

How to Connect to an Excel Spreadsheet Using JDBC-ODBC Bridge Driver?

JDBC-ODBC can be used to access MS Excel spreadsheets as if they were databases, and thus we could utilize the power of SQL.

There are two ways to connect to a spreadsheet file using jdbc-odbc: Using DSN connection and Using DSN-less connection. The main difference is the construction of JDBC URL.

1. Using DSN connection

To use DSN connection, we firstly need to set up the Excel Spreadsheet as an ODBC source by using Windows Administrative Tools. The details of creating a User DSN can be found from here. Once the DSN is defined, we can interact the target spreadsheet file using jdbc-odbc. The db connection string is (please refer the complete sample for details ):

java.sql.Connection c = java.sql.DriverManager.getConnection( "jdbc:odbc:qa", "", "" );

“qa” is the name of DSN which points at a spreadsheet file which is going to be processed.

2. Using DSN-less connection

It is also possible to connect to a spreadsheet without using DSN, which provides a more flexible way within code to point JDBC at an Excel file of interest without the accesses to a client registry to define the required DSN. Without DSN, the db connection is created as following, please not the difference of constructed JDBC URL:

java.sql.DriverManager.getConnection( "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=C:/Documents and Settings/myPath/Desktop/qa.xls");

Here DBQ defines the path to the target spreadsheet file (qa.xls). Both backslash and forward slash work well.

With using DSN, we need easy access to a client registry to define the required DSN, while jdbc-odbc driver provide a more flexible method to connect to spreadsheet files without DSN.

3. A complete example of connecting to a spreadsheet using JDBC-ODBC

Here is a complete sample of connecting to a Spreadsheet file using JDBC

import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.DriverManager;

public class ExcelReader
{
public static void main( String [] args )
{
Connection c = null;
Statement stmnt = null;

try
{
Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );

//using DSN connection. Here qa is the name of DSN
//c = DriverManager.getConnection( "jdbc:odbc:qa", "", "" );

//using DSN-less connection
c = DriverManager.getConnection( "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=C:/Documents and Settings/tangk1.FIC/Desktop/qa.xls");

stmnt = c.createStatement();
String query = "select * from [qas test$] where Month='March' and Year=2001;";
ResultSet rs = stmnt.executeQuery( query );

System.out.println( "Found the following URLs:" );
while( rs.next() )
{
System.out.println( rs.getString( "URL" ) + " " + rs.getInt("Year"));
}
}
catch( Exception e )
{
System.err.println( e );
}
finally
{
try
{
stmnt.close();
c.close();
}
catch( Exception e )
{
System.err.println( e );
}
}
}
}

Reference:

JDBC-ODBC Bridge Driver Enables Spreadsheet-as-database Interaction ( http://www.devx.com/Java/Article/17848)