Retreiving data from the database to an excel sheet file

[Sport Girl]

Hi everybody,

can anyone help me, please this is urgent.

I have the task to retreive data from a MySQL database to an excel sheet file.

I have done the code with no syntax errors but the fact is that i can't get the data retreived or written in the excel sheet file.

I really need ur help please.

[CODE]

#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use DBD::mysql;
use lib qw(.);
use Bugzilla;
use Bugzilla::Bug;
use Bugzilla::Util;
use Bugzilla::Constants;
use Time::Local;
use Spreadsheet::WriteExcel;


my $dbh = DBI->connect('DBI:mysql:database=bugs;host=localhost', '***', '***') or die "Connection Error";

my $Excelfile = "./Report.xls";

#create a new instance
my $excel = Spreadsheet::WriteExcel->new("$Excelfile");
my $worksheet = $excel->addworksheet("Bugs Report");
my $now = localtime time;
$worksheet->write(0, 0, "Report generated on :$now" );

my $stmt = "SELECT * FROM bugs ORDER BY bug_id";


my $sth = $dbh->prepare($stmt);
my @data;

$sth->execute() or die $dbh->errstr;

while ( @data = $sth->fetchrow_array()){
my $bug_id = $data[1];
my $assigned_to = $data[2];
my $bug_file_loc = $data[3];
my $bug_severity = $data[4];
my $bug_status = $data[5];
my $creation_ts = $data[6];
my $delta_ts = $data[7];
my $short_desc = $data[8];
my $op_sys = $data[9];
my $priority = $data[10];
my $product_id = $data[11];
my $rep_platform = $data[12];
my $reporter = $data[13];
my $version = $data[14];
my $component_id = $data[15];
my $resolution = $data[16];
my $target_milestone = $data[17];
my $qa_contact = $data[18];
my $status_whiteboard = $data[19];
my $votes = $data[20];
my $keywords = $data[21];
my $lastdiffed = $data[22];
my $everconfirmed = $data[23];
my $reporter_accessible = $data[24];
my $cclist_accessible = $data[25];
my $estimated_time = $data[26];
my $remaining_time= $data[27];
my $deadline = $data[28];
my $alias= $data[29];
my $row = 0;
my $col = 0;
foreach my $stmt (@data) {
$worksheet->write($row++, @data);
last;
}
}

$sth->finish();
$dbh->disconnect();

[FishMonger]

Please use the code tags so that your code will display with the proper indentation, assuming you do have it properly indented.

the fact is that i can't get the data retreived or written in the excel sheet fileIt's not real clear...are you having a problem retrieving the data from the DB, OR are you having a problem with writing the retrieved data to the spreadsheet?

Let's start by making a slight modification to the connect statement.my $dbh = DBI->connect('DBI:mysql:database=bugs;host=localhost', '***', '***',
{RaiseError => 1, PrintError => 0}) or die $DBI::errstr;
Then instead of writing to the spreadsheet, just print it. Once you've verified that you can retrieve the data, we'll look at the spreadsheet section.
my $sth = $dbh->prepare($stmt);
$sth->execute() or die $dbh->errstr; # the die statement isn't really needed due to the addition of the RaiseError.

if ( $sth->rows ) {
while ( my @data = $sth->fetchrow_array() ) {
print "@data\n";
}
print "Retrieved <$sth->rows> rows of data\n";
}
else {
print "No data retrieved\n";
}

[Sport Girl]

Hi everybody,

can anyone help me, please this is urgent.

I have the task to retreive data from a MySQL database to an excel sheet file.

I have done the code with no syntax errors but the fact is that i can't get the data retreived or written in the excel sheet file.

I really need ur help please.



#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use DBD::mysql;
use lib qw(.);
use Bugzilla;
use Bugzilla::Bug;
use Bugzilla::Util;
use Bugzilla::Constants;
use Time::Local;
use Spreadsheet::WriteExcel;


my $dbh = DBI->connect('DBI:mysql:database=bugs;host=localhost', '***', '***') or die "Connection Error";

my $Excelfile = "./Report.xls";

#create a new instance
my $excel = Spreadsheet::WriteExcel->new("$Excelfile");
my $worksheet = $excel->addworksheet("Bugs Report");
my $now = localtime time;
$worksheet->write(0, 0, "Report generated on :$now" );

my $stmt = "SELECT * FROM bugs ORDER BY bug_id";


my $sth = $dbh->prepare($stmt);
my @data;

$sth->execute() or die $dbh->errstr;

while ( @data = $sth->fetchrow_array()){
my $bug_id = $data[1];
my $assigned_to = $data[2];
my $bug_file_loc = $data[3];
my $bug_severity = $data[4];
my $bug_status = $data[5];
my $creation_ts = $data[6];
my $delta_ts = $data[7];
my $short_desc = $data[8];
my $op_sys = $data[9];
my $priority = $data[10];
my $product_id = $data[11];
my $rep_platform = $data[12];
my $reporter = $data[13];
my $version = $data[14];
my $component_id = $data[15];
my $resolution = $data[16];
my $target_milestone = $data[17];
my $qa_contact = $data[18];
my $status_whiteboard = $data[19];
my $votes = $data[20];
my $keywords = $data[21];
my $lastdiffed = $data[22];
my $everconfirmed = $data[23];
my $reporter_accessible = $data[24];
my $cclist_accessible = $data[25];
my $estimated_time = $data[26];
my $remaining_time= $data[27];
my $deadline = $data[28];
my $alias= $data[29];
my $row = 0;
my $col = 0;
foreach my $stmt (@data) {
$worksheet->write($row++, @data);
last;
}
}

$sth->finish();
$dbh->disconnect();

in fact i get in the first row of the excel file a data that is far from being the data of the database, here it is, the excel output:

Report generated on :Mon Sep 3 09:31:40 2007 2 2 8 12 15 6 9 5 8 2 6 2 19 12 5 22 17 18 14 14 14 14 21 14 28 14 14 2 15 14 6 28 23 14 14 9 9 21 13 8 8 13 14 22 15 23 23 14 22 28 28 28 14 15 15 22 2 14 14 5 9 14 14 14 5 14 14 12 14 14 14 14 28 14 14 8 14 14 14 8 14 14 14 14 14 14 28 8 22 15 28 28 15 28 14 21 21 21 14 14 8 14 14 5 14 14 14 15 21 14 14 14 34 12 14 2 2 2 2 14 23 14 6 14 21 21 14 35 14 19 13 19 19 19 19 19 26 21 14 19 17 18 9 31 32 32 14 31 14 35 32 12 22 14 2 2 30 30 31 30 32 13 13 23 23 23 14 14 28 28 28 30 28 28 9 28 28 32 2 31 28 2 6 2 21 30 9 14 14 30 30 30 30 14 14 30 28 28 31 12 14 28 31 9 9 9 2 9 31 30 30 32 2 20 6 2 30 2 17 17 19 18 26 23 19 26 19 26 19 26 11 11 20 33 33 33 20 20 20 20 20 20 33 20 33 30 9 30 32 30 31 9 15 15 30

[FishMonger]

You need to pass an array reference, not the array itself.
if ( $sth->rows ) {
while ( my $array_ref = $sth->fetchrow_arrayref ) {
$worksheet->write(++$row, $array_ref);
}
print "Retrieved <$sth->rows> rows of data\n";
}
else {
print "No data retrieved\n";
}

EDIT:
I forgot 1 thing. You're only specifying the row, but you should be specifying both row and column.
$worksheet->write(++$row, $col, $array_ref);

[Sport Girl]

So i guess the code must be like this if u'm not wrong;


#!/usr/bin/perl
use strict;
use warnings;
use DBI;

use DBD::mysql;
use lib qw(.);
use Bugzilla;
use Bugzilla::Bug;
use Bugzilla::Util;
use Bugzilla::Constants;
use Time::Local;
use Spreadsheet::WriteExcel;


my $dbh = DBI->connect('DBI:mysql:database=bugs;host=localhost', 'jinny', 'jinny',{RaiseError => 1, PrintError => 0}) or die $DBI::errstr;

my $Excelfile = "./Report.xls";

#create a new instance
my $excel = Spreadsheet::WriteExcel->new("$Excelfile");
my $worksheet = $excel->addworksheet("Bugs Report");
my $now = localtime time;
$worksheet->write(0, 0, "Report generated on :$now" );

my $stmt = "SELECT * FROM bugs ORDER BY bug_id";


my $sth = $dbh->prepare($stmt);


$sth->execute();
my $row;
my $col;
if($sth->rows){
while ( my $array_ref = $sth ->fetchrow_arrayref){

$worksheet->write(++$row,$col,$array_ref);
}
print "Retrieved <$sth->rows> rows of data\n";
}
else {
print "No data retrieved\n";
}

$sth->finish();
$dbh->disconnect();

[Sport Girl]

thank u very much u r the best

[Sport Girl]

but still have just one question if it doesn't bother you, how can i get the headers in the excel shett report i mean the fields names , for example bug_id , assigned_to......

[Sport Girl]

ok i have done it thanh u very much 4 ur help i really appreciate it