Pivot nach Wert sortieren in Delphi: autoSortScope und -2

HotXLS sortiert PivotTable-Elemente in Delphi nach Wert über zwei Eigenschaften an TXLSPivotField: SortType wählt auf- oder absteigend, und SortDataField nennt das nullbasierte Datenfeld, dessen Summen die Reihenfolge bestimmen. Öffnet HotXLS eine von Excel erzeugte XLSX, gewinnt es dieses Datenfeld aus dem autoSortScope-Element zurück, in dem Excel das Values-Feld als field="4294967294" ablegt — der vorzeichenlosen Schreibweise von -2

Nach Wert zu sortieren ist die Anfrage, die jeder Vertriebsreport irgendwann bekommt: "zeigen Sie mir die Regionen von groß nach klein". In der Excel-UI sind es zwei Klicks. Im Dateiformat ist es ein Attribut, eine verschachtelte Pivot-Area, ein reservierter Feldindex und ein gewählter Datenfeld-Ordinalwert — und ein Reader, der auch nur eines dieser Teile übersieht, öffnet die Datei ohne Murren. Er sortiert nur nach dem Falschen

Wo speichert Excel ein Nach-Wert-Sortier-Measure?

Excel speichert das Sortier-Measure nur innerhalb von autoSortScope, nicht am Feld selbst. Das sortType-Attribut an <pivotField> (ein ST_FieldSortType-Wert aus ECMA-376 Part 1: manual, ascending oder descending) sagt, in welche Richtung sortiert wird. Nach welchem Measure sortiert wird, steht in einem Kind <autoSortScope>, das eine <pivotArea> umschließt, deren Datenachsen-Referenz ein Datenfeld auswählt

<pivotField axis="axisRow" showAll="0" sortType="descending">
  <items count="5"> ... </items>
  <autoSortScope>
    <pivotArea dataOnly="0" outlineLevel="all" fieldPosition="0">
      <references count="1">
        <reference field="4294967294" count="1" selected="0">
          <x v="0"/>
        </reference>
      </references>
    </pivotArea>
  </autoSortScope>
</pivotField>

Das reference-Element zeigt normalerweise per Index auf ein Pivot-Feld. Der Feldindex -2 ist im PivotTable-Part von ECMA-376 für das Values-Pseudofeld reserviert, das Excel als "Values" zeigt, wenn zwei oder mehr Measures auf einer Achse sitzen. Das Attribut ist als vorzeichenloser 32-Bit-Integer typisiert, also geht -2 als 4294967294 über die Leitung. Das <x v="0"/> darunter wählt Datenfeld 0, den ersten Eintrag in <dataFields>. Es gibt auch ein rankBy-Attribut an <pivotField>, aber in den verglichenen Dateien ließ Excel es weg und stützte sich allein auf den Scope

Warum kam das Sortier-Measure als -1 zurück?

Der HotXLS-Reader verlor das Sortier-Measure ursprünglich, weil 4294967294 nicht in einen vorzeichenbehafteten 32-Bit-Integer passt. Der Attribut-Helfer ist ein dünner Wrapper über StrToIntDef, und ein außerhalb des Bereichs liegender Wert liefert still den Default, also griff der Datenachsen-Test nie, SortDataField blieb auf -1, und das Feld behielt nur sortType="descending" ohne Measure

Das Versagen war still auf die schlimmste Art. Ein absteigendes Feld ohne Measure sortiert trotzdem — nur nach Elementlabel in umgekehrter Reihenfolge —, also kam ein Report, den Excel nach Summe geordnet hatte, aus HotXLS in umgekehrter Elementreihenfolge zurück. Nichts warf eine Exception, und die Zahlen in jeder Zelle waren korrekt. Nur die Zeilenreihenfolge war falsch, und auch nur bei Dateien, die Excel erzeugt hatte

Der Fix in lxPivotXml.pas vergleicht das rohe Attribut-Token als String, bevor irgendeine Ganzzahlwandlung läuft. Hat rankBy bereits ein Measure gesetzt, gewinnt es; sonst löst eine Reference, deren field-Token exakt '4294967294' lautet, SortDataField aus ihrem gewählten x-Wert auf. Die Auswertungsseite brauchte keine Änderung: Der Komparator finalisierte bereits Member-Summen auf den Zeilen- und Spaltenachsen, behandelte absteigende Ordnung und brach Gleichstände. Die Daten erreichten ihn schlicht nie

Die allgemeine Lehre gilt für jeden in Delphi geschriebenen OOXML-Reader. ECMA-376 typisiert etliche Indexattribute als xsd:unsignedInt und verwendet dann die Obergrenze des Bereichs als Sentinel, sodass ein Reader mit Vorzeichen und Default-Fallback aus einem reservierten Wert "Attribut fehlt" macht. Vergleichen Sie das Token oder parsen Sie in Int64 oder Cardinal, wo immer ein Feldindex Values bedeuten kann

Pivot-Elemente aus Delphi-Code nach einem Measure sortieren

Um ein HotXLS-Pivot-Feld nach Wert zu sortieren, setzen Sie SortType auf xlpfstAscending oder xlpfstDescending und SortDataField auf den nullbasierten Index des Measures in der DataFields-Liste der Tabelle. Mit SortType = xlpfstManual, dem Default, wird SortDataField ignoriert, und die Elemente behalten ihre Cache- oder programmatische Reihenfolge

uses
  SysUtils, lxHandleX, lxPivot;

var
  Book: TXLSXWorkbook;
  Data, Report: TXLSXWorksheet;
  Pivot: TXLSPivotTable;
  Region: TXLSPivotField;
begin
  Book := TXLSXWorkbook.Create;
  try
    Data := Book.Sheets.Add('Data');
    Data.Cells[1, 1].Value := 'Region';
    Data.Cells[1, 2].Value := 'Product';
    Data.Cells[1, 3].Value := 'Units';
    // ... Zeilen 2..41 füllen ...
    Report := Book.Sheets.Add('Report');

    Pivot := Report.AddPivotTable('Data!A1:C41', 3, 1, 'UnitsByRegion');
    Region := Pivot.AddRowField('Region');
    Pivot.AddDataFieldByName('Units', xlpaSum);   // Datenfeld 0

    Region.SortType := xlpfstDescending;  // größte Region zuerst
    Region.SortDataField := 0;            // sortieren nach Summe von Units
    Region.AutoSortScope := '0';          // auch Excels eigenes autoSortScope emittieren

    if Book.MaterializeNativePivotTable(Pivot) <> 1 then
      raise Exception.Create('Pivot layout was rejected');
    Book.SaveAs('units-by-region.xlsx');
  finally
    Book.Free;
  end;
end;

Zwei Details verdienen eine Notiz. Der HotXLS-Writer emittiert SortDataField als rankBy, und wenn AutoSortScope nicht leer ist, schreibt er zusätzlich einen autoSortScope-Block mit der 4294967294-Datenachsen-Reference und legt den Property-String in den x v-Slot. Beim Laden erhält AutoSortScope dasselbe x v-Token, bei Wertsortierungen hält die Property den Datenfeld-Ordinalwert also als Text. Sie auf '0' zu setzen erzeugt daher eine Datei, die das Measure an beiden Stellen trägt — die engste Entsprechung zu dem, was Excel selbst schreibt. MaterializeNativePivotTable staffelt dann das sortierte Grid samt Layout-Metadaten gemeinsam ein und gibt bei Erfolg 1 zurück, sonst -1 mit einer Diagnose

Elementzeilen sind nicht das Einzige, das sortiert wird. Spaltenfelder laufen durch denselben Komparator, mit Spaltensummen statt Zeilensummen, also ordnet ein Spaltenfeld mit SortType = xlpfstDescending und gültigem SortDataField seine Spalten auf exakt dieselbe Weise nach Wert

Die Sortierung prüfen, die eine Excel-Arbeitsmappe bereits trägt

Bei von Excel erzeugten Dateien füllt HotXLS SortType, SortDataField und AutoSortScope beim Laden der Pivot-Definition, Sie können die Sortierung eines Reports also prüfen oder ändern, ohne die Tabelle neu zu bauen. Der Schnipsel unten listet jedes Feld, das nach einem Measure sortiert, und nennt das Measure über seinen DisplayName

var
  Book: TXLSXWorkbook;
  Pivot: TXLSPivotTable;
  Field: TXLSPivotField;
  I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('sales-report.xlsx');
    Pivot := Book.Sheets.SheetByName('Report').PivotTables[0];
    for I := 0 to Pivot.FieldCount - 1 do
    begin
      Field := Pivot.Fields[I];
      if (Field.SortType <> xlpfstManual) and (Field.SortDataField >= 0) then
        Writeln(Field.Name, ' sorted by ',
          Pivot.DataFields[Field.SortDataField].DisplayName);
    end;

    // die Region-Sortierung auf aufsteigend kippen und das bestehende Grid neu aufbauen
    Field := Pivot.FindFieldByName('Region');
    if Field <> nil then
      Field.SortType := xlpfstAscending;
    if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
      raise Exception.Create('Could not re-materialize the pivot');
    Book.SaveAs('sales-report-asc.xlsx');
  finally
    Book.Free;
  end;
end;

Das zweite Argument von MaterializeNativePivotTable weist HotXLS an, den bestehenden Ausgabebereich des Pivots zu übernehmen, statt ihn als Kollision zu behandeln. Dasselbe Muster taucht im Leitfaden zu Show Values As mit Rang und laufenden Summen in HotXLS auf, wo die Sortierreihenfolge zählt, weil laufende Summen in Anzeigereihenfolge akkumulieren

Wie bricht HotXLS Gleichstände, wenn zwei Elemente denselben Wert haben?

HotXLS bricht Gleichstände deterministisch über das Elementlabel, was eine bekannte Abweichung von Excel ist. Haben zwei Member identische Summen, fällt der Komparator auf den Vergleich ihrer Captions zurück, und das absteigende Flag kehrt auch diesen Vergleich um. Elemente, deren Metrik keine gültige Zahl ist, sortieren in aufsteigender Ordnung hinter den gültigen, und das absteigende Flag kehrt den ganzen Vergleich um, diese Gruppierung eingeschlossen

Excel verspricht keinerlei Gleichstandsordnung. In einer Test-Fixture, in der vier Member — A, B, C und D — alle dieselbe Summe hatten, lieferte eine aufsteigende Sortierung in Excel B, C, A, D. Nichts in den gespeicherten Metadaten sagt diese Permutation voraus; sie ist ein Nebenprodukt eines instabilen Sortierers und kann zwischen Dateien differieren. HotXLS liefert für dieselbe Eingabe A, B, C, D. Unsere Verifikation für diesen Fall prüft die Member-Menge und die Werte pro Member statt der Sequenz, und die absteigende Fixture mit 15/15-Gleichstand passte Zelle für Zelle zu Excel. Hängt Ihr nachgelagerter Code von der exakten Position gleichrangiger Zeilen ab, sortieren Sie über ein zweites Measure oder sorgen Sie für unterschiedliche Werte

Eingeklappte Member und das innerste Feld

Eingeklappte Member materialisieren sich in HotXLS als eine Aggregatzeile pro eingeklapptem Element. Wenn Excel die Details eines Elements verbirgt, schreibt es sd="0" an dieses <item>; HotXLS legt es als TXLSPivotItem.IsDetailHidden offen. Das eingeklappte Element rendert mit seinem schlichten Label, ersetzt seinen ganzen Teilbaum, und das Einklappen gilt für dieses Element unter jedem Parent, während umschließende Blöcke ihre eigenen Zwischensummen behalten

var
  Category: TXLSPivotField;
begin
  // Pivot hat Category (außen) und Product (innen) auf den Zeilen
  Category := Pivot.FindFieldByName('Category');
  if (Category <> nil) and (Category.ItemCount > 0) then
    Category.Items[0].IsDetailHidden := True;   // die erste Kategorie einklappen
  if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
    raise Exception.Create('Collapse was rejected');
end;

Einklappen interagiert vorhersagbar mit Sortieren: Eine eingeklappte Zeile bleibt Member ihres Feldes, also nimmt sie ihre Position aus ihrer Summe wie jedes andere Element. Das innerste Feld ist anders. Es hat keine innere Ebene, hat also nichts zum Einklappen, und seit einer früheren Korrektur rendert HotXLS für es auch nie eine Zwischensummenzeile, selbst wenn sein defaultSubtotal-Flag gesetzt ist. Excel verhält sich auf Ein-Feld- und Mehrebenen-Achsen genauso, und vor dieser Änderung konnte HotXLS unter dem innersten Feld eine phantomhafte Total-Zeile emittieren. Für die hier beteiligten Feldattribute behandelt der HotXLS-Durchgang durch die CT_PivotField-Schema-Validität, was das Schema an <pivotField> und seinen Items verlangt

Fallstricke beim Abgleich gegen Excel-Automation

Wenn Sie eine HotXLS-Sortierung per COM-Automation gegen Excel abgleichen, rufen Sie AutoSort mit der lokalisierten Caption des Measures auf, buchstabiert exakt so, wie Excel sie zeigt. Auf einem chinesischsprachigen Excel lautet die Default-Caption eines summierten Units-Felds '求和项:Units'; 'Sum of Units' auf dieser Maschine zu übergeben wirft keinen Fehler und ändert nichts. Dasselbe Skript, das auf einem englischen Excel funktioniert, wird andernorts still zum No-Op — lesen Sie die Caption also aus dem Datenfeld, statt sie hart zu kodieren

• PivotField.SortType ließ sich im von uns getesteten Excel-Build nicht per COM zurücklesen, verifizieren Sie die Sortierung also über das gespeicherte XML oder die gerenderten Zellen
• In jenem Build unterdrückte ein manuelles Gruppen-Parent-Feld auf derselben Achse außerhalb des sortierten Felds das Member-Sortieren nach Wert; sortiertes Rendern unter Manual-Group-Parents ist nicht verifiziert
• Schreibvorgänge an PivotField.Outline und PivotField.LayoutForm wurden in jenem Build ebenfalls abgewiesen, setzen Sie ein Outline-Layout für eine Fixture also über PivotTable.RowAxisLayout

Keine dieser Eigenheiten ändert das Dateiformat, aber sie entscheiden, ob ein Regressionstest irgendetwas beweist. Ein Test, der AutoSort mit der falschen Caption aufruft, besteht gegen einen Pivot, der nie sortiert wurde

FAQ

Was bedeutet Feld 4294967294 in einer PivotTable-XML-Datei?

Feld 4294967294 ist die vorzeichenlose 32-Bit-Kodierung des Feldindex -2, den ECMA-376 für das Values-Pseudofeld reserviert. Innerhalb von autoSortScope markiert es eine Datenachsen-Reference, und sein x-Kind wählt, nach welchem Datenfeld die Elemente sortiert werden

Sortiert HotXLS Pivot-Elemente nach Wert, ohne dass Excel installiert ist?

Ja. HotXLS wertet die Sortierung selbst aus, wenn es das Pivot-Grid materialisiert, unter Verwendung der Member-Summen des gewählten Datenfelds, und schreibt sortType, rankBy und optional autoSortScope, sodass Excel nach einem Refresh dieselbe Ordnung behält. Nur die Gleichstandsordnung unter gleichen Werten kann von Excel abweichen

Warum sind meine Pivot-Elemente alphabetisch statt nach Wert sortiert?

In der Regel, weil das Feld eine Sortierrichtung, aber kein Measure hat. Prüfen Sie, dass SortType nicht xlpfstManual ist und dass SortDataField zwischen 0 und DataFieldCount - 1 liegt; bei -1 ordnet HotXLS die Elemente in der gewünschten Richtung nach Label

Wertsortierung, eingeklappte Member und der Rest der nativen Pivot-Pipeline kommen in HotXLS, der Delphi-Spreadsheet-Komponente für XLS und XLSX, die Excel-Arbeitsmappen ohne installiertes Excel liest, editiert und schreibt. Bauen Sie den Pivot erst von Grund auf, behandelt das HotXLS-Tutorial zum Bauen und Aktualisieren von PivotTables Caches, Felder und Refresh, bevor Sie an die Ordnung gehen