Der Blog von fm-ProductNode

Excel-Tabellen aus BMEcat generieren

Tabellenkalkulation
Verfasst von franz-mue am 6. November 2017

Per Knopfdruck aus einem BMEcat-Produktkatalog Dateien für die Tabellenkalkulation mit LibreOffice, OpenOffice oder MS Excel generieren - das ist das Thema des Beitrags.

Als Werkzeug dient die Skripting-Plattform von fm-ProductNode. Außerdem zeigt der Beitrag an diesem umfangreicheren Beispiel, wie Open Source-PHP-Komponenten in fm-ProductNode eingebunden werden und wie die Kaskadierung von Template-Aufrufen als Strukturierungsmittel einsetzbar ist.

Aufgabenstellung

Mit dem Aufruf eines Templates der Skripting-Plattform von fm-ProductNode werden Inhalte eines vorhandenen BMEcat-Produktkatalogs in Tabellenkalkulations-Formate konvertiert. Konkret sollen Dateien im MS Excel-Format sowie im ODS-Format der kostenlosen Alternative LibreOffice beziehungsweise OpenOffice generiert werden.

Dem BMEcat-Dokument werden neben Produktdaten auch übergreifende Daten des Kopfbereiches entnommen. Darüber hinaus werden aus dem Kataloginhalt ein paar statistische Daten abgeleitet. Die beiden Ergebnisdokumente enthalten jeweils alle diese Daten, aufgeteilt in drei Arbeitsblätter (= Worksheets).

Ausgangssituation und Vorgehen

Ich gehe hier wieder von einem BMEcat-Dokument aus, das ich schon bei zwei früheren Beiträgen verwendet habe (siehe die "Siehe auch"-Rubrik auf der rechten Seitenleiste. Es handelt sich um einen Produktkatalog aus der Praxis, den ich jedoch inhaltlich etwas verfremdet habe. Nach dem Einlesen eines Katalogs mit dem Dateinamen catalog1.xml durch zum Beispiel das Konsolenkommando

$ php trans.php catalog.read --alias source1 workplace/catalog/catalog1.xml

steht der Katalog innerhalb von fm-ProductNode zur Abfrage und Weiterverarbeitung zur Verfügung.

Der Einfachheit halber werden aus dem Katalog die gleichen Produktdaten abgefragt, die auch schon in den beiden früheren Blog-Beiträgen herangezogen wurden.

Die komplette Verarbeitung und Erzeugung der Tabellenkalkulations-Dateien soll über den Aufruf eines Templates erledigt werden. Alle nötigen Anweisungen sind dazu in Templates abzulegen.

Als Hilfsmittel zur Generierung von Tabellenkalkulations-Dateien wird eine externe PHP-Komponente eingebunden.

Um die nachstehenden Ausführungen im Detail verstehen zu können, verweise ich auf das Benutzerhandbuch und die Schnittstellen-Dokumentation zu fm-ProductNode Release 2.1.

Für die Integration von externen Komponenten sollten PHP-Programmierkenntnisse - insbesondere grundlegende Kenntnisse der objektorientierten PHP-Programmierung vorhanden sein.

Die Ergebnisse

Am Ende der Abarbeitung der Aufgabenstellung stehen Dateien für Tabellenkalkulations-Software. Ich präsentiere gleich am Anfang des Blog-Beitrages einige Screenshots, um die Aufgabenstellung klarer zu veranschaulichen. Die drei nachfolgenden Screenshots zeigen beispielhaft die drei Worksheets der generierten Excel-Datei. Screenshots der ODS-Datei würden nur geringfügig anders aussehen, deshalb verzichte ich auf die Darstellung.

Ich möchte ein paar Besonderheiten zu den Screenshots nicht verschweigen: Es handelt sich um eine Excel-Datei, die ich mir jedoch mit dem Open Source-Werkzeug LibreOffice anzeigen lasse. Ich arbeite nämlich nur mit dem kostenlosen Werkzeug. Am unteren Rand der Screenshots sind Arbeitsblatt-Register zu sehen - allerdings wegen sehr geringer Größe kaum zu erkennen. Das Problem dürfte mit meinem Linux-Notebook zusammenhängen, und daher nicht unbedingt zu verallgemeinern sein.

Als erstes zeige ich einen Ausschnitt des Produkt-Arbeitsblattes:

Produktdaten in Excel Worksheet

Im nächsten Arbeitsblatt sind einige Metadaten des BMEcat-Dokuments aufgelistet:

Metadaten des BMEcat-Produktkataloges

Im dritten und letzten Arbeitsblatt der Datei geht es um ein paar statistische Angaben zum BMEcat-Dokument:

Statistische Angaben zum BMEcat-Produktkatalog

Einbinden von Dritt-Software in fm-ProductNode

Zur Vorbereitung der Konvertierungen in Tabellenkalkulations-Formate dient die Open Source-PHP-Komponente PHPSpreadsheet. PHPSpreadsheet ist die Nachfolge-Software von PHPExcel. Die Komponente ist umfangreich und wird in der PHP-Gemeinde häufig eingesetzt. Online-Dokumentation zur Komponente ist vorhanden.

Zum Einsatz der PHP-Komponente sind diese Schritte zu erledigen:

  1. Die PHP-Komponente unter dem workplace/extension-Verzeichnis von fm-ProductNode zu installieren.
  2. Mit ein paar Zeilen Code eine Zugriffsklasse für die Komponente zu erstellen.
  3. Mit der useExtension-Methode aus einem Template heraus auf die Komponente zugreifen.
  4. Gemäß den Twig-Konventionen die Komponente nutzen

Zur Installation der Komponente nach Punkt 1 wird auf die Dokumentation der Komponente selbst verwiesen. Im wesentlichen geht es um den in PHP-Kreisen gewohnten Aufruf des composer-Werzeuges. Für mich war zu beachten, dass PHPSpreadsheet gegenwärtig nur in einer Beta-Version verfügbar ist. Diese Stabilitätseinschränkung ist bei dem composer-Aufruf zu berücksichtigen. Nach der Installation befindet sich im extension-Verzeichnis ein vendor-Unterverzeichnis. Unter dem vendor-Verzeichnis ist die PHP-Komponente abgelegt.

Als Vorbereitung für die Einbindung der PHP-Komponente wird nach Punkt 2 eine kleine PHP-Klasse erstellt. Die PHP-Klasse erhält von mir den Namen Office. Die Klasse lege ich unter dem extension-Verzeichnis in einer Datei namens Office.php ab. Hier der Inhalt der Datei:

<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Ods;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Writer\Csv;

class Office {

  public function getSpreadsheet() {
    return new Spreadsheet();
  }

  public function getOdsWriter(Spreadsheet $spreadsheet) {
    return new Ods($spreadsheet);
  }

  public function getExcelWriter(Spreadsheet $spreadsheet) {
    return new Xlsx($spreadsheet);
  }

  public function getCsvWriter(Spreadsheet $spreadsheet) {
    return new Csv($spreadsheet);
  }

}

Die Zugriffsklasse beginnt mit einer require-Anweisung, um auf alle benötigten PHP-Klassen von PHPSpreadsheet Zugriff zu erhalten. In der darauf folgenden Klasse sind vier Methoden festgelegt, über die auf die vier benötigten PHP-Klassen von PHPSpreadsheet zugegriffen wird. Zum einen geht es dabei um die Spreadsheet-Klasse. In ein Objekt dieser Klasse werden die Daten aus dem Produktkatalog eingefügt werden. Daneben geht es um drei Klassen zur Ausgabe des Spreadsheets in den Formaten ods, xlsx und csv. Das letztere Format ist kein direkter Bestandteil der Aufgabenstellung; mit der Csv-Klasse soll lediglich ergänzend eine Datei im CSV-Textformat erzeugt werden, nachdem der Zusatzaufwand vernachlässigbar ist.

Ist diese Vorarbeit erst einmal erledigt, kann - siehe obiger Punkt 3 - mit der useExtension-Methode aus einem Template heraus mit PHPSpreadsheet gearbeitet werden. Die Anweisung sieht folgendermaßen aus:

set office = utilities.useExtension('Office')

Die Anweisung bindet die vorher selbst erstellte PHP-Klasse Office ein. Und über die Offlice-Klasse ist PHPSpreadsheet einsetzbar. Konkrete Verwendungsbeispiele sind in den folgenden Abschnitten zu sehen.

Ab jetzt ist die externe Komponente in den Templates nutzbar. Zum Beispiel legt die Anweisung

do office.spreadsheet.properties.setTitle('Product Catalog')

ein spreadsheet-Objekt an, legt durch den Aufruf der Methode properties ein weiteres properties-Objekts an, und setzt in dem Objekt den Titel des Spreadsheets auf "Product Catalog".

Durchführung per Template-Abarbeitung

Die Tabellenkalkulations-Dateien werden durch einen einzigen Aufruf generiert. Auf einer Linux-Konsole sieht der Aufruf so aus:

$ php trans.php task.process --alias source1 --template bmecat2Spreadsheet

source1 gibt dabei das Alias des BMEcat-Dokuments an. Aufgerufen wird ein Template namens bmecat2Spreadsheet. bmecat2Spreadsheet ist das Start-Template, von dem aus die übrigen Templates zur Abarbeitung aufgerufen werden. Es kommen nur Task Templates zum Einsatz; Twig Templates spielen in dem Beitrag keine Rolle.

Für diesen Beitrag wurde die Aufgabe auf viele Templates aufgeteilt. Damit möchte ich zeigen, dass die Template Engines von fm-ProductNode ausreichend Mittel besitzen, eine Aufgabe in Teilaufgaben zu zerlegen und die Teilaufgaben geordnet ablaufen zu lassen.

Die gewählte Template-Aufrufhierarchie sieht so aus:

bmecat2Spreadsheet
  spreadsheet/prepare
    spreadsheet/prepareMetadataWorksheet
    spreadsheet/prepareStatisticsWorksheet
    spreadsheet/prepareProductWorksheet
  addMetadataToSpreadsheet
  addStatisticsToSpreadsheet
  addProductsToSpreadsheet
    bmecat/getProductAttributes
      bmecat/getProductLink
      bmecat/getImageLink
      bmecat/getPrice
      bmecat/getGtin
    addProductToSpreadsheet
  spreadsheet/save
    spreadsheet/saveOds
    spreadsheet/saveExcel
    spreadsheet/saveCsv

Einrückungen sollen die hierarchischen Template-Beziehungen verdeutlichen. Zum Beispiel wird innerhalb von bmecat2Spreadsheet das Template spreadsheet/prepare aufgerufen, welches selbst wiederum das Template spreadsheet/prepareMetadataWorksheet aufruft.

Alle diese Templates liegen unter dem workplace/template/twig-Verzeichnis der fm-ProductNode-Anwendung. Da bei den Templates kein Suffix angegeben ist, wird jeweils automatisch die Endung .twig ergänzt. Im twig-Verzeichnis habe ich zwei Unterverzeichnisse bmecat und spreadsheet angelegt. In diesen Unterverzeichnissen platziere ich Templates, die nur BMEcat-Belange oder nur Spreadsheet-Belange betreffen. Der relative Pfad zum Template wird beim Aufruf der Template Engines mit angegeben, so dass die Template-Namen nach deren Aufgaben gruppiert werden.

Templates aufrufen und synchronisieren

Hier der Inhalt des initialen Templates bmecat2Spreadsheet:

// Prepare

set task = data.task

set log = utilities.log('INFO')
do context.session.addItem('log', log)

set spreadsheetPath = spreadsheetPath|default('productData') // without file suffix
do context.session.addItem('spreadsheetPath', spreadsheetPath)

set language = language|default('deu')
set catalogViewer = repository.currentCatalog.viewer(language)

do task.setTemplateName('spreadsheet/prepare').process

// Collect attributes, insert in spreadsheet and save

do task.setTemplateName('addMetadataToSpreadsheet').addContext('catalogViewer', catalogViewer).process
do task.setTemplateName('addStatisticsToSpreadsheet').addContext('catalogViewer', catalogViewer).process
do task.setTemplateName('addProductsToSpreadsheet').addContext('catalogViewer', catalogViewer).process

do task.setTemplateName('spreadsheet/save').process

Die Anweisungen im Template treffen ein paar Vorbereitungen, anschließend werden andere Templates zur weiteren Verarbeitung aufgerufen. Teil der Vorbereitungen ist die Verwendung eines Protokollierungs-Objektes namens log. Das Objekt thematisiere ich hier nicht weiter, denn das Thema war bereits Gegenstand des vorausgegangenen Blog-Beitrags.

In dem Template werden zwei Mechanismen zum Austausch von Daten zwischen Templates sichtbar:

Der eine Mechanismus betrifft nur den Austausch von Daten zwischen dem aufrufenden und dem aufgerufenen Template. Dazu wird die Methode addContext eines Templates verwendet. Sollen weitere Variablen an das aufgerufene Template übergeben werden, ist die Methode weitere Male aufzurufen. Der Kontext reicht Variablen nur in eine Richtung, hin zu aufgerufenen Template, weiter. Im aufgerufenen Template ist die Variable ohne jede weitere Vorbereitung einsetzbar.

Im aktuellen Beispiel wird das catalogViewer-Objekt weitergegeben. Über das catalogViewer-Objekt erfolgt der Zugriff auf die Inhalte des BMEcat-Kataloges. Das Objekt selbst ist sprachabhängig. Bei der Bereitstellung des catalogViewer-Objekts ist die Sprache festzulegen. Die Festlegung geschieht über die Variable language. Falls beim Aufruf des bmecat2Spreadsheet-Templates keine Variable language als Kontext mit übergeben wurde, wird von der Voreinstellung deu (= deutsch) ausgegangen.

Der zweite Mechanismus bezieht das session-Objekt ein. Das session-Objekt greift zur Ablage von Variablen auf einen gemeinsamen Speicherbereich zu, der allen Twig und Task Templates zugänglich ist. Der gemeinsame Speicherbereich ist auch zur Kommunikation in die Rückrichtung, also zur Übergabe von Variablen zurück zum Aufrufer verwendbar. Bei umfangreicheren Aufgabenstellungen ist bei der Verwendung Vorsicht geboten, um unnötige Seiteneffekte zu vermeiden.

Im vorausgehenden Template wird ein log-Objekt für Protokollierungs-Belange erstellt und über das session-Objekt allen anderen Templates zur Verfügung gestellt. Auch der Ausgabe-Dateipfad wird ohne Dateiendung bestimmt und im session-Objekt abgelegt.

Grundsätzlich steht über das shelf-Objekt ein dritter Mechanismus zum Datenaustausch zur Verfügung, der vor allem zur Persistierung gedacht ist. Im aktuellen Beitrag wird auf diesen Mechanismus nicht zurückgegriffen.

Das Spreadsheet vorbereiten

Aus den BMEcat-Daten soll ein Spreadsheet mit drei Worksheets erstellt werden. Die Vorbereitungen werden über das Template spreadsheet/prepare erledigt, das selbst wieder drei Unter-Templates aufruft - ein Template pro Worksheet:

set office = utilities.useExtension('Office')

if not office
  set log = context.session.item('log')
  do log.error("The spreadsheet extension couldn't be embedded.")
else
  set spreadsheet = office.spreadsheet
  do context.session.addItem('spreadsheet', spreadsheet)

  set properties = spreadsheet.properties
  do properties.setCreator('fm-ProductNode')
  do properties.setLastModifiedBy('fm-ProductNode')
  do properties.setTitle('Product Catalog')
  do properties.setSubject('Product Catalog')
  do properties.setDescription('Product Catalog created from a BMEcat 2005 document.')
  do properties.setKeywords('product catalog datafeed statistics')
  do properties.setCategory('product catalog')

    do data.task.setTemplateName('spreadsheet/prepareMetadataWorksheet').process
    do data.task.setTemplateName('spreadsheet/prepareStatisticsWorksheet').process
    do data.task.setTemplateName('spreadsheet/prepareProductWorksheet').process
endif

In dem Template wird die PHP-Komponente PHPSpreadsheet über die selbst erstellte Office-PHP-Klasse eingebunden. Falls das Einbinden schief geht, endet das Skript mit einer Protokollmeldung. Andernfalls werden erst einmal das Spreadsheet selbst erzeugt und globale Einstellungen für das Spreadsheet via properties-Objekt gesetzt. Das Spreadsheet wird im session-Objekt abgelegt, da es in der Folge noch in einigen Templates benötigt wird. In den Unter-Templates erfolgen anschließend die Vorbereitungen für die einzelnen Worksheets.

Die Worksheets aufsetzen

Das erste Worksheet enthält die gleichen Produktdaten wie die CSV-Dateien in den früheren Blog-Beiträgen. Unter dem Titel Product catalog legt das Template die Spaltennamen fest und führt ein paar optische Formatierungen für die erste Zeile durch.

Die Formatierungen erfolgen mit Hilfe des style-Objektes des Worksheets. Für einen vorab festgelegten Bereich an Zellen (A1:K1) der ersten Worksheet-Zeile wird die Hintergrundfarbe gesetzt, Fettdruck eingestellt, sowie am unteren Ende der Zellen zur Abgrenzung der Kopfzeile von den übrigen Produktdaten eine dünne Linie gezogen.

Hier das Template namens spreadsheet/prepareProductWorksheet:

set spreadsheet = context.session.item('spreadsheet')

set productSheetIndex = 0
do context.session.addItem('sheetIndex.product', productSheetIndex)

do spreadsheet.setActiveSheetIndex(productSheetIndex)
set sheet = spreadsheet.activeSheet
do sheet.setTitle('Product catalog')

set header = [ 'ID', 'Titel', 'Beschreibung', 'Link', 'Bildlink', 'Verfügbarkeit', 'Preis', 'gtin', 'Zustand', 'nicht_​jugendfrei', 'Versandkosten' ]

do sheet.fromArray(header, null,'A1')

set style = sheet.style('A1:K1')
do style.fill.setFillType('solid').startColor.setARGB('FFC4D9C4')
do style.font.setBold(true)
do style.borders.bottom.setBorderStyle('thin') // Does not work in ods

Die Templates für die beiden anderen Worksheets sehen sehr ähnlich aus, und werden deshalb hier nicht mehr aufgeführt; für diese und weitere Details verweise ich auf das korrespondierende GitHub Repository (siehe unten).

Als nächster Schritt ist das Befüllen der drei Arbeitsblätter des Spreadsheets mit BMEcat-Daten an der Reihe.

BMEcat-Metadaten zusammenstellen

Das Template für das Befüllen des entsprechenden Worksheets im Spreadsheet sieht folgendermaßen aus:

set log = context.session.item('log')

set spreadsheet = context.session.item('spreadsheet')
set metadataSheetIndex = context.session.item('sheetIndex.metadata')

do spreadsheet.setActiveSheetIndex(metadataSheetIndex)
set sheet = spreadsheet.activeSheet

set metadata = catalogViewer.metadata

do sheet.fromArray([ 'Katalog-ID', metadata.identifier ], null,'A1')
do sheet.fromArray([ 'Katalog-Version', metadata.version ], null,'A2')
do sheet.fromArray([ 'Katalog-Name', metadata.name ], null,'A3')
do sheet.fromArray([ 'Ersteller', metadata.generatorInfo ], null,'A4')

do sheet.fromArray([ 'Erstellungs-Datum', metadata.generationDate.date ], null,'A5')
do sheet.style('A5').numberFormat.setFormatCode('yyyy-mm-dd')

do sheet.fromArray([ 'Gültigkeitszeitraum von', metadata.defaults.validStart.date ], null,'A6')
do sheet.style('A6').numberFormat.setFormatCode('yyyy-mm-dd')

do sheet.fromArray([ 'Gültigkeitszeitraum bis', metadata.defaults.validEnd.date ], null,'A7')
do sheet.style('A7').numberFormat.setFormatCode('yyyy-mm-dd')

do sheet.fromArray([ 'Verwendete Sprachen', metadata.languages|join(', ') ], null,'A8')
do sheet.fromArray([ 'Vorgabe-Währung', metadata.defaults.currency ], null,'A9')
do sheet.fromArray([ 'Transaktion', metadata.transaction ], null,'A10')

do sheet.columnDimension('A').setAutoSize(true) // does not work in ods

do log.info('Catalog metadata added to spreadsheet.')

Zuerst werden im Template ein paar Vorbereitungen getroffen. Das zu Beginn im session-Objekt abgelegte log-Objekt wird dort entnommen. Ebenso wird das bereits existierende spreadsheet-Objekt und den Index für das benötigte Worksheet bereitgestellt. Aus dem Index wird das Worksheet-Objekt für die Metadaten ermittelt.

Die BMEcat-Ausgangsdaten stehen ohne weitere Vorarbeiten über das catalogViewer-Objekt zur Verfügung, denn es wird beim Aufruf direkt im Template-Kontext übergeben. Der Metadaten- beziehungsweise Kopfanteil des BMEcat-Dokuments ist über die metadata-Methode des catalogViewer-Objekts zugänglich.

Nachdem diese Vorarbeiten geleistet sind, werden die benötigten BMEcat-Metadaten von dem metadata-Objekt abgefragt, und in dem dafür vorgesehenen Worksheet des Spreadsheets mittels fromArray-Methode eingetragen. Pro Attribut werden in einer neuen Zeile zwei Zellen angelegt, nämlich der Name des Attributs sowie der Wert des Attributs.

Die meisten Attribute sind unmittelbar in das Worksheet zu übernehmen. Bei den Datumsangaben, werden die Zellen des Worksheets explizit auf das gewünschte Datums-Format hingewiesen. Abschließend wird das Worksheet angewiesen, die erste Spalte mit den Attribut-Namen der Breite des längsten Namens anzupassen.

BMEcat-Statistikdaten zusammenstellen

Die Angabe einiger statistischer Daten soll helfen, sich einen ersten Eindruck über das BMEcat-Dokument zu verschaffen. Zum Beispiel wird aufgelistet, wieviele Produkte, Klassifikationssysteme, Kataloggruppen, Formeln und IPPS im Dokument enthalten sind. Diese Informationen stehen bei fm-ProductNode zur Verfügung, und können daher unmittelbar abgefragt werden.

Die Zusammenstellung der statistischen Angaben zum Katalog verläuft sehr ähnlich wie das Befüllen des Worksheets für die Metadaten. Daher gebe ich nur noch den Inhalt des Templates ohne weitere Erläuterungen an:

// Prepare

set log = context.session.item('log')

set spreadsheet = context.session.item('spreadsheet')
set statisticsSheetIndex = context.session.item('sheetIndex.statistics')

do spreadsheet.setActiveSheetIndex(statisticsSheetIndex)
set sheet = spreadsheet.activeSheet

// Collect attributes and insert in spreadsheet
//
// Note: trim is used to force conversion to string

do sheet.fromArray([ 'Anzahl Produkte', catalogViewer.productCount|trim ], null,'A1') 
do sheet.fromArray([ 'Anzahl Klassifikationssysteme', catalogViewer.classificationCount|trim ], null,'A2')

set hasGroup = catalogViewer.hasCatalogGroup ? 'Ja' : 'Nein'
do sheet.fromArray([ 'Kataloggruppe vorhanden', hasGroup ], null,'A3')

do sheet.fromArray([ 'Anzahl Formeln', catalogViewer.formulaCount|trim ], null,'A4')
do sheet.fromArray([ 'Anzahl IPPs', catalogViewer.ippDefinitionCount|trim ], null,'A5')

set metadata = catalogViewer.metadata
do sheet.fromArray([ 'Anzahl Parteien', metadata.partyCount|trim ], null,'A6')
do sheet.fromArray([ 'Anzahl Sprachen', metadata.languages|length ], null,'A7')

do sheet.fromArray([ 'Anzahl Rahmenverträge', metadata.agreementCount|trim ], null,'A8')

do sheet.columnDimension('A').setAutoSize(true) // does not work in ods

do log.info('Catalog statistics added to spreadsheet.')

BMEcat-Produktdaten abfragen

Die Produktdaten werden in gleicher Weise dargestellt wie in den früheren Blog-Beiträgen: In der ersten Zeile des Worksheets bezeichnen die Spalten das jeweilige Produktattribut. Darunter sind die Attribute der Produkte aufgelistet. Pro Produkt wird eine eigene Zeile spendiert.

Da die Kopfzeile bereits in einem früher ausgeführten Template angelegt wurde, sind hier nur noch Produkte mit ihren Attributen darzustellen. Falls der BMEcat-Katalog tatsächlich Produkte enthält (also die Variable productCount mit einem Wert größer 0 belegt ist), werden alle Produkte in einer for-Schleife abgearbeitet. Das eigentliche Abfragen und Eintragen der Produktattribute wird in zwei separate Template namens bmecat/getProductAttributes und addProductToSpreadsheet ausgelagert.

Der Inhalt des zugehörigen Templates lautet:

set task = data.task

set log = context.session.item('log')

set productCount = catalogViewer.productCount

if productCount < 1
  do log.warn('The product catalog contains no products!')
else
  do log.info('The product catalog contains ' ~ productCount ~ ' products.')

  for productIndex in 0..(productCount - 1)
    set product = catalogViewer.productByIndex(productIndex)
    do context.session.addItem('product', product)

    do task.setTemplateName('bmecat/getProductAttributes').process

    do task.setTemplateName('addProductToSpreadsheet').addContext('productIndex', productIndex).process

    do log.info('Product No. ' ~ (productIndex + 1) ~ ' inserted in the spreadsheet.')
  endfor
endif

Und so sieht das erste der beiden Templates, bmecat/getProductAttributes, aus:

set task = data.task

set product = context.session.item('product')

set properties = product.properties
do context.session.addItem('properties', properties)

set id = product.supplierProductId|left(50)

set shortDescription = properties.shortDescription|left(150)
set longDescription = properties.longDescription|left(5000)|default('DESCRIPTION MISSING')

set productLink = task.setTemplateName('bmecat/getProductLink').process

set imageLink = task.setTemplateName('bmecat/getImageLink').process

set price = task.setTemplateName('bmecat/getPrice').process

set gtin = task.setTemplateName('bmecat/getGtin').process

set productAttributes = [ id, shortDescription, longDescription, productLink, imageLink, 'in_stock', price, gtin, 'neu', 'nein', 'DE::10 EUR' ]

do context.session.addItem('attributes', productAttributes)

Das vorausgehende Template fragt die benötigten Produktattribute ab und sammelt sie in dem Array productAttributes, das letztendlich an das aufrufende Template zurückgegeben wird. Tatsächlich werden in dem Template nur ein paar Attribute abgefragt; das Abfragen etlicher Attribute wird auf eigene Templates ausgelagert. Bei der ausgelagerten Attributsabfrage handelt es sich um Fälle, bei denen neben der Abfrage noch kleine Justierungen oder Berechnungen durchgeführt werden. Anbei als Beispiel das ausgelagerte Template bmecat/getProductLink:

set product = context.session.item('product')
set log = context.session.item('log')

set productLink = product.mimeInfoByIndex(3).source|left(2000)
if productLink is not valid_url
  set productLink = 'INVALID LINK'
  do log.error("Couldn't find a valid product link.")
endif

print productLink

Die übrigen Templates zur Ermittlung von Produktattributen sehen ähnlich aus, werden deshalb hier nicht mehr separat aufgeführt. Details sind jedoch auf meinem GitHub Repository (siehe unten) einzusehen.

BMEcat-Produktdaten im Spreadsheet zusammenstellen

Stehen die Produktattribut-Werte bereit, sind sie in das Worksheet einzutragen. Für jedes Produkt des BMEcat-Dokuments wird dazu das folgende Template namens addProductToSpreadsheet aufgerufen:

set productAttributes = context.session.item('attributes')

set spreadsheet = context.session.item('spreadsheet')
set productSheetIndex = context.session.item('sheetIndex.product')

do spreadsheet.setActiveSheetIndex(productSheetIndex)
set sheet = spreadsheet.activeSheet

set rowIndex = productIndex + 2

set rowCoordinate = 'A' ~ rowIndex
do sheet.fromArray(productAttributes, null, rowCoordinate)

// Change productLink to clickable url - does not work in ods
set productLink = productAttributes[3] // Index 3 correspondes to column D

if productLink is valid_url
  set productLinkCoordinate = 'D' ~ rowIndex
  do sheet.cell(productLinkCoordinate).hyperlink.setUrl(productLink)
endif

// Change imageLink to clickable url - does not work in ods
set imageLink = productAttributes[4] // Index 4 correspondes to column E

if imageLink is valid_url
  set imageLinkCoordinate = 'E' ~ rowIndex
  do sheet.cell(imageLinkCoordinate).hyperlink.setUrl(imageLink)
endif

Um für die Aufgabe arbeitsfähig zu werden, entnimmt das Template erst einmal die Produktattribute und Spreadsheet-Informationen aus dem session-Objekt. Die Produktattribute werden in der nächsten freien Zeile des Produkt-Worksheets eingetragen. Dazu werden vorher die Koordinaten der Zeile (rowCoordinate) ermittelt. Im Anschluss an den Zeileneintrag justiert das Template zwei Zellen nach. Die beiden Zellen sind für URLs vorgesehen. Wenn also in die Zelle ein Link eingetragen wurde, wird in der Zelle ein klickbarer Link hinterlegt. Zu dem etwas unschönen Nacharbeiten der Link-Zellen entschied ich mich, weil die Auswahl der Zellen über die Koordinaten-Notation leider nicht so flüssig vonstatten geht.

Ergebnisdaten generieren

Sind erst einmal die Worksheets des Spreadsheets erstellt, gelingt die Generierung der Daten im Zielformat sehr einfach. Das folgende Template lagert die Aufgabe auf drei Unter-Templates aus. Jedes Template ist für die Generierung einer Datei in einem Zielformat zuständig. Neben einer MS Excel-Datei und einer ODS-Datei für LibreOffice beziehungsweise OpenOffice wird gleich auch noch eine CSV-Datei generiert - also ein typischer Produktdaten-Feed, wie schon in früheren Blog-Beiträgen erstellt.

// Prepare

set task = data.task

set office = utilities.useExtension('Office')

// Save to filesystem

do task.setTemplateName('spreadsheet/saveOds').addContext('office', office).process
do task.setTemplateName('spreadsheet/saveExcel').addContext('office', office).process
do task.setTemplateName('spreadsheet/saveCsv').addContext('office', office).process

Als Beispiel sei die Erzeugung einer MS Excel-Datei gezeigt:

set log = context.session.item('log')

set spreadsheet = context.session.item('spreadsheet')

set excelWriter = office.excelWriter(spreadsheet)

set spreadsheetPath = context.session.item('spreadsheetPath')

set excelSuffix = '.xlsx'
set excelPath = spreadsheetPath ~ excelSuffix

do excelWriter.save(excelPath)

do log.info('Excel spreadsheet file created at path ' ~ excelPath ~ '.')

Ist das Spreadsheet erst einmal vorhanden, genügt zum Generieren der Datei die save-Methode. Das Template zur Erzeugung der ODS-Datei sieht ähnlich aus, und wird deswegen hier nicht mehr gezeigt.

Entsprechendes gilt für die Erzeugung der CSV-Datei. Die einzige Besonderheit ist hier, dass vor dem Abspeichern festzulegen ist, welches Worksheet verwendet werden soll.

Hinweis zur Generierung von PDF-Dateien

Mit der PHPSpreadsheet-Komponente ist es auch möglich, das Spreadsheet in eine PDF-Datei umzuwandeln und abzulegen. Zu diesem Zweck ist eine weitere PHP-Komponente für die PDF-Generierung auf dem Rechner zu installieren. Die PDF-Generierung konnte ich problemlos durchführen. Allerdings bringt sie im vorliegenden Fall keinen Nutzen: Im PDF-Dokument werden die Inhalte der Arbeitsblatt-Zellen vollständig angezeigt. Einige Spalten enthalten längere Inhalte, so dass sich das Arbeitsblatt mit den Produktdaten auf sehr viele Seiten ausdehnt. Das ganze wird sehr unübersichtlich. Aus diesem Grund ist die Generierung einer PDF-Datei kein Bestandteil des Blog-Beitrags.

Zusammenfassung und Fazit

Der Blog zeigt, wie mit Hilfe von fm-ProductNode Task Templates BMEcat-Produktkataloge in Tabellenkalkulations-Dateien umgewandelt werden können. Für die Generierung der Zieldateien wird auf die PHP-Komponente PHPSpreadsheet zurückgegriffen. Obwohl es sich um umfangreicheres Skripting handelt reichen die Strukturierungsmittel der Task und Twig Templates für die Aufgabe aus.

Der gesamte Code zur Bewältigung der Aufgabenstellung steht in meinem GitHub Repository im examples-Ordner zur Verfügung.

Nicht verschweigen möchte ich, dass es geringe Unterschiede bei den durch PHPSpreadsheet generierten Tabellenkalkulations-Dateien gibt. So werden beispielsweise auf meinem lokalen Rechner hinterlegte Links in Arbeitsblatt-Zellen von Excel-Dateien korrekt übernommen, bei den ODS-Dateien dagegen ist das nicht der Fall. Sonstige Unterschiede sind in der PHPSpreadsheet-Dokumentation vermerkt.

Ein weiterer Hinweis sollte nicht fehlen: Wenn die Anzahl der BMEcat-Produkte den siebenstelligen Bereich erreicht, könnten die Grenzen der Tabellenkalkulations-Software überschritten werden. Jedenfalls bei MS Excel scheint diese Begrenzung zu bestehen.

Tags: BMEcat, Konvertieren, Release2.1, Template, Excel, ODS, Tabellenkalkulation, PHPSpreadsheet, Statistik
Foto: Clker-Free-Vector-Images / pixabay.com

« Produktdaten-Feed - ein zweiter Anlauf - Mein BMEcat-Fachbuch »