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.