Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I am using apache to run a CGI HTML frontend to a database. This system is run locally. Apache does not let me execute external commands or system calls. I am trying to generate a quote from values returned from the database. I first looked at Win::OLE and now Spreadsheet::WriteExcel with the same issues. I can generate the output when i run these scripts from a command prompt, but not when run from the webserver. I also looked at modules that interact with Apache to try and attempt to either fork a process or create a new one to handle this event. Does anyone have any advise for me. How can I achieve my goal. I looked at embeeding the excel into my application using the following command

print CGI::header(-type => 'application/vnd.ms-excel')

But this does not provide any ability to print (other than Ctrl+P and save). Any ideas?

Replies are listed 'Best First'.
Re: Running external apps
by dragonchild (Archbishop) on Sep 12, 2004 at 21:57 UTC
    (I seem to remember a question along very similar lines in the past week.)

    You need to provide more information about what it is you're trying to do. Specifically,

    • What operating system is the webserver on? What version of Apache? What version of Perl?
    • What exactly are you trying to generate? A spreadsheet? I'm not sure what a "quote from values" is.
    • How are you trying to provide the ability to print? Are you attempting to serve a response directly to the client-side printer? That is a very difficult problem, unless you also control the setups of the client-side systems and, even then, it's not trivial. It's also not a Perl problem.

    In other words, don't give us the specific problem you're trying to solve. You need to give us the whole problem before we're going to be of much service.

    ------
    We are the carpenters and bricklayers of the Information Age.

    Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose

    I shouldn't have to say this, but any code, unless otherwise stated, is untested

[reply]
Re: Running external apps
by InfiniteSilence (Curate) on Sep 13, 2004 at 02:59 UTC
    Brother, I should have guessed this right away, but it turns out that this is a REALLY common question for people running Apache on Windows. You have to specify the exact path for your perl executable in your .cgi scripts on Win32 for them to work. Here is an example: 

    #!c:\perl\bin\perl.exe -w
use strict;
use Win32::OLE; 

my $xl=new Win32::OLE('Excel.Application') or die $!; 
$xl->Workbooks->Open('c:\temp\cats.xls') or die $!; 
my $cellvalue = $xl->Sheets(1)->Range('A1')->value();
print qq|Content-type: text/html\n\n|;
print '<html><body>' . $cellvalue . '</body></html>'; 
$xl->Quit(); #no zombie excels!
1;

    [download]
    If you don't do this you will get a 720003 error message in your Apache error logs (you do know where those are, don't you?) regarding your .cgi script.

    As an aside, though, you really should dump the idea of using Excel as a data source on the server. I mean, it is a memory hog and takes forever to load. If you have to, use Text::CSV and export that Excel data to .csv format. Much, much faster.

    Celebrate Intellectual Diversity

[reply]
[d/l]
Re: Running external apps
by reneeb (Chaplain) on Sep 13, 2004 at 07:55 UTC
    I use this code in a script:
    #! /usr/bin/perl

use strict;
use warnings;
use diagnostics;
use CGI;
use CGI::Carp qw(fatalsToBrowser);
use DBI;
use Data::Dumper;
use lib qw(/data/perllib);
use Spreadsheet::WriteExcel;
use Conf;

my $cgi    = CGI->new();
my %params = $cgi->Vars();

print $cgi->header(-type => 'application/vnd.ms-excel',);

# general settings for database-connection
my $USER     = $Conf::USER;
my $PASSWORD = $Conf::PASSWORD;
my $HOST     = $Conf::HOST;
my $DATABASE = $Conf::DATABASE;
my $DBMS     = $Conf::DBMS;
my $driver   = "dbi:$DBMS:$DATABASE:$HOST";

# create connection to database
my $dbh = DBI->connect($driver,$USER,$PASSWORD);

my $statement = "SELECT * FROM table;";

#---------------------------------------------------#
# write EXCEL-file                                  #
#---------------------------------------------------#
  binmode(\*STDOUT);
  my $EXCEL = new Spreadsheet::WriteExcel(\*STDOUT);
  my $sheet = $EXCEL->addworksheet("Features");
  my $state_h = $dbh->prepare($statement);
  $state_h->execute();
  my $col_h = 1;
  $sheet->write(0,0,'Header1');
  shift(@wanted_cols);
  foreach my $th(@wanted_cols){
    $sheet->write(0,$col_h,$th);
    $col_h++;
  }
  my $row = 1;
  while(my @res_array = $state_h->fetchrow_array){
    foreach my $col(0..(scalar(@res_array)-1)){
      $sheet->write($row,$col,$res_array[$col]);
    }
    $row++;
  }
  $EXCEL->close();

    [download]


    This code creates an Excel-window where you can use the all the Excel-abilities!
[reply]
[d/l]
      This code creates an Excel-window where you can use the all the Excel-abilities!

      Along the lines of what dragonchild pointed out, that code does not really create an Excel window at all. It returns Excel data to the client, but it's the client's duty to handle it correctly by, for example, opening it in an Excel window. Under normal circumstances, this task is beyond the scope of the Perl script. The client must be configured correctly ( ie Excel is installed properly ) for this to work as expected.

      Zenon Zabinski | zdog | zdog@perlmonk.org

[reply]
      To elaborate what zdog has said, this returns data identified as Excel data to the client. What the client does with it is completely outside the control of the CGI, as it should be. For example, if this is returned to IE on WinXP, this may open an Excel window within the browser, but only if configured appropriately. When returned to Mozilla on WinXP, it will ask you if you want to Save or Open, at which point Excel is one of the choices. When returned to Galeon on Linux, it will ask for Save or Open, and OpenOffice (if installed) may be one of the choices.

      In other words, your script returns some data. The client does what it wants with it. That codes does NOT creates an Excel-window where you can use the all the Excel-abilities!.

      ------
      We are the carpenters and bricklayers of the Information Age.

      Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose

      I shouldn't have to say this, but any code, unless otherwise stated, is untested

[reply]

Back to Seekers of Perl Wisdom