By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,849 Members | 2,169 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,849 IT Pros & Developers. It's quick & easy.

MySQL database to excel spreadsheet report

P: 42
Hi everybody ,

i have the task of developing in Perl a script that retrieves data from 3 tables ( bugs, profiles, products) from a MySQL database called bugs and display them in an excel sheet report.

I always get when trying to test it " 500 Internal Server Error - The server encountered an internal error or misconfiguration and was unable to complete your request."

Can somebody help me please.



//connect to MySQL database

#!/usr/bin/perl –w
//# -*- Mode: perl; indent-tabs-mode: nil -*-

//use lib qw(.);

//use Bugzilla;
//use Bugzilla::Bug; # EmitDependList

//use Bugzilla::Constants; # LOGIN_*
//use Bugzilla::User; # UserInGroup

//require "globals.pl";



use DBI;
use strict;
use warnings;

use Spreadsheet::SimpleExcel;



//my $cgi = Bugzilla->cgi;
//my $dbh = Bugzilla->dbh;
//my $template = Bugzilla->template;
//my $vars = {};



#data
my @data1 = (['Row1Col1', 'Row1Col2'],
[Row2Col1', 'Row2Col2']);

my $worksheet = ['Report.xls', {-data => \@data1}];

print “Available Database Drivers:\n\n”;
print join(“\n”, DBI - > availableDrivers()), “\n\n”;

//my %attr => (RaiseError => 0);

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’;

//-e checks to see if the file exists

if (-e “$excel_file”)
{

#open Excel file
my $Book = $Excel -> Workbooks -> Open(“$excel_file”);
}else{

//paste code here to create a new file
#create a new instance
my $excel = Spreadsheet::SimpleExcel -> new(-worksheets => [$worksheets]);


//$excel ->{Visible}=1;

#to create a file
my $filename = 'Report.xls';
my $excel = Spreadsheet::SimpleExcel -> new (-filename =>$filename);


//$excel -> { SheetsInNewWorkBook} =1;

//$workbook = $excel -> Workbooks ->Add();

//$worksheet = $ Workbook -> Worksheets(1);
//$worksheet -> {Name} = “Report”;

#add worksheets
$excel -> add_worksheet('Report.xls');

my $sth = $dbh -> prepare(‘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;’);

$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";

}

$sth -> finish();

#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();
$workbook -> SaveAs($excel_file);

$dbh -> disconnect();
Aug 17 '07 #1
Share this Question
Share on Google+
1 Reply


numberwhun
Expert Mod 2.5K+
P: 3,503
Hi everybody ,

i have the task of developing in Perl a script that retrieves data from 3 tables ( bugs, profiles, products) from a MySQL database called bugs and display them in an excel sheet report.

I always get when trying to test it " 500 Internal Server Error - The server encountered an internal error or misconfiguration and was unable to complete your request."

Can somebody help me please.


Expand|Select|Wrap|Line Numbers
  1. //connect to MySQL database
  2.  
  3. #!/usr/bin/perl –w
  4. //# -*- Mode: perl; indent-tabs-mode: nil -*-
  5.  
  6. //use lib qw(.);
  7.  
  8. //use Bugzilla;
  9. //use Bugzilla::Bug;       # EmitDependList
  10.  
  11. //use Bugzilla::Constants; # LOGIN_*
  12. //use Bugzilla::User;      # UserInGroup
  13.  
  14. //require "globals.pl";
  15.  
  16.  
  17.  
  18. use DBI;
  19. use strict;
  20. use warnings;
  21.  
  22. use Spreadsheet::SimpleExcel;
  23.  
  24.  
  25.  
  26. //my $cgi = Bugzilla->cgi;
  27. //my $dbh = Bugzilla->dbh;
  28. //my $template = Bugzilla->template;
  29. //my $vars = {};
  30.  
  31.  
  32.  
  33. #data
  34. my @data1 = (['Row1Col1', 'Row1Col2'],
  35.             [Row2Col1', 'Row2Col2']);
  36.  
  37. my $worksheet = ['Report.xls', {-data => \@data1}];
  38.  
  39. print “Available Database Drivers:\n\n”;
  40. print join(“\n”, DBI - > availableDrivers()), “\n\n”;
  41.  
  42. //my %attr => (RaiseError => 0);
  43.  
  44. my $dbh = DBI -> connect(“DBI:mysql:dbname=bugs:localhost”, “carole”, “carole”, { RaiseError => 1, PrintError => 0, 
  45.  
  46. AutoCommit => 0 })
  47.     or die(“Error: $DBI::errstr”);
  48.  
  49. my $excel_file = ‘C:/Perl/Report.xls’;
  50.  
  51. //-e checks to see if the file exists
  52.  
  53. if (-e “$excel_file”)
  54. {
  55.  
  56. #open Excel file
  57. my $Book = $Excel -> Workbooks -> Open(“$excel_file”);
  58. }else{
  59.  
  60. //paste code here to create a new file
  61. #create a new instance
  62. my $excel = Spreadsheet::SimpleExcel -> new(-worksheets => [$worksheets]);
  63.  
  64.  
  65. //$excel ->{Visible}=1;
  66.  
  67. #to create a file
  68. my $filename = 'Report.xls';
  69. my $excel = Spreadsheet::SimpleExcel -> new (-filename =>$filename);
  70.  
  71.  
  72. //$excel -> { SheetsInNewWorkBook} =1;
  73.  
  74. //$workbook = $excel -> Workbooks ->Add();
  75.  
  76. //$worksheet = $ Workbook -> Worksheets(1);
  77. //$worksheet -> {Name} = “Report”;
  78.  
  79. #add worksheets
  80. $excel -> add_worksheet('Report.xls');
  81.  
  82. my $sth = $dbh -> prepare(‘select b.bug_id, b.bug_file_loc, b.bug_severity, b.bug_status, b.creation_ts, b.delta_ts, 
  83.  
  84. b.short_desc, b.op_sys, b.priority, b.rep_plarform, b.version, b.component_id, b.resolution, b.target_milestone, 
  85.  
  86. b.status_whiteboard, b.votes, b.keywords, b.last_diffed, b.everconfirmed, b.reporter_accessible, b.cclist_accessible, 
  87.  
  88. b.estimated_time, b.remaining_time, b.deadline,  b.alias, pf.login_name, pd.name 
  89. from bugs b, profiles pf, products pd
  90. where bugs.assigned_to = profiles.user_id
  91. and bugs.product_id = products.id
  92. order by bug_id;’);
  93.  
  94. $sth ->execute() or die $dbh ->errstr;
  95.  
  96. while ( my $ref = $sth->fetchrow_hashref()) {
  97.  
  98.     print $ref ->{ 'bug_id', 'assigned_to', 'bug_file_loc', 'bug_severity', 'bug_status', 'creation_ts', 'delta_ts', 
  99.  
  100. 'short_desc', 'op_sys', 'priority', 'product_id', 'rep_plarform', 'version', 'component_id', 'resolution', 
  101.  
  102. 'target_milestone', 'status_whiteboard', 'votes', 'keywords', 'last_diffed', 'everconfirmed', 'reporter_accessible', 
  103.  
  104. 'cclist_accessible', 'estimated_time', 'remaining_time', 'deadline',  'alias', 'login_name', 'name'}, "\n";
  105.  
  106. }
  107.  
  108. $sth -> finish();
  109.  
  110. #create the spreadsheet
  111. $excel -> output();
  112.  
  113. #sort data of worksheet – ASC or DESC
  114. $excel -> sort_data(‘Report.xls’,0, ‘ASC’);
  115.  
  116. #add headers to 'Report'
  117. $excel -> set_headers('Report.xls', [qw/Bugs report/]);
  118.  
  119.  
  120.  
  121. #print sheet-names
  122. print join( “, “,$excel -> sheets()), “\n”;
  123.  
  124. #get the result as a string
  125. my $spreadsheet = $excel -> output_as_string();
  126.  
  127. #print result into a file and handle error
  128. $excel->output_to_file(“Report.xls”) or die $excel -> errstr();
  129. $workbook -> SaveAs($excel_file);
  130.  
  131. $dbh -> disconnect();
  132.  
First, please be sure to put all of your code inside of the code tags. This way our wonderful moderator(s) don't have to follow up behind you and clean up your posting. You can find a sample of code tags in the Reply Guidelines next to your message window when submitting a message.

(Miller: Can you please do this for the original post. ;-|) )

As for your issue, it sounds like you are not able to get to the database ( I am assuming that is where the error is coming from). Have you tried connecting to the database by hand, with the same information in the code? This will ensure that:

1. The database is running.
2. The code you are using will work when the time comes.

Try this and let us know what happens.

Regards,

Jeff
Aug 17 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.