-----Original Message-----
From: Bob Showalter [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 26, 2004 3:10 PM
To: 'Moon, John'; CGI Beginners
Subject: RE: What are you really trying to do? - document type = applicat
ion/vnd.ms-excel
Moon, John wrote:
> What I'm trying to do is make my new boss happy! They like everything
> in Excel.
>
> I have a BUNCH of CGI scripts on a SUN Unix generating tons of very
> nice HTML pages from data in an Oracle database.... I "need" to
> alternately be able to generate the same pages in an Excel
> spreadsheet...
>
> Any thoughts would be greatly appreciated...
Here's an extract from a CGI script of mine that produces an Excel file from
some database info. Before the sub is called, @data is filled with rows of
data. $q is my CGI object. I'm using the CGI, Spreadsheet::WriteExcel and
File::Temp modules.
Basically, I'm writing the Excel worksheet to a temp file and then sending
the contents of that file back to the client (you can also write directly to
the client; see the Spreadsheet::WriteExcel docs; I use the temp file so I
can send a Content-length header.)
(I use multiple write_xxx methods because some of my data looks like
numbers, but I want it treated as text strings.)
sub do_current_stock_status_excel {
# open the worksheet
my ($fh, $fname) = tempfile(undef, UNLINK => 1);
my $xls = new Spreadsheet::WriteExcel($fname);
my $wks = $xls->addworksheet;
# create formats
my $ftext = $xls->addformat(num_format => '@');
my $fdate = $xls->addformat(num_format => 'mm/dd/yy');
my $fquan = $xls->addformat(num_format => '#,##0_);(#,##0)');
my $fcost = $xls->addformat(num_format =>
'#,##0.0000_);(#,##0.0000)');
my $famnt = $xls->addformat(num_format => '#,##0.00_);(#,##0.00)');
# set column info
# each aref contains:
# [0] = Heading
# [1] = Column width
# [2] = format
# [3] = method to use to write the cell
# [4] = data source (coderef, $_ = current row)
my @col = (
[ 'Account', 12, $ftext, 'string', sub { $_->[10] } ],
[ 'Item Group', 6, $ftext, 'string', sub { $_->[6] } ],
[ 'Whse', 5, $ftext, 'string', sub { $_->[0] } ],
[ 'Item Code', 16, $ftext, 'string', sub { $_->[1] } ],
[ 'Description', 40, $ftext, 'string', sub { $_->[4] } ],
[ 'Comm Code', 8, $ftext, 'string', sub { $_->[7] } ],
[ 'Std Matl', 16, $fcost, 'number', sub { $_->[8] } ],
[ 'Std Oper', 16, $fcost, 'number', sub { $_->[9] } ],
[ 'On Hand', 16, $fquan, 'number', sub { $_->[3] } ],
[ 'Ext Matl', 16, $famnt, 'number', sub { $_->[8] * $_->[3] } ],
[ 'Ext Oper', 16, $famnt, 'number', sub { $_->[9] * $_->[3] } ],
[ 'Total', 16, $famnt, 'number', sub { ($_->[8] + $_->[9]) *
$_->[3] } ],
);
my $row = 0;
my $col = 0;
# set worksheet headings
$wks->write_string($row++, 0, "Current Stock Status");
$wks->write_string($row++, 0, "Company: $p{comp} $compname");
$wks->write_string($row++, 0, "As of Date: " .
$mcscgi->format_date($p{date}, '%b %d, %Y'));
$row++;
# set column headings, width, and format
for (@col) {
$wks->set_column($col, $col, $_->[1], $_->[2]);
$wks->write_string($row, $col, $_->[0]);
$col++;
}
# add the raw data
$row++;
for (@rows) {
$col = 0;
for my $c (@col) {
my ($method, $data) = @{$c}[3, 4];
$method = "write_$method";
$wks->$method($row, $col, &$data);
$col++;
}
$row++;
}
$xls->close;
# emit the response
print $q->header(
-type => 'application/vnd.ms-excel',
-Content_length => -s $fh);
print $_ while <$fh>;
}
Thanks again to all who replied and especially to Bob S. for the example....
I have installed all three modules in my local library on the Unix and
tested with a simple CGI script. Process works great ... so far ! I'll
finish the "manglement" reports first of the week.
$workbook = new SpreadSheet::WriteExcel('-'); creates the object "within"
CGI - outputs it - just like the print w/in CGI...
jwm
PS
Bottom posted ! ;-)
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
<http://learn.perl.org/> <http://learn.perl.org/first-response>
