Pivot Show Values As v Delphi: pořadí a běžící součet

HotXLS nastavuje z Delphi módy „Show Values As“ kontingenční tabulky Excel přes dvě vlastnosti na TXLSPivotDataField: ShowDataAs pro klasické módy jako běžící součet, rozdíl a procento z řádku a ExtendedShowDataAs pro pořadí, procento z nadřazeného a procento běžícího součtu. TXLSXWorkbook.MaterializeNativePivotTable pak zapíše vypočtenou mřížku tak, jak by ji napsal Excel. Ty dvě vlastnosti se vylučují a ty rozšířené závisí na rozšiřujícím URI, které Excel páruje citlivě na velikost písmen

Právě tenhle detail je důvod, proč tenhle příspěvek existuje. Kontingenční tabulka se sloupcem pořadí vypadá jako maličkost na vygenerování a při prvním otevření jí to obvykle bývá. Problém začíná, když uživatel klikne v Excelu na Obnovit a sloupec pořadí se bez varování změní zpátky na obyčejný součet. Správné Show Values As znamená správně dvě XML slovní zásoby, plus hrstku číselných pravidel, která Excel nikdy nedokumentuje v UI: jak se řadí shody, co znamená kumulativní procento z průměru a která prázdná buňka se stane prázdnou a která chybou

Proč jsou na jedno menu Excelu dvě vlastnosti?

Excel ukazuje jedno menu „Show Values As“, ale soubor si ho ukládá na dvou různých místech, takže HotXLS vystavuje dvě vlastnosti. Původní módy z Excelu 2007 bydlí v atributu showDataAs elementu dataField uvnitř pivotTableDefinition a používají výčet ECMA-376 ST_ShowDataAs. Módy, které přidal Excel 2010 — pořadí a procenta z nadřazeného a kumulativní — se do toho výčtu nevešly, takže skončily v elementu x14:dataField s atributem pivotShowAs, zabaleném v rozšiřujícím bloku extLst na též datovém poli

TXLSPivotShowDataAs zrcadlí první skupinu: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal a xlpsdaIndex. TXLSPivotExtendedShowDataAs pokrývá druhou: xlpesdaPercentOfParent, xlpesdaPercentOfParentRow, xlpesdaPercentOfParentColumn, xlpesdaPercentOfRunningTotal, xlpesdaRankAscending a xlpesdaRankDescending, s xlpesdaNone jako neutrální hodnotou. Datové pole použije jednu skupinu nebo druhou, nikdy obě. Necháte-li ShowDataAs na nenormální hodnotě a nastavíte-li navíc rozšířený mód, vrátí MaterializeNativePivotTable -1 a stávající výstup nechá úplně tak, jak byl, buňky a hranice kontingence včetně

BaseField a BaseItem nesou srovnávací cíl. BaseField je index od nuly do seznamu polí kontingenční tabulky, ne do názvů záhlaví zdrojových sloupců, a řídí srovnání, běžící součty, procenta ze zvoleného nadřazeného a pořadí. BaseItem je pozice položky od nuly uvnitř toho pole nebo jedna z konstant xlPivotBaseItemPrevious a xlPivotBaseItemNext pro „srovnání se sousedem“. Procenta z nadřazeného řádku a sloupce ignorují BaseField úplně; vyberou nejvnitřnější pole na odpovídající ose

Stavba kontingence s pořadím v Delphi

Nejkratší funkční cesta je postavit kontingenci, nastavit zobrazovací mód na datovém poli a před uložením ji zmaterializovat. Příklad níže řadí regionální částky podle kategorií sestupně, takže největší částka v každém řádku má pořadí 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';
    // ... vyplňte řádky 2..7 ...
    Report := Book.Sheets.Add('Report');

    Pivot := Report.AddPivotTable('Data!A1:C7', 4, 3, 'RegionRank');
    Pivot.AddRowField('Category');     // kontingenční pole 0
    Pivot.AddColumnField('Region');    // kontingenční pole 1
    Measure := Pivot.AddDataFieldByName('Amount', xlpaSum);
    Measure.DisplayName := 'Rank by Region';

    Measure.ShowDataAs := xlpsdaNormal;              // základní mód zůstává vypnutý
    Measure.ExtendedShowDataAs := xlpesdaRankDescending;
    Measure.BaseField := 1;                          // pořadí napříč 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;

Dva návyky se tady vyplatí. Zapatře, nastavujte ShowDataAs := xlpsdaNormal explicitně, když volíte rozšířený mód, i na čerstvém poli, protože týž kód často skončí znovupoužitý proti importované kontingenci, jejíž datové pole už základní mód nese. Za druhé kontrolujte návratovou hodnotu. Odmítnutá materializace je atomická, což je dobře, ale znamená to taky, že přehlédnutá kontrola odešle soubor s předchozí mřížkou uvnitř

BaseField přežije uložení, i kdybyste MaterializeNativePivotTable nikdy nezavolali. HotXLS zapisuje atribut baseField pro procento z nadřazeného, procento běžícího součtu a oba módy pořadí hned, jakmile je rozšířený mód nastavený, takže sešit uložený hned po konfiguraci pořád říká Excelu, přes které pole má řadit, až bude obnovovat

Co se kazí na rozšiřujícím URI?

Rozšiřující blok nesoucí pivotShowAs se identifikuje URI {E15A36E0-9728-4e99-A89B-3F7291B0FE68} a Excel ten řetězec při obnově srovnává citlivě na velikost písmen. Všimněte si malého e v 4e99. GUIDy jsou obvykle necitlivé na velikost písmen a spousta XML zapisovačů je dává do velkých písmen, což je přesně ten selhavý scénář: soubor s 4E99 se v Excelu otevře s každou cacheovanou buňkou správně, protože Excel jen vykreslí uložené hodnoty. Po RefreshTable už ale Excel rozšíření nerozpozná, zahodí rozšířený mód a přepočítá sloupec jako obyčejný součet. Žádná výzva k opravě, žádná chyba, jen sloupec pořadí, který se potichu proměnil v součty

HotXLS zapisuje URI doslovně tak, jak ho Excel čeká, takže to kousne jen tehdy, když něco po cestě přepíše balíček, třeba post-processing krok, který sjednotí velikost písmen GUIDů nebo znovu postaví elementy extLst. Test, který to chytne, je levný: otevřete vygenerovaný soubor v Excelu, obnovte kontingenci a potvrďte, že rozšířený sloupec stále ukazuje pořadí nebo procenta, ne syrové agregace. Test, který jen čte cacheované buňky, projde na rozbitém souboru pokaždé

Jak Excel řadí shody a pořaduje běžící součty?

Kontingenční pořadí Excelu je dense rank a HotXLS ho reprodukuje. U sestupných částek 30, 30 a 20 je výsledek 1, 1 a 2, ne soutěžní styl 1, 1 a 3, který dá RANK.EQ na listu. Shoda se počítá jako jedna odlišná předcházející hodnota. Potřebuje-li report soutěžní řazení, spočítejte ho ve zdrojových datech nebo ve vzorci listu vedle kontingence, protože žádný zobrazovací mód kontingence ho nevyprodukuje

Běžící součty, procenta běžícího součtu a srovnání předchozí nebo další následují pořadí, ve kterém členové doopravdy vystupují, ne pořadí seznamu položek pole. Ten rozdíl začne hrát rolu, jakmile volající změní řazení po sestavení cache

// Řádky seřazené sestupně: běžící součet kumuluje v zobrazovacím pořadí
Pivot.Fields[0].SortType := xlpfstDescending;

Measure.ExtendedShowDataAs := xlpesdaNone;
Measure.ShowDataAs := xlpsdaPercentDiff;   // % rozdíl od...
Measure.BaseField := 0;                    // ...pole Category
Measure.BaseItem := xlPivotBaseItemPrevious; // ...předchozí viditelný člen

if Book.MaterializeNativePivotTable(Pivot) <> 1 then
  raise Exception.Create('Pivot display mode was rejected');

Jakmile se SortType změní na xlpfstDescending, znamená „předchozí“ člen zobrazený výše a běžící součet začíná na vrcholu seřazeného seznamu. Implementace, která jde po uloženém seznamu položek, produkuje čísla, která vypadají věrohodně a rozcházejí se s Excelem v okamžiku, kdy kdokoli obnoví. Okrajoví členové potřebují pozornost taky: u prvního člena žádný předchozí soused není a jen mód procent-o srovnává okrajového člena se sebou samým; módy rozdílu nechávají tu buňku vlastnímu chování Excelu na okraji místo vymyslet nulu

Proč může kumulativní procento přesáhnout 100 procent?

Procento běžícího součtu nad průměry může legitimně překročit 100 procent, protože Excel kumuluje zobrazované agregáty členů a dělí agregátem, který není jejich součtem. U součtů je to to samé, takže poslední člen dosedne přesně na 100 procent. U průměrů sečíst tři průměry kategorií a vydělit celkovým průměrem není zlomek ničeho a poslední řádek může dosednout daleko nad 100 procent. HotXLS se Excelu tady záměrně přizpůsobuje místo hodnotu zastropovat, protože zastropované číslo by se rozcházelo s obnoveným souborem

Dělitel taky záleží na tom, kde základní pole sedí. Když je základní pole nejvnitřnějším polem na své ose, dělitelem je původní agregát pro tu pozici osy. Když je základní pole vnějším polem ve vnořeném uspořádání, dělitelem je součet napříč odpovídajícími členy zbývající osy. Obě pozice se proti výstupu Excelu kontrolovaly nezávisle, protože pravidlo, které funguje na jedné úrovni zanoření a potichu selhává na druhé, je klasická kontingenční chyba

Procenty bez kumulace se chovají jinak než kumulativní. Procento z řádku, sloupce a celkového součtu dělí původním agregátem v příslušném rozsahu, i u neaditivních agregací jako průměry, takže procento z celku na průměrech je průměr buňky vydělený celkovým průměrem, ne podíl na sečteném celku

Prázdné buňky, #NULL! a #DIV/0! v srovnávacích módech

Srovnání procent-o-základ rozlišují tři prázdné situace a každá produkuje jinou buňku. Když průsečík čitatele nemá žádné zdrojové řádky, buňka ukazuje #NULL!. Když základní položka nemá na tom průsečíku žádná data, buňka je prázdná. Když základní položka existuje a její agregát je nula, buňka ukazuje #DIV/0!. Kód, který čte zmaterializovanou mřížku, má testovat pomocí VarIsEmpty a VarIsError místo předpokládat, že každá hodnotová buňka drží číslo

Procenty bez kumulace jsou shovívavější: chybějící čitatel se tam počítá jako nula. Procenta z nadřazeného řádku a sloupce přidávají ještě jeden případ, protože kontingence bez pole na odpovídající ose nemá nadřazeného, kým by dělila, takže ty výsledky jsou prázdné. Celkové součty následují sémantiku každého módu taky, což zahrnuje prázdné součty podél osy, kterou srovnání nebo běžící součet prochází; řádek součtů plný prázdných pod běžícím součtem je správně, ne selhání materializace

Přepínání módů na importované kontingenci

Změna zobrazovacího módu kontingence, která přišla z existujícího sešitu, funguje stejně, s jedním příznakem navíc. Předejte True jako AAdoptExisting, aby si MaterializeNativePivotTable mohla vzít buňky, které Excel už pro tu kontingenci napsal

Pivot := Book.Sheets.SheetByName('Sales').PivotTables[0];
with Pivot.DataFields[0] do
begin
  ShowDataAs := xlpsdaNormal;                    // smaž základní mód
  ExtendedShowDataAs := xlpesdaPercentOfParentRow; // BaseField není potřeba
end;
if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
  raise Exception.Create('Could not re-materialize the pivot');

Při změně módu HotXLS odstraní z připravené definice zastaralé atributy showDataAs, pivotShowAs a baseItem, takže uložený soubor nikdy nese zastaralý základní mód vedle nového rozšířeného. Táž přípravný krok drží příznaky compact a outline na datových a neaktivních polích v kroku s rozvržením reportu, na čem záleží, protože Excel odvozuje obnovené popisky dat z těch příznaků a ne jen z řádkových a sloupcových polí. Neplatné hodnoty výčtů se odmítají taky: přetypování bajtu 255 do kterhokoli výčtu se v Delphi může rozšířit znaménkem na -1, takže HotXLS kontroluje dolní i horní mez a vrací -1, aniž by sáhl na mřížku

Přidáváte-li do téhož reportu i počítaná pole nebo počítané položky, přečtěte si nejdřív stavbu a obnovu kontingenčních tabulek s počítanými poli v HotXLS a o pravidlech XML na úrovni polí, která brání Excelu soubor opravovat, píše validita schématu kontingenčního pole XLSX v Delphi

FAQ

Může datové pole HotXLS použít ShowDataAs a ExtendedShowDataAs dohromady?

Ne. TXLSPivotDataField.ShowDataAs a ExtendedShowDataAs se vylučují; při obou nastavených vrátí MaterializeNativePivotTable -1 a předchozí výstup nechá beze změny

Proč se moje kontingenční pořadí po obnově v Excelu proměnilo v součet?

Rozšiřující URI byl nejspíš přepsán s jinou velikostí písmen. Excel páruje {E15A36E0-9728-4e99-A89B-3F7291B0FE68} citlivě na velikost písmen a po RefreshTable spadne zpět na obyčejnou agregaci, když se nesejde

Přeskakuje kontingenční pořadí HotXLS čísla po shodě?

Ne. HotXLS následuje dense kontingenční pořadí Excelu, takže 30, 30 a 20 dostanou pořadí 1, 1 a 2

Podpora Show Values As, nativní materializace kontingencí a zbytek funkční sady XLSX a XLS jsou v HotXLS Delphi spreadsheet component pro Delphi a C++Builder