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
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.
Only required file is this script
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
$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;
There is none. Use apache auth modules if you need it.