Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

SQL query to Excel worksheet in Embperl

by Stegalex (Chaplain)
on Jun 08, 2002 at 01:10 UTC ( #172713=snippet: print w/replies, xml ) Need Help??
Description: Take a SQL query as a paramater and produce an Excel spreadsheet containing the results. Uses HTML::Embperl and Spreadsheet::WriteExcel
[-
$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]) > $maxwidt
+hs{$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 +]
Replies are listed 'Best First'.
Re: SQL query to Excel worksheet in Embperl
by kschwab (Vicar) on Jun 16, 2002 at 16:03 UTC
    ++Stegalex...very cool, and immediatly useful.
Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: snippet [id://172713]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (6)
As of 2022-12-08 03:55 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?