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

I am using SpreadSheet::WriteExcel to create an excel spreadsheet from a MySQL db. I have followed the example given in the documentation for doing this using mod_perl and Apache but it doesn't work. All I get is the contents of the excel file diplayed as text in the browser window, followed by the header information. Does anybody have any ideas as to why this is? I am using: 
tie *XLS,'Apache';
binmode(*XLS);
my $workbook=SpreadSheet::WriteExcel->new(\*XLS);

[download]
this ties the output from WriteExcel to the Apache STDOUT. But it seems to be writing the excel data first always.

Replies are listed 'Best First'.
Re: Creating downloadable files on the fly
by jj808 (Hermit) on Oct 09, 2002 at 10:21 UTC
    Are you sending the correct Content-Type header to tell the browser that this is an excel file? The Excel content type is: 
    Content-Type: application/vnd.ms-excel

    [download]
    To force a file download, use the Content-Disposition header: 
    Content-Disposition: attachment; filename=test.xls

    [download]
    Basically print these two lines, followed by a blank line, then send the Excel data.

    JJ

[reply]
[d/l]
[select]
      Yeah, I either print those two Content headers or set them using the Response object, but they seem to have no effect. It seems to write the excel stream before it writes the header info so I get the following (source dump of resultant page): 
      ÐÏà¡....ÿÿÿÿÿÿÿÿÿÿÿ <--loads of stuff
HTTP/1.1 200 OK
Date: Wed, 09 Oct 2002 10:43:16 GMT
Server: Apache/1.3.20 (Win32) mod_ssl/2.8.4 OpenSSL/0.9.6b mod_perl/1.
+26
ContentDisposition: attachment; filename=test.xls
Content-Length: 0
Expires: Wed, 09 Oct 2002 10:43:15 GMT
Cache-Control: private
Keep-Alive: timeout=15, max=98
Connection: Keep-Alive
Content-Type: application/vnd.ms-excel

      [download]
[reply]
[d/l]
        I've knocked up a quick test: 
        #!/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]
        and here is the Apache config to go with it: 
        <Location /exceltest>
SetHandler perl-script
PerlHandler Apache::ExcelTest
PerlSendHeader on
</Location>

        [download]
        To see it in action, go to http://jonallen.info/exceltest.

        Hope this points you in the right direction.

        JJ

[reply]
[d/l]
[select]

        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

[reply]
[d/l]
        Can you reduce this to a test case (i.e. use a very simple Excel file such as 
        my $workbook  = Spreadsheet::WriteExcel->new(\*.XLS);
my $worksheet = $workbook->addworksheet();
$worksheet->write("A1","Hello, World");

        [download]
        and then post your complete script. I'll then run it on my machine and see if I can reproduce the problem.

        JJ

[reply]
[d/l]
Re: Creating downloadable files on the fly
by quinkan (Monk) on Oct 10, 2002 at 00:15 UTC
    You +did+ remember: 
    $|++;

    [download]
    didn't you ?
[reply]
[d/l]
Re: Creating downloadable files on the fly
by jmcnamara (Monsignor) on Oct 11, 2002 at 10:26 UTC

    As an addendum to this I'd like to point out that the Sreadsheet::WriteExcel distro contains a lot of example programs including working mod_perl and CGI examples.

    jj808 ++ for the above example.

    --
    John.

[reply]

Back to Seekers of Perl Wisdom