Aplicar formato condicional a intervalos de Excel mediante la API de JavaScript de Excel

Use el formato condicional cuando el complemento de Excel necesite marcar pedidos retrasados, resaltar valores negativos o visualizar tendencias sin cambiar los valores de celda. En este artículo se muestra cómo crear tipos de reglas comunes, actualizar las reglas existentes, controlar la prioridad de las reglas y borrarlas. Para obtener información general sobre la experiencia de formato condicional integrada de Excel, vea Usar formato condicional para resaltar información en Excel y Usar una fórmula para aplicar formato condicional en Excel.

Puntos clave

• Use Range.conditionalFormats para crear y administrar reglas de formato condicional para un intervalo.
• Cada ConditionalFormat objeto solo puede usar un tipo de regla, como cellValue, colorScaleo iconSet.
• Use priority y stopIfTrue para controlar cómo interactúan varias reglas en el mismo intervalo.
• Use clearFormat o clearAll para quitar detalles de formato o reglas completas.

Control por programación de formato condicional

La propiedad Range.conditionalFormats es una colección de objetos ConditionalFormat que se aplican al rango. El ConditionalFormat objeto contiene propiedades que definen el formato que se va a aplicar en función de ConditionalFormatType.

• cellValue
• colorScale
• custom
• dataBar
• iconSet
• preset
• textComparison
• topBottom

Cada una de estas propiedades de formato se corresponde con una variante *OrNullObject. Para obtener más información sobre ese patrón, vea *OrNullObject methods( Métodos de OrNullObject).

Solo puede establecer un tipo de formato para un ConditionalFormat objeto. La type propiedad , que es un valor de enumeración ConditionalFormatType , determina el tipo de formato. Se establece type al agregar un formato condicional a un intervalo.

Creación de reglas de formato condicional comunes

Agregue formatos condicionales a un intervalo mediante conditionalFormats.add. Después de agregar un formato condicional, establezca las propiedades específicas de ese formato. En los escenarios siguientes se muestran tipos de reglas comunes que puede adaptar a la hoja de cálculo.

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 operator propiedad 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 de 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. Este valor es null si type es lowestValue o highestValue.
• type: cómo se debe evaluar la fórmula. highestValue y lowestValue hacen 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 percentage tipo con una fórmula de "=50" por lo que la celda más amarilla es 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.

En el ejemplo siguiente se colorea las fuentes en verde para las celdas con valores más altos que la celda a 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ínimo y máximo del intervalo forman los límites y los tamaños proporcionales 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 criteria propiedad es una matriz de ConditionalIconCriterion, que define el símbolo que se va a insertar y la condición para la inserción. Esta matriz rellena automáticamente con elementos de criterio que tienen propiedades predeterminadas. No se pueden sobrescribir propiedades individuales. En su lugar, reemplace el objeto criteria completo.

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. ConditionalFormatPresetCriterion de ConditionalPresetCriteriaRule define estas reglas.

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 rule propiedad es un ConditionalTextComparisonRule que define una cadena que se va a comparar 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 el código las establezca.

• 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. El intervalo especificado ya debe tener una regla de formato condicional. Si el intervalo no tiene ninguna regla, los métodos de cambio no aplican uno nuevo.

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 ConditionalFormat.priority propiedad define qué formato tiene prioridad. Priority es un número igual al índice en ConditionalFormatCollectiony se establece al crear el formato. Un valor inferior priority significa mayor prioridad.

El ejemplo siguiente muestra una opción de color de fuente en conflicto entre los dos formatos. Los números negativos obtienen una fuente en negrita, pero no una fuente roja, porque la prioridad va al formato que les da 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 el código aplica un formato condicional con stopIfTrue === true a un intervalo, no se aplican formatos condicionales posteriores, 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 tienen 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 mediante 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();
});

Vea también

• Establecimiento del formato de intervalo mediante la API de JavaScript de Excel
• Agregar validación de datos en rangos de Excel
• Conceptos básicos del modelo de objetos de Excel para complementos de Office
• Referencia del objeto ConditionalFormat
• Agregar, cambiar o borrar formatos condicionales
• Uso de una fórmula para aplicar formato condicional en Excel