I am going to offer you a few suggestions in hopes that it might help you out.

I assume you have a simple mod_perl request handler working, eh? If you do not make sure you can at least return "hello world" to your browser! :) After that you should only need to do:

sub handler {

   my $r=shift;

   # generate your Spreadsheet binary and put it in, oh
   # $sheet, ok?

   $r->content_type('application/vnd.ms-excel');
   $r->header_out('Content-disposition' => "attachment; filename=\"$fi
+lename"");
   $r->send_http_header;

   $r->print($sheet);

   return DONE;

}
[download]

J

   # generate your Spreadsheet binary and put it in, oh
   # $sheet, ok?
[download]

So, I still need to generate the file, and read it into $somevariable do I?
Could I take the Spreadsheet::WriteExcel->new("-") call (STDOUT(?)) and stuff that onto $somevariable?

If I run the following code...


# Send the content type
my $name = "testing.xls";
print "Content-disposition: attachment; filename=$name\n";
print "Content-type: application/vnd.ms-excel\n\n";

# Redirect the output to STDOUT
my $workbook  = Spreadsheet::WriteExcel->new("-");

my $worksheet = $workbook->addworksheet();
$worksheet->write(0, 0, "This should work.");
$worksheet->write(0, 1, "In theory.");
$workbook->close();
[download]

It'll prompt the user to save the file, (named excel - because of mod_perl) When they click "ok" to save it, its called "testing.xls" like it should be.
But the spreadsheet is empty... :=/
Hrm, interesting
I like problems like this. I always end up learning something new. Yay.


_14k4

If you want to write to a scalar instead of a file I can send you a patch (it will be in the next version) that will let you do this:

    #!/usr/bin/perl -w

    use strict;
    use IO::Scalar;
    use Spreadsheet::WriteExcel;

    my $xls_str; 
    tie *XLS, 'IO::Scalar', \$xls_str;

    my $workbook  = Spreadsheet::WriteExcel->new(\*XLS);
    my $worksheet = $workbook->addworksheet();
    $worksheet->write(0, 0,  "Hi Excel!");
    $workbook->close();
    
    # The Excel file is now in $xls_str.
[download]

or to some other filehandle like this:     my $workbook  = Spreadsheet::WriteExcel->new(\*STDOUT);

John.
--
And the eighth and final rule, if this is your first time using Perl, you will have to write code.

Oh, and a second thing... do I have to try to return "hello world". I really only want to talk to myself, so would "Hello me" work as well? :)

Ok, its 4pm, and I'm tired. Give me a break. :)

print "Content: $page\r\n";
print "Content-disposition: inline; filename=\"$page\"\r\n";
my $size = (-s "$page");
print "Content-length: $size\r\n";
print "Content-type: application/octet-stream\r\n";
print "\r\n";

local $/ = undef;
open (F, "<$page") || die;
  binmode(F); binmode(STDOUT);
  print <F>;
close(F);
[download]

So basically, I still dont know how to take the WriteExcel data and stuff it into a variable? (read my replies above..)
I -could- write it to a temp file, read the temp file into a var, delete said temp file, and go from there...

Hrm, that might work.
Oh well. Its almost friday. :)

#!/usr/bin/perl -w

######################################################################
+#
#
# Example of how write to alternative filehandles.
#
# March 2001, John McNamara, jmcnamara@cpan.org
#

use strict;
use Spreadsheet::WriteExcel;
use IO::Scalar;

#
# Example 1. Write an Excel file to a string.
#

# Refer to the IO::Scalar documentation
my $xls_str; 
tie *XLS, 'IO::Scalar', \$xls_str;

my $workbook1  = Spreadsheet::WriteExcel->new(\*XLS);
my $worksheet1 = $workbook1->addworksheet();
$worksheet1->write(0, 0,  "Hi Excel!");
$workbook1->close(); # This is required

# The Excel file is now in $xls_str.
# If you write it to a new file remember to binmode() the filehandle.



#
# Example 2. Write an Excel file to an existing filehandle.
#

open TEST, "> mytest1.xls";

my $workbook2  = Spreadsheet::WriteExcel->new(\*TEST);
my $worksheet2 = $workbook2->addworksheet();
$worksheet2->write(0, 0,  "Hi Excel!");
$workbook2->close();



#
# Example 3. Write an Excel file to an OO style filehandle.
#

my $fh = FileHandle->new("> mytest2.xls");

my $workbook3  = Spreadsheet::WriteExcel->new($fh);
my $worksheet3 = $workbook3->addworksheet();
$worksheet3->write(0, 0,  "Hi Excel!");
$workbook3->close();
[download]

    # Untested
    tie *XLS => 'Apache';
    my $workbook = Spreadsheet::WriteExcel->new(\*XLS);
[download]

The code


    # Untested
    tie *XLS => 'Apache';
    my $workbook = Spreadsheet::WriteExcel->new(\*XLS);
[download]


Woohoo. Thanks all!

_14k4 - webmaster@860.org (www.poorheart.com)

I am having a similar problem. I have a link on a report detailing some ticket information. That link should regenerate the same data as displayed on the page but as an excel spreadsheet that is passed to the user for download and not saved on the server.

The link works, a download dialog appears to save the file but for some reason the file appears to be limited to around 400k in size ( a little under 4500 of the 9755 rows that should appear. When I change from a file handle to saving the file to the server it works fine, the problem only happens when it's a direct download. Any help you could offer would be great

    tie *XLS, 'IO::Scalar', \$xls_str;
    my $workbook = Spreadsheet::WriteExcel::Big->new(\*XLS);
    my $worksheet = $workbook->add_worksheet();

    # Hardcoded row and column fields
    $worksheet->set_landscape();
    $worksheet->set_row(0,30);
    $worksheet->set_column(0,0, 20);
    $worksheet->set_column(1,1, 20);
    $worksheet->set_column(2,2, 20);
    $worksheet->set_column(3,3, 20);
    $worksheet->set_column(4,4, 20);
    $worksheet->set_column(5,5, 20);
    $worksheet->set_column(6,6, 20);

    # Spreadsheet Formating
    my $format = $workbook->add_format();
    my $titleformat = $workbook->add_format();
    $format->set_bold(1);
    $titleformat->set_size(15);

    # Counters
    my $rowcount = 1;

    # loops for writing
    while ( my @row = $sth->fetchrow_array ) {
      my $row_ref = \@row;
      $worksheet->write_row($rowcount,0,  $row_ref);
      $rowcount++;
    } #end of loop

    $workbook->close();

    #my $size = (-s $xls_str);
    print "content: myfile.xls\r\n";
    print "content-disposition: inline; filename=myfile.xls\r\n";
    #print "content-length: $size\r\n";
    print "content-type: application/vnd.ms-excel\r\n";

    #local $/ = undef;
    #open (F, "<$xls_str");
    #  binmode(F); binmode(STDOUT);
    #  print <F>;
    #close(F);
    print $xls_str;

in the above script if I replace


    tie *XLS, 'IO::Scalar', \$xls_str;
    my $workbook = Spreadsheet::WriteExcel->new(\*XLS);

with


    my $filename = "myfile.xls";
    my $workbook = Spreadsheet::WriteExcel->new($filename);

The file is saved to my server with the correct number of rows

I believe the problem has something to do with IO::Scalar and/or an apache config but I'm a little lost as to what I could be missing