469,320 Members | 2,192 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,320 developers. It's quick & easy.

Wrinting into an Excel Sheet File

Hi all , i need to write into an excel sheet file the data retreived from the database. i have the script but the problem is that i can't get the data written in the excel file.

Can anybody help pleeeassse.

Expand|Select|Wrap|Line Numbers
  1. #!/usr/bin/perl 
  2. use strict;
  3. use warnings;
  4. use DBI;
  5. use DBD::mysql;
  6. use lib qw(.);
  7. use Bugzilla;
  8. use Bugzilla::Bug;
  9. use Bugzilla::Util;
  10. use Bugzilla::Constants;
  11. use Time::Local;
  12. use Spreadsheet::WriteExcel;
  13.  
  14.  
  15. my $dbh = DBI->connect('DBI:mysql:database=bugs;host=localhost', 'jinny', 'jinny') or die "Connection Error";
  16.  
  17. my $Excelfile = "./Report.xls";
  18.  
  19. #create a new instance
  20. my $excel = Spreadsheet::WriteExcel->new("$Excelfile");
  21. my $worksheet = $excel->addworksheet("Bugs Report");
  22. my $now = localtime time;
  23. $worksheet->write(0, 0, "Report generated on :$now" );
  24.  
  25. my $stmt = "SELECT * FROM bugs ORDER BY bug_id";
  26.  
  27.  
  28. my $sth = $dbh->prepare($stmt);
  29. my @data;
  30.  
  31. $sth->execute() or die $dbh->errstr;
  32.  
  33. while ( @data = $sth->fetchrow_array()){
  34.     my $bug_id = $data[1];
  35.     my $assigned_to = $data[2];
  36.     my $bug_file_loc = $data[3];
  37.     my $bug_severity = $data[4];
  38.     my $bug_status = $data[5];
  39.     my $creation_ts = $data[6];
  40.     my $delta_ts = $data[7];
  41.     my $short_desc = $data[8];
  42.     my $op_sys = $data[9];
  43.     my $priority = $data[10];
  44.     my $product_id = $data[11];
  45.     my $rep_platform = $data[12];
  46.     my $reporter = $data[13];
  47.     my $version = $data[14];
  48.     my $component_id = $data[15];
  49.     my $resolution = $data[16];
  50.     my $target_milestone = $data[17];
  51.     my $qa_contact = $data[18];
  52.     my $status_whiteboard = $data[19];
  53.     my $votes = $data[20];
  54.     my $keywords = $data[21];
  55.     my $lastdiffed = $data[22];
  56.     my $everconfirmed = $data[23];
  57.     my $reporter_accessible = $data[24];
  58.     my $cclist_accessible = $data[25];
  59.     my $estimated_time = $data[26];
  60.     my $remaining_time= $data[27];
  61.     my $deadline = $data[28];
  62.     my $alias= $data[29];
  63.     my $row = 0;
  64.     my $col = 0;
  65.     foreach my $stmt (@data) {
  66.         $worksheet->write($row++, @data);
  67.         last;
  68.     }
  69. }
  70.  
  71. $sth->finish();
  72. $dbh->disconnect();
  73.  
  74.  
Aug 31 '07 #1
6 1722
numberwhun
3,503 Expert Mod 2GB
Just a couple of quick questions back at you:

1. What errors were you getting when executing, if any?

2. Do you have Micro$oft Excel installed on this machine?

Regards,

Jeff
Aug 31 '07 #2
Hi Jeff, how r u?

As answers to ur quesrtions, i am not guetting any errors, there is no syntax errors but there is no retreival for the data as well.
I have also Microsoft Excel installed on my machine.

Regards
Sep 3 '07 #3
just wanna add that i get in the excel sheet file in the first row a data that is far from being the data of the database:

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
Sep 3 '07 #4
It's ok i have done this , thank u all very much for ur help , i appreciate it very much.
Sep 3 '07 #5
numberwhun
3,503 Expert Mod 2GB
It's ok i have done this , thank u all very much for ur help , i appreciate it very much.
That's great, but for those of use scratching our heads, what was the issue and what was your solution?
Sep 3 '07 #6
That's great, but for those of use scratching our heads, what was the issue and what was your solution?
Hi all,
I have a select stmt like this..
$q= "select empl_no,empl_name,doj,attnd as total,uninformed as .......id,1 from salary where category='$cat' and month='$cmprmonth' order by id";

and i am binding the vaues to variables, $empl_no.. etc.,... and iam printing all the values in table format on web page.

But I want to write into an excel sheet in a same table format when i click on the button.. because i want to take a print out from that excel report.

how to do it? please tell me its urgent... help me out in this...

regards,
greet
Nov 19 '07 #7

Post your reply

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

Similar topics

13 posts views Thread by Allison Bailey | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by listenups61195 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.