HotXLS setzt die „Show Values As“-Modi von Excel-Pivots aus Delphi über zwei Properties an TXLSPivotDataField: ShowDataAs für die klassischen Modi wie Running Total, Difference und Percent of Row, und ExtendedShowDataAs für Rank, Percent of Parent und Running Total Percent. TXLSXWorkbook.MaterializeNativePivotTable schreibt danach das berechnete Grid so, wie Excel es täte. Die beiden Properties schließen sich gegenseitig aus, und die erweiterten hängen an einer Extension-URI, die Excel case-sensitiv matcht
Genau dieses Detail ist der Grund, dass dieser Post existiert. Eine Pivot-Tabelle mit einer Rang-Spalte sieht aus wie eine Kleinigkeit zum Generieren, und beim ersten Öffnen ist sie das meist auch. Der Ärger beginnt, wenn ein Nutzer in Excel auf Refresh klickt und die Rang-Spalte ohne jede Warnung wieder zu einer schlichten Summe wird. Show Values As richtig zu setzen heißt, zwei XML-Vokabulare richtig zu setzen, plus eine Handvoll numerischer Regeln, die Excel in der UI nie dokumentiert: wie Ränge bei Gleichstand zählen, was eine kumulierte Prozentzahl eines Durchschnitts bedeutet, und welche leere Zelle blank wird statt einer Fehlermeldung
Warum gibt es zwei Properties für ein Excel-Menü?
Excel zeigt ein „Show Values As“-Menü, aber die Datei speichert es an zwei verschiedenen Orten, deshalb stellt HotXLS zwei Properties bereit. Die ursprünglichen Modi aus Excel 2007 liegen im showDataAs-Attribut des dataField-Elements innerhalb von pivotTableDefinition, mit der ECMA-376-Enumeration ST_ShowDataAs. Die Modi, die Excel 2010 hinzufügte – Rank sowie die Parent- und kumulierten Prozentzahlen –, passten nicht in diese Enumeration und wanderten deshalb in ein x14:dataField-Element mit einem pivotShowAs-Attribut, verpackt in einen extLst-Extension-Block am selben Data Field
TXLSPivotShowDataAs spiegelt die erste Gruppe: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal und xlpsdaIndex. TXLSPivotExtendedShowDataAs deckt die zweite ab: xlpesdaPercentOfParent, xlpesdaPercentOfParentRow, xlpesdaPercentOfParentColumn, xlpesdaPercentOfRunningTotal, xlpesdaRankAscending und xlpesdaRankDescending, mit xlpesdaNone als neutralem Wert. Ein Data Field benutzt die eine oder die andere Gruppe, nie beide. Lassen Sie ShowDataAs auf einem Nicht-Normal-Wert stehen und setzen zusätzlich einen erweiterten Modus, gibt MaterializeNativePivotTable -1 zurück und lässt die bestehende Ausgabe exakt wie sie war, Zellen und Pivot-Grenzen eingeschlossen
BaseField und BaseItem tragen das Vergleichsziel. BaseField ist ein nullbasierter Index in die Feldliste der Pivot-Tabelle, nicht in die Header-Namen der Quellspalten, und er steuert Vergleiche, Running Totals, Percent eines gewählten Parents und Ränge. BaseItem ist eine nullbasierte Item-Position innerhalb dieses Felds oder eine der Konstanten xlPivotBaseItemPrevious und xlPivotBaseItemNext für „verglichen mit dem Nachbarn“. Parent-Row- und Parent-Column-Prozentzahlen ignorieren BaseField völlig; sie nehmen das innerste Feld auf der passenden Achse
Einen gerankten Pivot in Delphi bauen
Der kürzeste funktionierende Weg: Pivot bauen, den Anzeigemodus am Data Field setzen und vor dem Speichern materialisieren. Das Beispiel unten rankt regionale Beträge pro Kategorie absteigend, der größte Betrag je Zeile bekommt also Rang 1
uses
SysUtils, lxHandleX, lxPivot;
var
Book: TXLSXWorkbook;
Data, Report: TXLSXWorksheet;
Pivot: TXLSPivotTable;
Measure: TXLSPivotDataField;
begin
Book := TXLSXWorkbook.Create;
try
Data := Book.Sheets.Add('Data');
Data.Cells[1, 1].Value := 'Category';
Data.Cells[1, 2].Value := 'Region';
Data.Cells[1, 3].Value := 'Amount';
// ... Zeilen 2..7 füllen ...
Report := Book.Sheets.Add('Report');
Pivot := Report.AddPivotTable('Data!A1:C7', 4, 3, 'RegionRank');
Pivot.AddRowField('Category'); // Pivot-Feld 0
Pivot.AddColumnField('Region'); // Pivot-Feld 1
Measure := Pivot.AddDataFieldByName('Amount', xlpaSum);
Measure.DisplayName := 'Rank by Region';
Measure.ShowDataAs := xlpsdaNormal; // Basic-Mode bleibt aus
Measure.ExtendedShowDataAs := xlpesdaRankDescending;
Measure.BaseField := 1; // Rang über Region
if Book.MaterializeNativePivotTable(Pivot) <> 1 then
raise Exception.Create('Pivot display mode was rejected');
Book.SaveAs('region-rank.xlsx');
finally
Book.Free;
end;
end;Zwei Gewohnheiten zahlen sich hier aus. Erstens: ShowDataAs := xlpsdaNormal explizit setzen, wenn Sie einen erweiterten Modus wählen, auch an einem frischen Feld, denn derselbe Code landet oft wiederverwendet an einem importierten Pivot, dessen Data Field bereits einen Basic-Mode trägt. Zweitens: den Rückgabewert prüfen. Eine abgelehnte Materialisierung ist atomar, was gut ist, bedeutet aber auch, dass eine verpasste Prüfung eine Datei mit dem alten Grid ausliefert
BaseField überlebt das Speichern auch, wenn Sie MaterializeNativePivotTable nie aufrufen. HotXLS schreibt das baseField-Attribut für Percent of Parent, Running Total Percent und beide Rank-Modi, sobald der erweiterte Modus gesetzt ist, sodass eine direkt nach der Konfiguration gespeicherte Mappe Excel beim Refresh trotzdem sagt, über welches Feld zu ranken ist
Was läuft mit der Extension-URI schief?
Der Extension-Block, der pivotShowAs trägt, ist über die URI {E15A36E0-9728-4e99-A89B-3F7291B0FE68} identifiziert, und Excel vergleicht diesen String beim Refresh case-sensitiv. Beachten Sie das kleine e in 4e99. GUIDs sind normalerweise case-insensitiv, und viele XML-Writer schreiben sie groß – genau das ist die Fehlerform: Eine Datei mit 4E99 öffnet in Excel mit jeder gecachten Zelle korrekt, weil Excel nur die gespeicherten Werte malt. Nach RefreshTable erkennt Excel die Extension jedoch nicht mehr, wirft den erweiterten Modus weg und berechnet die Spalte als gewöhnliche Summe neu. Kein Reparatur-Dialog, kein Fehler, nur eine Rang-Spalte, die still zu Summen wurde
HotXLS schreibt die wörtliche URI so, wie Excel sie erwartet, das beißt Sie also nur, wenn etwas nachgelagert das Paket umschreibt, etwa ein Post-Processing-Schritt, der GUID-Casing normalisiert oder extLst-Elemente neu baut. Der Test, der das erwischt, ist billig: Die erzeugte Datei in Excel öffnen, den Pivot refreshen und bestätigen, dass die erweiterte Spalte weiterhin Ränge oder Prozentzahlen zeigt statt roher Aggregate. Ein Test, der nur die gecachten Zellen liest, besteht auf einer kaputten Datei jedes Mal
Wie rankt Excel Gleichstände und ordnet Running Totals?
Der Pivot-Rang von Excel ist ein dichter Rang, und HotXLS bildet ihn ab. Bei absteigenden Beträgen 30, 30 und 20 ist das Ergebnis 1, 1 und 2, nicht der Competition-Stil 1, 1 und 3, den RANK.EQ auf einem Arbeitsblatt liefert. Ein Gleichstand zählt als ein bestimmter vorangehender Wert. Braucht ein Report Competition-Ranking, berechnen Sie es in den Quelldaten oder in einer Arbeitsblattformel neben dem Pivot, denn kein Pivot-Anzeigemodus produziert es
Running Totals, Running-Total-Prozentzahlen und Previous- oder Next-Vergleiche folgen der Reihenfolge, in der Member tatsächlich erscheinen, nicht der Reihenfolge der Item-Liste des Felds. Diese Unterscheidung zählt, sobald ein Aufrufer die Sortierung ändert, nachdem der Cache gebaut wurde
// Zeilen absteigend sortiert: die Running Total akkumuliert in Anzeige-Reihenfolge
Pivot.Fields[0].SortType := xlpfstDescending;
Measure.ExtendedShowDataAs := xlpesdaNone;
Measure.ShowDataAs := xlpsdaPercentDiff; // % Differenz von...
Measure.BaseField := 0; // ...dem Category-Feld
Measure.BaseItem := xlPivotBaseItemPrevious; // ...dem vorherigen sichtbaren Member
if Book.MaterializeNativePivotTable(Pivot) <> 1 then
raise Exception.Create('Pivot display mode was rejected');Nachdem SortType auf xlpfstDescending wechselt, heißt „previous“ der Member, der oberhalb angezeigt wird, und die Running Total startet am Kopf der sortierten Liste. Eine Implementierung, die stattdessen die gespeicherte Item-Liste abläuft, produziert Zahlen, die plausibel aussehen und Excel in dem Moment widersprechen, in dem irgendjemand refreshed. Randmember brauchen ebenfalls Sorgfalt: Am ersten Member gibt es keinen vorherigen Nachbarn, und nur der Percent-of-Mode vergleicht den Randmember mit sich selbst; die Difference-Modi überlassen diese Zelle dem eigenen Randverhalten von Excel, statt eine Null zu erfinden
Warum kann eine kumulierte Prozentzahl 100 Prozent überschreiten?
Eine Running-Total-Prozentzahl über Durchschnitten darf legitimerweise über 100 Prozent hinausgehen, weil Excel die angezeigten Member-Aggregate akkumuliert und durch ein Aggregate teilt, das nicht ihre Summe ist. Bei Summen sind beide dasselbe, das letzte Member landet also exakt bei 100 Prozent. Bei Durchschnitten ist drei Kategorie-Durchschnitte zu addieren und durch den Gesamtdurchschnitt zu teilen kein Bruchteil von irgendetwas, und die letzte Zeile kann deutlich über 100 Prozent landen. HotXLS matcht hier absichtlich Excel, statt den Wert zu clampen, denn eine geclampte Zahl würde der refreshenen Datei widersprechen
Der Divisor hängt auch davon ab, wo das Base Field sitzt. Ist das Base Field das innerste Feld auf seiner Achse, ist der Divisor das ursprüngliche Aggregate für diese Achsenposition. Ist das Base Field ein äußeres Feld in einem verschachtelten Layout, ist der Divisor die Summe über die passenden Member der verbleibenden Achse. Beide Positionen wurden unabhängig gegen Excel-Ausgabe geprüft, denn eine Regel, die auf einer Verschachtelungsebene funktioniert und auf der anderen still failt, ist der klassische Pivot-Bug
Einfache Prozentzahlen verhalten sich anders als die kumulierte. Percent of Row, Column und Grand Total teilen durch das ursprüngliche Aggregate im relevanten Scope, auch bei nicht additiven Aggregationen wie Durchschnitten; ein Percent of Total über Durchschnitten ist also der Zellendurchschnitt geteilt durch den Gesamtdurchschnitt, nicht ein Anteil an einer summierten Menge
Blanks, #NULL! und #DIV/0! in Vergleichsmodi
Percent-of-Base-Vergleiche unterscheiden drei leere Situationen, und jede erzeugt eine andere Zelle. Hat die Zähler-Schnittmenge keine Quellzeilen, zeigt die Zelle #NULL!. Hat das Base Item an dieser Schnittmenge keine Daten, ist die Zelle blank. Existiert das Base Item und ist sein Aggregate null, zeigt die Zelle #DIV/0!. Code, der das materialisierte Grid liest, sollte mit VarIsEmpty und VarIsError testen, statt anzunehmen, jede Wertezelle halte eine Zahl
Die einfachen Prozentzahlen sind gnädiger: Ein fehlender Zähler zählt dort als null. Parent-Row- und Parent-Column-Prozentzahlen fügen einen Fall hinzu, denn ein Pivot ohne Feld auf der entsprechenden Achse hat keinen Parent, durch den sich teilen ließe; diese Ergebnisse sind blank. Grand Totals folgen ebenfalls der eigenen Semantik jedes Modus, inklusive blanker Totals entlang der Achse, die ein Vergleich oder eine Running Total durchläuft; eine Total-Zeile voller Blanks unter einer Running Total ist korrekt, kein Materialisierungsversagen
Modi an einem importierten Pivot wechseln
Den Anzeigemodus eines Pivots zu ändern, der aus einer bestehenden Mappe stammt, funktioniert genauso, mit einem zusätzlichen Flag. Übergeben Sie True als AAdoptExisting, damit MaterializeNativePivotTable die Zellen übernehmen darf, die Excel für diesen Pivot bereits geschrieben hat
Pivot := Book.Sheets.SheetByName('Sales').PivotTables[0];
with Pivot.DataFields[0] do
begin
ShowDataAs := xlpsdaNormal; // Basic-Mode löschen
ExtendedShowDataAs := xlpesdaPercentOfParentRow; // kein BaseField nötig
end;
if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
raise Exception.Create('Could not re-materialize the pivot');Beim Moduswechsel entfernt HotXLS veraltete showDataAs-, pivotShowAs- und baseItem-Attribute aus der gestagten Definition, sodass die gespeicherte Datei nie einen veralteten Basic-Mode neben einem neuen erweiterten trägt. Derselbe Staging-Schritt hält die Compact- und Outline-Flags an Data- und Inactive-Fields im Takt mit dem Report-Layout, was zählt, weil Excel die refreshenen Daten-Captions aus diesen Flags ableitet und nicht nur aus den Row- und Column-Fields. Ungültige Enum-Werte werden ebenfalls abgelehnt: Ein Byte von 255 in eine der beiden Enums gecastet kann sich in Delphi sign-extenden zu -1, HotXLS prüft also untere und obere Grenze und gibt -1 zurück, ohne das Grid anzufassen
Fügen Sie demselben Report auch Calculated Fields oder Calculated Items hinzu, lesen Sie zuerst Pivot-Tabellen mit Calculated Fields in HotXLS bauen und refreshen, und für die Field-Level-XML-Regeln, die Excel vom Reparieren der Datei abhalten, siehe XLSX-Pivot-Field-Schema-Gültigkeit in Delphi
FAQ
Kann ein HotXLS-Data-Field ShowDataAs und ExtendedShowDataAs zusammen nutzen?
Nein. TXLSPivotDataField.ShowDataAs und ExtendedShowDataAs schließen sich gegenseitig aus; sind beide gesetzt, gibt MaterializeNativePivotTable -1 zurück und lässt die vorherige Ausgabe unverändert
Warum wurde mein Pivot-Rang nach dem Refresh in Excel zu einer Summe?
Die Extension-URI wurde vermutlich mit anderem Casing umgeschrieben. Excel matcht {E15A36E0-9728-4e99-A89B-3F7291B0FE68} case-sensitiv und fällt nach RefreshTable auf die schlichte Aggregation zurück, wenn sie nicht passt
Überspringt der HotXLS-Pivot-Rang Zahlen nach einem Gleichstand?
Nein. HotXLS folgt dem dichten Pivot-Rang von Excel, 30, 30 und 20 ranken also als 1, 1 und 2
Show-Values-As-Unterstützung, native Pivot-Materialisierung und der Rest des XLSX- und XLS-Feature-Sets kommen in der HotXLS Delphi spreadsheet component für Delphi und C++Builder