Sortér pivotelementer efter værdi: autoSortScope og -2

HotXLS sorterer PivotTable-elementer efter værdi i Delphi gennem to egenskaber på TXLSPivotField: SortType vælger stigende eller faldende, og SortDataField navngiver det nul-baserede datafelt, hvis totaler driver rækkefølgen. Når HotXLS åbner en Excel-fabrikeret XLSX, gendanner den datafeltet fra autoSortScope-elementet, hvor Excel gemmer Values-feltet som field="4294967294", den unsignede stavning af -2

Sortering efter værdi er den anmodning, enhver salgsrapport før eller senere får: "vis mig regionerne fra størst til mindst". I Excel-UI'en er det to klik. I filformatet er det en attribut, et indlejret pivotområde, et reserveret feltindeks og et valgt datafeltordinal, og en reader, der misser bare én af de bidder, åbner stadig filen uden klage. Den sorterer bare efter det forkerte

Hvor gemmer Excel et sorter-efter-værdi-mål?

Excel gemmer sorteringsmålet kun inde i autoSortScope, ikke på feltet selv. Attributten sortType på <pivotField> (en ECMA-376 Part 1 ST_FieldSortType-værdi: manual, ascending eller descending) siger, hvilken retning der skal sorteres. Hvilket mål der skal sorteres efter, bor i et barn <autoSortScope>, som pakker en <pivotArea>, hvis dataakse-reference vælger ét datafelt

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

reference-elementet peger normalt på et pivotfelt efter indeks. Feltindeks -2 er reserveret i PivotTable-delen af ECMA-376 til Values-pseudofeltet, det Excel viser som "Values", når to eller flere mål sidder på en akse. Attributten er typet som en unsigned 32-bit integer, så -2 går over ledningen som 4294967294. <x v="0"/> under den vælger datafelt 0, den første indgang i <dataFields>. Der findes også en rankBy-attribut på <pivotField>, men i de filer, vi sammenlignede, udelod Excel den og stoled alene på scopet

Hvorfor kom sorteringsmålet tilbage som -1?

HotXLS-readeren mistede oprindeligt sorteringsmålet, fordi 4294967294 ikke passer i en signed 32-bit integer. Attributhjælperen er en tynd wrapper over StrToIntDef, og en værdi uden for intervallet returnerer stille defaulten, så dataaksetesten aldrig matchede, SortDataField forblev -1, og feltet beholdt kun sortType="descending" uden noget mål knyttet

Fiaskoen var stille på den værste måde. Et faldende felt uden et mål sorterer stadig, bare efter elementnavn baglæns, så en rapport, som Excel havde ordnet efter total, kom tilbage fra HotXLS med elementrækkefølgen vendt. Intet rejste en exception, og tallene i hver celle var korrekte. Kun rækkefølgen var forkert, og kun for filer, Excel havde fabrikeret

Fixet i lxPivotXml.pas sammenligner den rå attributtoken som streng før nogen heltalskonvertering. Har rankBy allerede sat et mål, vinder det; ellers resolver en reference, hvis field-token er præcis '4294967294', SortDataField fra sin valgte x-værdi. Evalueringssiden behøvede ingen ændring: komparatoren færdiggjorde allerede totaler pr. medlem på række- og kolonneakserne, håndterede faldende rækkefølge og brød lige værdier. Dataene nåede den simpelthen aldrig

Den generelle lektie gælder enhver OOXML-reader skrevet i Delphi. ECMA-376 typer flere indeksattributter som xsd:unsignedInt og bruger så toppen af intervallet som sentinel, så en signed reader med en default-fallback forvandler en reserveret værdi til "attribut mangler". Sammenlign tokenet, eller parse ind i Int64 eller Cardinal, hvor end et feltindeks kan betyde Values

Sortering af pivot-elementer efter et mål fra Delphi-kode

For at sortere et HotXLS pivotfelt efter værdi, sæt SortType til xlpfstAscending eller xlpfstDescending og sæt SortDataField til det nul-baserede indeks af målet i tabellens DataFields-liste. Med SortType = xlpfstManual, som er defaulten, ignoreres SortDataField, og elementerne beholder deres cache- eller programmatiske rækkefølge

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';
    // ... udfyld rækker 2..41 ...
    Report := Book.Sheets.Add('Report');

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

    Region.SortType := xlpfstDescending;  // største region først
    Region.SortDataField := 0;            // sortér efter Sum of Units
    Region.AutoSortScope := '0';          // udsend også Excels egen autoSortScope

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

To detaljer fortjener en note. HotXLS-writeren udsender SortDataField som rankBy, og når AutoSortScope ikke er tom, skriver den også en autoSortScope-blok med 4294967294-dataakse-referencen og lægger egenskabsstrengen ind i x v-slotte. Ved indlæsning modtager AutoSortScope samme x v-token, så ved værdieringer holder egenskaben datafeltordinalen som tekst. At sætte den til '0' producerer derfor en fil, der bærer målet begge steder, hvilket er det tætteste på, hvad Excel selv skriver. MaterializeNativePivotTable scenariserer derefter det sorterede gitter og dets layoutmetadata sammen og returnerer 1 ved succes eller -1 med en diagnostik

Elementrækker er ikke det eneste, der bliver sorteret. Kolonnefelter går gennem samme komparator med totaler pr. kolonne i stedet for pr. række, så et kolonnefelt med SortType = xlpfstDescending og en gyldig SortDataField ordner sine kolonner efter værdi på præcis samme måde

Inspektion af den sortering, en Excel-projektmappe allerede bærer

Til Excel-fabrikerede filer fylder HotXLS SortType, SortDataField og AutoSortScope, mens pivotdefinitionen loades, så du kan auditere eller ændre en rapports sortering uden at bygge tabellen igen. Uddraget nedenfor lister hvert felt, der sorterer efter et mål, og navngiver målet ved dets 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;

    // vend Region-sorteringen til stigende og genopbyg det eksisterende gitter
    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;

Det andet argument til MaterializeNativePivotTable beder HotXLS om at adoptere pivots eksisterende outputområde i stedet for at behandle det som en kollision. Samme mønster optræder i guiden til Show Values As-rank og kumulative totaler i HotXLS, hvor sorteringsrækkefølgen betyder noget, fordi kumulative totaler akkumulerer i visningsrækkefølgen

Hvordan afgør HotXLS lige værdier, når to elementer har samme værdi?

HotXLS afgør lige værdier efter elementnavn, deterministisk, hvilket er en kendt forskel fra Excel. Har to medlemmer identiske totaler, falder komparatoren tilbage til at sammenligne deres captions, og faldende-flaget vender også den sammenligning. Elementer, hvis måling ikke er et gyldigt tal, sorterer efter de gyldige i stigende rækkefølge, og faldende-flaget vender hele sammenligningen, den gruppering inkluderet

Excel lover slet ingen rækkefølge ved lige værdier. I et testfixture, hvor fire medlemmer, A, B, C og D, alle havde samme total, producerede en stigende sortering i Excel B, C, A, D. Intet i de gemte metadata forudsiger den permutation; det er et biprodukt af en ustabil sortering, og det kan adskille sig mellem filer. HotXLS returnerer A, B, C, D for samme input. Vores verifikation for det tilfælde tjekker medlemssættet og værdierne pr. medlem frem for sekvensen, og faldende-fixturen med en 15/15-lighed matchede Excel celle for celle. Afhænger din downstream-kode af den præcise placering af lige rækker, så sortér efter et andet mål eller gør værdierne distinkte

Kollapsede medlemmer og det inderste felt

Kollapsede medlemmer materialiserer sig i HotXLS som én aggregeret række pr. kollapset element. Når Excel skjuler et elements detaljer, skriver den sd="0" på det <item>; HotXLS eksponerer det som TXLSPivotItem.IsDetailHidden. Det kollapsede element renderes med sit plain-navn, erstatter hele sit subtree, og kollapset gælder for det element under enhver forælder, mens omsluttende blokke beholder deres egne subtotaler

var
  Category: TXLSPivotField;
begin
  // Pivot har Category (ydre) og Product (indre) på rækker
  Category := Pivot.FindFieldByName('Category');
  if (Category <> nil) and (Category.ItemCount > 0) then
    Category.Items[0].IsDetailHidden := True;   // kollaps den første kategori
  if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
    raise Exception.Create('Collapse was rejected');
end;

Kollaps interagerer med sortering på en forudsigelig måde: en kollapset række er stadig medlem af sit felt, så den tager sin position fra sin total som ethvert andet element. Det inderste felt er anderledes. Det har intet indre niveau, så det har intet at kollapse, og siden en tidligere fix renderer HotXLS aldrig en subtotalrække for det heller, selv når dets defaultSubtotal-flag er sat. Excel opfører sig ens på enkeltfelt- og multiniveauakser, og før den ændring kunne HotXLS udsende en fantom-Total-række under det inderste felt. For de feltniveauattributter, der er involveret her, dækker HotXLS' gennemgang af CT_PivotField-skemavaliditet, hvad skemaet kræver på <pivotField> og dets elementer

Fælder ved tjek mod Excel-automatisering

Krydstjekker du en HotXLS-sortering mod Excel gennem COM-automatisering, så kald AutoSort med målets lokaliserede caption, stavet præcis som Excel viser den. På en kinesisksproget Excel er default-captionen for et summeret Units-felt '求和项:Units'; at give 'Sum of Units' med på den maskine rejser ingen fejl og ændrer intet. Samme script, der virker på en engelsk Excel, bliver stille en no-op andre steder, så læs captionen fra datafeltet i stedet for at hardkode den

• PivotField.SortType kunne ikke læses tilbage gennem COM i den Excel-build, vi testede, så verificér i stedet sorteringen fra den gemte XML eller de renderede celler
• I den build undertrykte placering af et manual-gruppe-forælderfelt på samme akse uden for det sorterede felt membersortering efter værdi; sorteret rendering under manual-gruppe-forældre er ikke verificeret
• Skrivninger til PivotField.Outline og PivotField.LayoutForm blev også afvist i den build, så sæt et outline-layout til et fixture gennem PivotTable.RowAxisLayout i stedet

Ingen af de kuriositeter ændrer filformatet, men de beslutter, om en regressionstest beviser noget. En test, der kalder AutoSort med den forkerte caption, består mod en pivot, der aldrig blev sorteret

FAQ

Hvad betyder felt 4294967294 i en PivotTable-XML-fil?

Felt 4294967294 er den unsignede 32-bit encoding af feltindeks -2, som ECMA-376 reserverer til Values-pseudofeltet. Inde i autoSortScope markerer det en dataakse-reference, og dets x-barn vælger, hvilket datafelt elementerne sorteres efter

Sorterer HotXLS pivot-elementer efter værdi uden Excel installeret?

Ja. HotXLS evaluerer sorteringen selv, når den materialiserer pivotgitteret, med totalerne pr. medlem af det valgte datafelt, og skriver sortType, rankBy og valgfrit autoSortScope, så Excel beholder samme rækkefølge efter en refresh. Kun rækkefølgen blandt lige værdier kan adskille sig fra Excel

Hvorfor er mine pivot-elementer sorteret alfabetisk i stedet for efter værdi?

Som regel fordi feltet har en sorteringsretning, men intet mål. Tjek at SortType ikke er xlpfstManual, og at SortDataField er mellem 0 og DataFieldCount - 1; med -1 ordner HotXLS elementerne efter navn i den ønskede retning

Værdiering, kollapsede medlemmer og resten af den native pivotpipeline følger med i HotXLS, Delphi-regnearkskomponenten til XLS og XLSX, som læser, redigerer og skriver Excel-projektmapper uden Excel installeret. Bygger du pivoten helt fra bunden først, dækker HotXLS-tutorialen om at bygge og genopfriske pivottabeller caches, felter og genopfriskning, før du når til at ordne dem