... #use strict; # deaktiviert wegen Excel-Sortierung use Net::LDAP; use Date::Calc qw(Delta_Days Today); use Win32::OLE; use Win32::OLE::Const 'Microsoft Excel'; $Win32::OLE::Warn = 3; #Fehler genau ausgeben ... ######### # # Methode die Excel-Datei erzeugt ## sub createExcel { # Lokale Variaben: my @toDo = @_; my $EVAL_ERROR; my $excel; my $book; my $sheet; showLine(); print "FUNCTION createExcel\n"; print "Try to open Excel.\n"; # falls Instanz von Excel schon läuft benutze diese: eval {$excel = Win32::OLE->GetActiveObject('Excel.Application')}; return 1 if $EVAL_ERROR; #wenn $excel noch nichts zugewiesen starte Excel: unless (defined $excel) { $excel = Win32::OLE->new('Excel.Application') #, sub {$_[0]->Quit;} or return 1; } print "Excel ok.\n"; # Excel sichtbar/unsichtbar machen $excel->{Visible} = 1; # Alerts vertecken (0=verstecken) $excel->{DisplayAlerts} = 0; # Erzeuge leere Tabelle: $book = $excel->Workbooks->Add; $sheet = $book->Worksheets(1); print "Write to File. \n"; my $length = @toDo; if ($length eq 0) { $sheet->Cells(1,1)->{Value} = "Keine passenden Einträge gefunden!"; } else { # Kopfzeile einfügen: $sheet->Cells(1,1)->{Value} = "Last Login"; $sheet->Cells(1,2)->{Value} = "Days"; $sheet->Cells(1,3)->{Value} = "Fullname"; $sheet->Cells(1,4)->{Value} = "Context"; $sheet->Range('A1:E1')->Font->{Bold}=1; $sheet->Range('A1:E1')->Font->{ColorIndex}=2; $sheet->Range('A1:E1')->Interior->{ColorIndex}=5; # Werte eintragen for (my $i=0; $i<$length; $i++) { $sheet->Cells($i+2,1)->{Value} = $toDo[$i][0]; $sheet->Cells($i+2,2)->{Value} = $toDo[$i][1]; $sheet->Cells($i+2,3)->{Value} = $toDo[$i][2]; $sheet->Cells($i+2,4)->{Value} = $toDo[$i][3]; } # Breite anpassen: my @columns = qw(C:D); foreach my $range (@columns) { $sheet->Columns($range)->AutoFit(); } # Sortieren my $excelLength = $length + 1; $sheet->Range("A2:D$excelLength")->Sort({ Key1 => $sheet->Range("a1"), Order1 => xlDescending }) } return 0; }