Slicers de tabelas Excel em Delphi: round-trip com o HotXLS

O HotXLS cria slicers de tabelas Excel a partir de Delphi e C++Builder com uma única chamada — Worksheet.Slicers.Add(Table, ColumnName) — e escreve todos os oito locais do package de que um slicer precisa: as partes slicer e slicerCache, os dois content-type overrides, as relações da worksheet e do workbook com os respetivos blocos extLst, e a âncora do drawing

Um slicer parece-se com um controlo que larga numa folha. No ficheiro não é nada disso. É um registo distribuído por duas partes novas, dois grafos de relações, três blocos de extensão e uma âncora de drawing, e o conjunto é validado como uma unidade quando o Excel abre o package. Falte uma peça e não obtém um slicer degradado; obtém o diálogo de reparação, seguido de um workbook com o slicer removido em silêncio. É esse modo de falha que torna os slicers irritantes de emitir à mão, e é por isso que a engenharia interessante aqui é bookkeeping em vez de rendering

Porque é que um slicer toca em oito sítios do package?

Porque um slicer são três conceitos separados que o OPC obriga a viver em três casas separadas: o estado do filtro (no âmbito do workbook), a view (no âmbito da worksheet) e a forma (no âmbito do drawing). O HotXLS escreve exatamente oito pontos de aterragem por slicer de tabela, e cada um deles é estrutural

• xl/slicers/slicerN.xml — uma parte por worksheet, que guarda cada view <slicer> dessa folha no namespace http://schemas.microsoft.com/office/spreadsheetml/2009/9/main
• xl/slicerCaches/slicerCacheN.xml — ao nível do workbook, um por coluna de tabela fatiada, que carrega o binding real
• um <Override> em [Content_Types].xml para cada uma dessas duas partes, usando application/vnd.ms-excel.slicer+xml e application/vnd.ms-excel.slicerCache+xml (a ECMA-376 Parte 2 exige um content type para cada parte, e o Excel recusa o package sem eles)
• uma relação da worksheet do tipo .../office/2007/relationships/slicer, mais uma extensão <x14:slicerList> dentro do extLst da worksheet que aponta para esse rid
• uma relação do workbook do tipo .../office/2007/relationships/slicerCache, mais uma extensão <x15:slicerCaches> dentro do extLst do workbook que lista os mesmos rids
• uma âncora mc:AlternateContent no drawing da folha que dá ao slicer a sua posição e tamanho
• um bloco <x15:tableSlicerCache> dentro da parte de cache que o liga a um table id e a um table column id

Os blocos de extensão usam o mecanismo padrão extLst da ECMA-376 Parte 1 §18.2.10: cada <ext> carrega um atributo uri que identifica a funcionalidade, e os leitores que não reconhecem o GUID saltam o bloco. O HotXLS distingue slicers de tabela de slicers de pivot apenas por esse GUID — {3A4CF648-6AED-40f4-86FF-DC5316D8AED3} para o slicerList de tabela, {A8765BA9-456A-4dab-B4F3-ACF838C121DE} para o de pivot, e correspondentemente {46BE6895-7355-4a93-B00E-2C351335B9C9} e {BBE1A952-AA13-448e-AADC-164F8A28A991} para as duas extensões slicerCaches do workbook. O binding da tabela em si vive sob {2F2917AC-EB37-4324-AD4E-5DD8C200BD13}. Essas constantes estão expostas na unit como XlsxExtUriSlicerListX15, XlsxExtUriWbSlicerCachesX15 e XlsxExtUriTableSlicerCache, pelo que nada no path de escrita carrega uma magic string

Que partes podem ser reproduzidas verbatim e quais têm de ser reconstruídas?

O HotXLS responde com uma única regra: uma parte só pode reproduzir os seus bytes originais se nada do que referencia puder ser renumerado durante o save. As caches de slicer de tabela falham esse teste, por isso o HotXLS reconstrói-as a partir do modelo em cada save, incondicionalmente — mesmo quando o utilizador nunca tocou no slicer. A razão é o tableId: o elemento <x15:tableSlicerCache> identifica a sua tabela pelo atributo id de <table>, e o HotXLS renumerara os table ids ao nível global do workbook quando escreve. Uma implementação inicial guardava a parte de cache como bytes crus no open e devolvia-os no save, que é a estratégia certa para a maioria das partes opacas. Produzia ficheiros que abriam limpos no caso comum e disparavam uma reparação no caso interessante: adicione uma tabela numa folha anterior, grave, e a cache passa a apontar para um table id que pertence a outra coisa qualquer, ou a coisa nenhuma. O bug é invisível numa regressão simples de abrir-e-gravar porque os ids, por acaso, não se movem

As caches com forma de pivot seguem o caminho oposto. A sua payload é <pivotTables> mais um bloco <x14:data> que descreve os itens em cache, o estado do sort e os índices de item — uma estrutura sem renumerar do lado do HotXLS e que é cara de modelar com fidelidade. Essas são mantidas como bytes crus e reproduzidas exatamente, com TXLSXSlicerCache.IsPivot e IsTableCache a distinguir as duas. As views de slicer seguem uma terceira política, com dirty tracking: um elemento <slicer> intocado e a sua âncora de drawing reproduzem verbatim, e no momento em que qualquer view da folha é modificada toda a parte da folha é regenerada a partir do modelo. É essa a forma geral do critério — reprodução verbatim onde as referências são estáveis, reconstrução a partir do modelo onde o writer reatribui identidade, dirty tracking onde ambos são aceitáveis — e aplica-se muito para além dos slicers, como as notas de round-trip lossless sobre themes, blocos extLst e calcChain desenvolvem em maior profundidade

Criar um slicer de tabela a partir de Delphi

O TXLSXSlicers.Add é toda a API de criação. Dado um TXLSXTable e um nome de coluna, materializa os table column ids, encontra ou cria o TXLSXSlicerCache de suporte, reserva o defined name correspondente e ancora uma view à direita da tabela. O overload de dois argumentos escolhe a colocação; o de seis aceita uma caixa inclusiva explícita baseada em 1

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

    // Colocação padrão: duas colunas afastadas da tabela,
    // 13 linhas de altura à altura de linha padrão de 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;

Duas regras de nomenclatura valem a pena saber porque são convenções do Excel e não invenções do HotXLS. A cache chama-se Slicer_<column> com caracteres não identificadores dobrados em underscores, e colisões anexam um sufixo numérico — Slicer_Region, depois Slicer_Region1, e assim por diante. E cada nome de cache é também reservado como defined name do workbook cuja fórmula é #N/A, que é o que o TXLSXSlicers.ReserveCacheDefinedName faz. O Excel cria esse nome por si, e isso importa: nomes de cache e defined names partilham um único namespace, pelo que um workbook que já tenha um nome chamado Slicer_Region colidiria de outro modo no próximo save. Se construir tabelas e validação em conjunto, as interações de defined names em o artigo sobre data validation, AutoFilter e tabelas cobrem os casos vizinhos. A partilha segue a mesma regra que o Excel usa. O TXLSXSlicerCaches.FindForTableColumn(ATableId, ATableColumnId) é consultado antes de se criar uma cache nova, pelo que duas views de slicer sobre a mesma coluna de tabela — uma na folha de dashboard, outra na folha de detalhe — partilham uma única parte de cache e mantêm-se sincronizadas no filtro. Apagar uma view corre a verificação inversa por todas as folhas do workbook e larga a cache mais o seu nome #N/A reservado apenas quando a última view que a referencia desapareceu

Como é que os relationship ids se mantêm estáveis nos dois lados?

Mantêm-se estáveis porque o HotXLS calcula o rid do slicer reproduzindo a ordem exata em que emite todas as outras relações e anexa as relações novas no fim. Nada do que já está no ficheiro se move. Isto soa a over-engineering até ter depurado a alternativa. Os relationship ids num .rels de worksheet são referenciados de dentro da própria parte da worksheet — por hyperlinks, pelo drawing, por comentários, por partes de tabela — e a ECMA-376 Parte 2 resolve-os posicionalmente dentro do grafo de relações, não por qualquer identidade estável. Se o writer inserir a relação do slicer no meio, todas as referências a seguir deslocam-se por um, e a folha começa silenciosamente a apontar para a parte errada. Por isso o XlsxWorksheetSlicerRelNumber percorre a cadeia determinística completa — hyperlinks externos, depois o VML de comentários, comentários, imagens de header/footer, threaded comments, o drawing, o fundo da folha, printer settings, tabelas, pivot tables, query tables, table single cells — e só então reivindica o número seguinte para o slicer. O XlsxWorkbookSlicerCacheFirstRid faz o mesmo do lado do workbook: sheets, sharedStrings, styles, VBA, external links, theme, connections, pivot caches, person, calcChain, sheet metadata, XML maps, custom XML, e depois a sequência de slicerCache. O problema geral é tratado em o artigo sobre resolução de relações OPC. A extensão do lado do workbook é depois construída a partir desses números pelo XlsxBuildWorkbookSlicerCachesExtXml, e se o ficheiro de origem já tinha blocos de extensão slicerCaches, são removidos do extLst cru primeiro, para o modelo os re-emitir com os rids atuais em vez de deixar uma cópia stale para trás

A âncora AlternateContent e o seu fallback legado

A âncora do drawing é o único sítio onde o markup do slicer fica genuinamente estranho, e a estranheza é deliberada. O HotXLS emite a forma dentro de um elemento mc:AlternateContent segundo a ECMA-376 Parte 3, com um ramo Choice que o Excel 2013 e posteriores seguem e um ramo Fallback para tudo o que é mais antigo

// O que o XlsxSlicerAnchorPayloadXml produz, estruturalmente:
//
//   <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>

Repare nos dois namespaces. O token Requires="sle15" resolve para o namespace de slicers de 2012, que é o gate de versão — um leitor que não perceba slicers de 2012 tem de saltar o ramo Choice inteiro. Mas o graphicData/@uri e o elemento sle:slicer lá dentro usam o namespace de 2010, porque é esse o identificador do lado do drawing que o graphic frame sempre usou. Trocar estes produz um ficheiro que o Excel abre sem reclamar e depois renderiza como um frame vazio, que é um sintoma inutilmente memorável. O ramo Fallback guarda um retângulo simples com um corpo de texto a explicar que slicers de tabela não são suportados nesta versão — é isso que um leitor da era de 2007 desenha no lugar do controlo, e é por isso que um Excel antigo consegue fazer round-trip do workbook sem destruir nada. O TXLSXSlicer expõe a geometria como uma caixa inclusiva baseada em 1 através de SetBounds(ARow1, ACol1, ARow2, ACol2) e das propriedades individuais FromRow / ToCol, mais AnchorType para o comportamento habitual de mover-e-dimensionar-com-células. O HasAnchor é False num caso específico: um slicer aninhado dentro de uma group shape. Esses reproduzem com a sua âncora de grupo verbatim e o HotXLS deliberadamente não sintetiza uma segunda âncora para eles, porque fazê-lo emitiria a forma duas vezes

Editar um slicer existente

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

  // Remover a última view sobre uma coluna larga a sua cache
  // e o defined name #N/A reservado com ela
  Sheet.Slicers.Delete(Sheet.Slicers.IndexByName('Region'));

As propriedades inteiras de três estados merecem uma nota, porque parecem um acidente de modelação e não são. ShowCaption, LockedPosition, ColumnCount, StartItem e CustomListSort usam todas -1 para significar que o atributo estava ausente no ficheiro de origem e deve continuar ausente na escrita, com 0 e 1 como valores explícitos. O Excel trata ausente e explicitamente-padrão de formas diferentes em alguns sítios, e preservar essa distinção é o que mantém limpo um byte diff de um save intocado. RowHeight e ColumnWidth são mantidas como strings crus por uma razão relacionada: o Excel escreve decimais nesses atributos EMU, e reanalisá-los através de um inteiro perderia precisão no round-trip. RowHeightEmu é o accessor inteiro de conveniência sobre o mesmo armazenamento, com XlsxSlicerDefaultRowHeight em 241300 EMU — 19 pontos à razão XlsxEmuPerPoint de 12700

Onde isto para

Dois limites honestos. Primeiro, o HotXLS modela o slicer de tabela por completo mas não modela a criação de slicers de pivot — pode abrir um workbook com slicers de pivot, ler a identidade deles, mantê-los intactos num save e apagá-los, mas o lado da cache reproduz como bytes e não há API para construir um de raiz. Segundo, um slicer não filtra nada por si. É uma UI sobre uma coluna de tabela, e o estado de linhas visíveis que conduz é do lado do Excel; o HotXLS escreve o controlo e o binding, não um resultado de filtro calculado. Se precisar de linhas realmente filtradas no ficheiro gravado, defina também o AutoFilter da tabela. Qualquer atributo que o HotXLS não modele em <slicer> ou <x15:tableSlicerCache> é transportado inalterado, pelo que um slicer vindo de um build mais novo do Excel continua a re-serializar com os seus atributos desconhecidos intactos mesmo depois de o mover. É essa a propriedade que torna seguro seguir o path de dirty rebuild: reconstruir a partir do modelo só é aceitável se o modelo for um superconjunto do que analisou. O mecanismo de extensão com tags GUID da ECMA-376 Parte 1 §18.2.10, e as definições das partes de slicer em [MS-XLSX], são o que torna essa garantia expressível em primeiro lugar

Os slicers de tabela vêm no HotXLS Delphi Component, que lê e escreve workbooks XLSX, XLSM e BIFF legado desde o Delphi 5 até à versão atual, mais C++Builder e Free Pascal, sem instalação do Excel e sem OLE. A referência completa da API e um build de trial estão na página do produto HotXLS Delphi Spreadsheet Component