Dieser Artikel ist Teil des Magazins 'Access im Unternehmen', Ausgabe 6/2018.
HTML-Kreuztabelle 1: Basics
Kreuztabellenabfragen sind eine praktische Sache, wenn es darum geht, Kombinationen aus m:n-Beziehungen abzubilden – zum Beispiel die Preise für verschiedene Bauelemente oder Materialien in Abhängigkeit von der Höhe und der Breite des Materials. Dabei benötigen Sie noch nicht einmal eine m:n-Beziehung, die in einer Kreuztabelle darzustellenden Daten können auch aus einer einzigen Tabelle stammen. Der Haken an Kreuztabellen ist, dass diese in der Regel nicht bearbeitet werden können. Wenn Sie also etwa den Preis für ein Bauelement mit einer Höhe von einem Meter und einer Breite von 50 Zentimetern einstellen wollen, müssen Sie wieder die zugrunde liegende Tabelle oder das darauf aufbauende Formular bemühen. Im vorliegenden Beitrag wollen wir zunächst einmal die Grundlage für die Bearbeitung schaffen – indem wir die Daten der Tabelle per HTML in Kreuztabellenform darstellen.
Basistabelle
Als Basistabelle verwenden wir die Tabelle tblMaterialpreise. Diese enthält die Felder MaterialpreisID, Breite, Hoehe und Preis. Die Datentypen können Sie dem Tabellenentwurf aus Bild 1 entnehmen.
Hier sehen Sie auch, dass wir für die beiden Felder Hoehe und Breite einen zusammengesetzten, eindeutigen Index definiert haben. Damit stellen wir sicher, dass für jede Kombination aus Höhe und Breite nur ein Preis festgelegt werden kann – mehr können wir in der Kreuztabelle nicht darstellen.
Daten in Kreuztabellenform ausgeben
Die Daten sollen nun so ausgegeben werden, dass die Spaltenüberschriften die Höhe, die Zeilenüberschriften die Breite und die Zellen selbst den jeweiligen Preis für die Kombination aus Höhe und Breite anzeigen. Dazu fügen wir der Tabelle erst einmal ein paar Datensätze zum Spielen hinzu. Die gefüllte Tabelle sieht dann wie in Bild 2 aus.
Danach wollen wir zuerst einmal die Daten in Kreuztabellenform im Direktbereich des VBA-Editors ausgeben. Erst nachdem wir den grundlegenden Ablauf programmiert haben, nehmen wir die Darstellung in einer HTML-Tabelle hinzu.
Die Ausgabe im Direktbereich soll durch die Prozedur Kreuztabelle erfolgen (siehe Listing 1). Diese definiert eine Variable für das aktuelle Datenbankobjekt (db) sowie drei Recordset-Variablen. Das Recordset rstSpaltenkoepfe nimmt alle Werte des Feldes Hoehe der Tabelle tblMaterialpreise auf. Damit jeder Wert nur einmal vorkommt, fügen wir neben SELECT als zweites Schlüsselwort noch DISTINCT hinzu. Auf diese Weise liefert das Recordset jeden Wert des Feldes Hoehe nur einmal, hier 800, 900 und 1.000. Außerdem sortieren wir die Werte nach der Größe. Damit können wir schon einmal die Spaltenüberschriften in den Direktbereich schreiben. Hier beginnen wir mit einer leeren Spalte, was wir durch die Ausgabe einer leeren Zeichenkette plus dem Komma-Zeichen (,) stellvertretend für einen Tabulator-Schritt erledigen. Dann durchlaufen wir in einer Do While-Schleife alle Datensätze des Recordsets rstSpaltenkoepfe und geben nacheinander alle Werte in einer Zeile aus. Damit Debug.Print nach der Ausgabe eines Wertes nicht in die nächste Zeile springt, hängen wir auch hier jeweils das Komma an. Dadurch wird auch hier ein Tabulator eingefügt statt des üblichen Zeilenumbruchs. Damit wäre die Zeile mit den Spaltenköpfen bereits geschafft. Das Ergebnis sieht bisher so aus:
Public Sub Kreuztabelle()
Dim db As DAO.Database
Dim rstSpaltenkoepfe As DAO.Recordset
Dim rstZeilen As DAO.Recordset
Dim rstWerte As DAO.Recordset
Set db = CurrentDb
Set rstSpaltenkoepfe = db.OpenRecordset("SELECT DISTINCT Hoehe FROM tblMaterialpreise ORDER BY Hoehe", dbOpenDynaset) Debug.Print ,
Do While Not rstSpaltenkoepfe.EOF
Debug.Print rstSpaltenkoepfe!Hoehe,
rstSpaltenkoepfe.MoveNext
Loop
Debug.Print
Set rstZeilen = db.OpenRecordset("SELECT DISTINCT Breite FROM tblMaterialpreise ORDER BY Breite", dbOpenDynaset) Do While Not rstZeilen.EOF
Debug.Print rstZeilen!Breite,
Set rstWerte = db.OpenRecordset("SELECT Preis FROM tblMaterialpreise WHERE Breite = " & rstZeilen!Breite _ & " ORDER BY Hoehe", dbOpenDynaset)
Do While Not rstWerte.EOF
Debug.Print rstWerte!Preis,
rstWerte.MoveNext
Loop
Debug.Print
rstZeilen.MoveNext
Loop
End Sub
Listing 1: Ausgabe der Spaltenköpfe, Zeilenköpfe und Preise im Direktbereich
800 900 1000
Damit wir danach mit der ersten Zeile mit Daten fortfahren können, folgt nun der Aufruf der Debug.Print-Anweisung ohne Parameter. Damit springen wir direkt in die nächste Zeile. Das zweite Recordset namens rstZeilen füllen wir mit allen Einträgen des Feldes Breite der Tabelle tblMaterialpreise, diesmal aufsteigend sortiert nach dem Feld Breite. Auch hier verwenden wir das DISTINCT-Schlüsselwort, damit jede Breite nur einmal aufgenommen wird. Dieses Recordset enthält nun also jeweils einen Datensatz für jede Breite, hier 800, 900 und 1.000.
Dieses Recordset durchlaufen wir nun in der nächsten Do While-Schleife und wollen in jedem Durchlauf eine komplette Zeile mit dem Zeilenkopf und den Werten für die Kombination aus Spaltenkopf und Zeilenkopf in den Direktbereich schreiben. Den Zeilenkopf geben wir schon einmal per Debug.Print aus, wobei wir dieser als Parameter den Wert rstZeilen!Breite plus einem Komma für einen Sprung zur nächsten Tabulatorposition statt in die nächste Zeile.
Außerdem brauchen wir für jede Zeile noch ein weiteres Recordset, das die Werte für die aktuelle Zeile und die jeweilige Spalte abruft. Dieses heißt rstWerte und enthält alle Werte des Feldes Preis der Tabelle tblMaterialpreise, deren Feld Breite den Wert des aktuellen Spaltenkopfes enthält – wieder sortiert nach dem Feld Hoehe.
Auch diese Werte durchlaufen wir in einer Do While-Schleife und geben in dieser jeweils den Inhalt des Feldes Preis aus – gefolgt von einem Komma, damit kein Zeilenumbruch, sondern ein Sprung an die nächste Tabulator-Stelle erfolgt. Das Ergebnis nach dem Durchlaufen sieht dann wie folgt aus:
800 900 1000
800 64 72 80
900 72 81 90
1000 80 90 100
Gar nicht schlecht für den Start! Allerdings enthält diese Vorgehensweise eine kleine Schwachstelle. Wir ergänzen die Tabelle tblMaterialpreise wie in Bild 3. Wie Sie sehen, haben wir mit 700 eine neue Höhe eingeführt, aber nur Werte für die Breiten 800 und 1.000 angegeben – die Kombination aus 700 und 900 fehlt. Das Ergebnis im Direktfenster sieht dann wie in Bild 4 aus. Der Preis für die Kombination 700 und 900 wird einfach weggelassen und die übrigen Kombinationen für die Höhe von 700 mm werden einfach nach links verschoben.
Bild 3: Erweiterte Beispieldaten in der Tabelle tblMaterialpreise
Bild 4: Ausgabe der neuen Beispieldaten
Alle Kombinationen ohne Ausnahme ausgeben
Wie bekommen wir es also hin, dass alle Kombinationen berücksichtigt werden, auch wenn ein Wert nicht angegeben ist – in der Form, dass die entsprechende Stelle einfach frei gelassen wird?
Dazu müssen wir eine kleine Ergänzung vornehmen, und zwar innerhalb der zweiten Do While-Schleife (siehe Listing 2). In dieser fügen wie zunächst eine Anweisung ein, welche den Datensatzzeiger des Recordsets, das wir zum Einfügen der Spaltenköpfe verwendet haben, für jeden Datensatz des Recordsets rstZeilen auf den ersten Datensatz zurücksetzt (MoveFirst). Warum das? Weil wir sicherstellen wollen, dass die Markierung im Direktfenster für jeden Spaltenkopf in jeder Zeile auch um eine Tabulatorposition vorrückt – unabhängig davon, ob es auch einen Wert für die Kombination aus den im Spalten- und Zeilenkopf angezeigten Werten gibt. Innerhalb der inneren Do While-Schleife, in der wir bisher einfach nur die Werte des Feldes Preis in den Direktbereich geschrieben haben, fügen wir nun eine If...Then-Bedingung ein, die prüft, ob der Wert des Feldes Hoehe des Recordsets rstSpaltenkoepfe mit dem des Recordsets rstWerte übereinstimmt. Ist dies der Fall, wird der Inhalt des Feldes Preis ausgeben und wir gehen mit der MoveNext-Methode einen Schritt weiter im Recordset rstWerte. Falls nicht, geben wir nur einen Tabulatorschritt im Direktfenster aus. In beiden Fällen bewegen wir den Datensatzzeiger des Recordsets rstSpaltenkoepfe um eine Position weiter. Auf diese Weise geben wir für Kombinationen, für die es keinen Preis gibt, einen Tabulatorsprung im Direktbereich aus. Das Ergebnis sieht nun wie in Bild 5 aus.
Bild 5: Ausgabe der neuen Beispieldaten mit Leerstellen
Public Sub Kreuztabelle()
'... wie in der ersten Fassung
Set rstZeilen = db.OpenRecordset("SELECT DISTINCT Breite FROM tblMaterialpreise ORDER BY Breite", dbOpenDynaset) Do While Not rstZeilen.EOF
rstSpaltenkoepfe.MoveFirst
Debug.Print rstZeilen!Breite,
Set rstWerte = db.OpenRecordset("SELECT Preis, Hoehe FROM tblMaterialpreise WHERE Breite = " _ & rstZeilen!Breite & " ORDER BY Hoehe", dbOpenDynaset)
Do While Not rstWerte.EOF
If rstSpaltenkoepfe!Hoehe = rstWerte!Hoehe Then
Debug.Print rstWerte!Preis,
rstWerte.MoveNext
Else
Debug.Print ,
End If
rstSpaltenkoepfe.MoveNext
Loop
Debug.Print
rstZeilen.MoveNext
Loop
End Sub
Listing 2: Ausgabe der Spaltenköpfe, Zeilenköpfe und Preise im Direktbereich auch mit leeren Kombinationen
Damit können wir die grundlegende Ausgabe als erledigt betrachten und uns der ersten Erweiterung zuwenden – der Ausgabe im HTML-Format im Webbrowser-Steuerelement von Access.
Webbrowser-Steuerelement anlegen
Dazu legen wir ein neues Formular namens frmKreuztabelleHTML an und fügen diesem ein Webbrowser-Steuerelement aus der Toolbox hinzu. Danach stellen sie noch seine Eigenschaften Horizontaler Anker und Vertikaler Anker auf den Wert Beide ein, damit sich die Größe des Webbrowser-Steuerelements beim Verändern der Größe des Formulars an dieses anpasst (siehe Bild 6).
Dann wollen wir zunächst dafür sorgen, dass beim Öffnen des Formulars eine leere Webseite erscheint. Dazu fügen wir dem VBA-Projekt der Anwendung zunächst einen Verweis auf die Bibliothek Microsoft HTML Object Library hinzu – und zwar über den Dialog, den Sie mit dem Menübefehl Extras|Verweise öffnen (siehe Bild 7). Danach legen wir eine neue Ereignisprozedur für das Ereignis Beim Öffnen des Formulars an. Die dort verwenden Variablen deklarieren wir im allgemeinen Teil des Klassenmoduls des Formulars:
Dim WithEvents objWebbrowser As WebBrowser
Dim objDocument As HTMLDocument
Die erste Variable nimmt einen Verweis auf das Webbrowser-Steuerelement auf, die zweite einen auf das darin angezeigte Dokument. Die Variablen füllen wir mit der Ereignisprozedur Form_Open, die wir wie folgt erweitern:
Private Sub Form_Open(Cancel As Integer)
Set objWebbrowser = Me!ctlWebbrowser.Object
Me.TimerInterval = 300
End Sub
Die Prozedur weist zuerst der Variablen objWebbrowser den Verweis auf das Webbrowser-Steuerelement zu. Wir könnten nun direkt mit der Navigate-Methode den Wert about:blank als Zielseite einstellen. Allerdings führt das manchmal zu Problem. Abhilfe schafft dann das verzögerte Laden dieser Seite, also aktivieren wir hier den Timer für das Formular, indem wir die Eigenschaft TimerInterval auf den Wert 300 einstellen (das entspricht 300 Millisekunden).
Erst in der für das Ereignis Bei Zeitgeber angegebenen Ereignisprozedur navigiert sie mit der Navigate-Methode zu einer leeren Seite namens about:blank. Außerdem setzen wir noch die Variable objDocument auf das im Webbrowser angezeigte Dokument. Schließlich setzen wir Me.TimerInterval auf den Wert 0, damit der Zeitgeber deaktiviert wird.
Private Sub Form_Timer()
objWebbrowser.Navigate "about:blank"
Set objDocument = objWebbrowser.Document
Me.TimerInterval = 0
End Sub
Damit erhalten wir nach dem Öffnen des Formulars das Bild aus Bild 8.
Füllen mit HTML-Kreuztabelle
Nun wollen wir die Ausgabe, die wir bereits in den Direktbereich vorgenommen haben, an den Browser schicken. Dazu bedarf es noch einiger Erweiterungen, nämlich in Form von HTML-Tags. Dabei wollen wir gar nicht erst damit anfangen, HTML in Form einer Zeichenkette zusammenzustellen und dann dem Dokument als Inhalt zuzuweisen. Wir wollen gleich auf der Objekt-Ebene arbeiten und damit die Grundlage schaffen, später auf Ereignisse der Elemente reagieren zu können. Dafür ist zwar noch ein weiterer Schritt nötig, aber die nachfolgend beschriebene Prozedur zeigt, wie Sie ein HTML-Dokument auf Basis von HTML-Objekten aufbauen. Hier kommt nun auch der Verweis auf die Bibliothek Microsoft HTML Object Library zum Tragen, den wir bereits weiter oben angelegt haben. Die neue Funktion KreuztabelleErstellen rufen wir von der Ereignisprozedur Form_Timer auf, die wir wie folgt ergänzen:
Private Sub Form_Timer()
objWebbrowser.Navigate "about:blank"
Set objDocument = objWebbrowser.Document
Me.TimerInterval = 0
DoEvents
KreuztabelleErstellen
End Sub
Die Prozedur aus Listing 3 verwendet einige neue Variablen, welche die Verweise auf die erstellten Objekt wie die HTML-Tabelle, die Zeile und die Zelle aufnehmen:
Private Sub KreuztabelleErstellen()
Dim db As DAO.Database
Dim rstSpaltenkoepfe As DAO.Recordset
Dim rstZeilen As DAO.Recordset
Dim rstWerte As DAO.Recordset
Dim objTable As MSHTML.HTMLTable
Dim objRow As MSHTML.HTMLTableRow
Dim objCell As MSHTML.HTMLTableCell
Set db = CurrentDb
Set objTable = objDocument.createElement("Table") objDocument.body.appendChild objTable
Set rstSpaltenkoepfe = db.OpenRecordset("SELECT DISTINCT Hoehe FROM tblMaterialpreise ORDER BY Hoehe", dbOpenDynaset) Set objRow = objTable.insertRow
Set objCell = objRow.insertCell
Do While Not rstSpaltenkoepfe.EOF
Set objCell = objRow.insertCell
objCell.innerText = rstSpaltenkoepfe!Hoehe
rstSpaltenkoepfe.MoveNext
Loop
Set rstZeilen = db.OpenRecordset("SELECT DISTINCT Breite FROM tblMaterialpreise ORDER BY Breite", dbOpenDynaset) Do While Not rstZeilen.EOF
Set objRow = objTable.insertRow
rstSpaltenkoepfe.MoveFirst
Set objCell = objRow.insertCell
objCell.innerText = rstZeilen!Breite
Set rstWerte = db.OpenRecordset("SELECT Preis, Hoehe FROM tblMaterialpreise WHERE Breite = " _ & rstZeilen!Breite & " ORDER BY Hoehe", dbOpenDynaset)
Do While Not rstWerte.EOF
If rstSpaltenkoepfe!Hoehe = rstWerte!Hoehe Then
Set objCell = objRow.insertCell
objCell.innerText = rstWerte!Preis
rstWerte.MoveNext
Else
Set objCell = objRow.insertCell
End If
rstSpaltenkoepfe.MoveNext
Loop
rstZeilen.MoveNext
Loop
End Sub
Listing 3: Ausgabe der Spaltenköpfe, Zeilenköpfe und Preise als HTML-Objekte
Dim objTable As MSHTML.HTMLTable
Dim objRow As MSHTML.HTMLTableRow
Dim objCell As MSHTML.HTMLTableCell
Nach dem obligatorischen Erstellen des Verweises auf die aktuelle Datenbank erstellen wir mit der createElement-Methode des Objekts objDocument ein neues Table-Element. Dieses schwebt noch im luftleeren Raum, daher müssen wir es noch dem body-Element des Dokuments hinzufügen, und zwar als Parameter der appendChild-Methode. Danach legen wir eine neue Zeile an (die erste) und eine neue Zelle (ebenfalls die erste, nämlich die, welche oben links die Spalten- und die Zellenüberschriften kreuzt). Dazu nutzen wir die insertRow-Methode des objTable-Objekts, deren Ergebnis wir in der Variablen objRow speichern. Die neue Zelle fügen wir dann dem objRow-Objekt über seine Methode insertCell hinzu und referenzieren es mit der Variablen objCell.
Danach fügen wir die Spaltenüberschriften ein, und zwar in der gleichen Do While-Schleife wie in der vorherigen Prozedur. Hier kommt dann in jedem Durchlauf die insertCell-Methode zum Einsatz, die jeweils eine neue Zelle hinzufügt. Diese sollen aber nicht mehr leer sein, sondern die Spaltenüberschrift anzeigen. Dazu weisen wir der Eigenschaft innerText des jeweils neu hinzugefügten Objekts den Namen der Spalte zu, in dem Fall entnommen aus dem Feld Hoehe des Recordsets rstSpaltenkoepfe. Danach folgt die verschachtelte Do While-Schleife, in der wir die weiteren Zeilen und Zellen zum HTML-Dokument hinzufügen. Hier kommen wieder die beiden Methoden insertRow und insertCell zum Einsatz. Der Aufbau der beiden Do While-Schleifen entspricht dem im vorherigen Beispiel. Wir legen also immer erst eine neue Zeile an und fügen dann die Zellen zu dieser Zeile hinzu. Daraus entsteht dann die Kreuztabelle aus Bild 9. Die Ansicht ist natürlich noch nicht besonders hübsch, also fügen wir noch ein paar Formatierungen hinzu.
CSS-Formatierungen hinzufügen
Um Formatierungen mit CSS hinzuzufügen, benötigen wir zwei Dinge: Eine Datei oder einen String mit den CSS-Formatierungen und Auszeichnungen der zu formatierenden Elemente, die dann in den CSS-Formatierungen referenziert werden. Bei diesen Auszeichnungen kann es sich ganz allgemein um die Elementnamen handeln, also etwa TABLE, TR oder TD. Es kann aber auch eine spezielle Auszeichnung sein, die durch die Eigenschaft className für das jeweilige Element festgelegt wird. Im Folgenden haben wir die Prozedur KreuztabelleErstellen entsprechend angepasst. Die ersten Zeilen deklarieren und erstellen ein Objekt, das die CSS-Definitionen aufnehmen wird. Dieses legen wir mit der Methode createStyleSheet als neues Objekt an. Dann weisen wir seiner Eigenschaft cssText den Wert der Funktion CSS zu:
Private Sub KreuztabelleErstellen()
...
Dim objCSS As Object
Set objCSS = objDocument.createStyleSheet("") objCSS.cssText = CSS
...
Für das Table-Element, das eine eigene CSS-Auszeichnung enthalten wird, die alle grundlegenden Definitionen enthält, brauchen wir nichts weiter zu tun:
Set objTable = objDocument.createElement("Table") ...
Das Cell-Objekt oben links statten wir mit den beiden Klassen columnHeader und rowHeader aus:
Set objCell = objRow.insertCell
objCell.className = "columnHeader rowHeader"
Dadurch soll es später die Eigenschaften übernehmen, die wir für diese beiden Klassen festlegen. Im Wesentlichen wird das ein Rahmen links (columnHeader) und ein Rahmen oben (rowHeader) sein. Die übrigen Elemente, die als Spaltenköpfe dienen, definieren wir mit der Klasse columnHeader:
Do While Not rstSpaltenkoepfe.EOF
Set objCell = objRow.insertCell
objCell.className = "columnHeader"
...
Loop
...
Schließlich fehlen noch die Zeilenköpfe, die wir als rowHeader markieren:
Do While Not rstZeilen.EOF
...
Set objCell = objRow.insertCell
objCell.className = "rowHeader"
...
Loop
End Sub
Der resultierende HTML-Code
Der HTML-Code sieht für unsere Beispieldaten nun wie folgt aus – hier können Sie erkennen, welche Elemente welchen Elementtyp aufweisen und mit welcher Klasse diese versehen sind:
<table>
<tbody>
<tr>
<td class="header rowheader"></td>
<td class="header">700</td>
...
</tr>
<tr>
<td class="rowheader">800</td>
<td>56</td>
...
</tr>
...
</tbody>
</table>
Die CSS-Definition
Die CSS-Definition stellen wir in einer eigenen String-Funktion zusammen, die wir CSS nennen. Sie sieht wie folgt aus und definiert zunächst die Eigenschaften, die tabellenweit gelten sollen – dementsprechend legen wir diese für das Element table fest. Hier definieren wir die Schriftart (Calibri), die Textausrichtung (center) sowie die Breite der Tabelle gemessen an der HTML-Seite (100%, also Ausdehnung über den gesamten Browser) und legen fest, dass Rahmen von zwei benachbarten Elementen zusammengefasst werden sollen (border-collapse: collapse):
Public Function CSS()
Dim strCSS As String
strCSS = strCSS & "table {" strCSS = strCSS & " font-family: Calibri;"
strCSS = strCSS & " text-align: center;"
strCSS = strCSS & " width: 100%;"
strCSS = strCSS & " border-collapse: collapse;"
strCSS = strCSS & "}"
Danach geht es weiter mit der Klasse columnHeader für die td-Elemente. Diese legt fest, dass der enthaltene Text fett ausgegeben werden soll (font-weight: bold) und dass der untere Rand der Zelle mit einer Linie der Breite 1px versehen werden soll – diese soll durchgezogen sein (solid) und die Farbe schwarz haben (black):
strCSS = strCSS & "td.columnHeader {" strCSS = strCSS & " font-weight: bold;"
strCSS = strCSS & " border-bottom: 1px solid black;"
strCSS = strCSS & "}"
Auf ähnliche Weise definieren wir die Klasse td.rowHeader. Der Unterschied ist, dass es hier eine Rahmenlinie am rechten Rand geben soll:
strCSS = strCSS & "td.rowHeader {" strCSS = strCSS & " font-weight: bold;"
strCSS = strCSS & " border-right: 1px solid black;"
strCSS = strCSS & "}"
CSS = strCSS
End Function
Nach dem erneuten Aufruf des Formulars sieht die HTML-Tabelle nun wie in Bild 10 aus.
Zusammenfassung und Ausblick
Damit ist die erste Version zur Ausgabe einer Kreuztabelle per HTML fertig. Neue Datensätze werden beim nächsten Aufruf wie in Bild 11 angezeigt. Im Beitrag HTML-Kreuztabelle 2: Werte bearbeiten (www.access-im-unternehmen.de/1162) zeigen wir, wie Sie die Daten direkt in der Kreuztabelle editieren können.
