MySQL database to excel spreadsheet report

**miller** · Aug 22 '07, 05:00 PM

Hello Sport Girl,

Your code, as you originally posted it, contained a lot of unrecognizable characters. I'm guessing that they may be unicode or some other special character set, and that is leading to problems with running your script on the server. I suspect that the problem is either in the editor that you're using or the method in which you're transferring the file to your web server.

I've translated all the special character to their likely intended character in your above post. I've also run your code through Perl Tidy in order to clean up your formatting. You may now notice an error in your code where you have two "else" statements.

Anyway, I suggust that you figure out what is causing your character set issue before trying to do anything else. That is likely leading to problems throughout your code base, not just with this one script.

- Miller

**Sport Girl** · Aug 27 '07, 09:25 AM

Hi Miller, thank u very much 4 trying to helping me. I appreciate it very much.

In fact i have done what you have told me and changed the character set to ASCII in my local machine where i am supposed to do the testing but the error 500 Internal Server Error is still persisting.
I don't know if the fact is that i am not given permissions or priveleges to change in the configuration as a trainee, so they do not take effects or it's just i can't test my new code for security reasons. I have talked to my consultant about it but....
Anyway i'll post the new code , the error on the internet page is: Internal Server Error
The server encountered an internal error or misconfiguratio n and was unable to complete your request.

and on unix when trying to do ./scriptname.cgi is:
Unrecognized character \x93 at ./scriptname.cgi line 11.

I have done under unix "Setup- file transfer" and the following message appears:
Unable to transfer file: Host does not respond- Could not connect.

But I am logged in in unix with a username and password(i am using Reflection - Host-Unix and Digital) and i am connected in MySQL to the database( i am using Reflection - Reflection X) and i am connected to the FTP in a email address( i am using Reflection - FTP Client). I checked the ASCII character set in the Unix and FTP windows.

So could it be security or configuration reasons or what else should i do?

Code:

#!/usr/bin/perl -W
use DBI;
use DBD::mysql;
use strict;
use File::Copy;
use File::Path;
use File::Basename;
use Spreadsheet::WriteExcel::Big;
use Data::Dumper;
my $worksheet = ['Report.xls'];
my $dbh = DBI->connect(DBI:mysql:dbname=bugs:localhost, carole, carole, { RaiseError=>1, PrintError=>0, AutoCommit=>0}) or die(Error:DBI::errstr);
my $excel_file = ‘C:/Perl/Report.xls’;
$query =qq{SELECT
b.bug_id, b.bug_file_loc, b.bug_severity, b.bug_status, b.creation_ts, b.delta_ts, b.short_desc, b.op_sys, b.priority, b.rep_plarform, b.version, b.component_id, b.resolution, b.target_milestone, b.status_whiteboard, b.votes, b.keywords, b.last_diffed,
b.everconfirmed, b.reporter_accessible, b.cclist_accessible, b.estimated_time, b.remaining_time, b.deadline,  b.alias, pf.login_name, pd.name
FROM bugs b, profiles pf, products pd
WHERE bugs.assigned_to = profiles.user_id
AND bugs.product_id = products.id
ORDER BY bug_id;};
my $sth = $dbh -> prepare($query);
$sth ->execute() or die $dbh ->errstr;
while ( my $ref = $sth->fetchrow_hashref()) {
    print $ref ->{
'bug_id', 'assigned_to', 'bug_file_loc', 'bug_severity', 'bug_status', 'creation_ts', 'delta_ts', 'short_desc', 'op_sys', 'priority', 'product_id', 'rep_plarform', 'version', 'component_id', 'resolution', 'target_milestone', 'status_whiteboard', 'votes',
 'keywords', 'last_diffed', 'everconfirmed', 'reporter_accessible', 'cclist_accessible', 'estimated_time', 'remaining_time', 'deadline',  'alias', 'login_name', 'name'}, "\n";
}
//-e checks to see if the file exists
if (-e $excel_file)
{
#open Excel file
my $Book = $Excel->Workbooks->Open($excel_file);
$exists="true";
}else{
$Book = $Excel->Workbooks->Add();
$exists = "false";
}
if($exists eq "true"){
  $previousSheet = $Book->ActiveSheet;
  $currentSheet = $Book->Worksheets->Add();
  $Book->ActiveSheet->{Name}="Report";
}else{
  $previousSheet = 0;
}
#create the spreadsheet
$excel -> output();
#sort data of worksheet – ASC or DESC
$excel -> sort_data(‘Report.xls’,0, ‘ASC’);
#add headers to 'Report'
$excel -> set_headers('Report.xls', [qw/Bugs report/]);
#print sheet-names
print join( , ,$excel -> sheets()), \n;
#get the result as a string
my $spreadsheet = $excel -> output_as_string();
#print result into a file and handle error
$excel->output_to_file(Report.xls) or die $excel -> errstr();
if($exists eq "true"){
  $Book->Save();
}else{
  $Book->SaveAs("$excel_file") or die $!;
}
$sth -> finish();
$dbh -> disconnect();

**Sport Girl** · Aug 30 '07, 10:26 AM

Hi everybody, just want to say that i have resolved the problem of the character set but i have syntax errors:

can somebody help me how can i rerieve data from multilple database tables cause my query syntax is wrong: i need to retrieve data from the tables bugs, profiles and products from the database bugs.

Please can somebody help, i really need it?

Code:

#!/usr/bin/perl -W
use DBI;
use DBD::mysql;
use strict;
use File::Copy;
use File::Path;
use File::Basename;
use Spreadsheet::WriteExcel::Big;
use Data::Dumper;
my $worksheet = ['Report.xls'];
my $dbh = DBI->connect("dbi:mysql:database=bugs;host=localhost") or die "Connection Error";
my $excel_file = 'C:/Perl/Report.xls';
$query =qq{SELECT
b.bug_id, b.bug_file_loc, b.bug_severity, b.bug_status, b.creation_ts, b.delta_ts, b.short_desc, b.op_sys, b.priority, b.rep_plarform, b.version, b.component_id, b.resolution, b.target_milestone, b.status_whiteboard, b.votes, b.keywords, b.last_diffed,
b.everconfirmed, b.reporter_accessible, b.cclist_accessible, b.estimated_time, b.remaining_time, b.deadline,  b.alias, pf.login_name, pd.name
FROM bugs b, profiles pf, products pd
WHERE bugs.assigned_to = profiles.user_id
AND bugs.product_id = products.id
ORDER BY bug_id;};
my $sth = $dbh -> prepare($query);
$sth ->execute() or die $dbh ->errstr;
while ( my $ref = $sth->fetchrow_hashref()) {
    print $ref ->{
'bug_id', 'assigned_to', 'bug_file_loc', 'bug_severity', 'bug_status', 'creation_ts', 'delta_ts', 'short_desc', 'op_sys', 'priority', 'product_id', 'rep_plarform', 'version', 'component_id', 'resolution', 'target_milestone', 'status_whiteboard', 'votes',
 'keywords', 'last_diffed', 'everconfirmed', 'reporter_accessible', 'cclist_accessible', 'estimated_time', 'remaining_time', 'deadline',  'alias', 'login_name', 'name'}, "\n";
}
if (-e $excel_file)
{
#open Excel file
my $Book = Workbooks->Open($excel_file);
$exists="true";
}else{
$Book =Workbooks->Add();
$exists = "false";
}
if($exists eq "true"){
  $previousSheet = $Book->ActiveSheet;
  $currentSheet = $Book->Worksheets->Add();
  $Book->ActiveSheet->{Name}="Report";
}else{
  $previousSheet = 0;
}
#create the spreadsheet
$excel_file -> output();
#sort data of worksheet  ASC or DESC
$excel -> sort_data('Report.xls',0,'ASC');
#add headers to 'Report'
$excel -> set_headers('Report.xls', [qw/Bugs report/]);
#get the result as a string
my $spreadsheet = $excel_file -> output_as_string();
#print result into a file and handle error
$excel->output_to_file('Report.xls') or die $excel -> errstr();
if($exists eq "true"){
  $Book->Save();
}else{
  $Book->SaveAs("$excel_file") or die $!;
}
$sth -> finish();
$dbh -> disconnect();

**numberwhun** · Aug 30 '07, 12:20 PM

Unfortunately, I don't have tons of experience with DBI or SQL so I would have to look into your question. But, I did want to add this, instead of using the "-W" or "-w" switch to the shebang line, you should really use the "use warnings;" pragma instead. Just standard best practice is all.

Regards,

Jeff

**Sport Girl** · Aug 30 '07, 01:24 PM

Hi Jeff,
thank u for ur cooperation .I appreciate it very much.
Actually i have made a step in syntax errors but still there is something to do:

in fact i can't get the data retrieved from the bugs, products and profiles tables written in the excel sheet file.
How can i get the data retreived written to the excel sheet unless from the table bugs?

I'll give details:

the database name is bugs

the fields in the table bugs are:

bug_id
bug_file_loc
$bug_severity
bug_status
creation_ts
delta_ts
short_desc
op_sys
priority
rep_platform
version
component_id
resolution
target_mileston e
status_whiteboa rd
votes
keywords
lastdiffed
everconfirmed
reporter_access ible
cclist_accessib le
estimated_time
remaining_time
deadline
my $alias

The field in the profiles table to be retrieved: login_name
The field in the products table to be retreived is : name

Can u give me a hand with this one?

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', 'jinny', 'jinny') or die "Connection Error";

my $Excelfile = "/tmp/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 b.bug_id, b.bug_file_loc, b.bug_severity, b.bug_status, b.creation_ts, b.delta_ts, b.short_desc, b.op_sys, b.priority, b.rep_platform, b.version, b.component_id, b.resolution, b.target_milestone, b.status_whiteboard, b.votes, b.keywords, b.lastdiffed,
b.everconfirmed, b.reporter_accessible, b.cclist_accessible, b.estimated_time, b.remaining_time, b.deadline,  b.alias, pf.login_name, pd.name
FROM bugs b, profiles pf, products pd
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 $bug_file_loc = $data[2];
    my $bug_severity = $data[3];
    my $bug_status = $data[4];
    my $creation_ts = $data[5];
    my $delta_ts = $data[6];
    my $short_desc = $data[7];
    my $op_sys = $data[8];
    my $priority = $data[9];
    my $rep_platform = $data[10];
    my $version = $data[11];
    my $component_id = $data[12];
    my $resolution = $data[13];
    my $target_milestone = $data[14];
    my $status_whiteboard = $data[15];
    my $votes = $data[16];
    my $keywords = $data[17];
    my $lastdiffed = $data[18];
    my $everconfirmed = $data[19];
    my $reporter_accessible = $data[20];
    my $cclist_accessible = $data[21];
    my $estimated_time = $data[22];
    my $remaining_time= $data[23];
    my $deadline = $data[24];
    my $alias= $data[25];
    my $login_name = $data[26];
    my $name = $data[27];
    my $row = 0;
    my $col = 0;
    foreach my $stmt (@data) {
        $worksheet->write($row++, @data);
        last;
    }
}
 
$sth->finish();
$dbh->disconnect();

MySQL database to excel spreadsheet report

MySQL database to excel spreadsheet report

Comment

Comment

Comment

Comment

Comment