473,383 Members | 1,748 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,383 software developers and data experts.

MySQL database to excel spreadsheet report

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
1 3009
numberwhun
3,509 Expert Mod 2GB
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

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

Similar topics

8
by: Johnny | last post by:
I was looking for information on how to import an excel file or other text file into a MySql database. I have done this with both access and SQL Server and am looking for a way to do this in...
3
by: bawar | last post by:
Hey, I can say I belong to the newbies in this subject. I own my own website with mySQL-databases, but now I have to show one table of those mySQL-databases in an excel worksheet. It is the...
4
by: ctkevin | last post by:
I am an experienced Access user but recently moved an Access database to the web for my employer. We use a host that does not allow the use of data access pages. Is there anyway to pre-build a...
6
by: DeniseY | last post by:
I have an Access report that is created on the fly by the user selecting the fields to be included. The Access report comes out fine, but I want it to automatically output to an Excel spreadsheet....
2
by: nofear | last post by:
I used to export my reports as snapshot but now I have to export them to Excel When I export my report to a Excel Spreadsheet the report header and footer are not included Only the data gets...
5
by: Sport Girl | last post by:
Hi everybody. I am new in PERL language and working in it since little hours, actually i am still a trainee and i need help please. In fact, i need a script in PERL that enables me to retrieve...
5
by: Sport Girl | last post by:
Hi again my great online assistants, just would like to say that i'm testing the script under unix , and after researches on the internet, i have realised that to resolve 500 Internal Server Error,...
1
by: Master Ken | last post by:
Hi All, I'm very new to C# and ASP and I am asking for some help as I don't really know where to start on this project. Some background info first I run a report each week which outputs data...
2
by: Alan M Dunsmuir | last post by:
A client has sent me a (Windows) Excel Spreadsheet (.xls) file containing a block of data he wants included as a table in a MySQL database in a (Linux-based) PHP/MySQL Web application I'm...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.