Re: SQL Database table to Perl Spreadsheet script

[Generoso]

All you need is to change the HTML code for OLE excel code.

#!/usr/bin/perl

use DBI;
use strict;
use warnings;
use OLE;
use Win32::OLE::Const "Microsoft Excel";
my $dbh = DBI->connect('dbi:mysql:database:3306','prithvi','prithvi') 
+or die "Cannot connect: $DBI::errstr\n";
my @sql =();
$sql[0] = qq[SELECT AccNum as Account, max(date) as Date, Type, count(
+*) as Total_No, sum(Amount) as Amount
FROM transaction GROUP BY AccNum, Type
union
SELECT AccNum, max(date),'Balance', count(*), sum(if(type='Debit',-1*A
+mount,Amount))
FROM transaction GROUP BY AccNum;];
$sql[1] = qq[SELECT AccNum,Type,Amount,ChequeNo,DDNo FROM transaction;
+];
$sql[2] = qq[desc transaction;];
my $excel = CreateObject OLE "Excel.Application";
$excel -> {Visible} = 1;
my $workbook = $excel    -> Workbooks -> Add;
my $i =0;
foreach (@sql) { $i++;
    my $sheet    = $workbook -> Worksheets("Sheet$i");
    $sheet                -> Activate;
    my $row = 1;
    my $col = 1;
    my $l = $_;
    $l =~ s/\n/<br>/g;
    print ' '.$l.'</p>';
    my $tran_cur = $dbh->prepare($_)or die "Cannot prepare: " . $DBI::
+errstr;
    $tran_cur->execute or die "Cannot execute: " . $DBI::errstr;
    foreach (@{$tran_cur->{NAME}})
        {$sheet->Cells($row,$col++)->{'Value'} = $_;}
    $col = 1;
    while( my @data = $tran_cur->fetchrow_array)
        {$row++;
        foreach (@data)
            { $sheet->Cells($row,$col++)->{'Value'} = $_;}
        $col = 1;
    }
}
$dbh->disconnect;
#$workbook -> SaveAs ("C:\\file_name.xls");
#$excel    -> Quit;