Kreiranje i osvežavanje Excel izvedenih tabela (Pivot Tables) u Delphi-ju pomoću HotXLS-a

HotXLS kreira i osvežava izvorne XLSX izvedene tabele (pivot tables) iz Delphi-ja i C++Builder-a, bez instalacije Excela i bez COM automatizacije na računaru. Pozivate AddPivotTable sa izvornim opsegom, prevlačite polja na ose redova, kolona, stranica i podataka, dodajete kalkulisane stavke ili prikaz procenta od ukupnog zbira, a komponenta upisuje delove pivotCacheDefinition i pivotTableDefinition koje Excel otvara kao aktivnu izvedenu tabelu spremnu za osvežavanje

Scenario koji opravdava ovaj trud je server za izveštavanje. Generišete stotine radnih svezaka tokom noći, od kojih svaka sadrži izvedenu tabelu koja sumira jedan nalog, a sledećeg meseca se brojevi promene i svaka datoteka mora da odražava nove izvorne redove. Pokretanje Excela iz Windows servisa je nestabilno i nije licencirano za serversku upotrebu, a ručno pisanje pivot XML-a je projekat arheologije specifikacija koji nikada ne prestaje. HotXLS se nalazi između ta dva ćorsokaka: tipizirani objektni model preko OOXML pivot delova, tako da isti Pascal kod koji popunjava ćelije takođe deklariše izvedenu tabelu i ponovo gradi njen keš u istom procesu

Kako kreirati izvedenu tabelu (pivot table) u Delphi-ju bez Excela?

Kreirate je jednim pozivom, a zatim raspoređujete polja po osama. Metod AddPivotTable uzima izvorni opseg u A1 notaciji, ciljnu ćeliju na kojoj se tabela sidri i naziv; parsira opseg, skenira svaku kolonu da bi odredio tip podataka, gradi keš izvedene tabele (ili ponovo koristi onaj koji je već povezan sa istim opsegom) i vraća TXLSPivotTable čija polja na početku leže van osa. Odatle praktične metode AddRowField, AddColumnField, AddPageField i AddDataFieldByName povezuju raspored, a svako polje podataka uzima jednu od jedanaest agregacija iz enumeracije TXLSPivotAggregation (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;

Zašto su keš i tabela dva odvojena dela

Izvedena tabela se zapravo sastoji od dva artefakta koji referenciraju jedan drugog, i razumevanje te podele je ključno. pivotCacheDefinition je snimak podataka: worksheetSource koji ukazuje na izvorni opseg i po jedno polje cacheField za svaku kolonu koja drži jedinstvene vrednosti te kolone (njene sharedItems) plus izvedene granice. pivotTableDefinition je prikaz (view): koje polje keša leži na kojoj osi, polja podataka i njihove agregacije, prekidači rasporeda. Prikaz se povezuje sa kešom preko cacheId identifikatora kroz relaciju pivotCaches radne sveske, tačno onako kako to nalažu ECMA-376 Deo 1 §18.10 i [MS-XLSX] standardi

To skretanje nije birokratija, već donosi dve stvari. Jedan keš može podržavati nekoliko tabela, tako da jedno osvežavanje keša ažurira svaki prikaz koji je iz njega izvučen. Takođe, keš čuva vrednosti svake kolone uklanjajući duplikate pomoću indeksne tabele po zapisu umesto same mreže, zbog čega kreiranje izvedene tabele podrazumeva skeniranje izvora, a ne kopiranje ćelija. HotXLS modelira ovo na isti način za oba motora, tako da je gornji kod skoro identičan klasičnoj putanji dokumentovanoj uz binarni raspored SX-zapisa iza klasičnih .xls izvedenih tabela. Ako vaš izvor živi na drugom listu ili mu pristupate preko naziva, prefiks opsega i definisani nazivi i unakrsne reference listova prate uobičajena pravila A1, pri čemu su dozvoljeni nazivi listova pod navodnicima za nazive koji sadrže razmake

Kalkulisane stavke nisu kalkulisana polja

Ova tri pojma označavaju tri različite stvari, a njihovo mešanje je klasična greška kod izvedenih tabela. Kalkulisana stavka (calculated item) živi unutar jednog polja i kombinuje sopstvene stavke tog polja po nazivu, tako da unutar polja Region možete definisati sintetički red CoreMarkets jednak zbiru North i South; HotXLS je izlaže kao TXLSPivotField.AddCalculatedItem i upisuje <calculatedItem> pod <calculatedItems> tog polja. Kalkulisano polje (calculated field) je drugačije: to je nova vrednost izvedena iz drugih kolona, kao što je Margin iz Revenue i Cost, i nosi se kao formula na polju keša preko TXLSPivotCacheField.Formula. Kalkulisani član (calculated member), dodat pomoću TXLSPivotTable.AddCalculatedMember, je prilagođeni član na nivou tabele koji može delovati kao mera (tip člana data) ili član dimenzije, što je uglavnom interesantno za izvedene tabele OLAP oblika

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;

Jedna stvarna granica važi za sva tri slučaja. HotXLS upisuje tekst formule u XML definiciju; ona je ne procenjuje. Excel izračunava kalkulisanu stavku, polje ili člana kada otvori datoteku, na isti način na koji izračunava svaki agregat u mreži. HotXLS upisuje uputstva, a ne rezultate, tako da formule koje navedete moraju biti važeće pivot formule u Excelovom dijalektu, referencirajući nazive polja i stavki onako kako bi to Excel radio

Kako prikazati vrednosti kao procenat od ukupnog zbira?

Podešavate režim prikaza vrednosti na polju podataka umesto da sami transformišete brojeve. Svojstvo TXLSPivotDataField.ShowDataAs uzima enumeraciju TXLSPivotShowDataAs, koja preslikava OOXML vrednosti ST_ShowDataAs: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal i xlpsdaIndex. Uobičajen trik je da se ista izvorna kolona postavi na osu podataka dvaput, jednom kao sirovi zbir (sum), a drugi put kao udeo u ukupnom zbiru, tako da izveštaj prikazuje i broj i njegovu težinu

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;

Režim xlpsdaPercentOfTotal ne zahteva bazu jer se odnosi na krajnji ukupni zbir, ali režimi koji se odnose na stavke (item-relative) zahtevaju. xlpsdaDifference, xlpsdaPercentDiff i xlpsdaRunTotal zahtevaju BaseField (indeks polja keša preko kojeg se vrši poređenje) i, za oblike sidrene za stavku, BaseItem indeks, gde $7FFD stoji za stražara (All). HotXLS upisuje ove kao atribute <dataField showDataAs="percentOfTotal" baseField="N" baseItem="M"/> i, kao i sa kalkulisanim formulama, prepušta aritmetiku Excelu

Grupisanje datuma i brojeva, i filteri polja stranice

Grupisanje se konfiguriše na polju keša, a ne na polju izvedene tabele, jer ono menja način na koji se izvorni domen deli u kategorije (buckets). Postavite HasGroup := True na polju keša koje vraća Cache.FindFieldByName, a zatim izaberite ili opcije za numerički opseg (GroupStartNum, GroupEndNum, GroupInterval) ili zastavice za hijerarhiju datuma (GroupByDate sa GroupMonths, GroupQuarters, GroupYears i rasponom GroupStartDate / GroupEndDate). HotXLS emituje odgovarajući <fieldGroup><rangePr groupBy="months"/> element tako da se polje grupisano po mesecu ili numeričkom opsegu širine 1000 otvara na način na koji bi ga Excel grupisao

Polja stranice (Page fields) su padajući meniji za filtriranje iznad tabele. Metod AddPageField postavlja polje na osu stranice, a PageItemIndex unapred bira jednu stavku keša, što je podrazumevano xlPageItemAll ($7FFD, što znači (All)). To let a reader tick several items at once, set MultipleItemSelectionAllowed := True, which HotXLS writes as <pivotField multipleItemSelectionAllowed="1"/>. For criteria beyond a manual pick, every field carries a typed Filters collection spanning the OOXML ST_FilterType families — count, percent, sum, caption, value, and date filters — each entry pairing a filter type with its comparison values

Kako osvežiti keš izvedene tabele kada se izvor se promeni?

Metod RefreshPivotCache u klasi TXLSXWorkbook ponovo skenira izvorni opseg i ponovo gradi keš na licu mesta, što je upravo ono što je potrebno grupnom ciklusu nakon što izmeni redove u pozadini. Prosledite identifikator keša (cache id), i metod ponovo čita svaku ćeliju u izvornom opsegu (zaglavlje na prvom redu, podatke od drugog), ponovo gradi domen deljenih stavki svakog polja uklanjanjem duplikata i ponovnim izvođenjem granica po tipu, i ponovo upisuje indekse stavki po zapisu. Vraća 1 u slučaju uspeha i -1 kada je identifikator keša nepoznat ili kada izvorna stranica nedostaje

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;

Šta HotXLS upisuje, a šta Excel izračunava

Držite podelu rada na umu i ništa vas neće iznenaditi. HotXLS je pisac definicija: on proizvodi pivotCacheDefinition, zapise keša i pivotTableDefinition, kompletno sa osama, agregacijama, kalkulisanim stavkama i članovima, prikazima vrednosti, grupisanjem i filterima. Excel je kalkulator: prilikom otvaranja, on materijalizuje grupisane kategorije, procenjuje kalkulisane formule, primenjuje showDataAs transformacije i agregira telo. Vrednosti koje korisnik vidi su Excelove, proizvedene iz uputstava koja je HotXLS napisao, zbog čega svaka formula i osnovni indeks moraju biti tačni u vreme pisanja, a ne proveravani u vreme renderovanja

Dva ograničenja vredi znati pre nego što dizajnirate rešenje oko ove funkcije. Metod AddPivotTable parsira pravougaoni A1 opseg sa opcionim prefiksom lista; imenovani opsezi i izvori iz spoljnih radnih svezaka su izvan onoga što graditelj razrešava, mada keševi pročitani iz datoteke koju je kreirao Excel čuvaju svoj izvor imenovanog opsega pri kružnom prenosu. Takođe, izvedene tabele kreirane u Excelu koje HotXLS čita sa diska se verno kopiraju bajt-za-bajt pri čuvanju, tako da se tipizirane izmene opisane ovde primenjuju čisto na izvedene tabele koje gradite u kodu, dok postojeće ostaju bez gubitaka. Za ulaznu stranu izveštaja — validirane ćelije i filtrirane tabele koje izvedena tabela sumira — pogledajte članak o validaciji podataka, AutoFilter-u i strukturiranim tabelama

Pivot model prikazan ovde je deo standardne komponente HotXLS Delphi Excel Component za Delphi i C++Builder, koja čita i piše i XLSX tipizirane pivot delove i klasične BIFF8 zapise iz istog objektnog modela