Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid

@ generating Excel Attachment using Perl

by siddheshsawant (Sexton)
on Jun 07, 2010 at 21:41 UTC ( #843578=perlquestion: print w/ replies, xml ) Need Help??
siddheshsawant has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monks I am working on developing a perl API where I have designed the API script to extract a data from DB depending on the i/p given at the front end.Now I want to generate an excel file depending on the i/p that I will give in browser window.Below is the program which works fine and generates excel sheet if I entered the i/p from console window but gives an error like "Can't call method "add_worksheet" on an undefined value at /var/www/cgi-bin/excel_report.cgi line 57." If I try to give i/p through web browser.The corresponding code is as follows:

#!/usr/bin/perl -w use CGI ':standard'; use CGI::Carp qw(warningsToBrowser fatalsToBrowser); use DateTime::Format::ISO8601 qw( ); use strict; use Spreadsheet::WriteExcel; use lib "/view/sawans1_insert_data/vobs/pp/pptai/api/NightlyDB"; use API; my @test_run_id; my @total_passed; my @date; my $option_type; my $user_input; my $test_run_id; my $test_run_list_ref; my $sdt = DateTime::Format::ISO8601->parse_datetime(param('start_date' +)); my $edt = DateTime::Format::ISO8601->parse_datetime(param('end_date')) +; for (my $dt = $sdt->clone(); $dt<=$edt; $dt->add( days => 1 )){ push @date,$dt->ymd(); } $user_input->{pp_branch} = param('pp_branch'); # Create a new Excel workbook my $file = "test_run.xls"; my $workbook = Spreadsheet::WriteExcel->new($file); # Add a worksheet my $worksheet = $workbook->add_worksheet(); # Add and define a format my $format = $workbook->add_format(); # Add a format $format->set_bold(); $format->set_color('red'); $format->set_align('center'); # Write a formatted and unformatted string, row and column notatio +n. my $col = my $row1 = 0; $worksheet->write(0, 0, 'Date', $format); $worksheet->write(0, 1, 'Nightly Web ID', $format); $worksheet->write(0, 2, 'Number Of Tests Passed', $format); my $row = 1; my $i = 0; foreach (@date){ #create hash reference variable for every date in array @date $user_input->{start_date} = $_; $user_input->{end_date} = $_; #Call get_test_run function to create test run list refer +ences $test_run_list_ref = &API::get_test_run($user_input); #For each such reference calculate # of objects by addition foreach my $tr_obj (@$test_run_list_ref){ push @total_passed, $tr_obj->number_of_passed(); $tr_obj->nightly_web_id(); $worksheet->write($row,0, $_); $worksheet->write($row,1,$test_run_id[$i]); $worksheet->write($row,2,$total_passed[$i]); $row++; $i++; } } print header( -attachment=>'test_run.xls', );

Kindly Let me know if anybody found out where I am mistaking !!!!

Comment on @ generating Excel Attachment using Perl
Download Code
Re: @ generating Excel Attachment using Perl
by almut (Canon) on Jun 07, 2010 at 22:05 UTC
    gives an error like "Can't call method "add_worksheet" on an undefined value

    "If the file cannot be created, due to file permissions or some other reason, new will return undef. Therefore, it is good practice to check the return value of new before proceeding. As usual the Perl variable $! will be set if there is a file creation error."

    (from TFM — emphasis added)

      Thanks sir for replying back pon my post yesterday .I read your question and one query strike in my curious mind is that , 'As per your reply I am not getting rights to generate an excel file but when I execute the same program in command line then I can generate the excel file at the same location where I am running that script Why is it so ?'
Re: @ generating Excel Attachment using Perl
by marto (Chancellor) on Jun 08, 2010 at 08:29 UTC
Re: @ generating Excel Attachment using Perl
by brap (Pilgrim) on Jun 08, 2010 at 15:31 UTC
      you made my day man !!!!! God bless you :)

        And had you actually read the documentation of the module you were using, you'd have found the solution hours ago. RTFM should be your first port of call before posting, as described in How do I post a question effectively? (which has been pointed out to you before, and gets displayed each time you post) and How to RTFM.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://843578]
Approved by almut
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (8)
As of 2014-08-30 18:01 GMT
Find Nodes?
    Voting Booth?

    The best computer themed movie is:

    Results (293 votes), past polls