Excel-draaitabellen bouwen en verversen in Delphi (HotXLS)

HotXLS bouwt en ververst native XLSX-draaitabellen vanuit Delphi en C++Builder zonder Excel-installatie en zonder COM-automatisering op de machine. U roept AddPivotTable aan met een bronbereik, laat velden op de rij-, kolom-, pagina- en data-as vallen, legt er berekende items of een percentage-van-totaal-weergave overheen, en het component schrijft de onderdelen pivotCacheDefinition en pivotTableDefinition die Excel opent als een levende, verversbare draaitabel

Het scenario dat dit de moeite waard maakt is een rapportageserver. U genereert honderden werkmappen per nacht, elk met een draaitabel die één account samenvat, en volgende maand verschuiven de cijfers en moet elk bestand de nieuwe bronrijen weerspiegelen. Excel aansturen vanuit een Windows-service is broos en niet gelicentieerd voor servergebruik, en de draaitabel-XML met de hand schrijven is een archeologisch specificatieproject dat nooit eindigt. HotXLS zit tussen die twee doodlopende wegen in: een getypeerd objectmodel over de OOXML-draaitabelonderdelen, zodat dezelfde Pascal die de cellen vult ook de draaitabel declareert en haar cache in hetzelfde proces herbouwt

Hoe maakt u een draaitabel in Delphi zonder Excel?

U maakt er een met één aanroep en plaatst daarna velden op assen. AddPivotTable neemt het bronbereik in A1-notatie, de doelcel waar de tabel wordt verankerd, en een naam; het parseert het bereik, scant elke kolom om haar gegevenstype af te leiden, bouwt een draaitabelcache (of hergebruikt er een die al aan hetzelfde bereik is gebonden), en retourneert een TXLSPivotTable waarvan alle velden buiten de assen beginnen. Vanaf daar bedraden de gemaksmethoden AddRowField, AddColumnField, AddPageField en AddDataFieldByName de opmaak, en elk dataveld neemt een van elf aggregaties uit de enum 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];                 // rapportblad (vanaf 1 in de XLSX-motor)

    // Bron A1:E500 op het blad 'Data'; veranker de draaitabel op rij 3, kolom 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;

Waarom de cache en de tabel twee aparte onderdelen zijn

Een draaitabel is eigenlijk twee artefacten die naar elkaar verwijzen, en de scheiding begrijpen is wat de rest hiervan helder houdt. De pivotCacheDefinition is de datamomentopname: een worksheetSource die naar het bronbereik wijst, en één cacheField per kolom met de afzonderlijke waarden van die kolom (haar sharedItems) plus afgeleide grenzen. De pivotTableDefinition is het beeld: welk cacheveld op welke as zit, de datavelden en hun aggregaties, de opmaakschakelaars. Het beeld bindt aan de cache via cacheId door de pivotCaches-relatie van de werkmap, precies zoals ECMA-376 deel 1 §18.10 en [MS-XLSX] het uiteenzetten

Die omweg is geen bureaucratie, zij koopt twee dingen. Eén cache kan meerdere tabellen ondersteunen, dus de cache één keer verversen werkt elk beeld bij dat eruit is getrokken. En de cache slaat de waarden van elke kolom ontdubbeld op met een indextabel per record in plaats van het ruwe raster, en daarom betekent een draaitabel bouwen dat u de bron scant en geen cellen kopieert. HotXLS modelleert dit voor beide motoren op dezelfde manier, dus de bovenstaande code is bijna identiek aan het klassieke pad dat is gedocumenteerd naast de binaire SX-recordindeling achter klassieke .xls-draaitabellen. Als uw bron op een ander blad woont of u haar via een naam adresseert, volgen het bereikvoorvoegsel en gedefinieerde namen en cross-sheetverwijzingen de gebruikelijke A1-regels, met geciteerde bladnamen toegestaan voor namen die spaties bevatten

Berekende items zijn geen berekende velden

Deze drie termen benoemen drie verschillende dingen, en ze door elkaar halen is de klassieke draaitabelfout. Een berekend item leeft binnen één veld en combineert de eigen items van dat veld op naam, dus binnen een veld Region kunt u een synthetische rij CoreMarkets definiëren die gelijk is aan North plus South; HotXLS stelt dat beschikbaar als TXLSPivotField.AddCalculatedItem en schrijft een <calculatedItem> onder de <calculatedItems> van dat veld. Een berekend veld is iets anders: het is een nieuwe waarde afgeleid uit andere kolommen, zoals Margin uit Revenue en Cost, en het wordt als formule op een cacheveld meegedragen via TXLSPivotCacheField.Formula. Een berekend lid, toegevoegd met TXLSPivotTable.AddCalculatedMember, is een aangepast lid op tabelniveau dat als maat kan optreden (lidtype data) of als dimensielid, vooral van belang voor OLAP-vormige draaitabellen

var
  Region: TXLSPivotField;
  Member: TXLSPivotCalculatedMember;
begin
  // Een berekend ITEM combineert items van één enkel veld op naam.
  Region := Pivot.AddRowField('Region');
  Region.AddCalculatedItem('CoreMarkets', '=North+South');

  // Een berekend LID wordt op tabelniveau gedeclareerd. Lidtype
  // 'data' markeert het als maat; een leeg type is een dimensielid.
  Member := Pivot.AddCalculatedMember('AvgTicket', '=Revenue/Units');
  Member.MemberType := 'data';
end;

Eén eerlijke grens geldt voor alle drie. HotXLS stoot de formuletekst in de definitie-XML uit; het evalueert haar niet. Excel berekent het berekende item, veld of lid wanneer het het bestand opent, op dezelfde manier waarop het elk aggregaat in het raster berekent. HotXLS schrijft de instructies, niet de resultaten, dus de formules die u aanlevert moeten geldige draaitabelformules zijn in het eigen dialect van Excel, met verwijzingen naar veld- en itemnamen zoals Excel dat zou doen

Hoe toont u waarden als percentage van het totaal?

U stelt de waardeweergavemodus in op het dataveld in plaats van de getallen zelf te transformeren. TXLSPivotDataField.ShowDataAs neemt de enum TXLSPivotShowDataAs, die de OOXML-waarden ST_ShowDataAs spiegelt: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal en xlpsdaIndex. Een gangbare truc is dezelfde bronkolom twee keer op de data-as te plaatsen, één keer als ruwe som en één keer als aandeel in het eindtotaal, zodat het rapport zowel het getal als zijn gewicht toont

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

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

  // Itemrelatieve modi hebben een basis nodig om mee te vergelijken. Een lopend
  // totaal over 'Quarter' (cacheveldindex 3) zou luiden:
  //   Share.ShowDataAs := xlpsdaRunTotal;
  //   Share.BaseField  := 3;         // cacheveldindex waarover de transformatie loopt
  //   Share.BaseItem   := $7FFD;     // $7FFD = "(All)"
end;

xlpsdaPercentOfTotal heeft geen basis nodig omdat het relatief is aan het eindtotaal, maar de itemrelatieve modi wel. xlpsdaDifference, xlpsdaPercentDiff en xlpsdaRunTotal vereisen BaseField (de cacheveldindex waarover de vergelijking loopt) en, voor de op een item verankerde vormen, een BaseItem-index, waarbij $7FFD voor de sentinel (All) staat. HotXLS schrijft deze als de attributen <dataField showDataAs="percentOfTotal" baseField="N" baseItem="M"/> en laat, net als bij berekende formules, de rekenkunde aan Excel

Data en getallen groeperen, en paginaveldfilters

Groepering wordt op het cacheveld geconfigureerd en niet op het draaitabelveld, omdat zij verandert hoe het brondomein in bakken wordt verdeeld. Zet HasGroup := True op het cacheveld dat Cache.FindFieldByName retourneert, en kies dan ofwel de knoppen voor numerieke bereiken (GroupStartNum, GroupEndNum, GroupInterval) ofwel de vlaggen voor de datumhiërarchie (GroupByDate met GroupMonths, GroupQuarters, GroupYears, en een span van GroupStartDate tot GroupEndDate). HotXLS stoot het bijpassende element <fieldGroup><rangePr groupBy="months"/> uit, zodat een veld dat per maand of per numerieke band van 1000 breed is gegroepeerd opent zoals Excel het gegroepeerd zou hebben

Paginavelden zijn de filterkeuzelijsten boven de tabel. AddPageField plaatst een veld op de pagina-as, en PageItemIndex selecteert vooraf één enkel cache-item, met als standaard xlPageItemAll ($7FFD, wat (All) betekent). Om een lezer meerdere items tegelijk te laten aanvinken, zet u MultipleItemSelectionAllowed := True, wat HotXLS als <pivotField multipleItemSelectionAllowed="1"/> schrijft. Voor criteria die verder gaan dan een handmatige keuze draagt elk veld een getypeerde Filters-collectie die de OOXML-families ST_FilterType beslaat — filters op aantal, percentage, som, bijschrift, waarde en datum — waarbij elke vermelding een filtertype koppelt aan haar vergelijkingswaarden

Hoe ververst u een draaitabelcache wanneer de bron verandert?

RefreshPivotCache op TXLSXWorkbook scant het bronbereik opnieuw en herbouwt de cache ter plaatse, wat een batchpijplijn nodig heeft nadat zij de onderliggende rijen heeft bewerkt. Geef de cache-id door, en de methode herleest elke cel in het bronbereik (kop op de eerste rij, data vanaf de tweede), herbouwt het domein van gedeelde items van elk veld door waarden opnieuw te ontdubbelen en de grenzen per type opnieuw af te leiden, en herschrijft de itemindices per record. Zij retourneert 1 bij succes en -1 wanneer de cache-id onbekend is of het bronblad ontbreekt

var
  Rc: Integer;
begin
  // ...de brongegevens zijn veranderd sinds de draaitabel werd gebouwd...
  Sheet := Book.Sheets[1];
  Sheet.Cells[2, 5].Value := 128000;             // een gecorrigeerd Revenue-cijfer

  // Herbouw de gedeelde items en recordindices van de cache uit de bron.
  Rc := Book.RefreshPivotCache(Pivot.CacheId);   // 1 = ververst, -1 = cache/bron ontbreekt
  if Rc = 1 then
    Book.SaveAs('sales-pivot-refreshed.xlsx');
end;

De semantische grens hier is het ronduit benoemen waard. Voordat deze methode bestond, leunde HotXLS op de vlag refreshOnLoad="1" en liet het alle verversingswerk bij de volgende opening aan Excel over, wat prima is wanneer een mens het bestand zal openen maar nutteloos in een pijplijn zonder scherm die correcte data moet overdragen. RefreshPivotCache maakt de opgeslagen cache actueel binnen het proces, en omdat tabellen via een id aan een cache binden, ziet elke draaitabel die uit die cache is getrokken de bijwerking. Wat het niet doet is het zichtbare raster opmaken of aggregeren — Excel herberekent de gerenderde tabel nog steeds uit de ververste cache wanneer het bestand opent

Wat HotXLS schrijft en wat Excel berekent

Houd de taakverdeling in beeld en niets verrast u. HotXLS is een definitieschrijver: het produceert de pivotCacheDefinition, de cacherecords en de pivotTableDefinition, compleet met assen, aggregaties, berekende items en leden, waardeweergaven, groepering en filters. Excel is de rekenmachine: bij het openen materialiseert het de groepsbakken, evalueert het de berekende formules, past het de showDataAs-transformaties toe en aggregeert het de romp. De waarden die een gebruiker ziet zijn die van Excel, geproduceerd uit de instructies die HotXLS schreef, en daarom moet elke formule en elke basisindex al bij het schrijven correct zijn in plaats van bij het renderen te worden gecontroleerd

Twee limieten zijn het kennen waard voordat u rond deze functie ontwerpt. AddPivotTable parseert een rechthoekig A1-bereik met een optioneel bladvoorvoegsel; bronnen op basis van benoemde bereiken en externe werkmappen vallen buiten wat de bouwer oplost, hoewel caches die uit een door Excel geschreven bestand worden gelezen hun benoemde-bereikbron bij de rondgang behouden. En door Excel gemaakte draaitabellen die HotXLS van schijf leest worden bij het opslaan byte voor byte opnieuw afgespeeld, dus de hier beschreven getypeerde bewerkingen gelden zuiver voor draaitabellen die u in code bouwt terwijl bestaande draaitabellen verliesloos blijven. Voor de invoerkant van een rapport — de gevalideerde cellen en gefilterde tabellen die de draaitabel samenvat — zie gegevensvalidatie, AutoFilter en gestructureerde tabellen

Het hier getoonde draaitabelmodel maakt deel uit van het standaard HotXLS Delphi Excel Component voor Delphi en C++Builder, dat zowel de getypeerde XLSX-draaitabelonderdelen als de klassieke BIFF8-records vanuit hetzelfde objectmodel leest en schrijft