Таблични slicers в Excel с HotXLS: round-trip за Delphi

HotXLS създава Excel таблични slicers от Delphi и C++Builder с едно извикване — Worksheet.Slicers.Add(Table, ColumnName) — и записва всичките осем места в пакета, от които един slicer има нужда: slicer и slicerCache частите, двете content-type overrides, worksheet и workbook връзките с техните съответстващи extLst блокове и drawing anchor-а

Един slicer изглежда като контрола, която пускаш върху лист. Във файла не е нищо подобно. Той е разпределен запис, разпръснат върху две нови части, два графа от връзки, три extension блока и един drawing anchor, и цялото нещо се валидира като единица, когато Excel отвори пакета. Изпуснеш ли едно парче, не получаваш деградирал slicer; получаваш диалога за ремонт, последван от работна книга със тихо премахнат slicer. Този режим на отказ е това, което прави ръчното емитиране на slicers досадно, и е причината интересното инженерство тук да е bookkeeping, а не рендиране

Защо един slicer докосва осем места в пакета?

Защото един slicer е три отделни концепции, които OPC принуждава в три отделни дома: състоянието на филтъра (workbook-scoped), изгледът (worksheet-scoped) и формата (drawing-scoped). HotXLS записва точно осем landing точки за всеки табличен slicer и всяка една от тях е носеща

• xl/slicers/slicerN.xml — по една част на работен лист, държаща всеки <slicer> изглед на този лист в namespace-а http://schemas.microsoft.com/office/spreadsheetml/2009/9/main
• xl/slicerCaches/slicerCacheN.xml — на ниво workbook, по една за всяка режеща се таблична колона, носеща действителния binding
• <Override> в [Content_Types].xml за всяка от тези две части, използващ application/vnd.ms-excel.slicer+xml и application/vnd.ms-excel.slicerCache+xml (ECMA-376 Part 2 изисква content type за всяка част и Excel отказва пакета без тях)
• worksheet връзка от тип .../office/2007/relationships/slicer, плюс <x14:slicerList> extension вътре в worksheet extLst, сочещ към този rid
• workbook връзка от тип .../office/2007/relationships/slicerCache, плюс <x15:slicerCaches> extension вътре в workbook extLst, изброяващ същите rids
• mc:AlternateContent anchor в drawing-а на листа, който дава на slicer-а позицията и размера му
• <x15:tableSlicerCache> блок вътре в cache частта, който го обвързва с table id и id на таблична колона

Extension блоковете използват стандартния ECMA-376 Part 1 §18.2.10 extLst механизъм: всеки <ext> носи uri атрибут, който идентифицира функционалността, а читателите, които не разпознават GUID-а, прескачат блока. HotXLS различава табличните slicers от pivot slicers само по този GUID — {3A4CF648-6AED-40f4-86FF-DC5316D8AED3} за табличния slicerList, {A8765BA9-456A-4dab-B4F3-ACF838C121DE} за pivot-а, и съответно {46BE6895-7355-4a93-B00E-2C351335B9C9} и {BBE1A952-AA13-448e-AADC-164F8A28A991} за двете workbook slicerCaches extension-и. Самият табличен binding живее под {2F2917AC-EB37-4324-AD4E-5DD8C200BD13}. Тези константи са изложени в модула като XlsxExtUriSlicerListX15, XlsxExtUriWbSlicerCachesX15 и XlsxExtUriTableSlicerCache, така че нищо в пътя за запис не носи magic string

Кои части могат да се възпроизведат дословно и кои трябва да се изградят наново?

HotXLS отговаря на това с едно-единствено правило: част може да възпроизведе оригиналните си байтове само ако нищо, което реферира, не може да бъде преномерирано по време на записа. Табличните slicer caches не издържат този тест, затова HotXLS ги изгражда наново от модела при всеки запис, безусловно — дори когато потребителят никога не е пипал slicer-а. Причината е tableId: елементът <x15:tableSlicerCache> идентифицира таблицата си по id атрибута на <table>, а HotXLS преномерира table ids workbook-глобално, когато пише. Една ранна имплементация съхраняваше cache частта като сурови байтове при отваряне и ги избутваше обратно при запис, което е правилната стратегия за повечето непрозрачни части. Тя произвеждаше файлове, които се отваряха чисто в обичайния случай, и задействаше ремонт в интересния: добави таблица на по-ранен лист, запиши, и cache-ът сега сочи table id, което принадлежи на нещо друго или изобщо на нищо. Бъгът е невидим в обикновен open-and-save regression тест, защото ids случайно не мърдат

Pivot-образните caches поемат по обратния път. Техният payload е <pivotTables> плюс <x14:data> блок, описващ cached items, sort състояние и индекси на елементи — структура без преномериране от страна на HotXLS и такава, която е скъпа да се моделира вярно. Те се пазят като сурови байтове и се възпроизвеждат точно, като TXLSXSlicerCache.IsPivot и IsTableCache разграничават двете. Изгледите на slicer-а следват трета политика, dirty-tracked: незасегнат <slicer> елемент и неговият drawing anchor се възпроизвеждат дословно, а в мига, в който някой изглед на листа бъде модифициран, цялата sheet част се генерира наново от модела. Това е общата форма на преценката — дословно възпроизвеждане, където референциите са стабилни, rebuild от модела, където writer-ът преназначава идентичност, dirty tracking, където и двете са приемливи — и важи далеч отвъд slicers, както бележките за lossless round-trip на теми, extLst блокове и calcChain разглеждат по-подробно

Създаване на табличен slicer от Delphi

TXLSXSlicers.Add е целият creation API. Получи ли TXLSXTable и име на колона, той материализира ids на табличните колони, намира или създава подкрепящия TXLSXSlicerCache, резервира съответстващото defined name и закотвя изглед вдясно от таблицата. Overload-ът с два аргумента избира разположението; този с шест приема изрична 1-based включваща кутия

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

    // Default разположение: две колони настрана от таблицата,
    // 13 реда височина при 19pt default височина на реда
    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;

Две правила за именуване си заслужава да знаеш, защото са Excel конвенции, а не изобретения на HotXLS. Cache-ът се нарича Slicer_<column>, като неидентификаторните символи се свиват до долни черти, а при колизия се добавя числов суфикс — Slicer_Region, после Slicer_Region1 и така нататък. И всяко cache име се резервира също като workbook defined name с формула #N/A, което прави TXLSXSlicers.ReserveCacheDefinedName. Excel сам създава това име, и има значение: cache имената и defined names споделят един namespace, така че работна книга, която вече има име Slicer_Region, иначе би колидирала при следващия запис. Ако строиш таблици и валидация заедно, взаимодействията на defined names в статията за data validation, AutoFilter и таблици покриват съседните случаи. Споделянето следва същото правило, което Excel използва. TXLSXSlicerCaches.FindForTableColumn(ATableId, ATableColumnId) се допитва преди да се създаде нов cache, така че два slicer изгледа над една и съща таблична колона — единият на dashboard листа, другият на детайлния лист — споделят една cache част и остават филтър-синхронизирани. Изтриването на изглед изпълнява обратната проверка през всеки лист в работната книга и пуска cache-а плюс резервираното му #N/A име само когато последният изглед, рефериращ го, изчезне

Как relationship ids остават стабилни и в двата края?

Те остават стабилни, защото HotXLS изчислява slicer rid-а, като възпроизвежда точния ред, в който емитира всяка друга връзка, и добавя новите връзки на опашката. Нищо, което вече е във файла, не мърда. Това звучи като over-engineering, докато не дебъгнеш алтернативата. Relationship ids в worksheet .rels се реферират отвътре в worksheet частта — от хипервръзки, от drawing-а, от коментари, от таблични части — и ECMA-376 Part 2 ги резолвира позиционно в графа на връзките, а не по някаква стабилна идентичност. Ако writer-ът вмъкне slicer връзката по средата, всяка референция след нея се измества с едно и листът тихо започва да сочи към грешната част. Затова XlsxWorksheetSlicerRelNumber обхожда пълната детерминистична верига — външни хипервръзки, после comment VML, коментари, header/footer изображения, threaded comments, drawing-а, фона на листа, настройките за печат, таблици, pivot таблици, query таблици, table single cells — и едва тогава претендира следващото число за slicer-а. XlsxWorkbookSlicerCacheFirstRid прави същото на страната на workbook-а: sheets, sharedStrings, styles, VBA, external links, theme, connections, pivot caches, person, calcChain, sheet metadata, XML maps, custom XML, после run-ът на slicerCache. Общият проблем е разгледан в материала за OPC relationship resolution. Workbook-страничният extension после се строи от тези числа от XlsxBuildWorkbookSlicerCachesExtXml, а ако изходният файл вече е имал slicerCaches extension блокове, те се отстраняват от суровия extLst първо, така че моделът ги пре-емитира спрямо актуалните rids, вместо да остави остаряло копие

AlternateContent anchor-ът и неговият legacy fallback

Drawing anchor-ът е единственото място, където slicer markup-ът става наистина странен, и странността е умишлена. HotXLS емитира формата вътре в mc:AlternateContent елемент съгласно ECMA-376 Part 3, с Choice клон, който Excel 2013 и по-новите поемат, и Fallback клон за всичко по-старо

// Какво произвежда 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>

Погледни двата namespace-а. Токенът Requires="sle15" резолвира към slicer namespace-а от 2012, който е версийната врата — четец, който не разбира slicers от 2012, трябва да прескочи целия Choice клон. Но graphicData/@uri и елементът sle:slicer вътре в него използват namespace-а от 2010, защото това е drawing-страничният идентификатор, който graphic frame-ът винаги е използвал. Обърнеш ли ги, получаваш файл, който Excel отваря без оплакване и после рендерира като празна рамка — запомнящо се безполезен симптом. Fallback клонът държи обикновен правоъгълник с текстово тяло, обясняващо, че табличните slicers не се поддържат в тази версия — това е, което четец от ерата на 2007 рисува вместо контролата, и е причината по-стар Excel да може да round-trip-не работната книга, без да унищожи нищо. TXLSXSlicer излага геометрията като 1-based включваща кутия през SetBounds(ARow1, ACol1, ARow2, ACol2) и отделните FromRow / ToCol свойства, плюс AnchorType за обичайното move-and-size-with-cells поведение. HasAnchor е False за един конкретен случай: slicer, вложен в group фигура. Тези се възпроизвеждат с group anchor-а си дословно и HotXLS умишлено не синтезира втори anchor за тях, защото така би емитирал формата два пъти

Редактиране на съществуващ slicer

var
  Slicer: TXLSXSlicer;
  Cache: TXLSXSlicerCache;
begin
  Slicer := Sheet.Slicers.FindByName('Region');
  if Slicer <> nil then
  begin
    Slicer.SetBounds(2, 8, 16, 11);      // 1-based, включващо
    Slicer.ShowCaption := 1;             // -1 = атрибутът е пропуснат
    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;

  // Премахването на последния изглед над колона пуска cache-а
  // и резервираното #N/A defined name заедно с него
  Sheet.Slicers.Delete(Sheet.Slicers.IndexByName('Region'));

Tri-state integer свойствата заслужават бележка, защото изглеждат като инцидент в моделирането, а не са. ShowCaption, LockedPosition, ColumnCount, StartItem и CustomListSort всички използват -1, за да означат, че атрибутът е отсъствал в изходния файл и трябва да остане отсъстващ при запис, като 0 и 1 са изричните стойности. Excel третира отсъстващото и изрично-по-подразбиране различно на няколко места, и запазването на това разграничение е това, което държи byte diff-а на незасегнат запис чист. RowHeight и ColumnWidth се пазят като сурови низове по сродна причина: Excel пише десетични числа в тези EMU атрибути, а препарсването им през integer би загубило прецизност при round-trip. RowHeightEmu е integer accessor-ът за удобство върху същото хранилище, като XlsxSlicerDefaultRowHeight е 241300 EMU — 19 точки при XlsxEmuPerPoint съотношение от 12700

Къде спира това

Две честни граници. Първо, HotXLS моделира табличния slicer напълно, но не моделира създаването на pivot slicers — можеш да отвориш работна книга с pivot slicers, да прочетеш идентичността им, да ги запазиш непокътнати през запис и да ги изтриеш, но cache страната се възпроизвежда като байтове и няма API за изграждането им от нулата. Второ, един slicer не филтрира нищо сам. Той е UI над таблична колона, а състоянието на видимите редове, което задвижва, е от страната на Excel; HotXLS записва контролата и binding-а, не изчислен филтърен резултат. Ако ти трябват редове, действително филтрирани в записания файл, задай и AutoFilter на таблицата. Всеки атрибут, който HotXLS не моделира върху <slicer> или <x15:tableSlicerCache>, се пренася непроменен, така че slicer, дошъл от по-нова Excel сборка, пак се ре-сериализира с непознатите си атрибути непокътнати, дори след като го преместиш. Това е свойството, което прави пътя на dirty-rebuild безопасен: rebuild от модела е приемлив само ако моделът е супермножество на това, което е парснал. GUID-маркираният extension механизъм от ECMA-376 Part 1 §18.2.10 и дефинициите на slicer частите в [MS-XLSX] са това, което прави тази гаранция изразима изобщо

Табличните slicers се доставят в HotXLS Delphi Component, който чете и пише XLSX, XLSM и legacy BIFF работни книги от Delphi 5 до текущата версия, плюс C++Builder и Free Pascal, без инсталиран Excel и без OLE. Пълният API reference и trial сборка има на продуктовата страница на HotXLS Delphi Spreadsheet Component