#!/usr/bin/perl -w

use strict;
use Spreadsheet::WriteExcel;

# Set the filename and send the content type
my $filename ="cgitest.xls";

print "Content-type: application/vnd.ms-excel\n";
# The Content-Disposition will generate a prompt to save the file. If 
+you want
# to stream the file to the browser, comment out the following line.
print "Content-Disposition: attachment; filename=$filename\n";
print "\n";

# Create a new workbook and add a worksheet. The special Perl filehand
+le "-"
# will redirect the output to STDOUT
#
my $workbook  = Spreadsheet::WriteExcel->new("-");
my $worksheet = $workbook->addworksheet();

# Write to the workbook
$worksheet->write(0, 0, "Hi Excel!");
[download]

There is also the Spreadsheet::WriteExcel::FromDB module which allows you to create Excel files directly from a DB query.

You will find some other suggestions about how to create Excel files here.

--
John.

If it it a Win32-System, you can do so with Win32::ODBC or DBI and DBD::ODBC very easily. Just access Excel like a ODBC-Database, and use the single sheets like tables. Just take care that the Sheetname plus a $ at the end is the tablename.

Or you could use Win32::OLE (have a look at the activestate perl-WinFaq). This has got more overhead, but if you want to format cells or build and execute makros, this might be the only way how it works.

There are also Modules like Excel::Spreadsheet or the like, but I haven't tried them yet.

Best regards,
perl -le "s==*F=e=>y~\*martinF~stronat~=>s~[^\w]~~g=>chop,print"

When I had thsi problem on a linux system I used DBD::CSV to create a tab-seperated database and then used $cgi->redirect to change to it. Systems with Excel installed will treat files with a .csv extension as spreadsheets and convert them to Excel format when loading.

The only problem with this is it will only transmit data, no fancy formatting ;)

This page is intentionally left justified.

Actually, you can really ensure the browser will send it to Excel by sending a content type of application/vnd.ms-excel, even for tab-delimited files. This has worked reliably for me across three versions of Excel.

Chris
M-x auto-bs-mode

------
We are the carpenters and bricklayers of the Information Age.

Don't go borrowing trouble. For programmers, this means Worry only about what you need to implement.

For anyone interested in adopting a similar scheme the Excel XML and HTML file specifications are available here.

--
John.

#!perl -w
use strict;
use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel';

my $logfile = shift || 'ce3.log';
my $excelFile = shift || 'C:\work\perl\win32\ce3log2Excel\ce3.xls';
my @logfileColumns = qw(start date class category module file line mes
+sage);
my $primaryColumn = "start";

# --- main program ---
my $logData = &ReadLogfile($logfile, \@logfileColumns, $primaryColumn)
+;
&CreateNewExcelFile($excelFile, $logData, \@logfileColumns );
# ------------------------------------------------------------
sub ReadLogfile {
    my ($logfile, $logfileColumns, $primaryColumn) = @_;
    
    print "Reading logfile: $logfile\n";
    unless (open (LOG, $logfile)){
    die "Error: couldn't read data from $logfile: $!\n";
    }
    my %log = ();
    while (<LOG>){
    chomp($_);
    my %cols;
    @cols{@{$logfileColumns}} = map{ s/\"//g; $_ } split(/,/, $_);
    $cols{date} = &FormatDateTime( $cols{date} );
    
    push ( @{ $log{ $cols{$primaryColumn} } },  \%cols);
    } # while
    return (\%log);
} # ReadLogfile
# ------------------------------------------------------------
sub CreateNewExcelFile {
    my ($file, $logData, $columns) = @_;
    my $sheets = [ keys %$logData ];
#    print map { "$_\n" } @$sheets;

    print "Creating new Excel-file: $file\n";
    unlink ($file);

    my ($excel, $book) = &ConnectToExcel();
    print "Creating ", scalar(@$sheets), " Sheets\n";
    $book->CreateSheets($sheets, $logData, $columns);
    $book->SaveAsFile($file);
    $book->Close;
} # CreateNewExcelFile
# ------------------------------------------------------------
sub FormatDateTime { # yes, I know about posix...
    my @list = localtime( $_[0] );
    $list[4]++; $list[5] += 1900;
    for (@list){ $_ = sprintf("%02i", $_); }
    return (join(".", @list[5,4,3])." ".join(":", @list[2,1,0]));
} # FormatDateTime
# ------------------------------------------------------------
sub ConnectToExcel {
    # get already open excel or open new
    my $excel = Win32::OLE->GetActiveObject('Excel.Application')
    || Win32::OLE->new('Excel.Application', 'Quit');
    
    my $book = $excel->Workbooks->Add;
    $excel->{Visible} = 1; # to see what is done for testing
    return ($excel, $book);
} # ConnectToExcel
# ------------------------------------------------------------

package Win32::OLE; # additional Methods for OLE...
# ------------------------------------------------------------
sub CreateSheets {
    my ($book, $pagesHRef, $logData, $columns) = @_;
    my (@sheets) = ();
    my $availSheets = $book->Worksheets->{Count};
    my @pages = sort @$pagesHRef;

    for (1..$availSheets){
    $sheets[$#pages-$availSheets+$_] = $book->Worksheets($_);
    }
    for $_ (reverse (0..($#pages-$availSheets))){
    $sheets[$_] = $book->Worksheets->Add;
    }
    for (reverse (0..$#pages)){
    $sheets[$_]->RenameWorkSheet($pages[$_]);
    $sheets[$_]->WriteHeadline($columns);

    my $i = 1;
    foreach my $line (@{ $logData->{ $pages[$_] } }){        
        $sheets[$_]->WriteDataLine($columns, $line, ++$i);
    }
    print "$i rows written to $pages[$_]\n";

    # Spaltenbreite automatisch anpassen
    $sheets[$_]->Columns("A:L")->EntireColumn->AutoFit();

    # Autofilter an
    $sheets[$_]->Columns("A:L")->EntireColumn->AutoFilter();

    # Gruppierungen
    $sheets[$_]->Columns("E:G")->Columns->Group;
    $sheets[$_]->Columns("A:A")->Columns->Group;
    $sheets[$_]->Columns("C:C")->Columns->Group;
#        ExecuteExcel4Macro "SHOW.DETAIL(2,7,FALSE,,4)"


    } # for

} # CreateSheets
# ------------------------------------------------------------
sub RenameWorkSheet {
    my ($sheet, $newName) = @_;
    $sheet->{Name} = $newName;
} # RenameSheet
# ------------------------------------------------------------
sub SaveAsFile {
    my ($book, $file) = @_;
    unlink ($file);
    $book->SaveAs($file);
} # SaveAsFile
# ------------------------------------------------------------
sub WriteHeadline {
    my ($sheet, $columns) = @_;
    my @columns = @$columns;
    $_ = ucfirst($_) foreach (@columns);
    my $lastCol = chr(64 + scalar(@columns));

    $sheet->Range("A1:${lastCol}1")->{'Value'} = \@columns; # text
    $sheet->Range("A1:${lastCol}1")->{'Font'}->{Bold} = 1; # bold

} # WriteHeadline
# ------------------------------------------------------------
sub WriteDataLine {
    my ($sheet, $columns, $line, $index) = @_;
    my %line = %$line;
    my $lastCol = chr(64 + scalar(@$columns));
    $sheet->Range("A$index:$lastCol$index")->{'Value'} = [ @line{@$col
+umns} ];
} # WriteDataLine
# ------------------------------------------------------------
[download]

Best regards,
perl -le "s==*F=e=>y~\*martinF~stronat~=>s~[^\w]~~g=>chop,print"