LlosLab blogg for programvareutviklingUtviklingsnotater for PDF-, Delphi- og dokumentprogramvareutviklere.

Teknisk artikkel

Bygge og oppdatere Excel-pivottabeller i Delphi med HotXLS

HotXLS Component

HotXLS bygger og oppdaterer innfødte XLSX-pivottabeller fra Delphi og C++Builder uten at Excel er installert eller at COM-automatisering kjører på maskinen. Du kaller AddPivotTable med et kildeområde, plasserer felt på rad-, kolonne-, side- og dataaksene, legger til beregnede elementer eller en prosent-av-total-visning, og komponenten skriver pivotCacheDefinition- og pivotTableDefinition-delene som Excel åpner som en levende, oppdaterbar pivottabell

Scenarioet som gjør dette verdt bryet, er en rapporteringsserver. Du genererer hundrevis av arbeidsbøker hver natt, og hver av dem inneholder en pivottabell som oppsummerer én konto. Neste måned endres tallene, og hver fil må gjenspeile de nye kilderadene. Å styre Excel fra en Windows-tjeneste er ustabilt og ikke lisensiert for serverbruk, og å skrive pivottabell-XML for hånd er et evighetsprosjekt innen spesifikasjonsarkeologi. HotXLS plasserer seg mellom disse to blindveiene: en typet objektmodell over OOXML-pivottabelldelene, slik at den samme Pascal-koden som fyller cellene, også erklærer pivottabellen og gjenoppbygger hurtigbufferen (cache) i samme prosess

Hvordan oppretter du en pivottabell i Delphi uten Excel?

Du oppretter en med et enkelt kall og plasserer deretter felt på aksene. AddPivotTable tar kildeområdet i A1-notasjon, målocellen der tabellen forankres, og et navn. Den analyserer området, skanner hver kolonne for å utlede datatype, bygger en pivothurtigbuffer (eller gjenbruker en som allerede er bundet til det samme området), og returnerer en TXLSPivotTable der alle felt starter utenfor aksene. Derfra setter de praktiske metodene AddRowField, AddColumnField, AddPageField og AddDataFieldByName opp oppsettet, og hvert datafelt tar en av elleve aggregeringer fra TXLSPivotAggregation-oppregningen (enum) (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;

Hvorfor hurtigbufferen og tabellen er to separate deler

En pivottabell består egentlig av to elementer (artifacts) som refererer til hverandre, og å forstå dette skillet gjør det enklere å forstå resten. pivotCacheDefinition er data-øyeblikksbildet: en worksheetSource som peker på kildeområdet, og ett cacheField per kolonne som inneholder den kolonnens unike verdier (dens sharedItems) pluss avledede grenser. pivotTableDefinition er visningen: hvilket hurtigbufferfelt som ligger på hvilken akse, datafeltene og deres aggregeringer, samt layout-innstillinger. Visningen bindes til hurtigbufferen med en cacheId via arbeidsbokens pivotCaches-relasjon, nøyaktig slik ECMA-376 del 1 §18.10 og [MS-XLSX] beskriver det

Denne indirekte tilknytningen er ikke byråkrati, den gir to fordeler. Én hurtigbuffer kan understøtte flere tabeller, slik at oppdatering av hurtigbufferen én gang oppdaterer alle visninger som er hentet fra den. I tillegg lagrer hurtigbufferen hver kolonnes verdier deduplisert med en indekstabell per post (record) i stedet for det rå rutenettet, og det er derfor opprettelse av en pivottabell innebærer skanning av kilden, ikke kopiering av celler. HotXLS modellerer dette på samme måte for begge motorer, så koden ovenfor er nesten identisk med den klassiske banen dokumentert ved siden av den binære SX-record-layouten bak klassiske .xls-pivottabeller. Hvis kilden din ligger på et annet ark eller du adresserer den via et navn, følger områdeprefikset og definerte navn og kryssarkreferanser de vanlige A1-reglene, der arknavn i anførselstegn er tillatt for navn som inneholder mellomrom

Beregnede elementer er ikke beregnede felt

Disse tre begrepene betegner tre forskjellige ting, og å blande dem sammen er den klassiske pivottabell-feilen. Et beregnet element (calculated item) lever inni ett enkelt felt og kombinerer dette feltets egne elementer etter navn, slik at du innenfor et Region-felt kan definere en syntetisk CoreMarkets-rad lik North pluss South. HotXLS eksponerer dette som TXLSPivotField.AddCalculatedItem og skriver et <calculatedItem> under dette feltets <calculatedItems>. Et beregnet felt (calculated field) er annerledes: det er en ny verdi avledet fra andre kolonner, som Margin fra Revenue og Cost, og det lagres som en formel på et hurtigbufferfelt via TXLSPivotCacheField.Formula. Et beregnet medlem (calculated member), lagt til med TXLSPivotTable.AddCalculatedMember, er et egendefinert medlem på tabellnivå som kan fungere som et mål (medlemstype data) eller et dimensjonsmedlem, hovedsakelig av interesse for OLAP-strukturerte pivottabeller

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;

Én avklart grense gjelder for alle tre. HotXLS skriver ut formelteksten i definisjons-XML-en; den evaluerer den ikke. Excel beregner det beregnede elementet, feltet eller medlemmet når det åpner filen, på samme måte som det beregner hvert aggregat i rutenettet. HotXLS skriver instruksjonene, ikke resultatene, så formlene du oppgir må være gyldige pivottabellformler i Excels egen dialekt, der felt- og elementnavn refereres slik Excel forventer

Hvordan viser du verdier som en prosent av totalen?

Du angir visningsmodus for verdi på datafeltet i stedet for å transformere tallene selv. TXLSPivotDataField.ShowDataAs tar TXLSPivotShowDataAs-oppregningen, som speiler OOXML ST_ShowDataAs-verdiene: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal og xlpsdaIndex. Et vanlig triks er å plassere den samme kildekolonnen på dataaksen to ganger, én gang som en rå sum og én gang som en andel av totalsummen, slik at rapporten viser både tallet og dets vekt

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 trenger ingen sammenligningsbase fordi den er relativ til totalsummen, men de element-relative modusene gjør det. xlpsdaDifference, xlpsdaPercentDiff og xlpsdaRunTotal krever BaseField (hurtigbufferfeltindeksen som transformasjonen kjører over) og, for de elementforankrede formene, en BaseItem-indeks, der $7FFD står for (Alle)-markøren. HotXLS skriver disse som attributter i <dataField showDataAs="percentOfTotal" baseField="N" baseItem="M"/>, og overlater i likhet med beregnede formler selve aritmetikken til Excel

Gruppere datoer og tall, og filtre for sidefelt

Gruppering konfigureres på hurtigbufferfeltet, ikke på pivottabellfeltet, fordi det endrer hvordan kildedomenet fordeles i bøtter (bucketed). Sett HasGroup := True på hurtigbufferfeltet returnert av Cache.FindFieldByName, og velg deretter enten innstillingene for numerisk område (GroupStartNum, GroupEndNum, GroupInterval) eller dato-hierarkiflaggene (GroupByDate med GroupMonths, GroupQuarters, GroupYears og et GroupStartDate / GroupEndDate-tidsspenn). HotXLS skriver ut det samsvarende <fieldGroup><rangePr groupBy="months"/>-elementet, slik at et felt gruppert etter måned eller etter et 1000-bredt numerisk bånd åpnes slik Excel ville ha gruppert det

Sidefelt (page fields) er filter-nedtrekkslistene over tabellen. AddPageField plasserer et felt på sideaksen, og PageItemIndex forhåndsvelger et enkelt hurtigbuffer-element, som standard satt til xlPageItemAll ($7FFD, som betyr (Alle)). For å la en leser krysse av for flere elementer samtidig, setter du MultipleItemSelectionAllowed := True, som HotXLS skriver som <pivotField multipleItemSelectionAllowed="1"/>. For andre kriterier enn et manuelt valg, har hvert felt en typet Filters-samling som spenner over OOXML ST_FilterType-familiene — antall, prosent, sum, bildetekst, verdi og datofiltre — der hver oppføring parer en filtertype med sammenligningsverdiene sine

Hvordan oppdaterer du en pivothurtigbuffer når kilden endres?

RefreshPivotCache på TXLSXWorkbook skanner kildeområdet på nytt og gjenoppbygger hurtigbufferen på stedet, noe en kjørsel (batch pipeline) trenger etter at den har redigert de underliggende radene. Send med hurtigbuffer-id-en, og metoden leser hver celle i kildeområdet på nytt (overskrift på første rad, data fra den andre), gjenoppbygger hvert felts felles-element-domene (shared-item domain) ved å deduplisere verdier på nytt og avlede grenser per type, og skriver indeksen for elementer per post på nytt. Den returnerer 1 ved suksess og -1 når hurtigbuffer-id-en er ukjent eller kildearket mangler

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;

Den semantiske grensen her er verdt å si tydelig. Før denne metoden eksisterte, stolte HotXLS på refreshOnLoad="1"-flagget og overlot alt oppdateringsarbeid til Excel ved neste gjenåpning. Dette fungerer fint når et menneske skal åpne filen, men er ubrukelig i en automatisert kjøreprosess som må levere korrekte data videre. RefreshPivotCache gjør den lagrede hurtigbufferen oppdatert i selve prosessen, og fordi tabeller bindes til en hurtigbuffer med id, ser hver pivottabell basert på denne hurtigbufferen oppdateringen. Det den ikke gjør, er å sette opp eller aggregere det synlige rutenettet — Excel må fortsatt rekapitulere den rendrede tabellen fra den oppdaterte hurtigbufferen når filen åpnes

Hva HotXLS skriver og hva Excel beregner

Hold arbeidsfordelingen i bakhodet, så blir ingenting en overraskelse. HotXLS is en definisjonsskriver: den produserer pivotCacheDefinition, hurtigbufferpostene (cache records) og pivotTableDefinition, komplett med akser, aggregeringer, beregnede elementer og medlemmer, verdivisninger, gruppering og filtre. Excel er kalkulatoren: ved åpning oppretter (materializes) den gruppebøttene (group buckets), evaluerer de beregnede formlene, bruker showDataAs-transformasjonene og aggregerer innholdet. Verdiene en bruker ser, er Excels egne, generert ut fra instruksjonene HotXLS skrev, og det er grunnen til at hver formel og basisindeks må være korrekt på skrivetidspunktet i stedet for å bli sjekket ved rendring

To begrensninger er verdt å kjenne til før du designer rundt denne funksjonen. AddPivotTable analyserer et rektangulært A1-område med et valgfritt ark-prefiks. Kilder fra navngitte områder og eksterne arbeidsbøker ligger utenfor det motoren løser, selv om hurtigbufere lest fra en Excel-generert fil bevarer sin navngitte kilde ved lagring og gjenåpning. I tillegg blir Excel-opprettede pivottabeller som HotXLS leser fra disk, gjenskapt byte-for-byte ved lagring, slik at de typede redigeringene som beskrives her gjelder rent for pivottabeller du bygger i koden, mens eksisterende pivottabeller forblir uendret (lossless). For inndatasiden til en rapport — de validerte cellene og filtrerte tabellene pivottabellen oppsummarer — se datavalidering, AutoFilter og strukturerte tabeller

Pivottabellmodellen vist her er en del av den standard HotXLS Delphi Excel Component for Delphi og C++Builder, som leser og skriver både de XLSX-typed pivottabelldelene og de klassiske BIFF8-postene (records) fra samme objektmodell