Evaluación de formato condicional de Excel en HotXLS

Una regla Top 10 por ciento está sobre una columna de 27 cifras de ventas, y Excel pinta tres celdas. El diez por ciento de 27 es 2.7, de modo que un renderizador que redondea obtiene tres, uno que toma el techo obtiene tres, y uno que trunca hacia abajo obtiene dos. Dos de esas tres adivinanzas coinciden con Excel en esta columna, y ambas están mal: Excel trunca el conteo a dos y además pinta la tercera celda porque empata con la segunda en 88. Cambie un valor y las adivinanzas 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 de qué color" no. Si su salida es un informe HTML, un PDF o una cuadrícula que usted pinta, tiene que calcularla, y las familias de reglas agregadas son donde se esconde la aritmética. La autoría de reglas es un tema aparte, 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 sobre la evaluación

Lo que el elemento conditionalFormatting dice y no dice

ECMA-376 e ISO 29500-1 definen almacenamiento, y hasta ahí llegan. 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, una bandera 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 conteo fraccionario, aboveAverage con stdDev="1" no dice si la desviación es de muestra o poblacional, y ninguna de las dos dice si un valor igual al corte califica. Elija mal y su PDF no coincide con el libro que el cliente tiene abierto en la siguiente ventana

TCondFormatRule.Evaluate: lo que una celda puede decidir por su cuenta

HotXLS tiene dos superficies de evaluación, y difieren 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 rebaseen correctamente, los cuatro predicados de texto, y los predicados de vacío y error. Las fórmulas de 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, promedios, duplicados, períodos 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 otra razón: 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, por tanto, "no decidido aquí" tan a menudo como "no coincide", lo cual está bien para una comprobación rápida de reglas de comparación y no es seguro para nada que usted envíe

¿Cómo evita un evaluador de hoja volver a escanear la hoja por cada celda?

Hace todo el trabajo de rango una vez, cuando se crea. TXLSXConditionalFormatEvaluator en lxHandleX.pas es una instantánea inmutable de los formatos condicionales de una hoja, obtenida de TXLSXWorksheet.CreateConditionalFormatEvaluator; quien la llama es dueño de ella y la libera. La alternativa ingenua, donde cada celda pintada dispara un escaneo de su rango de reglas para recalcular un promedio o un ranking, convierte una exportación de 10 000 celdas en 10 000 escaneos de rango por regla

En la construcción, el evaluador analiza cada sqref distinto una vez, de modo que diez reglas sobre el mismo rango comparten un análisis y un pase por sus celdas pobladas. Ese pase junta media, desviación poblacional, mínimo y máximo en un solo recorrido, y conserva una copia ordenada de los números solo cuando una regla Top/Bottom o un umbral percentil realmente necesita estadísticas de orden. Las reglas de duplicados y únicos construyen y ordenan sus claves de valor una vez, así 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 con el desplazamiento de la celda aplicado, de modo que =A1>AVERAGE($A$1:$A$100) mantiene sus partes relativas y absolutas comportándose exactamente como lo harían en Excel sin volver a analizarse 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 luego 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 que coincide y la establece, y las reglas de menor prioridad solo rellenan propiedades que siguen vacías: una regla de prioridad 1 que pone negrita y una regla de prioridad 2 que pone 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 pase de renderizado; filas y columnas tienen base uno
  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 diseño que TColor
        if Res.HasDataBar then  // DrawBar es su propia rutina
          DrawBar(Row, Col, Res.DataBarAxis, Res.DataBarEnd, Res.DataBarColor);
        if Res.HasIcon then     // DrawIcon es su propia rutina
          DrawIcon(Row, Col, Res.IconSetType, Res.IconIndex);
        if Res.ShowCellValue and Res.HasNumberFormat and
           Sheet.Cells.HasCell(Row, Col) then  // DrawCellText es su 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 pase a propósito. Crearla dentro del bucle interno compila y escanea todo de nuevo por cada celda, que es exactamente el costo que el 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, así que el renderizador dibuja la barra o el glifo y deja fuera el número

¿Cuántas celdas resalta realmente una regla Top 10 por ciento?

El conteo se trunca hacia abajo, nunca baja de uno, y todo valor empatado con el corte se incluye. La especificación no dice esto; es lo que Excel hace, y usted lo confirma en un minuto escribiendo los 27 valores de la figura en una columna, aplicando Top 10% y contando las celdas pintadas. TXLSXConditionalFormatEvaluator calcula el conteo de rango como Floor(Count * Min(Rank, 100) / 100), lo eleva 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 conteo de dos, un corte de 88 y tres celdas pintadas porque dos de ellas tienen 88. Las reglas Bottom-N hacen lo espejo con <=

Las reglas de superior al promedio cargan 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 de muestra y la poblacional difieren por la corrección de Bessel, que más importa en los rangos cortos donde la gente realmente usa esta regla. Con cinco valores ambas difieren en unos 12 por ciento. HotXLS usa la desviación poblacional, la misma cifra que devuelve STDEV.P, así que una forma rápida de verificar 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 superior o inferior es estricta salvo que la regla lleve equalAverage="1"

Las reglas de duplicados y únicos giran en torno a qué cuenta como "el mismo valor". El número 100 y el texto "100" comparten una 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 permanecen distintos, igual que los valores de error de distinto tipo. El texto que contiene * o ? se compara como patrón con comodines, al estilo COUNTIF, así que una celda con Q* cuenta contra todo valor que empiece con Q. Las celdas vacías son la trampa opuesta: una celda verdaderamente vacía dentro del rango nunca se pinta como duplicada, así que una columna sin terminar no se enciende de arriba a abajo, mientras que una celda que guarda una cadena vacía se cuenta entre las vacías

Las escalas de color, barras de datos y conjuntos de iconos vuelven como números

Las familias visuales se resuelven a valores listos para renderizar, y sus bordes son donde los renderizadores escritos a mano se desvían. Para una escala de color, EvaluateCell limita 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 obtiene el color mínimo en vez de uno extrapolado. Una escala de tres paradas usa el par medio-máximo para valores por encima del umbral medio y el par mínimo-medio en los demás casos. Una escala cuyos umbrales primero y último se resuelven al mismo número devuelve el último color en vez de dividir por cero. Los umbrales percentiles se interpolan linealmente entre valores ordenados vecinos, la definición de PERCENTILE.INC

Las barras de datos se normalizan al ancho de la celda. Para un rango que es 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 10 y 90 por ciento, de modo que el valor más pequeño igual recibe una barra corta y el más grande 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, del lado lejano del eje para 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 requieren cuidado con la rigurosidad de cada umbral, porque cada cfvo después del primero lleva su propia bandera gte. El evaluador recorre los umbrales hacia arriba, aplicando >= o > por umbral como dice ThresholdEqualsInclude, y gana el umbral más alto que el valor supera. Un conjunto invertido voltea el índice resuelto, las sobrescrituras 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 no recibe ningún icono en vez de uno equivocado que parece plausible

Un resultado para la cuadrícula, la exportación HTML y el PDF

TXLSXCfCellResult lleva todo lo que un renderizador necesita: color de relleno y de fuente con el tinte del 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 a su exportación HTML, su exportación PDF y TXLSWorkbookViewer, lo que mantiene las tres salidas de acuerdo celda por 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 cuando usted cambia de hoja o asigna otro libro

// Editando detrás de un visor en vivo: la instantánea en caché no ve el cambio
Sheet := Viewer.XlsxWorkbook.Sheets[1];   // índice de hoja con base uno
Sheet.Cells[5, 2].Value := 4200;          // mueve la media, el máximo y el ranking
Viewer.RefreshConditionalFormats;         // suelta la instantánea y repinta

Como la instantánea es inmutable, una edición en el lugar deja obsoletas las medias, los rankings y los extremos de la escala de color hasta que corre RefreshConditionalFormats. Lo mismo aplica a su propio código: un evaluador que conserve a través de 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 exporta el próximo martes. Es un comportamiento correcto y aun así un ticket de soporte si se espera que su archivo sea reproducible. Para informes archivados, reemplace las reglas timePeriod por una regla expression que compare contra una fecha de informe guardada en una celda, por ejemplo =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 referencias estructuradas a tablas dentro de una fórmula de formato condicional, así que apunte la regla al rango de celdas de la columna

Si el destino es una pantalla en lugar de un archivo, el mismo registro de resultado también impulsa la cuadrícula 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