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 independientes cada vez que una inserción o eliminación de fila o columna corta el rango cubierto por la regla en trozos que necesitan anclas de fórmula relativas distintas, y a continuación 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 ningún ajuste para desactivarlo. El desencadenante es concreto pero habitual: 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 más tarde se le inserta o elimina una fila en algún punto intermedio de ese mismo rango
La mayoría de los artículos 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 una regla de formato condicional o de validación de datos no es solo una fórmula alojada en una celda. Empareja una fórmula con un rango, sqref en términos de ECMA-376, y ambos tienen que moverse juntos. Cuando una edición estructural corta ese rango en dos trozos que necesitarían dos desplazamientos relativos distintos para seguir siendo correctos, mantener un único objeto de regla con una única cadena de fórmula deja de ser una opción, y fingir lo contrario es como una regla de resaltado empieza a comparar silenciosamente las filas equivocadas
¿Por qué insertar una fila divide una regla de formato condicional en lugar de simplemente desplazarla?
Una regla de formato condicional o de validación de datos mantiene exactamente una fórmula para todo su rango, evaluada en relación con una única 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 mediante el atributo sqref del 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 hacia el resto de él, del mismo modo que una fórmula relativa ordinaria se rellena hacia abajo por una columna. Imaginad un resaltado de desviación sobre B2:B50 que marca cualquier cifra real que supere su presupuesto, construido como una regla cellIs cuya Formula1 es el texto literal C2, es decir, comparar la celda B de la fila actual con 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 25Insertad esa única fila separadora 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 antes eran la 25 a la 50 se desplazan hacia abajo a la 26 a la 51, y para ellas C2 pasa a ser directamente la celda equivocada, ya que la fila 26 necesita compararse con C26, no con una cifra de presupuesto veintitantas filas por encima
Cómo decide HotXLS si una regla necesita dividirse
HotXLS solo crea objetos de regla adicionales cuando la geometría realmente lo exige: una rutina interna, XlsxBuildShiftedRuleParts, recorre cada área disjunta del sqref de la regla, calcula cuál era la celda ancla de esa área antes de la edición y cuál pasa a ser después, y comprueba si cada trozo resultante necesitaría la misma corrección de desplazamiento relativo. Si todos los trozos coinciden, sobrevive una única regla, con su sqref reconstruido como la unión de los trozos desplazados y su fórmula reancliada una sola vez. Una división real solo ocurre cuando los trozos discrepan, 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 un trozo es un movimiento de dos pasos que reutiliza la maquinaria que HotXLS ya lleva incorporada para los grupos de fórmulas compartidas de OOXML: primero la fórmula se traduce como si originalmente hubiera estado anclada en la celda superior izquierda propia de ese trozo, usando la misma aritmética de desplazamiento relativo que expande una fórmula compartida a lo largo de su rango, después 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 delante 23 filas para obtener C25, como si la regla siempre hubiera empezado ahí, y después dejar que el desplazamiento ordinario en la fila 25 la lleve hasta C26. Cualquier otra propiedad, color de relleno, detener-si-verdadero, el propio operador, viaja sin cambios al nuevo objeto de regla, así que ambas mitades siguen pintando las celdas del 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)¿Se dividen las barras de datos y los conjuntos de iconos 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 cualquier otro tipo de formato condicional como un único objeto de regla cuyo sqref simplemente crece para cubrir los trozos desplazados como una unión multiárea. 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, las clasificaciones superior e inferior, y los detectores de duplicados, vacíos y errores llevan una carga útil, un color de barra, un conjunto de puntos de escala, una familia de iconos, que describe todo el rango cubierto de una sola vez en lugar de una comparación relativa por celda, así que dividirlos en varios objetos de regla priorizados no aportaría ninguna corrección adicional y solo añadiría reglas que gestionar. Cuando una edición divide su rango, HotXLS recombina los trozos en una única regla con un sqref multiárea y reancla la carga útil como una sola unidad en lugar de clonar un nuevo objeto de regla por trozo. La distinción encaja con la taxonomía de tipos de regla del 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 distinguían de las reglas cellIs por 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 las reglas tras una edición estructural?
Las prioridades cambian porque cada clon empieza conservando 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 orden 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 haya tenido una asignada 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 y sin repeticiones. HotXLS la ejecuta una vez antes de que comience un desplazamiento, de modo 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 que haya quedado vacía, así que el archivo que se guarda nunca tiene dos entradas de regla reclamando la misma prioridad. Esto importa si seguisteis el consejo del artículo sobre los fundamentos del 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 toca esa hoja de cálculo, y entonces colapsan, porque la normalización solo garantiza unicidad y orden estable, no que vuestro esquema de numeración original regrese intacto
Las reglas de validación de datos también se dividen, sin ninguna prioridad que renumerar
Las reglas de validación de datos pasan por la misma lógica de particionado de rango que los formatos condicionales cellIs y de expresión, y a diferencia del formato condicional, todos los tipos de validación siguen esa vía de manera uniforme: HotXLS no tiene una familia aparte 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 gestiona una fórmula personalizada relativa. Lo que sí difiere es la prioridad: ECMA-376 no da al elemento dataValidation ningún atributo priority en absoluto, así que no existe ningún paso de renumeración para las validaciones como sí lo hay para los formatos condicionales. Imaginad una validación de fórmula personalizada que impide que el importe real de cada fila supere su propio presupuesto en la columna contigua
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 sobre los fundamentos de la validación de datos advierte contra adjuntar una regla antes de que el número de filas sea definitivo: una validación cubre únicamente las celdas literales que le disteis, y una edición estructural posterior puede dejar dos o más reglas haciendo el trabajo que antes hacía una sola. Nada se rompe funcionalmente: cada celda del rango original sigue estando validada por algo, pero el código que asume una entrada de DataValidations por columna empezará a indexar mal en cuanto la toque la primera edición. Hay un techo estricto sobre hasta dónde puede llegar esto: si una división empujara 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 en silencio, que es la biblioteca negándose a fabricar un libro corrupto en lugar de un límite que el uso ordinario vaya a alcanzar probablemente
Qué comprobar tras una inserción o eliminación masiva
Las dos cosas que merece la pena verificar después de que un script ejecute un lote de ediciones de filas o columnas sobre una hoja llena de formatos condicionales y validaciones son el recuento total de reglas y el orden de prioridad, ya que ambos pueden desviarse de formas fáciles de pasar por alto en una revisión de código y evidentes 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 antes 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 había dividido, y cinco reglas cellIs originales pueden acabar convertidas en varias veces esa cantidad de fragmentos de poco valor que cubren fragmentos minúsculos del rango original. Agrupar las ediciones estructurales, insertando todo el bloque nuevo en una sola llamada en lugar de fila a fila, mantiene el recuento de reglas ligado al número de anclas genuinamente distintas en lugar de al número de ediciones realizadas
El particionado 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 recoge 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í