HotXLS, el componente Excel para Delphi y C++Builder, divide automáticamente una regla de formato condicional o de validación de datos en dos o más objetos de regla separados cada vez que una inserción o eliminación de fila o columna corta el rango cubierto por la regla en piezas que necesitan anclas de fórmula relativas distintas, y luego reasigna a cada regla de formato condicional un número de prioridad nuevo y único. Este comportamiento se incorporó en la versión 2.196 del motor XLSX y se ejecuta automáticamente, sin ninguna configuración para desactivarlo. El disparador es acotado pero común: una regla cellIs o de expresión cuya fórmula lee una celda relativa a su propio rango, en una hoja de cálculo a la que después se le inserta o elimina una fila en algún punto intermedio de ese rango exacto
La mayoría de los análisis sobre automatización de Excel se detienen en el problema del texto de la fórmula: desplazar los números de fila y columna dentro de cada SUM() y cada VLOOKUP() para que las referencias sigan apuntando a las celdas correctas. Esa mitad de la historia es real, y se cubre en el artículo complementario sobre cómo HotXLS reescribe las referencias de fórmula cuando se mueven filas y columnas, pero un formato condicional o una regla de validación de datos no es solo una fórmula sentada en una celda. Empareja una fórmula con un rango, sqref en términos de ECMA-376, y los dos tienen que moverse juntos. Cuando una edición estructural corta ese rango en dos piezas que necesitarían dos desplazamientos relativos distintos para seguir siendo correctas, mantener un solo objeto de regla con una sola cadena de fórmula deja de ser una opción, y fingir lo contrario es cómo una regla de resaltado empieza silenciosamente a comparar las filas equivocadas
¿Por qué insertar una fila divide una regla de formato condicional en lugar de simplemente moverla?
Un formato condicional o una regla de validación de datos mantiene exactamente una fórmula para todo su rango, evaluada relativa a una sola celda ancla, así que en cuanto una edición obliga a que dos partes de ese rango necesiten dos desplazamientos relativos distintos, una sola fórmula ya no puede describir correctamente ambas partes. ECMA-376 expresa la cobertura de una regla como el atributo sqref en el elemento conditionalFormatting o dataValidation, y Excel evalúa Formula1 y Formula2 como si el texto se hubiera escrito en la celda superior izquierda de ese sqref y se hubiera rellenado a través del resto, de la misma manera en que una fórmula relativa ordinaria se rellena hacia abajo por una columna. Imagine un resaltado de varianza sobre B2:B50 que marca cualquier cifra real que exceda su presupuesto, construido como una regla cellIs cuyo Formula1 es el texto literal C2, es decir, comparar la celda B de la fila actual contra la celda C de esa misma fila
Idx := Sheet.AddConditionalFormat('B2:B50', xlsxCfOpGreaterThan, 'C2');
Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);
Sheet.InsertRows(25, 1); // one blank separator row, starting at old row 25Inserte esa fila separadora única en la antigua fila 25 y las filas por encima del punto de inserción no se mueven, así que su parte de la regla sigue leyendo Formula1 como C2 correctamente. Las filas que solían ser 25 a 50 se deslizan hacia abajo a 26 a 51, y para ellas C2 ahora es completamente la celda equivocada, ya que la fila 26 necesita compararse contra C26, no contra una cifra de presupuesto dos docenas de filas arriba
Cómo decide HotXLS si una regla necesita dividirse
HotXLS solo crea objetos de regla adicionales cuando la geometría genuinamente lo requiere: una rutina interna, XlsxBuildShiftedRuleParts, recorre cada área disjunta en el sqref de la regla, calcula cuál era la celda ancla de esa área antes de la edición y en cuál se convierte después, y comprueba si cada pieza resultante necesitaría la misma corrección de desplazamiento relativo. Si todas las piezas coinciden, sobrevive una sola regla, con su sqref reconstruido como la unión de las piezas desplazadas y su fórmula reanclada una sola vez. Una división genuina ocurre solo cuando las piezas no coinciden, exactamente el caso B2:B50 de arriba, donde el bloque superior conserva su ancla original y el bloque inferior necesita una nueva
Reanclar la fórmula de una pieza es un movimiento de dos pasos que reutiliza la maquinaria que HotXLS ya lleva para los grupos de fórmulas compartidas de OOXML: primero la fórmula se traduce como si originalmente hubiera estado anclada en la propia celda superior izquierda de esa pieza, usando la misma matemática de desplazamiento relativo que expande una fórmula compartida a través de su rango, y luego el resultado pasa por el mismo escáner de desplazamiento de filas y columnas que reescribe las fórmulas ordinarias de la hoja de cálculo. Así es como Formula1 pasa de C2 a C26 en dos movimientos en lugar de un caso especial escrito a mano: traducir C2 hacia adelante 23 filas para obtener C25, como si la regla siempre hubiera empezado ahí, y luego dejar que el desplazamiento ordinario en la fila 25 la empuje hasta C26. Cualquier otra propiedad, color de relleno, detener-si-verdadero, el operador en sí, viaja sin cambios hacia el nuevo objeto de regla, así que ambas mitades siguen pintando las celdas del mismo color que siempre tuvieron
// ConditionalFormats now holds two rules instead of one:
// B2:B25 Formula1 = 'C2' (rows above the insert)
// B26:B51 Formula1 = 'C26' (rows that shifted down)¿Las barras de datos y los conjuntos de iconos se dividen igual que las reglas cellIs?
No: HotXLS solo particiona los tipos de regla cuya corrección realmente depende de una fórmula relativa por región, las comparaciones cellIs y las reglas de expresión, y deja cada otro tipo de formato condicional como un único objeto de regla cuyo sqref simplemente crece para cubrir las piezas desplazadas como una unión de múltiples áreas. Internamente la bifurcación es una simple comprobación de Kind, cf.Kind in [cfkCellIs, cfkExpression], nada más exótico que eso. Las barras de datos, las escalas de dos y tres colores, los conjuntos de iconos, los rankings superior e inferior, y los detectores de duplicados, celdas en blanco y errores llevan una carga útil, un color de barra, un conjunto de paradas de escala, una familia de iconos, que describe todo el rango cubierto de una vez en lugar de una comparación relativa por celda, así que dividirlos en varios objetos de regla priorizados no compraría ninguna corrección adicional y solo agregaría reglas que gestionar. Cuando una edición divide su rango, HotXLS recombina las piezas en una sola regla con un sqref de múltiples áreas y reancla la carga útil como una sola unidad en lugar de clonar un nuevo objeto de regla por pieza. La distinción se alinea con la taxonomía de tipos de regla en el artículo sobre los fundamentos de formato condicional y texto enriquecido: las barras de datos, las escalas de color y los conjuntos de iconos ya se distinguen de las reglas cellIs al ignorar por completo la propiedad Style, y ahora resulta que también se distinguen del reanclaje por región por la misma razón subyacente
¿Por qué cambian las prioridades de regla después de una edición estructural?
Las prioridades cambian porque cada clon empieza reteniendo exactamente el mismo valor de prioridad que la regla de la que se dividió, y HotXLS ejecuta después una pasada de normalización que resuelve los duplicados resultantes en un ordenamiento limpio y sin huecos en lugar de dejar dos reglas empatadas en el mismo rango. Una segunda rutina interna, XlsxNormalizeConditionalFormatPriorities, toma la prioridad actual de cada formato condicional, recurre a la posición de esa regla en la colección para cualquier regla que nunca tuvo una establecida explícitamente, ordena toda la lista de forma estable para que los empates conserven su orden relativo original, y renumera el resultado ordenado a una secuencia densa 1, 2, 3 sin huecos ni repeticiones. HotXLS la ejecuta una vez antes de que comience un desplazamiento, así que la clonación parte de una base limpia, y de nuevo después de cada división y de que se elimine cada regla vaciada, así que el archivo que se guarda nunca tiene dos entradas de regla reclamando la misma prioridad. Esto importa si siguió el consejo del artículo de fundamentos de formato condicional de dejar huecos entre los valores de prioridad para que una regla posterior pueda insertarse sin renumerar el resto: los huecos sobreviven hasta que la siguiente edición de fila o columna toque esa hoja de cálculo, y luego colapsan, porque la normalización solo garantiza unicidad y orden estable, no que su esquema de numeración original regrese sin cambios
Las reglas de validación de datos también se dividen, sin una prioridad que renumerar
Las reglas de validación de datos pasan por la misma lógica de particionamiento de rango que las reglas cellIs y de expresión de formato condicional, y a diferencia del formato condicional, cada tipo de validación toma esa ruta de manera uniforme: HotXLS no tiene una familia separada sin fórmula para la validación de datos como sí la tienen las barras de datos y los conjuntos de iconos para el formato condicional, así que una regla de lista simple o de número entero se particiona mediante la misma rutina idéntica que maneja una fórmula personalizada relativa. Lo que difiere es la prioridad: ECMA-376 no le da al elemento dataValidation ningún atributo priority en absoluto, así que no hay paso de renumeración para las validaciones como sí lo hay para los formatos condicionales. Imagine una validación de fórmula personalizada que impide que el importe real de cada fila exceda su propio presupuesto en la columna adyacente
Sheet.AddCustomValidation('D2:D400', 'D2<=C2');
Sheet.DeleteRows(150, 5); // remove five rows out of the validated range
// DataValidations now holds two rules instead of one:
// D2:D149 Formula1 = 'D2<=C2' (rows above the deletion)
// D150:D395 Formula1 = 'D150<=C150' (rows that shifted up)Esto importa por la misma razón por la que el artículo de fundamentos de validación de datos advierte contra adjuntar una regla antes de que el conteo de filas sea definitivo: una validación cubre solo las celdas literales que se le dieron, y una edición estructural posterior puede dejar dos o más reglas haciendo el trabajo que antes hacía una. Nada se rompe funcionalmente: cada celda del rango original sigue siendo validada por algo, pero el código que asume una entrada de DataValidations por columna empezará a indexar mal después de que la primera edición la toque. Hay un tope estricto sobre hasta dónde puede llegar esto: si la división empujaría a una hoja de cálculo más allá de 65.534 reglas de validación de datos, HotXLS lanza una excepción en lugar de escribir un archivo que Excel rechazaría silenciosamente, que es la biblioteca negándose a fabricar un libro corrupto en lugar de un límite que el uso ordinario probablemente alcance
Qué verificar después de una inserción o eliminación masiva
Las dos cosas que vale la pena verificar después de que un script ejecuta un lote de ediciones de fila o columna sobre una hoja llena de formatos condicionales y validaciones son el conteo total de reglas y el orden de prioridad, ya que ambos pueden desviarse de maneras fáciles de pasar por alto en una revisión de código y obvias en cuanto alguien abre Administrar Reglas en Excel. Una sola edición rara vez causa mucho daño: una única inserción en medio de una regla cellIs produce como máximo dos objetos de regla donde había uno. El riesgo se acumula cuando una rutina de generación de informes inserta filas de una en una dentro de un bucle sobre una hoja que ya lleva varias reglas ancladas a fórmulas: cada pasada puede volver a dividir reglas que una pasada anterior ya dividió, y cinco reglas cellIs originales pueden terminar convertidas en varias veces esa cantidad de fragmentos de bajo valor que cubren fracciones del rango original. Agrupar las ediciones estructurales, insertar todo el bloque nuevo en una sola llamada en lugar de fila por fila, mantiene el conteo de reglas ligado al número de anclas genuinamente distintas en lugar del número de ediciones realizadas
El particionamiento de reglas y la normalización de prioridades se incluyen como comportamiento estándar del motor XLSX en el componente Excel HotXLS para Delphi para Delphi y C++Builder; la página del producto lleva la referencia completa de la API de edición de hojas de cálculo, incluidos los métodos de formato condicional y validación de datos descritos aquí