Excel-tabel-slicere i Delphi: HotXLS round-trip

HotXLS opretter Excel-tabel-slicere fra Delphi og C++Builder med ét kald — Worksheet.Slicers.Add(Table, ColumnName) — og skriver alle otte pakke-placeringer en slicer har brug for: slicer- og slicerCache-parten, begge content-type-overrides, worksheet- og workbook-relationerne med deres tilhørende extLst-blokke samt drawing-ankeret

En slicer ligner et kontrolelement du smider på et ark. I filen er den intet af den slags. Den er en distribueret post spredt over to nye parts, to relations-grafer, tre extension-blokke og ét drawing-anker, og det hele valideres som én enhed når Excel åbner pakken. Mangler du ét stykke, får du ikke en forringet slicer; du får repair-dialogen og derefter en workbook hvor sliceren er fjernet helt lydløst. Den fejl-tilstand er netop grunden til at slicere er træls at generere i hånden, og til at det interessante ingeniørarbejde her er bogholderi frem for rendering

Hvorfor rører én slicer otte steder i pakken?

Fordi en slicer er tre separate koncepter som OPC tvinger ind i tre separate hjem: filter-tilstanden (workbook-scoped), view'et (worksheet-scoped) og formen (drawing-scoped). HotXLS skriver præcis otte landingspunkter per tabel-slicer, og hvert eneste af dem er load-bearing

• xl/slicers/slicerN.xml — én part per worksheet med hvert <slicer>-view på det ark i namespace http://schemas.microsoft.com/office/spreadsheetml/2009/9/main
• xl/slicerCaches/slicerCacheN.xml — workbook-niveau, én per slicet tabelkolonne, der bærer selve bindingen
• et <Override> i [Content_Types].xml for hver af de to parts, med application/vnd.ms-excel.slicer+xml og application/vnd.ms-excel.slicerCache+xml (ECMA-376 Part 2 kræver en content type for hver part, og Excel nægter at åbne pakken uden dem)
• en worksheet-relation af typen .../office/2007/relationships/slicer, plus et <x14:slicerList>-extension inde i worksheet'ens extLst der peger på det rid
• en workbook-relation af typen .../office/2007/relationships/slicerCache, plus et <x15:slicerCaches>-extension inde i workbook'ens extLst der oplister de samme rids
• et mc:AlternateContent-anker i arkets drawing der giver sliceren dens position og størrelse
• en <x15:tableSlicerCache>-blok inde i cache-parten der binder den til et table id og et table column id

Extension-blokkene bruger standard-extLst-mekanismen fra ECMA-376 Part 1 §18.2.10: hvert <ext> bærer en uri-attribut der identificerer funktionen, og læsere der ikke genkender GUID'en springer blokken over. HotXLS skelner tabel-slicere fra pivot-slicere udelukkende på den GUID — {3A4CF648-6AED-40f4-86FF-DC5316D8AED3} for tabel-slicerList, {A8765BA9-456A-4dab-B4F3-ACF838C121DE} for pivot-udgaven, og tilsvarende {46BE6895-7355-4a93-B00E-2C351335B9C9} og {BBE1A952-AA13-448e-AADC-164F8A28A991} for de to workbook-slicerCaches-extensions. Selve tabel-bindingen ligger under {2F2917AC-EB37-4324-AD4E-5DD8C200BD13}. De konstanter er eksponeret i unit'en som XlsxExtUriSlicerListX15, XlsxExtUriWbSlicerCachesX15 og XlsxExtUriTableSlicerCache, så intet i write-stien bærer en magisk streng

Hvilke parts kan afspilles ordret, og hvilke skal bygges om?

HotXLS svarer med én enkelt regel: en part må afspille sine originale bytes kun hvis intet den refererer til kan blive omnummereret under save. Tabel-slicer-caches fejler den test, så HotXLS bygger dem om fra modellen ved hvert save, betingelsesløst — selv når brugeren aldrig har rørt sliceren. Årsagen er tableId: elementet <x15:tableSlicerCache> identificerer sin tabel via id-attributten på <table>, og HotXLS omnummererer tabel-id'er workbook-globalt når det skriver. En tidlig implementering gemte cache-parten som rå bytes ved open og skubbede dem tilbage ved save, hvilket er den rigtige strategi for de fleste opaque parts. Det gav filer der åbnede rent i det almindelige tilfælde og udløste en repair i det interessante: tilføj en tabel på et tidligere ark, gem, og cachen peger nu på et table id der tilhører noget andet — eller intet overhovedet. Fejlen er usynlig i en simpel open-and-save-regressionstest, fordi id'erne tilfældigvis ikke flytter sig

Pivot-formede caches går den modsatte vej. Deres payload er <pivotTables> plus en <x14:data>-blok der beskriver cached items, sorteringstilstand og item-indeks — en struktur uden HotXLS-side-omnummerering, og en der er dyr at modellere trofast. Dem holder man som rå bytes og afspiller præcist, med TXLSXSlicerCache.IsPivot og IsTableCache til at skelne de to. Slicer-views følger en tredje politik, dirty-tracked: et urørt <slicer>-element og dets drawing-anker afspilles ordret, og i det øjeblik et view på arket ændres, regenereres hele sheet-parten fra modellen. Det er den generelle form på vurderingen — ordret afspilning hvor referencer er stabile, model-genopbygning hvor writer'en tildeler identitet på ny, dirty tracking hvor begge dele er acceptable — og den rækker langt ud over slicere, som noterne om lossless round-trip for themes, extLst-blokke og calcChain går i dybden med

Oprettelse af en tabel-slicer fra Delphi

TXLSXSlicers.Add er hele oprettelses-API'et. Givet en TXLSXTable og et kolonnenavn materialiserer den tabelkolonne-id'erne, finder eller opretter den underliggende TXLSXSlicerCache, reserverer det matchende defined name og forankrer et view til højre for tabellen. Overload'et med to argumenter vælger placeringen; det med seks tager en eksplicit 1-baseret inklusiv boks

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

    // Standardplacering: to kolonner fri af tabellen,
    // 13 rækker høj ved standard-rækkehøjden på 19 pt
    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;

To navngivningsregler er værd at kende, fordi de er Excel-konventioner snarere end HotXLS-opfindelser. Cachen hedder Slicer_<column> med ikke-identifikator-tegn foldet om til underscores, og kollisioner får et numerisk suffiks — Slicer_Region, så Slicer_Region1 og så videre. Og hvert cache-navn reserveres også som et workbook defined name hvis formel er #N/A, hvilket er præcis det TXLSXSlicers.ReserveCacheDefinedName gør. Excel opretter selv det navn, og det betyder noget: cache-navne og defined names deler ét namespace, så en workbook der allerede har et navn kaldet Slicer_Region ville ellers kollidere ved næste save. Bygger du tabeller og validering sammen, dækker artiklen om data validation, AutoFilter og tabeller de tilstødende defined name-tilfælde. Delt lager følger samme regel som Excel bruger. TXLSXSlicerCaches.FindForTableColumn(ATableId, ATableColumnId) slås op før en ny cache oprettes, så to slicer-views over samme tabelkolonne — ét på dashboard-arket, ét på detail-arket — deler en enkelt cache-part og forbliver filter-synkroniserede. Sletning af et view kører den omvendte check på tværs af alle ark i workbook'en og smider cachen plus dens reserverede #N/A-navn væk, men kun når det sidste view der refererer til den er væk

Hvordan forbliver relations-id'er stabile i begge ender?

De forbliver stabile fordi HotXLS beregner slicer-rid'en ved at afspille den nøjagtige rækkefølge det udsender alle andre relationer i, og tilføjer de nye relationer i halen. Intet der allerede er i filen flytter sig. Det lyder som over-engineering, indtil du har debugget alternativet. Relations-id'er i en worksheet .rels refereres indefra i worksheet-parten — af hyperlinks, af drawing'en, af kommentarer, af table-parts — og ECMA-376 Part 2 opløser dem positionelt inde i relations-grafen, ikke via nogen stabil identitet. Indsætter writer'en slicer-relationen i midten, forskydes enhver reference efter den med ét, og arket begynder stille og roligt at pege på den forkerte part. Så XlsxWorksheetSlicerRelNumber gennemløber hele den deterministiske kæde — eksterne hyperlinks, så kommentar-VML'et, kommentarer, header/footer-billeder, threaded comments, drawing'en, arkets baggrund, printer-indstillinger, tabeller, pivot-tabeller, query-tabeller, table single cells — og først derefter gør krav på næste nummer til sliceren. XlsxWorkbookSlicerCacheFirstRid gør det samme på workbook-siden: sheets, sharedStrings, styles, VBA, eksterne links, theme, connections, pivot-caches, person, calcChain, sheet metadata, XML maps, custom XML, og så slicerCache-kørslen. Det generelle problem er gennemarbejdet i stykket om OPC relationship resolution. Workbook-side-extensionen bygges så ud fra de numre af XlsxBuildWorkbookSlicerCachesExtXml, og hvis kildefilen allerede havde slicerCaches-extension-blokke, strippes de først ud af den rå extLst, så modellen genudsender dem mod de aktuelle rids i stedet for at efterlade en forældet kopi

AlternateContent-ankeret og dets legacy-fallback

Drawing-ankeret er det ene sted hvor slicer-markup'en bliver oprigtigt underlig, og underligheden er bevidst. HotXLS udsender formen inde i et mc:AlternateContent-element per ECMA-376 Part 3, med en Choice-gren som Excel 2013 og nyere tager og en Fallback-gren til alt ældre

// Det XlsxSlicerAnchorPayloadXml producerer, strukturelt:
//
//   <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>

Læg mærke til de to namespaces. Token'et Requires="sle15" opløses til 2012-slicer-namespace'et, hvilket er versions-porten — en læser der ikke forstår 2012-slicere, må springe hele Choice-grenen over. Men graphicData/@uri og sle:slicer-elementet inde i den bruger 2010-namespace'et, fordi det er den drawing-side-identifikator graphic frame'en altid har brugt. Får du dem omvendt, producerer du en fil Excel åbner uden klage og derefter renderer som en tom ramme, hvilket er et mindeværdigt uhjælpsomt symptom. Fallback-grenen holder et almindeligt rektangel med en tekst der forklarer at tabel-slicere ikke understøttes i denne version — det er hvad en læser fra 2007-tiden tegner i stedet for kontrolelementet, og det er derfor en ældre Excel kan round-trip'e workbook'en uden at ødelægge noget. TXLSXSlicer eksponerer geometrien som en 1-baseret inklusiv boks gennem SetBounds(ARow1, ACol1, ARow2, ACol2) og de enkelte FromRow / ToCol-egenskaber, plus AnchorType til den sædvanlige move-and-size-with-cells-adfærd. HasAnchor er False i ét specifikt tilfælde: en slicer der er gemt inde i en group shape. De afspilles ordret med deres group-anker, og HotXLS laver bevidst ikke et andet anker til dem, da det ville udsende formen to gange

Redigering af en eksisterende slicer

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

  // Fjernelse af det sidste view over en kolonne smider cachen
  // og det reserverede #N/A defined name med
  Sheet.Slicers.Delete(Sheet.Slicers.IndexByName('Region'));

De tri-state heltals-egenskaber fortjener en note, for de ligner et modellerings-uheld og er det ikke. ShowCaption, LockedPosition, ColumnCount, StartItem og CustomListSort bruger alle -1 til at betyde at attributten var fraværende i kildefilen og skal forblive fraværende ved write, med 0 og 1 som eksplicitte værdier. Excel behandler fraværende og eksplicit-default forskelligt nogle få steder, og det er netop bevarelsen af den forskel der holder en byte-diff af et urørt save ren. RowHeight og ColumnWidth holdes som rå strenge af en relateret grund: Excel skriver decimaler ind i de EMU-attributter, og at genparse dem gennem et heltal ville tabe præcision ved round-trip. RowHeightEmu er heltals-accessoren som bekvemmelighed over samme lager, med XlsxSlicerDefaultRowHeight på 241300 EMU — 19 points ved XlsxEmuPerPoint-forholdet på 12700

Hvor det stopper

To ærlige begrænsninger. For det første modellerer HotXLS tabel-sliceren fuldstændigt, men modellerer ikke pivot-slicer-oprettelse — du kan åbne en workbook med pivot-slicere, læse deres identitet, holde dem intakte gennem et save og slette dem, men cache-siden afspilles som bytes og der er intet API til at bygge en fra bunden. For det andet filtrerer en slicer ikke noget af sig selv. Den er en UI over en tabelkolonne, og den visible-row-tilstand den driver er Excel-siden; HotXLS skriver kontrolelementet og bindingen, ikke et beregnet filterresultat. Skal rækker faktisk være filtrerede i den gemte fil, skal du også sætte tabellens AutoFilter. Enhver attribut HotXLS ikke modellerer på <slicer> eller <x15:tableSlicerCache> føres uændret igennem, så en slicer der kommer fra en nyere Excel-build stadig re-serialiseres med sine ukendte attributter intakte, selv efter du har flyttet den. Det er den egenskab der gør dirty-rebuild-stien sikker at tage: genopbygning fra modellen er kun acceptabel hvis modellen er en superset af det den parsete. GUID-mærkede extension-mekanisme fra ECMA-376 Part 1 §18.2.10 og slicer-part-definitionerne i [MS-XLSX] er det der overhovedet gør den garanti udtrykkelig

Tabel-slicere leveres i HotXLS Delphi Component, som læser og skriver XLSX-, XLSM- og legacy-BIFF-workbooks fra Delphi 5 og frem til den aktuelle release, plus C++Builder og Free Pascal, uden Excel-installation og uden OLE. Fuld API-reference og en trial-build findes på produktsiden for HotXLS Delphi Spreadsheet Component