Esquema de campos pivot XLSX válido no Delphi com HotXLS

O HotXLS escreve definições de tabelas dinâmicas XLSX cujos elementos pivotField e cacheField validam contra o esquema ECMA-376 Part 1 §18.10: os atributos de eixo usam os tokens ST_Axis axisRow, axisCol e axisPage, os campos da área de valores transportam dataField="1", as listas de items nunca estão vazias, e os campos de cache guardam um numFmtId numérico. Desde a v2.384.33 que o leitor também respeita as predefinições do esquema que antes lia mal

Os bugs por trás desta limpeza partilham um traço pouco lisonjeiro: nenhum falhou um teste alguma vez. O HotXLS escrevia uma tabela dinâmica, o HotXLS lia-a de volta, cada campo aterrava no eixo certo, e a suíte de round-trip ficou verde durante anos. O problema era que o escritor e o leitor tinham concordado em silêncio num dialeto privado. Uma tabela dinâmica construída a partir do Delphi parecia boa ao componente que a fez, enquanto uma verificação contra o CT_PivotField e o CT_CacheField revelava tokens de enumeração inválidos, um elemento vazio que o esquema proíbe e flags que o Excel espera mas nunca recebeu. Se gera tabelas dinâmicas num servidor e as envia a pessoas que as abrem no Excel ou as dão ao seu próprio parser, o único contrato que conta é o esquema, não o que quer que o seu próprio leitor por acaso perdoe

Porque é que os round trips do HotXLS nunca apanhavam os tokens de eixo errados?

Os round trips do HotXLS nunca apanhavam os tokens de eixo errados porque o leitor aceitava ambas as grafias. O velho XlsxPivotAxisAttr emitia axis="rowAxis", colAxis e pageAxis, que se leem naturalmente em inglês mas não existem no esquema; o ST_Axis define exatamente quatro valores, axisRow, axisCol, axisPage e axisValues. Entretanto o PivotAxisFromToken no lxPivotXml.pas aceitava tanto o token do esquema como o inventado, por isso todos os autotestes passavam. O escritor agora só emite os tokens do esquema, e o leitor continua a aceitar as grafias antigas para que ficheiros gravados por versões anteriores do HotXLS ainda carreguem com a disposição intacta

<!-- antes da v2.384.33: valor ST_Axis inválido, CT_Items vazio -->
<pivotField axis="rowAxis" defaultSubtotal="1"><items count="0"></items></pivotField>

<!-- desde a v2.384.33 -->
<pivotField axis="axisRow" defaultSubtotal="1">
  <items count="4"><item x="0"/><item x="1"/><item x="2" h="1"/><item t="default"/></items>
</pivotField>

O leitor tolerante aceitava ambas as grafias, por isso todos os round trips passavam enquanto o ficheiro quebrava qualquer verificação estrita de esquema — escreva só os quatro tokens ST_Axis e deixe o CT_Items transportar pelo menos um item

O que exige o CT_PivotField que o velho escritor saltava?

O CT_PivotField exige três coisas que o velho BuildPivotTableXml omitia ou lia mal. Primeiro, um campo agregado na área de valores tem de o dizer na sua própria definição com dataField="1"; o escritor agora põe essa flag em todos os campos referenciados por uma entrada em DataFields, e não só na lista <dataFields>. Segundo, o CT_Items precisa de pelo menos um item, por isso um campo sem items já não recebe um <items count="0"> vazio e o elemento inteiro é simplesmente omitido. Terceiro, cada item conserva o seu estado: h="1" para um item escondido (TXLSPivotItem.IsHidden) e sd="0" para detalhes recolhidos (IsDetailHidden), ambos os quais o velho escritor largava em cada gravação

A parte subtil são os items de subtotal no final. Quando um campo tem items, o Excel lista um item extra por função de subtotal depois dos items de dados, tipados com ST_ItemType: <item t="default"/> para o subtotal automático, depois sum, countA, avg, max, min, product, count, stdDev, stdDevP, var e varP para os explícitos. O HotXLS deriva essas entradas de TXLSPivotField.Subtotals no momento de gravar e conta-as no items count. Campos criados pelo AddPivotTable começam com um conjunto Subtotals vazio, que escreve defaultSubtotal="0" e nenhum item final, por isso pida subtotais explicitamente quando o relatório precisar deles. Note a armadilha de nomes: xlpsCount mapeia para countA (todas as entradas) e xlpsCountNums mapeia para count (só números)

Anatomia da lista de items pivot no HotXLS em que as entradas de items de dados são seguidas por items de subtotal finais derivados de TXLSPivotField.Subtotals como t=default e t=avg e contados no items count, com a armadilha de nomes xlpsCount para countA e xlpsCountNums para count explicitada — Campos do AddPivotTable começam com um conjunto Subtotals vazio, que escreve defaultSubtotal=0 e nenhum item final — pida as funções que quer e o escritor deriva um item por função na contagem

uses
  lxHandleX, lxPivot;

var
  Book  : TXLSXWorkbook;
  Sheet : TXLSXWorksheet;
  Pivot : TXLSPivotTable;
  Region: TXLSPivotField;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    Sheet := Book.Sheets[1];                  // base um, como o motor XLS
    Pivot := Sheet.AddPivotTable('Data!$A$1:$D$800', 3, 6, 'RegionTotals');
    if Pivot = nil then
      raise Exception.Create('Bad source range or anchor');

    Region := Pivot.AddRowField('Region');    // nil se não houver campo assim
    if Region <> nil then
      Region.Subtotals := [xlpsDefault, xlpsAverage];  // -> t="default", t="avg"
    Pivot.AddColumnField('Quarter');
    Pivot.AddDataFieldByName('Revenue', xlpaSum);      // marca o Revenue com dataField="1"

    Book.SaveAs('orders-pivot.xlsx');
  finally
    Book.Free;
  end;
end;

Como lê agora o HotXLS items de subtotal e predefinições do esquema?

O leitor do HotXLS agora salta qualquer item cujo atributo t esteja presente e não seja data, porque as entradas de subtotal, total geral e vazio não transportam índice de cache nenhum. Antes da v2.384.34 essas entradas eram carregadas como items ordinários com CacheItemIndex a -1, por isso uma tabela dinâmica feita no Excel voltava com membros fantasma que não apontavam para lado nenhum, e qualquer código que percorresse Items tinha de os filtrar à mão. Como o escritor reconstrói as entradas finais a partir de Subtotals, o trabalho do leitor é traduzi-las para esse conjunto, não mantê-las como dados

A segunda correção do leitor é sobre atributos ausentes. No esquema, o defaultSubtotal no CT_PivotField e o containsString no CT_SharedItems valem ambos true por predefinição, e o Excel omite-os quando guardam esse valor. O HotXLS lia um atributo ausente como false, o que significava que cada tabela dinâmica gravada pelo Excel perdia silenciosamente o seu subtotal predefinido ao carregar, e um campo de cache de texto puro era classificado como misto em vez de string. Esta é a imagem espelhada do bug do eixo: um escritor que escreve sempre todos os atributos nunca exercita o caminho das predefinições, por isso só ficheiros de outro produtor o expõem

Porque era numFmtId="General" inválido nos campos de cache?

O valor numFmtId="General" era inválido porque o ST_NumFmtId é um inteiro sem sinal, não um nome de formato. O velho escritor de cache tinha essa string codificada a ferro em cada cacheField, emprestando o nome que os utilizadores veem no diálogo Format Cells. O HotXLS agora escreve o NumberFormat do campo de cache como um número, que é 0 (o formato General incorporado) salvo se algo o definir. Um parser estrito que tipa atributos a partir do esquema rejeita o valor antigo de imediato, e essa é exatamente a classe de falha que se transforma num diálogo de reparação; o artigo sobre as regras OPC e de markup por trás do pedido de reparação do Excel cobre como esses diálogos são despoletados

Porque é que as tabelas dinâmicas abaixo da linha 65535 eram cortadas?

Tabelas dinâmicas XLSX colocadas na linha 65536 ou abaixo eram cortadas porque o modelo partilhado de tabelas dinâmicas guardava FirstRow, LastRow, FirstHeaderRow, FirstDataRow e os homólogos de coluna como Word, e o código de deslocação de linhas as prendia com Min(.., High(Word)). Isso é um resquício do registo SxView do BIFF8, onde 16 bits chegam, mas uma folha XLSX corre até 1.048.576 linhas. Desde a v2.384.37 que essas propriedades no TXLSPivotTable são Integer, as prisões desapareceram, e só o escritor BIFF8 estreita os valores. O TXLSXWorksheet.AddPivotTable e o AddPivotTableCopy agora devolvem nil para uma âncora fora de 1..1048576 por 1..16384, ou para uma cópia cuja extensão sairia da grelha

Âncora de tabela dinâmica do HotXLS na linha 70001 contra o teto de 16 bits em que FirstRow e LastRow eram guardados como Word e presos com Min contra High(Word) em 65535, cortando tabelas dinâmicas na linha ou abaixo dela até a v2.384.37 passar o modelo para campos Integer com devolução de nil fora da grelha — Os campos Word eram um resquício do SxView do BIFF8 num formato cujas folhas correm até 1048576 linhas — uma âncora para além da linha 65536 dava a volta para o intervalo de 16 bits e perdia a tabela dinâmica ao gravar

var
  Pivot: TXLSPivotTable;
  Check: TXLSXWorkbook;
begin
  // A linha 70001 dava a volta ao intervalo de 16 bits; agora sobrevive à gravação e à carga
  Pivot := Sheet.AddPivotTable('Data!$A$1:$D$800', 70001, 1, 'LateTotals');
  if Pivot = nil then
    Exit;  // âncora fora da folha ou intervalo de origem não resolvível
  Pivot.AddRowField('Region');
  Pivot.AddDataFieldByName('Revenue', xlpaSum);
  Book.SaveAs('late.xlsx');

  Check := TXLSXWorkbook.Create;
  try
    Check.Open('late.xlsx');
    Pivot := Check.Sheets[1].PivotTables.FindByName('LateTotals');
    Assert((Pivot <> nil) and (Pivot.FirstRow = 70001));
  finally
    Check.Free;
  end;
end;

O motor XLS clássico recebeu a correção correspondente na v2.384.38. O modelo dele guardava os valores brutos de base zero do SxView e do DConRef e passava âncoras do AddPivotTable diretas, enquanto a documentação, as demos e o motor XLSX usavam todos células de base um como Cells[Row, Col]. Ambos os motores agora mantêm posições de base um no modelo, o leitor BIFF8 acrescenta 1 e o escritor subtrai 1 na fronteira do registo, por isso código que ancorava em (0, 0) tem de passar para (1, 1), porque o AddPivotTable clássico agora devolve nil para uma âncora fora de 1..65536 por 1..256; a nova chamada escreve os mesmos bytes que a velha. A disposição dos registos em si não mudou e está descrita nos registos SX do BIFF8 por trás das tabelas dinâmicas .xls clássicas

Valide contra o esquema, não contra o seu próprio leitor

A lição generaliza para além das tabelas dinâmicas: um leitor tolerante esconde violações do escritor, por isso um round trip pelo seu próprio código prova consistência, não correção. Todos os bugs aqui sobreviveram porque o lado tolerante e o lado defeituoso viviam na mesma biblioteca. As verificações que apanham de facto esta classe de defeito são uma validação de esquema das partes geradas, ficheiros produzidos pelo Excel passados pelo seu leitor com atributos omitidos nas predefinições, e fixtures que fixam o token exato em vez do resultado analisado. Tabelas dinâmicas que construa através da API, incluindo os campos calculados, items calculados e disposições de percentagem do total mostrados em construir e atualizar tabelas dinâmicas XLSX com campos calculados, recebem o XML corrigido sem alteração de código, enquanto tabelas dinâmicas carregadas de ficheiros do Excel continuam a repetir as suas partes originais até as modificar

Todas estas correções chegam no atual componente de folhas de cálculo HotXLS para Delphi, que lê e escreve XLS, XLSX e tabelas dinâmicas a partir de Delphi e C++Builder sem Excel nem automação COM na máquina