Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    London
    Posts
    76

    Unanswered: Report in Excel using Perl

    Hi, I am need of help, I would like some advise and example scripts on how to export a query in Excel containing multiple rows using Perl. I am new to Perl and have managed to write a script to query the database and email the results to users. However, this data is a single value...any help, especially example scripts would be highly useful...thanks

  2. #2
    Join Date
    Jan 2004
    Location
    Germany
    Posts
    167
    You want to create an Excel-sheet using Perl? The magic word is Spreadsheet::WriteExcel (it's available at cpan.org). You can do it like this:
    Code:
    #! /usr/bin/perl
    
    use strict;
    use warnings;
    use lib qw(/homes/reneeb/cpan);
    use Spreadsheet::WriteExcel;
    
    my $filex     = 'Test.xls';
    my $workbook  = Spreadsheet::WriteExcel->new($filex);
    my $worksheet = $workbook->add_worksheet('Test');
    
    my @values = qw(1 2 3 4 Test 4 7);
    my $col       = 0;
    
    foreach my $val(@values){
      $worksheet->write(0,$col,$val);
      $col++;
    }
    board.perl-community.de - The German Perl-Community

  3. #3
    Join Date
    Feb 2004
    Location
    London
    Posts
    76
    I have managed to figure the script to display multiple rows:

    #!c:\perl\bin\perl

    use strict;

    use DBI;
    #use MIME::Lite;

    # Connect to the Database
    my $db = DBI->connect("dbi:Oracle:xcalibur_live ", "", "", { AutoCommit => 0 } );

    # check connect to DB
    $db or (print <<EOD
    Error: Can't connect to database
    EOD
    and die $DBI::errstr);

    print "connected.\n";

    #-------------------------------------------------------------
    # This is an example query using while to print the returned
    # values
    #-------------------------------------------------------------

    print "No of Queries Resolved by answered person \n";

    my $queries_resolved = qq{select cm.answeredby as Answeredby, count(c.QUERYNOID) as No_of_queries
    from COMPLAINTMEDIAENTRIES cm, COMPLAINTS c
    where c.QUERYNOID = cm.QUERYNO
    and cm.ANSWEREDDATETIME between to_date(sysdate-7, 'dd-mon-yy hh24:mi:ss')
    and to_date(sysdate, 'dd-mon-yy hh24:mi:ss')
    and cm.RESOLVED = 'Y'
    group by cm.answeredby};

    my $sth = $db->prepare ($queries_resolved);
    $sth->execute();


    my ($Answeredby,$No_of_queries);
    $sth->bind_columns(undef, \$Answeredby, \$No_of_queries);


    while ($sth->fetch() ) {

    print "$Answeredby, $No_of_queries \n";

    }

    #Print "\n No of Queries Answered but Unresolved by answered person \n";

    my $queries_answered_unresolved = qq{select cm.answeredby as Answeredby, count(c.QUERYNOID) as No_of_queries
    from COMPLAINTMEDIAENTRIES cm, COMPLAINTS c
    where c.QUERYNOID = cm.QUERYNO
    and cm.ANSWEREDDATETIME between to_date(sysdate-7, 'dd-mon-yy hh24:mi:ss')
    and to_date(sysdate, 'dd-mon-yy hh24:mi:ss')
    and cm.RESOLVED is null
    group by cm.answeredby};

    my $sth2 = $db->prepare ($queries_answered_unresolved);
    $sth2->execute();

    my ($Answeredby,$No_of_queries);
    $sth2->bind_columns(undef, \$Answeredby, \$No_of_queries);



    while ($sth2->fetch() ) {

    print "$Answeredby, $No_of_queries \n";

    }


    $sth->finish();

    $sth2->finish();

    $db->disconnect();


    any help in incorporating your excel script would be of great help!

  4. #4
    Join Date
    Feb 2004
    Location
    London
    Posts
    76
    I have managed to do this...thanks for your help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •