Re: writing excel files

[Chuck Fox]

[EMAIL PROTECTED] wrote:

I need to be able to create Microsoft Excel files from a perl script. 
I want to know which module for doing this that people recommend. I 
don't need anything too fancy. There will be very little formatting. 
What formatting I need to do will be column widths and maybe bold 
and/or underline. I also need to be able to use formulas. Which module 
do people recommend?
Hi,

I just wrote a script for use with Excel from a database. It writes a 
csv compliant file.  My needs were simple for this particular file so I 
chose simple methods. I used the column list construct so that I could 
correctly order the output from the hash, that might be overkill, but I 
still needed the list of columns so that I could output them a column 
headers for the spreadsheet.  There are modules out there that will give 
you a much broader range of features as well as an api to work with 
them.  But for quick and dirty, this works fine.

Regards,

Chuck

<code>
sub DoWork
{
   my( $pServer, $pUser, $pPassword, $pStartDate, $pEndDate ) = @_;
   my( $Query );
   my( $Count, $Error, @Rows );
   my( $Line );
   my( @Record );
   my( $statementHandle );
   my( $data );
   my( $output );
   my( @ColumnList );
   $Query =<<EOF;
SELECT
   session_id,
   wr_promo_code,
   start_time = CONVERT( varchar(30), start_time, 109 ),
   end_time = CONVERT( varchar(30), end_time, 109 ),
   ip_address,
   browser_type,
   browser_version,
   user_agent,
   page_set_id,
   user_id,
   error_id
FROM
   web_reg_sessions
WHERE
   start_time >= ?
AND start_time < ?
EOF
   @ColumnList = ( "session_id", "wr_promo_code", "start_time", 
"end_time", "ip_address", "browser_type", "browser_version", "user_agent",
"page_set_id", "user_id", "error_id" );

   $Connection = DBI->connect( 
"dbi:Sybase:server=$pServer;database=web_reg", $pUser, $pPassword );

   $statementHandle = $Connection->prepare( $Query )
       or die "Count prepare statement: " . $dbh->errstr;
   $statementHandle->execute( $pStartDate, $pEndDate )
       or die "Couldn't execute statement: " . $statementHandle->errstr;
   print $OUT join( ",", @ColumnList );
   print $OUT "\n";
   while( $data = $statementHandle->fetchrow_hashref() )
   {
       $output = "";
       foreach $column ( @ColumnList )
       {
           if( $column eq "start_time" || $column eq "end_time" )
           {
               $tmp = $data->{$column};
               $tmp =~ s/  / /g;
               $tmp =~ s/ /-/g;
               $tmp =~ s/:000//;
               $output .= "\"" . $tmp . "\",";
           }
           else
           {
               $output .= "\"" . $data->{$column} . "\"";
               $output .= "," if $column ne $ColumnList[$#ColumList];
           }
       }
       print $OUT "$output\n";
   }
   $statementHandle->finish;
}
</code>

--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]