Working with Excel Sheets ·

On Windows, you could use the jdbc:odbc driver to access Excel. This does not need any extra jars.

NOTE: On 64 bit windows, you need to download and install the 64 bit ODBC drivers (Download and install AccessDatabaseEngine_x64.exe)

Example:

Given a excel sheet “D:\\myfolder\\sample.xls”, with the following data:

Name	Age	Sex
Raju	20	Male
Ramu	30	Male
Sheela	25	Female

You can do the following

// Get the DB instance
var db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver", 
"jdbc:odbc:Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};
DBQ=D:\\myfolder\\sample.xls;readOnly=false", 
"", 
"");


// Run a select
$rs = db.select("select * from [Sheet1$]");


// Assert the first row has Raju in the Name column
_assertEqual("Raju", $rs[0]["Name"]);


// Assert the first row has 20 in the Age column. 
// All values will be strings, so we need to parseInt the age.
_assertEqual(20, parseInt($rs[0]["Age"]));


// Run an update.
db.update("update [Sheet1$] set Age=21 where Name='Raju'");


// Do a select again to verify
$rs = db.select("select * from [Sheet1$]");
_assertEqual(21, parseInt($rs[0]["Age"]));

Note the readOnly=false in the connection string on the first line.
This is required if you wish to update the Excel sheet.

If you do not add readOnly=false, you will get an error like this:

java.sql.SQLException: [Microsoft][ODBC Excel Driver] Operation must use an 
updateable query.