Construirea și actualizarea tabelelor pivot Excel în Delphi cu HotXLS

HotXLS construiește și actualizează tabele pivot native XLSX din Delphi și C++Builder fără a fi necesară instalarea Excel sau automatizarea COM pe mașină. Apelați AddPivotTable cu un interval sursă, plasați câmpuri pe axele de rânduri, coloane, pagini și date, adăugați elemente calculate sau o afișare procentuală din total, iar componenta scrie secțiunile pivotCacheDefinition și pivotTableDefinition pe care Excel le deshechide ca un tabel pivot activ și actualizabil

Scenariul care face ca acest efort să merite este cel al unui server de raportare. Generați sute de registre de calcul pe noapte, fiecare conținând un tabel pivot care rezumă un cont, iar luna următoare cifrele se modifică și fiecare fișier trebuie să reflecte noile rânduri sursă. Rularea Excel dintr-un serviciu Windows este instabilă și neautorizată pentru utilizare pe server, iar scrierea manuală a XML-ului pentru tabele pivot este un proiect de arheologie a specificațiilor care nu se termină niciodată. HotXLS se situează între aceste două căi blocate: oferă un model de obiecte tipizat peste componentele de pivot OOXML, astfel încât același cod Pascal care completează celulele declară, de asemenea, pivotul și îi reconstruiește memoria cache în cadrul aceluiași proces

Cum se creează un tabel pivot în Delphi fără Excel?

Creați un tabel pivot printr-un singur apel și apoi plasați câmpurile pe axe. AddPivotTable primește intervalul sursă în notație A1, celula de destinație unde se ancorează tabelul și un nume; analizează intervalul, scanează fiecare coloană pentru a deduce tipul de date, construiește un cache pivot (sau reutilizează unul deja asociat aceluiași interval) și returnează un obiect TXLSPivotTable ale cărui câmpuri pornesc inițial în afara axelor. De acolo, metodele de asistență AddRowField, AddColumnField, AddPageField și AddDataFieldByName configurează structura, iar fiecare câmp de date primește una dintre cele unsprezece agregări din enumerarea 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;

De ce memoria cache și tabelul sunt două părți separate

Un tabel pivot este compus de fapt din două elemente care se referă unul la celălalt, iar înțelegerea acestei separări este esențială. Secțiunea pivotCacheDefinition este instantaneul datelor: un worksheetSource care indică intervalul sursă și câte un cacheField pentru fiecare coloană care stochează valorile distincte ale acelei coloane (proprietatea sharedItems) plus limitele derivate. Secțiunea pivotTableDefinition reprezintă vizualizarea (view): ce câmp de cache se află pe ce axă, câmpurile de date și agregările lor, setările de layout. Vizualizarea se asociază la cache prin cacheId prin relația pivotCaches a registrului de calcul, exact așa cum prevăd specificațiile ECMA-376 Partea 1 §18.10 și [MS-XLSX]

Această redirecționare nu este o simplă formalitate, ci oferă două avantaje. Un singur cache poate susține mai multe tabele, astfel încât actualizarea o singură dată a memoriei cache actualizează fiecare vizualizare derivată din aceasta. De asemenea, cache-ul stochează valorile fiecărei coloane deduplicate cu un tabel de index per înregistrare în loc de grila brută, motiv pentru care crearea unui pivot implică scanarea sursei, nu copierea celulelor. HotXLS modelează acest proces în același mod pentru ambele motoare, astfel încât codul de mai sus este aproape identic cu calea clasică descrisă alături de structura binară a înregistrărilor SX din spatele tabelelor pivot clasice .xls. Dacă sursa dvs. se află pe o altă foaie sau o adresați printr-un nume, prefixul intervalului și numele definite și referințele între foi urmează regulile obișnuite A1, fiind permise numele de foi ghilimate pentru denumirile care conțin spații

Elementele calculate nu sunt câmpuri calculate

Acești trei termeni desemnează trei lucruri diferite, iar confundarea lor este greșeala clasică în tabelele pivot. Un element calculat (calculated item) există în interiorul unui singur câmp și combină elementele proprii ale acelui câmp după nume, astfel încât în cadrul unui câmp Region puteți defini un rând sintetic CoreMarkets egal cu North plus South; HotXLS îl expune prin TXLSPivotField.AddCalculatedItem și scrie un element <calculatedItem> sub secțiunea <calculatedItems> a acelui câmp. Câmpurile calculate (calculated fields) sunt diferite: reprezintă o valoare nouă derivată din alte coloane, cum ar fi Margin din Revenue și Cost, și sunt stocate ca formule în câmpurile de cache prin intermediul TXLSPivotCacheField.Formula. Un membru calculat (calculated member), adăugat cu TXLSPivotTable.AddCalculatedMember, este un membru personalizat la nivel de tabel care poate acționa ca o măsură (tipul de membru data) sau ca membru de dimensiune, fiind util în special pentru tabelele pivot structurate OLAP

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;

O limită certă se aplică tuturor celor trei cazuri. HotXLS scrie textul formulei în XML-ul de definiție; nu îl evaluează. Excel calculează elementul, câmpul sau membrul calculat când deschide fișierul, în același mod în care calculează fiecare agregat din grilă. HotXLS scrie instrucțiunile, nu rezultatele, astfel încât formulele pe care le furnizați trebuie să fie formule pivot valide în dialectul specific Excel, făcând referire la numele de câmpuri și elemente așa cum o face Excel

Cum se afișează valorile ca procent din total?

Setați modul de afișare a valorilor în câmpul de date, în loc să transformați singuri numerele. TXLSPivotDataField.ShowDataAs primește enumerarea TXLSPivotShowDataAs, care reflectă valorile OOXML ST_ShowDataAs: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal și xlpsdaIndex. Un truc obișnuit este să plasați aceeași coloană sursă pe axa de date de două ori, o dată ca sumă brută și o dată ca procent din totalul general, astfel încât raportul să afișeze atât numărul, cât și ponderea sa

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;

Modul xlpsdaPercentOfTotal nu necesită o bază deoarece este raportat la totalul general, dar modurile relative la element au nevoie de una. Opțiunile xlpsdaDifference, xlpsdaPercentDiff și xlpsdaRunTotal necesită BaseField (indexul câmpului de cache peste care se realizează compararea) și, pentru formele ancorate la element, un index BaseItem, unde $7FFD reprezintă marcatorul (All). HotXLS scrie aceste valori ca atribute ale elementului <dataField showDataAs="percentOfTotal" baseField="N" baseItem="M"/> și, la fel ca în cazul formulelor calculate, lasă operațiunile aritmetice în seama Excel

Gruparea datelor și a numerelor și filtrele pentru câmpurile de pagină

Gruparea este configurată în câmpul de cache, nu în câmpul de pivot, deoarece modifică modul în care este împărțit domeniul sursă. Setați HasGroup := True pe câmpul de cache returnat de Cache.FindFieldByName, apoi alegeți parametrii pentru intervalul numeric (GroupStartNum, GroupEndNum, GroupInterval) sau opțiunile pentru ierarhia de date (GroupByDate cu GroupMonths, GroupQuarters, GroupYears și un interval GroupStartDate / GroupEndDate). HotXLS emite elementul corespunzător <fieldGroup><rangePr groupBy="months"/>, astfel încât un câmp grupat pe luni sau pe un interval numeric de 1000 se va deschide exact așa cum l-ar fi grupat Excel

Câmpurile de pagină (page fields) sunt listele derulante de filtrare situate deasupra tabelului. AddPageField plasează un câmp pe axa paginii, iar PageItemIndex preselectează un singur element de cache, având ca valoare implicită xlPageItemAll ($7FFD, însemnând (All)). Pentru a permite cititorului să bifeze mai multe elemente simultan, setați MultipleItemSelectionAllowed := True, proprietate pe care HotXLS o scrie ca <pivotField multipleItemSelectionAllowed="1"/>. Pentru criterii dincolo de selecția manuală, fiecare câmp conține o colecție tipizată Filters care cuprinde familiile ST_FilterType din OOXML — filtre de număr, procent, sumă, titlu, valoare și dată —, fiecare intrare asociind un tip de filtru cu valorile sale de comparare

Cum se actualizează memoria cache a pivotului când sursa se modifică?

Metoda RefreshPivotCache din TXLSXWorkbook rescanează intervalul sursă și reconstruiește cache-ul în mod direct, exact ceea ce are nevoie un flux de procesare pe loturi (batch pipeline) după ce editează rândurile de bază. Transmiteți ID-ul de cache, iar metoda recitește fiecare celulă din intervalul sursă (antetul pe primul rând, datele începând cu al doilea), reconstruiește domeniul de elemente partajate ale fiecărui câmp prin rededuplicarea valorilor și reevaluarea limitelor per tip, rescriind indicii elementelor pentru fiecare înregistrare. Returnează 1 în caz de succes și -1 când ID-ul de cache este necunoscut sau foaia sursă lipsește

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;

Limita semantică merită menționată clar. Înainte de existența acestei metode, HotXLS se baza pe flagul refreshOnLoad="1" și lăsa sarcina de actualizare în seama Excel la următoarea deschidere, ceea ce este în regulă când fișierul este deschis de un operator uman, dar inutil într-un flux headless care trebuie să livreze date corecte. RefreshPivotCache actualizează cache-ul salvat în cadrul procesului (in-process) și, deoarece tabelele se asociază la cache prin ID, fiecare pivot derivat din acel cache va reflecta actualizarea. Ceea ce nu face această metodă este generarea aspectului sau agregarea grilei vizibile — Excel recalculează în continuare tabelul randat din cache-ul actualizat în momentul deschiderii fișierului

Ce scrie HotXLS și ce calculează Excel

Păstrați în vedere această diviziune a sarcinilor și nimic nu vă va surprinde. HotXLS scrie definițiile: produce secțiunea pivotCacheDefinition, înregistrările de cache și pivotTableDefinition, completate cu axe, agregări, elemente și membri calculați, afișări de valori, grupări și filtre. Excel este cel care calculează: la deschidere, materializează grupurile, evaluează formulele calculate, aplică transformările showDataAs și agregă corpul tabelului. Valorile pe care le vede utilizatorul sunt ale Excel, generate pe baza instrucțiunilor scrise de HotXLS, motiv pentru care fiecare formulă și index de bază trebuie să fie corecte în momentul scrierii, nu verificate ulterior la randare

Merită cunoscute două limite înainte de a proiecta în jurul acestei funcționalități. AddPivotTable analizează un interval dreptunghiular A1 cu un prefix opțional de foaie; sursele bazate pe intervale numite (named ranges) și registre externe sunt dincolo de ceea ce rezolvă constructorul, deși cache-urile citite dintr-un fișier creat în Excel își păstrează sursele de interval numit la salvare. De asemenea, tabelele pivot create în Excel și citite de HotXLS de pe disc sunt rescrise octet cu octet la salvare, astfel încât editările tipizate descrise aici se aplică curat pe pivoturile create în cod, în timp ce pivoturile existente rămân neschimbate. Pentru partea de introducere a datelor unui raport — celulele validate și tabelele filtrate pe care le rezumă pivotul —, consultați articolul despre validarea datelor, AutoFilter și tabele structurate

Modelul de tabel pivot prezentat aici face parte din pachetul standard HotXLS Delphi Excel Component pentru Delphi și C++Builder, care citește și scrie atât secțiunile tipizate de pivot XLSX, cât și înregistrările clasice BIFF8 din același model de obiecte