http://www.perlmonks.org?node_id=515372

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

Monks,
I am trying to write an invoice in an Excel file as required by my specification. I am writing directly to the CGI stream by using:
print "Content-type: application/pascrm-download\n"; print "Content-Disposition: attachment; filename=$file\n\n"; my $workbook = Spreadsheet::WriteExcel->new("-");

This works well for the customers that have a small number of sites that need to be billed monthly, however, when generating an invoice for my largest customer, 242 rows, hangs my program. I can output up to about 120 rows, by limiting my query for billable service plans to 120, and I am aware that the Spreadsheet::WriteExcel is limited to ~7MB for the BIFF that is used by the module.

Any hints or suggestions are greatly appreciated. I am considering breaking this out of my CGI program and simply writing an external script to primatively fork out to do this function and return the location of the file on the filesystem that I just created, then read the file in and then print the file to the CGI stream as a download.

amt.

perlcheat

Replies are listed 'Best First'.
Re: Spreadsheet::WriteExcel with CGI
by ChemBoy (Priest) on Dec 08, 2005 at 21:47 UTC

    It may not be the cleanest of solutions, but at the moment we do what you're doing with a temp file: create the .XLS file in some random location, then read it back into memory (in the same script) and print it to the client (then, presumably, delete the temp file). This keeps everything in one process, so you can report errors directly to the client (and not bother forking), and also lets you defer printing the headers until you know more about your output (the size, of course, but also if there were any errors as you fetched the data).

    That is:

    use File::Temp 'tempfile'; use CGI 'header'; my ($fh,$filename) = tempfile(); my $workbook = Spreadsheet::WriteExcel->new($filename); do_stuff($workbook); $fh->seek(0,0); my $data; my $size = -z $filename; $size== $fh->read($data,$size) or bail_the_heck_out(); #CGI::Carp migh +t help print header( -type=>"application/vnd.ms-excel", -content_length=>$size, -attachment=>$file, # assuming you set that elsewhere ), $data;



    If God had meant us to fly, he would *never* have given us the railroads.
        --Michael Flanders

      i'm getting an error for $! as "Resource temporarily Unavailable". I'm trying to write to a file using Spreadsheet::WriteExcel's write(). The $worksheet object is defined at the top of the file, but when I hit the below while loop, it won't write() and puts RTU in the $! variable.

      I don't think it's a scoping issue because the $worksheet object is defined well before the while loop. My query for the information that needs to go into the doesn't choke, so I'm not writing with uninitialized variables. Would appreciate your opinion.
      # At top of file my $workbook = Spreadsheet::WriteExcel->new($target); my $worksheet = $workbook->add_worksheet("Ventus_Networks_Invoice"); my $row = 0; ... # This is the header row, that prints if( $qrv > 0 ) { my $mid_header = $workbook->add_format(%oUpper,%iLeft,%iRight,%greybg,%fBold,%iLower); my $mid_theader= $workbook->add_format(%oUpper,%iLeft,%iRight,%greybg,%fBold,%iLower,%f +Twrap); $worksheet->write($row,0,'Site Name',$workbook->add_format(%oUpper,%oLeft,%iLower,%iRight,%greybg,%fB +old)); ... # After headers are printed, print data while(my @i = $sth_site->fetchrow_array() ){ $worksheet->write($row,0,$i[1], $workbook->add_format(%oLeft,%iRight,%iLower)); # Location ID # This is where $! is set to "Resource Temporarily Unavailable
      amt.

      perlcheat

        I'm not all that familiar with the internal workings of S::WE, but that seems very odd. Are you sure the write() call is actually failing? I apologize if that's obvious, but I see from the documentation that it returns 0 on success, and your snippet doesn't include code that reports the error, so it seemed worth checking. Also, I haven't any other ideas at all, unless there's some bizarre issue with the filesystem your temp directory is on—I don't think I've ever seen code that produced that particular error. :-\



        If God had meant us to fly, he would *never* have given us the railroads.
            --Michael Flanders

Re: Spreadsheet::WriteExcel with CGI
by davidrw (Prior) on Dec 08, 2005 at 21:28 UTC
Re: Spreadsheet::WriteExcel with CGI
by dragonchild (Archbishop) on Dec 09, 2005 at 04:28 UTC
    Use Excel::Template, the BIG_FILES option, and get_buffer().

    My criteria for good software:
    1. Does it work?
    2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?
Re: Spreadsheet::WriteExcel with CGI
by helphand (Pilgrim) on Dec 11, 2005 at 20:16 UTC

    I've written many spreadsheets far larger than 242 rows with this module, so unless you are hitting the 7meg point, I'd say the problem is something else.

    Your content type looks funny to me, I use

    print "Content-type: application/vnd.ms-excel\n"; print "Content-Disposition: attachment; filename=${fn}\n\n"; my $workbook = Spreadsheet::WriteExcel->new("-");

    As to a workaround, the technique I've used for spreadsheets that involve long db queries to build is to fork off the process in the background and email the resulting spreadsheet file to the user.

    Scott