Excel-pivot-taulukoiden luominen ja päivittäminen Delphissä HotXLS:n avulla

HotXLS luo ja päivittää natiiveja XLSX-pivot-taulukoita Delphistä ja C++Builderista ilman, että koneelle on asennettu Exceliä tai COM-automaatiota. Kutsut AddPivotTable-metodia lähdealueen kanssa, pudotat kentät rivi-, sarake-, sivu- ja tietokeleille (axes), lisäät laskettuja elementtejä tai prosenttiosuuden näyttötilan, ja komponentti kirjoittaa pivotCacheDefinition- ja pivotTableDefinition-osat, jotka Excel avaa elävänä, päivitettävänä pivot-taulukkona

Skenaario, joka tekee tästä vaivan arvoisen, on raportointipalvelin. Luot satoja työkirja yössä, ja jokainen niistä sisältää yhtä tiliä yhteenvetävän pivotin. Seuraavassa kuussa luvut muuttuvat ja jokaisen tiedoston on heijastettava uusia lähderivejä. Excelin ohjaaminen Windows-palvelusta on haurasta eikä sitä ole lisensoitu palvelinkäyttöön, ja pivot-XML:n kirjoittaminen käsin on loputon spesifikaatioarkeologinen projekti. HotXLS sijoittuu näiden kahden umpikuvan väliin: se tarjoaa tyypitetyn objektimallin OOXML-pivot-osien päälle, joten sama Pascal, joka täyttää solut, myös määrittelee pivotin ja rakentaa sen välimuistin uudelleen samassa prosessissa

Miten pivot-taulukko luodaan Delphissä ilman Exceliä?

Luot sellaisen yhdellä kutsulla ja asetat sitten kentät akseleille. AddPivotTable ottaa vastaan lähdealueen A1-notaatiossa, kohdesolun johon taulukko ankkuroituu, ja nimen. Se jäsentää alueen, skannaa jokaisen sarakkeen päätelläkseen sen tietotyypin, rakentaa pivot-välimuistin (tai käyttää uudelleen jo samaan alueeseen sidottua välimuistia) ja palauttaa TXLSPivotTable-objektin, jonka kentät alkavat akselin ulkopuolelta. Siitä eteenpäin mukavuusmetodit AddRowField, AddColumnField, AddPageField ja AddDataFieldByName rakentavat asettelun, ja jokainen tietokenttä ottaa yhden yhdestätoista koosteesta (aggregations) TXLSPivotAggregation-luettelosta (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];                 // raporttitaulukko (1-perusteinen XLSX-moottorissa)

    // Lähde A1:E500 'Data'-taulukossa; ankkuroi pivot riville 3, sarakkeeseen 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;

Miksi välimuisti ja taulukko ovat kaksi erillistä osaa

Pivot-taulukko koostuu todellisuudessa kahdesta toisiinsa viittaavasta osasta, ja tämän jaon ymmärtäminen pitää loput asiat selkeinä. pivotCacheDefinition on tietojen snapshot: worksheetSource viittaa lähdealueeseen, ja yksi cacheField saraketta kohden sisältää sarakkeen erilliset arvot (sen sharedItems) sekä johdetut rajat. pivotTableDefinition on näkymä: mikä välimuistikenttä istuu milläkin akselilla, tietokenttä ja niiden koosteet sekä asettelukytkimet. Näkymä sitoutuu välimuistiin cacheId-tunnisteella työkirjan pivotCaches-suhteen kautta, aivan kuten ECMA-376 Part 1 §18.10 ja [MS-XLSX] määrittelevät

Tämä epäsuoruus ei ole byrokratiaa, ja se ostaa kaksi asiaa. Yksi välimuisti voi tukea useita taulukoita, joten välimuistin päivittäminen kerran päivittää jokaisen siitä piirretyn näkymän. Ja välimuisti tallentaa kunkin sarakkeen arvot dedupikoituna tietueryhmäkohtaisen indeksitaulukon kanssa raa'an ruudukon sijaan, minkä vuoksi pivotin rakentaminen tarkoittaa lähteen skannaamista, ei solujen kopioimista. HotXLS mallintaa tämän samalla tavalla molemmissa moottoreissa, joten yllä oleva koodi on lähes identtinen klassisen polun kanssa, joka on dokumentoitu klassisten .xls-pivottien taustalla olevan binaarisen SX-tietuerakenteen yhteydessä. Jos lähteesi elää toisella taulukolla tai osoitat sitä nimen kautta, alueen etuliite ja määritellyt nimet ja ristiintaulukkoviittaukset noudattavat tavallisia A1-sääntöjä, ja lainausmerkeillä varustetut taulukoiden nimet ovat sallittuja välilyöntejä sisältäville nimille

Lasketut elementit eivät ole laskettuja kenttiä

Nämä kolme termiä nimeävät kolme eri asiaa, ja niiden sekoittaminen on klassinen pivot-virhe. Laskettu elementti (calculated item) elää yhden kentän sisällä ja yhdistää kyseisen kentän omat elementit nimen perusteella, joten Region-kentässä voit määrittää synteettisen CoreMarkets-rivin, joka on yhtä kuin North plus South. HotXLS tarjoaa tämän nimellä TXLSPivotField.AddCalculatedItem ja kirjoittaa <calculatedItem>-merkinnän kyseisen kentän <calculatedItems>-luetteloon. Laskettu kenttä (calculated field) on erilainen: se on uusi arvo, joka on johdettu muista sarakkeista, kuten Margin sarakkeista Revenue ja Cost, ja se kulkee kaavana välimuistikentässä TXLSPivotCacheField.Formula-ominaisuuden kautta. Laskettu jäsen (calculated member), joka lisätään TXLSPivotTable.AddCalculatedMember-kutsulla, on taulukkotason mukautettu jäsen, joka voi toimia mittana (measure, jäsentyyppi data) tai dimensiovalintana (dimension member), pääasiassa OLAP-muotoisten pivottien tarpeisiin

var
  Region: TXLSPivotField;
  Member: TXLSPivotCalculatedMember;
begin
  // Laskettu ELEMENTTI yhdistää yhden kentän elementtejä nimen mukaan.
  Region := Pivot.AddRowField('Region');
  Region.AddCalculatedItem('CoreMarkets', '=North+South');

  // Laskettu JÄSEN määritellään taulukkotasolla. Jäsentyyppi
  // 'data' merkitsee sen mitaksi; tyhjä tyyppi on dimensiojäsen.
  Member := Pivot.AddCalculatedMember('AvgTicket', '=Revenue/Units');
  Member.MemberType := 'data';
end;

Yksi rehellinen raja koskee kaikkia kolmea. HotXLS tuottaa kaavatekstin määrittely-XML-koodiin; se ei laske sitä. Excel laskee lasketun elementin, kentän tai jäsenen, kun se avaa tiedoston, aivan kuten se laskee jokaisen koosteen ruudukossa. HotXLS kirjoittaa ohjeet, ei tuloksia, joten antamiesi kaavojen on oltava valideja pivot-kaavoja Excelin omassa murteessa, viitaten kenttien ja elementtien nimiin kuten Excel odottaa

Miten arvot näytetään prosenttiosuutena kokonaissummasta?

Asetat arvon näyttötilan (value display mode) tietokenttään sen sijaan, että muuttaisit luvut itse. TXLSPivotDataField.ShowDataAs ottaa vastaan TXLSPivotShowDataAs-luettelon, joka peilaa OOXML:n ST_ShowDataAs-arvoja: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal ja xlpsdaIndex. Yleinen temppu on asettaa sama lähdesarake tietokenttään kahdesti, kerran raakana summana ja kerran osuutena kokonaissummasta, jolloin raportti näyttää sekä luvun että sen painoarvon

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

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

  // Elementtirelatiiviset tilat tarvitsevat vertailukohdan. Juokseva summa
  // pitkin 'Quarter'-kenttää (välimuistikentän indeksi 3) olisi:
  //   Share.ShowDataAs := xlpsdaRunTotal;
  //   Share.BaseField  := 3;         // välimuistikentän indeksi, jonka yli muunnos suoritetaan
  //   Share.BaseItem   := $7FFD;     // $7FFD = "(All)"
end;

xlpsdaPercentOfTotal ei tarvitse kantaa, koska se on suhteessa loppusummaan, mutta elementtirelatiiviset tilat tarvitsevat. xlpsdaDifference, xlpsdaPercentDiff ja xlpsdaRunTotal edellyttävät BaseField-asetusta (välimuistikentän indeksi, jonka yli vertailu suoritetaan) ja elementtiin kiinnitetyille muodoille BaseItem-indeksiä, jossa $7FFD edustaa (All)-sentineliä. HotXLS kirjoittaa nämä määritteinä <dataField showDataAs="percentOfTotal" baseField="N" baseItem="M"/> ja jättää laskennan Excelille

Päivämäärien ja numeroiden ryhmittely sekä sivu-kenttien suodattimet

Ryhmittely määritetään välimuistikentässä, ei pivot-kentässä, koska se muuttaa sitä, miten lähdealue jaetaan lohkoihin. Aseta HasGroup := True välimuistikentälle, jonka palauttaa Cache.FindFieldByName, ja valitse sitten joko numeerisen alueen asetukset (GroupStartNum, GroupEndNum, GroupInterval) tai päivämäärähierarkian liput (GroupByDate yhdessä asetusten GroupMonths, GroupQuarters, GroupYears ja GroupStartDate / GroupEndDate kanssa). HotXLS tuottaa vastaavan <fieldGroup><rangePr groupBy="months"/>-elementin, jotta kuukauden tai 1000-leveän numeerisen kaistan mukaan ryhmitelty kenttä avautuu siten kuin Excel olisi sen ryhmitellyt

Sivukentät (page fields) ovat suodatinpudotusvalikkoja taulukon yläpuolella. AddPageField asettaa kentän sivualueelle (page axis), ja PageItemIndex valitsee valmiiksi yksittäisen välimuistielementin, oletuksena xlPageItemAll ($7FFD, eli (All)). Jotta lukija voi valita useita elementtejä kerralla, aseta MultipleItemSelectionAllowed := True, minkä HotXLS kirjoittaa muodossa <pivotField multipleItemSelectionAllowed="1"/>. Manuaalisen valinnan ulkopuolella jokainen kenttä sisältää Filters-kokoelman, joka kattaa OOXML:n ST_FilterType-perheet — määrä-, prosentti-, summa-, otsikko-, arvo- ja päivämääräsuodattimet —, ja jokainen merkintä parittaa suodatintyypin sen vertailuarvojen kanssa

Miten pivot-välimuisti päivitetään, kun lähde muuttuu?

TXLSXWorkbook-luokan RefreshPivotCache skannaa lähdealueen uudelleen ja rakentaa välimuistin paikan päällä, mikä on juuri se mitä eräajoputki tarvitsee muokattuaan taustalla olevia rivejä. Välitä välimuistin id, ja menetelmä lukee uudelleen jokaisen solun lähdealueelta (ylätunniste ensimmäiseltä riviltä, tiedot toiselta), rakentaa kunkin kentän jaetun elementtialueen dedupikoimalla arvot ja johtamalla uudelleen tyyppikohtaiset rajat, ja kirjoittaa tietueryhmäkohtaiset elementti-indeksit uudelleen. Se palauttaa arvon 1 onnistuessaan ja -1, jos välimuistin id on tuntematon tai lähdetaulukko puuttuu

var
  Rc: Integer;
begin
  // ...lähdeaineisto on muuttunut sen jälkeen, kun pivot rakennettiin...
  Sheet := Book.Sheets[1];
  Sheet.Cells[2, 5].Value := 128000;             // korjattu Revenue-luku

  // Rakenna uudelleen välimuistin jaetut elementit ja tietueryhmäindeksit lähteestä.
  Rc := Book.RefreshPivotCache(Pivot.CacheId);   // 1 = päivitetty, -1 = välimuisti/lähde puuttuu
  if Rc = 1 then
    Book.SaveAs('sales-pivot-refreshed.xlsx');
end;

Semanttinen raja tässä on syytä sanoa suoraan. Ennen tämän menetelmän olemassaoloa HotXLS tukeutui refreshOnLoad="1"-lippuun ja jättää kaiken päivitystyön Excelille seuraavan avaamisen yhteydessä. Tämä on hienoa, kun ihminen avaa tiedoston, mutta hyödytöntä headless-putkessa, jonka on luovutettava oikeita tietoja. RefreshPivotCache tekee tallennetusta välimuistista ajantasaisen prosessin sisällä. Koska taulukot sitoutuvat välimuistiin id:n perusteella, jokainen kyseisestä välimuistista piirretty pivot-taulukko näkee päivityksen. Se, mitä se ei tee, on näkyvän ruudukon asettelu tai aggregointi — Excel laskee edelleen renderöidyn taulukon päivitetystä välimuistista, kun tiedosto avataan

Mitä HotXLS kirjoittaa ja mitä Excel laskee

Pidä työnjako mielessä, niin mikään ei yllätä sinua. HotXLS on määrittelyjen kirjoittaja: se tuottaa pivotCacheDefinition-tiedoston, välimuistitietueet ja pivotTableDefinition-tiedoston akseleineen, koosteineen, laskettuine elementteineen ja jäsenineen, arvonäyttöineen, ryhmittelyineen ja suodattimineen. Excel on laskin: avattaessa se luo ryhmälohkot, arvioi lasketut kaavat, soveltaa showDataAs-muunnoksia ja aggregoidun rungon. Arvot, jotka käyttäjä näkee, ovat Excelin tuottamia HotXLS:n kirjoittamien ohjeiden perusteella, minkä vuoksi jokaisen kaavan ja perusindeksin on oltava oikein kirjoitushetkellä sen sijaan, että ne tarkistettaisiin renderöintiaikana

Kaksi rajoitusta on syytä tietää ennen tämän ominaisuuden ympärille suunnittelua. AddPivotTable jäsentää suorakulmaisen A1-alueen valinnaisella taulukon etuliitteellä. Nimetyt alueet ja ulkoiset työkirjalähteet ovat sen ratkaisukyvyn ulkopuolella, vaikka Excelillä luodut ja levyltä luetut välimuistit säilyttävät nimetyn alueensa lähteen edestakaisella matkalla. Excelillä luodut pivot-taulukot, jotka HotXLS lukee levyltä, toistetaan tavu tavulta tallennuksen yhteydessä, joten tässä kuvatut tyypitetyt muokkaukset soveltuvat puhtaasti koodissa rakentamiisi pivot-taulukoihin, kun taas olemassa olevat pysyvät muuttumattomina. Raportin syötepuolelle — validoituihin soluihin ja suodatettuihin taulukoihin, joita pivot tekee yhteenvedon — katso artikkeli tietojen validointi, AutoFilter ja rakenteiset taulukot

Tässä esitetty pivot-malli on osa standardia Delphille ja C++Builderille tarkoitettua HotXLS Delphi Excel -komponenttia, joka lukee ja kirjoittaa sekä XLSX-tyypitetyt pivot-osat että klassiset BIFF8-tietueet samasta objektimallista