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