LlosLab blog o razvoju softveraInženjerske bilješke za programere PDF-a, Delphija i dokumentacijskog softvera

Tehnički članak

Izrada i osvježavanje Excel izvedenih tablica u Delphiju pomoću HotXLS-a

HotXLS Component

HotXLS izrađuje i osvježava izvorne XLSX izvedene tablice iz Delphija i C++Buildera bez potrebe za instalacijom Excela i bez COM automatizacije na računalu. Pozivate AddPivotTable s izvornim rasponom, postavljate polja na osi redaka, stupaca, stranica i podataka, dodajete izračunate stavke ili prikaz postotka od ukupnog iznosa, a komponenta zapisuje dijelove pivotCacheDefinition i pivotTableDefinition koje Excel otvara kao aktivnu izvedenu tablicu spremnu za osvježavanje

Scenarij zbog kojeg se ovo isplati je poslužitelj za izvješćivanje. Svake noći generirate stotine radnih knjiga, od kojih svaka sadrži izvedenu tablicu koja sažima jedan račun, a sljedeći mjesec se brojevi promijene i svaka datoteka mora odražavati nove izvorne retke. Pokretanje Excela iz Windows servisa je nestabilno i nije licencirano za poslužiteljsku upotrebu, a ručno pisanje XML-a za izvedenu tablicu je projekt specifikacijske arheologije kojem nema kraja. HotXLS se nalazi između ova dva slijepa kraja: tipizirani objektni model preko OOXML dijelova izvedene tablice, tako da isti Pascal koji popunjava ćelije također deklarira izvedenu tablicu i ponovno gradi njezinu privremenu memoriju (cache) u istom procesu

Kako stvoriti izvedenu tablicu u Delphiju bez Excela?

Stvarate je jednim pozivom, a zatim postavljate polja na osi. AddPivotTable prima izvorni raspon u A1 bilježenju, odredišnu ćeliju na kojoj se tablica sidri i naziv; raščlanjuje raspon, skenira svaki stupac kako bi zaključio njegov tip podataka, gradi priručnu memoriju izvedene tablice (ili ponovno koristi onu koja je već povezana s istim rasponom) i vraća TXLSPivotTable čija polja sva počinju izvan osi. Odatle praktične metode AddRowField, AddColumnField, AddPageField i AddDataFieldByName povezuju raspored, a svako podatkovno polje prima jednu od jedanaest agregacija iz TXLSPivotAggregation popisa (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 priručna memorija i tablica dva odvojena dijela

Izvedena tablica zapravo se sastoji od dvaju artefakata koji se međusobno referenciraju, a razumijevanje te podjele ključ je za razumijevanje svega ostalog. pivotCacheDefinition je snimka podataka: worksheetSource koji pokazuje na izvorni raspon i jedno polje cacheField po stupcu koje sadrži jedinstvene vrijednosti tog stupca (njegove sharedItems) plus izvedene granice. pivotTableDefinition je prikaz (view): koje polje priručne memorije leži na kojoj osi, podatkovna polja i njihove agregacije, te prekidači rasporeda. Prikaz se povezuje s priručnom memorijom preko cacheId kroz vezu pivotCaches radne knjige, točno onako kako to propisuju ECMA-376 Part 1 §18.10 i [MS-XLSX] standardi

To preusmjeravanje nije birokracija, ono donosi dvije prednosti. Jedna priručna memorija može podržavati nekoliko tablica, pa jedno osvježavanje priručne memorije ažurira svaki prikaz izveden iz nje. Osim toga, priručna memorija pohranjuje vrijednosti svakog stupca uklanjanjem duplikata pomoću tablice indeksa po zapisu umjesto sirove mreže, zbog čega izgradnja izvedene tablice znači skeniranje izvora, a ne kopiranje ćelija. HotXLS to modelira na isti način za oba pokretača, pa je gornji kôd gotovo identičan klasičnoj putanji dokumentiranoj uz binarni izgled SX-zapisa iza klasičnih .xls izvedenih tablica. Ako se vaš izvor nalazi na drugom listu ili mu se obraćate mutu imena, prefiks raspona i definirana imena i reference među listovima slijede uobičajena A1 pravila, s dopuštenim navodnicima za nazive listova koji sadrže razmake

Izračunate stavke nisu isto što i izračunata polja

Ova tri pojma označavaju tri različite stvari, a njihovo miješanje klasična je pogreška pri radu s izvedenim tablicama. Izračunata stavka živi unutar jednog polja i kombinira vlastite stavke tog polja po imenu, pa unutar polja Regija možete definirati sintetički redak CoreMarkets uglavnom jednak zbroju Sjever plus Jug; HotXLS to izlaže kao TXLSPivotField.AddCalculatedItem i zapisuje <calculatedItem> ispod <calculatedItems> tog polja. Izračunato polje je drugačije: to je nova vrijednost izvedena iz drugih stupaca, poput marže iz prihoda i troškova, i prenosi se kao formula na polju priručne memorije kroz TXLSPivotCacheField.Formula. Izračunati član, dodan pomoću TXLSPivotTable.AddCalculatedMember, prilagođeni je član na razini tablice koji može djelovati kao mjera (tip člana data) ili član dimenzije, što je uglavnom zanimljivo za izvedene tablice u obliku OLAP-a

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 vrijedi za sva tri slučaja. HotXLS ispisuje tekst formule u XML definicije; on ga ne procjenjuje. Excel izračunava izračunatu stavku, polje ili člana kada otvori datoteku, na isti način na koji izračunava svaki agregat u mreži. HotXLS zapisuje upute, a ne rezultate, pa formule koje dajete moraju biti valjane formule izvedene tablice u Excelovom dijalektu, referencirajući nazive polja i stavki onako kako bi to Excel činio

Kako prikazati vrijednosti kao postotak od ukupnog iznosa?

Postavljate način prikaza vrijednosti na podatkovnom polju umjesto da sami transformirate brojeve. TXLSPivotDataField.ShowDataAs prima popis TXLSPivotShowDataAs, koji odražava vrijednosti OOXML-a ST_ShowDataAs: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal i xlpsdaIndex. Uobičajeni trik je postaviti isti izvorni stupac na os podataka dvaput, jednom kao sirovi zbroj, a drugi put kao udio u ukupnom zbroju, tako da izvješće 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;

Grupiranje datuma i brojeva, te filtri polja stranice

Grupiranje se konfigurira na polju priručne memorije, a ne na polju izvedene tablice, jer ono mijenja način na koji se izvorni domena dijeli u grupe. Postavite HasGroup := True na polju priručne memorije koje vraća Cache.FindFieldByName, a zatim odaberite gumbe za numerički raspon (GroupStartNum, GroupEndNum, GroupInterval) or zastavice hijerarhije datuma (GroupByDate s GroupMonths, GroupQuarters, GroupYears i rasponom GroupStartDate / GroupEndDate). HotXLS ispušta odgovarajući element <fieldGroup><rangePr groupBy="months"/> tako da se polje grupirano po mjesecu ili po numeričkom pojasu širine 1000 otvara onako kako bi ga Excel grupirao

Polja stranice su padajući izbornici filtara iznad tablice. AddPageField postavlja polje na os stranice, a PageItemIndex unaprijed odabire jednu stavku priručne memorije, pri čemu je zadana vrijednost xlPageItemAll ($7FFD, što znači (Sve)). Da biste čitatelju omogućili označavanje nekoliko stavki odjednom, postavite MultipleItemSelectionAllowed := True, što HotXLS zapisuje kao <pivotField multipleItemSelectionAllowed="1"/>. Za kriterije izvan ručnog odabira, svako polje nosi tipiziranu zbirku Filters koja obuhvaća obitelji OOXML-a ST_FilterType — filtre broja, postotka, zbroja, naslova, vrijednosti i datuma, pri čemu svaki unos uparuje vrstu filtra s njegovim usporednim vrijednostima

Kako osvježiti priručnu memoriju izvedene tablice kada se izvor promijeni?

Metoda RefreshPivotCache na TXLSXWorkbook ponovno skenira izvorni raspon i obnavlja priručnu memoriju na licu mjesta, što je ono što je potrebno serijskom cjevovodu nakon što uredi redove u pozadini. Proslijedite ID priručne memorije i metoda ponovno čita svaku ćeliju u izvornom rasponu (zaglavlje u prvom redu, podatke od drugog), ponovno gradi domenu zajedničkih stavki svakog polja uklanjanjem duplikata i ponovnim izvođenjem granica po tipu, te ponovno zapisuje indekse stavki po zapisu. Vraća 1 u slučaju uspjeha i -1 kada je ID priručne memorije nepoznat ili 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;

Semantičku granicu ovdje vrijedi jasno istaknuti. Prije nego što je ova metoda postojala, HotXLS se oslanjao na zastavicu refreshOnLoad="1" i prepuštao sav posao osvježavanja Excelu pri sljedećem otvaranju, što je u redu kada će čovjek otvoriti datoteku, ali beskorisno u cjevovodu bez grafičkog sučelja koji mora predati točne podatke. RefreshPivotCache čini spremljenu priručnu memoriju ažurnom unutar samog procesa, a budući da se tablice povezuju s priručnom memorijom prema ID-ju, svaka izvedena tablica povučena iz te priručne memorije vidi to ažuriranje. Ono što metoda ne čini je raspoređivanje ili agregiranje vidljive mreže — Excel i dalje ponovno izračunava prikazanu tablicu iz osvježene priručne memorije kada se datoteka otvori

Što zapisuje HotXLS, a što izračunava Excel

Dvije granice vrijedi znati prije nego što počnete dizajnirati oko ove značajke. AddPivotTable raščlanjuje pravokutni A1 raspon s opcionalnim prefiksom lista; imenovani rasponi i izvori iz vanjskih radnih knjiga izvan su onoga što ovaj graditelj razrješava, iako priručne memorije pročitane iz datoteke koju je stvorio Excel zadržavaju svoj izvor imenovanog raspona tijekom dvosmjernog procesa. Osim toga, izvedene tablice stvorene u Excelu koje HotXLS čita s diska reproduciraju se bajt za bajt pri spremanju, pa se ovdje opisane tipizirane izmjene primjenjuju isključivo na izvedene tablice koje gradite u kodu, dok postojeće izvedene tablice ostaju netaknute. Za ulaznu stranu izvješća — provjerene ćelije i filtrirane tablice koje izvedena tablica sažima — pogledajte članak o provjeri podataka, AutoFilteru i strukturiranim tablicama

Model izvedene tablice prikazan ovdje dio je standardne komponente HotXLS Delphi Excel Component za Delphi i C++Builder, koja čita i zapisuje i XLSX tipizirane dijelove izvedene tablice i klasične BIFF8 zapise iz istog objektnog modela