Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

Using Spreadsheet::WriteExcel with mod_perl and Content-Disposition?

by one4k4 (Hermit)
on Mar 15, 2001 at 00:17 UTC ( #64481=perlquestion: print w/replies, xml ) Need Help??

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

Maybe this is a mod_perl issue, but I need to have a user download a dynamically created spreadsheet. (created using Spreadsheet::WriteExcel)

Basically, here is how it should go..
Click link to download sheet
mod_perl module pulls q's from the DataBase,
Prints "Content-type: application/excel(or some needed varient)"
Prints "Content-Disposition: inline; filename=$name"
Then the spreadsheet is opened like so...
my $workbook = Spreadsheet::WriteExcel->new("-");


Only, the data isnt sent to the browser. Now, maybe I'm quite young with this stuff but I'd think since I set the Content-type headers that it would prompt the user to save the file.

It works outside of mod_perl

I have been reading about 'headers_out()' and 'send_httpd_header'(I believe thats what it was...) and have gotten just about nowhere. I've got the Eagle book, on mod_perl, and cant seem to find anything on Disposition. There is a discussion here on the mod_perl archives that is similar, but doesnt seem to offer me a solution.

In httpd.conf I have:
PerlSendHeader On
but is that needed? I read that in a discussion somewhere. Has anybody done this before? I mean, I'm sure somebody has, but umm, how did they do it?
Regardless, thanks for pointing me in the right direction if its at all possible.

_14k4 - webmaster@860.org (www.poorheart.com)
  • Comment on Using Spreadsheet::WriteExcel with mod_perl and Content-Disposition?

Replies are listed 'Best First'.
Re: Using Spreadsheet::WriteExcel with mod_perl and Content-Disposition?
by jlawrenc (Scribe) on Mar 15, 2001 at 01:25 UTC
    Dear one4k4,

    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; }
    Let me know how this goes. I've been meaning to do this myself! :) Thanks for prompting me to look into this.

    J

      # generate your Spreadsheet binary and put it in, oh # $sheet, ok?
      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();
      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.
        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. :)
inline attachments
by howard40 (Beadle) on Mar 15, 2001 at 03:32 UTC
    And if you're returning something that you think the browser can display inline (like a graphic), you could use this:
    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);



    THE UNIX CAR: you have to rebuild the engine whenever you hang a new air freshener in the car.
      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. :)
Update: Provblem 'solved'
by one4k4 (Hermit) on Mar 20, 2001 at 08:14 UTC
    Well, it seems to be solved. Sort of. I mean, it works grand, and the patch to OLEwriter.pm and sample code(below) that John sent me did the trick.

    #!/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();


    Its working beautifully, and seems to have no problem under mod_perl. I'm going to try to use the method below, per John's asking, and see if that works tomorrow when I get to my machine.
    # Untested tie *XLS => 'Apache'; my $workbook = Spreadsheet::WriteExcel->new(\*XLS);

    But we'll see what happens. Hopefully the next version will have no problems getting released. John's got my support.

    _14k4 - webmaster@860.org (www.poorheart.com)
      The code
      # Untested tie *XLS => 'Apache'; my $workbook = Spreadsheet::WriteExcel->new(\*XLS);

      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

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://64481]
Approved by root
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (3)
As of 2022-05-28 20:38 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Do you prefer to work remotely?



    Results (101 votes). Check out past polls.

    Notices?