O HotXLS compila fórmulas escritas na notação de referência R1C1 através de TXLSCalculator.GetCompiledFormulaR1C1, que aceita formatos como R2C3 (absoluto), R[-1]C[2] (deslocamento relativo) e RC (a célula atual), converte-os para a notação A1 em relação à linha e à coluna da célula onde a fórmula reside, e envia o resultado para o mesmo compilador que lida com fórmulas A1 comuns. Para códigos Delphi e C++Builder que geram a mesma fórmula ao longo de centenas de linhas, esse único método remove toda uma classe de bugs de concatenação de strings
A classe de bug é familiar para qualquer pessoa que já tenha preenchido uma coluna via programação. Você faz um loop pelas linhas e, para cada linha, cria uma string de fórmula A1 com Format('D%d*E%d', [Row, Row]). Cada iteração une números de linha ao texto, e os números de linha são a única parte que muda. Erre o deslocamento uma vez, misture um contador de loop baseado em 0 com números de linha A1 baseados em 1, ou desloque o bloco de dados por uma linha de cabeçalho, e cada fórmula na coluna apontará para uma linha incorreta. Nada gera erros; os números simplesmente estão errados. A fórmula que você realmente pretendia usar, "multiplique as duas células à minha esquerda", nunca menciona nenhum número de linha, e a notação R1C1 permite que você a escreva dessa maneira
O que é a notação R1C1 e quando você deve usá-la?
A notação R1C1 aborda as células pelo número da linha e da coluna, em vez de letra da coluna mais número da linha, e marca referências relativas como deslocamentos explícitos a partir da célula da fórmula. R2C3 é a célula absoluta na linha 2, coluna 3, que a notação A1 escreve como $C$2. R[-1]C[2] fica uma linha acima e duas colunas à direita de onde quer que a fórmula esteja. RC é a própria célula da fórmula. Os deslocamentos entre colchetes são o ponto principal: uma referência relativa na notação R1C1 é lida da mesma forma, não importa qual célula a hospede, enquanto a renderização A1 dessa mesma referência muda a cada linha
A notação se justifica em exatamente um cenário, que é bastante comum: geração de modelos, onde a mesma fórmula relativa deve ser inserida em cada linha de uma região de dados. Em A1, você deve renderizar novamente o texto da fórmula por linha. Em R1C1, o texto é uma constante. Isso também é mais próximo de como os formatos de arquivo de planilha pensam internamente: os registros de fórmulas compartilhadas armazenam referências relativas como deslocamentos de linha e coluna a partir da célula hospedeira, de modo que uma string A1 por linha é algo que seu código sintetiza apenas para que o analisador a decomponha de volta em deslocamentos. A notação R1C1 evita essa viagem de ida e volta. Para fórmulas interativas e escritas por humanos, A1 continua sendo a escolha natural, razão pela qual permanece o padrão em todos os lugares no HotXLS
Como o HotXLS compila uma fórmula R1C1?
O HotXLS expõe o recurso em dois níveis, adicionados na versão v2.175.0. O TXLSCalculator.GetCompiledFormulaR1C1(UncompiledFormula: String; SheetID, CurRow, CurCol: Integer): TXLSCompiledFormula é o que a maioria dos códigos chama: ele retorna uma fórmula compilada pronta para avaliação, exatamente como seu equivalente A1 GetCompiledFormula, mas com dois parâmetros extras que nomeiam a linha e a coluna (baseadas em 0) da célula à qual a fórmula pertence. Abaixo dele, o TXLSFormula.GetCompiledR1C1 o produz a árvore de sintaxe bruta, e uma função autônoma R1C1ToA1(const AFormula: String; CurRow, CurCol: Integer): String realiza a conversão real da notação. O pipeline é deliberadamente simples: traduzir o texto R1C1 para o texto A1 correspondente usando as coordenadas da célula hospedeira e, em seguida, compilar o texto A1 por meio do mecanismo existente — o mesmo mecanismo que resolve nomes definidos e referências entre planilhas e envia funções de planilha personalizadas
Como a conversão ocorre antes da compilação, tudo downstream se comporta como se você mesmo tivesse escrito a fórmula A1. O R1C1ToA1('R2C3', 4, 3) retorna '$C$2' independentemente da célula hospedeira, pois ambas as coordenadas são absolutas. O R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3) — uma fórmula localizada em D5, já que CurRow = 4 and CurCol = 3 são baseados em 0 — retorna 'SUM(D2:D4)': os deslocamentos foram resolvidos em relação à linha 5, coluna D, e emitidos como referências A1 relativas simples. Intervalos não precisam de tratamento especial; os dois-pontos passam e cada ponto final se converte independentemente
// The formula lives in D5: CurRow = 4, CurCol = 3 (both 0-based)
S := R1C1ToA1('R2C3', 4, 3);
// S = '$C$2' (absolute row and column)
S := R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3);
// S = 'SUM(D2:D4)' (offsets resolved against D5)
S := R1C1ToA1('ROUND(R[-1]C[0], 2)', 4, 3);
// S = 'ROUND(D4, 2)' (the R in ROUND is left alone)Preenchendo uma coluna com uma fórmula relativa
A recompensa aparece no loop. Compare a versão A1, que renderiza novamente o texto da fórmula a cada iteração, com la versão R1C1, onde a fórmula é uma constante e apenas as coordenadas hospedeiras se movem. Ambas são compiladas através de TXLSCalculator e avaliadas com GetValue; a calculadora recebe um callback de provedor de células na construção para que o avaliador possa obter os valores das células a partir de sua fonte de dados
// A1 style: a different formula string for every row
for Row := 1 to 500 do
begin
FormulaText := Format('D%d*E%d', [Row + 1, Row + 1]); // 1-based A1 rows
Compiled := Calc.GetCompiledFormula(FormulaText, 0);
// ... evaluate, store, free ...
end;const
AmountFormula = 'RC[-2]*RC[-1]'; // two cells to the left, same row
var
Calc: TXLSCalculator;
Compiled: TXLSCompiledFormula;
Value: Variant;
Row: Integer;
begin
Calc := TXLSCalculator.Create(nil, Provider.GetValue);
try
for Row := 1 to 500 do
begin
Compiled := Calc.GetCompiledFormulaR1C1(AmountFormula, 0, Row, 5);
try
if Calc.GetValue(0, Compiled, Row, 5, Value, 1) = lxOk then
StoreResult(Row, 5, Value);
finally
Compiled.Free;
end;
end;
finally
Calc.Free;
end;
end;O loop R1C1 não possui aritmética de linha alguma no texto da fórmula. 'RC[-2]*RC[-1]' significa "mesma linha, duas colunas à esquerda, multiplicado por mesma linha, uma coluna à esquerda" tanto na linha 2 quanto na linha 500, e se o bloco de dados for posteriormente deslocado para baixo por uma linha de cabeçalho, a constante da fórmula não muda — apenas os limites do loop mudam. A versão A1 tem dois locais para errar o ajuste + 1; a versão R1C1 tem zero
Quais formatos R1C1 o conversor aceita?
O conversor R1C1ToA1 reconhece as formas documentadas para a v2.175.0: R[n]C[m] para deslocamentos relativos em qualquer direção, RnCm para linha e coluna absolutas, R[-n]C[m] com deslocamentos negativos, o simples RC para a própria célula da fórmula e intervalos como R1C1:R3C3 ou R[-1]C:R[1]C. As partes de linha e coluna são analisadas de forma independente, portanto formas mistas como R[1]C3 — linha relativa, coluna absoluta — também funcionam, e as letras são insensíveis a maiúsculas e minúsculas, de modo que r[-1]c[2] é compilado da mesma forma que seu equivalente em maiúsculas. As partes entre colchetes tornam-se coordenadas A1 não ancoradas (relativas); os números puros tornam-se coordenadas absolutas ancoradas com $
A questão interessante é como o conversor evita desconfigurar todo o restante da fórmula, já que R e C são letras comuns. Sua regra de discriminação é baseada em tokens: um R só é considerado o início de uma referência quando não é precedido por outra letra, e o candidato deve então ser analisado completamente — parte de linha opcional, C obrigatório, parte de coluna opcional — ou o texto é restaurado intocado. É por isso que o ROUND(R[-1]C[0], 2) converte apenas a referência interna: o R em ROUND é seguido por O em vez de um dígito, colchete ou C, de modo que a análise falha e o nome da função passa inalterado. A mesma lógica protege ROW(), e nomes de funções que começam com C nunca são candidatos, pois apenas o R inicia uma referência. As notas de lançamento da versão v2.175.0 afirmam que literais de string e identificadores também são ignorados; mesmo assim, se um literal entre aspas na sua fórmula por acaso contiver texto com formato idêntico a uma referência R1C1, vale a pena verificar a saída compilada uma vez antes de confiar nela em produção
Bases de coordenadas e detalhes de ancoragem que vale a pena conhecer
Duas convenções se encontram dentro desta API, e mantê-las claras evita a única armadilha real. Os parâmetros CurRow e CurCol de GetCompiledFormulaR1C1 são baseados em 0, seguindo a API da calculadora, enquanto os números dentro da própria notação são baseados em 1, correspondendo ao que o Excel exibe: R2C3 é a linha 2, coluna 3, que é $C$2, não $D$3. Se o seu contador de loop já for baseado em 0, você o passa diretamente como CurRow; o + 1 vive dentro do conversor, não no seu código
Mais um detalhe importa se a fórmula compilada for sobreviver à célula para a qual foi compilada. Quando você omite uma parte da linha ou coluna — as formas RC[-1] ou R[2]C —, a coordenada omitida resolve-se para a célula hospedeira e é emitida como uma coordenada absoluta ancorada em $ no texto A1 convertido. No momento da avaliação, isso é invisível, já que o valor é o mesmo de qualquer maneira. Mas a ancoragem relativa versus absoluta decide como as referências se deslocam quando linhas ou colunas são inseridas ou excluídas posteriormente, como abordado no artigo complementar sobre ajuste de referência de fórmula. Se precisar que uma coordenada permaneça relativa durante as edições estruturais, escreva o deslocamento de forma explícita — R[0]C[-1] em vez de RC[-1] — para que o conversor emita uma referência A1 não ancorada
A compilação R1C1 faz parte do mecanismo de fórmulas no HotXLS Delphi Excel Component, juntamente com o compilador A1, o gráfico de recálculo e a API de avaliação mostrados acima. Se o seu código cria planilhas por meio de loops de fórmulas nas colunas, mover esses loops de A1 concatenado por string para uma única constante R1C1 é uma das melhorias de confiabilidade mais baratas disponíveis