Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options

Convert SQL queries to XLS files

by dpavlin (Friar)
on Nov 06, 2008 at 14:00 UTC ( #721989=sourcecode: print w/ replies, xml ) Need Help??

Category: CGI Programming
Author/Contact Info Dobrica Pavlinušić,,
Description: 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, have multiple reports, each in it's own directory?

If you are interested in history of changes you can see them in subversion repository.

#!/usr/bin/perl -T
use warnings;
use strict;

=head1 NAME - convert sql queries on file system to Excel file

=head1 USAGE

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

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

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

You can also run script from command line, and it will produce
C<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
C<sql2xls.cgi> is installed.


Only required file is this script C<< sql2xls.cgi >>

If your server is configured to execute C<.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 creat
C<> file in same directory in which C<sql2xls.cgi> is with so
like this:

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

  $db_encoding     = 'utf-8';
  $xls_date_format = '';

  $debug = 1;


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

=head1 AUTHOR

Dobrica Pavlinusic,, L<


use Spreadsheet::WriteExcel;
use DBI;
use CGI::Carp qw(fatalsToBrowser);
use Encode qw/decode/;
use Data::Dump qw/dump/;

our $dsn      = 'DBI:Pg:dbname=';
our $database = 'template1';
our $user     = 'dpavlin';
our $passwd   = '';
our $path     = 'sql_reports.xls';

our $db_encoding     = 'iso-8859-2';
our $xls_date_format = '';

our $debug = 1;

my $sql_dir = $ENV{SCRIPT_FILENAME} || '.';
$sql_dir =~ s,/[^/]+$,,;

sub require_config {
    my $config_path = $1 if "$sql_dir/" =~ m/^(.+)$/; # untai
    warn "# using $config_path\n";
    require $config_path if -e $config_path;


my $reports_path = $ENV{PATH_INFO} || '';
$reports_path =~ s/\.\.//g; # some protection against path exploits
$reports_path ||= shift @ARGV; # for CLI invocation
$sql_dir .= "/$reports_path" if -e "$sql_dir/$reports_path";


warn "SQL queries from $sql_dir\n";

opendir(DIR, $sql_dir) || die "can't opendir $sql_dir: $!";
my @sql_files = sort grep { /\.sql$/i && -f "$sql_dir/$_" } readdir(DI
closedir DIR;

my $workbook;
    # use as cgi script
    print "Content-type: application/\n\n";
    $workbook = Spreadsheet::WriteExcel->new("-");
} else {
    # Create a new Excel workbook
    $workbook = Spreadsheet::WriteExcel->new( $path );
    warn "Creating XLS file $path\n";

my $date_format = $workbook->add_format(num_format => $xls_date_format

our $dbh;
sub use_database {
    $dbh->disconnect if $dbh;
    my $database = shift || return;
    print STDERR "## connect to $database\n" if $debug;
    $dbh = DBI->connect($dsn . $database,$user,$passwd, { RaiseError =
+> 1, AutoCommit => 0 }) || die $DBI::errstr;
    if ( $db_encoding ) {
        if ( $dsn =~ m{Pg} ) {
            $dbh->do( qq{ set client_encoding = '$db_encoding'; } );
        } elsif ( $dsn =~ m{mysql} ) {
            $dbh->do( qq{ set names '$db_encoding'; } );
        } else {
            warn "Don't know how to set encoding to $db_encoding for $

use_database( $database );

sub _c {
    return shift unless $db_encoding;
    return decode( $db_encoding, shift );

foreach my $sql_file (@sql_files) {

    my $sheet_name = $sql_file;
    $sheet_name =~ s/\d+[_-]//;
    $sheet_name =~ s/_/ /g;
    $sheet_name =~ s/\.sql//;

    # Add a worksheet
    warn "# clipping sheet name '$sheet_name' to 31 char limit\n" if l
+ength $sheet_name > 31;
    my $worksheet = $workbook->addworksheet( substr($sheet_name,0,31) 

    print STDERR "working on $sql_file\n" if ($debug);

    open(SQL,"$sql_dir/$sql_file") || die "can't open sql file '$sql_d
+ir/$sql_file': $!";
    my $comment = '';
    my $full_sql = "";
    while(<SQL>) {
        if (/^\\c\s+(\S+)/) {
            use_database( $1 );
        } elsif (/^--(.+)/) {
        } else {
            $full_sql.= ' ' . $_;

    $full_sql =~ s/\s\s+/ /gs;
    $full_sql .= ';' unless $full_sql =~ m/;\s*/s;

    print STDERR "sql: $full_sql\ncomment: $comment\n" if ($debug);

    my $row = 0;

    if ($comment) {

        #  Add and define a format
        my $fmt_comment = $workbook->addformat();    # Add a format

        $comment =~ s/^\s+//;
        $comment =~ s/\s+$//;

        $worksheet->write($row, 0, _c($comment), $fmt_comment);

    my $fmt_header = $workbook->addformat();    # Add a format

    foreach my $sql ( split(/;/, $full_sql ) ) {

        warn "SQL: $sql\n";

        my $sth = $dbh->prepare($sql);

        next unless $sth->{NAME}; # $sth->rows doesn't work for insert
+ into with MySQL

        my @types = eval {
            map { $dbh->type_info($_) ? $dbh->type_info($_)->{TYPE_NAM
+E} : '?' } @{ $sth->{TYPE} };

        for(my $col=0; $col<=$#{ $sth->{NAME} }; $col++) {
            $worksheet->write($row, $col, ${ $sth->{NAME} }[$col], $fm

        while (my @row = $sth->fetchrow_array() ) {
            for(my $col=0; $col<=$#row; $col++) {
                my $data = $row[$col];
                next unless defined $data;
                if ( $types[$col] && $types[$col] =~ m/^date/i ) {
                    $data .= 'T' if $data =~ m/^\d\d\d\d-\d\d-\d\d$/;
                    $data =~ s/^(\d\d\d\d-\d\d-\d\d)\s(\d\d:\d\d:\d\d)
                    warn "## by type datetime $data\n" if $debug;
                    $worksheet->write_date_time( $row, $col, $data, $d
+ate_format );
                } elsif ( $data =~ s/^(\d\d\d\d-\d\d-\d\d)[\sT](\d\d:\
+d\d:\d\d)$/$1T$2/ ) {
                    warn "## heuristic date time: $1T$2\n" if $debug;
                    $worksheet->write_date_time( $row, $col, "$1T$2", 
+$date_format );
                } else {
                    $worksheet->write($row, $col, _c( $data ) );

        $row++; # separete queries by one row




Comment on Convert SQL queries to XLS files
Download Code
Replies are listed 'Best First'.
Re: Convert SQL queries to XLS files
by jmcnamara (Monsignor) on Nov 06, 2008 at 14:12 UTC
    Very nice. ++

    If you don't mind I'll add this to the Spreadsheet::WriteExcel examples directory in the next release.


      Yeah, sure! Spreadsheet::WriteExcel is workhorse behind this CGI which does just REST API to SQL files :-)

Re: Convert SQL queries to XLS files
by Corion (Pope) on Nov 06, 2008 at 16:49 UTC

    Also see Querylet and CGI::Querylet, which implement a small "language" for reports, and can output to the console, to Excel and XLS, and which also can run as CGI.

      Thanks, it looks interesting for cases where I would want to mungle data after query. My use-case for this script is more something like: you already have SQL query files somewhere and want to export them quickly with single CGI script with nice REST API.


Back to Code Catacombs

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: sourcecode [id://721989]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (5)
As of 2015-12-01 04:28 GMT
Find Nodes?
    Voting Booth?

    What would be the most significant thing to happen if a rope (or wire) tied the Earth and the Moon together?

    Results (793 votes), past polls