Excel Table Slicers in Delphi: HotXLS round-trip

HotXLS maakt Excel table slicers vanuit Delphi en C++Builder met één call — Worksheet.Slicers.Add(Table, ColumnName) — en schrijft alle acht package-locaties die een slicer nodig heeft: de slicer- en slicerCache-parts, beide content-type overrides, de worksheet- en workbook-relationships met hun bijbehorende extLst-blokken, en de drawing anchor

Een slicer ziet eruit als een control die je op een sheet dropt. In het bestand is hij allesbehalve dat. Het is een verdeelde record die verspreid ligt over twee nieuwe parts, twee relationship-grafen, drie extension-blokken en één drawing anchor, en het geheel wordt als eenheid gevalideerd wanneer Excel het package opent. Mis één onderdeel en je krijgt geen gedegradeerde slicer; je krijgt de reparatiedialoog, gevolgd door een workbook waaruit de slicer stilletjes is verwijderd. Die faalmodus is wat slicers vervelend maakt om met de hand te genereren, en het is waarom de interessante engineering hier boekhouden is in plaats van rendering

Waarom raakt één slicer acht plekken in het package?

Omdat een slicer drie afzonderlijke concepten is die OPC in drie afzonderlijke huizen dwingt: de filterstate (workbook-gescoped), de view (worksheet-gescoped) en de shape (drawing-gescoped). HotXLS schrijft precies acht landing points per table slicer, en elk daarvan is dragend

• xl/slicers/slicerN.xml — één part per worksheet, die elke <slicer>-view op die sheet bevat in de namespace http://schemas.microsoft.com/office/spreadsheetml/2009/9/main
• xl/slicerCaches/slicerCacheN.xml — op workbook-niveau, één per geslicede tabelkolom, die de daadwerkelijke binding draagt
• een <Override> in [Content_Types].xml voor elk van die twee parts, met application/vnd.ms-excel.slicer+xml en application/vnd.ms-excel.slicerCache+xml (ECMA-376 Part 2 vereist een content type voor elke part, en Excel weigert het package zonder)
• een worksheet relationship van het type .../office/2007/relationships/slicer, plus een <x14:slicerList>-extension binnen de worksheet-extLst die naar die rid wijst
• een workbook relationship van het type .../office/2007/relationships/slicerCache, plus een <x15:slicerCaches>-extension binnen de workbook-extLst die dezelfde rids opsomt
• een mc:AlternateContent-anchor in de sheet drawing die de slicer zijn positie en grootte geeft
• een <x15:tableSlicerCache>-blok in de cache part die hem aan een table id en een table column id bindt

De extension-blokken gebruiken het standaard extLst-mechanisme van ECMA-376 Part 1 §18.2.10: elke <ext> draagt een uri-attribuut dat de feature identificeert, en readers die de GUID niet herkennen slaan het blok over. HotXLS onderscheidt table slicers van pivot slicers alleen op die GUID — {3A4CF648-6AED-40f4-86FF-DC5316D8AED3} voor de tabel-slicerList, {A8765BA9-456A-4dab-B4F3-ACF838C121DE} voor de pivot-variant, en dienovereenkomstig {46BE6895-7355-4a93-B00E-2C351335B9C9} en {BBE1A952-AA13-448e-AADC-164F8A28A991} voor de twee workbook-slicerCaches-extensions. De tabelbinding zelf leeft onder {2F2917AC-EB37-4324-AD4E-5DD8C200BD13}. Die constanten worden in de unit blootgesteld als XlsxExtUriSlicerListX15, XlsxExtUriWbSlicerCachesX15 en XlsxExtUriTableSlicerCache, zodat niets in het write-pad een magic string draagt

Welke parts kunnen verbatim worden afgespeeld, en welke moeten herbouwd worden?

HotXLS beantwoordt dat met één regel: een part mag zijn originele bytes alleen afspelen als niets waarnaar hij verwijst tijdens een save hernummerd kan worden. Table slicer caches zakken voor die test, dus HotXLS herbouwt ze bij elke save onvoorwaardelijk uit het model — zelfs wanneer de gebruiker de slicer nooit heeft aangeraakt. De reden is tableId: het <x15:tableSlicerCache>-element identificeert zijn tabel via het id-attribuut van <table>, en HotXLS hernummerd table ids workbook-globaal bij het schrijven. Een vroege implementatie sloeg de cache part bij het openen op als ruwe bytes en duwde ze bij de save weer terug, wat de juiste strategie is voor de meeste opake parts. Dat leverde bestanden op die in het gangbare geval schoon openden en in het interessante geval een reparatie triggerden: voeg een tabel toe op een eerdere sheet, sla op, en de cache wijst nu naar een table id dat van iets anders is, of van helemaal niets. De bug is onzichtbaar in een gewone open-en-save-regressietest omdat de ids toevallig niet verschuiven

Pivot-vormige caches kiezen de tegenovergestelde route. Hun payload is <pivotTables> plus een <x14:data>-blok dat cached items, sort state en item indices beschrijft — een structuur zonder hernummering aan HotXLS-kant, en een die duur is om getrouw te modelleren. Die worden als ruwe bytes bewaard en exact afgespeeld, met TXLSXSlicerCache.IsPivot en IsTableCache om ze uit elkaar te houden. De slicer-views volgen een derde beleid, dirty tracking: een onaangeraakt <slicer>-element en zijn drawing anchor spelen verbatim af, en zodra een view op de sheet wordt gewijzigd wordt de hele sheet part opnieuw uit het model gegenereerd. Dat is de algemene vorm van het oordeel — verbatim afspelen waar referenties stabiel zijn, modelherbouw waar de writer identiteit opnieuw toewijst, dirty tracking waar beide acceptabel zijn — en het geldt ruim voorbij slicers, zoals de lossless round-trip notities over themes, extLst-blokken en calcChain in meer detail doorwerken

Een table slicer maken vanuit Delphi

TXLSXSlicers.Add is de hele creatie-API. Met een TXLSXTable en een kolomnaam materialiseert hij de table column ids, zoekt of maakt hij de ondersteunende TXLSXSlicerCache, reserveert de bijbehorende defined name, en ankert een view rechts van de tabel. De overload met twee argumenten kiest de plaatsing; die met zes argumenten neemt een expliciete 1-based inclusieve box

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Slicer: TXLSXSlicer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('sales.xlsx');
    Sheet := Book.Sheets[0];

    // Standaardplaatsing: twee kolommen vrij van de tabel,
    // 13 rijen hoog bij de 19pt standaard rijhoogte
    Slicer := Sheet.Slicers.Add(Sheet.Tables[0], 'Region');
    Slicer.Caption   := 'Sales region';
    Slicer.StyleName := 'SlicerStyleLight1';
    Slicer.ColumnCount := 2;

    Book.SaveAs('sales-sliced.xlsx');
  finally
    Book.Free;
  end;
end;

Twee naamgevingsregels zijn het weten waard omdat het Excel-conventies zijn en geen HotXLS-uitvindingen. De cache heet Slicer_<column> met niet-identificator-tekens omgevouwen naar underscores, en botsingen krijgen een numeriek suffix — Slicer_Region, dan Slicer_Region1, enzovoort. En elke cache-naam wordt ook gereserveerd als workbook defined name met als formule #N/A, wat TXLSXSlicers.ReserveCacheDefinedName doet. Excel maakt die naam zelf aan, en dat ertoe doet: cache-namen en defined names delen één namespace, dus een workbook dat al een naam Slicer_Region heeft, zou anders bij de volgende save botsen. Als je tabellen en validatie samen bouwt, behandelen de defined-name-interacties in het artikel over data validation, AutoFilter en tabellen de aangrenzende gevallen. Delen volgt dezelfde regel die Excel gebruikt. TXLSXSlicerCaches.FindForTableColumn(ATableId, ATableColumnId) wordt geraadpleegd voordat een nieuwe cache wordt aangemaakt, dus twee slicer views over dezelfde tabelkolom — één op de dashboard-sheet, één op de detail-sheet — delen één cache part en blijven filter-gesynchroniseerd. Het verwijderen van een view draait de omgekeerde check over elke sheet in het workbook en laat de cache plus zijn gereserveerde #N/A-naam alleen vallen wanneer de laatste view die ernaar verwijst weg is

Hoe blijven relationship ids aan beide kanten stabiel?

Ze blijven stabiel omdat HotXLS de slicer-rid berekent door de exacte volgorde af te spelen waarin hij elke andere relationship uitstuurt, en de nieuwe relationships aan de staart toevoegt. Niets dat al in het bestand staat beweegt. Dat klinkt als over-engineering tot je het alternatief hebt gedeebugd. Relationship ids in een worksheet-.rels worden van binnenuit de worksheet part gerefereerd — door hyperlinks, door de drawing, door comments, door table parts — en ECMA-376 Part 2 lost ze positioneel binnen de relationship-graaf op, niet via een stabiele identiteit. Als de writer de slicer-relationship in het midden invoegt, verschuift elke referentie erna met één, en de sheet begint stilletjes naar de verkeerde part te wijzen. Dus wandelt XlsxWorksheetSlicerRelNumber door de volledige deterministische keten — externe hyperlinks, dan de comment-VML, comments, header/footer-afbeeldingen, threaded comments, de drawing, de sheet-achtergrond, printerinstellingen, tabellen, pivot tables, query tables, table single cells — en claimt pas daarna het volgende nummer voor de slicer. XlsxWorkbookSlicerCacheFirstRid doet hetzelfde aan de workbook-kant: sheets, sharedStrings, styles, VBA, externe links, theme, connections, pivot caches, person, calcChain, sheet metadata, XML maps, custom XML, en dan de slicerCache-reeks. Het algemene probleem wordt uitgewerkt in het stuk over OPC relationship resolution. De workbook-kant-extension wordt daarna uit die nummers gebouwd door XlsxBuildWorkbookSlicerCachesExtXml, en als het bronbestand al slicerCaches-extension-blokken had, worden die eerst uit de ruwe extLst verwijderd, zodat het model ze opnieuw uitzendt tegen de huidige rids in plaats van een verouderd exemplaar achter te laten

De AlternateContent-anchor en zijn legacy fallback

De drawing anchor is de enige plek waar de slicer-markup echt vreemd wordt, en die vreemdheid is bewust. HotXLS zendt de shape uit binnen een mc:AlternateContent-element volgens ECMA-376 Part 3, met een Choice-tak die Excel 2013 en later nemen en een Fallback-tak voor alles ouder

// Wat XlsxSlicerAnchorPayloadXml structureel produceert:
//
//   <mc:AlternateContent>
//     <mc:Choice xmlns:sle15=".../drawing/2012/slicer" Requires="sle15">
//       <xdr:graphicFrame>
//         <a:graphicData uri=".../drawing/2010/slicer">
//           <sle:slicer xmlns:sle=".../drawing/2010/slicer" name="Region"/>
//         </a:graphicData>
//       </xdr:graphicFrame>
//     </mc:Choice>
//     <mc:Fallback><xdr:sp>...notice shape...</xdr:sp></mc:Fallback>
//   </mc:AlternateContent>

Kijk naar de twee namespaces. De Requires="sle15"-token lost op naar de 2012-slicer-namespace, en dat is de versiepoort — een reader die 2012-slicers niet begrijpt moet de hele Choice-tak overslaan. Maar de graphicData/@uri en het sle:slicer-element erbinnen gebruiken de 2010-namespace, omdat dat de drawing-kant-identificator is die de graphic frame altijd heeft gebruikt. Deze omgekeerd zetten levert een bestand op dat Excel zonder klagen opent en dan als een leeg frame rendert, een gedenkwaardig onbehulpzaam symptoom. De Fallback-tak bevat een gewone rechthoek met een tekstbody die uitlegt dat table slicers in deze versie niet worden ondersteund — dat is wat een reader uit het 2007-tijdperk tekent in plaats van de control, en het is waarom een oudere Excel het workbook kan round-trippen zonder iets te vernielen. TXLSXSlicer stelt de geometrie beschikbaar als een 1-based inclusieve box via SetBounds(ARow1, ACol1, ARow2, ACol2) en de afzonderlijke FromRow / ToCol-properties, plus AnchorType voor het gebruikelijke move-and-size-with-cells-gedrag. HasAnchor is False voor één specifiek geval: een slicer genest binnen een group shape. Die spelen hun group anchor verbatim af en HotXLS synthetiseert er bewust geen tweede anchor voor, want dat zou de shape twee keer uitzenden

Een bestaande slicer bewerken

var
  Slicer: TXLSXSlicer;
  Cache: TXLSXSlicerCache;
begin
  Slicer := Sheet.Slicers.FindByName('Region');
  if Slicer <> nil then
  begin
    Slicer.SetBounds(2, 8, 16, 11);      // 1-based, inclusief
    Slicer.ShowCaption := 1;             // -1 = attribuut weggelaten
    Slicer.LockedPosition := 1;
    Slicer.RowHeightEmu := 19 * XlsxEmuPerPoint;   // 241300 EMU

    Cache := Slicer.Cache;
    if Cache <> nil then
    begin
      Cache.SortOrder   := 'descending';
      Cache.CrossFilter := 'showItemsWithNoData';
    end;
  end;

  // Het verwijderen van de laatste view over een kolom laat zijn cache
  // en de gereserveerde #N/A defined name meevallen
  Sheet.Slicers.Delete(Sheet.Slicers.IndexByName('Region'));

De tri-state integer properties verdienen een noot, want ze zien eruit als een modelleringsongeluk en zijn dat niet. ShowCaption, LockedPosition, ColumnCount, StartItem en CustomListSort gebruiken allemaal -1 om aan te geven dat het attribuut in het bronbestand ontbrak en bij het schrijven afwezig moet blijven, met 0 en 1 als expliciete waarden. Excel behandelt afwezig en expliciet-default op een paar plekken verschillend, en dat onderscheid bewaren is wat een byte-diff van een onaangeraakte save schoon houdt. RowHeight en ColumnWidth worden om een verwante reden als ruwe strings bewaard: Excel schrijft decimalen in die EMU-attributen, en ze opnieuw door een integer parsen zou bij een round-trip precisie verliezen. RowHeightEmu is de integer-accessor voor het gemak over dezelfde opslag, met XlsxSlicerDefaultRowHeight op 241300 EMU — 19 points bij de XlsxEmuPerPoint-verhouding van 12700

Waar dit ophoudt

Twee eerlijke grenzen. Ten eerste modellen HotXLS de table slicer volledig, maar niet het aanmaken van pivot slicers — je kunt een workbook met pivot slicers openen, hun identiteit lezen, ze intact houden over een save heen en ze verwijderen, maar de cache-kant speelt bytes af en er is geen API om er een vanaf nul te bouwen. Ten tweede filtert een slicer zelf niets. Het is een UI over een tabelkolom, en de zichtbare-rijen-state die hij aanstuurt is Excel-kant; HotXLS schrijft de control en de binding, geen berekend filterresultaat. Als je rijen echt gefilterd in het opgeslagen bestand wilt, zet dan ook de table AutoFilter aan. Elk attribuut dat HotXLS niet modelt op <slicer> of <x15:tableSlicerCache> wordt ongewijzigd meegedragen, dus een slicer uit een nieuwere Excel-build herserialiseert met zijn onbekende attributen intact, zelfs nadat je hem verplaatst. Dat is de eigenschap die het dirty-rebuild-pad veilig maakt om te nemen: herbouwen uit het model is alleen acceptabel als het model een superset is van wat het heeft geparsed. Het GUID-gelabelde extension-mechanisme uit ECMA-376 Part 1 §18.2.10, en de slicer part-definities in [MS-XLSX], maken die garantie in de eerste plaats uitdrukbaar

Table slicers zitten in de HotXLS Delphi Component, die XLSX, XLSM en legacy BIFF-workbooks leest en schrijft van Delphi 5 tot en met de huidige release, plus C++Builder en Free Pascal, zonder Excel-installatie en zonder OLE. De volledige API-referentie en een trial build staan op de productpagina van de HotXLS Delphi Spreadsheet Component