HotXLS sætter Excel-pivottabellers "Show Values As"-modes fra Delphi gennem to egenskaber på TXLSPivotDataField: ShowDataAs til de klassiske modes som løbende total, difference og procent af række, og ExtendedShowDataAs til rank, procent af parent og løbende total-procent. TXLSXWorkbook.MaterializeNativePivotTable skriver derefter det beregnede gitter, som Excel ville. De to egenskaber er gensidigt udelukkende, og de udvidede afhænger af en extension-URI, som Excel matcher case-sensitivt
Netop den detalje er grunden til, at dette opslag findes. En pivottabel med en rank-kolonne ligner en lille ting at generere, og ved første åbning er den det som regel. Problemet starter, når en bruger klikker Refresh i Excel, og rank-kolonnen vender tilbage til en almindelig sum uden nogen advarsel. At få Show Values As rigtigt betyder at få to XML-vokabularer rigtigt plus en håndfuld numeriske regler, Excel aldrig dokumenterer i UI'et: hvordan ligge rangerer, hvad en kumulativ procent af et gennemsnit betyder, og hvilken tom celle der bliver blank frem for en fejl
Hvorfor er der to egenskaber til én Excel-menu?
Excel viser én "Show Values As"-menu, men filen gemmer den to forskellige steder, så HotXLS eksponerer to egenskaber. De oprindelige modes fra Excel 2007 ligger i showDataAs-attributten på dataField-elementet inde i pivotTableDefinition med ECMA-376 ST_ShowDataAs-opremsningen. De modes, Excel 2010 tilføjede, rank og parent- og kumulative procenter, passede ikke i den opremsning, så de kom i et x14:dataField-element med en pivotShowAs-attribut, pakket ind i en extLst-extensionblok på samme datafelt
TXLSPivotShowDataAs spejler den første gruppe: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal og xlpsdaIndex. TXLSPivotExtendedShowDataAs dækker den anden: xlpesdaPercentOfParent, xlpesdaPercentOfParentRow, xlpesdaPercentOfParentColumn, xlpesdaPercentOfRunningTotal, xlpesdaRankAscending og xlpesdaRankDescending med xlpesdaNone som neutral værdi. Et datafelt bruger én gruppe eller den anden, aldrig begge. Lader man ShowDataAs stå på en non-normal værdi og også sætter en udvidet mode, returnerer MaterializeNativePivotTable -1 og lader det eksisterende output stå præcis som det var, celler og pivot-grænser inkluderet
BaseField og BaseItem bærer sammenligningsmålet. BaseField er et nul-baseret indeks i pivottabellens feltliste, ikke i source-kolonnerne header-navne, og det driver sammenligninger, løbende totaler, procent af en valgt parent og rangeringer. BaseItem er en nul-baseret item-position inde i det felt, eller en af konstanterne xlPivotBaseItemPrevious og xlPivotBaseItemNext for "sammenlignet med nabo". Parent-række- og parent-kolonne-procenter ignorerer BaseField helt; de vælger det inderste felt på den matchende akse
At bygge en rangeret pivot i Delphi
Den korteste virkende vej er at bygge pivoten, sætte visningsmoden på datafeltet og materialisere den før gem. Eksemplet nedenfor rangerer regionale beløb pr. kategori i faldende rækkefølge, så det største beløb i hver række er rank 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';
// ... udfyld rækker 2..7 ...
Report := Book.Sheets.Add('Report');
Pivot := Report.AddPivotTable('Data!A1:C7', 4, 3, 'RegionRank');
Pivot.AddRowField('Category'); // pivotfelt 0
Pivot.AddColumnField('Region'); // pivotfelt 1
Measure := Pivot.AddDataFieldByName('Amount', xlpaSum);
Measure.DisplayName := 'Rank by Region';
Measure.ShowDataAs := xlpsdaNormal; // basismoden forbliver slået fra
Measure.ExtendedShowDataAs := xlpesdaRankDescending;
Measure.BaseField := 1; // rank på tværs af 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;To vaner betaler sig her. For det første: sæt ShowDataAs := xlpsdaNormal eksplicit, når man vælger en udvidet mode, selv på et friskt felt, for den samme kode ender ofte med at blive genbrugt mod en importeret pivot, hvis datafelt allerede bærer en basismode. For det andet: tjek returværdien. En afvist materialisering er atomar, hvilket er godt, men det betyder også, at en overset tjek sender en fil med det tidligere gitter i sig
BaseField overlever gem, selv hvis man aldrig kalder MaterializeNativePivotTable. HotXLS skriver baseField-attributten for procent af parent, løbende total-procent og begge rank-modes, så snart den udvidede mode er sat, så en arbejdsbog gemt lige efter konfiguration stadig fortæller Excel, hvilket felt der skal rangeres på tværs af, når den refresh'er
Hvad går galt med extension-URI'en?
Extensionblokken, der bærer pivotShowAs, identificeres af URI'en {E15A36E0-9728-4e99-A89B-3F7291B0FE68}, og Excel sammenligner den streng case-sensitivt under refresh. Læg mærke til det lille e i 4e99. GUID'er er normalt case-insensitive, og mange XML-skrivere gør dem store, hvilket præcis er fejlmåden: en fil med 4E99 åbner i Excel med hver cachede celle korrekt, for Excel maler blot de lagrede værdier. Efter RefreshTable genkender Excel dog ikke længere extensionen, dropper den udvidede mode og genberegner kolonnen som en almindelig sum. Ingen reparationsdialog, ingen fejl, blot en rank-kolonne, der stille blev totaler
HotXLS skriver den literale URI, som Excel forventer den, så det bider kun, hvis noget downstream omskriver pakken, for eksempel et post-processing-trin, der normaliserer GUID-casing eller bygger extLst-elementer igen. Testen, der fanger det, er billig: åbn den genererede fil i Excel, refresh pivoten, og bekræft, at den udvidede kolonne stadig viser rangeringer eller procenter frem for rå aggregater. En test, der kun læser de cachede celler, består på en ødelagt fil hver gang
Hvordan rangerer Excel ligge og ordner løbende totaler?
Excels pivot-rank er en dense rank, og HotXLS gengiver den. Med faldende beløb på 30, 30 og 20 er resultatet 1, 1 og 2, ikke konkurrence-stilen 1, 1 og 3, som man får fra RANK.EQ på et regneark. En lige tæller som én distinkt foregående værdi. Kræver en rapport konkurrence-rangering, så beregn den i source-dataene eller i en regnearksformel ved siden af pivoten, for ingen pivot-visningsmode frembringer den
Løbende totaler, løbende total-procenter og tidligere/næste-sammenligninger følger den rækkefølge, medlemmer faktisk optræder i, ikke rækkefølgen af feltets item-liste. Den skel betyder noget, så snart en kalder ændrer sorteringen, efter cacheen er bygget
// Rækker sorteret faldende: den løbende total akkumulerer i visningsrækkefølge
Pivot.Fields[0].SortType := xlpfstDescending;
Measure.ExtendedShowDataAs := xlpesdaNone;
Measure.ShowDataAs := xlpsdaPercentDiff; // %-forskel fra...
Measure.BaseField := 0; // ...Category-feltet
Measure.BaseItem := xlPivotBaseItemPrevious; // ...forrige synlige medlem
if Book.MaterializeNativePivotTable(Pivot) <> 1 then
raise Exception.Create('Pivot display mode was rejected');Efter SortType skifter til xlpfstDescending betyder "previous" medlemmet vist ovenover, og den løbende total starter fra toppen af den sorterede liste. En implementering, der i stedet går den lagrede item-liste igennem, frembringer tal, der ligner noget og tager fejl af Excel i det øjeblik nogen refresh'er. Kantmedlemmer kræver også omtanke: ved det første medlem er der ingen tidligere nabo, og kun percent-of-moden sammenligner kantmedlemmet med sig selv; difference-modes lader den celle være til Excels egen kantadfærd i stedet for at finde på et nul
Hvorfor kan en kumulativ procent overstige 100 procent?
En løbende total-procent over gennemsnit kan legitimt komme forbi 100 procent, for Excel akkumulerer de viste medlemsaggregater og dividerer med et aggregat, der ikke er deres sum. For summer er de to det samme, så sidste medlem lander på præcis 100 procent. For gennemsnit er det at lægge tre kategorigennemsnit sammen og dividere med det overordnede gennemsnit ikke en brøkdel af noget, og den sidste række kan lande langt over 100 procent. HotXLS matcher Excel her med vilje frem for at clamp'e værdien, for et clamped tal ville tage fejl af den refreshede fil
Divisoren afhænger også af, hvor base-feltet sidder. Er base-feltet det inderste felt på sin akse, er divisoren det oprindelige aggregat for den akseposition. Er base-feltet et ydre felt i et indlejret layout, er divisoren summen på tværs af de matchende medlemmer af den resterende akse. Begge positioner blev tjekket uafhængigt mod Excel-output, for en regel, der virker på ét nesting-niveau og stille fejler på det andet, er den klassiske pivot-bug
Enkle procenter opfører sig anderledes end den kumulative. Procent af række, kolonne og grand total dividerer med det oprindelige aggregat på det relevante scope, også for ikke-additive aggregeringer som gennemsnit, så en procent af total på gennemsnit er celle-gennemsnittet divideret med det overordnede gennemsnit, ikke en andel af en opsummeret pool
Blanke, #NULL! og #DIV/0! i sammenligningsmodes
Percent-of-base-sammenligninger skelner tre tomme situationer, og hver frembringer en anden celle. Har tæller-skæringen ingen source-rækker, viser cellen #NULL!. Har base-itemmet ingen data ved den skæring, er cellen blank. Findes base-itemmet, og dets aggregat er nul, viser cellen #DIV/0!. Kode, der læser det materialiserede gitter, bør teste med VarIsEmpty og VarIsError frem for at antage, at hver værdicelle holder et tal
De enkle procenter er mere tilgivende: en fraværende tæller tæller som nul dér. Parent-række- og parent-kolonne-procenter tilføjer endnu et tilfælde, da en pivot uden felt på den tilsvarende akse ikke har nogen parent at dividere med, så de resultater er blanke. Grand totals følger også hver modes egen semantik, hvilket inkluderer blanke totaler langs aksen, en sammenligning eller løbende total går langs; en totalrække fuld af blanke under en løbende total er korrekt, ikke en materialiseringsfejl
At skifte modes på en importeret pivot
At skifte visningsmoden på en pivot, der kom fra en eksisterende arbejdsbog, virker på samme måde med ét ekstra flag. Giv True som AAdoptExisting, så MaterializeNativePivotTable må overtage de celler, Excel allerede skrev for den pivot
Pivot := Book.Sheets.SheetByName('Sales').PivotTables[0];
with Pivot.DataFields[0] do
begin
ShowDataAs := xlpsdaNormal; // ryd basismoden
ExtendedShowDataAs := xlpesdaPercentOfParentRow; // intet BaseField behøves
end;
if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
raise Exception.Create('Could not re-materialize the pivot');Når moden skifter, fjerner HotXLS forældede showDataAs-, pivotShowAs- og baseItem-attributter fra den stagede definition, så den gemte fil aldrig bærer en forældet basismode ved siden af en ny udvidet. Samme staging-trin holder compact- og outline-flaggene på data- og inaktive felter i trit med rapportlayoutet, hvilket betyder noget, for Excel afleder refreshede data-captions fra de flag og ikke kun fra række- og kolonnefelter. Ugyldige enum-værdier afvises også: at caste en byte på 255 ind i en af enummerne kan sign-extend'e til -1 i Delphi, så HotXLS tjekker både nedre og øvre grænse og returnerer -1 uden at røre gitteret
Tilføjer man også calculated fields eller calculated items til samme rapport, så læs at bygge og refresh'e pivottabeller med calculated fields i HotXLS først, og for felt-niveau XML-reglerne, der holder Excel fra at reparere filen, se XLSX pivot field schema-validitet i Delphi
FAQ
Kan et HotXLS-datafelt bruge ShowDataAs og ExtendedShowDataAs sammen?
Nej. TXLSPivotDataField.ShowDataAs og ExtendedShowDataAs er gensidigt udelukkende; med begge sat returnerer MaterializeNativePivotTable -1 og lader det tidligere output uændret
Hvorfor blev min pivot-rank til en sum efter refresh i Excel?
Extension-URI'en blev sandsynligvis omskrevet med en anden casing. Excel matcher {E15A36E0-9728-4e99-A89B-3F7291B0FE68} case-sensitivt og falder tilbage på den almindelige aggregering efter RefreshTable, når den ikke matcher
Springer HotXLS pivot-rank tal over efter en lige?
Nej. HotXLS følger Excels dense pivot-rank, så 30, 30 og 20 rangeres som 1, 1 og 2
Show Values As-understøttelse, native pivot-materialisering og resten af XLSX- og XLS-funktionssættet følger med i HotXLS Delphi spreadsheet component til Delphi og C++Builder