#!/usr/bin/perl -w

package Apache::ExcelTest;

use Apache::Constants qw(:common);
use Apache::Request;
use Spreadsheet::WriteExcel;

sub handler{
  my $r = Apache::Request->new(shift);

  print "Content-disposition: attachment;filename=test.xls\n";
  print "Content-type: application/vnd.ms-excel\n\n";

  tie *XLS => 'Apache';
  binmode(*XLS);
  my $workbook  = Spreadsheet::WriteExcel->new(\*XLS);
  my $worksheet = $workbook->addworksheet();
  $worksheet->write("A1","Hello, World");
  $workbook->close;
}

1;
[download]

<Location /exceltest>
SetHandler perl-script
PerlHandler Apache::ExcelTest
PerlSendHeader on
</Location>
[download]

Hope this points you in the right direction.

JJ

I was playing with writing a spreadsheet to the screen, and this code works:


#!/usr/local/bin/perl

use strict;
use warnings;

use CGI qw/:standard/;
  
if( param())
{

    print header(-type=>'application/vnd.ms-excel',
                 -attachment=>'test.xls');
  use Spreadsheet::WriteExcel;
  
  my $workbook = Spreadsheet::WriteExcel->new('-');
  my $worksheet = $workbook->addworksheet(param('color'));
  
  
  $worksheet->write(0,0,param('name'));
  $worksheet->write(0,1,"dere");
  $worksheet->write(0,2,"be jammin");
  $worksheet->write(1,0,"Monkey");
  $worksheet->write(2,0,param('words'));
  
  $workbook->close();

} else {
  print header,
  start_html('A Simple Example'),
  h1('A Simple Example'),
  start_form,
  "What's your name? ",textfield('name'),p,
  "What's the combination?", p,
  checkbox_group(-name=>'words',
                 -values=>['eenie','meenie','minie','moe'],
                 -defaults=>['eenie','minie']), p,
  "What's your favorite color? ",
  popup_menu(-name=>'color',
                   -values=>['red','green','blue','chartreuse']),p,
  submit,
  end_form,
  hr;

}
[download]

Hope this helps! Update: The above code was pretty much cut'n'pasted from cgi

I still get exactly the same result. On my local machine the excel data appears in text format. It worked from your URL though. I have checked the MIME headers supported by my browser and vnd-ms.excel is listed:

GET /HTest HTTP/1.1
Accept: image/gif, image/x-xbitmap, image/jpeg, image/pjpeg, applicati
+on/vnd.ms-powerpoint, application/vnd.ms-excel, application/msword, *
+/*
Accept-Encoding: gzip, deflate
Accept-Language: en-gb
Connection: Keep-Alive
Host: localhost
User-Agent: Mozilla/4.0 (compatible; MSIE 5.5; Windows NT 5.0)

HTTP/1.1 200 OK
Keep-Alive: timeout=15, max=100
Connection: Keep-Alive
Transfer-Encoding: chunked
Content-Type: text/plain
[download]

Do I need to install libapreq to get this to work? I haven't yet managed to get it to compile (it tells me I don't have mod_perl installed!)

Well if you don't have mod_perl installed (as stated in the original question), you won't be writing code as a handler, and also won't be tying the output to Apache.

Try this in a 'normal' CGI script:

#!/usr/bin/perl -w

print "Content-disposition: attachment;filename=test.xls\n";
print "Content-type: application/vnd.ms-excel\n\n";

binmode(STDOUT);
my $workbook  = Spreadsheet::WriteExcel->new('-');
my $worksheet = $workbook->addworksheet();
$worksheet->write("A1","Hello, World");
$workbook->close;
[download]

OK, I do have mod_perl installed. I have now installed libapreq and the demo works. Thanks. I had to change the makefile and hard code the paths to the includes and libs MyConfig wasn't picking them up!!. So now I can generate Excel files on the fly from a handler, but I don't want to write a new handler for every file. How do I pass values onto the handler so that it can acces the correct database and dataset? I've never played with handlers before so this is completely new territory for me.

Like this:

use Apache::Request;

sub handler {
  my $r = Apache::Request->new(shift);
  my $test = $r->param('test');

  # your code here...
}
[download]

The $r->param method handles both GET and POST requests, similar to using CGI.pm.

I would recommend you get a copy of The Eagle Book - this will tell you everything you need to know.

JJ

When you say Response object, I assume you write the app in Perlscript, right or ... Anyway below is a snippet c&p'ed from working code

 
## previous code 
$Response->{'Buffer'} = 1;

$Response->{ContentType} = "application/vnd.ms-excel";
$Response->AddHeader('Content-Disposition', "attachment; filename=$fil
+ename");
#    'Specify size so that the browser's progress bar works properly
$Response->AddHeader( "Content-Length", -s $long_filename);

open(IN, "<$long_filename") || AppendToLog("Unable to open yada yada "
+);
binmode(IN);
my @buf = <IN>;
close(IN);

BinaryWrite(@buf);

$Response->Flush;

## more code


sub BinaryWrite(@) {
    my $output;
    foreach $output (@_) {
        if (length($output) > 128000) {
            BinaryWrite(unpack('a128000 a*', $output));
        } 
        else {
            my $variant =Win32::OLE::Variant->new(VT_UI1, $output);
            $Response->BinaryWrite($variant);
        }
    }
}
[download]

Please also remember that things will fail if you output anything to STDOUT before

Thanks for that, I was hoping to create them on the fly and not have to store them in files prior to the user downloading them. But if I can't get it to work that is what I will have to do. Could get quite disc hungry, some of the data sets are many, many MBs in size.

my $workbook  = Spreadsheet::WriteExcel->new(\*.XLS);
my $worksheet = $workbook->addworksheet();
$worksheet->write("A1","Hello, World");
[download]

JJ