Referencias de fórmulas al insertar filas en HotXLS

HotXLS ajusta las referencias de las fórmulas automáticamente al insertar o eliminar filas o columnas en una hoja de trabajo XLSX. Los métodos InsertRows, DeleteRows, InsertCols y DeleteCols del motor reescriben cada fórmula sobreviviente de modo que sus referencias A1 (relativas, absolutas y de rango) sigan apuntando a los mismos datos después de la edición estructural, y las referencias a un bloque eliminado se convierten en #REF!, coincidiendo con el comportamiento de Excel

El error que esto evita es uno de los más silenciosos en la generación de informes. Un generador escribe cifras diarias en C2:C9 con =SUM(C2:C9) debajo, y luego un paso posterior inserta una fila de encabezado en la parte superior. Si el motor solo mueve los valores de las celdas y deja el texto de la fórmula intacto, esa SUMA sigue leyendo C2:C9 mientras que los datos ahora residen en C3:C10; por lo tanto, el total omite silenciosamente el último día y duplica el valor del encabezado. Nada provoca una excepción, el archivo se abre bien y el número es simplemente incorrecto. Antes de la versión 2.160, el motor XLSX de HotXLS dejaba las fórmulas intactas durante las ediciones estructurales; desde la versión 2.160, la reescritura es automática y no hay ninguna bandera que configurar

¿Qué sucede con las fórmulas al insertar una fila en Excel?

La regla de Excel es que las referencias siguen a los datos, no a las direcciones. Cuando se inserta una fila, cada referencia cuyo índice de fila se sitúe en o por debajo del punto de inserción se desplaza hacia abajo el número de filas insertadas; las referencias situadas completamente por encima del punto de inserción no se modifican. La eliminación de filas ejecuta la misma regla a la inversa: las referencias por debajo del bloque eliminado se desplazan hacia arriba, y las referencias al propio bloque eliminado se convierten en #REF! porque las celdas que nombraban ya no existen. Las columnas se comportan de forma idéntica en el otro eje. Una biblioteca de hojas de cálculo que quiera que su resultado sobreviva al contacto con los usuarios de Excel tiene que reproducir este comportamiento exactamente, porque los usuarios razonan sobre sus fórmulas en estos términos sin siquiera pensarlo

La parte que sorprende a los desarrolladores es que las referencias absolutas también se mueven. Los anclajes $ en $B$2 controlan lo que sucede cuando una fórmula se copia o se arrastra a otra celda; no hacen nada durante las ediciones estructurales. Inserte una fila por encima de la fila 2 y Excel reescribirá $B$2 como $B$3, manteniendo los signos de dólar intactos, porque el valor del que depende la fórmula se movió físicamente a la fila 3. Un motor que desplazara únicamente las referencias relativas corrompería exactamente las fórmulas que los usuarios anclan de forma más deliberada. HotXLS desplaza ambas formas y conserva los marcadores $ en el texto reescrito

¿Cómo desplaza HotXLS las referencias de fórmulas automáticamente?

Los cuatro métodos de edición estructural en TXLSXWorksheet delegan en un único motor de geometría: ShiftSheetGeometry(RowFrom, RowDelta, ColFrom, ColDelta). InsertRows(BeforeRow, Count) lo llama con un delta de fila positivo, DeleteRows(StartRow, Count) con uno negativo, y los métodos de columna hacen lo mismo en el eje de columnas. La rutina reubica primero las celdas en sí (descartando cualquier celda que caiga dentro de un bloque eliminado) y luego recorre cada celda de fórmula restante y pasa su texto a través de XlsxAdjustFormulaRowColRefs, un analizador que busca referencias de estilo A1 y reescribe sus componentes de fila y columna de acuerdo con el desplazamiento. El mismo paso reubica rangos combinados, hipervínculos, comentarios, imágenes, gráficos, formatos condicionales, validaciones de datos y rangos de tablas, de modo que toda la hoja se desplaza como una sola unidad

// Distribución antes de la edición:
//   C2..C9  cifras diarias
//   C10     =SUM(C2:C9)
Sheet.InsertRows(2, 1);      // una fila en blanco antes de la fila 2
// Distribución después de la llamada:
//   C3..C10 cifras diarias
//   C11     =SUM(C3:C10)    -- el rango se movió con los datos

Vale la pena conocer algunos detalles del analizador. XlsxAdjustFormulaRowColRefs reconoce referencias de celda única en las cuatro formas de anclaje (A1, $A1, A$1, $A$1) y rangos de dos esquinas como A1:B3, ajustando cada extremo de forma independiente. Las fórmulas cuyo texto ya comienza con # (un marcador de error de una edición anterior) se omiten en lugar de volver a analizarse. And InsertCols añade una particularidad propia equivalente a Excel: las columnas recién insertadas heredan el ancho de su vecino izquierdo, que es exactamente lo que hace el comando Insertar columnas de hoja de Excel

¿Cuándo se convierte una referencia eliminada en #REF!?

La regla de desplazamiento para un índice de fila o columna individual tiene tres resultados. Un índice anterior al punto de edición no cambia. Un índice situado en o después del punto de edición se desplaza por el delta. Y al eliminar, un índice que cae dentro del bloque eliminado no tiene un nuevo valor coherente (la celda ha desaparecido), por lo que el analizador reescribe la referencia completa como #REF!. Para una referencia de rango, ambos extremos se pasan por la misma regla, y si cualquiera de los extremos cae dentro del bloque eliminado, la referencia se reescribe como #REF! en lugar de dejarse a medio validar

// A12 contiene  =A4+A6+A10
Sheet.DeleteRows(5, 3);      // eliminar filas 5..7
// La fórmula, ahora en A9, lee  =A4+#REF!+A7
//   A4  : por encima del bloque eliminado, sin cambios
//   A6  : dentro de las filas 5..7, desaparecida -> #REF!
//   A10 : por debajo del bloque, se desplaza hacia arriba -> A7

Generar un error #REF! evidente en lugar de redirigir de forma silenciosa es la decisión correcta, y es la misma que toma Excel. Una fórmula que apuntara a una celda vecina después de que se eliminara su entrada real devolvería un número de aspecto plausible; #REF! se propaga a través de las fórmulas dependientes y sale a la luz en la primera prueba rápida. La misma conversión se aplica en el eje de columnas

// E1 contiene  =B1*$C$1
Sheet.DeleteCols(3, 1);      // eliminar columna C
// La fórmula, ahora en D1, lee  =B1*#REF!
// El anclaje absoluto no protegió $C$1 -- la propia celda ha desaparecido

¿Qué formas de referencia no se reescriben?

El analizador se centra en referencias A1 de la misma hoja con una forma explícita de letra de columna más número de fila, y vale la pena ser precisos sobre lo que queda fuera de esto. Las referencias a columnas completas como A:A y a filas completas como 1:1 carecen de uno de los dos componentes, por lo que el analizador las deja tal como están escritas. Las referencias de tabla estructuradas (Table1[Amount]) también se ignoran. La reescritura también funciona estrictamente en notación A1; si su código crea fórmulas en estilo R1C1, conviértalas a A1 antes de una edición estructural, como se describe en el artículo complementario sobre la notación de fórmulas R1C1 en Delphi

Las estructuras entre hojas y a nivel de libro se gestionan mediante pasos independientes en lugar de por el analizador de texto de celdas. Después de ajustar la hoja editada, ShiftSheetGeometry propaga el mismo cambio de geometría a las fórmulas de otras hojas que hacen referencia a la hoja editada, a los rangos de series de gráficos, a los destinos de hipervínculos internos y a los nombres definidos. Los nombres definidos reciben una protección adicional a nivel de ciclo de vida de la hoja: desde la versión 2.150, la eliminación de una hoja de trabajo reescribe cada calificador SheetN! dentro de la fórmula de un nombre definido como #REF!, y el cambio de nombre de una hoja reescribe el calificador con el nuevo nombre, de modo que los nombres nunca apuntan a una hoja que ya no existe. Cómo encajan los nombres y las fórmulas entre hojas se cubre en el artículo sobre nombres definidos y fórmulas entre hojas

Recalcular después del desplazamiento

El ajuste de referencias reescribe el texto de la fórmula; no recalcula los resultados. Después de una edición estructural, los valores almacenados en caché junto a las fórmulas describen la geometría antigua, por lo que la secuencia fiable es: realizar primero todas las inserciones y eliminaciones, luego activar el cálculo una vez y finalmente guardar. Ejecutar el desplazamiento antes del cálculo también mantiene la información de dependencia coherente: cada referencia reescrita nombra a su verdadero precedente, que es exactamente lo que el motor de cálculo incremental y su gráfico de dependencias necesitan para volver a calcular el conjunto mínimo de celdas afectadas. Si una fórmula reescrita contiene ahora #REF!, el cálculo muestra el valor de error inmediatamente en lugar de dejar un número obsoleto en el archivo

El ajuste de referencias de fórmulas se incluye como comportamiento estándar del motor XLSX en HotXLS Delphi Excel Component para Delphi y C++Builder; la página del producto contiene la referencia completa de la API de edición de hojas de trabajo, incluyendo los métodos de inserción y eliminación mostrados aquí