Office-Automation mit Perl

Max Maischein

Frankfurt.pm

Office Automation

Microsoft Office
Am Beispiel von Excel
Kurzer Blick auf OpenOffice
Ideen prinzipiell auch mit OOo verwendbar

Microsoft Excel

Perl ist die Schweizer Offizierskettensäge der Programmiersprachen
Excel ist die Schweizer Offizierskettensäge aller Büros

Excel ist überall

Jede Firma verwendet Excel:

Für die Buchhaltung
Für die Datenerfassung
Für den Urlaubsplan
Charts! Charts! Charts!

Perl und Excel

Perl arbeitet gut mit Excel zusammen:

print join("\t", @columns), "\n";
DBD::CSV
SpreadSheet::ParseExcel
SpreadSheet::WriteExcel

Perl und Excel (2)

Die Zusammenarbeit könnte aber noch besser sein, insbesondere für

Umformatieren von Tabellen
Zusammenführen von Daten
Drucken von Dateien
Erstellen von Grafiken

Perl und Excel (3)

Wäre es nicht schön, wenn Perl Excel steuern könnte?

Wenn ich es von Hand machen kann
kann es die Maschine besser

OLE, Win32::OLE und das MS Office Objektmodell

Microsoft hat OLE
Excel ist komplett steuerbar über OLE
Perl hat Win32::OLE

Der Wald vor lauter Bäumen

Es gibt nur ein kleines Problem:

Die Office Dokumentation ist groß
Die Onlinehilfe ist genau so groß
Man macht es schneller von Hand, als in der Onlinehilfe zu suchen.

Play it again, Sam

Der Office Makro-Recorder

zeichnet Vorgänge auf
spielt sie wieder ab
und zeigt uns den Visual Basic Code

Aufgabe

Wir bekommen täglich eine Excel-Datei.

Zeilenüberschriften einfügen
Die Datei muss an einen Verteiler weitergeschickt werden

Analyse

Aufwand der Schritte

Manueller Aufwand für die Gesamtaufgabe maximal 5 Minuten
pro Tag
Langweilig, eintönig
Krankheit? Urlaub?

Get a new plan, Stan?

Makroaufzeichnung einschalten (ALT-F8)
... Excel bedienen ...
Makroaufzeichnung anhalten
Makro im VB-Editor öffnen (ALT-F11)
Modul1 anschauen

Schritt 1

Schritt 2

Schritt 3

Schritt 4

Schritt 5

Erstes Ergebnis

 1:    'Option Explicit 1
 2:    Rows("1:1").Select
 3:    Selection.Insert Shift:=xlDown
 4:    ActiveCell.FormulaR1C1 = "Sprache"
 5:    Rows("1:1").Select
 6:    Range("B1").Activate
 7:    ActiveCell.FormulaR1C1 = "Problem"
 8:    Rows("1:1").Select
 9:    Range("C1").Activate
10:    ActiveCell.FormulaR1C1 = "Zeilen"
11:    Range("A1").Select
12:    ActiveWorksheet.SaveAs "foo.xls"

Exkurs: Excel aus Perl Sicht

Jede Excel-Datei ist ein "Buch" mit vielen Blättern.

Excel hat mehrere Dateien offen
Eine Datei heisst "Workbook"
... enthält mehrere Worksheets
Ein Worksheet ist ein Tabellenblatt (z.B. Tabelle 1)

Exkurs: Excel aus Perl Sicht (2)

Die Liste Workbooks enthält alle offenen Dateien.

Exkurs: Excel aus Perl Sicht (3)

Von allen offenen Dateien ist die aktive Datei besonders ausgezeichnet über die Excel Eigenschaft ActiveWorkbook.

Exkurs: Excel aus Perl Sicht (4)

Innerhalb des aktiven Workbooks gibt es noch das aktive Arbeitsblatt, das direkt erreichbar ist über ActiveWorksheet.

Exkurs: Excel aus Perl Sicht (5)

Die aktuell aktive Eingabezelle ist über ActiveCell erreichbar.

Erstes Ergebnis

 1:    'Option Explicit 1
 2:    Rows("1:1").Select
 3:    Selection.Insert Shift:=xlDown
 4:    ActiveCell.FormulaR1C1 = "Sprache"
 5:    Rows("1:1").Select
 6:    Range("B1").Activate
 7:    ActiveCell.FormulaR1C1 = "Problem"
 8:    Rows("1:1").Select
 9:    Range("C1").Activate
10:    ActiveCell.FormulaR1C1 = "Zeilen"
11:    Range("A1").Select
12:    ActiveWorksheet.SaveAs "foo.xls"

Exkurs: Visual Basic-Syntax

Sehr kurzer Überblick über die Syntax:

Kommentarzeichen:

 1:    ' Dies ist ein Kommentar

Zeilenfortsetzung:

 1:    Dies ist ein Befehl, _
 2:    der über zwei Zeilen geht

Exkurs: Visual Basic-Syntax

Funktionen:

 1:    X = Left("Hello World",5)

Prozeduren:

 1:    Print "Hello World"

Achtung!

 1:    Print ("Hello World",1) ' Fehler
 2:    Print "Hello World",1   ' "Hello World1"

Objektmethoden

Objektmethoden:

 1:    ActiveWorkbook.SaveAs ...
 2:    
 3:    $excel->ActiveWorkbook->SaveAs(...)

Exkurs: Optionale / benannte Parameter

Fehlende/optionale Parameter:

 1:    .SaveAs "myfile.csv",,,xlFormatCSV
 2:    ' Kein Perl Äquivalent

 1:    ->SaveAs("myfile.csv",,,xlFormatCSV)
 2:    # Ist etwas anderes!

Benannte Parameter:

 1:    .SaveAs Filename := "myfile.csv" _
 2:        Format := xlFormatCSV

 1:    $wb->SaveAs( { Filename => "myfile.csv",
 2:        Format => xlFormatCSV })

VB nach Perl übersetzen

Punkt nach ->
Dim Foo As Bar nach my $foo;
Set Plonk = X nach $plonk = $x
Active... nach my $foo =

$Excel-> vor alle "globalen" Aufrufe:

 1:    Workbooks
 2:    # wird zu
 3:    $Excel->Workbooks

Beispiel 1: Speichern einer Datei

VB:

 1:  ActiveWorksheet.SaveAs "foo.xls"

Direkte Übersetzung:

 1:  my $Excel = Win32::OLE->new('Excel.Application');
 2:  my $Worksheet = $Excel->ActiveWorksheet;
 3:  $Worksheet->SaveAs("foo.xls");

Beispiel 1: Massenexport nach CSV

VB:

 1:  ActiveWorksheet.SaveAs "foo.xls"

Bessere Übersetzung:

 1:  use Win32::OLE qw(in);
 2:  Win32::OLE->Warn(3); # croak() bei OLE-Fehler
 3:  
 4:  my $Excel = Win32::OLE->new('Excel.Application');
 5:  my $Worksheets = $Excel->ActiveWorkbook->Worksheets;
 6:  for my $Worksheet (in ($Worksheets)) {
 7:      $Worksheet->SaveAs(...);
 8:  };
 9:  $Excel->ActiveWorkbook->Close();

Einbinden von Typbibliotheken und Konstanten

VB:

 1:  ActiveWorksheet.SaveAs "foo.csv", FileFormat := xlFormatCSV

Übersetzung:

 1:  ...
 2:  ...
 3:  $Worksheet->SaveAs("foo.csv", { FileFormat => xlFormatCSV });

Einbinden von Typbibliotheken und Konstanten

VB:

 1:  ActiveWorksheet.SaveAs "foo.csv", FileFormat := xlFormatCSV

Übersetzung:

 1:  use Win32::OLE::Const 'Microsoft Excel';
 2:  ...
 3:  $Worksheet->SaveAs("foo.csv", { FileFormat => xlFormatCSV });

Konstanten aus dem Internet

Alternativ kann man auch auf die Schnelle eine Konstante durch eine Google-Suche ermitteln:

 1:  Google: xlVeryHidden

Beispiel 2: Präsentieren von CSV-Daten

Ein (Perl) Programm erzeugt ganz viele CSV-Dateien, für jeden Kunden eine.

Die Aufgabe ist es, diese Daten in präsentierbare Form zu bringen.

Datei laden
Spaltentitel einfügen
Pivot-Tabelle erzeugen
Chart aus Tabelle erzeugen
Datei als .xls speichern

Schritt 1

Schritt 2

Schritt 3

Schritt 4

Schritt 5 - Pivot-Table Wizard

Schritt 6

Schritt 7

Schritt 8

Schritt 9

Schritt 10

Schritt 11

Nicht-so-live demo (Spaltentitel)

 1:    Rows("1:1").Select
 2:    Selection.Insert Shift:=xlDown
 3:    ActiveCell.FormulaR1C1 = "Sprache"
 4:    Rows("1:1").Select
 5:    Range("B1").Activate
 6:    ActiveCell.FormulaR1C1 = "Problem"
 7:    Rows("1:1").Select
 8:    Range("C1").Activate
 9:    ActiveCell.FormulaR1C1 = "Zeilen"
10:    Range("A1").Select

Nicht-so-live demo (Pivottabelle)

 1:  ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
 2:      "Tabelle1!R1C1:R10C3").CreatePivotTable TableDestination:="", TableName:= _
 3:      "PivotTable2", DefaultVersion:=xlPivotTableVersion10
 4:  ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
 5:  ActiveSheet.Cells(3, 1).Select
 6:  With ActiveSheet.PivotTables("PivotTable2").PivotFields("Sprache")
 7:      .Orientation = xlRowField
 8:      .Position = 1
 9:  End With
10:  With ActiveSheet.PivotTables("PivotTable2").PivotFields("Problem")
11:      .Orientation = xlColumnField
12:      .Position = 1
13:  End With
14:  ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
15:      "PivotTable2").PivotFields("Zeilen"), "Summe von Zeilen", xlSum
16:  
17:=head1 Nicht-so-live demo (Chart)

 1:    Charts.Add
 2:    ActiveChart.SetSourceData Source:=Sheets("Tabelle5").Range("A3")
 3:    ActiveChart.Location Where:=xlLocationAsNewSheet
 4:    ActiveChart.ChartType = xlRadar
 5:    ActiveChart.ChartType = xlAreaStacked

Nicht-so-live demo (Speichern)

 1:    ActiveWorkbook.SaveAs Filename:= _
 2:        "C:\Dokumente und Einstellungen\xn09999\Eigene Dateien\Mappe1.xls", FileFormat _
 3:        :=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
 4:        False, CreateBackup:=False

Perl Programmierung:

Setup

 1:    my $Excel = Win32::OLE->new('Excel.Application', sub { $_[0]->Quit });
 2:    
 3:    # Immer absolute Pfadnamen verwenden,
 4:    # da Excel ein anderes aktuelles Verzeichnis hat
 5:    my $workbook = $Excel->Workbooks->Add($filename);
 6:    
 7:    # Traditioneller Name
 8:    my $sheet = $workbook->Worksheets("Tabelle1");

Spaltentitel (Perl)

 1:    # Zeilen einfügen
 2:    # Rows("1:1").Select
 3:    # Selection.Insert Shift:=xlDown
 4:    $sheet->Rows("1:1")->Select->Insert( Shift => xlDown );
 5:    
 6:    # ActiveCell.FormulaR1C1 = "Sprache"
 7:    # Rows("1:1").Select
 8:    $sheet->Cells("A1")->FormulaR1C1 = "Sprache";
 9:    
10:    # Range("B1").Activate
11:    # ActiveCell.FormulaR1C1 = "Problem"
12:    $sheet->Cells("B1")->FormulaR1C1 = "Problem";
13:    
14:    # Rows("1:1").Select
15:    # Range("C1").Activate
16:    # ActiveCell.FormulaR1C1 = "Zeilen"
17:    $sheet->Cells("C1")->FormulaR1C1 = "Zeilen";
18:    # Range("A1").Select

Slow-motion (2)

Pivottabelle (Cache)

 1:    # ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
 2:    #    "Tabelle1!R1C1:R10C3").CreatePivotTable TableDestination:="", _
 3:    #    TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10
 4:    my $cache = $workbook->PivotCaches->Add({
 5:        SourceType => xlDatabase, SourceData => 'Tabelle1!R1C1:R10C3'
 6:    });

Tabelle erstellen

 1:    $cache->CreatePivotTable( { TableDestination => "",
 2:        TableName => "PivotTable2",
 3:        DefaultVersion => xlPivotTableVersion10
 4:    });

Slow-motion (3)

 1:    #ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
 2:    $sheet->PivotTableWizard(...);
 3:    
 4:    #ActiveSheet.Cells(3, 1).Select
 5:    #With ActiveSheet.PivotTables("PivotTable2").PivotFields("Sprache")
 6:    #    .Orientation = xlRowField
 7:    #    .Position = 1
 8:    #End With
 9:    my $table = $sheet->PivotTables("PivotTable2");
10:    for ($table->PivotFields("Sprache")) {
11:        $_->Orientation = xlRowField;
12:        $_->Position = 1;
13:    }
14:    
15:    #With ActiveSheet.PivotTables("PivotTable2").PivotFields("Problem")
16:    #    .Orientation = xlColumnField
17:    #    .Position = 1
18:    #End With
19:    for ($table->PivotFields("Problem")) {
20:        $_->Orientation = xlColumnField;
21:        $_->Position = 1;
22:    }
23:    
24:    #ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
25:    #    "PivotTable2").PivotFields("Zeilen"), "Summe von Zeilen", xlSum
26:    $table->AddDataField( $table->PivotFields("Zeilen"),
27:        "Summe von Zeilen", xlSum );

Slow-motion replay (4)

Chart

 1:    # Charts.Add
 2:    my $chart = $workbook->Charts->Add();
 3:    #ActiveChart.SetSourceData Source:=Sheets("Tabelle5").Range("A3")
 4:    #ActiveChart.Location Where:=xlLocationAsNewSheet
 5:    #ActiveChart.ChartType = xlAreaStacked
 6:    #ActiveChart.ChartType = xlRadar
 7:    for ($chart) {
 8:        $_->SetSourceData(...);
 9:        $_->Location( {Where => xlLocationAsNewSheet});
10:        $_->ChartType( xlRadar );
11:    }

Nicht-so-live demo (Speichern)

Speichern

 1:    #ActiveWorkbook.SaveAs Filename:= _
 2:    #    "C:\Dokumente und Einstellungen\xn09999\Eigene Dateien\Mappe1.xls", _
 3:    #    FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
 4:    #    ReadOnlyRecommended:=False, CreateBackup:=False
 5:    $workbook->SaveAs(...);

Wo noch?

Was für Excel klappt, klappt auch für fast alle anderen Office Produkte
Word, PowerPoint
Corel Draw mit Corel Basic

Wo nicht?

IE hat ein OLE-Interface, aber keinen Makro-Recorder
Outlook (bzw. nur sehr begrenztes Modell)
Mozilla/FireFox
OpenOffice 2

Designgedanken

Exportiert eine sinnvolle externe API und verwendet diese selbst:

Microsoft Office
eBay
Erspart anderen Entwicklern eine Menge Frustration

OpenOffice hat eine externe API, aber diese ist komplett unbrauchbar.

Danke

Bonus Level

Perl in Excel

Perl-Features sind auch schon in Excel vorhanden:

Die SVERWEIS Funktion (engl. VLOOKUP) ist das Nachschlagen in einer Tabelle. Also ein Hash Lookup aus Perl.

 1:  =SVERWEIS(A1;B:C;2;FALSCH)

ist das selbe wie:

 1:  my %b_c = (
 2:      Perl => 'http://www.perl.org',
 3:      PHP  => 'http://hardenedphp.de',
 4:      Ruby => 'http://ruby-lang.org',
 5:  );
 6:  print $b_c{ $a1 };

Einzeiler mit Excel-Dateien, auch ohne MS Office

xlsperl / xlsgrep von JJ ("xlstools"):

 1:  xlsgrep "Hello" datei1.xls

 1:  xlsperl -le "s/Excelworkshop/Perlworkshop/" datei1.xls

Einzeiler mit Excel-Dateien, mit MS Office

ExcelPerl Martin Fabiani:

 1:  excelperl -le "s/Excelworkshop/Perlworkshop/" datei1.xls

http://www.perlmonks.org/?node=excelperl

Exkurs: Mehr Visual Basic-Syntax

Zeilenfortsetzung:

 1:    Print _
 2:      "Hello World"

Vergleiche die Shell Syntax

 1:    echo \
 2:        "Hello World"

Exkurs: VB-Stringmanipulation

Konkatenation:

 1:    Print "Hello " & "World"

String-Escapes:

Keine!

 1:    Print "Er sagte " & Chr(34) & "Hello World" & Chr(34) & vbCrLf

Das Spiegeluniversum

In Soviet Russia, Excel automates Perl

Datenimport nach OpenOffice (2)

HTTP::Server::Simple zusammen mit externer Datenverknüpfung in OpenOffice 2 (+live demo?!)

Server starten:

 1:  perl -MHTTP::Server::Simple -e "HTTP::Server::Simple->new->run"

Daten nach OOo importieren:

Einfügen -> Verknuepfung zu externen Daten -> URL -> http://localhost:8080/
Tabelle auswählen
Fertig

Datenim- oder Export via ODBC

Problematisch, da die Administration Administratorprivilegien benötigt.
Funktioniert nicht gut über Rechnergrenzen hinweg
Erst recht nicht über Betriebssystemgrenzen hinweg

OpenOffice Basic

OpenOffice Basic ist OK, aber das Objektmodell ist schrecklich, da es direkt vom Java-Objektmodell übernommen ist:

 1:    sub Testmakro
 2:    rem -------------------------------------
 3:    rem define variables
 4:    dim document   as object
 5:    dim dispatcher as object
 6:    rem -------------------------------------
 7:    rem get access to the document
 8:    document   = ThisComponent.CurrentController.Frame
 9:    dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

 1:    rem -------------------------------------
 2:    dim args1(0) as new com.sun.star.beans.PropertyValue
 3:    args1(0).Name = "StringName"
 4:    args1(0).Value = "X"
 5:    
 6:    dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args1())

OpenOffice Parameterübergabe

 1:    dim args2(1) as new com.sun.star.beans.PropertyValue
 2:    args2(0).Name = "By"
 3:    args2(0).Value = 1
 4:    args2(1).Name = "Sel"
 5:    args2(1).Value = false
 6:    
 7:    dispatcher.executeDispatch(document, _
 8:        ".uno:GoRight", "", 0, args2())

Sinnvoller:

 1:    document.GoRight By := 1, Sel := False

OpenOffice Parameterübergabe (Perlish)

 1:    args2(0).Name = "By"
 2:    args2(0).Value = 1
 3:    args2(1).Name = "Sel"
 4:    args2(1).Value = false
 5:    
 6:    dispatcher.executeDispatch(document, _
 7:        ".uno:GoRight", "", 0, args2())

In Perl:

 1:    sub AUTOLOAD {
 2:        my ($obj,@args) = @_;
 3:        my ($method = $AUTOLOAD) =~ s/.*::(\w+)$/$1/g;
 4:        $dispatcher->executeDispatch($obj,".uno:$method",
 5:            "", 0, \@args);
 6:    }
 7:    $document->GoRight( By => 1, Sel => undef );

21. Februar 2007