Excel-tabellslicers i Delphi: HotXLS Round-Trip

HotXLS skapar Excel-tabellslicers från Delphi och C++Builder med ett enda anrop — Worksheet.Slicers.Add(Table, ColumnName) — och skriver alla åtta paketplatser en slicer behöver: slicer- och slicerCache-delarna, båda content-type-överskrifterna, kalkylblads- och arbetsboksrelationerna med sina matchande extLst-block, och ritningsankaret

En slicer ser ut som en kontroll du släpper på ett ark. I filen är den inte alls det. Den är en distribuerad post spridd över två nya delar, två relationsgrafer, tre extensionsblock och ett ritningsankar, och hela paketet valideras som en enhet när Excel öppnar paketet. Missa en pjäs och du får ingen degraderad slicer; du får reparationsdialogen, följd av en arbetsbok där slicern tyst tagits bort. Det felmodsläget är vad som gör slicers irriterande att avge för hand, och det är varför det intressanta ingenjörsarbetet här är bokföring snarare än rendering

Varför rör en slicer åtta ställen i paketet?

För att en slicer är tre separata koncept som OPC tvingar in i tre separata hem: filtertillståndet (arbetsboksscopat), vyn (kalkylbladsscopad) och formen (ritningsscopad). HotXLS skriver exakt åtta landningspunkter per tabellslicer, och varenda en av dem är bärande

• xl/slicers/slicerN.xml — en del per kalkylblad, som håller varje <slicer>-vy på det arket i namespacen http://schemas.microsoft.com/office/spreadsheetml/2009/9/main
• xl/slicerCaches/slicerCacheN.xml — arbetsboksnivå, en per snittad tabellkolumn, som bär den faktiska bindningen
• ett <Override> i [Content_Types].xml för var och en av de två delarna, med application/vnd.ms-excel.slicer+xml och application/vnd.ms-excel.slicerCache+xml (ECMA-376 Part 2 kräver en innehållstyp för varje del, och Excel vägrar paketet utan dem)
• en kalkylbladsrelation av typ .../office/2007/relationships/slicer, plus en <x14:slicerList>-extension inuti kalkylbladets extLst som pekar på det rid:t
• en arbetsboksrelation av typ .../office/2007/relationships/slicerCache, plus en <x15:slicerCaches>-extension inuti arbetsbokens extLst som listar samma rid:n
• ett mc:AlternateContent-ankar i arkets ritning som ger slicern dess position och storlek
• ett <x15:tableSlicerCache>-block inuti cachedelen som binder den till ett tabell-id och ett tabellkolumn-id

Extensionsblocken använder standardmekanismen extLst från ECMA-376 Part 1 §18.2.10: varje <ext> bär ett uri-attribut som identifierar funktionen, och läsare som inte känner igen GUID:en hoppar över blocket. HotXLS skiljer tabellslicers från pivot-slicers enbart på den GUID:en — {3A4CF648-6AED-40f4-86FF-DC5316D8AED3} för tabellens slicerList, {A8765BA9-456A-4dab-B4F3-ACF838C121DE} för pivotvarianten, och motsvarande {46BE6895-7355-4a93-B00E-2C351335B9C9} och {BBE1A952-AA13-448e-AADC-164F8A28A991} för de två arbetsboks-slicerCaches-extensionerna. Tabellbindningen i sig lever under {2F2917AC-EB37-4324-AD4E-5DD8C200BD13}. De konstanterna exponeras i enheten som XlsxExtUriSlicerListX15, XlsxExtUriWbSlicerCachesX15 och XlsxExtUriTableSlicerCache, så ingenting i skrivvägen bär en magisk sträng

Vilka delar kan spelas upp ordagrant, och vilka måste byggas om?

HotXLS svarar på det med en enda regel: en del får bara spela upp sina originalbyten om ingenting den refererar kan numreras om under sparning. Tabellslicer-cacher klarar inte det testet, så HotXLS bygger om dem från modellen vid varje sparning, villkorslöst — även när användaren aldrig rört slicern. Anledningen är tableId: elementet <x15:tableSlicerCache> identifierar sin tabell via id-attributet på <table>, och HotXLS numrerar om tabell-id:n arbetsboksglobalt när det skriver. En tidig implementation lagrade cachedelen som råa byte vid öppning och tryckte tillbaka dem vid sparning, vilket är rätt strategi för de flesta ogenomskinliga delar. Den producerade filer som öppnades rent i det vanliga fallet och utlöste en reparation i det intressanta: lägg till en tabell på ett tidigare ark, spara, och cachen pekar nu på ett tabell-id som tillhör något annat, eller ingenting alls. Buggen är osynlig i en vanlig öppna-och-spara-regression för att id:n råkar inte flytta på sig

Pivotformade cacher tar motsatt väg. Deras nyttolast är <pivotTables> plus ett <x14:data>-block som beskriver cachade objekt, sorteringstillstånd och objektindex — en struktur utan HotXLS-sidig omnumrering, och en som är dyr att modellera troget. De hålls som råa byte och spelas upp exakt, med TXLSXSlicerCache.IsPivot och IsTableCache som skiljer de två åt. Slicer-vyerna följer en tredje policy, dirty-spårad: ett orört <slicer>-element och dess ritningsankar spelas upp ordagrant, och i samma ögonblick som någon vy på arket ändras regenereras hela arkdelen från modellen. Det är den generella formen på domen — ordagram uppspelning där referenser är stabila, modellombryggnad där skribenten tilldelar om identitet, dirty-spårning där båda är acceptabla — och den gäller långt bortom slicers, som de förlustfria round-trip-noterna om teman, extLst-block och calcChain arbetar igenom mer på djupet

Skapa en tabellslicer från Delphi

TXLSXSlicers.Add är hela skapelse-API:t. Givet en TXLSXTable och ett kolumnnamn materialiserar den tabellkolumn-id:n, hittar eller skapar den underliggande TXLSXSlicerCache:n, reserverar det matchande definierade namnet och förankrar en vy till höger om tabellen. Tvåargsöverlagringen väljer placeringen; sexargsversionen tar en explicit 1-baserad inklusiv box

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

    // Standardplacering: två kolumner från tabellen,
    // 13 rader hög vid 19 pt standardradhöjd
    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;

Två namngivningsregler är värda att känna till för att de är Excel-konventioner snarare än HotXLS-uppfinningar. Cachen namnges Slicer_<column> med icke-identifierartecken vikta till understreck, och kollisioner får ett numeriskt suffix — Slicer_Region, sedan Slicer_Region1, och så vidare. Och varje cachenamn reserveras också som ett definierat arbetsboksnamn vars formel är #N/A, vilket är vad TXLSXSlicers.ReserveCacheDefinedName gör. Excel skapar det namnet själv, och det spelar roll: cachenamn och definierade namn delar ett namespace, så en arbetsbok som redan har ett namn kallat Slicer_Region skulle annars kollidera vid nästa sparning. Bygger du tabeller och validering tillsammans täcker artikeln om datavalidering, AutoFilter och tabeller de närliggande fallen. Delning följer samma regel Excel använder. TXLSXSlicerCaches.FindForTableColumn(ATableId, ATableColumnId) konsulteras innan en ny cache skapas, så två slicer-vyer över samma tabellkolumn — en på dashboard-arket, en på detaljarket — delar en enda cachedel och förblir filtersynkroniserade. Att radera en vy kör omvändkontrollen över varje ark i arbetsboken och släpper cachen plus dess reserverade #N/A-namn bara när den sista vyn som refererar den är borta

Hur hålls relations-id:n stabila i båda ändar?

De hålls stabila för att HotXLS beräknar slicer-rid:t genom att spela upp exakt den ordning i vilken det avger varje annan relation, och lägger till de nya relationerna i svansen. Ingenting som redan finns i filen flyttas. Det låter som överkonstruktion tills du har felsökt alternativet. Relations-id:n i ett kalkylblads .rels refereras inifrån kalkylbladsdelen — av hyperlänkar, av ritningen, av kommentarer, av tabell-delar — och ECMA-376 Part 2 löser dem positionellt inom relationsgrafen, inte genom någon stabil identitet. Sätter skribenten in slicer-relationen i mitten flyttas varje referens efter den ett steg, och arket börjar tyst peka på fel del. Så XlsxWorksheetSlicerRelNumber går igenom hela den deterministiska kedjan — externa hyperlänkar, sedan kommentars-VML:en, kommentarer, sidhuvuds-/sidfotsbilder, trådade kommentarer, ritningen, arkets bakgrund, skrivarinställningar, tabeller, pivot-tabeller, query-tabeller, tabell-enceller — och gör först därefter anspråk på nästa nummer för slicern. XlsxWorkbookSlicerCacheFirstRid gör samma sak på arbetsbokssidan: ark, sharedStrings, styles, VBA, externa länkar, tema, connections, pivot-cacher, person, calcChain, arkmetadata, XML-maps, anpassad XML, sedan slicerCache-körningen. Det generella problemet arbetas igenom i stycket om OPC-relationsupplösning. Arbetsbokssidans extension byggs sedan från de numren av XlsxBuildWorkbookSlicerCachesExtXml, och om källfilen redan hade slicerCaches-extensionsblock tas de bort ur det råa extLst:et först, så modellen avger dem på nytt mot de aktuella rid:n i stället för att lämna kvar en inaktuell kopia

AlternateContent-ankaret och dess äldre fallback

Ritningsankaret är det enda stället där slicer-markeringen blir genuint märklig, och märkligheten är avsiktlig. HotXLS avger formen inuti ett mc:AlternateContent-element enligt ECMA-376 Part 3, med en Choice-gren som Excel 2013 och senare tar och en Fallback-gren för allt äldre

// Vad XlsxSlicerAnchorPayloadXml producerar, strukturellt:
//
//   <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>

Titta på de två namespaces. Token Requires="sle15" löser till 2012 års slicer-namespace, som är versionsgrinden — en läsare som inte förstår 2012-slicers måste hoppa över hela Choice-grenen. Men graphicData/@uri och elementet sle:slicer inuti den använder 2010-namespacen, för det är den ritningssidiga identifieraren den grafiska ramen alltid har använt. Får du dem bakvänt producerar du en fil som Excel öppnar utan klagomål och sedan renderar som en tom ram, vilket är ett minnesvärt ohjälpsamt symptom. Fallback-grenen håller en vanlig rektangel med en textkropp som förklarar att tabellslicers inte stöds i den här versionen — det är vad en läsare från 2007-eran ritar i stället för kontrollen, och det är varför en äldre Excel kan rundresa arbetsboken utan att förstöra något. TXLSXSlicer exponerar geometrin som en 1-baserad inklusiv box genom SetBounds(ARow1, ACol1, ARow2, ACol2) och de individuella egenskaperna FromRow / ToCol, plus AnchorType för det vanliga flytta-och-storleksändra-med-cellerna-beteendet. HasAnchor är False för ett specifikt fall: en slicer nästlad inuti en gruppform. De spelas upp med sitt gruppankar ordagrant och HotXLS syntetiserar medvetet inte ett andra ankar för dem, för det skulle avge formen två gånger

Redigera en befintlig slicer

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

  // Att ta bort den sista vyn över en kolumn släpper dess cache
  // och det reserverade #N/A-namnet med den
  Sheet.Slicers.Delete(Sheet.Slicers.IndexByName('Region'));

De treläges heltalsegenskaperna förtjänar en not, för de ser ut som en modelleringsolycka och är inte det. ShowCaption, LockedPosition, ColumnCount, StartItem och CustomListSort använder alla -1 för att betyda att attributet saknades i källfilen och ska förbli frånvarande vid skrivning, med 0 och 1 som explicita värden. Excel behandlar frånvarande och explicit-standard olika på några ställen, och att bevara den distinktionen är vad som håller en byte-diff av en orörd sparning ren. RowHeight och ColumnWidth hålls som råa strängar av en närliggande anledning: Excel skriver decimaler in i de EMU-attributen, och att reparsa dem genom ett heltal skulle förlora precision på rundresan. RowHeightEmu är heltalsaccessorn för bekvämlighet över samma lagring, med XlsxSlicerDefaultRowHeight på 241300 EMU — 19 punkter vid XlsxEmuPerPoint-kvoten 12700

Var detta slutar

Två ärliga gränser. För det första modellerar HotXLS tabellslicern fullständigt men modellerar inte pivot-slicerskapande — du kan öppna en arbetsbok med pivot-slicers, läsa deras identitet, behålla dem intakta över en sparning och radera dem, men cachesidan spelas upp som byte och det finns inget API för att bygga en från grunden. För det andra filtrerar en slicer ingenting av sig själv. Den är ett UI över en tabellkolumn, och tillståndet för synliga rader som den driver är Excel-sidan; HotXLS skriver kontrollen och bindningen, inte ett beräknat filterresultat. Behöver du rader faktiskt filtrerade i den sparade filen, sätt även tabellens AutoFilter. Varje attribut HotXLS inte modellerar på <slicer> eller <x15:tableSlicerCache> förs vidare oförändrat, så en slicer som kom från en nyare Excel-byggnad serialiseras fortfarande om med sina okända attribut intakta även efter att du flyttat den. Det är den egenskapen som gör dirty-ombryggnadsvägen säker att ta: att bygga om från modellen är bara acceptabelt om modellen är en supermängd av det den parsade. Den GUID-taggade extensionsmekanismen från ECMA-376 Part 1 §18.2.10, och slicer-deldefinitionerna i [MS-XLSX], är vad som gör den garantin uttryckbar från första början

Tabellslicers levereras i HotXLS Delphi Component, som läser och skriver XLSX-, XLSM- och äldre BIFF-arbetsböcker från Delphi 5 till den aktuella utgåvan, plus C++Builder och Free Pascal, utan Excel-installation och utan OLE. Fullständig API-referens och en testversion finns på produktsidan för HotXLS Delphi Spreadsheet Component