HotXLS stelt de "Show Values As"-modi van Excel-pivottabellen in vanuit Delphi via twee properties op TXLSPivotDataField: ShowDataAs voor de klassieke modi zoals running total, difference en percent of row, en ExtendedShowDataAs voor rank, percent of parent en running total percent. TXLSXWorkbook.MaterializeNativePivotTable schrijft daarna het berekende grid zoals Excel dat zou doen. De twee properties sluiten elkaar uit, en de uitgebreide zijn afhankelijk van een extension-URI die Excel hoofdlettergevoelig matcht
Dat laatste detail is de reden dat dit bericht bestaat. Een pivottabel met een rank-kolom lijkt iets kleins om te genereren, en bij de eerste opening is het dat meestal ook. De problemen beginnen wanneer een gebruiker in Excel op Refresh klikt en de rank-kolom zonder enige waarschuwing terugvalt op een gewone som. Show Values As goed krijgen betekent twee XML-vocabulaires goed krijgen, plus een handjevol rekenregels die Excel nooit in de UI documenteert: hoe gelijke standen ranken, wat een cumulatief percentage van een gemiddelde betekent, en welke lege cel een blanco wordt versus een fout
Waarom twee properties voor één Excel-menu?
Excel toont één "Show Values As"-menu, maar het bestand bewaart het op twee verschillende plekken, dus HotXLS stelt twee properties bloot. De oorspronkelijke modi uit Excel 2007 zitten in het attribuut showDataAs van het element dataField binnen pivotTableDefinition, met de ECMA-376-opsomming ST_ShowDataAs. De modi die Excel 2010 toevoegde — rank en de parent- en cumulatieve percentages — pasten niet in die opsomming, dus belandden ze in een x14:dataField-element met een pivotShowAs-attribuut, verpakt in een extLst-extension-blok op hetzelfde dataveld
TXLSPivotShowDataAs spiegelt de eerste groep: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal en xlpsdaIndex. TXLSPivotExtendedShowDataAs dekt de tweede: xlpesdaPercentOfParent, xlpesdaPercentOfParentRow, xlpesdaPercentOfParentColumn, xlpesdaPercentOfRunningTotal, xlpesdaRankAscending en xlpesdaRankDescending, met xlpesdaNone als neutrale waarde. Een dataveld gebruikt de ene groep of de andere, nooit beide. Laat u ShowDataAs op een niet-normale waarde staan en stelt ook nog een extended modus in, dan geeft MaterializeNativePivotTable -1 terug en laat de bestaande uitvoer exact zoals hij was, cellen en pivotgrenzen incluis
BaseField en BaseItem voeren het vergelijkingsdoel mee. BaseField is een zero-based index in de veldenlijst van de pivottabel, niet in de headernamen van de bronkolommen, en hij stuurt vergelijkingen, running totals, percent van een gekozen parent en ranks aan. BaseItem is een zero-based itempositie binnen dat veld, of een van de constanten xlPivotBaseItemPrevious en xlPivotBaseItemNext voor "vergeleken met de buur". Parent-rij- en parent-kolompercentages negeren BaseField volledig; zij kiezen het binnenste veld op de betreffende as
Een gerankte pivot bouwen in Delphi
Het kortste werkende pad is de pivot bouwen, de weergavemodus op het dataveld zetten en hem materialiseren vóór het opslaan. Het voorbeeld hieronder rankt regionale bedragen per categorie aflopend, zodat het grootste bedrag in elke rij rank 1 is
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';
// ... vul rij 2..7 ...
Report := Book.Sheets.Add('Report');
Pivot := Report.AddPivotTable('Data!A1:C7', 4, 3, 'RegionRank');
Pivot.AddRowField('Category'); // pivotveld 0
Pivot.AddColumnField('Region'); // pivotveld 1
Measure := Pivot.AddDataFieldByName('Amount', xlpaSum);
Measure.DisplayName := 'Rank by Region';
Measure.ShowDataAs := xlpsdaNormal; // basismodus blijft uit
Measure.ExtendedShowDataAs := xlpesdaRankDescending;
Measure.BaseField := 1; // rank over 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;Twee gewoontes lonen hier. Ten eerste: zet ShowDataAs := xlpsdaNormal expliciet wanneer u een extended modus kiest, ook op een vers veld, want dezelfde code wordt vaak hergebruikt tegen een geïmporteerde pivot waarvan het dataveld al een basismodus voert. Ten tweede: controleer de returnwaarde. Een geweigerde materialisatie is atomair, wat prettig is, maar het betekent ook dat een gemiste controle een bestand verscheept met het vorige grid erin
BaseField overleeft het opslaan ook als u MaterializeNativePivotTable nooit aanroept. HotXLS schrijft het attribuut baseField voor percent of parent, running total percent en beide rank-modi zodra de extended modus is gezet, dus een werkboek dat direct na de configuratie wordt opgeslagen vertelt Excel nog steeds welk veld erover gerankt moet worden bij een refresh
Wat gaat er mis met de extension-URI?
Het extension-blok dat pivotShowAs voert, wordt geïdentificeerd door de URI {E15A36E0-9728-4e99-A89B-3F7291B0FE68}, en Excel vergelijkt die string hoofdlettergevoelig tijdens een refresh. Let op de kleine e in 4e99. GUID's zijn normaal hoofdletteronafhankelijk en veel XML-schrijvers zetten ze om naar hoofdletters, en dat is precies het faalscenario: een bestand met 4E99 opent in Excel met elke gecachte cel correct, want Excel tekent alleen de opgeslagen waarden. Na RefreshTable herkent Excel de extensie echter niet meer, laat de extended modus vallen en herberekent de kolom als gewone som. Geen reparatieprompt, geen fout, alleen een rank-kolom die stilletjes totalen werd
HotXLS schrijft de letterlijke URI zoals Excel hem verwacht, dus dit bijt u alleen als er iets stroomafwaarts het pakket herschrijft, bijvoorbeeld een nabewerkingsstap die GUID-casing normaliseert of extLst-elementen herbouwt. De test die het vangt, is goedkoop: open het gegenereerde bestand in Excel, refresh de pivot en bevestig dat de extended kolom nog steeds ranks of percentages toont in plaats van rauwe aggregaten. Een test die alleen de gecachte cellen leest, slaagt op een kapot bestand telkens opnieuw
Hoe rankt Excel gelijke standen en ordent hij running totals?
De pivotrank van Excel is een dense rank, en HotXLS reproduceert hem. Bij aflopende bedragen van 30, 30 en 20 is het resultaat 1, 1 en 2, niet de competition-stijl 1, 1 en 3 die u van RANK.EQ op een werkblad krijgt. Een gelijke stand telt als één voorafgaande waarde. Heeft een rapport competition ranking nodig, reken het dan uit in de brongegevens of in een werkbladformule naast de pivot, want geen enkele pivot-weergavemodus produceert het
Running totals, running-total-percentages en vorige/volgende-vergelijkingen volgen de volgorde waarin leden werkelijk verschijnen, niet de volgorde van de itemlijst van het veld. Dat onderscheid telt zodra een aanroeper de sortering wijzigt nadat de cache is gebouwd
// Rijen aflopend gesorteerd: het running total accumuleert in weergavevolgorde
Pivot.Fields[0].SortType := xlpfstDescending;
Measure.ExtendedShowDataAs := xlpesdaNone;
Measure.ShowDataAs := xlpsdaPercentDiff; // % verschil vanaf...
Measure.BaseField := 0; // ...het Category-veld
Measure.BaseItem := xlPivotBaseItemPrevious; // ...het vorige zichtbare lid
if Book.MaterializeNativePivotTable(Pivot) <> 1 then
raise Exception.Create('Pivot display mode was rejected');Nadat SortType op xlpfstDescending is gezet, betekent "previous" het lid dat erboven staat, en begint het running total bovenaan de gesorteerde lijst. Een implementatie die in plaats daarvan de opgeslagen itemlijst doorloopt, produceert getallen die plausibel ogen en pas afwijken van Excel op het moment dat iemand een refresh doet. Ook randleden vragen omzichtigheid: bij het eerste lid is er geen vorige buur, en alleen de percent-of-modus vergelijkt het randlid met zichzelf; de difference-modi laten die cel over aan het eigen randgedrag van Excel in plaats van zelf een nul te verzinnen
Waarom kan een cumulatief percentage boven de 100 procent uitkomen?
Een running-total-percentage over gemiddelden kan legitiem voorbij de 100 procent gaan, want Excel accumuleert de aggregaten van de getoonde leden en deelt door een aggregaat dat niet hun som is. Bij sommen vallen die twee samen, dus het laatste lid landt exact op 100 procent. Bij gemiddelden is het optellen van drie categoriegemiddelden en delen door het totaalgemiddelde geen fractie van iets, en de laatste rij kan ruim boven de 100 procent uitkomen. HotXLS matcht Excel hier met opzet in plaats van de waarde af te klemmen, want een afgeklemde waarde zou afwijken van het gerefreshde bestand
De deler hangt ook af van waar het basisveld zit. Is het basisveld het binnenste veld op zijn as, dan is de deler het oorspronkelijke aggregaat voor die aspositie. Is het basisveld een buitenste veld in een geneste lay-out, dan is de deler de som over de matchende leden van de overblijvende as. Beide posities zijn onafhankelijk tegen Excel-uitvoer getest, want een regel die op één nestniveau werkt en op het andere stilletjes faalt, is de klassieke pivotbug
Gewone percentages gedragen zich anders dan het cumulatieve. Percent of row, column en grand total delen door het oorspronkelijke aggregaat op de betreffende scope, ook voor niet-additieve aggregaties zoals gemiddelden, dus een percent of total op gemiddelden is het celgemiddelde gedeeld door het totaalgemiddelde, niet een aandeel in een opgetelde poel
Blanco's, #NULL! en #DIV/0! in vergelijkingsmodi
Percent-of-base-vergelijkingen onderscheiden drie lege situaties, en elke situatie levert een andere cel op. Heeft de teller-intersectie geen bronrijen, dan toont de cel #NULL!. Heeft het basisitem op die intersectie geen data, dan is de cel blanco. Bestaat het basisitem en is zijn aggregaat nul, dan toont de cel #DIV/0!. Code die het gematerialiseerde grid leest, zou moeten testen met VarIsEmpty en VarIsError in plaats van ervan uit te gaan dat elke waardecel een getal bevat
De gewone percentages zijn vergevingsgezinder: een ontbrekende teller telt daar als nul. Parent-rij- en parent-kolompercentages voegen nog een geval toe, want een pivot zonder veld op de betreffende as heeft geen parent om door te delen, dus die resultaten zijn blanco. Grand totals volgen ook de eigen semantiek van elke modus, inclusief blanco totalen langs de as waarlangs een vergelijking of running total loopt; een totalenrij vol blanco's onder een running total is correct, geen materialisatiefout
Modi wisselen op een geïmporteerde pivot
De weergavemodus van een pivot uit een bestaand werkboek wijzigen werkt op dezelfde manier, met één extra vlag. Geef True door als AAdoptExisting, zodat MaterializeNativePivotTable de cellen mag overnemen die Excel al voor die pivot had geschreven
Pivot := Book.Sheets.SheetByName('Sales').PivotTables[0];
with Pivot.DataFields[0] do
begin
ShowDataAs := xlpsdaNormal; // ruim de basismodus op
ExtendedShowDataAs := xlpesdaPercentOfParentRow; // geen BaseField nodig
end;
if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
raise Exception.Create('Could not re-materialize the pivot');Bij een moduswijziging verwijdert HotXLS verouderde attributen showDataAs, pivotShowAs en baseItem uit de staged definitie, zodat het opgeslagen bestand nooit een verlopen basismodus naast een nieuwe extended modus voert. Dezelfde staging-stap houdt de compact- en outline-vlaggen op data- en inactieve velden in de pas met de rapportlay-out, wat telt omdat Excel gerefreshde data-onderschriften uit die vlaggen afleidt en niet alleen uit de rij- en kolomvelden. Ongeldige enum-waarden worden ook geweigerd: een byte van 255 in een van beide enums casten kan in Delphi sign-extenden naar -1, dus HotXLS controleert zowel de onder- als de bovengrens en geeft -1 terug zonder het grid aan te raken
Voegt u ook berekende velden of berekende items toe aan hetzelfde rapport, lees dan eerst pivottabellen met berekende velden bouwen en refreshen in HotXLS, en voor de veldniveau-XML-regels die verhinderen dat Excel het bestand repareert, zie XLSX pivot field schema validity in Delphi
FAQ
Kan een HotXLS-dataveld ShowDataAs en ExtendedShowDataAs samen gebruiken?
Nee. TXLSPivotDataField.ShowDataAs en ExtendedShowDataAs sluiten elkaar uit; met beide gezet geeft MaterializeNativePivotTable -1 terug en blijft de vorige uitvoer ongewijzigd
Waarom werd mijn pivotrank een som na een refresh in Excel?
De extension-URI is vermoedelijk herschreven met andere casing. Excel matcht {E15A36E0-9728-4e99-A89B-3F7291B0FE68} hoofdlettergevoelig en valt na RefreshTable terug op de gewone aggregatie wanneer hij niet matcht
Slaat de HotXLS-pivotrank getallen over na een gelijke stand?
Nee. HotXLS volgt de dense pivotrank van Excel, dus 30, 30 en 20 ranken als 1, 1 en 2
Show Values As-ondersteuning, native pivotmaterialisatie en de rest van de XLSX- en XLS-functieset zitten in de HotXLS Delphi spreadsheet component voor Delphi en C++Builder