Relational databases are great for storing the detailed transactional data generated by businesses, and SQL is a great tool
for extracting, collating, sorting and summarising this data. As for presenting the results in a convenient form to a business user,
there are lots of commercial reporting tools available. But sometimes business users simply want an extract of the data in a
portable form so that they can manipulate it themselves.
Spreadsheets can provide the portability and convenience, and are a natural way of presenting tabular data,
but how to get the data from the database into Excel?
"SQLtoXLS" is a stand-alone Java program capable of
extracting data from any SQL database into an Excel workbook (an XLS file).
It was originally written to run unattended as part of a scheduled task, generating
regular database reports which can then be mailed as an attachment to a number
of recipients. The primary input is a 'query definition file' - an XML file
which defines a set of database connection parameters and also one or
more SQL queries to be run against this database. The primary output is an
Excel 97/2000 workbook containing a number of worksheets, each one containing
the results of one of these SQL queries.
Because this is a Java program it can be run on any computer for which
there's a Java Runtime Environment, therefore including both Windows and Unix
platforms. It can connect to any database which provides a standard JDBC driver.
It does not require an installation or a license for the Excel program on the
runtime platform. The resulting workbook can be viewed with any program capable
of reading Excel 97/2000 workbooks, including the free Excel viewer available
from the Microsoft web site.
All of the required supporting components are freely available
and distributable under public or open source licenses. JDBC drivers
for specific databases have their own licensing, but if you have the
database you're licensed to use the equivalent driver from the
A Java Runtime Environment (JRE) is required and can be
downloaded from http://java.sun.com/j2se. Note that only
the JRE is required, not the full Java Development Kit (JDK).
The POI package available from http://jakarta.apache.org/poi provides an
API allowing Java programs to write the OLE2 Compound Documents of
which the Excel files are an example. The contributors to the Open
Source POI project have done the hard work and this program couldn't
have been written as quickly or as simply without it.
This program was tested against against Oracle v8 and v9 using
Oracle's own JDBC drivers, downloadable from the Oracle web site. It
was tested against SQL Server v7.0 using JDBC drivers from http://jtds.sourceforge.net (which
also has drivers for Sybase). Drivers for SQL Server 2000/2003
are available from the Microsoft web site.
An example query definition
<Query tabref="Recent Hires">
select'Employees hired since ' || TO_CHAR(SYSDATE-30,'DD-Mon-YYYY')
select 'Report Date: ' || TO_CHAR(SYSDATE,'DD-Mon-YYYY')
select emp.empno, emp.ename, emp.job, emp.hiredate,
emp.sal salary, emp.comm commission, dept.dname dept
from emp, dept
where emp.deptno = dept.deptno
and emp.hiredate > sysdate-30
How this is interpreted
The program will create a worksheet to hold the results of this query, and will label the worksheet tab 'Recent Hires'.
The contents of the HeadingQuery node will be interpreted as an SQL statement to be run in order to generate a heading. Note that this particular example
will return two 'rows' from the database which are displayed as two rows of heading in the worksheet.
Similarly, the SQL query defined within the QueryText node will be run to generate the results.
Please note that this program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License version 2.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
The zip file contains an executable jar file, a self-documenting example of a query definition file,
an explanatory ReadMe and the source code.