Tuesday, June 3, 2008

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.

3 comments:

Unknown said...

How to insert a row in the excelsheet when it is completely blank? I am treating an excelsheet as database using JdbcOdbc. I want to insert the column names as employee_name, employee_id and salary.I have written a code to insert these 3 field-names which is as follows:
Connection con = null;PreparedStatement pS=null;ResultSet rs=null;String query;
try{ Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection( "jdbc:odbc:MYDSN" );
query="insert into [Sheet2$] values('employee_name','employee_id','salary')";
pS=con.prepareStatement(query);
pS.execute();
con.close();
}catch(Exception e){e.printStackTrace();}

But the exception-message is:
[Microsoft][ODBC Excel Driver] Number of query values and destination fields are not the same.

But if I insert these fields manually and then execute a query to put values (for example- ABC,id1234,10000), then it works. What shall i do??? Please help.

Anonymous said...

How about if you want to insert more then 255 characters in a cell. If i use the code you provided, it truncates after 255 characters. Please suggest.

Anonymous said...

Thanks! It is great to "readOnly=false" so that it is OK to update Excel ......