Excel kimutatások (Pivot Table) készítése és frissítése Delphiben a HotXLS segítségével

A HotXLS natív XLSX kimutatásokat (pivot táblákat) épít fel és frissít Delphiből és C++Builderből anélkül, hogy Excel telepítésére vagy COM automatizációra lenne szükség a gépen. Meghívja az AddPivotTable metódust egy forrástartománnyal, mezőket helyez el a sor-, oszlop-, oldal- és adat-tengelyekre, számított elemeket vagy százalékos megjelenítést rétegez rá, a komponens pedig megírja azokat a pivotCacheDefinition és pivotTableDefinition részeket, amelyeket az Excel élő, frissíthető kimutatásként nyit meg

A forgatókönyv, amiért megéri ezzel foglalkozni, egy jelentéskészítő szerver. Éjszakánként több száz munkafüzetet generál, amelyek mindegyike egy-egy fiókot összegző kimutatást hordoz, majd a következő hónapban a számok elmozdulnak, és minden fájlnak tükröznie kell az új forrássorokat. Az Excel vezérlése Windows szolgáltatásból törékeny, és szerveres használatra nem is licencelt, a kimutatás XML-jének kézi megírása pedig egy soha véget nem érő specifikáció-régészeti projekt. A HotXLS e két zsákutca között helyezkedik el: egy típusos objektummodell az OOXML kimutatás-részek felett, így ugyanaz a Pascal kód, amely feltölti a cellákat, deklarálja a kimutatást és újraépíti annak gyorsítótárát is ugyanabban a folyamatban

Hogyan hozható létre kimutatás Delphiben Excel nélkül?

Egyetlen hívással létrehozhat egyet, majd mezőket helyezhet el a tengelyeken. Az AddPivotTable átveszi a forrástartományt A1 jelöléssel, a céllcellát, ahová a táblázat rögzül, valamint egy nevet; elemzi a tartományt, átvizsgál minden oszlopot az adattípusának következtetésére, felépít egy kimutatás-gyorsítótárat (vagy újrahasznál egyet, amely már ehhez a tartományhoz van kötve), és egy olyan TXLSPivotTable-t ad vissza, amelynek mezői mind a tengelyen kívül indulnak. Innen a kényelmi metódusok, mint az AddRowField, AddColumnField, AddPageField és AddDataFieldByName huzalozzák be az elrendezést, és minden adatmező a TXLSPivotAggregation felsorolás tizenegy összesítésének egyikét veszi fel (xlpaSum, xlpaCount, xlpaAverage, xlpaMax, xlpaMin, xlpaProduct, xlpaCountNums, xlpaStdDev, xlpaStdDevP, xlpaVar, xlpaVarP)

uses
  lxHandleX, lxPivot;

var
  Book : TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Pivot: TXLSPivotTable;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('sales.xlsx');
    Sheet := Book.Sheets[1];                 // report sheet (1-based in the XLSX engine)

    // Source A1:E500 on the 'Data' sheet; anchor the pivot at row 3, col 1.
    Pivot := Sheet.AddPivotTable('Data!$A$1:$E$500', 3, 1, 'SalesByRegion');
    if Pivot <> nil then
    begin
      Pivot.AddRowField('Region');
      Pivot.AddColumnField('Quarter');
      Pivot.AddPageField('Year');
      Pivot.AddDataFieldByName('Revenue', xlpaSum);
      Pivot.AddDataFieldByName('Units', xlpaAverage);
      Book.SaveAs('sales-pivot.xlsx');
    end;
  finally
    Book.Free;
  end;
end;

Miért két különálló rész a gyorsítótár és a táblázat?

A kimutatás valójában két olyan objektum (lelet), amelyek egymásra hivatkoznak, és a felosztás megértése teszi tisztába a többi részt. A pivotCacheDefinition az adat-pillanatkép: egy worksheetSource, amely a forrástartományra mutat, és oszloponként egy-egy cacheField, amely az adott oszlop egyedi értékeit (a sharedItems-t) és a származtatott határokat tartalmazza. A pivotTableDefinition a nézet: melyik gyorsítótár-mező melyik tengelyen helyezkedik el, az adatmezők és azok összesítései, az elrendezési kapcsolók. A nézet a cacheId segítségével kapcsolódik a gyorsítótárhoz a munkafüzet pivotCaches kapcsolatán keresztül, pontosan úgy, ahogyan az ECMA-376 Part 1 §18.10 és a [MS-XLSX] előírja

Ez a indirekció nem bürokrácia, hanem két dolgot tesz lehetővé. Egyetlen gyorsítótár több táblázatot is kiszolgálhat, így a gyorsítótár egyszeri frissítése frissít minden belőle rajzolt nézetet. A gyorsítótár ráadásul rekordonkénti indextáblával tárolja az egyes oszlopok értékeit deduplikálva a nyers rács helyett, ezért jelent a kimutatás felépítése a forrás átvizsgálását, nem pedig a cellák másolását. A HotXLS mindkét motor esetében ugyanígy modellezi ezt, így a fenti kód szinte teljesen megegyezik a klasszikus útvonallal, amelyet a klasszikus .xls kimutatások mögötti bináris SX-rekord elrendezés mellett dokumentáltunk. Ha a forrás egy másik munkalapon él, vagy névvel hivatkozik rá, a tartomány előtagja és a definiált nevek és lapok közötti hivatkozások a szokásos A1 szabályokat követik, és az idézőjeles munkalapnevek is megengedettek a szóközöket tartalmazó nevekhez

A számított elemek nem számított mezők

Ez a három kifejezés három különböző dolgot nevez meg, és összekeverésük a klasszikus kimutatás-hiba. A számított elem (calculated item) egyetlen mezőn belül él, és a mező saját elemeit kombinálja név szerint, így például egy Region mezőn belül definiálhat egy szintetikus CoreMarkets sort, amely egyenlő a North és a South összegével; a HotXLS ezt a TXLSPivotField.AddCalculatedItem metóduson keresztül teszi elérhetővé, és egy <calculatedItem> elemet ír a mező <calculatedItems> listája alá. A számított mező (calculated field) más: ez egy új érték, amely más oszlopokból származik, mint a Margin a Revenue-ből és a Cost-ból, és képletként hordozza egy gyorsítótár-mezőn a TXLSPivotCacheField.Formula-n keresztül. A számított tag (calculated member), amelyet a TXLSPivotTable.AddCalculatedMember segítségével adunk hozzá, egy táblaszintű egyéni tag, amely mérőszámként (data tagtípus) vagy dimenziótagként működhet, főként az OLAP-alakú kimutatásoknál fontos

var
  Region: TXLSPivotField;
  Member: TXLSPivotCalculatedMember;
begin
  // A calculated ITEM combines items of a single field by name.
  Region := Pivot.AddRowField('Region');
  Region.AddCalculatedItem('CoreMarkets', '=North+South');

  // A calculated MEMBER is declared at the table level. Member type
  // 'data' marks it as a measure; an empty type is a dimension member.
  Member := Pivot.AddCalculatedMember('AvgTicket', '=Revenue/Units');
  Member.MemberType := 'data';
end;

Egy őszinte korlát mindháromra vonatkozik. A HotXLS a képlet szövegét a definíciós XML-be írja ki; nem értékeli ki azt. Az Excel számítja ki a számított elemet, mezőt vagy tagot a fájl megnyitásakor, ugyanúgy, ahogy a rács minden összesítését kiszámítja. A HotXLS az utasításokat írja le, nem az eredményeket, így az Ön által megadott képleteknek érvényes kimutatás-képleteknek kell lenniük az Excel saját nyelvjárásában, úgy hivatkozva a mező-- és elemnevekre, ahogy az Excel elvárja

Hogyan jeleníthetők meg az értékek a végösszeg százalékában?

Az értékek megjelenítési módját az adatmezőn állítja be, ahelyett, hogy saját maga alakítaná át a számokat. A TXLSPivotDataField.ShowDataAs a TXLSPivotShowDataAs felsorolást veszi fel, amely az OOXML ST_ShowDataAs értékeit tükrözi: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal és xlpsdaIndex. Gyakori trükk, hogy ugyanazt a forrásoszlopot kétszer helyezik el az adattengelyen, egyszer nyers összegként, egyszer pedig a végösszeg részesedéseként, így a jelentés a számot és annak súlyát is mutatja

var
  Rev, Share: TXLSPivotDataField;
begin
  Rev := Pivot.AddDataFieldByName('Revenue', xlpaSum);

  Share := Pivot.AddDataFieldByName('Revenue', xlpaSum);
  Share.DisplayName := 'Share of total';
  Share.ShowDataAs  := xlpsdaPercentOfTotal;

  // Item-relative modes need a base to compare against. A running total
  // down 'Quarter' (cache-field index 3) would read:
  //   Share.ShowDataAs := xlpsdaRunTotal;
  //   Share.BaseField  := 3;         // cache-field index the transform runs over
  //   Share.BaseItem   := $7FFD;     // $7FFD = "(All)"
end;

Az xlpsdaPercentOfTotal-nak nincs szüksége bázisra, mert a végösszeghez viszonyít, de az elem-relatív módoknak igen. Az xlpsdaDifference, xlpsdaPercentDiff és xlpsdaRunTotal megköveteli a BaseField-et (a gyorsítótár-mező indexét, amelyen az összehasonlítás fut) és az elemhez rögzített formákhoz egy BaseItem indexet, ahol a $7FFD az (All) sentinel helyőrzőt jelöli. A HotXLS ezeket <dataField showDataAs="percentOfTotal" baseField="N" baseItem="M"/> attribútumokként írja ki, és a számított képletekhez hasonlóan az aritmetikát az Excelre bízza

Dátumok és számok csoportosítása, valamint oldalszintű szűrők

A csoportosítás a gyorsítótár-mezőn van konfigurálva, nem a kimutatás-mezőn, mert ez megváltoztatja a forrástartomány vödrözését (bucketed). Állítsa be a HasGroup := True értéket a Cache.FindFieldByName által visszaadott gyorsítótár-mezőn, majd válasszon a numerikus tartomány gombjai (GroupStartNum, GroupEndNum, GroupInterval) vagy a dátumhierarchia jelzők közül (GroupByDate a GroupMonths, GroupQuarters, GroupYears, valamint a GroupStartDate / GroupEndDate tartománnyal). A HotXLS kibocsátja a megfelelő <fieldGroup><rangePr groupBy="months"/> elemet, így a hónapok szerint vagy 1000 széles numerikus sáv szerint csoportosított mező úgy nyílik meg, ahogyan az Excel csoportosította volna

Az oldalmezők (page fields) a táblázat feletti szűrő legördülő menük. Az AddPageField elhelyez egy mezőt az oldaltengelyen, a PageItemIndex pedig előre kiválaszt egyetlen gyorsítótár-elemet, alapértelmezetten xlPageItemAll ($7FFD, azaz (All)) értékre. Ha engedélyezni szeretné a felhasználónak több elem egyszerre történő kijelölését, állítsa a MultipleItemSelectionAllowed := True értéket, amelyet a HotXLS <pivotField multipleItemSelectionAllowed="1"/> formában ír ki. A kézi kiválasztáson túli kritériumokhoz minden mező egy típusos Filters gyűjteményt hordoz, amely lefedi az OOXML ST_FilterType családokat – darab, százalék, összeg, felirat, érték és dátum szűrőket –, ahol minden bejegyzés párosít egy szűrőtípust az összehasonlító értékeivel

Hogyan frissíthető a kimutatás gyorsítótára, ha a forrás megváltozik?

A TXLSXWorkbook RefreshPivotCache metódusa újra átvizsgálja a forrástartományt, és helyben újraépíti a gyorsítótárat, amire egy kötegelt feldolgozónak szüksége van a mögöttes sorok szerkesztése után. Adja át a gyorsítótár azonosítóját (cache id), és a metódus újraolvas minden cellát a forrástartományban (fejléc az első sorban, adatok a másodiktól kezdve), újraépíti az egyes mezők megosztott elemeinek tartományát az értékek deduplikálásával és a típusonkénti határok újbóli származtatásával, valamint újraírja a rekordonkénti elemindexeket. Sikeres végrehajtás esetén 1, ismeretlen gyorsítótár-azonosító vagy hiányzó forráslap esetén pedig -1 értékkel tér vissza

var
  Rc: Integer;
begin
  // ...the source data has changed since the pivot was built...
  Sheet := Book.Sheets[1];
  Sheet.Cells[2, 5].Value := 128000;             // a corrected Revenue figure

  // Rebuild the cache's shared items and record indices from the source.
  Rc := Book.RefreshPivotCache(Pivot.CacheId);   // 1 = refreshed, -1 = cache/source missing
  if Rc = 1 then
    Book.SaveAs('sales-pivot-refreshed.xlsx');
end;

A szemantikai határt érdemes itt nyíltan kimondani. Mielőtt ez a metódus létezett volna, a HotXLS a refreshOnLoad="1" jelzőre támaszkodott, és a frissítési munkát az Excelre bízta a következő megnyitáskor, ami rendben van, ha ember nyitja meg a fájlt, de haszontalan egy olyan headless feldolgozóban, amelynek pontos adatokat kell átadnia. A RefreshPivotCache a gyorsítótárat a folyamaton belül teszi naprakésszé, és mivel a táblázatok azonosító alapján kötődnek a gyorsítótárhoz, az abból a gyorsítótárból kirajzolt minden kimutatás látja a frissítést. Amit nem csinál meg, az a látható rács elrendezése vagy összesítése – az Excel továbbra is újraszámítja a renderelt táblázatot a frissített gyorsítótárból a fájl megnyitásakor

Amit a HotXLS ír ki, és amit az Excel számol ki

Tartsa szem előtt a munkamegosztást, és semmi sem fogja meglepni. A HotXLS egy definíció-író: előállítja a pivotCacheDefinition-t, a gyorsítótár-rekordokat és a pivotTableDefinition-t, tengelyekkel, összesítésekkel, számított elemekkel és tagokkal, érték-megjelenítésekkel, csoportosítással és szűrőkkel kiegészítve. Az Excel a számológép: megnyitáskor létrehozza a csoportvödröket, kiértékeli a számított képleteket, alkalmazza a showDataAs transzformációkat, és összesíti a törzset. A felhasználó által látott értékek az Excel értékei, amelyeket a HotXLS által leírt utasításokból állított elő, ezért kell minden képletnek és bázisindexnek helyesnek lennie az írás idején, ahelyett, hogy renderelési időben ellenőriznék őket

Két korlátozást érdemes tudni, mielőtt e funkció köré tervezne. Az AddPivotTable egy téglalap alakú A1 tartományt elemez opcionális lapelőtaggal; a megnevezett tartományok (named range) és a külső munkafüzet-források kívül esnek azon, amit az építő felold, bár az Excel által készített fájlból beolvasott gyorsítótárak megőrzik a megnevezett tartomány-forrásukat az oda-vissza út során. Ezenkívül a lemezről beolvasott, Excel által létrehozott kimutatások mentéskor bájtról bájtra visszaíródnak, így az itt leírt típusos szerkesztések tisztán érvényesülnek az Ön által kódban épített kimutatásokra, míg a meglévő kimutatások veszteségmentesek maradnak. A jelentések bemeneti oldalához – az ellenőrzött cellákhoz és a szűrt táblázatokhoz, amelyeket a kimutatás összesít – lásd az adatellenőrzés, AutoFilter és strukturált táblázatok cikket

Az itt bemutatott kimutatás-modell a standard HotXLS Delphi Excel Component része Delphihez és C++Builderhez, amely ugyanabból az objektummodellből olvassa és írja az XLSX típusos kimutatás-részeit és a klasszikus BIFF8 rekordokat is