Pivotitems op waarde sorteren in Delphi: autoSortScope en -2

HotXLS sorteert PivotTable-items op waarde in Delphi via twee eigenschappen op TXLSPivotField: SortType kiest oplopend of aflopend, en SortDataField benoemt het 0-based datafield wiens totalen de volgorde sturen. Opent HotXLS een door Excel vervaardigde XLSX, dan herstelt hij dat datafield uit het autoSortScope-element, waar Excel het Values-veld opslaat als field="4294967294", de unsigned-spelling van -2

Op waarde sorteren is het verzoek dat elk verkooprapport uiteindelijk krijgt: "laat me de regio's van groot naar klein zien". In de Excel-UI zijn het twee klikken. In het bestandsformaat is het een attribuut, een geneste pivot area, een gereserveerd veldindex en een geselecteerde datafield-ordinaal, en een reader die één van die stukken mist opent het bestand toch zonder klacht. Hij sorteert alleen naar het verkeerde

Waar bewaart Excel een sorteer-op-waarde-measure?

Excel bewaart de sorteer-measure alleen binnen autoSortScope, niet op het veld zelf. Het attribuut sortType op <pivotField> (een ECMA-376 Part 1 ST_FieldSortType-waarde: manual, ascending of descending) zegt in welke richting gesorteerd wordt. Welke measure er gesorteerd wordt zit in een kind <autoSortScope>, dat een <pivotArea> omwikkelt waarvan de data-as-referentie één datafield selecteert

<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>

Het reference-element wijst normaal op een pivotveld bij index. Veldindex -2 is in het PivotTable-deel van ECMA-376 gereserveerd voor het pseudo-veld Values, degene die Excel als "Values" toont wanneer twee of meer measures op een as zitten. Het attribuut is getypeerd als een unsigned 32-bit integer, dus -2 gaat over de lijn als 4294967294. De <x v="0"/> eronder selecteert datafield 0, de eerste entry in <dataFields>. Er is ook een attribuut rankBy op <pivotField>, maar in de bestanden die we vergeleken liet Excel hem weg en leunde hij alleen op de scope

Waarom kwam de sorteer-measure terug als -1?

De HotXLS-reader verloor oorspronkelijk de sorteer-measure omdat 4294967294 niet in een signed 32-bit integer past. De attribuuthelper is een dunne wikkel over StrToIntDef, en een waarde buiten bereik geeft zwijgend de default terug, dus de data-as-toets matchte nooit, SortDataField bleef op -1, en het veld hield alleen sortType="descending" over zonder measure

De misser was stil op de slechtst denkbare manier. Een aflopend veld zonder measure sorteert nog steeds, alleen op itemlabel omgekeerd, dus een rapport dat Excel op totaal had geordend kwam uit HotXLS terug met de itemvolgorde omgedraaid. Niets gooide een exception en de getallen in elke cel waren correct. Alleen de rijvolgorde was fout, en alleen bij bestanden die Excel had vervaardigd

De fix in lxPivotXml.pas vergelijkt de rauwe attribuuttoken als string vóór enige integerconversie. Heeft rankBy al een measure gezet, dan wint die; anders resolvt een reference waarvan het token field exact '4294967294' is de SortDataField uit zijn geselecteerde x-waarde. De evaluatiekant hoefde niet te veranderen: de comparator finaliseerde al totalen per lid op de rij- en kolomassen, behandelde aflopende volgorde en brak gelijke standen. De data bereikte hem alleen nooit

De algemene les geldt voor elke in Delphi geschreven OOXML-reader. ECMA-376 typeert verscheidene indexattributen als xsd:unsignedInt en gebruikt dan de bovenkant van het bereik als sentinel, dus een signed reader met een default-fallback maakt van een gereserveerde waarde "attribuut ontbreekt". Vergelijk de token, of parse naar Int64 of Cardinal, overal waar een veldindex Values kan betekenen

Pivotitems sorteren op een measure vanuit Delphi-code

Om een HotXLS-pivotveld op waarde te sorteren, zet u SortType op xlpfstAscending of xlpfstDescending en zet u SortDataField op de 0-based index van de measure in de lijst DataFields van de tabel. Met SortType = xlpfstManual, de default, wordt SortDataField genegeerd en houden items hun cache- of programmatische volgorde

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';
    // ... vul rijen 2..41 ...
    Report := Book.Sheets.Add('Report');

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

    Region.SortType := xlpfstDescending;  // grootste regio eerst
    Region.SortDataField := 0;            // sorteren op Som van Units
    Region.AutoSortScope := '0';          // zend ook de eigen autoSortScope van Excel uit

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

Twee details verdienen een notitie. De HotXLS-writer zendt SortDataField uit als rankBy, en wanneer AutoSortScope niet leeg is schrijft hij ook een autoSortScope-blok met de 4294967294-data-as-referentie, waarbij de propertystring in de x v-gleuf terechtkomt. Bij het laden ontvangt AutoSortScope diezelfde x v-token, dus bij waardesorteringen bevat de eigenschap de datafield-ordinaal als tekst. Hem op '0' zetten levert dus een bestand op dat de measure op beide plekken draagt, wat het dichtst bij matcht met wat Excel zelf schrijft. MaterializeNativePivotTable zet daarna de gesorteerde grid en zijn lay-outmetadata samen in één keer klaar en geeft 1 terug bij succes of -1 met een diagnostiek

Itemrijen zijn niet het enige dat wordt gesorteerd. Kolomvelden gaan door dezelfde comparator, met totalen per kolom in plaats van per rij, dus een kolomveld met SortType = xlpfstDescending en een geldige SortDataField ordent zijn kolommen op waarde op precies dezelfde manier

De sortering inspecteren die een Excel-werkboek al draagt

Voor door Excel vervaardigde bestanden vult HotXLS SortType, SortDataField en AutoSortScope tijdens het laden van de pivotdefinitie, dus u kunt de sortering van een rapport controleren of wijzigen zonder de tabel te herbouwen. Het fragment hieronder zet elk veld op een rij dat op een measure sorteert en benoemt de measure bij zijn 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;

    // draai de Region-sortering naar oplopend en herbouw de bestaande grid
    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;

Het tweede argument van MaterializeNativePivotTable zegt HotXLS het bestaande outputbereik van de pivot over te nemen in plaats van het als een botsing te behandelen. Hetzelfde patroon staat in de gids voor Show Values As-rank en lopende totalen in HotXLS, waar de sorteervolgorde telt omdat lopende totalen in weergavevolgorde accumuleren

Hoe breekt HotXLS gelijke standen wanneer twee items dezelfde waarde hebben?

HotXLS breekt gelijke standen op itemlabel, deterministisch, wat een bekend verschil met Excel is. Hebben twee leden identieke totalen, dan valt de comparator terug op het vergelijken van hun captions, en de aflopend-vlag draait die vergelijking ook om. Items waarvan de metric geen geldig getal is sorteren na de geldige in oplopende volgorde, en de aflopend-vlag draait de hele vergelijking om, die groepering inbegrepen

Excel belooft helemaal geen gelijke-standen-volgorde. In een testfixture waar vier leden, A, B, C en D, allemaal dezelfde totaal hadden, leverde een oplopende sortering in Excel B, C, A, D op. Niets in de opgeslagen metadata voorspelt die permutatie; hij is een bijproduct van een instabiele sortering, en hij kan tussen bestanden verschillen. HotXLS geeft A, B, C, D terug voor dezelfde invoer. Onze verificatie voor dat geval controleert de ledenset en de waarden per lid in plaats van de volgorde, en de aflopende fixture met een gelijke stand 15/15 matchte Excel cel voor cel. Hangt uw downstream-code af van de exacte positie van gelijke rijen, sorteer dan op een tweede measure of maak de waarden onderscheiden

Ingeklapte leden en het binnenste veld

Ingeklapte leden materiëren in HotXLS als één geaggregeerde rij per ingeklapt item. Verbergt Excel de details van een item, dan schrijft hij sd="0" op die <item>; HotXLS stelt hem bloot als TXLSPivotItem.IsDetailHidden. Het ingeklapte item rendert met zijn kale label, vervangt zijn hele subtree, en de inklapping geldt voor dat item onder elke ouder, terwijl omhullende blokken hun eigen subtotaal houden

var
  Category: TXLSPivotField;
begin
  // Pivot heeft Category (buitenste) en Product (binnenste) op rijen
  Category := Pivot.FindFieldByName('Category');
  if (Category <> nil) and (Category.ItemCount > 0) then
    Category.Items[0].IsDetailHidden := True;   // klap de eerste categorie in
  if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
    raise Exception.Create('Collapse was rejected');
end;

Inklappen werkt met sorteren op voorspelbare wijze samen: een ingeklapte rij is nog steeds een lid van zijn veld, dus hij neemt zijn positie in vanuit zijn totaal als elk ander item. Het binnenste veld is anders. Het heeft geen binnenste niveau, dus het heeft niets in te klappen en, sinds een eerdere fix, rendert HotXLS er ook nooit een subtotaalrij voor, zelfs niet wanneer zijn vlag defaultSubtotal staat. Excel doet hetzelfde op enkelveld- en meerniveau-assen, en vóór die verandering kon HotXLS een fantoom-Totaal-rij onder het binnenste veld uitzenden. Voor de veldniveau-attributen die hierbij betrokken zijn, behandelt de HotXLS-walkthrough van CT_PivotField-schema-geldigheid wat het schema vereist op <pivotField> en zijn items

Valkuilen bij het toetsen tegen Excel-automatisering

Wanneer u een HotXLS-sortering tegen Excel afzet via COM-automatisering, roep dan AutoSort aan met de gelokaliseerde caption van de measure, gespeld precies zoals Excel hem toont. Op een Excel in het Chinees is de default caption voor een gesommeerd Units-veld '求和项:Units'; op die machine gooit het meegeven van 'Sum of Units' geen fout en verandert het niets. Hetzelfde script dat op een Engelse Excel werkt wordt elders zwijgend een no-op, dus lees de caption uit het datafield in plaats van hem vast te coderen

• PivotField.SortType kon in de Excel-build die we testten niet via COM worden teruggelezen, dus verifieer de sortering uit de opgeslagen XML of de gerenderde cellen
• In die build onderdrukte het zetten van een handmatige-groep-ouderveld op dezelfde as buiten het gesorteerde veld het sorteren van leden op waarde; gesorteerde rendering onder handmatige-groep-ouders is niet geverifieerd
• Schrijfacties naar PivotField.Outline en PivotField.LayoutForm werden in die build ook geweigerd, dus zet een outline-lay-out voor een fixture via PivotTable.RowAxisLayout

Geen van die eigenaardigheden verandert het bestandsformaat, maar ze beslissen of een regressietest iets bewijst. Een test die AutoSort met de verkeerde caption aanroept, slaagt tegen een pivot die nooit gesorteerd is

FAQ

Wat betekent veld 4294967294 in een PivotTable-XML-bestand?

Veld 4294967294 is de unsigned 32-bit-encoding van veldindex -2, die ECMA-376 reserveert voor het pseudo-veld Values. Binnen autoSortScope markeert hij een data-as-referentie, en zijn kind x selecteert op welk datafield de items worden gesorteerd

Sorteert HotXLS pivotitems op waarde zonder Excel geïnstalleerd?

Ja. HotXLS evalueert de sortering zelf wanneer hij de pivotgrid materialiseert, met de totalen per lid van het gekozen datafield, en schrijft sortType, rankBy en optioneel autoSortScope zodat Excel na een refresh dezelfde volgorde houdt. Alleen de gelijke-standen-volgorde kan van Excel verschillen

Waarom zijn mijn pivotitems alfabetisch gesorteerd in plaats van op waarde?

Doorgaans omdat het veld een sorteerrichting heeft maar geen measure. Controleer dat SortType niet xlpfstManual is en dat SortDataField tussen 0 en DataFieldCount - 1 ligt; met -1 ordent HotXLS items op label in de gevraagde richting

Waardesortering, ingeklapte leden en de rest van de native pivot-pipeline worden geleverd in HotXLS, de Delphi-spreadsheetcomponent voor XLS en XLSX, die Excel-werkboeken leest, bewerkt en schrijft zonder Excel geïnstalleerd. Bouwt u de pivot eerst vanaf nul, dan behandelt de HotXLS-tutorial over pivot tables bouwen en verversen caches, velden en refresh voordat u aan het ordenen komt