Formeln in LibreOffice Calc mit einem perl-Skript erzeugen

Hier kommt mal wieder eine Kleinigkeit, die mich etwas Mühe gekostet hat: ein perl-Skript, das mir ein XLSX-Spreadsheet erzeugt, in dem funktionierende Formeln eingebettet sind. XLSX ist das XML-Dokumentenformat von Microsoft Office, funktioniert aber auch in LibreOffice. Technisch ist es weit umständlicher als das OpenDocument Format und nicht wirklich frei, aber das soll hier nicht das Thema sein.

Ich bin an und für sich kein großer Office-Benutzer und weiß eigentlich auch nur das Nötigste - aber beruflich hab ich halt doch immer mal wieder den Auftrag, irgendwelche Office-Dateien zu verwenden oder zu liefern. In solchen Fällen versuche ich immer, mir mit Perl-Skripten die Handarbeit vom Hals zu schaffen und stattdessen eine schöne Automation zu verwenden ;-)

Kürzlich wollte nun ein Kollege in ein Spreadsheet, das ich für ihn generiere, in mehreren Zellen eine Verknüpfung zu einem anderen Tabellenblatt ("sheet") eingebaut haben.

Ich hatte größere Schwierigkeiten, die richtige Schreibweise für Formelbefehle und die Zellenkoordinaten zu finden - LibreOffice ist hier zu hilfreich und verbirgt über automatische Korrekturen im Calc-Programm viele Eingabefehler vor dem Benutzer - die Schreibweise aus dem GUI ins Skript übernommen hat nicht funktioniert und beim Öffnen der neuen Datei erhielt ich immer eine Fehlermeldung in allen Zellen mit Formel. Interessant daran: wenn ich im Office-Programm die Formel bearbeitete und abspeicherte, fing sie an zu funktionieren, obwohl ich an der Schreibweise nichts geändert hatte (z.B. einfach ein Zeichen gelöscht und dasselbe Zeichen wieder eingetippt). Das war extrem frustrierend, zeigt es doch, dass hier im Sinne der Bequemlichkeit Dinge in der Software unter der Haube passieren, die man als Benutzer nicht erkennen kann.

Außerdem ist die Syntax zwischen Microsoft Office und LibreOffice leicht unterschiedlich. Auch bei /r/LibreOffice und /r/perl waren die Lösungsvorschläge für meine Fragen breit gestreut, was die mögliche Schreibweise der Formeln angeht. Letztendlich hat sich dann eine Schreibweise gefunden, die bei LibreOffice und MS Office funktioniert - hurra!

Für das Experimentieren hatte ich mir ein winziges Perl-Skript geschrieben, das eine XLSX-Datei mit zwei Sheets erzeugt und in einer Zelle des ersten Blatts einen Verweis auf das zweite Blatt einfügt. Beim Öffnen der Datei mit Office wird dann der Verweis ausgeführt und der referenzierte Wert (im Skript unten grün markiert) erscheint dynamisch in der Formelzelle. Der rot markierte Text wird durch die Zuweisung "$u[4]=..." durch die Formel ersetzt. Die Formel enthält noch Platzhalter %d und %s, um mit printf bestimmte Zeilennummern und Spaltennamen einzusetzen, während die XLSX-Datei erzeugt wird. Die write-Methode für eine Zeile des Spreadsheets erwartet eine Referenz auf ein Array mit den Zelleninhalten, deshalb muss ich als Parameter \@l angeben. Es gibt auch speziellere write-Methoden, mit denen man jede Zelle einzeln befüllen kann, aber write ist meistens gut darin, die Datentypen im Array zu erkennen und die Arbeit intern passend zu delegieren.

Das Spreadsheet sieht dann so aus:

Der erste Tab "unix users" enthält in Zelle E1 die Formel =IFERROR( VLOOKUP( A1; $Lookup.$A$1:$B$10000; 2; 0 );"" ). Statt der Platzhalters %d sieht man die Zeilennummer "1" bzw. die Spaltennummer "2" und statt der %s werden die Spaltennamen "A" und "B" eingesetzt, d.h. der Bereich, in dem nachgeschlagen wird, geht von A1 bis B1000 und es wird bei einem Treffer in A1 der Inhalt der 2. Spalte des Bereichs (hier dann B1) übernommen.
Den Formeltext in kursiv im vorigen Absatz habe ich übrigens aus dem Eingabefeld in LibreOffice kopiert. Man sieht, dass die Schreibweise nicht dem entspricht, was im Skript als Formelschreibweise verwendet wird - hier steht plötzlich $Lookup. und nicht Lookup!. Wenn ich mit unzip in die XLSX-Datei hineinschaue (trotz der Endung ist es eine schlichte Zip-Datei!), sehe ich die Schreibweise zwischen den XML-Tags für die Formel <f> ... </f> so wie im Skript: <f>IFERROR(VLOOKUP(A1,Lookup!$A$1:$B$10000,2,0),"")</f>

Und der zweite Tab "Lookup" enthält für jeden Unixuser die Beschreibung für das GECOS-Feld.

Für eine komplexe Geschichte wie eine XLSX-Datei ist das mit knapp 20 Zeilen perl zu bewerkstelligen. Ich liebe dieses Perl-Modul.

#!/usr/bin/perl -w
use strict 'refs';
use strict 'vars';
use utf8;
use Excel::Writer::XLSX; 

my $excelout = 'formel.xlsx';
my $workbook = Excel::Writer::XLSX->new($excelout);
my $lookupfrm=qq{=IFERROR(VLOOKUP(A%d,Lookup!\$%s\$1:\$%s\$1000,%d,0),"")};
my $worksheet;
my @u=("root","*",0,0,"no gecos","/root","/bin/sh");
my $row=0; 

print STDERR "# write Excel unix users\n";
$worksheet=$workbook->add_worksheet('unix users');
$worksheet->keep_leading_zeros();
$u[4]=sprintf($lookupfrm,1+$row,"A","B",2),
$worksheet->write($row, 0, \@u);
print STDERR "# unix user @u\n"; 

$worksheet=$workbook->add_worksheet('Lookup');
my @l=("root","root user");
$worksheet->write($row, 0, \@l);
print STDERR "# lookup @l\n"; 

$workbook->close();