XLS JDBC

XLSJDBC is a readonly jdbc driver to access xls files from java, just as it where another ordinary SQL database.

Requirements

To be able to use this library you must obtain two other libraries as well.

The first is the org.relique.jdbc.csv package who contains a parser for SQL statement. Most parts of this project are anyway based on the development of the CVSJDBC driver. You can download this driver package from sourceforge.

http://csvjdbc.sourceforge.net/ is the place to look for.

Second you need a library who can read the XLS files. For this we use the jakarta poi package. We only have tested it with release 2.5 of POI. Available at http://jakarta.apache.org/poi/

The xlsjdbc driver is found here: http://sourceforge.net/project/showfiles.php?group_id=50004

Once you got those packages you can use the driver.

Usage

import java.sql.*;

public class DemoDriver
{
  public static void main(String[] args)
  {
    try
    {
      // load the driver into memory
      Class.forName("org.aarboard.jdbc.xls.XlsDriver");

      // create a connection. The first command line parameter is assumed to
      //  be the directory in which the .xls files are held
      Connection conn = DriverManager.getConnection("jdbc:aarboard:xls:" + args[0] );

      // create a Statement object to execute the query with
      Statement stmt = conn.createStatement();

      // Select the columns from sample.xls
      ResultSet results = stmt.executeQuery("SELECT * FROM sample");

      // dump out the results
      while (results.next())
      {
        System.out.println("ID= " + results.getString("ID") + "   NAME= " + results.getString("NAME"));
      }

      // clean up
      results.close();
      stmt.close();
      conn.close();
    }
    catch(Exception e)
    {
      System.out.println("Oops-> " + e);
    }
  }
}

Actually the "only" thing you can do is a select * from xlsfile statement.

There are no other sql statements or options supported, not even a simple where clausle. You see, there is plenty room for improvements.

Advanced options

Retrieving date fields can cause problems, when the date is entered as string
in excel and not as date. To solve this problem you have to specify the dateformat (as used by java.text.SimpleDateFormat)

For Switzerland we use this:

info.setProperty(org.aarboard.jdbc.xls.XlsDriver.STRING_DATE_FORMAT, "d.M.yyyy");
conn= DriverManager.getConnection(jdbcURL, info );

Other XLS JDBC solutions

There exists another opensource project who allows access to XLS files via JDBC.

http://xlsql.dev.java.net/

With xlsql you can use the full select syntax and even have write access to xls files.

But it requires a rather large setup before you can use it.

Project infos

The project ist hosted at sourceforge.

http://sourceforge.net/projects/xlsjdbc/ is the main project link.

For news you can subscribe to freshmeat

http://freshmeat.net/projects/xlsjdbc

Hosted at