HotXLS compila fórmulas escritas en notación de referencia R1C1 a través de TXLSCalculator.GetCompiledFormulaR1C1, que acepta formatos como R2C3 (absoluto), R[-1]C[2] (desplazamiento relativo) y RC (la celda actual), los convierte a notación A1 basándose en la fila y la columna de la celda donde se encuentra la fórmula, y envía el resultado al mismo compilador que procesa las fórmulas A1 comunes. Para el código en Delphi y C++Builder que genera la misma fórmula en cientos de filas, este único método elimina por completo una categoría entera de errores de concatenación de cadenas
Esta clase de error es familiar para cualquiera que haya rellenado una columna mediante programación. Se realiza un bucle sobre las filas y, para cada fila, se construye una cadena de fórmula A1 con Format('D%d*E%d', [Row, Row]). Cada iteración inserta números de fila en el texto, siendo estos números la única parte que cambia. Si se comete un error en el desplazamiento una sola vez, se mezcla un contador de bucle basado en 0 con números de fila A1 basados en 1, o se desplaza el bloque de datos debido a una fila de encabezado, cada fórmula de la columna apuntará a una fila desplazada. No se genera ninguna excepción; simplemente los números son incorrectos. La fórmula que realmente se pretendía escribir, "multiplicar las dos celdas a mi izquierda", no menciona ningún número de fila, y la notación R1C1 permite escribirla de esa forma
¿Qué es la notación R1C1 y cuándo se debe utilizar?
La notación R1C1 direcciona las celdas por número de fila y de columna en lugar de por letra de columna más número de fila, y marca las referencias relativas como desplazamientos explícitos desde la celda de la fórmula. R2C3 es la celda absoluta en la fila 2, columna 3, que la notación A1 escribe como $C$2. R[-1]C[2] está una fila arriba y dos columnas a la derecha de donde se sitúe la fórmula. RC representa la celda de la fórmula en sí. Los desplazamientos entre corchetes son la clave: una referencia relativa en R1C1 se lee de la misma manera independientemente de qué celda la albergue, mientras que la representación en A1 de esa misma referencia varía con cada fila
La notación demuestra su utilidad en un escenario concreto, y que resulta muy común: la generación de plantillas, donde la misma fórmula relativa debe colocarse en cada fila de una región de datos. En A1, se debe volver a renderizar el texto de la fórmula para cada fila. En R1C1, el texto es una constante. Esto también se aproxima más a cómo procesan los formatos de archivos de hojas de cálculo a nivel interno: los registros de fórmulas compartidas almacenan las referencias relativas como desplazamientos de fila y columna desde la celda anfitriona, por lo que una cadena A1 por fila es algo que el código sintetiza solo para que el analizador la descomponga de nuevo en desplazamientos. R1C1 evita este viaje de ida y vuelta. Para fórmulas interactivas escritas por personas, A1 sigue siendo la elección natural, por lo que continúa siendo el valor predeterminado en todo HotXLS
¿Cómo compila HotXLS una fórmula R1C1?
HotXLS expone esta característica en dos niveles, añadidos en la versión v2.175.0. El método TXLSCalculator.GetCompiledFormulaR1C1(UncompiledFormula: String; SheetID, CurRow, CurCol: Integer): TXLSCompiledFormula es el que invoca la mayor parte del código: devuelve una fórmula compilada lista para su evaluación, exactamente igual que su equivalente A1 GetCompiledFormula, pero con dos parámetros adicionales que indican la fila y columna basadas en 0 de la celda a la que pertenece la fórmula. Por debajo, TXLSFormula.GetCompiledR1C1 genera el árbol de sintaxis básica, y una función independiente R1C1ToA1(const AFormula: String; CurRow, CurCol: Integer): String realiza la conversión de notación propiamente dicha. El flujo de trabajo es deliberadamente sencillo: traduce el texto R1C1 al texto A1 equivalente utilizando las coordenadas de la celda anfitriona, y luego compila el texto A1 a través del motor existente, el mismo motor que resuelve nombres definidos y referencias entre hojas y despacha funciones de hoja de cálculo personalizadas
Debido a que la conversión ocurre antes de la compilación, todo el proceso posterior se comporta como si hubiera escrito la fórmula A1 usted mismo. R1C1ToA1('R2C3', 4, 3) devuelve '$C$2' independientemente de la celda anfitriona, ya que ambas coordenadas son absolutas. R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3) (una fórmula situada en D5, ya que CurRow = 4 y CurCol = 3 se basan en 0) devuelve 'SUM(D2:D4)': los desplazamientos se resolvieron con respecto a la fila 5, columna D, y se emitieron como referencias A1 relativas simples. Los rangos no requieren un tratamiento especial; los dos puntos se mantienen y cada extremo se convierte de forma independiente
// La fórmula se encuentra en D5: CurRow = 4, CurCol = 3 (ambos basados en 0)
S := R1C1ToA1('R2C3', 4, 3);
// S = '$C$2' (fila y columna absolutas)
S := R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3);
// S = 'SUM(D2:D4)' (desplazamientos resueltos con respecto a D5)
S := R1C1ToA1('ROUND(R[-1]C[0], 2)', 4, 3);
// S = 'ROUND(D4, 2)' (la R en ROUND no se modifica)Llenar una columna con una sola fórmula relativa
El beneficio se hace evidente en el bucle. Compare la versión A1, que vuelve a renderizar el texto de la fórmula en cada iteración, con la versión R1C1, donde la fórmula es una constante y solo se mueven las coordenadas de la celda anfitriona. Ambos compilan a través de TXLSCalculator y se evalúan con GetValue; el calculador recibe una llamada de retorno de proveedor de celdas en la construcción para que el evaluador pueda extraer los valores de las celdas desde su origen de datos
// Estilo A1: una cadena de fórmula diferente para cada fila
for Row := 1 to 500 do
begin
FormulaText := Format('D%d*E%d', [Row + 1, Row + 1]); // filas A1 basadas en 1
Compiled := Calc.GetCompiledFormula(FormulaText, 0);
// ... evaluar, almacenar, liberar ...
end;const
AmountFormula = 'RC[-2]*RC[-1]'; // dos celdas a la izquierda, misma fila
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;El bucle R1C1 no contiene aritmética de fila alguna en el texto de la fórmula. 'RC[-2]*RC[-1]' significa "misma fila, dos columnas a la izquierda, multiplicado por misma fila, una columna a la izquierda" tanto en la fila 2 como en la fila 500 por igual, y si el bloque de datos se desplaza posteriormente hacia abajo debido a una fila de cabecera, la constante de la fórmula no varía; solo cambian los límites del bucle. La versión A1 tiene dos puntos donde es posible equivocarse en el ajuste de + 1; la versión R1C1 tiene cero
¿Qué formatos R1C1 acepta el convertidor?
El convertidor R1C1ToA1 reconoce los formatos documentados para la versión v2.175.0: R[n]C[m] para desplazamientos relativos en cualquier dirección, RnCm para fila y columna absolutas, R[-n]C[m] con desplazamientos negativos, el formato simple RC para la celda de la fórmula en sí, y rangos como R1C1:R3C3 o R[-1]C:R[1]C. Las partes de fila y columna se analizan de manera independiente, por lo que también funcionan formatos mixtos como R[1]C3 (fila relativa, columna absoluta) y las letras no diferencian mayúsculas de minúsculas, por lo que r[-1]c[2] compila igual que su gemelo en mayúsculas. Las partes entre corchetes se convierten en coordenadas A1 no ancladas (relativas); los números solos pasan a ser coordenadas absolutas ancladas con $
La pregunta interesante es cómo evita el convertidor corromper el resto de elementos en la fórmula, ya que la R y la C son letras muy comunes. Su regla de discriminación se basa en tokens: una R solo se considera el inicio de una referencia cuando no va precedida de otra letra, y el candidato debe entonces analizarse por completo (parte de fila opcional, C obligatoria, parte de columna opcional) o de lo contrario el texto se restaura intacto. Por eso ROUND(R[-1]C[0], 2) convierte solo la referencia interna: la R de ROUND va seguida de la O en lugar de un dígito, corchete o C, por lo que el análisis falla y el nombre de la función se transmite literalmente. La misma lógica protege a ROW(), y las funciones cuyos nombres comienzan con C nunca son candidatas, ya que solo la R inicia una referencia. Las notas de lanzamiento de la versión v2.175.0 indican que los literales de cadena e identificadores también se omiten; aun así, si un literal entre comillas en su fórmula contiene texto con una estructura idéntica a una referencia R1C1, vale la pena verificar la salida compilada antes de confiar en ella en producción
Bases de coordenadas y detalles de anclaje que conviene conocer
Dos convenciones se encuentran dentro de esta API, y mantenerlas claras evita la única trampa real. Los parámetros CurRow and CurCol de GetCompiledFormulaR1C1 se basan en 0, siguiendo la API del calculador, mientras que los números dentro de la propia notación se basan en 1, coincidiendo con lo que muestra Excel: R2C3 representa la fila 2, columna 3, que es $C$2, no $D$3. Si el contador del bucle ya se basa en 0, se pasa directamente como CurRow; el incremento de + 1 reside dentro del convertidor, no en el código
Un detalle más es importante si la fórmula compilada sobrevive a la celda para la que fue compilada. Cuando se omite una parte de fila o columna (como en los formatos RC[-1] o R[2]C), la coordenada omitida se resuelve con respecto a la celda anfitriona y se emite como una coordenada absoluta anclada con $ en el texto A1 convertido. En el momento de la evaluación esto resulta invisible, ya que el valor es idéntico de cualquier modo. Sin embargo, el anclaje relativo frente al absoluto determina cómo se desplazan las referencias cuando se insertan o eliminan filas o columnas posteriormente, como se analiza en el artículo complementario sobre el ajuste de referencias de fórmulas. Si necesita que una coordenada siga siendo relativa a lo largo de las ediciones estructurales, escriba el desplazamiento explícitamente (R[0]C[-1] en lugar de RC[-1]) para que el convertidor emita una referencia A1 no anclada
La compilación R1C1 forma parte del motor de fórmulas en el Componente de Hoja de Cálculo Delphi HotXLS, junto con el compilador A1, el grafo de recalculación y la API de evaluación mostrada anteriormente. Si su código compila hojas de cálculo realizando bucles de fórmulas en las columnas, migrar esos bucles de un formato A1 concatenado en cadena a una sola constante R1C1 representa una de las mejoras de fiabilidad más económicas que se pueden realizar