Sortera pivotelement efter värde: autoSortScope i Delphi

HotXLS sorterar PivotTable-element efter värde i Delphi via två egenskaper på TXLSPivotField: SortType väljer stigande eller fallande, och SortDataField namnger det nollbaserade datafält vars summor driver ordningen. När HotXLS öppnar en Excel-författad XLSX återvinner den datafältet från autoSortScope-elementet, där Excel lagrar Values-fältet som field="4294967294", den oesignerade stavningen av -2

Att sortera efter värde är förfrågan varje säljrapport förr eller senare får: "visa regionerna från störst till minst". I Excel-gränssnittet är det två klick. I filformatet är det ett attribut, ett nästlat pivotområde, ett reserverat fältindex och en vald datafältsordning, och en läsare som missar vilken som helst av de bitarna öppnar filen ändå utan klagan. Den sorterar bara efter fel sak

Var lagrar Excel ett sortera-efter-värde-mått?

Excel lagrar sorteringsmåttet bara inne i autoSortScope, inte på fältet själv. Attributet sortType på <pivotField> (ett ECMA-376 Part 1 ST_FieldSortType-värde: manual, ascending eller descending) säger åt vilket håll som ska sorteras. Vilket mått som ska sorteras efter bor i ett barn <autoSortScope>, som wrappar en <pivotArea> vars dataaxelreferens väljer ut ett datafält

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

Elementet reference pekar normalt på ett pivotfält efter index. Fältindex -2 är reserverat i PivotTable-delen av ECMA-376 för Values-pseudofältet, det Excel visar som "Values" när två eller flera mått sitter på en axel. Attributet är typat som ett oesignerat 32-bitars heltal, så -2 går över tråden som 4294967294. <x v="0"/> under det väljer datafält 0, första posten i <dataFields>. Det finns också ett rankBy-attribut på <pivotField>, men i filerna vi jämförte lät Excel det ligga och förlitade sig på scopet ensamt

Varför kom sorteringsmåttet tillbaka som -1?

HotXLS-läsaren tappade ursprungligen sorteringsmåttet eftersom 4294967294 inte får plats i ett signerat 32-bitars heltal. Attributhjälparen är en tunn wrapper över StrToIntDef, och ett värde utanför intervallet returnerar tyst standardvärdet, så dataaxeltestet matchade aldrig, SortDataField stannade på -1, och fältet behöll bara sortType="descending" utan något mått fäst

Felet var tyst på värsta sättet. Ett fallande fält utan mått sorterar ändå, bara efter elementetikett i omvänd ordning, så en rapport Excel ordnat efter summa kom tillbaka från HotXLS som elementordningen bakvänd. Inget kastade ett undantag och talen i varje cell var korrekta. Bara radordningen var fel, och bara för filer Excel hade författat

Fixen i lxPivotXml.pas jämför den råa attribut-tokenen som en sträng före varje heltalskonvertering. Har rankBy redan satt ett mått vinner den; annars löser en referens vars field-token är exakt '4294967294' SortDataField från sitt valda x-värde. Utvärderingssidan behövde ingen ändring: komparatorn färdigställde redan per-medlem-summor på rad- och kolumnaxlarna, hanterade fallande ordning och bröt oavgjorda värden. Datan nådde den bara aldrig

Den allmänna lärdomen gäller vilken OOXML-läsare skriven i Delphi som helst. ECMA-376 typar flera indexattribut som xsd:unsignedInt och använder sedan toppen av intervallet som sentinel, så en signerad läsare med standardreserv gör en reserverad varden till "attribut saknas". Jämför tokenen, eller parsa in i Int64 eller Cardinal, varhelst ett fältindex kan betyda Values

Att sortera pivotelement efter ett mått från Delphi-kod

För att sortera ett HotXLS-pivotfält efter värde, sätt SortType till xlpfstAscending eller xlpfstDescending och sätt SortDataField till det nollbaserade indexet av måttet i tabellens DataFields-lista. Med SortType = xlpfstManual, som är standard, ignoreras SortDataField och elementen behåller sin cache- eller programmatiska ordning

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

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

    Region.SortType := xlpfstDescending;  // största regionen först
    Region.SortDataField := 0;            // sortera efter Sum of Units
    Region.AutoSortScope := '0';          // sänd också ut 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;

Två detaljer förtjänar en notering. HotXLS-skrivaren sänder ut SortDataField som rankBy, och när AutoSortScope inte är tomt skriver den också ett autoSortScope-block med 4294967294-dataaxelreferensen, med egenskapssträngen i x v-platsen. Vid inläsning tar AutoSortScope emot samma x v-token, så för värdsorteringar håller egenskapen datafältsordningstalet som text. Att sätta den till '0' ger alltså en fil som bär måttet på båda platserna, vilket är den närmaste matchningen av vad Excel själv skriver. MaterializeNativePivotTable mellanlagrar sedan det sorterade rutnätet och dess layoutmetadata tillsammans och returnerar 1 vid framgång eller -1 med en diagnostik

Elementrader är inte det enda som sorteras. Kolumnfält går genom samma komparator, med per-kolumn-summor i stället för per-rad-summor, så ett kolumnfält med SortType = xlpfstDescending och en giltig SortDataField ordnar sina kolumner efter värde på exakt samma sätt

Att granska sorteringen en Excel-arbetsbok redan bär

För Excel-författade filer fyller HotXLS SortType, SortDataField och AutoSortScope medan pivotdefinitionen läses in, så du kan granska eller ändra en rapports sortering utan att bygga om tabellen. Snutten nedan listar varje fält som sorterar efter ett mått och namnger måttet efter dess 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;

    // vänd Region-sorteringen till stigande och bygg om befintligt rutnät
    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 andra argumentet hos MaterializeNativePivotTable säger åt HotXLS att anta pivotens befintliga utdataområde i stället för att behandla det som en kollision. Samma mönster förekommer i guiden till Show Values As-rang och löpande summor i HotXLS, där sorteringsordning spelar roll eftersom löpande summor ackumuleras i visningsordning

Hur bryter HotXLS oavgjorda värden när två element har samma värde?

HotXLS bryter oavgjorda värden efter elementetikett, deterministiskt, vilket är en känd skillnad från Excel. När två medlemmar har identiska summor faller komparatorn tillbaka på att jämföra deras bildtexter, och den fallande flaggan vänder den jämförelsen också. Element vars mätvärde inte är ett giltigt tal sorteras efter de giltiga i stigande ordning, och den fallande flaggan vänder hela jämförelsen, den grupperingen inkluderad

Excel lovar ingen ordning vid oavgjorda värden alls. I en testfixtur där fyra medlemmar, A, B, C och D, alla hade samma summa gav en stigande sortering i Excel B, C, A, D. Ingenting i den sparade metadatan förutsäger den permutationen; det är en biprodukt av en instabil sortering, och den kan skilja sig mellan filer. HotXLS returnerar A, B, C, D för samma indata. Vår verifiering för det fallet kontrollerar medlemsuppsättningen och per-medlem-värdena i stället för sekvensen, och den fallande fixturen med 15/15 oavgjort matchade Excel cell för cell. Beror din nedströmskod på den exakta positionen av lika rader, sortera på ett andra mått eller gör värdena åtskilda

Kollapsade medlemmar och det innersta fältet

Kollapsade medlemmar materialiseras i HotXLS som en aggregerad rad per kollapsat element. När Excel döljer ett elements detaljer skriver den sd="0" på det <item>; HotXLS exponerar det som TXLSPivotItem.IsDetailHidden. Det kollapsade elementet renderas med sin vanliga etikett, ersätter hela sitt delträd, och kollapsen gäller elementet under varje förälder, medan omslutande block behåller sina egna delsummor

var
  Category: TXLSPivotField;
begin
  // Pivoten har Category (yttre) och Product (inre) på rader
  Category := Pivot.FindFieldByName('Category');
  if (Category <> nil) and (Category.ItemCount > 0) then
    Category.Items[0].IsDetailHidden := True;   // kollapsa första kategorin
  if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
    raise Exception.Create('Collapse was rejected');
end;

Kollapsning samspelar med sortering på ett förutsägbart sätt: en kollapsad rad är fortfarande en medlem av sitt fält, så den tar sin position från sin summa som vilket annat element som helst. Det innersta fältet är annorlunda. Det har ingen inre nivå, så det har inget att kollapsa och, sedan en tidigare fix, renderar HotXLS aldrig en delsummorad för det heller, även när dess defaultSubtotal-flagga är satt. Excel beter sig likadant på enkel- och flernivåaxlar, och före den ändringen kunde HotXLS sända ut en fantom-Total-rad under det innersta fältet. För fältnivåattributen inblandade här täcker HotXLS-genomgången av CT_PivotField-schema giltighet vad schemat kräver på <pivotField> och dess items

Fallgropar vid kontroll mot Excel-automation

När du korskontrollerar en HotXLS-sortering mot Excel via COM-automation, anropa AutoSort med måttets lokaliserade bildtext, stavat exakt som Excel visar det. På en kinesiskspråkig Excel är standardbildtexten för ett summerat Units-fält '求和项:Units'; att skicka 'Sum of Units' på den maskinen kastar inget fel och ändrar ingenting. Samma skript som fungerar på en engelskspråkig Excel blir tyst en no-op annorstädes, så läs bildtexten från datafältet i stället för att hårdkoda den

• PivotField.SortType kunde inte läsas tillbaka via COM i Excel-bygget vi testade, så verifiera sorteringen från sparad XML eller de renderade cellerna i stället
• I det bygget undertryckte ett manuellt-grupp-föräldrafält på samma axel utanför det sorterade fältet medlemsortering efter värde; sorterad rendering under manuella gruppföräldrar är inte verifierad
• Skrivningar till PivotField.Outline och PivotField.LayoutForm avvisades i det bygget också, så sätt en outline-layout för en fixtur via PivotTable.RowAxisLayout i stället

Inget av de quirken ändrar filformatet, men de avgör om ett regressionstest bevisar något. Ett test som anropar AutoSort med fel bildtext passerar mot en pivot som aldrig sorterades

FAQ

Vad betyder fält 4294967294 i en PivotTable-XML-fil?

Fält 4294967294 är den oesignerade 32-bitarskodningen av fältindex -2, som ECMA-376 reserverar för Values-pseudofältet. Inuti autoSortScope markerar det en dataaxelreferens, och dess x-barn väljer vilket datafält elementen sorteras efter

Sorterar HotXLS pivotelement efter värde utan att Excel är installerat?

Ja. HotXLS utvärderar sorteringen själv när den materialiserar pivotrutnätet, med per-medlem-summor av det valda datafältet, och skriver sortType, rankBy och valfritt autoSortScope så att Excel behåller samma ordning efter en uppdatering. Bara ordningen vid oavgjorda värden kan skilja sig från Excel

Varför är mina pivotelement sorterade alfabetiskt i stället för efter värde?

Oftast för att fältet har en sorteringsriktning men inget mått. Kontrollera att SortType inte är xlpfstManual och att SortDataField ligger mellan 0 och DataFieldCount - 1; med -1 ordnar HotXLS elementen efter etikett i den begärda riktningen

Värdsortering, kollapsade medlemmar och resten av den nativa pivotpipelinen levereras i HotXLS, Delphi-kalkylarkskomponenten för XLS och XLSX, som läser, redigerar och skriver Excel-arbetsböcker utan att Excel är installerat. Bygger du pivoten från grunden först täcker HotXLS-handledningen om att bygga och uppdatera pivottabeller cachar, fält och uppdatering innan du kommer till att ordna dem