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


Reply via email to