Byg og opdater Excel-pivottabeller i Delphi med HotXLS

HotXLS bygger og opdaterer indfødte XLSX-pivottabeller fra Delphi og C++Builder uden Excel installeret og uden COM-automatisering på maskinen. Du kalder AddPivotTable med et kildeområde, placerer felter på række-, kolonne-, side- og dataakserne, lægger beregnede elementer eller en procent-af-total-visning ovenpå, og komponenten skriver delene pivotCacheDefinition og pivotTableDefinition, som Excel åbner som en live pivottabel, der kan opdateres

Scenariet, der gør dette besværet værd, er en rapporteringsserver. Du genererer hundredvis af projektmapper om natten, der hver bærer en pivottabel, som opsummerer én konto, og næste måned flytter tallene sig, og hver fil skal afspejle de nye kilderækker. At drive Excel fra en Windows-tjeneste er skrøbeligt og ikke licenseret til serverbrug, og at skrive pivot-XML i hånden er et evighedsprojekt med specifikationsarkæologi. HotXLS sidder mellem disse to blindgyder: En typet objektmodel over OOXML-pivotdelene, så den samme Pascal, der udfylder cellerne, også erklærer pivottabellen og genopbygger dens cache i den samme proces

Hvordan opretter du en pivottabel i Delphi uden Excel?

Du opretter en med et enkelt kald og placerer derefter felter på akser. AddPivotTable tager kildeområdet i A1-notation, destinationscellen, hvor tabellen forankres, og et navn; den fortolker området, scanner hver kolonne for at udlede dens datatype, opbygger en pivot-cache (eller genbruger en, der allerede er bundet til det samme område) og returnerer en TXLSPivotTable, hvis felter alle starter uden for aksen. Derfra forbinder bekvemmelighedsmetoderne AddRowField, AddColumnField, AddPageField og AddDataFieldByName layoutet, og hvert datafelt tager en af elleve aggregationer fra TXLSPivotAggregation-opregningen (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 cachen og tabellen er to separate dele

En pivottabel er reelt to artefakter, der refererer til hinanden, og forståelsen af opdelingen er det, der holder styr på resten af dette. pivotCacheDefinition er data-snapshotet: En worksheetSource, der peger på kildeområdet, og et cacheField pr. kolonne, der indeholder den pågældende kolonnes særskilte værdier (dens sharedItems) plus afledte grænser. pivotTableDefinition er visningen: Hvilket cachefelt der sidder på hvilken akse, datafelterne og deres aggregationer samt layout-indstillingerne. Visningen binder sig til cachen via cacheId gennem projektmappens pivotCaches-relation, præcis som ECMA-376 del 1 §18.10 og [MS-XLSX] lægger det frem

Denne indirektion er ikke bureaukrati, den køber to ting. Én cache kan understøtte flere tabeller, så en enkelt opdatering af cachen opdaterer enhver visning, der er tegnet ud fra den. Og cachen gemmer hver kolonnes værdier deduplikeret med en indekstabel pr. post i stedet for det rå gitter, hvilket er grunden til, at opbygning af en pivottabel betyder scanning af kilden, ikke kopiering af celler. HotXLS modellerer dette på samme måde for begge motorer, så koden ovenfor is næsten identisk med den klassiske sti dokumenteret sammen med det binære SX-post-layout bag klassiske .xls-pivottabeller. Hvis din kilde lever på et andet ark, eller du adresserer den via et navn, følger områdepræfikset samt definerede navne og referencer på tværs af ark de sædvanlige A1-regler, med citerede arknavne er tilladt for navne, der indeholder mellemrum

Beregnede elementer er ikke beregnede felter

Disse tre udtryk navngiver tre forskellige ting, og at blande dem sammen er den klassiske pivot-fejl. Et beregnet element (calculated item) lever inde i et enkelt felt og kombinerer dette felts egne elementer efter navn, så du inden for et Region-felt kan definere en syntetisk CoreMarkets-række lig med North plus South; HotXLS eksponerer det som TXLSPivotField.AddCalculatedItem og skriver et <calculatedItem> under feltets <calculatedItems>. Et beregnet felt (calculated field) er anderledes: Det er en ny værdi afledt af andre kolonner, som f.eks. Margin ud fra Revenue og Cost, og det bæres som en formel på et cachefelt via TXLSPivotCacheField.Formula. Et beregnet medlem (calculated member), tilføjet med TXLSPivotTable.AddCalculatedMember, er et brugerdefineret medlem på tabelniveau, der kan fungere som et mål (medlemstype data) eller et dimensionsmedlem, hovedsageligt af interesse for OLAP-formede 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 ærlig grænse gælder for alle tre. HotXLS udsender formelteksten i definitions-XML'en; den evaluerer den ikke. Excel beregner det beregnede element, felt eller medlem, når den åbner filen, på samme måde som den beregner ethvert aggregat i gitteret. HotXLS skriver instruktionerne, ikke resultaterne, så de formler, du leverer, skal være gyldige pivotformler i Excels egen dialekt, der refererer til felt- og elementnavne, som Excel ville gøre det

Hvordan viser du værdier som en procentdel af totalen?

Du indstiller værdivisningstilstanden på datafeltet i stedet for selv at transformere tallene. TXLSPivotDataField.ShowDataAs tager TXLSPivotShowDataAs-opregningen, som afspejler OOXML-ST_ShowDataAs-værdierne: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal, og xlpsdaIndex. Et almindeligt trick er at placere den samme kildekolonne på dataaksen to gange, én gang som en rå sum og én gang som en andel af hovedtotalen, så rapporten viser både tallet og dets vægt

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 har ikke brug for nogen base, fordi den er relativ til hovedtotalen, men de element-relative tilstande har. xlpsdaDifference, xlpsdaPercentDiff og xlpsdaRunTotal kræver BaseField (indekset for cachefeltet, som sammenligningen kører over) og, for de elementforankrede former, et BaseItem-indeks, hvor $7FFD står for (Alle)-skildvagten. HotXLS skriver disse som attributterne <dataField showDataAs="percentOfTotal" baseField="N" baseItem="M"/> og overlader, ligesom med beregnede formler, aritmetikken til Excel

Gruppering af datoer og tal samt sidefeltsfiltre

Gruppering konfigureres på cachefeltet, ikke på pivotfeltet, fordi det ændrer, hvordan kildedumænet opdeles. Indstil HasGroup := True på cachefeltet returneret af Cache.FindFieldByName, og vælg derefter enten de numeriske interval-indstillinger (GroupStartNum, GroupEndNum, GroupInterval) eller datohierarki-flaggene (GroupByDate med GroupMonths, GroupQuarters, GroupYears og et GroupStartDate / GroupEndDate-spand). HotXLS udsender det matchende <fieldGroup><rangePr groupBy="months"/>-element, så et felt grupperet efter måned eller efter et 1000-bredt numerisk bånd åbnes på den måde, som Excel ville have grupperet det på

Sidefelter er filter-rullelisterne over tabellen. AddPageField placerer et felt på sideaksen, og PageItemIndex forhåndsvælger et enkelt cache-element, som standard xlPageItemAll ($7FFD, hvilket betyder (Alle)). For at lade en læser markere flere elementer på én gang, skal du indstille MultipleItemSelectionAllowed := True, hvilket HotXLS skriver som <pivotField multipleItemSelectionAllowed="1"/>. For kriterier ud over et manuelt valg bærer hvert felt en typet Filters-samling, der dækker OOXML-ST_FilterType-familierne — antals-, procent-, sum-, billedtekst-, værdi- og datofiltre — hvor hver post parrer en filtertype med sine sammenligningsværdier

Hvordan opdaterer du en pivot-cache, når kilden ændres?

RefreshPivotCache på TXLSXWorkbook scanner kildeområdet igen og genopbygger cachen på stedet, hvilket er det, en batchpipeline har brug for, efter at den har redigeret de underliggende rækker. Angiv cache-id'et, og metoden genlæser hver celle i kildeområdet (overskrift på første række, data fra den anden), genopbygger hvert felts delte element-domæne ved at gen-deduplikere værdier og gen-aflede grænserne pr. type, og genskriver elementindeksene pr. post. Den returnerer 1 ved succes og -1, når cache-id'et er ukendt, 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 grænse her er værd at sige tydeligt. Før denne metode eksisterede, stolede HotXLS på refreshOnLoad="1"-flaget og overlod alt opdateringsarbejde til Excel ved næste åbning, hvilket er fint, når et menneske vil åbne filen, men ubrugeligt i en headless pipeline, der skal levere korrekte data. RefreshPivotCache gør den gemte cache aktuel in-process, og fordi tabeller binder sig til en cache via id, ser enhver pivottabel tegnet fra den cache opdateringen. Hvad den ikke gør, er at layoute eller aggregere det synlige gitter — Excel genberegner stadig den rendered tabel ud fra den opdaterede cache, når filen åbnes

Hvad HotXLS skriver, og hvad Excel beregner

Hold arbejdsdelingen for øje, og intet vil overraske dig. HotXLS er en definitions-skriver: Den producerer pivotCacheDefinition, cache-posterne og pivotTableDefinition, komplet med akser, aggregationer, beregnede elementer og medlemmer, værdivisninger, gruppering og filtre. Excel er beregneren: Ved åbning materialiserer den gruppe-bøtterne, evaluerer de beregnede formler, anvender showDataAs-transformationerne og aggregerer kroppen. De værdier, en bruger ser, er Excels, produceret ud fra de instruktioner, HotXLS skrev, hvilket er grunden til, at enhver formel og basisindeks skal være korrekte på skrivetidspunktet frem for kontrolleret på renderingstidspunktet

To begrænsninger er værd at kende, før du designer udenom denne funktion

AddPivotTable fortolker et rektangulært A1-område med et valgfrit arkpræfiks; navngivne områder og eksterne projektmappekilder er uden for, hvad byggeren løser, selvom cacher læst fra en Excel-oprettet fil bevarer deres navngivne kilde på turen frem og tilbage. Og Excel-oprettede pivottabeller, som HotXLS læser fra disken, genafspilles byte-for-byte ved gemning, så de typede redigeringer, der beskrives her, gælder rent for pivottabeller, du bygger i kode, mens eksisterende pivottabeller forbliver tabsfrie. For input-siden af en rapport — de validerede celler og filtrerede tabeller, som pivottabellen opsummerer — se datavalidering, AutoFilter og strukturerede tabeller

Pivot-modellen vist her er en del af standarden HotXLS Delphi Excel Component til Delphi og C++Builder, som læser og skriver både de XLSX-typed pivotdele og de klassiske BIFF8-poster fra den samme objektmodel