Slicer для таблиц Excel в Delphi: round-trip с HotXLS

HotXLS создаёт табличные slicer-ы Excel из Delphi и C++Builder одним вызовом — Worksheet.Slicers.Add(Table, ColumnName) — и записывает все восемь точек в пакете, которые нужны slicer-у: части slicer и slicerCache, оба переопределения content-type, отношения рабочего листа и рабочей книги с соответствующими extLst-блоками и якорь в drawing

Slicer выглядит как элемент управления, который вы кладёте на лист. В файле это совсем не так. Это распределённая запись, размазанная по двум новым частям, двум графам отношений, трём блокам расширений и одному якорю drawing, и при открытии пакета Excel проверяет всё это как единое целое. Потеряете одну деталь — и получите не деградировавший slicer, а диалог восстановления, после которого в книге останется slicer, молча удалённый. Такой режим отказа и делает slicer-ы раздражающими для ручной эмиссии, поэтому интересная инженерия здесь — это учёт записей, а не отрисовка

Почему один slicer трогает восемь мест в пакете?

Потому что slicer — это три отдельных понятия, которые OPC загоняет в три отдельных дома: состояние фильтра (в масштабе рабочей книги), представление (в масштабе листа) и фигуру (в масштабе drawing). HotXLS пишет ровно восемь точек приземления на каждый табличный slicer, и каждая из них несёт нагрузку

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

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

Какие части можно воспроизводить байт в байт, а какие нужно пересобирать?

HotXLS отвечает на это одним правилом: часть может воспроизводить исходные байты, только если ничто из того, на что она ссылается, не может быть перенумеровано при сохранении. Табличные slicer cache проваливают этот тест, поэтому HotXLS пересобирает их из модели при каждом сохранении, безусловно — даже если пользователь вообще не трогал slicer. Причина — tableId: элемент <x15:tableSlicerCache> опознаёт свою таблицу по атрибуту id элемента <table>, а HotXLS при записи перенумеровывает id таблиц в масштабе всей книги. Ранняя реализация хранила часть кэша как сырые байты при открытии и выталкивала их обратно при сохранении — правильная стратегия для большинства непрозрачных частей. Файлы при этом открывались чисто в типичном случае, а в интересном срабатывал ремонт: добавьте таблицу на более раннем листе, сохраните — и кэш теперь указывает на чужой table id или вообще ни на какой. В обычном регрессе «открыл-сохранил» баг невидим, потому что id как раз не сдвинулись

Pivot-кэши идут противоположным путём. Их содержимое — <pivotTables> плюс блок <x14:data> с описанием кэшированных элементов, состояния сортировки и индексов элементов — структура без перенумерации на стороне HotXLS, и моделировать её честно дорого. Такие части хранятся как сырые байты и воспроизводятся байт в байт, а различают их TXLSXSlicerCache.IsPivot и IsTableCache. Представления slicer-а идут по третьей политике, с dirty-трекингом: нетронутый элемент <slicer> и его якорь drawing воспроизводятся байт в байт, а как только на листе меняется любое представление, вся часть листа перегенерируется из модели. Это общая форма суждения — байтовое воспроизведение там, где ссылки стабильны, пересборка из модели там, где писатель переназначает идентичность, и dirty-трекинг там, где допустимы оба варианта — и применимо оно далеко за пределами slicer-ов, как подробно разбирает заметка о round-trip без потерь: темы, extLst-блоки и calcChain

Создание табличного slicer-а из Delphi

TXLSXSlicers.Add — это весь API создания. Получив TXLSXTable и имя колонки, он материализует id колонок таблицы, находит или создаёт подложный TXLSXSlicerCache, резервирует соответствующее определённое имя и якорит представление справа от таблицы. Перегрузка с двумя аргументами выбирает размещение; версия с шестью аргументами принимает явный бокс с инклюзивными координатами, начиная с 1

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

    // Размещение по умолчанию: на две колонки правее таблицы,
    // 13 строк в высоту при стандартной высоте строки 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;

Два правила именования стоит знать, потому что это соглашения Excel, а не выдумки HotXLS. Кэш называется Slicer_<column>, причём символы, не входящие в идентификаторы, сворачиваются в подчёркивания, а при коллизиях добавляется числовой суффикс — Slicer_Region, затем Slicer_Region1 и так далее. И каждое имя кэша резервируется также как определённое имя книги с формулой #N/A — это и делает TXLSXSlicers.ReserveCacheDefinedName. Excel создаёт такое имя сам, и это важно: имена кэшей и определённые имена живут в одном пространстве имён, так что книга, где уже есть имя Slicer_Region, иначе столкнулась бы с коллизией при следующем сохранении. Если вы строите таблицы и валидацию вместе, соседние случаи взаимодействия определённых имён разобраны в статье про валидацию данных, AutoFilter и таблицы. Совместное использование кэша следует тому же правилу, что и в Excel. TXLSXSlicerCaches.FindForTableColumn(ATableId, ATableColumnId) опрашивается до создания нового кэша, так что два представления slicer-а над одной колонкой таблицы — одно на листе дашборда, другое на детальном листе — делят одну часть кэша и остаются синхронизированными по фильтру. Удаление представления запускает обратную проверку по всем листам книги и сбрасывает кэш вместе с зарезервированным именем #N/A, только когда исчезает последнее представление, ссылающееся на него

Как id отношений остаются стабильными с обеих сторон?

Они остаются стабильными, потому что HotXLS вычисляет rid slicer-а, воспроизводя точный порядок, в котором он эмитит все остальные отношения, и дописывает новые отношения в хвост. Ничего из уже присутствующего в файле не сдвигается. Звучит как over-engineering, пока вы не отладили альтернативу. На id отношений в .rels листа ссылаются изнутри части листа — гиперссылки, drawing, комментарии, части таблиц — а ECMA-376 Part 2 разрешает их позиционно внутри графа отношений, а не по какой-то стабильной идентичности. Вставь писатель отношение slicer-а в середину — и каждая ссылка после него сдвинется на единицу, а лист незаметно начнёт указывать не на ту часть. Поэтому XlsxWorksheetSlicerRelNumber проходит всю детерминированную цепочку — внешние гиперссылки, затем comment VML, комментарии, изображения в колонтитулах, threaded comments, drawing, фон листа, настройки принтера, таблицы, pivot-таблицы, query tables, отдельные ячейки таблиц — и только потом претендует на следующий номер для slicer-а. XlsxWorkbookSlicerCacheFirstRid делает то же самое на стороне книги: листы, sharedStrings, стили, VBA, внешние связи, тема, connections, pivot-кэши, person, calcChain, метаданные листов, XML maps, custom XML, и только затем серия slicerCache. Общая задача разобрана в заметке о разрешении отношений OPC. Расширение на стороне книги затем строится из этих номеров функцией XlsxBuildWorkbookSlicerCachesExtXml, и если в исходном файле уже были extLst-блоки slicerCaches, их сначала вырезают из сырого extLst, чтобы модель переэмитила их под текущие rid, а не оставила устаревшую копию

Якорь AlternateContent и его fallback для старых версий

Якорь drawing — единственное место, где разметка slicer-а становится по-настоящему странной, и эта странность намеренная. 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>

Посмотрите на два пространства имён. Токен Requires="sle15" разрешается в пространство имён slicer-ов 2012 года — это версионный гейт: читатель, который не понимает slicer-ы 2012 года, обязан пропустить всю ветку Choice. А вот graphicData/@uri и элемент sle:slicer внутри используют пространство имён 2010 года, потому что именно этот идентификатор со стороны drawing graphic frame использовал всегда. Перепутаете их местами — получите файл, который Excel открывает без жалоб, а затем отрисовывает пустую рамку: запоминающийся своей бесполезностью симптом. Ветка Fallback держит обычный прямоугольник с текстовым телом, объясняющим, что табличные slicer-ы в этой версии не поддерживаются — это то, что читатель эпохи 2007 рисует вместо элемента управления, и именно поэтому старый Excel может сохранить книгу в round-trip, ничего не разрушив. TXLSXSlicer выставляет геометрию как инклюзивный бокс с 1-базисными координатами через SetBounds(ARow1, ACol1, ARow2, ACol2) и отдельные свойства FromRow / ToCol, плюс AnchorType для привычного поведения «двигать и растягивать вместе с ячейками». HasAnchor равен False ровно в одном случае: slicer, вложенный внутрь групповой фигуры. Такие воспроизводятся вместе со своим групповым якорем байт в байт, и HotXLS намеренно не синтезирует для них второй якорь — иначе фигура была бы эмитирована дважды

Редактирование существующего 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;

  // Удаление последнего представления над колонкой сбрасывает её кэш
  // и зарезервированное определённое имя #N/A вместе с ним
  Sheet.Slicers.Delete(Sheet.Slicers.IndexByName('Region'));

Целочисленные свойства с тремя состояниями заслуживают примечания — они выглядят как случайность модели, но это не так. ShowCaption, LockedPosition, ColumnCount, StartItem и CustomListSort используют -1 для обозначения того, что атрибут отсутствовал в исходном файле и должен остаться отсутствующим при записи, а 0 и 1 — как явные значения. В нескольких местах Excel различает отсутствующий атрибут и явно заданный по умолчанию, и сохранение этого различия — то, что держит байтовый диф нетронутого сохранения чистым. RowHeight и ColumnWidth по схожей причине хранятся как сырые строки: Excel пишет десятичные дроби в эти EMU-атрибуты, и повторный парсинг их через целочисленный тип потерял бы точность на round-trip. RowHeightEmu — целочисленный удобный аксессор поверх того же хранилища, с XlsxSlicerDefaultRowHeight на 241300 EMU — 19 пунктов при соотношении XlsxEmuPerPoint 12700

Где это заканчивается

Два честных ограничения. Первое: HotXLS полностью моделирует табличный slicer, но не моделирует создание pivot-slicer-ов — вы можете открыть книгу с pivot-slicer-ами, прочитать их идентичность, сохранить их нетронутыми при сохранении и удалить их, но сторона кэша воспроизводится как байты, и API для построения такого с нуля нет. Второе: slicer сам по себе ничего не фильтрует. Это UI над колонкой таблицы, а состояние видимых строк, которым он управляет, живёт на стороне Excel; HotXLS пишет элемент управления и привязку, а не вычисленный результат фильтрации. Если в сохранённом файле нужны реально отфильтрованные строки — задайте заодно AutoFilter таблицы. Любой атрибут, который HotXLS не моделирует на <slicer> или <x15:tableSlicerCache>, проносится без изменений, так что slicer из более свежей сборки Excel после перемещения всё равно сериализуется обратно с нетронутыми незнакомыми атрибутами. Это свойство и делает безопасным путь пересборки по dirty-флагу: пересобирать из модели допустимо, только если модель — надмножество того, что было распарсено. Механизм расширений с GUID-тегами из ECMA-376 Part 1 §18.2.10 и определения частей slicer-а из [MS-XLSX] — то, что делает эту гарантию выразимой в принципе

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