#!/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*Amount,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;