SQL2XLS: create Excel sheets from bunch of SQL files

Wouldn't it be nice to have a CGI script which would convert bunch of SQL queries into XLS file on-the-fly? And while we are at it, let's have multiple reports, each in it's own directory?

In a sense, it's simple REST API to SQL files on disk which produce Excel files. I first wrote something like this back in 2002, but until now, I didn't have subversion repository for it or announced it to the world.

Each file in current directory which ends in *.sql will be converted to Excel sheet. If you want to have specific order, you can prefix filenames with numbers which will be striped when creating sheet names.

Comments in sql files (lines beginning with --) will be placed in first line in bold.

To specify database on which SQL query is executed \c database syntax is supported.

You can also run script from command line, and it will produce sql_reports.xls file.

If run within directory, it will use files in it to produce file.

When called as CGI, directory name can be appended to name of script to produce report for any sub-directory within directory where sql2xls.cgi is installed.

INSTALLATION

Only required file is this script sql2xls.cgi

If your server is configured to execute .cgi files, you can drop this script anywhere, but you can also add something like

   ScriptAlias /xls-reports /srv/SQL2XLS/sql2xls.cgi

in Apache's virtual host configuration to get nice URLs

To configure default database, user, password and other settings create config.pl file in same directory in which sql2xls.cgi is with something like this:

  $dsn      = 'DBI:mysql:dbname=';
  $database = 'database';
  $user     = 'user';
  $passwd   = 'password';
  $path     = 'sql_reports.xls';

  $db_encoding     = 'utf-8';
  $xls_date_format = 'dd.mm.yyyy';
  $debug = 1;

SECURITY

There is none. Use apache auth modules if you need it.