HotXLS bouwt en vernieuwt systeemeigen XLSX-draaitabellen vanuit Delphi en C++Builder zonder dat Excel is geïnstalleerd en zonder COM-automatisering op de machine. U roept AddPivotTable aan met een bronbereik, sleept velden naar de rij-, kolom-, pagina- en gegevensassen, voegt berekende items of een procent-van-totaal weergave toe, en de component schrijft de pivotCacheDefinition- en pivotTableDefinition-onderdelen die Excel opent als een live, vernieuwbare draaitabel
Het scenario dat dit de moeite waard maakt is een rapportageserver. U genereert honderden werkmappen per nacht, die elk een draaitabel bevatten die één account samenvat, en volgende maand veranderen de cijfers en moet elk bestand de nieuwe bronrijen weerspiegelen. Excel aansturen vanuit een Windows-service is kwetsbaar en niet gelicentieerd voor servergebruik, en het handmatig schrijven van de draaitabel-XML is een specificatie-archeologisch project dat nooit ophoudt. HotXLS zit tussen die twee doodlopende wegen in: een getypeerd objectmodel over the OOXML-draaitabelonderdelen, zodat dezelfde Pascal die de cellen vult ook de draaitabel declareert en de cache ervan in hetzelfde proces opnieuw opbouwt
Hoe maakt u in Delphi een draaitabel zonder Excel?
U maakt er een aan met één enkele aanroep en plaatst vervolgens velden op assen. AddPivotTable accepteert het bronbereik in A1-notatie, the doelcel waar de tabel wordt verankerd, en een naam; het parseert het bereik, scant elke kolom om het datatype ervan af te leiden, bouwt een draaitabelcache (of hergebruikt er een die al aan hetzelfde bereik is gekoppeld), en retourneert een TXLSPivotTable waarvan de velden allemaal buiten de assen beginnen. Van daaruit bepalen de handige methoden AddRowField, AddColumnField, AddPageField en AddDataFieldByName de lay-out, en elk gegevensveld krijgt een van de elf aggregaties uit de TXLSPivotAggregation-opsomming (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;Waarom de cache en de tabel twee afzonderlijke onderdelen zijn
Een draaitabel bestaat eigenlijk uit twee artefacten die naar elkaar verwijzen, en het begrijpen van die splitsing is belangrijk. De pivotCacheDefinition is de momentopname van de gegevens: een worksheetSource die naar het bronbereik verwijst, en één cacheField per kolom die de unieke waarden van die kolom (de sharedItems) plus afgeleide grenzen bevat. De pivotTableDefinition is de weergave: welk cacheveld op welke as staat, de gegevensvelden en hun aggregaties, de lay-out-toggles. De weergave koppelt aan de cache via de cacheId via de pivotCaches-relatie van de werkmap, precies zoals ECMA-376 Part 1 §18.10 and [MS-XLSX] dat voorschrijven
Die indirectie is geen bureaucratie, het leurt twee dingen op. Eén cache kan achter meerdere tabellen staan, dus het eenmaal vernieuwen van de cache werkt elke weergave bij die eruit is getrokken. En de cache slaat de waarden van elke kolom ontdubbeld op met een index-tabel per record in plaats van het ruwe raster, en dat is waarom het bouwen van een draaitabel het scannen van de bron betekent, niet het kopiëren van cellen. HotXLS modelleert dit op dezelfde manier voor beide engines, dus de bovenstaande code is bijna identiek aan het klassieke pad dat is gedocumenteerd bij de binaire SX-recordlay-out achter klassieke .xls-draaitabellen. Als uw bron op een ander blad staat of u deze aanspreekt via een naam, de rangeprefix en gedefinieerde namen en cross-sheet-verwijzingen volgen de gebruikelijke A1-regels, waarbij bladnamen tussen aanhalingstekens zijn toegestaan voor namen die spaties bevatten
Berekende items zijn geen berekende velden
Deze drie termen benoemen drie verschillende dingen, en ze verwarren is de klassieke draaitabelfout. Een berekend item leeft binnen één veld en combineert de eigen items van dat veld op naam, dus binnen een regio-veld kunt u een synthetische CoreMarkets-rij definiëren die gelijk is aan North plus South; HotXLS legt dit bloot als TXLSPivotField.AddCalculatedItem en schrijft een <calculatedItem> onder de <calculatedItems> van dat veld. Een berekend veld is anders: het is een nieuwe waarde die is afgeleid van andere kolommen, zoals Marge uit Omzet en Kosten, en het wordt gedragen als een formule op een cacheveld via TXLSPivotCacheField.Formula. Een berekend lid, toegevoegd met TXLSPivotTable.AddCalculatedMember, is een aangepast lid op tabelniveau dat kan fungeren als een meting (lidtype data) of een dimensielid, vooral van belang voor draaitabellen in OLAP-vorm
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;Eén eerlijke grens geldt voor alle drie. HotXLS schrijft de formule-tekst in de definitie-XML; het evalueert deze niet. Excel berekent het berekende item, veld of lid wanneer het het bestand opent, op dezelfde manier als het alle aggregaten in het raster berekent. HotXLS schrijft de instructies, niet de resultaten, dus de formules die u opgeeft moeten geldige draaitabelformules zijn in de eigen syntactische vorm van Excel, waarbij wordt verwezen naar veld- en itemnamen zoals Excel dat zou doen
Hoe toont u waarden als een percentage van het totaal?
U stelt de waardeweergavemodus in op het gegevensveld in plaats van zelf de getallen te transformeren. TXLSPivotDataField.ShowDataAs accepteert de TXLSPivotShowDataAs-opsomming, die de OOXML ST_ShowDataAs-waarden weerspiegelt: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal en xlpsdaIndex. Een veelgebruikte truc is om dezelfde bronkolom tweemaal op de gegevensas te plaatsen, eenmaal als een ruwe som en eenmaal als een aandeel in het eindtotaal, zodat het rapport zowel het getal als het gewicht ervan toont
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 heeft geen basis nodig omdat het relatief is ten opzichte van het eindtotaal, maar de item-relatieve modi hebben dat wel. xlpsdaDifference, xlpsdaPercentDiff en xlpsdaRunTotal vereisen BaseField (de cacheveld-index waarover de vergelijking loopt) en, voor de op items verankerde vormen, een BaseItem-index, waarbij $7FFD staat voor de (Alle) sentinel. HotXLS schrijft deze als de attributen <dataField showDataAs="percentOfTotal" baseField="N" baseItem="M"/> en laat de berekening, net als bij de berekende formules, over aan Excel
Datums en getallen groeperen, en paginaveldfilters
Groepering wordt geconfigureerd op het cacheveld, niet op het draaitabelveld, omdat het de manier verandert waarop het brondomein wordt ingedeeld (bucketed). Stel HasGroup := True in op het cacheveld dat wordt geretourneerd door Cache.FindFieldByName, en kies vervolgens de numerieke-bereikknoppen (GroupStartNum, GroupEndNum, GroupInterval) of de datum-hiërarchievlaggen (GroupByDate met GroupMonths, GroupQuarters, GroupYears, en een GroupStartDate / GroupEndDate-bereik). HotXLS stuurt het bijbehorende element <fieldGroup><rangePr groupBy="months"/> uit, zodat een veld gegroepeerd per maand of per een 1000-brede numerieke band wordt geopend op de manier waarop Excel het zou hebben gegroepeerd
Paginavelden zijn de filter-dropdowns boven de tabel. AddPageField plaatst een veld op de pagina-as, en PageItemIndex selecteert vooraf één cache-item, standaard ingesteld op xlPageItemAll ($7FFD, wat (Alles) betekent). Om een lezer in staat te stellen meerdere items tegelijk aan te vinken, stelt u MultipleItemSelectionAllowed := True in, wat HotXLS schrijft als <pivotField multipleItemSelectionAllowed="1"/>. Voor criteria die verder gaan dan een handmatige selectie, bevat elk veld een getypeerde Filters-collectie die de OOXML ST_FilterType-families omvat — aantal-, percentage-, som-, bijschrift-, waarde- en datumfilters — waarbij elk item een filtertype koppelt aan de bijbehorende vergelijkingswaarden
Hoe vernieuwt u een draaitabelcache wanneer de bron verandert?
RefreshPivotCache op TXLSXWorkbook scant het bronbereik opnieuw en bouwt de cache ter plaatse opnieuw op, wat een batch-pijplijn nodig heeft nadat deze de onderliggende rijen heeft bewerkt. Geef de cache-id door, en de methode leest elke cel in het bronbereik opnieuw (koptekst op de eerste rij, gegevens vanaf de tweede), bouwt het domein met gedeelde items van elk veld opnieuw op door de waarden opnieuw te ontdubbelen en de grenzen per type opnieuw af te leiden, en schrijft de item-indices per record opnieuw. Het retourneert 1 bij succes en -1 wanneer de cache-id onbekend is of het bronblad ontbreekt
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;De semantische grens hier is de moeite waard om duidelijk te vermelden. Voordat deze methode bestond, vertrouwde HotXLS op de vlag refreshOnLoad="1" en liet al het vernieuwingswerk over aan Excel bij de volgende keer openen, wat prima is wanneer een mens het bestand opent, maar nutteloos in een headless pijplijn die correcte gegevens moet overdragen. RefreshPivotCache maakt de opgeslagen cache in-process actueel, en omdat tabellen via de id aan een cache binden, ziet elke draaitabel die uit die cache is getrokken de update. Wat het niet doet, is het indelen of aggregeren van het zichtbare raster — Excel herberekent de gerenderde tabel nog steeds op basis van de vernieuwde cache wanneer het bestand wordt geopend
Wat HotXLS schrijft en wat Excel berekent
Houd de taakverdeling in het oog en niets zal u verrassen. HotXLS schrijft de definities: het produceert de pivotCacheDefinition, the cache-records en de pivotTableDefinition, compleet met assen, aggregaties, berekende items en leden, waardeweergaven, groeperingen en filters. Excel is de rekenmachine: bij het openen materialiseert het de groepsindelingen (buckets), evalueert het de berekende formules, past het de showDataAs-transformaties toe en aggregeert het de hoofdtekst. De waarden die een gebruiker ziet zijn die van Excel, geproduceerd op basis van de instructies die HotXLS heeft geschreven. Daarom moet elke formule en basisindex correct zijn op het moment van schrijven in plaats van te worden gecontroleerd op het moment van renderen
Twee limieten zijn de moeite waard om te weten voordat u om deze functie heen ontwerpt. AddPivotTable parseert een rechthoekig A1-bereik met een optioneel bladvoorvoegsel; bronnen met een benoemd bereik en externe werkmappen vallen buiten wat de builder oplost, hoewel caches die worden gelezen uit een door Excel gemaakt bestand hun bron met benoemd bereik behouden bij een round-trip. En door Excel gemaakte draaitabellen die HotXLS van de schijf leest, worden byte-voor-byte herhaald bij het opslaan, zodat de getypeerde bewerkingen die hier worden beschreven clean van toepassing zijn op draaitabellen die u in code bouwt, terwijl bestaande draaitabellen lossless blijven. Voor de invoerzijde van een rapport — de gevalideerde cellen en gefilterde tabellen die de draaitabel samenvat — zie datavalidatie, AutoFilter en gestructureerde tabellen
Het getoonde draaitabelmodel maakt deel uit van de standaard HotXLS Delphi Excel-component voor Delphi en C++Builder, die zowel de XLSX getypeerde draaitabelonderdelen als de klassieke BIFF8-records leest en schrijft vanuit hetzelfde objectmodel