Excel-Tabellen-Slicer in Delphi: HotXLS-Round-Trip

HotXLS erstellt Excel-Tabellen-Slicer aus Delphi und C++Builder mit einem Aufruf, Worksheet.Slicers.Add(Table, ColumnName), und schreibt alle acht Package-Stellen, die ein Slicer braucht: die Slicer- und SlicerCache-Parts, beide Content-Type-Overrides, die Worksheet- und Workbook-Relationships mit ihren passenden extLst-Blöcken und den Drawing-Anker

Ein Slicer sieht aus wie ein Control, das Sie auf ein Blatt werfen. In der Datei ist er das Gegenteil. Er ist ein verteilter Record über zwei neue Parts, zwei Relationship-Graphen, drei Erweiterungsblöcke und einen Drawing-Anker, und das Ganze wird als Einheit validiert, wenn Excel das Package öffnet. Lassen Sie ein Stück weg, und Sie bekommen keinen degradierten Slicer; Sie bekommen den Reparatur-Dialog, gefolgt von einer Arbeitsmappe, in der der Slicer still entfernt wurde. Dieser Fehlermodus macht Slicer lästig, von Hand zu emittieren, und er ist der Grund, warum die interessante Ingenieursarbeit hier Buchhaltung ist statt Rendering

Warum fasst ein Slicer acht Stellen im Package an?

Weil ein Slicer drei getrennte Konzepte ist, die OPC in drei getrennte Zuhause zwingt: den Filterzustand (Workbook-Scope), die View (Worksheet-Scope) und die Form (Drawing-Scope). HotXLS schreibt exakt acht Landepunkte pro Tabellen-Slicer, und jeder einzelne trägt Last

• xl/slicers/slicerN.xml, ein Part pro Worksheet, der jede <slicer>-View auf diesem Blatt im Namespace http://schemas.microsoft.com/office/spreadsheetml/2009/9/main hält
• xl/slicerCaches/slicerCacheN.xml, auf Workbook-Ebene, einer pro gesliceter Tabellenspalte, mit der eigentlichen Bindung
• ein <Override> in [Content_Types].xml für jeden dieser beiden Parts, mit application/vnd.ms-excel.slicer+xml und application/vnd.ms-excel.slicerCache+xml (ECMA-376 Part 2 verlangt für jeden Part einen Content-Type, und Excel weigert sich ohne sie)
• eine Worksheet-Relationship vom Typ .../office/2007/relationships/slicer, plus eine <x14:slicerList>-Erweiterung im Worksheet-extLst, die auf diese rid zeigt
• eine Workbook-Relationship vom Typ .../office/2007/relationships/slicerCache, plus eine <x15:slicerCaches>-Erweiterung im Workbook-extLst, die dieselben rids listet
• ein mc:AlternateContent-Anker im Blatt-Drawing, der dem Slicer Position und Größe gibt
• ein <x15:tableSlicerCache>-Block im Cache-Part, der ihn an eine Tabellen-id und eine Tabellenspalten-id bindet

Die Erweiterungsblöcke nutzen den Standard-extLst-Mechanismus aus ECMA-376 Part 1 §18.2.10: Jedes <ext> trägt ein uri-Attribut, das das Feature identifiziert, und Reader, die die GUID nicht erkennen, überspringen den Block. HotXLS unterscheidet Tabellen-Slicer von Pivot-Slicern allein über diese GUID, {3A4CF648-6AED-40f4-86FF-DC5316D8AED3} für die Tabellen-slicerList, {A8765BA9-456A-4dab-B4F3-ACF838C121DE} für die Pivot-Variante, und entsprechend {46BE6895-7355-4a93-B00E-2C351335B9C9} und {BBE1A952-AA13-448e-AADC-164F8A28A991} für die zwei Workbook-slicerCaches-Erweiterungen. Die Tabellenbindung selbst lebt unter {2F2917AC-EB37-4324-AD4E-5DD8C200BD13}. Diese Konstanten sind in der Unit als XlsxExtUriSlicerListX15, XlsxExtUriWbSlicerCachesX15 und XlsxExtUriTableSlicerCache offengelegt, also trägt nichts im Schreibpfad einen Magic String

Welche Parts können wortwörtlich replaien, und welche müssen neu gebaut werden?

HotXLS beantwortet das mit einer einzigen Regel: Ein Part darf seine Originalbytes nur replaien, wenn nichts, das er referenziert, beim Speichern umnummeriert werden kann. Tabellen-Slicer-Caches fallen durch diesen Test, also baut HotXLS sie bei jedem Save bedingungslos aus dem Modell neu, sogar wenn der Benutzer den Slicer nie angefasst hat. Der Grund ist tableId: Das <x15:tableSlicerCache>-Element identifiziert seine Tabelle über das id-Attribut von <table>, und HotXLS nummeriert Tabellen-ids beim Schreiben workbook-global um. Eine frühe Implementierung speicherte den Cache-Part beim Öffnen als rohe Bytes und schob sie beim Save wieder heraus, die richtige Strategie für die meisten opaken Parts. Sie erzeugte Dateien, die im gewöhnlichen Fall sauber öffneten, und löste im interessanten Fall eine Reparatur aus: Fügen Sie auf einem früheren Blatt eine Tabelle hinzu, speichern Sie, und der Cache zeigt jetzt auf eine Tabellen-id, die zu etwas anderem gehört oder zu gar nichts. Der Bug ist in einem schlichten Open-and-Save-Regressionstest unsichtbar, weil die ids zufällig nicht wandern

Pivot-förmige Caches nehmen die entgegengesetzte Route. Ihre Payload ist <pivotTables> plus ein <x14:data>-Block, der gecachte Items, Sortierzustand und Item-Indizes beschreibt, eine Struktur ohne HotXLS-seitige Umnummerierung und eine, die teuer treu zu modellieren ist. Die bleiben rohe Bytes und werden exakt replaied, wobei TXLSXSlicerCache.IsPivot und IsTableCache die beiden unterscheiden. Die Slicer-Views folgen einer dritten Politik, Dirty-Tracking: Ein unberührtes <slicer>-Element und sein Drawing-Anker replaien wortwörtlich, und in dem Moment, wo eine View auf dem Blatt verändert wird, wird der ganze Blatt-Part aus dem Modell neu erzeugt. Das ist die generelle Form des Urteils, wortwörtliches Replay, wo Referenzen stabil sind, Modell-Rebuild, wo der Writer Identität neu vergibt, Dirty-Tracking, wo beides akzeptabel ist, und es gilt weit über Slicer hinaus, wie die Lossless-Round-Trip-Notizen zu Themes, extLst-Blöcken und calcChain vertieft durcharbeiten

Einen Tabellen-Slicer aus Delphi erstellen

TXLSXSlicers.Add ist die ganze Erstellungs-API. Gegeben eine TXLSXTable und einen Spaltennamen materialisiert sie die Tabellenspalten-ids, findet oder erzeugt den dahinterliegenden TXLSXSlicerCache, reserviert den passenden definierten Namen und verankert eine View rechts von der Tabelle. Die Zwei-Argument-Überladung wählt die Platzierung; die Sechs-Argument-Variante nimmt eine explizite 1-basierte inklusive Box

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

    // Standard-Platzierung: zwei Spalten Abstand zur Tabelle,
    // 13 Zeilen hoch bei der 19pt-Standardzeilenhöhe
    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;

Zwei Namensregeln lohnen das Wissen, weil sie Excel-Konventionen sind statt HotXLS-Erfindungen. Der Cache heißt Slicer_<Spalte>, wobei Nicht-Identifier-Zeichen zu Unterstrichen gefaltet werden, und Kollisionen hängen ein numerisches Suffix an, Slicer_Region, dann Slicer_Region1 und so weiter. Und jeder Cache-Name wird auch als Workbook-definierter Name reserviert, dessen Formel #N/A ist, genau das, was TXLSXSlicers.ReserveCacheDefinedName tut. Excel erzeugt diesen Namen selbst, und er zählt: Cache-Namen und definierte Namen teilen einen Namespace, also würde eine Arbeitsmappe, die schon einen Namen Slicer_Region hat, beim nächsten Save kollidieren. Wenn Sie Tabellen und Validierung zusammen bauen, decken die definierten-Namen-Interaktionen in dem Artikel über Datenvalidierung, AutoFilter und Tabellen die Nachbarfälle ab. Sharing folgt derselben Regel wie bei Excel. TXLSXSlicerCaches.FindForTableColumn(ATableId, ATableColumnId) wird konsultiert, bevor ein neuer Cache erzeugt wird, also teilen sich zwei Slicer-Views über dieselbe Tabellenspalte, eine auf dem Dashboard-Blatt, eine auf dem Detail-Blatt, einen einzigen Cache-Part und bleiben filtersynchronisiert. Das Löschen einer View läuft die umgekehrte Prüfung über jedes Blatt der Arbeitsmappe und lässt den Cache plus seinen reservierten #N/A-Namen erst fallen, wenn die letzte referenzierende View weg ist

Wie bleiben Relationship-ids an beiden Enden stabil?

Sie bleiben stabil, weil HotXLS die Slicer-rid berechnet, indem es die exakte Reihenfolge replaied, in der es jede andere Relationship emittiert, und die neuen Relationships am Schwanz anhängt. Nichts, das schon in der Datei ist, bewegt sich. Das klingt nach Over-Engineering, bis Sie die Alternative debuggt haben. Relationship-ids in einer Worksheet-.rels werden von innen im Worksheet-Part referenziert, von Hyperlinks, vom Drawing, von Kommentaren, von Tabellen-Parts, und ECMA-376 Part 2 löst sie positionsbezogen innerhalb des Relationship-Graphen auf, nicht über eine stabile Identität. Wenn der Writer die Slicer-Relationship in die Mitte einfügt, verschiebt sich jede Referenz danach um eins, und das Blatt zeigt still auf den falschen Part. Also läuft XlsxWorksheetSlicerRelNumber die volle deterministische Kette, externe Hyperlinks, dann das Kommentar-VML, Kommentare, Header/Footer-Bilder, Threaded Comments, das Drawing, den Blatt-Hintergrund, Druckeinstellungen, Tabellen, Pivot-Tabellen, Query-Tabellen, Tabellen-Einzelzellen, ab und beansprucht erst danach die nächste Nummer für den Slicer. XlsxWorkbookSlicerCacheFirstRid macht dasselbe auf der Workbook-Seite: Blätter, sharedStrings, Styles, VBA, externe Links, Theme, Connections, Pivot-Caches, Person, calcChain, Sheet-Metadaten, XML-Maps, Custom-XML, dann der slicerCache-Lauf. Das generelle Problem ist in dem Stück über OPC-Relationship-Auflösung durchgearbeitet. Die Workbook-seitige Erweiterung wird dann aus diesen Zahlen von XlsxBuildWorkbookSlicerCachesExtXml gebaut, und wenn die Quelldatei schon slicerCaches-Erweiterungsblöcke hatte, werden sie zuerst aus dem rohen extLst entfernt, damit das Modell sie gegen die aktuellen rids neu emittiert, statt eine veraltete Kopie zurückzulassen

Der AlternateContent-Anker und sein Legacy-Fallback

Der Drawing-Anker ist die eine Stelle, wo das Slicer-Markup wirklich seltsam wird, und die Seltsamkeit ist Absicht. HotXLS emittiert die Form innerhalb eines mc:AlternateContent-Elements nach ECMA-376 Part 3, mit einem Choice-Zweig, den Excel 2013 und neuer nehmen, und einem Fallback-Zweig für alles Ältere

// Was XlsxSlicerAnchorPayloadXml strukturell erzeugt:
//
//   <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>...Hinweisform...</xdr:sp></mc:Fallback>
//   </mc:AlternateContent>

Sehen Sie sich die zwei Namespaces an. Das Requires="sle15"-Token löst zum 2012er-Slicer-Namespace auf, das ist das Versions-Gate, ein Reader, der 2012er-Slicer nicht versteht, muss den ganzen Choice-Zweig überspringen. Aber die graphicData/@uri und das sle:slicer-Element darin verwenden den 2010er-Namespace, denn das ist der Drawing-seitige Identifier, den der Graphic Frame immer verwendet hat. Diese zu vertauschen erzeugt eine Datei, die Excel ohne Murren öffnet und dann als leeren Frame rendert, ein einprägsam unbrauchbares Symptom. Der Fallback-Zweig hält ein schlichtes Rechteck mit einem Textkörper, der erklärt, dass Tabellen-Slicer in dieser Version nicht unterstützt werden, das ist, was ein 2007er-Reader anstelle des Controls zeichnet, und es ist der Grund, warum ein älteres Excel die Arbeitsmappe round-trippen kann, ohne etwas zu zerstören. TXLSXSlicer legt die Geometrie als 1-basierte inklusive Box über SetBounds(ARow1, ACol1, ARow2, ACol2) und die Einzel-Properties FromRow / ToCol offen, plus AnchorType für das übliche Move-and-Size-with-Cells-Verhalten. HasAnchor ist False für genau einen Fall: einen Slicer, der in einer Gruppenform verschachtelt ist. Die replaien mit ihrem Gruppen-Anker wortwörtlich, und HotXLS synthetisiert bewusst keinen zweiten Anker für sie, denn das würde die Form zweimal emittieren

Einen bestehenden Slicer bearbeiten

var
  Slicer: TXLSXSlicer;
  Cache: TXLSXSlicerCache;
begin
  Slicer := Sheet.Slicers.FindByName('Region');
  if Slicer <> nil then
  begin
    Slicer.SetBounds(2, 8, 16, 11);      // 1-basiert, inklusive
    Slicer.ShowCaption := 1;             // -1 = Attribut ausgelassen
    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;

  // Das Entfernen der letzten View über einer Spalte lässt deren Cache
  // und den reservierten #N/A-definierten Namen mitfallen
  Sheet.Slicers.Delete(Sheet.Slicers.IndexByName('Region'));

Die Drei-Zustand-Integer-Properties verdienen eine Anmerkung, denn sie sehen aus wie ein Modellierungsunfall und sind keiner. ShowCaption, LockedPosition, ColumnCount, StartItem und CustomListSort nutzen alle -1, um zu bedeuten, dass das Attribut in der Quelldatei fehlte und beim Schreiben fehlen bleiben soll, mit 0 und 1 als expliziten Werten. Excel behandelt fehlend und explizit-default an ein paar Stellen unterschiedlich, und diese Unterscheidung zu erhalten ist, was einen Byte-Diff eines unberührten Saves sauber hält. RowHeight und ColumnWidth werden aus verwandtem Grund als rohe Strings gehalten: Excel schreibt Dezimalzahlen in diese EMU-Attribute, und sie durch einen Integer neu zu parsen würde beim Round-Trip Präzision verlieren. RowHeightEmu ist der Integer-Komfort-Accessor über denselben Speicher, mit XlsxSlicerDefaultRowHeight bei 241300 EMU, 19 Punkte beim XlsxEmuPerPoint-Verhältnis von 12700

Wo das aufhört

Zwei ehrliche Grenzen. Erstens modelliert HotXLS den Tabellen-Slicer vollständig, aber nicht die Pivot-Slicer-Erzeugung, Sie können eine Arbeitsmappe mit Pivot-Slicern öffnen, ihre Identität lesen, sie über einen Save intakt halten und sie löschen, aber die Cache-Seite replaied als Bytes, und es gibt keine API, einen von Grund auf zu bauen. Zweitens filtert ein Slicer von selbst nichts. Er ist eine UI über einer Tabellenspalte, und der sichtbare-Zeilen-Zustand, den er treibt, ist Excel-seitig; HotXLS schreibt das Control und die Bindung, kein berechnetes Filterergebnis. Wenn Sie Zeilen brauchen, die in der gespeicherten Datei tatsächlich gefiltert sind, setzen Sie zusätzlich den Tabellen-AutoFilter. Jedes Attribut, das HotXLS an <slicer> oder <x15:tableSlicerCache> nicht modelliert, wird unverändert durchgetragen, also re-serialisiert ein Slicer aus einem neueren Excel-Build mit seinen unbekannten Attributen intakt, auch nachdem Sie ihn bewegt haben. Das ist die Eigenschaft, die den Dirty-Rebuild-Pfad sicher macht: Aus dem Modell neu zu bauen ist nur akzeptabel, wenn das Modell eine Obermenge dessen ist, was es geparst hat. Der GUID-getaggte Erweiterungsmechanismus aus ECMA-376 Part 1 §18.2.10 und die Slicer-Part-Definitionen in [MS-XLSX] sind, was diese Garantie überhaupt erst ausdrückbar macht

Tabellen-Slicer fahren im HotXLS Delphi Component, der XLSX, XLSM und Legacy-BIFF-Arbeitsmappen von Delphi 5 bis zum aktuellen Release liest und schreibt, plus C++Builder und Free Pascal, ohne Excel-Installation und ohne OLE. Vollständige API-Referenz und ein Trial-Build stehen auf der HotXLS Delphi Spreadsheet Component Produktseite