Take a SQL query and spit out results as Excel in a browser window

[David Williams]

Hello all!

I just thought I would share a cool utility I wrote this afternoon.  The
attached program takes an URL-escaped sql query as an argument and then
executes the query and presents the result set in a spreadsheet.  Please go
easy on my coding as I know there are some kludgy things in there. Enjoy!

Please feel free to email me at mailto:[EMAIL PROTECTED] with
your critique.

Have Fun!

[-
$escmode=0;

use DBI;
use URI::Escape;
$dbh = DBI->connect(your connect string here);
$dbh->{LongTruncOk} = 1;
$dbh->{LongReadLen} = 2**16-2;

#------------- $fdat{sql} may contain multiple statments, split -#
$sql =  uri_unescape($fdat{sql});
$heading = $sql;
$heading =~ s/^\s|"//g;
$heading =~ s/\n|\r/ /g;
$heading = substr($heading,0,27) . "...";

$sql =~ s/;[\s]*$//;
@sql = split (";", $sql);

foreach $sql (@sql) {
        chomp;
        $sql =~ s/"//g;
        %fields = {};
        $fields{sql} = $sql;
        if ($sql =~ /^[\s]*select/i) {          # it will return rows
                eval { $sth = $dbh->prepare(qq{$sql}) };
                if ($@) {
                        $fields{error} = $@;
                } else {
                        if ($dbh->errstr) {
                                $fields{error} = $dbh->errstr;
                        } else {
                                eval { $sth->execute; };
                                if ($@) {
                                        $fields{error} = $@;
                                } else {
                                        eval { $fields{tableref} = 
$sth->fetchall_arrayref };
                                        if ($@) {
                                                $fields{error} = $@;
                                        }
                                        eval { $fields{field_count} = 
$sth->{NUM_OF_FIELDS} };
                                        if ($@) {
                                                $fields{error} = $@;
                                        }
                                        eval { $fields{field_names_ref} = $sth->{NAME} 
};
                                        if ($@) {
                                                $fields{error} = $@;
                                        }
                                }
                        }
                        if ($sth) {$sth->finish}
                }
        }
        push (@output, {%fields} );
}
#------------------ Now add results to the spreadsheet ---------#

use Spreadsheet::WriteExcel;
my $workbook  = Spreadsheet::WriteExcel->new("/tmp/$$.xls");

my $worksheet = $workbook->addworksheet($heading);

my $format = $workbook->addformat();
$format->set_text_wrap();

foreach $record (@output) {
        $i=0;
        while ($i < $record->{field_count}) {
                if ( length(${$record->{field_names_ref}}[$i]) > $maxwidths{$i} ) {
                        $maxwidths{$i} = length(${$record->{field_names_ref}}[$i]) + 1;
                }
                $i++;
        }

        $i=0;
        while ( $i <= $#{$record->{tableref}} ) {
                $j=0;
                while ($j < $record->{field_count}) {
                        if ( ${$record->{tableref}[$i]}[$j] =~ /\n/ ) {
                                @lines = split ("\n", ${$record->{tableref}[$i]}[$j]);
                                foreach $line (@lines) {
                                        if ( length($line) > $maxwidths{$j} ) {
                                                $maxwidths{$j} = length($line);
                                        }
                                }
                        } else {
                                if ( length(${$record->{tableref}[$i]}[$j]) > 
$maxwidths{$j} ) {
                                        $maxwidths{$j} = 
length(${$record->{tableref}[$i]}[$j]);
                                }
                        }
                        $j++;
                }
                $i++;
        }
}

while ( ($col, $width) = each (%maxwidths) ) {
        $worksheet->set_column($col, $col, $width);
}

foreach $record (@output) {
        $i=0;
        while ($i < $record->{field_count}) {
                $worksheet->write(0, $i, lc(${$record->{field_names_ref}}[$i]), 
$format);
                $i++;
        }

        $i=0;
        while ( $i <= $#{$record->{tableref}} ) {
                $j=0;
                while ($j < $record->{field_count}) {
                        ${$record->{tableref}[$i]}[$j] =~ s/\r//g;
                        $worksheet->write($i+1, $j, ${$record->{tableref}[$i]}[$j], 
$format );
                        $j++;
                }
                $i++;
        }
}

$workbook->close;

$scalar = `cat /tmp/$$.xls`;
unlink "/tmp/$$.xls";

$dbh->disconnect;

$http_headers_out{'Content-type'} = "application/vnd.ms-excel";
-]
[+ $scalar +]


---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]