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

[David Williams <da...@conceptionstore.com>]

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:webmaster@conceptionstore.com 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: embperl-unsubscribe@perl.apache.org
For additional commands, e-mail: embperl-help@perl.apache.org