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;
-
Are you posting in the right place? Check out Where do I post X? to know for sure.
-
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
<u> <ul>
-
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
intervention).
-
Want more info? How to link
or How to display code and escape characters
are good places to start.