I've got a csv-Logfile in a certain format, and have written a perl-Script that converts the log into an excel-File via Win32::OLE.

#!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]

It's rather quick and a little dirty, but maybe it could give you some hints for using or not using Win32::OLE :-)

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

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.