|
Re: capturing STDOUT
by jmcnamara (Monsignor) on Apr 30, 2001 at 03:04 UTC
|
Spreadsheet::WriteExcel accepts a valid filehandle as an argument to new() as of version 0.31. The best way to achieve what you want is via a tied scalar and the IO::Scalar module which is part of the IO::Stringy distribution. For example:
#!/usr/bin/perl -w
use strict;
use Spreadsheet::WriteExcel;
use IO::Scalar;
# Write an Excel file to a string via IO::Scalar.
# Refer to the IO::Scalar documentation for further details.
#
my $a;
tie *XLS, 'IO::Scalar', \$a;
# Create a spreadsheet and write something to it.
#
my $workbook = Spreadsheet::WriteExcel->new(\*XLS);
my $worksheet = $workbook->addworksheet();
$worksheet->write(0, 0, "Hi Excel!");
$workbook->close(); # This is required
# The Excel file is now in $a. As a demonstration, print it to a f
+ile.
#
open TMP, "> test.xls";
binmode(TMP);
print TMP $a;
There are further examples of using filehandles with Spreadsheet::WriteExcel in the filehandle.pl program that is in the examples directory of the distribution.
John.
--
| [reply]
[d/l] |
|
|
The cgi.pl example in the distro takes a different approach:
#!/usr/bin/perl -w
######################################################################
+#########
#
# Example of how to use the Spreadsheet::WriteExcel module to send an
+Excel
# file to a browser in a CGI program.
#
# On Windows the hash-bang line should be something like:
# #!C:\Perl\bin\perl.exe
#
# Dec 2000, John McNamara, jmcnamara@cpan.org
#
use strict;
use Spreadsheet::WriteExcel;
# Set the filename and send the content type
my $filename ="cgitest.xls";
print "Content-type: application/vnd.ms-excel\n";
print "Content-Disposition: attachment; filename=$filename\n\n";
# Create a new workbook and add a worksheet. The special Perl filehand
+le - will
# redirect the output to STDOUT
#
my $workbook = Spreadsheet::WriteExcel->new("-");
my $worksheet = $workbook->addworksheet();
# Set the column width for column 1
$worksheet->set_column(0, 0, 20);
# Create a format
my $format = $workbook->addformat();
$format->set_bold();
$format->set_size(15);
$format->set_color('blue');
# Write to the workbook
$worksheet->write(0, 0, "Hi Excel!", $format);
On my browser, this (1) prompts "do you want to open or save this downloaded file?" and (2) opens TWO windows, one blank. Ugh. Can you comment on your excellent tie soln above, and the alternative as per cgi.pl? Thanks! | [reply]
[d/l] |
|
|
On my browser, this (1) prompts "do you want to open or save this downloaded file?" and (2) opens TWO windows, one blank.
As to why you see two windows, I cannot say. I cannot replicate this behaviour. Maybe someone with more CGI experience could explain this. However, I have noted some small differences in the way Netscape and IE handle the example program. The joys of CGI and browsers, I guess. %^)
If you wish to stream the file to Excel you can set the Content-type as follows:
print "Content-type: application/vnd.ms-excel\n\n";
If you wish to prompt the user to view the file or save it with a specified filename you can do something like the following:
my $filename ="cgitest.xls";
print "Content-type: application/vnd.ms-excel\n";
print "Content-Disposition: attachment; filename=$filename\n\n";
The example that you posted has been updated in more recent versions of the module to highlight these options. However, the view or save options can be overridden by the client browser.
Can you comment on your tie soln above, and the alternative as per cgi.pl?
In a CGI program you have to redirect the Excel file to STDOUT. The easiest way to do this is to use the "Minus" file as a file name in the constructor:
my $workbook = Spreadsheet::WriteExcel->new("-");
The "Minus" file is documented in perlopentut*. The comment in the above example refers to it as a filehandle but it isn't, it is a file name.
As such you do not need to use tie at all in this situation. You would only need to use the above technique if you wanted to do something unusual such as stream the file to a browser and save a local copy.
The option of passing filehandles to the constructor was introduced mainly to allow the files to be streamed over sockets and to allow Spreadsheet::WriteExcel to work with mod_perl.
John.
--
* Does anyone know of anywhere else that this is documented?
| [reply]
[d/l]
[select] |
|
|
|
|
|
Re: capturing STDOUT
by AgentM (Curate) on Apr 30, 2001 at 02:20 UTC
|
You could redirect STDOUT to a pipe using the pipe creation command. Then, when you're done, you can read the entire pipe into a variable.
my $bologna;
{
pipe(READ,WRITE);
local *STDOUT=*WRITE;
local $|=1; #don't forget to unbuffer
(your evil STDOUT hard-coded code)
$bologna=<READ>;
}
print $bologna;
Voila. Pipes kick *ss when it comes to storing variable amounts of data and handle redirection. Of course, this isn't a problem in PerlLand, but I welcome you to CLand, if you'd tag along...
AgentM Systems nor Nasca Enterprises nor
Bone::Easy nor Macperl is responsible for the
comments made by
AgentM. Remember, you can build any logical system with NOR.
| [reply]
[d/l] |
|
|
I think you have the backwards; it should be { local *STDOUT=*WRITE; &evil_STDOUT_code(); }
Nice Weird Al Yankovic reference, by the way.
| [reply]
[d/l] |
|
|
I can't seem to get this to work. I have the following code, but on my linux/perl5.6.0 system, strace says it is blocking on the read.
#!/usr/bin/perl -w
use strict;
my $stuff;
{
pipe(READ,WRITE);
local *STDOUT=*WRITE;
print "Into the pipe\n";
local $/;
$stuff = <READ>;
}
print "Pipe has: $stuff";
Any suggestions? | [reply]
[d/l] |
|
|
Just a guess... But I think that it is still blocking on the READ handle because the WRITE handle is still open.
Yup, just checked. That is what is happening...
#!/usr/bin/perl -w
pipe(READ,WRITE);
print WRITE "Good\n";
close(WRITE);
print <READ>;
Try that code with and without the close(WRITE) line commented out. If you comment it out it hangs.
Here is some more trouble that you're going to run into. Because you need to close the WRITE handle before the <READ> will stop, you are going to run into a buffer limitation (actually this could happen anyway). On many systems a pipe buffer can only accept 4k to 8k before it blocks. It will block until somebody cleans it out by reading the READ end. This means if the output of your command is too big, just executing it will block as it tries to put all of it's info into the WRITE handle.
The way to fix all of this is to pipe, fork, have the parent
READ and the child WRITE.
#!/usr/bin/perl -w
pipe(READ,WRITE);
my $pid = fork;
die $! unless defined $pid;
### parent
if( $pid ){
local $/ = 1; # never do this global
my $out = <READ>;
### child
}else{
# local *STDOUT = *WRITE;
print WRITE "Good\n";
close(WRITE);
exit;
}
print "I got [$out]\n";
my @a=qw(random brilliant braindead); print $a[rand(@a)]; | [reply]
[d/l]
[select] |
Re: capturing STDOUT
by MrNobo1024 (Hermit) on Apr 30, 2001 at 03:11 UTC
|
use IO::Scalar;
tie *STDOUT, 'IO::Scalar', \$a;
# do STDOUT-specific code here
untie *STDOUT;
# and now do something interesting with $a
| [reply]
[d/l] |
|
|
This is a good solution in a general context.
However, one possible problem with this in the context of Spreadsheet::WriteExcel is that on Windows you have to binmode() the filehandle before passing it to the constructor. This means that STDOUT would remain in binary mode after the untie.
John.
--
| [reply] |
