Slicer di tabella Excel in Delphi: round-trip HotXLS

HotXLS crea gli slicer di tabella di Excel da Delphi e C++Builder con una sola chiamata — Worksheet.Slicers.Add(Table, ColumnName) — e scrive tutte e otto le posizioni del package che uno slicer richiede: le parti slicer e slicerCache, entrambi gli override dei content type, le relationship del foglio e della cartella di lavoro con i rispettivi blocchi extLst, e l'anchor del drawing

Uno slicer sembra un controllo che trascini su un foglio. Nel file non è affatto così. È un record distribuito su due nuove parti, due grafi di relationship, tre blocchi di estensione e un anchor del drawing, e il tutto viene validato come un'unica unità quando Excel apre il package. Se manca un pezzo non ottieni uno slicer degradato; ottieni la finestra di riparazione e poi una cartella di lavoro con lo slicer rimosso in silenzio. È questo modo di fallire che rende gli slicer fastidiosi da emettere a mano, ed è il motivo per cui l'ingegneria interessante qui è contabilità, non rendering

Perché un singolo slicer tocca otto punti del package?

Perché uno slicer è tre concetti separati che OPC costringe in tre abitazioni separate: lo stato del filtro (a livello di cartella di lavoro), la vista (a livello di foglio) e la forma (a livello di drawing). HotXLS scrive esattamente otto punti di atterraggio per ogni slicer di tabella, e ognuno di essi è portante

• xl/slicers/slicerN.xml — una parte per foglio di lavoro, che contiene ogni vista <slicer> di quel foglio nel namespace http://schemas.microsoft.com/office/spreadsheetml/2009/9/main
• xl/slicerCaches/slicerCacheN.xml — a livello di cartella di lavoro, una per ogni colonna di tabella filtrata, che porta il binding vero e proprio
• un <Override> in [Content_Types].xml per ognuna di quelle due parti, con application/vnd.ms-excel.slicer+xml e application/vnd.ms-excel.slicerCache+xml (ECMA-376 Part 2 richiede un content type per ogni parte, e senza quelli Excel rifiuta il package)
• una relationship del foglio di tipo .../office/2007/relationships/slicer, più un'estensione <x14:slicerList> dentro l'extLst del foglio che punta a quel rid
• una relationship della cartella di lavoro di tipo .../office/2007/relationships/slicerCache, più un'estensione <x15:slicerCaches> dentro l'extLst della cartella che elenca gli stessi rid
• un anchor mc:AlternateContent nel drawing del foglio che dà allo slicer posizione e dimensione
• un blocco <x15:tableSlicerCache> dentro la parte cache che lo lega a un table id e a un table column id

I blocchi di estensione usano il meccanismo extLst standard di ECMA-376 Part 1 §18.2.10: ogni <ext> porta un attributo uri che identifica la funzionalità, e i reader che non riconoscono il GUID saltano il blocco. HotXLS distingue gli slicer di tabella dagli slicer pivot solo tramite quel GUID — {3A4CF648-6AED-40f4-86FF-DC5316D8AED3} per il slicerList di tabella, {A8765BA9-456A-4dab-B4F3-ACF838C121DE} per quello pivot, e corrispondentemente {46BE6895-7355-4a93-B00E-2C351335B9C9} e {BBE1A952-AA13-448e-AADC-164F8A28A991} per le due estensioni slicerCaches della cartella di lavoro. Il binding di tabella in sé sta sotto {2F2917AC-EB37-4324-AD4E-5DD8C200BD13}. Quelle costanti sono esposte nell'unit come XlsxExtUriSlicerListX15, XlsxExtUriWbSlicerCachesX15 e XlsxExtUriTableSlicerCache, così nel percorso di scrittura non c'è nessuna stringa magica

Quali parti si possono replicare alla lettera e quali vanno ricostruite?

HotXLS risponde con una regola sola: una parte può replicare i suoi byte originali solo se nulla di ciò che referenzia può essere rinumerato durante il salvataggio. Le cache degli slicer di tabella non superano questo test, quindi HotXLS le ricostruisce dal modello a ogni salvataggio, incondizionatamente — anche quando l'utente non ha mai toccato lo slicer. Il motivo è tableId: l'elemento <x15:tableSlicerCache> identifica la sua tabella tramite l'attributo id di <table>, e HotXLS rinumera i table id a livello di intera cartella quando scrive. Una prima implementazione teneva la parte cache come byte grezzi all'apertura e li risputava fuori al salvataggio, che è la strategia giusta per la maggior parte delle parti opache. Produceva file che si aprivano puliti nel caso comune e scatenavano una riparazione in quello interessante: aggiungi una tabella in un foglio precedente, salva, e la cache ora punta a un table id che appartiene a qualcos'altro, o a niente. Il bug è invisibile in una semplice regressione apri-e-salva perché gli id per caso non si spostano

Le cache a forma di pivot prendono la strada opposta. Il loro payload è <pivotTables> più un blocco <x14:data> che descrive gli item in cache, lo stato di ordinamento e gli indici degli item — una struttura senza rinumerazioni lato HotXLS, e costosa da modellare fedelmente. Quelle restano byte grezzi e vengono replicate esattamente, con TXLSXSlicerCache.IsPivot e IsTableCache a distinguere i due casi. Le viste dello slicer seguono una terza politica, con dirty tracking: un elemento <slicer> intatto e il suo anchor del drawing vengono replicati alla lettera, e nel momento in cui una qualsiasi vista del foglio viene modificata l'intera parte del foglio viene rigenerata dal modello. Questa è la forma generale del giudizio — replica alla lettera dove i riferimenti sono stabili, ricostruzione dal modello dove lo scrittore riassegna le identità, dirty tracking dove entrambe vanno bene — e vale ben oltre gli slicer, come approfondisce la nota sul round-trip senza perdite di temi, blocchi extLst e calcChain

Creare uno slicer di tabella da Delphi

TXLSXSlicers.Add è l'intera API di creazione. Dato un TXLSXTable e un nome di colonna materializza i table column id, trova o crea la TXLSXSlicerCache sottostante, riserva il defined name corrispondente e ancora una vista a destra della tabella. L'overload a due argomenti sceglie il posizionamento; quello a sei argomenti prende un riquadro esplicito 1-based inclusivo

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

    // Posizionamento predefinito: a due colonne di distanza dalla tabella,
    // 13 righe di altezza, con l'altezza riga predefinita di 19pt
    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;

Due regole di naming vale la pena conoscerle perché sono convenzioni di Excel, non invenzioni di HotXLS. La cache si chiama Slicer_<column> con i caratteri non identificatore ripiegati in underscore, e le collisioni appendono un suffisso numerico — Slicer_Region, poi Slicer_Region1 e così via. E ogni nome di cache viene anche riservato come defined name della cartella di lavoro con formula #N/A, ed è quello che fa TXLSXSlicers.ReserveCacheDefinedName. Excel crea quel nome da solo, e conta: i nomi delle cache e i defined name condividono un unico namespace, quindi una cartella che ha già un nome chiamato Slicer_Region andrebbe in collisione al salvataggio successivo. Se costruisci tabelle e validazione insieme, le interazioni tra defined name in l'articolo su data validation, AutoFilter e tabelle coprono i casi vicini. La condivisione segue la stessa regola che usa Excel. TXLSXSlicerCaches.FindForTableColumn(ATableId, ATableColumnId) viene consultato prima di creare una nuova cache, così due viste di slicer sulla stessa colonna di tabella — una sul foglio dashboard, una sul foglio dettagli — condividono un'unica parte cache e restano sincronizzate sui filtri. La cancellazione di una vista esegue il controllo inverso su tutti i fogli della cartella e butta via la cache più il suo nome #N/A riservato solo quando l'ultima vista che la referenzia sparisce

Come restano stabili i relationship id su entrambi i lati?

Restano stabili perché HotXLS calcola il rid dello slicer ripercorrendo l'ordine esatto in cui emette ogni altra relationship, e append le nuove relationship in coda. Niente di quello che c'è già nel file si sposta. Sembra over-engineering finché non hai debuggato l'alternativa. I relationship id in un .rels di foglio vengono referenziati dall'interno della parte del foglio — dagli hyperlink, dal drawing, dai commenti, dalle parti di tabella — e ECMA-376 Part 2 li risolve posizionalmente dentro il grafo delle relationship, non tramite un'identità stabile. Se lo scrittore inserisce la relationship dello slicer in mezzo, ogni riferimento successivo slitta di uno, e il foglio inizia silenziosamente a puntare alla parte sbagliata. Quindi XlsxWorksheetSlicerRelNumber percorre l'intera catena deterministica — hyperlink esterni, poi il VML dei commenti, i commenti, le immagini di header/footer, i commenti in threaded, il drawing, lo sfondo del foglio, le impostazioni di stampa, le tabelle, le pivot table, le query table, le singole celle di tabella — e solo allora reclama il numero successivo per lo slicer. XlsxWorkbookSlicerCacheFirstRid fa lo stesso lato cartella di lavoro: fogli, sharedStrings, stili, VBA, collegamenti esterni, tema, connessioni, pivot cache, person, calcChain, metadati dei fogli, XML map, XML personalizzato, e poi la serie delle slicerCache. Il problema generale è svolto in il pezzo sulla risoluzione delle relationship OPC. L'estensione lato cartella viene poi costruita da quei numeri da XlsxBuildWorkbookSlicerCachesExtXml, e se il file sorgente aveva già blocchi di estensione slicerCaches questi vengono prima tolti dall'extLst grezzo, così il modello li riemette contro i rid correnti invece di lasciare in giro una copia stantia

L'anchor AlternateContent e il suo fallback legacy

L'anchor del drawing è l'unico punto in cui il markup dello slicer diventa davvero strano, e la stranezza è voluta. HotXLS emette la forma dentro un elemento mc:AlternateContent secondo ECMA-376 Part 3, con un ramo Choice che prendono Excel 2013 e successivi e un ramo Fallback per tutto il resto

// Cosa produce XlsxSlicerAnchorPayloadXml, strutturalmente:
//
//   <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>

Guarda i due namespace. Il token Requires="sle15" risolve nel namespace degli slicer del 2012, che è il gate di versione — un reader che non capisce gli slicer del 2012 deve saltare l'intero ramo Choice. Ma graphicData/@uri e l'elemento sle:slicer al suo interno usano il namespace del 2010, perché è quello l'identificatore lato drawing che il graphic frame ha sempre usato. Scambiarli produce un file che Excel apre senza lamentarsi e poi renderizza come una cornice vuota, un sintomo memorabilmente poco utile. Il ramo Fallback contiene un semplice rettangolo con un corpo di testo che spiega che gli slicer di tabella non sono supportati in questa versione — è quello che un reader di epoca 2007 disegna al posto del controllo, ed è il motivo per cui un Excel vecchio può fare round-trip della cartella senza distruggere nulla. TXLSXSlicer espone la geometria come riquadro 1-based inclusivo tramite SetBounds(ARow1, ACol1, ARow2, ACol2) e le proprietà individuali FromRow / ToCol, più AnchorType per il solito comportamento muovi-e-ridimensiona-con-le-celle. HasAnchor è False in un caso specifico: uno slicer annidato dentro una group shape. Quelle replicano alla lettera il loro anchor di gruppo e HotXLS non sintetizza volutamente un secondo anchor per loro, perché farlo emetterebbe la forma due volte

Modificare uno slicer esistente

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

  // Rimuovere l'ultima vista su una colonna elimina la sua cache
  // e con esso il nome definito riservato #N/A
  Sheet.Slicers.Delete(Sheet.Slicers.IndexByName('Region'));

Le proprietà intere a tre stati meritano una nota, perché sembrano un incidente di modellazione e non lo sono. ShowCaption, LockedPosition, ColumnCount, StartItem e CustomListSort usano tutte -1 per dire che l'attributo era assente nel file sorgente e deve restare assente in scrittura, con 0 e 1 come valori espliciti. In qualche punto Excel tratta l'assente e l'esplicitamente-default in modo diverso, e preservare quella distinzione è ciò che mantiene pulito il diff byte per byte di un salvataggio intatto. RowHeight e ColumnWidth restano stringhe grezze per un motivo collegato: Excel scrive decimali in quegli attributi EMU, e riparsarli passando da un intero perderebbe precisione al round-trip. RowHeightEmu è l'accessor comodo intero sullo stesso storage, con XlsxSlicerDefaultRowHeight a 241300 EMU — 19 punti al rapporto XlsxEmuPerPoint di 12700

Dove si ferma

Due limiti onesti. Primo, HotXLS modella completamente lo slicer di tabella ma non modella la creazione degli slicer pivot — puoi aprire una cartella che contiene slicer pivot, leggere la loro identità, tenerli intatti attraverso un salvataggio ed eliminarli, ma il lato cache viene replicato come byte e non c'è un'API per costruirne uno da zero. Secondo, uno slicer non filtra nulla da solo. È una UI sopra una colonna di tabella, e lo stato delle righe visibili che pilota sta lato Excel; HotXLS scrive il controllo e il binding, non un risultato di filtro calcolato. Se ti servono righe davvero filtrate nel file salvato, imposta anche l'AutoFilter della tabella. Qualsiasi attributo che HotXLS non modella su <slicer> o <x15:tableSlicerCache> viene portato avanti invariato, così uno slicer arrivato da una build più recente di Excel si riserializza con i suoi attributi sconosciuti intatti anche dopo che l'hai spostato. È questa proprietà a rendere sicuro il percorso del rebuild sporco: ricostruire dal modello è accettabile solo se il modello è un superset di ciò che ha parsato. Il meccanismo di estensione taggato con GUID di ECMA-376 Part 1 §18.2.10, e le definizioni delle parti slicer in [MS-XLSX], sono ciò che rende esprimibile quella garanzia

Gli slicer di tabella sono inclusi nella HotXLS Delphi Component, che legge e scrive cartelle di lavoro XLSX, XLSM e BIFF legacy da Delphi 5 fino alla release corrente, più C++Builder e Free Pascal, senza installazione di Excel e senza OLE. Riferimento API completo e una build di prova sono sulla pagina prodotto della HotXLS Delphi Spreadsheet Component