Aplicar formato condicional a los rangos de Excel
La biblioteca de JavaScript de Excel proporciona API para aplicar formato condicional a los rangos de datos en las hojas de cálculo. Esta funcionalidad facilita el análisis visual de grandes conjuntos de datos. El formato también se actualiza dinámicamente según los cambios en del rango.
Nota:
Este artículo trata el formato condicional en el contexto de los complementos de JavaScript de Excel. Los artículos siguientes ofrecen información detallada sobre las capacidades completas de formato condicional de Excel.
Control por programación de formato condicional
La propiedad Range.conditionalFormats es una colección de objetos ConditionalFormat que se aplican al rango. La objeto ConditionalFormat contiene varias propiedades que definen el formato que se aplicará en función del ConditionalFormatType.
cellValuecolorScalecustomdataBariconSetpresettextComparisontopBottom
Nota:
Cada una de estas propiedades de formato se corresponde con una variante *OrNullObject. Más información sobre ese patrón en la sección métodos *OrNullObject.
Solo puede establecerse un tipo de formato para el objeto ConditionalFormat. Esto depende de la propiedad type, que es un valor de enumeración ConditionalFormatType. type se establece al agregar un formato condicional a un rango.
Creación de reglas de formato condicional
Los formatos condicionales se agregan a un rango mediante conditionalFormats.add. Una vez agregado, se pueden establecer las propiedades específicas del formato condicional. Los siguientes ejemplos muestran la creación de distintos tipos de formato.
Valor de celda
El formato condicional del valor de celda aplica un formato definido por el usuario según los resultados de una o dos fórmulas en la ConditionalCellValueRule. La propiedad operator es un ConditionalCellValueOperator que define cómo se relacionan las expresiones resultantes con el formato.
El siguiente ejemplo muestra el color de fuente rojo aplicado a cualquier valor en el rango menor que cero.
await Excel.run(async (context) => {
const sheet = context.workbook.worksheets.getItem("Sample");
const range = sheet.getRange("B21:E23");
const conditionalFormat = range.conditionalFormats.add(
Excel.ConditionalFormatType.cellValue
);
// Set the font of negative numbers to red.
conditionalFormat.cellValue.format.font.color = "red";
conditionalFormat.cellValue.rule = { formula1: "=0", operator: "LessThan" };
await context.sync();
});
Escala de colores
El formato condicional de escala de color aplica un degradado de color en el rango de datos. La propiedad criteria en el ColorScaleConditionalFormat define tres ConditionalColorScaleCriterion: minimum, maximum y, opcionalmente, midpoint. Cada uno de los puntos de escala del criterio tiene tres propiedades:
color: el código de color HTML para el punto de conexión.formula: un número o una fórmula que representa el punto de conexión. Esto seránullsitypeeslowestValueohighestValue.type: cómo se debe evaluar la fórmula.highestValueylowestValuehacen referencia a valores del rango a los que se va a aplicar formato.
El siguiente ejemplo muestra un rango colorado de color azul a amarillo y a rojo. Tenga en cuenta que minimum y maximum son los valores más bajos y altos respectivamente y usan fórmulas null. midpoint usa el tipo percentage con una fórmula de "=50" para que la celda más amarilla sea el valor medio.
await Excel.run(async (context) => {
const sheet = context.workbook.worksheets.getItem("Sample");
const range = sheet.getRange("B2:M5");
const conditionalFormat = range.conditionalFormats.add(
Excel.ConditionalFormatType.colorScale
);
// Color the backgrounds of the cells from blue to yellow to red based on value.
const criteria = {
minimum: {
formula: null,
type: Excel.ConditionalFormatColorCriterionType.lowestValue,
color: "blue"
},
midpoint: {
formula: "50",
type: Excel.ConditionalFormatColorCriterionType.percent,
color: "yellow"
},
maximum: {
formula: null,
type: Excel.ConditionalFormatColorCriterionType.highestValue,
color: "red"
}
};
conditionalFormat.colorScale.criteria = criteria;
await context.sync();
});
Personalizados
El formato condicional personalizado aplica un formato definido por el usuario a las celdas con una fórmula de complejidad arbitraria. El objeto ConditionalFormatRule le permite definir la fórmula en notaciones diferentes:
formula: notación estándar.formulaLocal- Localizado en función del idioma del usuario.formulaR1C1: notación de estilo R1C1.
El ejemplo siguiente da color verde a la fuente de las celdas con valores superiores a la celda de su izquierda.
await Excel.run(async (context) => {
const sheet = context.workbook.worksheets.getItem("Sample");
const range = sheet.getRange("B8:E13");
const conditionalFormat = range.conditionalFormats.add(
Excel.ConditionalFormatType.custom
);
// If a cell has a higher value than the one to its left, set that cell's font to green.
conditionalFormat.custom.rule.formula = '=IF(B8>INDIRECT("RC[-1]",0),TRUE)';
conditionalFormat.custom.format.font.color = "green";
await context.sync();
});
Barra de datos
La barra de datos de formato condicional agrega barras de datos en las celdas. De forma predeterminada, los valores mínimos y máximos del rango forman los límites y el tamaño proporcional de las barras de datos. El DataBarConditionalFormat objeto tiene varias propiedades para controlar la apariencia de la barra.
El ejemplo siguiente aplica formato al rango con barras de datos, rellenadas de izquierda a derecha.
await Excel.run(async (context) => {
const sheet = context.workbook.worksheets.getItem("Sample");
const range = sheet.getRange("B8:E13");
const conditionalFormat = range.conditionalFormats.add(
Excel.ConditionalFormatType.dataBar
);
// Give left-to-right, default-appearance data bars to all the cells.
conditionalFormat.dataBar.barDirection = Excel.ConditionalDataBarDirection.leftToRight;
await context.sync();
});
Conjunto de iconos
El formato condicional de conjuntos de iconos usa iconos de Excel para resaltar las celdas. La propiedad criteria es una matriz de ConditionalIconCriterion, que define el símbolo a insertar y en qué condición insertarlo. Esta matriz se rellena previamente de forma automática con elementos criterio con propiedades predeterminadas. No se pueden sobrescribir las propiedades individuales. En su lugar, se debe reemplazar todo el objeto de criterios.
El siguiente ejemplo muestra un conjunto de iconos de tres triángulos aplicado en el rango.
await Excel.run(async (context) => {
const sheet = context.workbook.worksheets.getItem("Sample");
const range = sheet.getRange("B8:E13");
const conditionalFormat = range.conditionalFormats.add(
Excel.ConditionalFormatType.iconSet
);
const iconSetCF = conditionalFormat.iconSet;
iconSetCF.style = Excel.IconSet.threeTriangles;
/*
With a "three*" icon set style, such as "threeTriangles", the third
element in the criteria array (criteria[2]) defines the "top" icon;
e.g., a green triangle. The second (criteria[1]) defines the "middle"
icon, The first (criteria[0]) defines the "low" icon, but it can often
be left empty as this method does below, because every cell that
does not match the other two criteria always gets the low icon.
*/
iconSetCF.criteria = [
{},
{
type: Excel.ConditionalFormatIconRuleType.number,
operator: Excel.ConditionalIconCriterionOperator.greaterThanOrEqual,
formula: "=700"
},
{
type: Excel.ConditionalFormatIconRuleType.number,
operator: Excel.ConditionalIconCriterionOperator.greaterThanOrEqual,
formula: "=1000"
}
];
await context.sync();
});
Criterios preestablecidos
El formato condicional predefinido aplica un formato definido por el usuario al rango basándose en una regla estándar seleccionada. Estas reglas se definen en el ConditionalFormatPresetCriterion en la ConditionalPresetCriteriaRule.
En el ejemplo siguiente se colorea la fuente blanca siempre que el valor de una celda sea al menos una desviación estándar por encima del promedio del rango.
await Excel.run(async (context) => {
const sheet = context.workbook.worksheets.getItem("Sample");
const range = sheet.getRange("B2:M5");
const conditionalFormat = range.conditionalFormats.add(
Excel.ConditionalFormatType.presetCriteria
);
// Color every cell's font white that is one standard deviation above average relative to the range.
conditionalFormat.preset.format.font.color = "white";
conditionalFormat.preset.rule = {
criterion: Excel.ConditionalFormatPresetCriterion.oneStdDevAboveAverage
};
await context.sync();
});
Comparación de texto
El formato condicional de comparación de texto usa la comparación de cadenas como condición. La propiedad rule es una ConditionalTextComparisonRule que define una cadena que se compara con la celda y un operador para especificar el tipo de comparación.
En el ejemplo siguiente se da formato rojo al color de fuente cuando el texto de una celda contiene "Delayed".
await Excel.run(async (context) => {
const sheet = context.workbook.worksheets.getItem("Sample");
const range = sheet.getRange("B16:D18");
const conditionalFormat = range.conditionalFormats.add(
Excel.ConditionalFormatType.containsText
);
// Color the font of every cell containing "Delayed".
conditionalFormat.textComparison.format.font.color = "red";
conditionalFormat.textComparison.rule = {
operator: Excel.ConditionalTextOperator.contains,
text: "Delayed"
};
await context.sync();
});
Superior e inferior
El formato condicional superior e inferior aplica un formato a los valores mayores o menores de un rango. La propiedad rule, que es del tipo ConditionalTopBottomRule, establece si la condición se basa en el valor mayor o el menor, así como si la evaluación está basada en rango o en porcentaje.
El ejemplo siguiente aplica un resaltado de color verde a la celda con el valor más alto del rango.
await Excel.run(async (context) => {
const sheet = context.workbook.worksheets.getItem("Sample");
const range = sheet.getRange("B21:E23");
const conditionalFormat = range.conditionalFormats.add(
Excel.ConditionalFormatType.topBottom
);
// For the highest valued cell in the range, make the background green.
conditionalFormat.topBottom.format.fill.color = "green"
conditionalFormat.topBottom.rule = { rank: 1, type: "TopItems"}
await context.sync();
});
Cambio de reglas de formato condicional
El ConditionalFormat objeto ofrece varios métodos para cambiar las reglas de formato condicional después de que se hayan establecido.
- changeRuleToCellValue
- changeRuleToColorScale
- changeRuleToContainsText
- changeRuleToCustom
- changeRuleToDataBar
- changeRuleToIconSet
- changeRuleToPresetCriteria
- changeRuleToTopBottom
En el ejemplo siguiente se muestra cómo usar el changeRuleToPresetCriteria método de la lista anterior para cambiar una regla de formato condicional existente al tipo de regla de criterios preestablecidos.
Nota:
El intervalo especificado debe tener una regla de formato condicional existente para usar los métodos de cambio. Si el intervalo especificado no tiene ninguna regla de formato condicional, los métodos de cambio no aplican una nueva regla.
await Excel.run(async (context) => {
const sheet = context.workbook.worksheets.getItem("Sample");
const range = sheet.getRange("B2:M5");
// Retrieve the first existing `ConditionalFormat` rule on this range.
// Note: The specified range must have an existing conditional format rule.
const conditionalFormat = range.conditionalFormats.getItemOrNullObject("0");
// Change the conditional format rule to preset criteria.
conditionalFormat.changeRuleToPresetCriteria({
criterion: Excel.ConditionalFormatPresetCriterion.oneStdDevAboveAverage,
});
conditionalFormat.preset.format.font.color = "red";
await context.sync();
});
Varios formatos y prioridad
Puede aplicar varios formatos condicionales a un rango. Si los formatos tienen elementos en conflicto, como diferentes colores de fuente, solo se aplica un formato a ese elemento en particular. La prioridad está definida por la propiedad ConditionalFormat.priority. La prioridad es un número (igual que el índice de la ConditionalFormatCollection) y puede definirse al crear el formato. Cuanto menor sea el priority valor, mayor será la prioridad del formato.
El ejemplo siguiente muestra una opción de color de fuente en conflicto entre los dos formatos. Los números negativos cambiarán a una fuente en negrita, pero NO a una fuente de color roja, porque se da prioridad al formato que aplica una fuente azul.
await Excel.run(async (context) => {
const sheet = context.workbook.worksheets.getItem("Sample");
const temperatureDataRange = sheet.tables.getItem("TemperatureTable").getDataBodyRange();
// Set low numbers to bold, dark red font and assign priority 1.
const presetFormat = temperatureDataRange.conditionalFormats
.add(Excel.ConditionalFormatType.presetCriteria);
presetFormat.preset.format.font.color = "red";
presetFormat.preset.format.font.bold = true;
presetFormat.preset.rule = { criterion: Excel.ConditionalFormatPresetCriterion.oneStdDevBelowAverage };
presetFormat.priority = 1;
// Set negative numbers to blue font with green background and set priority 0.
const cellValueFormat = temperatureDataRange.conditionalFormats
.add(Excel.ConditionalFormatType.cellValue);
cellValueFormat.cellValue.format.font.color = "blue";
cellValueFormat.cellValue.format.fill.color = "lightgreen";
cellValueFormat.cellValue.rule = { formula1: "=0", operator: "LessThan" };
cellValueFormat.priority = 0;
await context.sync();
});
Formatos condicionales mutuamente excluyentes
La propiedad stopIfTrue de ConditionalFormat impide que se apliquen al rango formatos condicionales de menor prioridad. Cuando se aplica un intervalo que coincida con el formato condicional con stopIfTrue === true, no se aplican los formatos condicionales siguientes, incluso si sus detalles de formato no son contradictorios.
El siguiente ejemplo muestra dos formatos condicionales que se agregan a un rango. Los números negativos tendrán una fuente azul con un fondo verde claro, independientemente de si la otra condición de formato es verdadera.
await Excel.run(async (context) => {
const sheet = context.workbook.worksheets.getItem("Sample");
const temperatureDataRange = sheet.tables.getItem("TemperatureTable").getDataBodyRange();
// Set low numbers to bold, dark red font and assign priority 1.
const presetFormat = temperatureDataRange.conditionalFormats
.add(Excel.ConditionalFormatType.presetCriteria);
presetFormat.preset.format.font.color = "red";
presetFormat.preset.format.font.bold = true;
presetFormat.preset.rule = { criterion: Excel.ConditionalFormatPresetCriterion.oneStdDevBelowAverage };
presetFormat.priority = 1;
// Set negative numbers to blue font with green background and
// set priority 0, but set stopIfTrue to true, so none of the
// formatting of the conditional format with the higher priority
// value will apply, not even the bolding of the font.
const cellValueFormat = temperatureDataRange.conditionalFormats
.add(Excel.ConditionalFormatType.cellValue);
cellValueFormat.cellValue.format.font.color = "blue";
cellValueFormat.cellValue.format.fill.color = "lightgreen";
cellValueFormat.cellValue.rule = { formula1: "=0", operator: "LessThan" };
cellValueFormat.priority = 0;
cellValueFormat.stopIfTrue = true;
await context.sync();
});
Borrar reglas de formato condicional
Para quitar las propiedades de formato de una regla de formato condicional específica, use el método clearFormat del ConditionalRangeFormat objeto . El clearFormat método crea una regla de formato sin configuración de formato.
Para quitar todas las reglas de formato condicional de un rango específico o de una hoja de cálculo completa, use el método clearAll del ConditionalFormatCollection objeto .
En el ejemplo siguiente se muestra cómo quitar todo el formato condicional de una hoja de cálculo con el clearAll método .
await Excel.run(async (context) => {
const sheet = context.workbook.worksheets.getItem("Sample");
const range = sheet.getRange();
range.conditionalFormats.clearAll();
await context.sync();
});