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 para 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 conviertan 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 en un paso posterior se 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 tal cual, esa suma SUM sigue leyendo C2:C9 mientras que los datos ahora residen en C3:C10, por lo que el total descarta silenciosamente el último día y cuenta dos veces un encabezado. Nada produce 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 2.160, la reescritura es automática y no hay ningún parámetro 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 ubique en o por debajo del punto de inserción se desplaza hacia abajo según la cantidad de filas insertadas; las referencias ubicadas por completo encima del punto de inserción no se modifican. Eliminar filas ejecuta la misma regla a la inversa: las referencias debajo del bloque eliminado se deslizan hacia arriba, y las referencias al propio bloque eliminado se convierten en #REF! porque las celdas a las que hacían referencia ya no existen. Las columnas se comportan de forma idéntica en el otro eje. Una biblioteca de hojas de cálculo que pretenda que su resultado sobreviva al uso de los usuarios de Excel debe reproducir esta mecánica con exactitud, ya que los usuarios razonan sobre sus fórmulas en estos términos sin 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 realizan nada durante las ediciones estructurales. Inserte una fila arriba de la fila 2 y Excel reescribirá $B$2 como $B$3, con los signos de dólar intactos, debido a que el valor del cual depende la fórmula se movió físicamente a la fila 3. Un motor que desplazara únicamente referencias relativas corrompería exactamente las fórmulas que las personas anclan de forma más deliberada. HotXLS desplaza ambas variantes y conserva los marcadores $ en el texto reescrito
¿Cómo desplaza HotXLS las referencias de fórmulas de forma automática?
Los cuatro métodos de edición estructural en TXLSXWorksheet delegan en un único motor geométrico: ShiftSheetGeometry(RowFrom, RowDelta, ColFrom, ColDelta). InsertRows(BeforeRow, Count) lo llama con una diferencia de fila positiva, DeleteRows(StartRow, Count) con una negativa, y los métodos de columna realizan lo mismo en el eje de las columnas. La rutina primero reubica las celdas mismas (descartando cualquier celda que caiga dentro de un bloque eliminado) y luego recorre cada celda de fórmula sobreviviente y pasa su texto a través de XlsxAdjustFormulaRowColRefs, un escáner que encuentra referencias estilo A1 y reescribe sus componentes de fila y columna según el desplazamiento. La misma pasada reubica rangos combinados, hipervínculos, comentarios, imágenes, gráficos, formatos condicionales, validaciones de datos y rangos de tablas, por lo que toda la hoja se mueve como una sola unidad
// Layout before the edit:
// C2..C9 daily figures
// C10 =SUM(C2:C9)
Sheet.InsertRows(2, 1); // one blank row before row 2
// Layout after the call:
// C3..C10 daily figures
// C11 =SUM(C3:C10) -- the range moved with the dataVale la pena conocer algunos detalles del escáner. XlsxAdjustFormulaRowColRefs reconoce referencias de una sola celda en sus 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 escanearse. E InsertCols agrega un detalle propio para mantener la paridad con Excel: las columnas recién insertadas heredan el ancho de su vecino izquierdo, que es lo que realiza 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 se modifica. Un índice en o después del punto de edición se mueve según la diferencia. Y bajo una eliminación, un índice que cae dentro del bloque eliminado no tiene ningún valor nuevo significativo (la celda desapareció), por lo que el escáner reescribe toda la referencia como #REF!. Para una referencia de rango, ambos extremos pasan por la misma regla, y si cualquiera de los extremos aterriza dentro del bloque eliminado, la referencia se reescribe como #REF! en lugar de dejarse a medias
// A12 holds =A4+A6+A10
Sheet.DeleteRows(5, 3); // delete rows 5..7
// The formula, now in A9, reads =A4+#REF!+A7
// A4 : above the deleted block, unchanged
// A6 : inside rows 5..7, gone -> #REF!
// A10 : below the block, slides up -> A7Producir un error #REF! evidente en lugar de redireccionar silenciosamente es la solución correcta, y es la que adopta Excel. Una fórmula que apuntara a una celda vecina después de que se eliminó 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 las columnas
// E1 holds =B1*$C$1
Sheet.DeleteCols(3, 1); // remove column C
// The formula, now in D1, reads =B1*#REF!
// The absolute anchor did not protect $C$1 -- the cell itself is gone¿Qué formas de referencia no se reescriben?
El escáner apunta a 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 eso. 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 escáner las deja tal como están escritas. Las referencias de tabla estructuradas (Table1[Amount]) también se ignoran. La reescritura también opera estrictamente en notación A1; si su código construye 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
Recalcular después del desplazamiento
El ajuste de referencias reescribe el texto de las fórmulas; no vuelve a calcular 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 confiable es: realizar primero todas las inserciones y eliminaciones, luego activar el recálculo una vez y, finalmente, guardar. Ejecutar el desplazamiento antes del recálculo también mantiene la información de dependencia correcta (cada referencia reescrita nombra su verdadero precedente, que es exactamente lo que el motor de recá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 ahora contiene #REF!, el recálculo muestra el valor de error de inmediato en lugar de dejar un número obsoleto en el archivo
El ajuste de referencias de fórmulas se distribuye como comportamiento estándar del motor XLSX en el Componente HotXLS Delphi Excel 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í