-----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>