PHP: NHL-Spielerstatistiken in Excel ausgeben für Fantasy-Leagues

Das Problem

HockeyIch habe mir sagen lassen, dass es auch hier bei uns Leute gibt die in einer Fantasy League mitspielen. Irgendwie werden da virtuelle aber real existierende Sportler gezogen und verkauft und nach einem speziellen Algorithmus werden Punkte verteilt, die abhängig von den tatsächlichen Resultaten sind, die dieser Spieler erzielt. Nun, auf jeden Fall wird da viel mit Statistiken gewurstelt. Ein Fantasy League-Mitglied ist an mich herangetreten, weil er die online publizierten Resultate der Skaters und Goalies der NHL in einem Excel haben wollte.

Zuerst dachte ich, das sei ein Fall für Dapper. War es aber nicht, da auf der Webpage reine Text-Exporte waren.

Die Erfahrung hat gezeigt, dass die Form der Ausgabe auf diesen Seiten oftmals ändert und man sich auf so wenig wie möglich verlassen sollte.

Die Felder dieser Tabelle haben keine Trennzeichen, sondern eine feste Breite und sind links- oder rechtsbündig formatiert.

Einige Punkte der Lösung

Download der Webseite in eine Variable

Zuerst muss man mal an die Seite rankommen. In PHP den Inhalt einer URL in einen String laden ist in der Theorie relativ einfach. In der Praxis jedoch ist aus Sicherheitsgründen der Zugriff auf URLs für die file* Funktionen oftmals eingeschränkt. Nunja, dann wird halt ein cURL-Fallback mit eingebaut, so dass wir auf jeden Fall in $page ein Array aus den Zeilen der HTML-Datei haben:

  if(!($page = @file($url))) {
    $ch = curl_init();
    curl_setopt ($ch, CURLOPT_URL, $url);
    curl_setopt ($ch, CURLOPT_USERAGENT, 'User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.8.1.6) Gecko/20070723 Iceweasel/2.0.0.6 (Debian-2.0.0.6-0etch1+lenny1)');
    curl_setopt ($ch, CURLOPT_HEADER, 0);
    curl_setopt ($ch, CURLOPT_RETURNTRANSFER, 1);
    $result = curl_exec ($ch);
    curl_close ($ch);
    $page=explode("\n",$result);
  }

Überschriften auslesen

Ich lese die Tabellenübberschriften mit einem regulären Ausdruck aus (man beachte dass die Sternchen sich der Formatierung (links/rechts) anpassen), aus dem danach die Spaltenbreiten gelesen werden können. Aus den Spaltenbreiten wiederum kann ein regulärer Ausdruck gebaut werden über den auf die Werte zugegriffen werden kann.

Beispielsweise steht auf der Webpage:

NAME                                   POS GP   G    A PTS SOG +/- PIM PPG SHG

daraus lese ich mit folgendem Regexp die Feldbreiten:

'@^(NAME *)(POS *)(GP)( *G)( *A)( *PTS)( *SOG)( *\\+/\\-)( *PIM)( *PPG)( *SHG)$@'

Damit baue ich dynamisch einen neuen regulären Ausdruck, der die Tabellendaten auslesen kann:

/^(.{39})(.{4})(.{2})(.{4})(.{5})(.{4})(.{4})(.{4})(.{4})(.{4})(.{4})$/

Codiert sieht es so aus. $tline im Codebeispiel beinhaltet eine getrimmte Zeile aus dem HTML.

// Überschriften und Feldgrössen
if(preg_match('@^(NAME *)(POS *)(GP)( *G)( *A)( *PTS)( *SOG)( *\\+/\\-)( *PIM)( *PPG)( *SHG)$@',$tline,$matches)) {
  if(!$inresults) {
    // Nach den Überschriften kommen die Tabelleneinträge
    $inresults=true;
    // Überschriften auslesen
    $headercaptions=preg_split('/ +/', $tline, -1, PREG_SPLIT_NO_EMPTY);
    // Überschriften ausgeben
    echo "\r\n".join("\t",$headercaptions)."\r\n";
  }
  // Wie breit ist ein Feld (in Zeichen)
  $sizes=array();
  for($i=1;$i<count($matches);$i++) {
    $sizes[$i-1]=strlen($matches[$i]);
  }

  // Suchmuster für Einträge aufbauen
  $searchpattern="";
  foreach($sizes as $size) {
    $searchpattern.='(.{'.$size.'})';
  }
  //print $searchpattern."<br />";
  $searchpattern='/^'.$searchpattern.'$/';
  continue;
}
// Tabellenzeilen auslesen
if($inresults && preg_match($searchpattern, $tline, $matches)) {
   $row=array();
   for($i=1; $i<count($matches); $i++) {
     $row[]=trim($matches[$i]);
   }
   // Und ausgeben
   echo join("\t",$row)."\r\n";
   continue;
}

Ausgabe als Excel

Hier bescheisse ich… Ich gebe nur eine tabulatorseparierte Liste aus. Excel schnallts, OpenOffice leider nicht (hat da wer eine gescheitere Lösung? HTML-Tabellen?)…

Zuerst die Headers:

header("Content-type: application/vnd.ms-excel");
header('Content-Disposition: inline; filename="'.$dateiname.'.xls"');

Einzelne Zellen werden mittels Tabulator (\t) voneinander getrennt, Zeilenenden werden durch "\r\n" markiert:

echo join("\t",$row)."\r\n";

Die Lösung

Die komplette Lösung kann hier fantasy-league heruntergeladen werden und eine Demo gibts auf apps.oncode.info

Office 2007 Wordateien (docx) unter (Debian) Linux mit OpenOffice öffnen

Voraussetzungen

Word 2007Office 2007 greift um sich! Immer öfters begegnet man den docx Dateien im OpenXML Format. Die vorgestellte Lösung erlaubt das Öffnen und Ansehen dieser Dateien in OpenOffice. Ob sie allerdings echt Layouttreu sind, habe ich nicht gecheckt. Damit es läuft sollte ein OpenOffice > 2.0 installiert sein → bei mir hats mit 2.2.1 gefunzt.

Vorgehen

Zum Glück wird dee Translator weiterentwickelt. Die entsprechenden Dateinamen müssen einfach angepasst werden. Der Downloadlink sollte generisch sein.

  1. Download des OpenOffice OpenXML Translator – RPM’s von Novell.
  2. Im Verzeichnis drin das RPM entpacken (Dateinamen bitte anpassen):
    rpm2cpio odf-converter-1.0.0-5.i586.rpm | cpio -iv --make-directories
  3. OpenOffice schliessen und die entsprechenden Erweiterungen ins richtige Verzeichnis kopieren:
    cp -r usr/lib/ooo-2.0/* /usr/lib/openoffice/
  4. Die Doku kopieren:
    cp -r usr/share/doc/packages/odf-converter /usr/share/doc/
  5. Mime-XML kopieren:
    cp usr/share/mime/packages/odf-converter.xml  /usr/share/mime/packages/
  6. Mime-Datenbank updaten:
    update-mime-database /usr/share/mime
  7. KDE3 Mime:
    cp opt/kde3/share/mimelnk/application/* /usr/share/mimelnk/application/
  8. Gnome Mime:
    cp opt/kde3/share/mimelnk/application/* /usr/share/mimelnk/application/
  9. Dann noch die Icons:
    cp -r opt/gnome/share/icons/* /usr/share/icons/
  10. Sich freuen!!!

Addition Mai 2008:

Zamzar macht das nun auch online! Ganz stark, und vorallem mit allen Formaten, also auch xlsx (Excel 2007)!

Histogramme in Excel

Aufgabenstellung

In einer Umfrage wurde gefragt, wie viele Minuten für den Arbeitsweg gebraucht und mit welchem Verkehrsmittel sie zurückgelegt werden:

Wieviel Zeit in Minuten benötigen Sie für den Schulweg? Welches Verkehrsmittel benützen Sie?
15 Individualverkehr
25 Öffentlicher Verkehr
5 zu Fuss
40 Individualverkehr
30 Sonstiges
55 Kombiniert
80 Sonstiges
45 Öffentlicher Verkehr

Wie stellt man das als Diagramm dar? Jede einzelne Minute kann es nicht sein, das würde wohl etwas unübersichtlich… Man müsste Bereiche bilden können (bis zu einer Viertelstunde, zwischen einer Viertelstunde und einer halben Stunde, …). Genau hier kommen die Histogramme ins Spiel…

Vorgehen

Für die Diagrammdaten muss eine Tabelle nach folgendem Schema erstellt werden:

A B C D E F G
1 Zeit [Min] Individualverkehr Kombiniert Öffentlicher Verkehr Sonstiges Obergrenze Insgesamt
2 0-15 15
3 16-30 30
4 31-45 45
5 46-60 60
6 61-75 75
7 76-90 90
8 91-120 120
9 >121 9999

Da wir nach Art und Weise wie der Schulweg zurückgelegt wird gruppieren möchten, muss die Tabelle mit den Rohdaten zuerst danach sortiert werden: Daten > Sortieren….

Nun wirds tricky. Folgendes wiederholen für alle leeren Spalten (Individualverkehr, Kombiniert, Öffentlicher Verkehr, …):

  1. Den leeren Zellen eines Bereichs markieren (Beispielsweise B2-B9 für Individualverkehr)
  2. Einfügen > Funktion… > Statistik > HÄUFIGKEIT (FREQUENCY in Englisch). Daten sind die Quelldaten (die Minuten für den Individualverkehr) und die Klassen sind die Obergrenzen (bei diesem Beispiel G2:G9). Und nun (!) die drei Tasten ctrl+shift+Enter (Deutsch: strg+umschalt+Eingabe) gleichzeitigdrücken.

    Haeufigkeitsfunktion im Funktionsassistent

Nachdem alle Arten von Verkehrsmitteln so eingereiht sind, kann ein Diagramm erstellt werden mit den Daten ohne die Obergrenze (A2:F9).

Histogramm

Weiteres

Es gibt auch ein Add-In (Analyse-Funktionen), aber als Kontrollfreak mach ichs gerne von Hand…

Arbeitszimmer hat eine sehr gute, bebilderte einführung.

Excel und Powerpoint mit htdig indizieren

Möchte man xls und ppt in den Index der Intranet Suchengine htdig aufnehmen, muss man etwas Magie wirken lassen:

  1. Es braucht ein Umwandler „gewünscht2txt“. Ich habe xls2csv für Excel und catppt für Powerpoint gewählt.
  2. Die Mimetypes sollten aktualisiert werden. /etc/htdig/mime.types:
    [...]
    application/vnd.ms-excel        xls
    application/vnd.ms-powerpoint   ppt
    [...]
  3. Externe Parser müssen aktiviert werden in /etc/htdig/htdig.conf:
    [...]
    external_parsers: application/msword /usr/share/htdig/parse_doc.pl \
    application/postscript /usr/share/htdig/parse_doc.pl \
    application/pdf /usr/share/htdig/parse_doc.pl \
    application/vnd.ms-powerpoint /usr/share/htdig/parse_doc.pl \
    application/vnd.ms-excel /usr/share/htdig/parse_doc.pl
    [...]
  4. Nun muss noch /usr/share/htdig/parse_doc.pl angepasst werden:
    [...]
    #
    # Excel
    #
    $XLS2CSV = "/usr/bin/xls2csv";#
    # Powerpoint
    #
    $CATPPT = "/usr/bin/catppt";
    [...]
    } elsif ( $ARGV[1] =~  /excel/) {       # it's MS Excel - this detection is a kludge
        $parser = $XLS2CSV;
        # convert all possible sheets to ascii
        $parsecmd = "$parser "$ARGV[0]" |";
        $type = "MS-Excel";
        $dehyphenate = 0;               # Excel documents not likely hyphenated
    } elsif ( $ARGV[1] =~  /powerpoint/) {
        $parser = $CATPPT;
        # convert all possible sheets to ascii
        $parsecmd = "$parser "$ARGV[0]" |";
        $type = "MS-Powerpoint";
        $dehyphenate = 0;
    }
    [...]
  5. Nun sollts funzen.

Man könnte es wohl auch über html machen (mit dem Helfer xlhtml). Dazu müsste die Zeile in htdig.conf heissen:

application/vnd.ms-excel->text/html /usr/share/htdig/parse_doc.pl

und dann müsste inparse_doc.pl xlhtml mit den korrekten Parametern aufgerufen werden.

Generell können folgende Probleme auftreten:

Apache Index:
Indiziert man Verzeichnisse, die das Apache Index Modul erzeugt (Dateibaum ohne index.html), so können Seiten mehrfach gespeichert sein weil sich die Parameter unterscheiden. Um dies zu verhindern muss htdig.conf ergänzt werden:

bad_querystr: ?C=D ?C=S ?C=M ?C=N ?O=A ?D=A ?D=D ?M=A ?M=D ?N=A ?N=D ?S=A ?S=D C=D C=S C=M C=N O=A D=A D=D M=A M=D N=A N=D S=A S=D

Ghostscript-Hanger:
Mein Indexer hatte die Tendenz beim Aufruf von gs zu hangen. Nundenn, ein Umstellen von „pstotext“ auf „pdftotext“ hat das ganze subjektiv schneller gemacht und bis jetzt ist es nimmer gestalled. In /usr/share/htdig/parse_doc.pl:

[...]
#
# set this to your PDF to text converter
#
#$CATPDF = "/usr/bin/pstotext";                         # From "pstotext"
$CATPDF = "/usr/bin/pdftotext";                         # From "pdftotext"
if (! -x $CATPDF) { $CATPDF = "/usr/bin/pdftotext"; }   # From "xpdf"/"xpdf-i"
if (! -x $CATPDF) { $CATPDF = "/usr/bin/ps2ascii"; }    # From a ghostscript
if (! -x $CATPDF) { $CATPDF = "/bin/true"; }
[...]