Quase toda parte do formato binário legado do Excel é um único registro com um tipo claro de dois bytes e comprimento de dois bytes. Uma célula é um LABELSST ou um NUMBER. Uma região mesclada é um MERGEDCELLS. Você pode ler a maior parte de uma planilha percorrendo os registros um por vez e despachando pelo tipo do registro. As tabelas dinâmicas (PivotTables) quebram esse ritmo. Uma única tabela dinâmica não é apenas um registro, é um pequeno programa composto por dezenas de registros cooperativos distribuídos em dois locais diferentes no mesmo fluxo de documento composto do OLE, e os relacionamentos entre eles são posicionais, compactados por bits e implacáveis. Essa é a estrutura que a maioria dos leitores de BIFF8 ignora completamente ou preserva como bytes opacos, porque gravar uma a partir do zero significa reproduzir cada referência cruzada que o próprio Excel mantém.
A razão pela qual uma tabela dinâmica é difícil é que ela na verdade consiste em dois artefatos soldados um ao outro. Há o cache da tabela dinâmica, um snapshot independente dos dados de origem com seu próprio subfluxo, e há a visualização da tabela (table view), o layout que estabelece quais campos ficam em qual eixo. O cache e a visualização referenciam-se mutuamente por índice. Erre um único índice e o arquivo abrirá com um erro de atualização ou com uma grade silenciosamente vazia.
O cache da tabela dinâmica é um subfluxo próprio
O cache reside no fluxo de globais da pasta de trabalho (workbook globals stream) como um subfluxo completo do BIFF, emoldurado por um registro BOF cujo tipo de documento é 0x0006 (o valor que marca um cache de tabela dinâmica, em oposição a 0x0005 para a pasta de trabalho ou 0x0010 para uma planilha) e fechado pelo respectivo EOF. Dentro desse quadro, a estrutura é fixa. Um registro SXDB é o cabeçalho do cache. Ele carrega a contagem de registros, a quantidade de campos do cache e o identificador do fluxo que a visualização da tabela citará para se vincular a este cache. Cada coluna de origem, então, contribui com um registro de definição de campo SXFDB seguido por um SXFDBType que a classifica, e depois os valores únicos que a coluna assumiu, emitidos como um registro de item tipado por valor distinto.
Os registros de itens são onde o cache mostra sua utilidade. Um valor de texto torna-se um SXSTRING, um valor numérico um SXNUM, um valor lógico um SXBOOLEAN e um erro de fórmula um SXERR. O cache não armazena a grade de origem, ele armazena os valores distintos por campo, além de uma tabela de índices que informa, para o registro n, qual item distinto cada campo assumiu. É por isso que construir uma tabela dinâmica programaticamente não é uma questão de copiar células. Você precisa escanear o intervalo de origem, inferir o tipo de cada campo a partir dos valores que ele contém, remover duplicatas em uma lista de itens tipada e registrar cada linha como uma tupla de índices de itens. O HotXLS faz exatamente isso: uma coluna totalmente numérica é emitida com itens SXNUM, uma coluna de texto misto torna-se itens SXSTRING e as datas são transportadas como valores seriais pelo mesmo caminho numérico.
SXDBB e a compactação de bits que o torna interessante
A tabela de índices por registro é a parte mais curiosa de toda a estrutura do ponto de vista técnico, e ela reside no registro SXDBB. A codificação mais óbvia armazenaria o índice de item de cada campo como uma palavra de 16 bits. O Excel não faz isso. Ele compacta o índice de cada campo no número exato de bits necessários para endereçar os itens desse campo, e nada mais. A largura é de ceil(log2(itemCount + 1)) bits. O + 1 é importante: o valor extra é uma sentinela que significa "em branco, sem valor para este campo neste registro", de modo que um campo com três itens distintos precisa representar quatro estados e, portanto, consome dois bits, não o único bit que três itens apenas sugeririam. Um campo sem itens não contribui com bits e é pulado inteiramente durante a compactação.
Os bits de um registro são concatenados entre todos os campos e, em seguida, o próximo registro começa em uma nova fronteira de byte. Os registros são alinhados por byte, não compactados por bits de ponta a ponta, o que torna o acesso aleatório à tabela viável ao custo de alguns bits de preenchimento por linha. A compactação dentro de um byte ocorre pelo bit menos significativo primeiro. Uma vez que você aceita essas duas regras, o codificador é uma bomba de bits direta, e o decodificador é o espelho dele.
// Width of one field's index in the SXDBB stream.
// citmTotal distinct items need ceil(log2(citmTotal + 1)) bits,
// the +1 reserving a "blank" sentinel value.
function BitsForFieldItems(itemCount: Integer): Integer;
var
capacity: Integer;
begin
Result := 0;
if itemCount <= 0 then
Exit; // empty field contributes zero bits
Result := 1;
capacity := 2;
while capacity < itemCount + 1 do
begin
Inc(Result);
capacity := capacity * 2;
end;
end;O motivo pelo qual esse detalhe não pode ser ignorado é o teto de 8224 bytes em um único registro BIFF. Cada registro no formato, incluindo os registros de tabela dinâmica, deve conter sua carga em no máximo 8224 bytes, e um cache ativo de tabela dinâmica com milhares de linhas de origem ultrapassará esse limite muito antes de emitir todas as linhas. Portanto, a tabela de índices é dividida. O HotXLS limita um corpo SXDBB individual a 8220 bytes, que é o limite de registro de 8224 menos o cabeçalho de quatro bytes de tipo e comprimento, divide isso pela largura em bytes de um registro compactado para saber quantas linhas inteiras cabem e, em seguida, emite tantos registros SXDBB contínuos quantos a contagem de linhas exigir. Cada continuação reinicia de forma limpa em uma fronteira de registro, para que nenhuma linha seja dividida entre dois registros. Um leitor que conhece a largura de bits por registro pode percorrer cada SXDBB em sequência como se fossem um array de bits contíguo.
O layout da visualização: SXLI para o corpo, SXPI para a página
Com o cache construído, a visualização da tabela (table view) é a segunda metade. Seu núcleo são os itens de linha do eixo (axis line items), as linhas do corpo da tabela dinâmica que enumeram cada combinação de valores de campo de linha e de coluna que a tabela desenha. Eles são transportados em registros SXLI (tipo de registro 0x00B5, descrito no documento [MS-XLS] §2.4.275). Um único SXLI armazena muitas linhas, novamente até que o limite de 8224 bytes exija um novo registro, e ele usa um pequeno truque de compactação: cada linha armazena apenas como ela se difere da linha acima dela, expresso como uma contagem de prefixo comum, de modo que um eixo profundamente aninhado não repita os valores dos campos externos em cada linha. A linha de total geral e a primeira linha de qualquer registro sempre redefinem essa contagem de prefixo para zero, para que o leitor nunca precise olhar para trás de uma fronteira de registro para reconstruir uma linha.
O eixo de página, os campos de filtro que ficam acima de uma tabela dinâmica, é um registro separado. O SXPI (tipo de registro 0x00B6, [MS-XLS] §2.4.276) carrega uma entrada de dez bytes por campo de página: o índice do campo dinâmico isxvd, o item de cache selecionado iCache, uma palavra de posição ipos e um ID de objeto legado objId. O valor de iCache é o item a ser observado. Um campo de página que exibe "(Tudo)", não filtrando nada, armazena a sentinela 0x7FFD em vez de um índice de item real. Uma tabela dinâmica construída programaticamente abre com cada campo de página definido como "(Tudo)" até que o chamador pré-selecione um item, momento em que o índice do cache desse item substitui a sentinela e o Excel abre com o filtro já aplicado. Junto com esses, ficam os registros de suporte que descrevem os campos individuais e suas formatações: SXVD e SXVDEx para definições de visualização de campo, SXIVD para as listas de índices de campo que ordenam cada eixo e SXFormat para formatação de números, cada um indexando de volta ao mesmo cache que as linhas do corpo referenciam.
Dois gravadores em um: blobs brutos e o modelo tipado
Existe um motivo estrutural pelo qual o HotXLS mantém dois caminhos completamente separados para gravar uma tabela dinâmica, e isso provém diretamente das demandas de fidelidade. Quando uma pasta de trabalho é lida do disco, seus registros de tabela dinâmica foram gravados pelo Excel ou por algum outro gerador, e eles podem usar variantes de registros, peculiaridades de ordenação ou registros de extensão que nenhum gerador de terceiros modela completamente. A única coisa segura a fazer com esses bytes é devolvê-los inalterados. Portanto, uma tabela dinâmica proveniente de um arquivo é marcada com a flag FromRawBlobs = True, e ao salvar, o gravador repete os blobs de registros preservados de forma literal. Nada é regenerado, nada é reinterpretado, e a jornada de abertura e salvamento é estável em nível de bytes.
Uma tabela dinâmica que o programa construiu é o caso oposto. Não há bytes originais a serem preservados, apenas o modelo de objeto tipado: um TXLSPivotCache com seus campos e listas de itens, e um TXLSPivotTable com suas atribuições de eixos. Essa tabela é marcada com a flag FromRawBlobs = False, e o gravador a serializa do modo complexo, emitindo um subfluxo de cache BOF = 0x0006 novo, compactando a tabela de índices SXDBB a partir dos índices de itens que o modelo tipado contém e organizando os registros SXLI e SXPI conforme a configuração do eixo. A flag é o que permite que ambos os tipos coexistam em uma única pasta de trabalho. Sem ela, um gravador único teria que descartar a fidelidade das tabelas importadas ou recusar a geração de novas. Quaisquer registros de extensão específicos do gerador que uma tabela importada carregava são mantidos como registros suplementares, acessíveis por meio da lista SupplementalRecords da tabela, de modo que uma tabela inspecionada por meio do modelo tipado não perca as partes que o modelo não descreve.
Construindo uma tabela dinâmica no código
Toda a estrutura acima fica por trás de uma única chamada. O AddPivotTable recebe o intervalo de origem na notação A1, a célula de destino onde o canto superior esquerdo da tabela se ancora e um nome. Ele analisa o intervalo, escania-o para inferir tipos de campos e constrói o cache (reutilizando um cache existente se outra tabela já se vincular ao mesmo intervalo) e retorna um TXLSPivotTable tipado com um campo por coluna de origem, com todos os campos inicialmente fora do eixo. Você então posiciona os campos nos eixos e escolhe uma agregação. A assinatura é exatamente esta, e o cache, a compactação SXDBB e os registros de visualização são todos gerados para você no momento de salvar.
uses
lxHandle, lxPivot;
var
Book : TXLSWorkbook;
Sheet: IXLSWorkSheet;
Pivot: TXLSPivotTable;
begin
Book := TXLSWorkbook.Create;
try
Book.Open('Sales.xls');
Sheet := Book.Sheets[1];
// Source A1:E500 on 'Data'; anchor the pivot at row 3, col 1.
Pivot := Sheet.AddPivotTable('Data!$A$1:$E$500', 3, 1, 'SalesByRegion');
if Pivot <> nil then
begin
Pivot.AddRowField('Region');
Pivot.AddColumnField('Quarter');
Pivot.AddDataFieldByName('Revenue', xlpaSum);
end;
Book.SaveAs('Sales-Pivot.xls');
finally
Book.Free;
end;
end;A primeira linha do intervalo de origem é lida como o cabeçalho que nomeia os campos de cache, de modo que AddRowField('Region') localiza uma coluna pelo texto do cabeçalho em vez da posição. Como a tabela retornada é um modelo tipado com FromRawBlobs = False, o gravador segue o caminho do zero: ele constrói um cache independente que não depende de o intervalo de origem ainda estar presente no momento da atualização, que é exatamente a propriedade que você deseja quando a tabela dinâmica for enviada para um destinatário que pode mover ou excluir os dados subjacentes.
A leitura e conciliação dos registros de tabela dinâmica e cache de um arquivo que você não produziu, incluindo o caminho de preservação de blobs brutos, são abordadas no tutorial do workbench de auditoria e conversão de pastas de trabalho. Quando o intervalo de origem chega a dezenas de milhares de linhas e o fluxo SXDBB abrange muitos registros continuados, as técnicas descritas nas notas de desempenho para pastas de trabalho grandes impedem que a construção do cache domine o seu tempo de execução. Ambos se alinham com o gravador de tabela dinâmica fornecido no componente de planilha HotXLS para Delphi e C++Builder, junto com as APIs de célula, fórmula, gráfico e formatação abordadas em outras seções deste blog.