Una regla de Top 10 por ciento sobre una columna de 27 cifras de ventas hace que Excel pinte tres celdas. El diez por ciento de 27 es 2.7, así que un renderizador que redondea obtiene tres, uno que toma el techo obtiene tres y uno que trunca obtiene dos. Dos de esas tres apuestas coinciden con Excel en esta columna, y ambas están mal: Excel trunca el recuento a dos y luego pinta también la tercera celda porque empata con la segunda en 88. Cambia un valor y las apuestas afortunadas dejan de coincidir
Esa es toda la dificultad de dibujar formato condicional fuera de Excel. Las reglas están en el archivo; la respuesta a «qué celdas se iluminan y en qué color» no. Si tu salida es un informe HTML, un PDF o una rejilla que pintas tú, tienes que calcularlo, y las familias de reglas agregadas son donde se esconde la aritmética. Crear reglas es otro tema, cubierto en formato condicional y estilos de texto enriquecido, y lo que le pasa al rango de una regla cuando se mueven filas está cubierto en partición de formatos condicionales anclados. Esta nota va de la evaluación
Lo que el elemento conditionalFormatting dice y no dice
ECMA-376 e ISO 29500-1 definen almacenamiento, y ahí se detienen. Un elemento conditionalFormatting (§18.3.1.18) lleva un sqref y una lista de hijos cfRule (§18.3.1.10). Cada regla tiene un type, un operator opcional, una priority, un indicador stopIfTrue, uno o dos hijos formula y, para las familias visuales, un conjunto de umbrales cfvo. Todo eso registra fielmente lo que el usuario marcó en el diálogo. Nada de eso es un algoritmo
Para muchos tipos de regla la brecha es inofensiva. cellIs con operator="greaterThan" significa mayor que, y containsText significa que la subcadena está presente. El problema empieza con las reglas cuyo veredicto depende del resto del rango: top10 con percent="1" no dice cómo se redondea un recuento fraccional, aboveAverage con stdDev="1" no dice si la desviación es muestral o poblacional, y ninguno de los dos dice si un valor igual al corte califica. Elegir mal y tu PDF discrepa del libro que el cliente tiene abierto en la ventana de al lado
TCondFormatRule.Evaluate: lo que una celda puede decidir sola
HotXLS tiene dos superficies de evaluación, y se diferencian en lo que están dispuestas a responder. El motor clásico (.xls) expone TCondFormatRule.Evaluate en lxCondFormat.pas, que toma una calculadora, un índice de hoja, una posición de celda y el valor de la celda, y devuelve un Boolean para una regla. Cubre los ocho operadores de cellIs (between, not between, equal, not equal, greater, less, greater or equal, less or equal), reglas expression de forma libre evaluadas en la celda para que las referencias relativas se reajusten bien, los cuatro predicados de texto y los predicados de vacío y error. Las fórmulas umbral se resuelven en la posición de la celda, y una regla between cuyos límites llegan invertidos los intercambia en vez de rechazarla. Las comparaciones de texto no distinguen mayúsculas, igual que en Excel
// Motor clásico: una regla, una celda, un Boolean
function AnyRuleMatches(Calculator: TXLSCalculator; CondFormat: TCondFormat;
SheetIndex, Row, Col: Integer; const Value: Variant): Boolean;
var
I: Integer;
begin
Result := False;
for I := 0 to CondFormat.RuleCount - 1 do
// top10, medias, duplicados, periodos de tiempo y los tipos visuales
// responden False aquí; necesitan el rango completo
if CondFormat.Rule(I).Evaluate(Calculator, SheetIndex, Row, Col, Value) then
Exit(True);
end;El método devuelve False para top10, aboveAverage, belowAverage, duplicateValues y uniqueValues. Una celda no puede saber si está en el diez por ciento superior sin ver los demás valores, así que responder sería adivinar. Las reglas de fecha relativa (timePeriod) también responden False en esta ruta. Las cuatro familias visuales, dataBar, colorScale2, colorScale3 e iconSet, responden False por otro motivo: nunca producen un sí o un no, producen un color, una longitud de barra o un índice de icono, y un Boolean no tiene dónde poner eso. Un False de esta llamada significa pues «no decidido aquí» tantas veces como «no coincide», lo que está bien para una comprobación rápida de reglas de comparación y no es seguro para nada que entregues
¿Cómo evita un evaluador de hoja reescanear la hoja por cada celda?
Hace todo el trabajo de rango una vez, al crearse. TXLSXConditionalFormatEvaluator en lxHandleX.pas es una instantánea inmutable de los formatos condicionales de una hoja, obtenida de TXLSXWorksheet.CreateConditionalFormatEvaluator; quien la pide la posee y la libera. La alternativa ingenua, en la que cada celda pintada dispara un escaneo de su rango de reglas para recalcular una media o un rango, convierte un export de 10,000 celdas en 10,000 escaneos de rango por regla
En la construcción el evaluador analiza cada sqref distinto una vez, así que diez reglas sobre el mismo rango comparten un análisis y una pasada por sus celdas pobladas. Esa pasada reúne media, desviación poblacional, mínimo y máximo en un único recorrido, y solo conserva una copia ordenada de los números cuando una regla Top/Bottom o un umbral de percentil necesita de verdad estadísticos de orden. Las reglas de duplicados y únicos construyen y ordenan sus claves de valor una vez, de modo que cada búsqueda posterior es una búsqueda binaria. Las fórmulas de regla se compilan una vez y se reevalúan por celda aplicando el desplazamiento de la celda, así que =A1>AVERAGE($A$1:$A$100) mantiene sus partes relativas y absolutas comportándose exactamente como en Excel sin reanalizarse por cada fila. Las filas se agrupan para que EvaluateCell solo visite las reglas cuyos rangos pueden alcanzar la fila pedida
Las reglas se aplican después en orden de priority, el número más bajo primero, con los empates resueltos por posición en el archivo. Cada propiedad de formato se toma de la primera regla coincidente que la fija, y las reglas de prioridad menor solo rellenan las propiedades que siguen vacías: una regla de prioridad 1 que fija negrita y una regla de prioridad 2 que fija un relleno rojo se muestran ambas, y la celda sale en negrita sobre rojo. Una coincidencia en una regla con StopIfTrue termina el recorrido para esa celda
procedure PaintConditional(Sheet: TXLSXWorksheet; Canvas: TCanvas;
FirstRow, LastRow, FirstCol, LastCol: Integer);
var
Evaluator: TXLSXConditionalFormatEvaluator;
Res: TXLSXCfCellResult;
Row, Col: Integer;
begin
// Una instantánea por pasada de render; filas y columnas empiezan en 1
Evaluator := Sheet.CreateConditionalFormatEvaluator;
try
for Row := FirstRow to LastRow do
for Col := FirstCol to LastCol do
begin
if not Evaluator.EvaluateCell(Row, Col, Res) then
Continue;
if Res.HasFillColor then
Canvas.Brush.Color := TColor(Res.FillColor and $00FFFFFF); // BGR, mismo trazado que TColor
if Res.HasDataBar then // DrawBar es tu propia rutina
DrawBar(Row, Col, Res.DataBarAxis, Res.DataBarEnd, Res.DataBarColor);
if Res.HasIcon then // DrawIcon es tu propia rutina
DrawIcon(Row, Col, Res.IconSetType, Res.IconIndex);
if Res.ShowCellValue and Res.HasNumberFormat and
Sheet.Cells.HasCell(Row, Col) then // DrawCellText es tu propia rutina
DrawCellText(Row, Col,
Evaluator.FormatValue(Sheet.Cells[Row, Col].Value, Res.NumberFormatId));
end;
finally
Evaluator.Free;
end;
end;La instantánea se construye una vez por pasada a propósito. Crearla dentro del bucle interior compila y escanea todo otra vez por cada celda, que es exactamente el coste que este diseño existe para evitar. ShowCellValue vuelve False cuando una regla de barra de datos lleva showValue="0" o un conjunto de iconos está configurado para mostrar solo el icono, de modo que el renderizador dibuja la barra o el glifo y deja fuera el número
¿Cuántas celdas ilumina de verdad una regla de Top 10 por ciento?
El recuento se trunca hacia abajo, nunca baja de uno, y todo valor empatado con el corte se incluye. La especificación no lo dice; es lo que hace Excel, y puedes confirmarlo en un minuto tecleando los 27 valores de la figura en una columna, aplicando Top 10% y contando las celdas pintadas. TXLSXConditionalFormatEvaluator calcula el recuento de rango como Floor(Count * Min(Rank, 100) / 100), lo sube a 1 cuando cae en cero, lo limita al número de celdas numéricas pobladas, lee el corte de los valores ordenados y luego compara cada celda con >=. Para la columna de 27 valores eso significa un recuento de dos, un corte de 88 y tres celdas pintadas porque dos de ellas contienen 88. Las reglas Bottom-N reflejan esto con <=
Las reglas de por encima de la media llevan la segunda ambigüedad. Con stdDev="1" la regla selecciona celdas al menos una desviación estándar por encima de la media, y la desviación muestral y la poblacional se diferencian en la corrección de Bessel, que más pesa en los rangos cortos donde la gente realmente usa esta regla. Con cinco valores las dos difieren en torno al 12 por ciento. HotXLS usa la desviación poblacional, la misma cifra que devuelve STDEV.P, así que una forma rápida de comprobar cualquier renderizador es poner =AVERAGE(r)+STDEV.P(r) junto al rango y ver qué celdas lo superan. Las comparaciones contra una banda de desviación son inclusivas; la comparación simple de por encima o por debajo es estricta salvo que la regla lleve equalAverage="1"
Las reglas de duplicados y únicos dependen de qué cuenta como «el mismo valor». El número 100 y el texto «100» comparten clave, porque el texto numérico se normaliza al espacio de claves numéricas; las claves de texto se comparan sin distinguir mayúsculas; TRUE y 1 siguen siendo distintos, igual que los valores de error de distinto tipo. El texto que contiene * o ? se trata como patrón con comodines, al estilo COUNTIF, de modo que una celda con Q* cuenta contra todo valor que empiece por Q. Las celdas vacías son la trampa contraria: una celda realmente vacía dentro del rango nunca se pinta como duplicado, así que una columna sin terminar no se ilumina de arriba abajo, mientras que una celda que guarda una cadena vacía cuenta entre los vacíos
Escalas de color, barras de datos y conjuntos de iconos vuelven como números
Las familias visuales se resuelven en valores listos para renderizar, y sus bordes son donde los renderizadores escritos a mano derivan. Para una escala de color, EvaluateCell recorta la posición al intervalo de 0 a 1 e interpola cada canal RGB con truncamiento, así que un valor por debajo de la parada mínima recibe el color mínimo en lugar de uno extrapolado. Una escala de tres paradas usa el par medio-máximo para los valores por encima del umbral medio y el par mínimo-medio en los demás casos. Una escala cuyo primer y último umbral resuelven al mismo número devuelve el último color en vez de dividir por cero. Los umbrales de percentil se interpolan linealmente entre los valores ordenados vecinos, la definición PERCENTILE.INC
Las barras de datos se normalizan al ancho de celda. Para un rango todo positivo, DataBarAxis es 0 y DataBarEnd corre entre la longitud mínima y máxima de barra de la regla, que por defecto son el 10 y el 90 por ciento, así que el valor más pequeño sigue teniendo una barra corta y el mayor nunca llena del todo la celda. Cuando el rango cruza el cero, el eje queda en su posición proporcional y DataBarEnd marca dónde se detiene la barra, al otro lado del eje para los valores negativos. Dirección, degradado, borde y colores de barra negativa se rellenan en el mismo registro, con los colores de tema y tintes ya resueltos
Los conjuntos de iconos exigen cuidado con si cada umbral es estricto o inclusivo, porque cada cfvo tras el primero lleva su propio indicador gte. El evaluador recorre los umbrales hacia arriba, aplicando >= o > por umbral según diga ThresholdEqualsInclude, y gana el umbral más alto que el valor supera. Un conjunto invertido voltea el índice resuelto, las anulaciones por icono pueden sustituir un glifo de otra familia mediante IconSetType, y un umbral que no se puede resolver detiene la regla para esa celda, de modo que la celda se queda sin icono en lugar de uno erróneo creíble
Un resultado para la rejilla, el export HTML y el PDF
TXLSXCfCellResult lleva todo lo que un renderizador necesita: color de relleno y de fuente con el tinte de tema aplicado, negrita, cursiva, subrayado, tachado, un id de formato numérico, la geometría y colores de la barra de datos, y la familia e índice del icono. HotXLS alimenta con el mismo registro su export HTML, su export PDF y TXLSWorkbookViewer, lo que mantiene las tres salidas de acuerdo celda a celda. El visor crea un evaluador para la hoja activa la primera vez que pinta y lo reutiliza a través de desplazamiento, selección y repintado, y lo suelta al cambiar de hoja o asignar otro libro
// Editar detrás de un visor en vivo: la instantánea en caché no ve el cambio
Sheet := Viewer.XlsxWorkbook.Sheets[1]; // índice de hoja empezando en 1
Sheet.Cells[5, 2].Value := 4200; // mueve la media, el máximo y la clasificación
Viewer.RefreshConditionalFormats; // suelta la instantánea y repintaComo la instantánea es inmutable, una edición in situ deja medias, rangos y extremos de escala de color obsoletos hasta que se ejecuta RefreshConditionalFormats. Lo mismo vale para tu propio código: un evaluador que conservas entre ediciones debe liberarse y recrearse, porque CreateConditionalFormatEvaluator captura los valores en el momento en que se llama
Las reglas de fecha relativa merecen una decisión temprana. El evaluador de hoja compara las reglas timePeriod contra la fecha actual de la máquina, así que una regla de «esta semana» se renderiza de una forma en un PDF producido hoy y de otra cuando el mismo libro se exporte el próximo martes. Es un comportamiento correcto y aun así genera una incidencia de soporte si se espera que tu archivo sea reproducible. Para informes archivados, sustituye las reglas timePeriod por una regla expression que compare contra una fecha de informe guardada en una celda, como =AND(A2>=$Z$1-6,A2<=$Z$1) para una ventana de siete días; la salida depende entonces del libro y de nada más. Excel también rechaza las referencias estructurales de tabla dentro de una fórmula de formato condicional, así que apunta la regla al rango de celdas de la columna
Si el objetivo es una pantalla en lugar de un archivo, el mismo registro de resultado alimenta también la rejilla de hoja de cálculo VCL personalizada descrita en otro lugar de este blog. El modelo de reglas, la referencia de la API del evaluador y las compilaciones de prueba están en la página del componente de hoja de cálculo HotXLS para Delphi