HotXLS erstellt und aktualisiert native XLSX-Pivot-Tabellen aus Delphi and C++Builder heraus, ganz ohne installiertes Excel und ohne COM-Automatisierung auf dem Server. Sie rufen AddPivotTable mit einem Quellbereich auf, platzieren Felder auf Zeilen-, Spalten-, Filter- und Datenachsen, fügen berechnete Elemente oder eine Prozent-von-Gesamtsumme-Anzeige hinzu, und die Komponente schreibt die pivotCacheDefinition- und pivotTableDefinition-Parts, die Excel als Live-Pivot öffnet
Das Szenario, das diese Mühe rechtfertigt, ist ein Reporting-Server. Sie generieren nachts Hunderte von Arbeitsmappen, von denen jede eine Pivot-Tabelle zur Zusammenfassung eines Kontos enthält. Im nächsten Monat ändern sich die Zahlen, und jede Datei muss die neuen Quellzeilen widerspiegeln. Das Ansteuern von Excel über einen Windows-Dienst ist instabil und für den Serverbetrieb nicht lizenziert, und das manuelle Schreiben des Pivot-XML ist ein scheinbar endloses Projekt. HotXLS schließt diese Lücke: Es bietet ein typisiertes Objektmodell über die OOXML-Pivot-Parts, sodass derselbe Pascal-Code, der die Zellen füllt, auch das Pivot deklariert und seinen Cache im selben Prozess neu aufbaut
Wie erstellt man eine Pivot-Tabelle in Delphi ohne Excel?
Sie erstellen eine Pivot-Tabelle mit einem einzigen Aufruf und platzieren anschließend die Felder auf den Achsen. AddPivotTable erwartet den Quellbereich in A1-Notation, die Zielzelle, an der die Tabelle verankert wird, und einen Namen. Die Methode parst den Bereich, scannt jede Spalte, um deren Datentyp abzuleiten, erstellt einen Pivot-Cache (oder verwendet einen bereits an denselben Bereich gebundenen Cache wieder) und gibt ein TXLSPivotTable-Objekt zurück, dessen Felder alle zunächst außerhalb der Achsen liegen. Von dort aus legen die praktischen Methoden AddRowField, AddColumnField, AddPageField und AddDataFieldByName das Layout fest. Jedes Datenfeld kann eine von elf Aggregationen aus dem Enum TXLSPivotAggregation annehmen (xlpaSum, xlpaCount, xlpaAverage, xlpaMax, xlpaMin, xlpaProduct, xlpaCountNums, xlpaStdDev, xlpaStdDevP, xlpaVar, xlpaVarP)
uses
lxHandleX, lxPivot;
var
Book : TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Pivot: TXLSPivotTable;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('sales.xlsx');
Sheet := Book.Sheets[1]; // report sheet (1-based in the XLSX engine)
// Source A1:E500 on the 'Data' sheet; anchor the pivot at row 3, col 1.
Pivot := Sheet.AddPivotTable('Data!$A$1:$E$500', 3, 1, 'SalesByRegion');
if Pivot <> nil then
begin
Pivot.AddRowField('Region');
Pivot.AddColumnField('Quarter');
Pivot.AddPageField('Year');
Pivot.AddDataFieldByName('Revenue', xlpaSum);
Pivot.AddDataFieldByName('Units', xlpaAverage);
Book.SaveAs('sales-pivot.xlsx');
end;
finally
Book.Free;
end;
end;Warum Cache und Tabelle zwei separate Teile sind
Eine Pivot-Tabelle besteht aus zwei Artefakten, die aufeinander verweisen, und das Verständnis dieser Aufteilung ist wichtig. Die pivotCacheDefinition ist die Daten-Momentaufnahme: Eine worksheetSource zeigt auf den Quellbereich und ein cacheField pro Spalte hält die eindeutigen Werte dieser Spalte (ihre sharedItems) sowie abgeleitete Grenzwerte. Die pivotTableDefinition ist die Ansicht: Welches Cache-Feld liegt auf welcher Achse, welche Datenfelder und deren Aggregationen gibt es und wie sehen die Layout-Optionen aus. Die Ansicht wird mit dem Cache über die cacheId der pivotCaches-Beziehung der Arbeitsmappe verknüpft, genau wie es ECMA-376 Teil 1 §18.10 und [MS-XLSX] vorgeben
Diese Indirektion ist keine Bürokratie, sondern bringt zwei Vorteile: Ein Cache kann mehrere Tabellen sichern, sodass die Aktualisierung des Cache alle zugehörigen Tabellen aktualisiert. Und der Cache speichert die Werte jeder Spalte dedupliziert in einer Index-Tabelle pro Datensatz statt im rohen Gitter, weshalb das Erstellen eines Pivots das Scannen der Quelle und nicht das Kopieren von Zellen bedeutet. HotXLS modelliert dies für beide Engines auf dieselbe Weise, sodass der obige Code fast identisch mit dem klassischen Pfad ist, der zusammen mit dem binären SX-Record-Layout hinter klassischen .xls-Pivots dokumentiert ist. Wenn Ihre Quelle auf einem anderen Blatt liegt oder Sie sie über einen Namen ansprechen, folgen der Bereichspräfix sowie die definierten Namen und blattübergreifenden Bezüge den üblichen A1-Regeln, wobei einfache Anführungszeichen für Blattnamen erlaubt sind, die Leerzeichen enthalten
Berechnete Elemente sind keine berechneten Felder
Diese Begriffe bezeichnen drei verschiedene Dinge, und das Verwechseln dieser Begriffe ist ein typischer Fehler bei der Arbeit mit Pivot-Tabellen. Ein berechnetes Element (Calculated Item) lebt innerhalb eines einzigen Feldes und kombiniert die Elemente dieses Feldes namentlich. So können Sie innerhalb eines Region-Feldes eine synthetische Zeile CoreMarkets definieren, die North plus South entspricht. HotXLS stellt dies als TXLSPivotField.AddCalculatedItem bereit und schreibt ein <calculatedItem> unter die <calculatedItems> dieses Feldes. Ein berechnetes Feld (Calculated Field) ist anders: Es ist ein neuer Wert, der aus anderen Spalten abgeleitet wird, wie z. B. die Marge aus Umsatz und Kosten. Es wird als Formel auf einem Cache-Feld über TXLSPivotCacheField.Formula definiert. Ein berechnetes Mitglied (Calculated Member), hinzugefügt mit TXLSPivotTable.AddCalculatedMember, ist ein benutzerdefiniertes Mitglied auf Tabellenebene, das als Kennzahl (Measure) (Mitgliedstyp data) oder Dimensionselement fungieren kann, was vor allem für OLAP-Pivots von Interesse ist
var
Region: TXLSPivotField;
Member: TXLSPivotCalculatedMember;
begin
// A calculated ITEM combines items of a single field by name.
Region := Pivot.AddRowField('Region');
Region.AddCalculatedItem('CoreMarkets', '=North+South');
// A calculated MEMBER is declared at the table level. Member type
// 'data' marks it as a measure; an empty type is a dimension member.
Member := Pivot.AddCalculatedMember('AvgTicket', '=Revenue/Units');
Member.MemberType := 'data';
end;Eine ehrliche Einschränkung gilt für alle drei: HotXLS schreibt den Formeltext in die XML-Definition, berechnet ihn aber nicht. Excel wertet das berechnete Element, das Feld oder das Mitglied beim Öffnen der Datei aus, genau wie es jede Aggregation im Gitter berechnet. HotXLS schreibt die Anweisungen, nicht die Ergebnisse. Die von Ihnen bereitgestellten Formeln müssen also gültige Pivot-Formeln im Excel-Dialekt sein und Feld- sowie Elementnamen so referenzieren, wie Excel es erwartet
Wie zeigt man Werte als Prozent der Gesamtsumme an?
Sie setzen den Anzeige-Modus für die Werte auf dem Datenfeld, statt die Zahlen selbst umzurechnen. TXLSPivotDataField.ShowDataAs erwartet den Enum-Typ TXLSPivotShowDataAs, der den OOXML-Werten ST_ShowDataAs entspricht: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal und xlpsdaIndex. Ein gängiger Trick besteht darin, dieselbe Quellspalte zweimal auf der Datenachse zu platzieren — einmal als Summe und einmal als Anteil an der Gesamtsumme —, damit der Bericht sowohl den Absolutwert als auch das Gewicht anzeigt
var
Rev, Share: TXLSPivotDataField;
begin
Rev := Pivot.AddDataFieldByName('Revenue', xlpaSum);
Share := Pivot.AddDataFieldByName('Revenue', xlpaSum);
Share.DisplayName := 'Share of total';
Share.ShowDataAs := xlpsdaPercentOfTotal;
// Item-relative modes need a base to compare against. A running total
// down 'Quarter' (cache-field index 3) would read:
// Share.ShowDataAs := xlpsdaRunTotal;
// Share.BaseField := 3; // cache-field index the transform runs over
// Share.BaseItem := $7FFD; // $7FFD = "(All)"
end;xlpsdaPercentOfTotal benötigt keine Basis, da die Angabe relativ zur Gesamtsumme ist. Die elementbezogenen Modi benötigen dies jedoch: xlpsdaDifference, xlpsdaPercentDiff und xlpsdaRunTotal erfordern BaseField (den Index des Cache-Feldes, über das der Vergleich läuft) und, für die elementbezogenen Formen, einen BaseItem-Index (wobei $7FFD für das (Alle)-Sentinel steht). HotXLS schreibt diese als Attribute <dataField showDataAs="percentOfTotal" baseField="N" baseItem="M"/> und überlässt die Berechnung ebenfalls Excel
Gruppieren von Daten und Zahlen sowie Filter für Berichtsfilterfelder
Die Gruppierung wird auf dem Cache-Feld und nicht auf dem Pivot-Feld konfiguriert, da sie bestimmt, wie der Quellbereich eingeteilt wird. Setzen Sie HasGroup := True auf dem Cache-Feld, das Sie über Cache.FindFieldByName ermittelt haben, und wählen Sie entweder die numerischen Regler (GroupStartNum, GroupEndNum, GroupInterval) oder die Datums-Hierarchie-Flags (GroupByDate mit GroupMonths, GroupQuarters, GroupYears und einer GroupStartDate / GroupEndDate-Spanne). HotXLS gibt das passende Element <fieldGroup><rangePr groupBy="months"/> aus, sodass ein nach Monaten oder in 1000er-Schritten gruppiertes Feld so geöffnet wird, wie Excel es gruppiert hätte
Berichtsfilterfelder (Page Fields) sind die Filter-Dropdowns über der Tabelle. AddPageField platziert ein Feld auf der Filterachse, und PageItemIndex wählt ein einzelnes Cache-Element aus (Standardwert ist xlPageItemAll, also $7FFD, was „(Alle)“ entspricht). Um einem Leser die Auswahl mehrerer Elemente zu ermöglichen, setzen Sie MultipleItemSelectionAllowed := True, was HotXLS als <pivotField multipleItemSelectionAllowed="1"/> schreibt. Für Kriterien, die über eine manuelle Auswahl hinausgehen, enthält jedes Feld eine typisierte Filters-Sammlung, die die OOXML-Typen ST_FilterType abdeckt — Top10-, Prozent-, Summen-, Beschriftungs-, Werte- und Datumsfilter, wobei jeder Eintrag einen Filtertyp mit seinen Vergleichswerten paart
Wie aktualisiert man einen Pivot-Cache, wenn sich die Quelle ändert?
RefreshPivotCache auf TXLSXWorkbook scannt den Quellbereich neu und baut den Cache in-process auf. Dies ist genau das, was eine automatisierte Daten-Pipeline benötigt, nachdem sie die zugrunde liegenden Zeilen bearbeitet hat. Übergeben Sie die Cache-ID, und die Methode liest jede Zelle im Quellbereich neu ein (Kopfzeile in der ersten Zeile, Daten ab der zweiten), baut den Bereich der eindeutigen Elemente jedes Feldes durch Re-Deduplizierung neu auf, berechnet die Grenzwerte neu und schreibt die Elementindizes pro Datensatz neu. Sie gibt 1 bei Erfolg und -1 zurück, wenn die Cache-ID unbekannt oder das Quellblatt nicht auffindbar ist
var
Rc: Integer;
begin
// ...the source data has changed since the pivot was built...
Sheet := Book.Sheets[1];
Sheet.Cells[2, 5].Value := 128000; // a corrected Revenue figure
// Rebuild the cache's shared items and record indices from the source.
Rc := Book.RefreshPivotCache(Pivot.CacheId); // 1 = refreshed, -1 = cache/source missing
if Rc = 1 then
Book.SaveAs('sales-pivot-refreshed.xlsx');
end;Diese semantische Grenze sollte man sich bewusst machen: Bevor es diese Methode gab, verließ sich HotXLS auf das Flag refreshOnLoad="1" und überließ alle Aktualisierungsarbeiten Excel beim nächsten Öffnen. Das ist in Ordnung, wenn eine Person die Datei öffnet, aber nutzlos in einer automatisierten Pipeline, die korrekte Daten weitergeben muss. RefreshPivotCache aktualisiert den gespeicherten Cache direkt im Prozess. Da Tabellen über die ID an einen Cache gebunden sind, sieht jede auf diesem Cache basierende Pivot-Tabelle die Aktualisierung. Was die Methode nicht tut, ist das Layouten oder Berechnen des sichtbaren Gitters — Excel berechnet die gerenderte Tabelle beim Öffnen der Datei weiterhin selbst aus dem aktualisierten Cache
Was HotXLS schreibt und was Excel berechnet
Behalten Sie diese Arbeitsteilung im Auge, und Sie werden keine Überraschungen erleben: HotXLS schreibt die Definitionen. Es erzeugt die pivotCacheDefinition, die Cache-Datensätze und die pivotTableDefinition mitsamt Achsen, Aggregationen, berechneten Elementen und Mitgliedern, Wertanzeigen, Gruppierungen und Filtern. Excel ist der Kalkulator: Beim Öffnen materialisiert es die Gruppen-Einteilungen, wertet die berechneten Formeln aus, wendet die showDataAs-Transformationen an und aggregiert den Tabellenkörper. Die Werte, die ein Benutzer sieht, stammen von Excel und basieren auf den Anweisungen, die HotXLS geschrieben hat. Jede Formel und jeder Basisindex muss daher beim Schreiben korrekt sein und wird nicht erst beim Rendern überprüft
Zwei Einschränkungen sollten Sie kennen, bevor Sie diese Funktion einplanen: AddPivotTable parst einen rechteckigen A1-Bereich mit optionalem Blattpräfix. Benannte Bereiche und externe Arbeitsmappen als Quellen liegen außerhalb dessen, was der Builder auflösen kann. Allerdings behalten aus einer Excel-Datei gelesene Caches ihre Quellen auf benannte Bereiche beim Round-Trip bei. Und von Excel erstellte Pivots, die HotXLS von der Festplatte liest, werden beim Speichern Byte für Byte unverändert wieder ausgegeben. Die hier beschriebenen typisierten Änderungen gelten also nur für in Code erstellte Pivots, während bestehende Pivots verlustfrei erhalten bleiben. Für die Eingabeseite eines Berichts — die validierten Zellen und gefilterten Tabellen, die das Pivot zusammenfasst — lesen Sie Datenvalidierung, AutoFilter und strukturierte Tabellen
Das hier gezeigte Pivot-Modell ist Teil der standardmäßigen Excel-Komponente HotXLS für Delphi und C++Builder, die sowohl die typisierten XLSX-Pivot-Parts als auch die klassischen BIFF8-Datensätze aus demselben Objektmodell liest und schreibt