sqlsheet

JDBC Driver for Excel Spreadsheets

by Patrick Calahan (http://www.pcal.net)

Licensed under the Apache License, Version 2.0

download | javadocs

Overview

SQLSheet is a JDBC driver which allows you to interact with Microsoft Excel
spreadsheets using SQL statements.

Features:

  • Pure Java (no native components)
  • Fast (operates directly on the spreadsheet, does not rely on an in-memory database)
  • Read and write operations
  • PreparedStatement support

The JDBC driver class name is:


    net.pcal.sqlsheet.XlsDriver

Considerations

SQLSheet relies on the excellent Apache POI

library to actually manipulate the spreadsheets - all the driver does it
parse SQL and translate it into POI operations.
If you simply need to manipulate spreadsheets from Java code,
you are probably better off using POI directly.

SqlSheet is primarily useful in the context of other SQL-oriented tools
(such as ETL scripting languages) which operate via JDBC. SqlSheet
allows you to introduce Excel connectivity into those environments in a way
that is completely transparent to them.

Requirements

SQLSheet requires that the following libraries be on the classpath:

Concept Mapping

The following table shows how SQLSheet maps database concepts to
Excel spreadsheets:

SQL XLS
Database Instance Excel Workbook File
Table Sheet (Tab)
Column Sheet Column, column names in row 0
Row Worksheet Row

SQL Support

SqlSheet supports a limited subset of SQL for Excel operations as listed
below. Support for more operations is planned in future versions.

CREATE TABLE sheet_name (column_name type, …)

Used to create sheets in the workbook. The
name of the table is the name of the sheet. The first row of the new
sheet will be filled in with the colum names.
Column types are used to style the worksheet cells appropriately (as date,
decimal).

INSERT INTO sheet_name (column1, …) VALUES (value1, …)

Inserts a new row into the sheet using the given values for the given columns.
Column names are determined by looking at the first row of the sheet.
PreparedStatement parameters are accepted for values.

SELECT * FROM sheet_name

Returns a result set containing all of the rows and columns in the named sheet.

Usage

SqlSheet JDBC connections are established in much the same way as
any other sort of connection. The main difference is that the
connection URL is of the form


jdbc:xls:(URL_TO_MY_XLS_FILE)

where XLS_URL is any valid URL to an XLS file. For example,

  jdbc:xls:file:///C:/Documents/MySheet.xls

or

  jdbc:xls:http://www.someserver.com/sheetdir/MySheet.xls

Here is a simple example that opens a JDBC connection to MySheet.xls:


try {
  Class.forName("net.pcal.sqlsheet.XlsDriver").newInstance();
} catch (ClassNotFoundException cnfe) { /* ... */ }
// ...
String url = "jdbc:xls:file:///C:/Documents/MySheet.xls";
java.sql.Connection db_connection = DriverManager.getConnection(url);

If a file: URL is used and the file does not exist,
a new workbook will be created.

Any valid URL can be used to connect to the target workbook.
However, if the URL scheme is anything other than file:, the
spreadsheet connection will be read-only; any SQL operations that attempt
to modify the sheet will result in a SQLException being thrown.

Saving Changes

Any new workbooks, sheets, or rows created by a Connection will not be
saved to disk until the connection is closed.

Known Limitations

As of this writing, the underlying JSqlParser does not understand
quoted column or alias names.