Skapa och uppdatera Excel-pivottabeller i Delphi med HotXLS

HotXLS skapar och uppdaterar inbyggda XLSX-pivottabeller från Delphi och C++Builder utan att Excel eller COM-automatisering krävs på maskinen. Du anropar AddPivotTable med ett källområde, placerar fält på rad-, kolumn-, sid- och datamängdsaxlar, samt lägger till beräknade element eller en procent-av-totalt-visning. Komponenten skriver sedan delarna pivotCacheDefinition och pivotTableDefinition som Excel öppnar som en levande, uppdateringsbar pivottabell

Det scenario som gör detta värt besväret är en rapporteringsserver. Du genererar hundratals arbetsböcker varje natt, där varje bok innehåller en pivottabell som sammanfattar ett konto, och nästa månad ändras siffrorna så att varje fil måste återspegla de nya källraderna. Att köra Excel från en Windows-tjänst är instabilt och inte licensierat för serveranvändning, och att skriva pivottabellens XML manuellt är ett specifikations-arkeologiskt projekt som aldrig tar slut. HotXLS erbjuder en lösning mitt emellan: en typad objektmodell över OOXML-pivotdelarna, så att samma Pascal-kod som fyller cellerna också deklarerar pivottabellen och bygger om dess cache i samma process

Hur skapar du en pivottabell i Delphi utan Excel?

Du skapar en med ett enda anrop och placerar sedan fält på axlarna. AddPivotTable tar källområdet i A1-notation, målcellen där tabellen förankras och ett namn. Den analyserar området, skannar varje kolumn för att härleda dess datatyp, bygger en pivot-cache (eller återanvänder en som redan är kopplad till samma område) och returnerar en TXLSPivotTable vars fält alla startar utanför axlarna. Därifrån konfigurerar de bekväma metoderna AddRowField, AddColumnField, AddPageField och AddDataFieldByName layouten, och varje datafält tar en av elva aggregationer från TXLSPivotAggregation-uppräkningen (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;

Varför cachen och tabellen är två separata delar

En pivottabell består egentligen av två artefakter som refererar till varandra, och att förstå den uppdelningen är nyckeln till att hålla ordning på resten. pivotCacheDefinition är datamängdens ögonblicksbild: en worksheetSource som pekar på källområdet, och ett cacheField per kolumn som innehåller den kolumnens unika värden (dess sharedItems) plus härledda gränser. pivotTableDefinition är vyn: vilket cachefält som sitter på vilken axel, datafälten och deras aggregationer samt layout-inställningar. Vyn binds till cachen via cacheId genom arbetsbokens pivotCaches-relation, exakt så som ECMA-376 del 1 §18.10 och [MS-XLSX] beskriver det

Den indirektionen är inte byråkrati, den ger två fördelar. En cache kan ligga bakom flera tabeller, så att uppdatera cachen en gång uppdaterar varje vy som ritats från den. Och cachen lagrar varje kolumns värden deduplicerade med en index-tabell per post snarare än i det råa rutnätet, vilket är anledningen till att skapandet av en pivottabell innebär att man skannar källan, inte att man kopierar celler. HotXLS modellerar detta på samma sätt för båda motorerna, så koden ovan är nästan identisk med den klassiska metoden som dokumenteras tillsammans med den binära SX-postlayouten bakom klassiska .xls-pivottabeller. Om din källa finns på ett annat blad eller om du adresserar den via ett namn, följer området och definierade namn och korsbladsreferenser de vanliga A1-reglerna, med tillåtelse för citattecken runt bladnamn som innehåller mellanslag

Beräknade element är inte beräknade fält

Dessa tre termer namnger tre olika saker, och att blanda ihop dem är det klassiska pivottabellsfelet. Ett beräknat element (calculated item) lever inuti ett fält och kombinerar det fältets egna element efter namn, så inom ett region-fält kan du definiera en syntetisk CoreMarkets-rad som är lika med North plus South; HotXLS exponerar detta som TXLSPivotField.AddCalculatedItem och skriver en <calculatedItem> under det fältets <calculatedItems>. Ett beräknat fält (calculated field) är annorlunda: det är ett nytt värde härlett från andra kolumner, som Margin från Revenue och Cost, och det bärs som en formel på ett cachefält via TXLSPivotCacheField.Formula. En beräknad medlem (calculated member), som läggs till med TXLSPivotTable.AddCalculatedMember, är en anpassad medlem på tabellnivå som kan fungera som ett mått (medlemstyp data) eller en dimensionsmedlem, vilket främst är av intresse för OLAP-formade 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;

En ärlig gräns gäller för alla tre. HotXLS skriver ut formeltexten i definitions-XML; den utvärderar den inte. Excel beräknar det beräknade elementet, fältet eller medlemmen när filen öppnas, på samma sätt som den beräknar alla aggregationer i rutnätet. HotXLS skriver instruktionerna, inte resultaten, så formlerna du anger måste vara giltiga pivottabellsformler i Excels egen dialekt och referera till fält- och elementnamn som Excel förväntar sig

Hur visar du värden som en procentandel av totalen?

Du ställer in visningsläget för värdet på datafältet snarare än att själv transformera siffrorna. TXLSPivotDataField.ShowDataAs tar TXLSPivotShowDataAs-uppräkningen, som speglar OOXML-värdena för ST_ShowDataAs: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal och xlpsdaIndex. Ett vanligt knep är att placera samma källkolumn på dataaxeln två gånger, en gång som en rå summa och en gång som en andel av totalsumman, så att rapporten visar både talet och dess vikt

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 behöver ingen bas eftersom den är relativ till totalsumman, men de element-relativa lägena gör det. xlpsdaDifference, xlpsdaPercentDiff och xlpsdaRunTotal kräver BaseField (cachefält-indexet som jämförelsen körs över) och, för de element-förankrade formerna, ett BaseItem-index, där $7FFD står för (All)-markören. HotXLS skriver dessa som attributen <dataField showDataAs="percentOfTotal" baseField="N" baseItem="M"/> och överlåter på samma sätt som med beräknade formler aritmetiken till Excel

Gruppera datum och tal, samt filter för sidfält

Gruppering konfigureras på cachefältet, inte på pivotfältet, eftersom det ändrar hur källdomänen delas upp. Ställ in HasGroup := True på cachefältet som returneras av Cache.FindFieldByName, och välj sedan antingen inställningarna för numeriska intervall (GroupStartNum, GroupEndNum, GroupInterval) eller flaggorna för datumhierarki (GroupByDate med GroupMonths, GroupQuarters, GroupYears och ett GroupStartDate / GroupEndDate-intervall). HotXLS genererar det matchande elementet <fieldGroup><rangePr groupBy="months"/> så att ett fält grupperat efter månad eller ett 1000-brett numeriskt intervall öppnas på det sätt Excel skulle ha grupperat det

Sidfält (page fields) är filterrullgardinsmenyerna ovanför tabellen. AddPageField placerar ett fält på sidaxeln, och PageItemIndex förväljer ett enskilt cache-element, med standardvärdet xlPageItemAll ($7FFD, vilket betyder (All)). För att låta en läsare välja flera element samtidigt, ställ in MultipleItemSelectionAllowed := True, vilket HotXLS skriver som <pivotField multipleItemSelectionAllowed="1"/>. För villkor utöver ett manuellt val har varje fält en typad Filters-samling som spänner över familjerna för OOXML ST_FilterType — antal-, procent-, summa-, rubrik-, värde- och datumfilter, där varje post parar en filtertyp med dess jämförelsevärden

Hur uppdaterar du en pivot-cache när källan ändras?

RefreshPivotCache på TXLSXWorkbook skannar källområdet igen och bygger om cachen på plats, vilket är vad en batch-pipeline behöver efter att ha redigerat de underliggande raderna. Skicka cache-id:t så läser metoden om varje cell i källområdet (rubrik på första raden, data från den andra), bygger om varje fälts shared-item-domän genom att deduplicera värden och härleda typgränserna igen, och skriver om posternas elementindex. Den returnerar 1 vid framgång och -1 när cache-id:t är okänt eller källbladet saknas

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 semantiska gränsen här är värd att nämna tydligt. Innan denna metod fanns förlitar sig HotXLS på flaggan refreshOnLoad="1" och överlät allt uppdateringsarbete till Excel vid nästa öppning, vilket är bra när en människa ska öppna filen men värdelöst i en headless-pipeline som måste leverera korrekt data. RefreshPivotCache gör den sparade cachen aktuell direkt i processen, och eftersom tabeller binds till a cache via id ser varje pivottabell som ritats från den cachen uppdateringen. Vad den inte gör är att lägga ut eller aggregera det synliga rutnätet — Excel beräknar fortfarande den renderade tabellen från den uppdaterade cachen när filen öppnas

Vad HotXLS skriver och vad Excel beräknar

Håll arbetsfördelningen i sikte så kommer inget att överraska dig. HotXLS är en skrivare av definitioner: den producerar pivotCacheDefinition, cache-posterna och pivotTableDefinition, komplett med axlar, aggregationer, beräknade element och medlemmar, värdevisningar, gruppering och filter. Excel är kalkylatorn: vid öppning materialiserar den grupperingshinkarna, utvärderar de beräknade formlerna, tillämpar showDataAs-transformationerna och aggregerar kroppen. Värdena en användare ser är Excels, producerade utifrån instruktionerna HotXLS skrev, vilket är varför varje formel och basindex måste vara korrekta vid skrivtillfället snarare än kontrollerade vid renderingen

Två gränser är värda att känna till innan du designar runt den här funktionen. AddPivotTable analyserar ett rektangulärt A1-område med ett valfritt bladprefix; namngivna områden och externa arbetsbokskällor ligger utanför vad byggaren kan lösa, även om cacher som läses från en Excel-skapad fil bevarar sitt namngivna källområde vid tur och retur (round-trip). Och Excel-skapade pivottabeller som HotXLS läser från disk sparas byte för byte, så de typade ändringarna som beskrivs här gäller fullt ut för pivottabeller du bygger i kod medan befintliga pivottabeller förblir intakta. För indatasidan i en rapport — de validerade cellerna och filtrerade tabellerna som pivottabellen sammanfattar — se datavalidering, AutoFilter och strukturerade tabeller

Pivottabellsmodellen som visas här är en del av standardversionen av HotXLS Delphi Excel Component för Delphi och C++Builder, som läser och skriver både XLSX typade pivotdelar och klassiska BIFF8-poster från samma objektmodell