Validade de schema de campos pivot XLSX em Delphi com HotXLS

O HotXLS escreve definições de tabela dinâmica XLSX cujos elementos pivotField e cacheField validam contra o schema ECMA-376 Part 1 §18.10: atributos de eixo usam os tokens ST_Axis axisRow, axisCol e axisPage, campos da área de valores carregam dataField="1", listas de itens nunca ficam vazias, e campos de cache guardam um numFmtId numérico. Desde a v2.384.33 o reader também respeita os defaults do schema que ele errava antes

Os bugs por trás desta limpeza dividem um traço nada lisonjeiro: nenhum deles jamais derrubou um teste. O HotXLS escrevia um pivot, o HotXLS lia de volta, todo campo caía no eixo certo, e a suíte de round-trip ficou verde por anos. O problema era que writer e reader tinham combinado silenciosamente um dialeto privado. Um pivot construído do Delphi parecia bom para o componente que o fez, enquanto uma checagem contra CT_PivotField e CT_CacheField revelava tokens de enumeração inválidos, um elemento vazio que o schema proíbe e flags que o Excel espera mas nunca recebeu. Se você gera pivots num servidor e os manda para pessoas que abrem no Excel ou os alimentam aos próprios parsers delas, o único contrato que conta é o schema, não aquilo que o seu próprio reader por acaso perdoa

Por que os round trips do HotXLS nunca pegaram os tokens de eixo errados?

Os round trips do HotXLS nunca pegaram os tokens de eixo errados porque o reader aceitava as duas grafias. O antigo XlsxPivotAxisAttr emitia axis="rowAxis", colAxis e pageAxis, que soam naturais em inglês mas não existem no schema; o ST_Axis define exatamente quatro valores, axisRow, axisCol, axisPage e axisValues. Enquanto isso o PivotAxisFromToken no lxPivotXml.pas casava tanto o token do schema quanto o inventado, então todo autoteste passava. O writer agora emite só os tokens do schema, e o reader continua aceitando as grafias antigas para que arquivos salvos por versões anteriores do HotXLS ainda carreguem com o layout intacto

<!-- 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 que o CT_PivotField exige que o antigo writer pulava?

O CT_PivotField exige três coisas que o antigo BuildPivotTableXml omitia ou errava. Primeiro, um campo agregado na área de valores precisa dizer isso na própria definição com dataField="1"; o writer agora seta essa flag em todo campo referenciado por uma entrada em DataFields, e não só na lista <dataFields>. Segundo, o CT_Items precisa de ao menos um item, então um campo sem itens não ganha mais um <items count="0"> vazio e o elemento inteiro simplesmente é omitido. Terceiro, cada item mantém seu estado: h="1" para item oculto (TXLSPivotItem.IsHidden) e sd="0" para detalhes recolhidos (IsDetailHidden), ambos os quais o antigo writer derrubava a cada save

A parte sutil são os itens de subtotal no final. Quando um campo tem itens, o Excel lista um item extra por função de subtotal depois dos itens de dados, tipado 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 do TXLSPivotField.Subtotals na hora do save e as conta no items count. Campos criados pelo AddPivotTable começam com um conjunto Subtotals vazio, o que grava defaultSubtotal="0" e nenhum item final, então peça os subtotais explicitamente quando o relatório precisa deles. Note a armadilha de nomenclatura: xlpsCount mapeia para countA (todas as entradas) e xlpsCountNums mapeia para count (só números)

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 1, 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 tal campo
    if Region <> nil then
      Region.Subtotals := [xlpsDefault, xlpsAverage];  // -> t="default", t="avg"
    Pivot.AddColumnField('Quarter');
    Pivot.AddDataFieldByName('Revenue', xlpaSum);      // marca Revenue com dataField="1"

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

Como o HotXLS lê itens de subtotal e defaults do schema agora?

O reader do HotXLS agora pula qualquer item cujo atributo t está presente e não é data, porque entradas de subtotal, total geral e vazio não carregam índice de cache. Antes da v2.384.34 essas entradas eram carregadas como itens ordinários com CacheItemIndex setado para -1, então um pivot feito no Excel voltava com membros fantasmas que não apontavam para lugar nenhum, e qualquer código que percorresse Items tinha que filtrá-los à mão. Como o writer reconstrói as entradas finais a partir de Subtotals, o trabalho do reader é traduzi-las para esse conjunto, não mantê-las como dados

O segundo fix do reader é sobre atributos ausentes. No schema, defaultSubtotal no CT_PivotField e containsString no CT_SharedItems têm default true, e o Excel os omite quando guardam esse default. O HotXLS lia um atributo faltante como false, o que significava que todo pivot salvo pelo Excel perdia silenciosamente o subtotal default no load, 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 writer que sempre soletra cada atributo nunca exercita o caminho do default, então só arquivos de outro produtor o expõem

Por que numFmtId="General" era inválido em 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 antigo writer de cache hard-codava essa string em todo cacheField, pegando emprestado o nome que os usuários veem no diálogo Format Cells. O HotXLS agora escreve o NumberFormat do campo de cache como número, que é 0 (o formato General embutido) a menos que algo o tenha setado. Um parser estrito que tipa atributos a partir do schema rejeita o valor antigo de cara, e essa é exatamente a classe de falha que vira diálogo de reparo; o artigo sobre as regras de OPC e markup por trás do prompt de reparo do Excel cobre como esses diálogos são disparados

Por que tabelas dinâmicas abaixo da linha 65535 eram cortadas?

Tabelas dinâmicas XLSX posicionadas na linha 65536 ou abaixo eram cortadas porque o modelo compartilhado de pivot guardava FirstRow, LastRow, FirstHeaderRow, FirstDataRow e os correspondentes de coluna como Word, e o código de deslocamento de linhas os travava com Min(.., High(Word)). Isso é um resquício do registro SxView do BIFF8, onde 16 bits bastam, mas uma planilha XLSX vai até 1.048.576 linhas. Desde a v2.384.37 essas propriedades no TXLSPivotTable são Integer, os travamentos sumiram, e só o writer BIFF8 estreita os valores. O TXLSXWorksheet.AddPivotTable e o AddPivotTableCopy agora devolvem nil para âncora fora de 1..1048576 por 1..16384, ou para uma cópia cuja extensão sairia da grade

var
  Pivot: TXLSPivotTable;
  Check: TXLSXWorkbook;
begin
  // A linha 70001 antes virava no intervalo de 16 bits; agora sobrevive a save e load
  Pivot := Sheet.AddPivotTable('Data!$A$1:$D$800', 70001, 1, 'LateTotals');
  if Pivot = nil then
    Exit;  // âncora fora da planilha 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 Classic ganhou o fix correspondente na v2.384.38. O modelo dele guardava os valores crus de base 0 do SxView e do DConRef e passava âncoras do AddPivotTable direto, enquanto a documentação, as demos e o motor XLSX usavam células de base 1 como Cells[Row, Col]. Os dois motores agora mantêm posições de base 1 no modelo, o reader BIFF8 soma 1 e o writer subtrai 1 na fronteira do registro, então código que ancorava em (0, 0) precisa migrar para (1, 1), porque o AddPivotTable Classic agora devolve nil para âncora fora de 1..65536 por 1..256; a chamada nova grava os mesmos bytes que a antiga. O layout do registro em si não mudou e está descrito em os registros SX do BIFF8 por trás das tabelas dinâmicas de .xls clássico

Valide contra o schema, não contra o seu próprio reader

A lição generaliza para além de pivots: um reader tolerante esconde violações do writer, então um round trip pelo seu próprio código prova consistência, não correção. Todo bug aqui sobreviveu porque o lado tolerante e o lado defeituoso moravam na mesma biblioteca. As checagens que de fato pegam essa classe de defeito são uma validação de schema das partes geradas, arquivos produzidos pelo Excel passados pelo seu reader com atributos omitidos nos defaults, e fixtures que fixam o token exato em vez do resultado parseado. Pivots que você constrói pela API, incluindo os calculated fields, calculated items e layouts percent-of-total mostrados em construir e atualizar tabelas dinâmicas XLSX com calculated fields, recebem o XML corrigido sem mudança de código, enquanto pivots carregados de arquivos do Excel continuam reproduzindo as partes originais deles até você modificá-los

Todos esses fixes vão na versão atual do componente de planilha HotXLS para Delphi, que lê e escreve XLS, XLSX e tabelas dinâmicas do Delphi e do C++Builder sem Excel nem automação COM na máquina