HotXLS ställer in Excel-pivottabellers "Show Values As"-lägen från Delphi via två egenskaper på TXLSPivotDataField: ShowDataAs för de klassiska lägena som löpande summa, skillnad och procent av rad, och ExtendedShowDataAs för rang, procent av förälder och löpande summoprocent. TXLSXWorkbook.MaterializeNativePivotTable skriver sedan det beräknade rutnätet som Excel skulle ha gjort. De två egenskaperna är ömsesidigt uteslutande, och de utökade beror på en extension-URI som Excel matchar skiftlägeskänsligt
Den sista detaljen är skälet till att det här inlägget finns. En pivottabell med en rangkolumn ser ut som en liten sak att generera, och vid första öppningen är det oftast sant. Problemet börjar när en användare klickar Uppdatera i Excel och rangkolumnen går tillbaka till en vanlig summa utan någon varning. Att få Show Values As rätt betyder att få två XML-vokabularer rätt, plus en handfull numeriska regler Excel aldrig dokumenterar i användargränssnittet: hur lika värden rangordnas, vad en kumulativ procent av ett medelvärde betyder, och vilken tom cell som blir blank jämfört med ett fel
Varför finns det två egenskaper för en enda Excel-meny?
Excel visar en enda "Show Values As"-meny, men filen lagrar den på två olika ställen, så HotXLS exponerar två egenskaper. De ursprungliga lägena från Excel 2007 bor i attributet showDataAs på elementet dataField inuti pivotTableDefinition, med ECMA-376-uppräkningen ST_ShowDataAs. De lägen Excel 2010 lade till, rang och de parent- och kumulativa procentsatserna, fick inte plats i den uppräkningen, så de hamnade i ett x14:dataField-element med ett pivotShowAs-attribut, insvept i ett extLst-extensionblock på samma datafält
TXLSPivotShowDataAs speglar första gruppen: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal och xlpsdaIndex. TXLSPivotExtendedShowDataAs täcker den andra: xlpesdaPercentOfParent, xlpesdaPercentOfParentRow, xlpesdaPercentOfParentColumn, xlpesdaPercentOfRunningTotal, xlpesdaRankAscending och xlpesdaRankDescending, med xlpesdaNone som neutralt värde. Ett datafält använder ena gruppen eller den andra, aldrig båda. Lämnar du ShowDataAs på ett icke-normalt värde och dessutom sätter ett utökat läge returnerar MaterializeNativePivotTable -1 och lämnar befintlig utdata exakt som den var, celler och pivotgränser inkluderade
BaseField och BaseItem bär jämförelsemålet. BaseField är ett nollbaserat index i pivottabellens fältlista, inte i källkolumnernas rubriknamn, och det driver jämförelser, löpande summor, procent av en vald förälder och ranger. BaseItem är en nollbaserad postposition inuti det fältet, eller en av konstanterna xlPivotBaseItemPrevious och xlPivotBaseItemNext för "jämfört med grannen". Parent-rad- och parent-kolumn-procentsatserna ignorerar BaseField helt; de plockar det innersta fältet på matchande axel
Bygga en rankad pivot i Delphi
Den kortaste fungerande vägen är att bygga pivottabellen, sätta visningsläget på datafältet och materialisera den före sparning. Exemplet nedan rankar regionala belopp per kategori i fallande ordning, så det största beloppet i varje rad får 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';
// ... fyll rader 2..7 ...
Report := Book.Sheets.Add('Report');
Pivot := Report.AddPivotTable('Data!A1:C7', 4, 3, 'RegionRank');
Pivot.AddRowField('Category'); // pivotfält 0
Pivot.AddColumnField('Region'); // pivotfält 1
Measure := Pivot.AddDataFieldByName('Amount', xlpaSum);
Measure.DisplayName := 'Rank by Region';
Measure.ShowDataAs := xlpsdaNormal; // basläget hålls av
Measure.ExtendedShowDataAs := xlpesdaRankDescending;
Measure.BaseField := 1; // rang över 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;Två vanor lönar sig här. För det första, sätt ShowDataAs := xlpsdaNormal explicit när du väljer ett utökat läge, även på ett färskt fält, eftersom samma kod ofta återanvänds mot en importerad pivot vars datafält redan bär ett basläge. För det andra, kontrollera returvärdet. En avvisad materialisering är atomär, vilket är bra, men det betyder också att en missad kontroll levererar en fil med det tidigare rutnätet i sig
BaseField överlever sparningen även om du aldrig anropar MaterializeNativePivotTable. HotXLS skriver attributet baseField för procent av förälder, löpande summoprocent och båda ranglägena så snart det utökade läget är satt, så en arbetsbok som sparas direkt efter konfigurationen talar fortfarande om för Excel vilket fält som ska rankas över när den uppdateras
Vad går fel med extension-URI:n?
Extensionblocket som bär pivotShowAs identifieras av URI:n {E15A36E0-9728-4e99-A89B-3F7291B0FE68}, och Excel jämför den strängen skiftlägeskänsligt vid uppdatering. Notera den gemena e i 4e99. GUID:er är normalt skiftlägesokänsliga och många XML-skrivare skriver dem med versaler, vilket är exakt felfallet: en fil med 4E99 öppnas i Excel med alla cachade celler korrekta, eftersom Excel bara målar de lagrade värdena. Efter RefreshTable känner Excel dock inte längre igen extensionen, tappar det utökade läget och räknar om kolumnen som en vanlig summa. Ingen reparationsprompt, inget fel, bara en rangkolumn som tyst blev summor
HotXLS skriver den literala URI:n som Excel förväntar sig, så det här biter bara dig om något nedströms skriver om paketet, till exempel ett efterbehandlingssteg som normaliserar GUID-versaler eller bygger om extLst-element. Testet som fångar det är billigt: öppna den genererade filen i Excel, uppdatera pivottabellen och bekräfta att den utökade kolumnen fortfarande visar ranger eller procentsatser snarare än råa aggregationer. Ett test som bara läser de cachade cellerna går igenom på en trasig fil varje gång
Hur rankar Excel lika värden och ordnar löpande summor?
Excel:s pivotorang är en dense rank, och HotXLS återskapar den. Med fallande belopp 30, 30 och 20 blir resultatet 1, 1 och 2, inte tävlingsstilen 1, 1 och 3 som du får av RANK.EQ på ett kalkylblad. Ett lika värde räknas som ett föregående distinkt värde. Behöver en rapport tävlingsrankning, beräkna den i källdata eller i en kalkylbladsformel bredvid pivottabellen, för inget pivotvisningsläge producerar den
Löpande summor, löpande summoprocentsatser och jämförelser med tidigare eller nästa följer ordningen medlemmarna faktiskt visas i, inte ordningen i fältets postlista. Den skillnaden spelar roll så fort en anropare ändrar sorteringen efter att cachen byggts
// Rader sorterade fallande: den löpande summan ackumuleras i visningsordning
Pivot.Fields[0].SortType := xlpfstDescending;
Measure.ExtendedShowDataAs := xlpesdaNone;
Measure.ShowDataAs := xlpsdaPercentDiff; // % skillnad mot...
Measure.BaseField := 0; // ...Category-fältet
Measure.BaseItem := xlPivotBaseItemPrevious; // ...föregående synliga medlem
if Book.MaterializeNativePivotTable(Pivot) <> 1 then
raise Exception.Create('Pivot display mode was rejected');Efter att SortType ändrats till xlpfstDescending betyder "previous" medlemmen som visas ovanför, och den löpande summan börjar från toppen av den sorterade listan. En implementation som vandrar i den lagrade postlistan i stället producerar tal som ser rimliga ut och strider mot Excel i ögonblick någon uppdaterar. Kantmedlemmar behöver också omsorg: vid den första medlemmen finns ingen tidigare granne, och bara procent-av-läget jämför kantmedlemmen med sig själv; skillnadslägena lämnar den cellen till Excel:s eget kantbeteende i stället för att hitta på en nolla
Varför kan en kumulativ procentsats överstiga 100 procent?
En löpande summoprocentsats över medelvärden kan legitimt passera 100 procent, eftersom Excel ackumulerar de visade medlemsaggregationerna och dividerar med en aggregation som inte är deras summa. För summor är de två samma sak, så den sista medlemmen landar på exakt 100 procent. För medelvärden är att addera tre kategorimedelvärden och dividera med det övergripande medelvärdet inte en andel av något, och sista raden kan landa långt över 100 procent. HotXLS matchar Excel här med flit i stället för att klämma värdet, eftersom ett klämt tal skulle strida mot den uppdaterade filen
Divisorn beror också på var base-fältet sitter. Är base-fältet det innersta fältet på sin axel är divisorn den ursprungliga aggregationen för den axelpositionen. Är base-fältet ett yttre fält i en nästlad layout är divisorn summan över de matchande medlemmarna av den kvarvarande axeln. Båda positionerna kontrollerades oberoende mot Excel-utdata, eftersom en regel som fungerar på en nästlingsnivå och tyst fallerar på den andra är den klassiska pivotbuggen
Enkla procentsatser beter sig annorlunda än den kumulativa. Procent av rad, kolumn och grand total dividerar med den ursprungliga aggregationen på relevant omfattning, även för icke-additiva aggregationer som medelvärden, så en procent av totalt på medelvärden är cellens medelvärde dividerat med det övergripande medelvärdet, inte en andel av en summerad pool
Blanka, #NULL! och #DIV/0! i jämförelselägen
Procent-av-base-jämförelser skiljer tre tomma situationer, och var och en producerar en annan cell. Har täljarsnittet inga källrader visar cellen #NULL!. Har basposten ingen data vid det snittet är cellen blank. Finns basposten och dess aggregation är noll visar cellen #DIV/0!. Kod som läser det materialiserade rutnätet bör testa med VarIsEmpty och VarIsError i stället för att anta att varje värdecell håller ett tal
De enkla procentsatserna är mer förlåtande: en saknad täljare räknas som noll där. Parent-rad- och parent-kolumn-procentsatserna lägger till ett fall till, eftersom en pivot utan fält på motsvarande axel saknar en förälder att dividera med, så de resultaten är blanka. Grand totals följer varje läges egen semantik också, vilket inkluderar blanka summor längs axeln som en jämförelse eller löpande summa vandrar; en totalrad full av blanka under en löpande summa är korrekt, inte ett materialiseringsfel
Byta lägen på en importerad pivot
Att byta visningsläge för en pivot som kom från en befintlig arbetsbok fungerar på samma sätt, med en extra flagga. Skicka True som AAdoptExisting så att MaterializeNativePivotTable får ta över cellerna Excel redan skrev för den pivottabellen
Pivot := Book.Sheets.SheetByName('Sales').PivotTables[0];
with Pivot.DataFields[0] do
begin
ShowDataAs := xlpsdaNormal; // rensa basläget
ExtendedShowDataAs := xlpesdaPercentOfParentRow; // inget BaseField behövs
end;
if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
raise Exception.Create('Could not re-materialize the pivot');När läget ändras tar HotXLS bort föråldrade showDataAs-, pivotShowAs- och baseItem-attribut ur den mellanlagrade definitionen, så den sparade filen aldrig bär ett gammalt basläge bredvid ett nytt utökat. Samma mellanlagringssteg håller compact- och outline-flaggorna på data- och inaktiva fält i stäm med rapportlayouten, vilket spelar roll eftersom Excel härleder uppdaterade datacaptioner ur de flaggorna och inte bara ur rad- och kolumnfälten. Ogiltiga uppräkningsvärden avvisas också: att kasta en byte på 255 in i endera uppräkningen kan sign-extenda till -1 i Delphi, så HotXLS kontrollerar både nedre och övre gräns och returnerar -1 utan att röra rutnätet
Lägger du också till beräknade fält eller beräknade poster i samma rapport, läs att bygga och uppdatera pivottabeller med beräknade fält i HotXLS först, och för fältnivåns XML-regler som hindrar Excel från att reparera filen, se XLSX pivot field schema-validitet i Delphi
FAQ
Kan ett HotXLS-datafält använda ShowDataAs och ExtendedShowDataAs tillsammans?
Nej. TXLSPivotDataField.ShowDataAs och ExtendedShowDataAs är ömsesidigt uteslutande; med båda satta returnerar MaterializeNativePivotTable -1 och lämnar tidigare utdata oförändrad
Varför blev min pivotorang en summa efter uppdatering i Excel?
Extension-URI:n skrevs troligen om med annan versalhantering. Excel matchar {E15A36E0-9728-4e99-A89B-3F7291B0FE68} skiftlägeskänsligt och faller tillbaka på den vanliga aggregationen efter RefreshTable när den inte matchar
Hoppar HotXLS pivotorang över tal efter ett lika värde?
Nej. HotXLS följer Excel:s dense pivotorang, så 30, 30 och 20 rankas som 1, 1 och 2
Show Values As-stöd, materialisering av inbyggda pivottabeller och resten av XLSX- och XLS-funktionsuppsättningen medföljer HotXLS Delphi spreadsheet component för Delphi och C++Builder