Gradnja in osveževanje vrtilnih tabel Excel v Delphi s HotXLS

HotXLS gradi in osvežuje izvorne vrtilne tabele XLSX iz Delphi in C++Builder brez nameščenega Excela in brez avtomatizacije COM na stroju. Pokličete AddPivotTable z izvornim obsegom, spustite polja na osi vrstic, stolpcev, strani in podatkov, dodate plast izračunanih elementov ali prikaz odstotka skupne vrednosti, komponenta pa zapiše dela pivotCacheDefinition in pivotTableDefinition, ki jih Excel odpre kot živo vrtilno tabelo z možnostjo osveževanja

Scenarij, zaradi katerega je to vredno truda, je strežnik za poročanje. Vsako noč ustvarite na stotine delovnih zvezkov, od katerih vsak prenaša vrtilno tabelo, ki povzema en račun, naslednji mesec pa se številke premaknejo in vsaka datoteka mora odražati nove izvorne vrstice. Upravljanje Excela iz storitve Windows je krhko in ni licencirano za strežniško uporabo, ročno pisanje XML-ja vrtilnih tabel pa je projekt arheologije specifikacij, ki se nikoli ne konča. HotXLS sedi med tema dvema slepima ulicama: tipiziran objektni model čez dele vrtilnih tabel OOXML, tako da isti Pascal, ki polni celice, tudi deklarira vrtilno tabelo in znova zgradi njen predpomnilnik v istem procesu

Kako ustvariti vrtilno tabelo v Delphi brez Excela?

Ustvarite jo z enim samim klicem in nato postavite polja na osi. AddPivotTable vzame izvorno območje v zapisu A1, ciljno celico, kjer se tabela sidra, in ime; razčleni območje, pregleda vsak stolpec, da ugotovi njegov podatkovni tip, zgradi predpomnilnik vrtilne tabele (ali ponovno uporabi tistega, ki je že vezan na isto območje), in vrne TXLSPivotTable, katere polja se vsa začnejo izven osi. Od tam priročne metode AddRowField, AddColumnField, AddPageField in AddDataFieldByName povežejo postavitev, vsako podatkovno polje pa vzame eno od enajstih agregacij iz enuma 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;

Zakaj sta predpomnilnik in tabela dva ločena dela

Vrtilna tabela sta dejansko dva artefakta, ki se sklicujeta drug na drugega, in razumevanje te razdelitve ohranja preostanek jasen. pivotCacheDefinition je posnetek podatkov: worksheetSource, ki kaže na izvorno območje, in po en cacheField na stolpec, ki drži različne vrednosti tega stolpca (njegove sharedItems) ter izpeljane meje. pivotTableDefinition je pogled: katero polje predpomnilnika sedi na kateri osi, podatkovna polja in njihove agregacije ter stikala postavitve. Pogled se veže na predpomnilnik prek cacheId skozi razmerje delovnega zvezka pivotCaches, natanko tako, kot določata ECMA-376 del 1 §18.10 in [MS-XLSX]

Ta posrednost ni birokracija, prinaša dve stvari. En predpomnilnik lahko podpira več tabel, zato osveževanje predpomnilnika enkrat posodobi vsak pogled, črpan iz njega. Predpomnilnik tudi shranjuje vrednosti vsakega stolpca deduplicirane z indeksno tabelo na zapis nemesto surove mreže, zato gradnja vrtilne tabele pomeni pregledovanje vira in ne kopiranja celic. HotXLS to modelira na enak način za oba motorja, zato je zgornja koda skoraj enaka klasični poti, dokumentirani ob binarni postavitvi SX-zapisov za klasične vrtilne tabele .xls. Če vaš vir živi na drugem listu ali ga nagovarjate prek imena, predpona obsega in definirana imena in sklici čez liste sledijo običajnim pravilom A1, pri čemer so dovoljena narekovana imena listov za imena, ki vsebujejo presledke

Izračunani elementi niso izračunana polja

Ti trije izrazi poimenujejo tri različne stvari in njihovo mešanje je klasična napaka pri vrtilnih tabelah. Izračunani element živi znotraj enega polja in združuje lastne elemente tega polja po imenu, tako da lahko znotraj polja Region določite sintetično vrstico CoreMarkets, enako North plus South; HotXLS to izpostavlja kot TXLSPivotField.AddCalculatedItem in zapiše <calculatedItem> pod <calculatedItems> tega polja. Izračunano polje je drugačno: to je nova vrednost, izpeljana iz drugih stolpcev, kot je Margin iz Revenue in Cost, in se prenaša kot formula na polju predpomnilnika prek TXLSPivotCacheField.Formula. Izračunani član, dodan z TXLSPivotTable.AddCalculatedMember, pa je lasten član na ravni tabele, ki lahko deluje kot mera (tip člana data) ali član dimenzije, kar je predvsem zanimivo za vrtilne tabele oblike 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;

Ena iskrena meja velja za vse tri. HotXLS odda besedilo formule v XML definicije; ne ovrednoti pa ga. Excel izračuna izračunani element, polje ali člana, ko odpre datoteko, na enak način, kot izračuna vsak agregat v mreži. HotXLS zapisuje navodila in ne rezultatov, zato morajo biti formule, ki jih navedete, veljavne formule vrtilnih tabel v lastnem Excelovem dialektu, ki se sklicujejo na imena polj in elementov, kot bi jih Excel

Kako prikazati vrednosti kot odstotek skupne vrednosti?

Nastavite način prikaza vrednosti na podatkovnem polju, nemesto da bi sami transformirali številke. TXLSPivotDataField.ShowDataAs vzame enum TXLSPivotShowDataAs, ki zrcali vrednosti OOXML ST_ShowDataAs: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal in xlpsdaIndex. Pogost trik je, da isti izvorni stolpec postavite na podatkovno os dvakrat, enkrat kot surovo vsoto in enkrat kot delež skupne vrednosti, tako da poročilo prikazuje tako številko kot njeno težo

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;

xlpsdaPercentOfTotal ne potrebuje osnove, ker je relativen glede na skupno vsoto, načini, relativni na elemente, pa jo potrebujejo. xlpsdaDifference, xlpsdaPercentDiff in xlpsdaRunTotal zahtevajo BaseField (indeks polja predpomnilnika, čez katerega teče primerjava) in, za oblike, zasidrane na elemente, indeks BaseItem, kjer $7FFD stoji za stražarja (All). HotXLS zapiše te kot atribute <dataField showDataAs="percentOfTotal" baseField="N" baseItem="M"/> in, tako kot pri izračunanih formulah, prepusti aritmetiko Excelu

Združevanje datumov in števil ter filtri polj strani

Združevanje se konfigurira na polju predpomnilnika in ne na polju vrtilne tabele, ker spremeni način razvrščanja izvorne domene. Nastavite HasGroup := True na polju predpomnilnika, ki ga vrne Cache.FindFieldByName, nato pa izberite gumbe za številski obseg (GroupStartNum, GroupEndNum, GroupInterval) ali zastavice datumskega drevesa (GroupByDate z GroupMonths, GroupQuarters, GroupYears in razponom GroupStartDate / GroupEndDate). HotXLS odda ustrezen element <fieldGroup><rangePr groupBy="months"/>, tako da se polje, združeno po mesecih ali po 1000 širokem številskem pasu, odpre tako, kot bi ga združil Excel

Polja strani so spustni filtri nad tabelo. AddPageField postavi polje na os strani, PageItemIndex pa vnaprej izbere posamezen element predpomnilnika, kar je privzeto xlPageItemAll ($7FFD, kar pomeni (All)). Da bralcu omogočite izbiro več elementov hkrati, nastavite MultipleItemSelectionAllowed := True, kar HotXLS zapiše kot <pivotField multipleItemSelectionAllowed="1"/>. Za kriterije zunaj ročne izbire vsako polje prenaša tipizirano zbirko Filters, ki obsega družine OOXML ST_FilterType — filtre po številu, odstotkih, vsoti, naslovih, vrednostih in datumih —, pri čemer vsak vnos združi tip filtra z njegovimi primerjalnimi vrednostmi

Kako osvežiti predpomnilnik vrtilne tabele, ko se vir spremeni?

RefreshPivotCache na TXLSXWorkbook ponovno pregleda izvorni obseg in znova zgradi predpomnilnik na mestu, kar je tisto, kar paketni potek dela potrebuje, potem ko uredi spodnje vrstice. Prenesite ID predpomnilnika in metoda ponovno prebere vsako celico v izvornem obsegu (glava v prvi vrstici, podatki od druge dalje), ponovno zgradi domeno skupnih elementov vsakega polja z dedupliciranjem vrednosti in ponovnim izpeljevanjem mej po tipu ter ponovno zapiše indekse elementov na zapis. Vrne 1 ob uspehu in -1, ko je ID predpomnilnika neznan ali pa izvorna stran manjka

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čno mejo je tukaj vredno odkrito zapisati. Preden je ta metoda obstajala, se je HotXLS zanašal na zastavico refreshOnLoad="1" in prepustil vso delo osveževanja Excelu ob naslednjem odpiranju, kar je v redu, ko datoteko odpre človek, a neuporabno v strežniškem poteku dela brez glave, ki mora oddati pravilne podatke. RefreshPivotCache poskrbi, da je shranjeni predpomnilnik posodobljen v samem procesu, in ker se tabele vežejo na predpomnilnik prek ID-ja, vsaka vrtilna tabela, črpana iz tega predpomnilnika, vidi posodobitev. Tisto, česar ne stori, pa je postavitev ali agregacija vidne mreže — Excel še vedno preračuna izrisano tabelo iz osveženega predpomnilnika, ko se datoteka odpre

Kaj piše HotXLS in kaj računa Excel

Imejte razdelitev dela pred očmi in nič vas ne bo presenetilo. HotXLS je pisec definicij: ustvari pivotCacheDefinition, zapise predpomnilnika in pivotTableDefinition, skupaj z osmi, agregacijami, izračunanimi elementi in člani, prikazi vrednosti, združevanjem in filtri. Excel is the calculator: ob odpiranju materializira skupinske vedre, ovrednoti izračunane formule, uveljavi transformacije showDataAs in agregira telo. Vrednosti, ki jih uporabnik vidi, so Excelove, ustvarjene iz navodil, ki jih je napisal HotXLS, zato mora biti vsaka formula in osnovni indeks pravilen ob času zapisovanja in se ne preverja ob času izrisa

Dve omejitvi je vredno poznati, preden začnete načrtovati okoli te funkcije. AddPivotTable razčleni pravokoten obseg A1 z neobvezno predpono lista; viri imenovanih območij in zunanjih delovnih zvezkov so izven tega, kar graditelj razreši, čeprav predpomnilniki, prebrani iz datoteke, ki jo je ustvaril Excel, ohranijo svoj vir imenovanih območij pri prenosu v obe smeri. Prav tako se vrtilne tabele, ki jih je ustvaril Excel in jih HotXLS prebere z diska, ob shranjevanju prepišejo bajt za bajtom, tako da se tipizirana urejanja, opisana tukaj, čisto uveljavijo na vrtilnih tabelah, ki jih zgradite v kodi, medtem ko obstoječe tabele ostanejo brez izgub. Za vhodno stran poročila — potrjene celice in filtrirane tabele, ki jih vrtilna tabela povzema —, si oglejte potrjevanje podatkov, AutoFilter in strukturirane tabele

Model vrtilne tabele, prikazan tukaj, je del standardne komponente HotXLS Component za Delphi in C++Builder, ki bere in zapisuje tako tipizirane dele vrtilnih tabel XLSX kot tudi klasične zapise BIFF8 iz istega objektnega modela