Um seguidor de fórmula compartilhada em XLSX não carrega texto de fórmula. Seu elemento <f t="shared" si="N"/> aponta para uma célula master em outro lugar da planilha, e o leitor precisa reconstruir o texto deslocando a fórmula master pela diferença de linha e coluna. O HotXLS Component para Delphi e C++Builder faz essa expansão no momento da abertura, então todo seguidor reporta uma fórmula completa
Se você já carregou um XLSX do mundo real em uma biblioteca de terceiros e encontrou que uma coluna de mil fórmulas tem texto em exatamente uma célula e strings vazias nas outras 999, você encontrou essa feature pelo lado errado. Nada está corrompido. O arquivo está fazendo o que a ECMA-376 permite que ele faça, e o leitor simplesmente parou no ponto em que o XML parou
Por que a célula de fórmula compartilhada está vazia?
Porque o formato deliberadamente armazena a fórmula uma única vez. Na ECMA-376 Parte 1 e ISO/IEC 29500-1, o elemento <f> (§18.3.1.40) carrega um atributo t do tipo ST_CellFormulaType, e o valor shared significa que esta célula participa de um grupo identificado pelo atributo si. Exatamente uma célula no grupo, o master, também carrega um atributo ref dando o intervalo ao qual o grupo se aplica, e só essa célula carrega o texto da fórmula como conteúdo de elemento. Toda outra célula no grupo é um seguidor. Ela repete t="shared" e o mesmo si, e seu conteúdo de elemento é vazio. O Excel escreve esses grupos agressivamente, porque um fill-down sobre uma coluna de 200.000 linhas colapsa de 200.000 strings de fórmula para uma string mais 199.999 elementos placeholder minúsculos. A economia é real e o custo cai inteiramente sobre o leitor: sem expansão, o seguidor não tem significado por si só
O deslocamento é uma tradução, não uma cópia de texto
O HotXLS resolve um seguidor localizando o master registrado sob o mesmo si, computando o delta de linha e coluna da âncora do master até a célula atual, e traduzindo toda referência na fórmula master por esse delta. Dimensões relativas se movem, dimensões absolutas não, e referências mistas movem só sua metade não absoluta. Literais de string são completamente pulados, então uma fórmula que por acaso contém o texto "A1" mantém esse texto inalterado em todo seguidor
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 decoração. Um seguidor cujas coordenadas caem fora do intervalo aplicável do master não é expandido, porque o arquivo estaria então fazendo uma afirmação que o grupo não suporta. Da mesma forma, quando um deslocamento empurraria uma referência acima da linha um ou à esquerda da coluna A, o HotXLS emite #REF! para aquele token em vez de silenciosamente prendê-lo, que é o que o próprio Excel produziria para a mesma edição. Essa tradução é prima próxima, mas não a mesma coisa, da reescrita de referência que acontece quando você insere ou exclui linhas. Esse caminho tem suas próprias regras sobre o que um intervalo faz quando uma edição o corta, e é descrito separadamente no artigo sobre ajuste de referência de fórmula durante inserção e exclusão. A expansão compartilhada é mais simples: é um deslocamento puro a partir de uma âncora conhecida, aplicado uma vez, no momento do parse
Quais formas de referência o deslocador precisa cobrir?
Todas elas, ou a expansão é um bug de perda de dados disfarçado. Um deslocador ingênuo que só entende A1 e A1:B2 vai corromper ou descartar as formas mais exóticas, e workbooks reais estão cheios delas. O tradutor de fórmula compartilhada do HotXLS reconhece a família A1 inteira antes de decidir o que mover. Referências de workbook externo como [Book.xlsx]Sheet1!A1 e referências 3D como Sheet1:Sheet3!A1 mantêm seu prefixo intacto enquanto a referência de célula final se desloca. Nomes de planilha entre aspas sobrevivem, incluindo o caso incômodo onde a planilha é literalmente nomeada A1, então 'A1'!A1 desloca só a parte depois do ponto de exclamação. Coluna inteira A:A move sua dimensão de coluna e nada mais; linha inteira 1:1 move sua dimensão de linha e nada mais; $A:$A não se move de forma alguma. Referências de tabela estruturada como Table[A1] ficam intocadas, porque a parte entre colchetes é 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]Nomes de função são a armadilha silenciosa aqui. Um scanner de token que pega letras seguidas por dígitos vai alegremente reescrever LOG10 em LOG11 uma linha abaixo. O HotXLS exige um limite de referência antes de um token candidato e depois dele, então um identificador que continua em uma letra, dígito, underscore, ponto, ou um parêntese de abertura não é uma referência de célula. Se você está trabalhando na outra família de notação, o mesmo problema de limite aparece de forma diferente, e o artigo sobre notação R1C1 cobre onde os dois modelos divergem
Por que um elemento f autofechado engole o próximo valor?
Porque um elemento autofechado não produz nenhum evento de fim de elemento. Este é o bug mais caro de toda a feature, e não é específico de nenhum parser XML em particular. No TXMLReader, <f t="shared" si="4"/> dispara exatamente um evento Element com IsEmptyElement setado como True, e nunca dispara o EndElement correspondente. Um parser que fecha seu estado de captura de fórmula só no EndElement, portanto, permanece dentro da fórmula, e o próximo texto que ele vê, que é o resultado em cache dentro de <v>, é anexado ao buffer da fórmula. Pior, o estado sobrevive ao limite da célula, então a próxima célula que possui um <f> real tem seu texto de fórmula absorvido pela célula anterior. A correção é encerrar o estado de fórmula no próprio evento Element sempre que IsEmptyElement for True, e rodar ali toda a resolução de seguidor em vez de esperar. Isso significa ler t, si, ref, aca, e ca dos atributos, aplicar a expansão compartilhada, gravar os atributos de recálculo na célula, e limpar o estado compartilhado, tudo dentro do ramo que trata o elemento vazio. Note que o formato permite ambas as grafias, <f t="shared" si="4"/> e <f t="shared" si="4"></f>, e a segunda de fato dispara um EndElement. Um leitor correto precisa tratar o par de forma idêntica, e é por isso que o HotXLS cobre ambas as grafias no mesmo arquivo de regressão
Valores si esparsos, sem ordem, e a fila pendente
O atributo si é um inteiro sem sinal fornecido pelo arquivo, não uma posição de array que você controla. Nada no schema exige que índices compartilhados sejam densos, comecem em zero, ou apareçam em ordem crescente, e nada impede um arquivo hostil ou simplesmente estranho de usar si="4294967290" na primeira célula. Dimensionar um array de busca a partir do maior si observado é, portanto, uma primitiva de exaustão de memória, não uma otimização. O HotXLS mantém o caminho de abertura de workbook em uma tabela esparsa ordenada em vez disso: grupos compartilhados são registrados sob sua chave inteira em um TStringList ordenado, o que torna a busca uma pesquisa binária sobre quantos grupos realmente existem, sem relação com o tamanho numérico dos índices. Ordem é a segunda metade do problema. Um master normalmente precede seus seguidores na ordem do documento, mas isso é uma convenção em vez de uma regra, então qualquer seguidor que não consegue resolver seu si no momento em que é analisado vai para uma fila pendente. Quando a planilha termina, a fila é reproduzida contra a tabela agora completa, e os masters tardios resolvem seus órfãos. Células que nunca encontram um master mantêm uma fórmula vazia, que é o resultado honesto para um arquivo que referencia um grupo que nunca definiu
Expandindo fórmulas compartilhadas sem carregar o workbook
Os leitores em streaming enfrentam a mesma exigência sob um orçamento de memória muito mais apertado, e eles a resolvem com uma tabela local à worksheet. TXLSDirectReader e TXLSRowCursor ambos expandem seguidores em fórmulas completas por célula enquanto preservam seu comportamento de memória limitada e projeção, então uma passagem só-para-frente sobre uma planilha de 300 MB ainda te entrega 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;Duas restrições decorrem desse design. Primeiro, a projeção nunca pode pular o master. Um filtro de linha configurado com FirstRow e LastRow, ou um filtro de coluna construído com IncludeColumn, pode pular a emissão da célula master para seu callback, mas o parser ainda precisa registrar seu si, coordenadas de âncora, intervalo aplicável, e texto de fórmula, senão todo seguidor dentro da projeção resolve para nada. Só o trabalho do lado do seguidor, o deslocamento e a decodificação de valor, é seguro de pular. Segundo, a tabela é por worksheet e seu ciclo de vida precisa ser gerenciado explicitamente: TXLSRowCursor mantém uma instância pela duração de uma passagem de planilha e a limpa no reinício, troca de planilha, fim de arquivo, exceção, e fechamento, então um grupo definido na planilha um nunca pode vazar para a planilha dois. Como o caminho de streaming é um loop quente, ele usa um hash de inteiro de endereçamento aberto em vez da tabela de string ordenada, o que evita uma conversão de inteiro para string por célula
O que acontece ao salvar, e onde estão os limites
Uma vez que um seguidor foi expandido ele é uma fórmula comum, e o HotXLS o grava de volta como um elemento <f> independente sem t="shared" e sem si. O round trip é estável e os resultados <v> em cache sobrevivem, mas a saída é maior que a entrada para uma planilha fortemente compartilhada, e o agrupamento que o Excel criou não é reconstruído ao salvar. Se a fidelidade em nível de byte dos grupos compartilhados importa mais para você do que ter texto de fórmula real em toda célula, essa é a troca que você está aceitando. O lado XLS é diferente, a propósito: o registro SHRFMLA do BIFF8 tem sua própria codificação e seu próprio writer, com um toggle de grupo compartilhado no workbook
Duas coisas relacionadas explicitamente não são fórmulas compartilhadas mesmo compartilhando o elemento <f>. Fórmulas de array CSE legadas usam t="array" com um ref cobrindo o intervalo ancorado, e 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 registro XLDAPR. Tratar uma célula de spill de array dinâmico como um seguidor compartilhado ou CSE é um bug genuíno de correção, e a separação é coberta no artigo sobre fórmulas de array dinâmico e spill. Leia os três casos como três parsers que por acaso compartilham um nome de tag, e o código permanece honesto
A expansão de fórmula compartilhada, os leitores em streaming, e o tradutor de referência descritos aqui vêm como parte do componente Excel HotXLS para Delphi e C++Builder; a página de produto traz a referência completa de fórmula e API de leitura direta, incluindo as propriedades de projeção usadas acima