Quase todas as partes do formato binário legado do Excel consistem num registo único com um tipo limpo de dois bytes e um comprimento de dois bytes. Uma célula é um LABELSST ou um NUMBER. Uma região fundida é um MERGEDCELLS. Pode ler a maior parte de uma folha de cálculo percorrendo os registos um de cada vez e efetuando o processamento com base na palavra do tipo. As PivotTables quebram esse ritmo. Uma única tabela dinâmica não é um mero registo; trata-se de um pequeno programa composto por dezenas de registos interligados distribuídos por dois locais diferentes no mesmo fluxo de documento composto OLE (OLE compound document stream), e as relações entre eles são posicionais, compactadas por bits e implacáveis. Esta é a estrutura que a maioria dos leitores de BIFF8 ignora por completo ou preserva como bytes opacos, porque escrever uma do zero significa reproduzir cada referência cruzada que o próprio Excel mantém.
A razão pela qual uma tabela dinâmica é complexa prende-se com o facto de consistir em dois artefactos soldados. Existe a cache dinâmica (pivot cache), uma captura autónoma (snapshot) dos dados de origem com o seu próprio subfluxo (substream), e a vista de tabela (table view), o esquema que indica quais os campos posicionados em cada eixo. A cache e a vista referenciam-se mutuamente por índice. Se errar um único índice, o ficheiro abrirá com um erro de atualização ou uma grelha silenciosamente vazia.
A cache dinâmica é um subfluxo autónomo
A cache reside no fluxo global do livro (workbook globals stream) como um subfluxo BIFF completo, delimitado por um registo BOF cujo tipo de documento é 0x0006 (o valor que identifica uma cache dinâmica, por oposição a 0x0005 para o livro ou 0x0010 para uma folha de cálculo) e fechado pelo respetivo EOF. Dentro desse enquadramento, a estrutura é fixa. Um registo SXDB constitui o cabeçalho da cache. Contém a contagem de registos, o número de campos da cache e o identificador do fluxo que a vista de tabela referenciará para se vincular a esta cache. Cada coluna de origem contribui depois com um registo de definição de campo SXFDB seguido por um SXFDBType que o classifica, e de seguida os valores únicos registados nessa coluna, emitidos como um registo de item tipificado por valor distinto.
Os registos de itens são a base da utilidade da cache. Um valor de texto converte-se num SXSTRING, um valor numérico num SXNUM, um valor lógico num SXBOOLEAN e um erro de fórmula num SXERR. A cache não armazena a grelha de origem; guarda os valores distintos por campo, além de uma tabela de índices que indica, para o registo n, qual o item distinto que cada campo assumiu. É por isso que construir uma tabela dinâmica programaticamente não se resume a copiar células. É necessário analisar o intervalo de origem, inferir o tipo de cada campo a partir dos valores que contém, remover duplicados para obter uma lista de itens tipificada e registar cada linha como um tuplo de índices de itens. O HotXLS faz precisamente isto: uma coluna totalmente numérica é emitida com itens SXNUM, uma coluna com texto misto converte-se em itens SXSTRING, e as datas são processadas como valores sequenciais através do mesmo percurso numérico.
SXDBB e a compactação de bits que o torna interessante
A tabela de índices por registo é a parte tecnicamente mais curiosa de toda a estrutura, e reside no registo SXDBB. A codificação direta armazenaria o índice do item de cada campo como uma palavra de 16 bits. O Excel não faz isso. Compacta o índice de cada campo precisamente no número de bits necessários para endereçar os itens desse campo, e nada mais. A largura é de ceil(log2(itemCount + 1)) bits. O + 1 é crucial: o valor adicional é uma sentinela que indica "vazio, sem valor para este campo neste registo", pelo que um campo com três itens distintos necessita de representar quatro estados e, portanto, consome dois bits, e não o único bit que três itens apenas sugeririam. Um campo sem itens contribui com zero bits e é ignorado durante a compactação.
// 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;Os bits de um registo são concatenados ao longo de todos os campos, iniciando depois o registo seguinte num limite de byte limpo. Os registos são alinhados por bytes e não compactados por bits de ponta a ponta, o que torna o acesso aleatório à tabela viável à custa de alguns bits de preenchimento (padding) por linha. A compactação dentro de um byte efetua-se com o bit menos significativo primeiro. Aceitando estas duas regras, o codificador assemelha-se a uma bomba de bits simples, sendo o descodificador o seu espelho.
A razão pela qual este detalhe não pode ser ignorado é o limite máximo de 8224 bytes num único registo BIFF. Cada registo no formato, incluindo os registos de tabelas dinâmicas, tem de acomodar a sua carga de dados em, no máximo, 8224 bytes, e uma cache dinâmica ativa com milhares de linhas de origem ultrapassará esse teto muito antes de emitir cada linha. Por conseguinte, a tabela de índices é dividida. O HotXLS limita o corpo de um único SXDBB a 8220 bytes (o limite de registo de 8224 menos o cabeçalho de registo de quatro bytes de tipo e comprimento), divide esse valor pela largura em bytes de um registo compactado para determinar quantas linhas inteiras cabem e, em seguida, emite tantos registos SXDBB adicionais quantos a contagem de linhas exija. Cada continuação recomeça num limite de registo limpo, para que nenhuma linha seja cortada entre dois registos. Um leitor que conheça a largura de bits por registo consegue percorrer cada SXDBB em sequência como se de um vetor de bits contíguo se tratasse.
O esquema da vista: SXLI para o corpo, SXPI para a página
Com a cache criada, a vista de tabela é a segunda metade. O seu núcleo são os itens de linha de eixo, as linhas do corpo dinâmico que enumeram todas as combinações de valores de campo de linha e de campo de coluna desenhados pela tabela. Estes são transportados em registos SXLI (tipo de registo 0x00B5, descrito em [MS-XLS] §2.4.275). Um SXLI contém várias linhas, novamente até que o limite de 8224 bytes force um novo registo, e utiliza um pequeno truque de compressão: cada linha armazena apenas a sua diferença em relação à linha anterior, representada como uma contagem de prefixo comum, de modo a evitar que um eixo com aninhamento profundo repita os valores de campos externos em cada linha. A linha de total geral e a primeira linha de qualquer registo repõem sempre essa contagem de prefixo a zero, dispensando o leitor de olhar para trás além dos limites de um registo para reconstruir uma linha.
O eixo da página, isto é, os menus de filtro que se situam acima de uma tabela dinâmica, constitui um registo separado. O SXPI (tipo de registo 0x00B6, [MS-XLS] §2.4.276) transporta 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 detalhe relevante. Um campo de página que apresente "(Tudo)" (All), sem filtrar nada, armazena a sentinela 0x7FFD em vez de um índice de item real. Uma tabela dinâmica gerada programaticamente abre com cada campo de página definido para "(Tudo)" até que o chamador pré-selecione um item, momento em que o índice da cache desse item substitui a sentinela e o Excel abre com o filtro já aplicado. A par destes, encontram-se os registos de suporte que descrevem campos individuais e a sua formatação: SXVD e SXVDEx para definições de vista de campo, SXIVD para as listas de índices de campos que ordenam cada eixo, e SXFormat para formatação de números, com cada um a indexar de volta para a mesma cache referenciada pelas linhas do corpo.
Dois escritores num só: blocos binários (raw blobs) e o modelo tipificado
Existe uma razão estrutural pela qual o HotXLS mantém dois caminhos completamente distintos para escrever uma tabela dinâmica, decorrendo diretamente de exigências de fidelidade. Quando um livro é lido a partir do disco, os seus registos dinâmicos foram escritos pelo Excel ou por outro produtor, podendo utilizar variantes de registos, especificidades de ordenação ou registos de extensão que nenhum escritor externo modela por complet. A única ação segura com esses bytes consiste em devolvê-los inalterados. Por conseguinte, uma tabela dinâmica proveniente de um ficheiro é sinalizada com FromRawBlobs = True, e ao guardar, o escritor replica os blocos de registos preservados na íntegra. Nada é regenerado, nada é reinterpretado, e a operação de leitura e gravação mantém a estabilidade dos bytes.
Uma tabela dinâmica construída pelo programa constitui o cenário oposto. Não existem bytes originais a preservar, apenas o modelo de objetos tipificado: um TXLSPivotCache com os seus campos e listas de itens, e um TXLSPivotTable com as suas atribuições de eixos. Essa tabela é sinalizada com FromRawBlobs = False, e o escritor serializa-a de forma complexa, emitindo um novo subfluxo de cache BOF = 0x0006, compactando a tabela de índices SXDBB a partir dos índices de itens contidos no modelo tipificado e estruturando os registos SXLI e SXPI com base na configuração do eixo. A sinalização permite a coexistência de ambos os tipos num único livro. Sem ela, um escritor único teria de descartar a fidelidade das tabelas lidas ou recusar gerar novas. Quaisquer registos de extensão específicos do produtor incluídos numa tabela lida são preservados como registos suplementares, acessíveis através da lista SupplementalRecords da tabela, garantindo que uma tabela inspecionada através do modelo tipificado não perca as partes que o modelo não descreve.
Construir uma tabela dinâmica em código
Todo o mecanismo descrito reside atrás de uma única chamada. O método AddPivotTable recebe o intervalo de origem na notação A1, a célula de destino onde o canto superior esquerdo da tabela se fixa, e um nome. Ele analisa o intervalo, examina-o para inferir tipos de campo e criar a cache (reutilizando uma cache existente se outra tabela já se associar ao mesmo intervalo), e retorna um TXLSPivotTable tipificado com um campo por coluna de origem, estando inicialmente cada campo fora de qualquer eixo. Em seguida, posiciona os campos nos eixos e escolhe uma agregação. A assinatura é exatamente esta, sendo a cache, a compactação SXDBB e os registos de vista gerados no momento da gravação.
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 é interpretada como o cabeçalho que atribui nome aos campos da cache, pelo que AddRowField('Region') localiza uma coluna com base no texto do seu cabeçalho e não pela posição. Como a tabela retornada é um modelo tipificado com FromRawBlobs = False, o escritor adota o caminho de criação do zero: cria uma cache autónoma que não depende da presença do intervalo de origem no momento de atualização, constituindo a propriedade pretendida quando a tabela dinâmica é enviada a um destinatário que possa mover ou eliminar os dados subjacentes.
A leitura e reconciliação de registos dinâmicos e de cache de ficheiros não produzidos por si, incluindo o percurso de preservação de blocos binários (raw-blobs), são abordadas no tutorial de auditoria de livros e bancada de conversão. Quando o intervalo de origem contiver dezenas de milhares de linhas e o fluxo SXDBB se estender por vários registos continuados, as técnicas descritas nas notas de desempenho para livros de grande dimensão evitam que a criação da cache domine o tempo de execução. Ambas combinam com o escritor dinâmico disponibilizado no HotXLS spreadsheet component para Delphi e C++Builder, a par das APIs de célula, dúvida, gráfico e formatação documentadas noutras secções deste blogue.