Um seguidor de fórmula partilhada num XLSX não transporta qualquer texto de fórmula. O seu elemento <f t="shared" si="N"/> aponta para uma célula mestre algures na folha, e o leitor tem de reconstruir o texto deslocando a fórmula mestre pela diferença de linha e coluna. O HotXLS Component para Delphi e C++Builder faz essa expansão no momento da abertura, pelo que cada seguidor devolve uma fórmula completa
Se já carregou um XLSX real numa biblioteca de terceiros e verificou que uma coluna com mil fórmulas tem texto em exatamente uma célula e strings vazias nas outras 999, já se cruzou com esta funcionalidade pelo lado errado. Nada está corrompido. O ficheiro está a fazer o que a ECMA-376 permite, e o leitor simplesmente parou no ponto onde o XML parou
Porque é que a célula da fórmula partilhada aparece vazia?
Porque o formato armazena a fórmula deliberadamente uma única vez. Na ECMA-376 Parte 1 e na ISO/IEC 29500-1, o elemento <f> (§18.3.1.40) transporta um atributo t do tipo ST_CellFormulaType, e o valor shared significa que esta célula pertence a um grupo identificado pelo atributo si. Exatamente uma célula do grupo, o mestre, transporta ainda um atributo ref com o intervalo a que o grupo se aplica, e só essa célula transporta o texto da fórmula como conteúdo do elemento. Todas as outras células do grupo são seguidoras. Repetem t="shared" e o mesmo si, e o conteúdo do seu elemento fica vazio. O Excel escreve estes grupos de forma agressiva, porque um preenchimento para baixo numa coluna de 200 000 linhas passa de 200 000 strings de fórmula para uma string mais 199 999 pequenos elementos de marcador. A poupança é real e o custo recai inteiramente sobre o leitor: sem expansão, o seguidor não tem qualquer significado por si só
O deslocamento é uma tradução, não uma cópia de texto
O HotXLS resolve um seguidor localizando o mestre registado sob o mesmo si, calculando a diferença de linha e coluna entre a âncora do mestre e a célula atual, e traduzindo cada referência da fórmula mestre por essa diferença. As dimensões relativas movem-se, as dimensões absolutas não, e as referências mistas movem apenas a sua metade não absoluta. Os literais de string são ignorados por completo, pelo que uma fórmula que contenha o texto "A1" mantém esse texto inalterado em todos os seguidores
const
// xl/worksheets/sheet1.xml, trimmed to the interesting cells
SheetXml: WideString=
'<row r="1"><c r="A1"><v>1</v></c>'+
'<c r="B1"><f t="shared" si="4" ref="B1:B3">'+
'A1+$A$1+A$1+$A1+"A1"+SUM(A1:A2)</f><v>7</v></c></row>'+
'<row r="2"><c r="B2"><f t="shared" si="4"/><v>8</v></c></row>'+
'<row r="3"><c r="B3"><f t="shared" si="4"></f><v>9</v></c></row>';
var
Wb: TXLSXWorkbook;
Sh: TXLSXWorksheet;
begin
Wb:= TXLSXWorkbook.Create;
try
Wb.Open(FileName);
Sh:= Wb.Sheets[1];
// Master, verbatim
// B1 -> A1+$A$1+A$1+$A1+"A1"+SUM(A1:A2)
// Follower one row down: relative row moves, absolute row frozen,
// the mixed A$1 keeps its row, and the literal stays a literal
// B2 -> A2+$A$1+A$1+$A2+"A1"+SUM(A2:A3)
ShowMessage(Sh.Cells[2, 2].Formula);
finally
Wb.Free;
end;
end;O atributo ref é um portão, não um adorno. Um seguidor cujas coordenadas caiam fora do intervalo aplicável do mestre não é expandido, porque o ficheiro estaria então a fazer uma afirmação que o grupo não suporta. Do mesmo modo, quando um deslocamento empurraria uma referência acima da linha um ou à esquerda da coluna A, o HotXLS emite #REF! para esse token em vez de o fixar silenciosamente, que é exatamente o que o próprio Excel produziria para a mesma edição. Esta tradução é prima próxima, mas não idêntica, da reescrita de referências que ocorre ao inserir ou eliminar linhas. Esse percurso tem regras próprias sobre o que acontece a um intervalo quando uma edição o corta, e está descrito em separado no artigo sobre o ajuste de referências de fórmula ao inserir e eliminar. A expansão partilhada é mais simples: é um mero deslocamento a partir de uma âncora conhecida, aplicado uma vez, no momento da análise
Que formas de referência tem o deslocador de cobrir?
Todas, ou a expansão é um bug de perda de dados disfarçado. Um deslocador ingénuo que só reconheça A1 e A1:B2 vai corromper ou perder as formas mais exóticas, e as pastas de trabalho reais estão cheias delas. O tradutor de fórmulas partilhadas do HotXLS reconhece toda a família de referências A1 antes de decidir o que mover. Referências a pastas de trabalho externas como [Book.xlsx]Sheet1!A1 e referências 3D como Sheet1:Sheet3!A1 mantêm o seu prefixo intacto enquanto a referência de célula final se desloca. Os nomes de folha entre aspas sobrevivem, incluindo o caso mais complicado em que a folha se chama literalmente A1, pelo que 'A1'!A1 desloca apenas a parte a seguir ao ponto de exclamação. A coluna inteira A:A desloca apenas a sua dimensão de coluna; a linha inteira 1:1 desloca apenas a sua dimensão de linha; $A:$A não se move de todo. Referências estruturadas a tabelas como Table[A1] ficam intocadas, porque a parte entre parênteses retos é um nome de coluna, não uma coordenada
// One master, expanded two columns to the right and zero rows down.
// Master D1: A1+A:A+$A:$A
// F1 : C1+C:C+$A:$A
//
// One master, expanded three rows down and zero columns across.
// Master A1: B1+$C$1+"A1"+A:A+1:1+'Data'!A1+LOG10(A1)+Table[A1]+'A1'!A1
// A3 : B3+$C$1+"A1"+A:A+3:3+'Data'!A3+LOG10(A3)+Table[A1]+'A1'!A3
//
// Note what did NOT move in the second line: the absolute $C$1, the
// string literal "A1", the whole column A:A under a pure row delta,
// the function name LOG10, and the structured reference Table[A1]Os nomes de funções são a armadilha silenciosa aqui. Um analisador de tokens que capture letras seguidas de dígitos vai alegremente reescrever LOG10 como LOG11 uma linha abaixo. O HotXLS exige uma fronteira de referência antes e depois de um candidato a token, pelo que um identificador que continue com uma letra, dígito, sublinhado, ponto ou parêntese de abertura não é uma referência de célula. Se estiver a trabalhar na outra notação, o mesmo problema de fronteira surge de forma diferente, e o artigo sobre a notação R1C1 cobre onde os dois modelos divergem
Porque é que um elemento f autofechado engole o valor seguinte?
Porque um elemento autofechado não produz qualquer evento de fim de elemento. Este é o bug mais dispendioso de toda a funcionalidade, e não é específico de nenhum analisador XML em particular. No TXMLReader, <f t="shared" si="4"/> gera exatamente um evento Element com IsEmptyElement a True, e nunca gera o EndElement correspondente. Um analisador que só encerra o seu estado de captura de fórmula no EndElement fica, por isso, preso dentro da fórmula, e o próximo texto que encontra, que é o resultado em cache dentro de <v>, acaba anexado ao buffer da fórmula. Pior ainda, o estado sobrevive à fronteira da célula, pelo que a célula seguinte que tenha um <f> real vê o seu texto de fórmula absorvido pela célula anterior. A correção é terminar o estado da fórmula logo no evento Element sempre que IsEmptyElement for True, e executar ali mesmo toda a resolução do seguidor em vez de esperar. Isso significa ler t, si, ref, aca e ca a partir dos atributos, aplicar a expansão partilhada, escrever os atributos de recálculo na célula e limpar o estado partilhado, tudo dentro do ramo que trata o elemento vazio. Note-se que o formato permite ambas as grafias, <f t="shared" si="4"/> e <f t="shared" si="4"></f>, e a segunda gera de facto um EndElement. Um leitor correto tem de tratar o par de forma idêntica, razão pela qual o HotXLS cobre ambas as grafias no mesmo ficheiro de regressão
Valores si esparsos, sem ordem, e a fila de pendentes
O atributo si é um número inteiro sem sinal fornecido pelo ficheiro, não uma posição de array que o programador controle. Nada no esquema exige que os índices partilhados sejam densos, comecem em zero, ou apareçam por ordem ascendente, e nada impede um ficheiro hostil ou simplesmente estranho de usar si="4294967290" na primeira célula. Dimensionar um array de pesquisa a partir do maior si observado é, por isso, um primitivo de esgotamento de memória, não uma otimização. O HotXLS mantém o percurso de abertura da pasta de trabalho sobre uma tabela esparsa ordenada: os grupos partilhados são registados sob a sua chave inteira numa TStringList ordenada, o que torna a pesquisa uma procura binária sobre o número real de grupos existentes, sem relação com a dimensão numérica dos índices. A ordem é a segunda metade do problema. Um mestre normalmente precede os seus seguidores na ordem do documento, mas isso é uma convenção, não uma regra, pelo que qualquer seguidor que não consiga resolver o seu si no momento em que é analisado entra numa fila de pendentes. Quando a folha termina, a fila é reprocessada contra a tabela agora completa, e os mestres tardios resolvem os seus órfãos. As células que nunca encontram um mestre mantêm uma fórmula vazia, que é o resultado honesto para um ficheiro que referencia um grupo que nunca chegou a definir
Expandir fórmulas partilhadas sem carregar a pasta de trabalho
Os leitores em streaming enfrentam o mesmo requisito com um orçamento de memória muito mais apertado, e resolvem-no com uma tabela local à folha de cálculo. O TXLSDirectReader e o TXLSRowCursor expandem ambos os seguidores em fórmulas completas por célula, preservando o seu comportamento de memória limitada e de projeção, pelo que uma passagem apenas para a frente sobre uma folha de 300 MB continua a devolver texto de fórmula real
var
Reader: TXLSDirectReader;
Cursor: TXLSRowCursor;
begin
// Projection: only rows 2..3, only column A. The master lives in row 1,
// outside the projection, and is still parsed so the followers resolve
Reader:= TXLSDirectReader.Create;
try
Reader.FirstRow:= 2;
Reader.LastRow:= 3;
Reader.IncludeColumn(1);
Reader.OnCell:= HandleCell; // Cell.Formula is fully expanded here
Reader.ReadFile(FileName);
finally
Reader.Free;
end;
// Forward-only row traversal, same expansion
Cursor:= TXLSRowCursor.Create;
try
Cursor.Open(FileName);
if Cursor.FindFirst then
repeat
if Cursor.CellCount > 0 then
WriteLn(Cursor.RowIndex, ': ', Cursor.Cells[0].Formula);
until not Cursor.FindNext;
finally
Cursor.Free;
end;
end;Deste desenho decorrem duas restrições. Primeiro, a projeção nunca pode saltar o mestre. Um filtro de linhas definido com FirstRow e LastRow, ou um filtro de colunas construído com IncludeColumn, pode dispensar o envio da célula mestre ao callback do utilizador, mas o analisador continua a ter de registar o seu si, coordenadas de âncora, intervalo aplicável e texto da fórmula, caso contrário todos os seguidores dentro da projeção resolvem para nada. Só o trabalho do lado do seguidor, o deslocamento e a descodificação do valor, é seguro saltar. Segundo, a tabela é por folha de cálculo e a sua duração tem de ser gerida explicitamente: o TXLSRowCursor mantém uma instância durante a passagem de uma folha e limpa-a no reinício, na mudança de folha, no fim do ficheiro, em caso de exceção e no fecho, de modo que um grupo definido na folha um nunca possa vazar para a folha dois. Como o percurso de streaming é um ciclo intensivo, usa uma tabela de hash de endereçamento aberto sobre inteiros em vez da tabela de strings ordenada, o que evita uma conversão de inteiro para string por célula
O que acontece ao gravar, e onde estão os limites
Depois de um seguidor ser expandido, passa a ser uma fórmula normal, e o HotXLS grava-a de volta como um elemento <f> independente, sem t="shared" e sem si. O ciclo de ida e volta é estável e os resultados em cache de <v> sobrevivem, mas o resultado é maior do que a entrada numa folha fortemente partilhada, e o agrupamento criado pelo Excel não é reconstruído na gravação. Se a fidelidade ao nível do byte dos grupos partilhados lhe interessar mais do que ter texto de fórmula real em cada célula, esta é a troca que está a aceitar. O lado XLS é diferente, aliás: o registo SHRFMLA do BIFF8 tem a sua própria codificação e o seu próprio gravador, com um comutador de grupo partilhado na pasta de trabalho
Há duas coisas relacionadas que não são explicitamente fórmulas partilhadas, mesmo partilhando o elemento <f>. As fórmulas de array CSE legadas usam t="array" com um ref a cobrir o intervalo ancorado, e os arrays dinâmicos usam a mesma grafia t="array" mas são identificados por um atributo cm que encadeia através de cellMetadata até um registo XLDAPR. Tratar uma célula de derrame (spill) de array dinâmico como um seguidor partilhado ou CSE é um verdadeiro bug de correção, e essa separação está coberta em o artigo sobre fórmulas de array dinâmico e derrame. Leia os três casos como três analisadores que por acaso partilham um nome de tag, e o código mantém-se honesto
A expansão de fórmulas partilhadas, os leitores em streaming e o tradutor de referências aqui descritos fazem parte do componente Excel HotXLS para Delphi e C++Builder; a página do produto traz a referência completa da API de fórmulas e leitura direta, incluindo as propriedades de projeção usadas acima