Slicery tabel w Delphi: round-trip z HotXLS

HotXLS tworzy slicery tabel z Delphi i C++Buildera jednym wywołaniem — Worksheet.Slicers.Add(Table, ColumnName) — i zapisuje wszystkie osiem miejsc w pakiecie, których slicer potrzebuje: części slicer i slicerCache, oba nadpisania typów zawartości, relacje arkusza i skoroszytu z pasującymi blokami extLst oraz kotwicę rysunku

A slicer wygląda jak kontrolka, którą upuszczasz na arkusz. W pliku nie ma z nią nic wspólnego. To rozproszony rekord rozłożony na dwie nowe części, dwa grafy relacji, trzy bloki rozszerzeń i jedną kotwicę rysunku, a całość jest walidowana jako jednostka, gdy Excel otwiera pakiet. Zabraknie jednego elementu — i nie dostajesz okrojonego slicera, tylko okno naprawy, a po nim skoroszyt ze slicerem po cichu usuniętym. Ten tryb awarii sprawia, że ręczne emitowanie slicerów to udręka, i dlatego ciekawa inżynieria jest tu w księgowaniu, a nie w renderowaniu

Dlaczego jeden slicer dotyka ośmiu miejsc w pakiecie?

Bo slicer to trzy osobne koncepcje, które OPC wpycha do trzech osobnych domów: stan filtra (na poziomie skoroszytu), widok (na poziomie arkusza) i kształt (na poziomie rysunku). HotXLS zapisuje dokładnie osiem punktów lądowania na slicer tabeli i każdy z nich jest elementem nośnym

• xl/slicers/slicerN.xml — jedna część na arkusz, trzymająca wszystkie widoki <slicer> na tym arkuszu w przestrzeni nazw http://schemas.microsoft.com/office/spreadsheetml/2009/9/main
• xl/slicerCaches/slicerCacheN.xml — na poziomie skoroszytu, jedna na kolumnę tabeli, przenosząca faktyczne powiązanie
• <Override> w [Content_Types].xml dla każdej z tych dwóch części, używający application/vnd.ms-excel.slicer+xml i application/vnd.ms-excel.slicerCache+xml (ECMA-376 Part 2 wymaga typu zawartości dla każdej części, a Excel bez nich odrzuca pakiet)
• relacja arkusza typu .../office/2007/relationships/slicer plus rozszerzenie <x14:slicerList> w extLst arkusza wskazujące ten rid
• relacja skoroszytu typu .../office/2007/relationships/slicerCache plus rozszerzenie <x15:slicerCaches> w extLst skoroszytu wymieniające te same ridy
• kotwica mc:AlternateContent w rysunku arkusza nadająca slicerowi pozycję i rozmiar
• blok <x15:tableSlicerCache> wewnątrz części cache wiążący go z id tabeli i id kolumny tabeli

Bloki rozszerzeń używają standardowego mechanizmu extLst z ECMA-376 Part 1 §18.2.10: każdy <ext> niesie atrybut uri identyfikujący funkcję, a czytniki, które nie rozpoznają GUID, pomijają blok. HotXLS odróżnia slicery tabel od slicerów przestawnych wyłącznie po tym GUID — {3A4CF648-6AED-40f4-86FF-DC5316D8AED3} dla tabelowego slicerList, {A8765BA9-456A-4dab-B4F3-ACF838C121DE} dla przestawnego, a odpowiednio {46BE6895-7355-4a93-B00E-2C351335B9C9} i {BBE1A952-AA13-448e-AADC-164F8A28A991} dla dwóch rozszerzeń slicerCaches w skoroszycie. Samo powiązanie tabeli żyje pod {2F2917AC-EB37-4324-AD4E-5DD8C200BD13}. Te stałe są wystawione w unitcie jako XlsxExtUriSlicerListX15, XlsxExtUriWbSlicerCachesX15 i XlsxExtUriTableSlicerCache, więc w ścieżce zapisu nie ma ani jednego magicznego stringa

Które części można odtworzyć wiernie, a które trzeba przebudować?

HotXLS odpowiada na to jedną zasadą: część może odtworzyć swoje oryginalne bajty tylko wtedy, gdy nic, na co się powołuje, nie może zostać przenumerowane podczas zapisu. Cache slicera tabeli nie przechodzi tego testu, więc HotXLS przebudowuje go z modelu przy każdym zapisie, bezwarunkowo — nawet gdy użytkownik nigdy nie tknął slicera. Powodem jest tableId: element <x15:tableSlicerCache> identyfikuje swoją tabelę atrybutem id elementu <table>, a HotXLS przy zapisie przenumerowuje id tabel w całym skoroszycie. Wczesna implementacja trzymała część cache jako surowe bajty od otwarcia i wypychała je z powrotem przy zapisie, co jest właściwą strategią dla większości nieprzezroczystych części. Produkowała pliki, które otwierały się czysto w typowym przypadku, i wywoływały naprawę w tym ciekawym: dodaj tabelę na wcześniejszym arkuszu, zapisz, a cache wskazuje teraz id tabeli należące do czegoś innego albo do niczego. Ten bug jest niewidoczny w zwykłym regresie otwórz-i-zapisz, bo id akurat się nie przesuwają

Cache w kształcie przestawnym idą odwrotną drogą. Ich zawartość to <pivotTables> plus blok <x14:data> opisujący cache'owane elementy, stan sortowania i indeksy elementów — struktura bez żadnego przenumerowania po stronie HotXLS i taka, którą drogo kosztuje wierne zamodelowanie. Te są trzymane jako surowe bajty i odtwarzane dokładnie, a TXLSXSlicerCache.IsPivot i IsTableCache rozróżniają te dwa przypadki. Widoki slicera podlegają trzeciej polityce, opartej o śledzenie zmian: nietknięty element <slicer> i jego kotwica rysunku odtwarzają się wiernie, a w momencie gdy jakikolwiek widok na arkuszu zostanie zmodyfikowany, cała część arkusza jest regenerowana z modelu. To ogólny kształt tej decyzji — wierne odtworzenie tam, gdzie referencje są stabilne, przebudowa z modelu tam, gdzie zapis przydziela tożsamość na nowo, śledzenie zmian tam, gdzie oba podejścia są do przyjęcia — i działa znacznie szerzej niż dla slicerów, co notatki o lossless round-trip dla motywów, bloków extLst i calcChain rozpracowują dokładniej

Tworzenie slicera tabeli z Delphi

TXLSXSlicers.Add to całe API tworzenia. Dostając TXLSXTable i nazwę kolumny, materializuje id kolumn tabeli, znajduje lub tworzy zapleczowy TXLSXSlicerCache, rezerwuje pasującą zdefiniowaną nazwę i zakotwicza widok na prawo od tabeli. Przeciążenie z dwoma argumentami wybiera miejsce; to z sześcioma przyjmuje jawną ramkę 1-based inclusive

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

    // Domyślne umiejscowienie: dwie kolumny od tabeli,
    // 13 wierszy wysokości przy domyślnej wysokości wiersza 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;

Warto znać dwie reguły nazewnictwa, bo to konwencje Excela, a nie wymysły HotXLS. Cache nazywa się Slicer_<kolumna> ze znakami niebędącymi identyfikatorami zwijanymi do podkreśleń, a kolizje dostają numeryczny sufiks — Slicer_Region, potem Slicer_Region1 i tak dalej. Każda nazwa cache jest też rezerwowana jako zdefiniowana nazwa skoroszytu z formułą #N/A, co robi TXLSXSlicers.ReserveCacheDefinedName. Excel tworzy tę nazwę sam i to ważne: nazwy cache i zdefiniowane nazwy dzielą jedną przestrzeń nazw, więc skoroszyt, który już ma nazwę Slicer_Region, kolidowałby inaczej przy następnym zapisie. Jeśli budujesz tabele i walidację razem, artykuł o walidacji danych, AutoFilterze i tabelach pokrywa sąsiednie przypadki. Współdzielenie idzie wg tej samej zasady, której używa Excel. TXLSXSlicerCaches.FindForTableColumn(ATableId, ATableColumnId) jest sprawdzany, zanim powstanie nowy cache, więc dwa widoki slicera na tej samej kolumnie tabeli — jeden na arkuszu dashboardu, drugi na arkuszu szczegółów — dzielą jedną część cache i pozostają zsynchronizowane pod względem filtra. Usunięcie widoku uruchamia odwrotną kontrolę przez wszystkie arkusze w skoroszycie i pozbywa się cache oraz jego zarezerwowanej nazwy #N/A dopiero, gdy zniknie ostatni widok, który na niego wskazuje

Jak id relacji pozostają stabilne na obu końcach?

Pozostają stabilne, bo HotXLS liczy rid slicera, odtwarzając dokładną kolejność, w jakiej emituje każdą inną relację, i dopina nowe relacje na końcu. Nic, co już jest w pliku, się nie przesuwa. Brzmi to jak over-engineering, dopóki nie zdebugujesz alternatywy. Identyfikatory relacji w .rels arkusza są przywoływane z wnętrza części arkusza — przez hiperłącza, rysunek, komentarze, części tabel — a ECMA-376 Part 2 rozwiązuje je pozycyjnie w grafie relacji, nie przez żadną stabilną tożsamość. Gdyby zapis wstawił relację slicera w środku, każda późniejsza referencja przesunęłaby się o jeden i arkusz zaczyna cicho wskazywać złą część. Dlatego XlsxWorksheetSlicerRelNumber przechodzi cały deterministyczny łańcuch — zewnętrzne hiperłącza, potem VML komentarzy, komentarze, obrazy nagłówka/stopki, komentarze wątkowe, rysunek, tło arkusza, ustawienia drukarki, tabele, tabele przestawne, tabele zapytań, pojedyncze komórki tabel — i dopiero wtedy rezerwuje następny numer dla slicera. XlsxWorkbookSlicerCacheFirstRid robi to samo po stronie skoroszytu: arkusze, sharedStrings, style, VBA, zewnętrzne linki, motyw, połączenia, cache przestawne, person, calcChain, metadane arkusza, mapy XML, własny XML, a potem ciąg slicerCache. Ogólny problem jest rozpracowany w tekście o rozwiązywaniu relacji OPC. Rozszerzenie po stronie skoroszytu jest potem budowane z tych numerów przez XlsxBuildWorkbookSlicerCachesExtXml, a jeśli plik źródłowy miał już bloki rozszerzeń slicerCaches, są one najpierw wycinane z surowego extLst, żeby model wyemitował je od nowa względem aktualnych ridów, zamiast zostawiać nieświeżą kopię

Kotwica AlternateContent i jej legacy fallback

Kotwica rysunku to jedyne miejsce, gdzie markup slicera robi się naprawdę dziwny, a ta dziwność jest zamierzona. HotXLS emituje kształt w elemencie mc:AlternateContent zgodnie z ECMA-376 Part 3, z gałęzią Choice, którą bierze Excel 2013 i nowsze, oraz Fallback dla wszystkiego starszego

// Co strukturalnie generuje XlsxSlicerAnchorPayloadXml:
//
//   <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>

Popatrz na te dwie przestrzenie nazw. Token Requires="sle15" rozwiązuje się do przestrzeni nazw slicerów z 2012, która jest bramką wersji — czytnik, który nie rozumie slicerów z 2012, musi pominąć całą gałąź Choice. Ale graphicData/@uri i element sle:slicer w środku używają przestrzeni nazw z 2010, bo to identyfikator po stronie rysunku, którego ramka graficzna używała zawsze. Odwrócenie tego daje plik, który Excel otwiera bez zastrzeżeń, a potem renderuje jako pustą ramkę, co jest pamiętnie mało pomocnym objawem. Gałąź Fallback trzyma zwykły prostokąt z tekstem wyjaśniającym, że slicery tabel nie są wspierane w tej wersji — to właśnie rysuje czytnik z ery 2007 w miejscu kontrolki i dlatego starszy Excel może zrobić round-trip skoroszytu, niczego nie niszcząc. TXLSXSlicer wystawia geometrię jako ramkę 1-based inclusive przez SetBounds(ARow1, ACol1, ARow2, ACol2) i indywidualne właściwości FromRow / ToCol, plus AnchorType dla typowego zachowania przesuwaj-i-rozciągaj-wraz-z-komórkami. HasAnchor ma wartość False w jednym konkretnym przypadku: slicer zagnieżdżony w kształcie grupy. Takie odtwarzają się ze swoją kotwicą grupy wiernie, a HotXLS celowo nie syntetyzuje dla nich drugiej kotwicy, bo wtedy wyemitowałby kształt dwa razy

Edycja istniejącego slicera

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;

  // Usunięcie ostatniego widoku na kolumnie zdejmuje jej cache
  // razem z zarezerwowaną zdefiniowaną nazwą #N/A
  Sheet.Slicers.Delete(Sheet.Slicers.IndexByName('Region'));

Właściwości całkowite w trzech stanach zasługują na notkę, bo wyglądają jak wypadek przy modelowaniu, a nie są. ShowCaption, LockedPosition, ColumnCount, StartItem i CustomListSort używają -1, żeby oznaczyć atrybut nieobecny w pliku źródłowym, który powinien pozostać nieobecny przy zapisie, a 0 i 1 jako jawne wartości. Excel w kilku miejscach traktuje nieobecność inaczej niż jawną wartość domyślną, a zachowanie tej różnicy trzyma czysty bajtowy diff nietkniętego zapisu. RowHeight i ColumnWidth są trzymane jako surowe stringi z pokrewnego powodu: Excel zapisuje ułamki dziesiętne w tych atrybutach EMU, a przepuszczenie ich przez liczbę całkowitą straciłoby precyzję przy round-tripie. RowHeightEmu to całkowity wygodny akcesor na tym samym magazynie, z XlsxSlicerDefaultRowHeight na 241300 EMU — 19 punktów przy współczynniku XlsxEmuPerPoint równym 12700

Gdzie to się kończy

Dwie uczciwe granice. Po pierwsze, HotXLS modeluje slicer tabeli kompletnie, ale nie modeluje tworzenia slicerów przestawnych — możesz otworzyć skoroszyt z slicerami przestawnymi, przeczytać ich tożsamość, zachować je nietknięte przez zapis i usunąć je, ale strona cache odtwarza się jako bajty i nie ma API do zbudowania jej od zera. Po drugie, slicer sam z siebie niczego nie filtruje. To UI na kolumnie tabeli, a stan widocznych wierszy, którym steruje, jest po stronie Excela; HotXLS zapisuje kontrolkę i powiązanie, nie wyliczony wynik filtra. Jeśli potrzebujesz wierszy faktycznie przefiltrowanych w zapisanym pliku, ustaw też AutoFilter tabeli. Każdy atrybut, którego HotXLS nie modeluje na <slicer> lub <x15:tableSlicerCache>, jest przenoszony bez zmian, więc slicer pochodzący z nowszej wersji Excela wciąż re-serializuje się z nierozpoznanymi atrybutami nietkniętymi, nawet po tym, jak go przesuniesz. To właśnie ta właściwość czyni ścieżkę dirty-rebuild bezpieczną: przebudowa z modelu jest do przyjęcia tylko wtedy, gdy model jest nadzbiorem tego, co sparsował. Mechanizm rozszerzeń otagowanych GUID z ECMA-376 Part 1 §18.2.10 oraz definicje części slicera z [MS-XLSX] to właśnie to, co w ogóle umożliwia wyrażenie tej gwarancji

Slicery tabel są w HotXLS Delphi Component, który czyta i zapisuje XLSX, XLSM i legacy BIFF w skoroszytach od Delphi 5 po aktualne wydanie, plus C++Builder i Free Pascal, bez instalacji Excela i bez OLE. Pełny API reference i wersję próbną znajdziesz na stronie produktu HotXLS Delphi Spreadsheet Component