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.
| Required Components |
|
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 database vendor. 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. Logging is done using log4j, available from http://logging.apache.org/log4j An XML parser is required such as the Xerces parser, available from http://xml.apache.org/xerces-j 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"> <HeadingQuery> select'Employees hired since ' || TO_CHAR(SYSDATE-30,'DD-Mon-YYYY') from dual union select 'Report Date: ' || TO_CHAR(SYSDATE,'DD-Mon-YYYY') from dual </HeadingQuery> <QueryText> 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 </QueryText> </Query> |
| 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. |
| 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. Download zip file |