Aplicar formatação condicional a intervalos do Excel usando a API JavaScript do Excel

Use formatação condicional quando o suplemento do Excel precisar sinalizar pedidos atrasados, realçar valores negativos ou visualizar tendências sem alterar os valores da célula. Este artigo mostra como criar tipos de regras comuns, atualizar regras existentes, controlar a prioridade das regras e limpar regras. Para obter informações sobre a experiência interna de formatação condicional do Excel, consulte Usar formatação condicional para realçar informações no Excel e Usar uma fórmula para aplicar formatação condicional no Excel.

Principais pontos

• Use Range.conditionalFormats para criar e gerenciar regras de formatação condicional para um intervalo.
• Cada ConditionalFormat objeto pode usar apenas um tipo de regra, como cellValue, colorScale, ou iconSet.
• Use priority e stopIfTrue controle como várias regras interagem no mesmo intervalo.
• Use clearFormat ou clearAll para remover detalhes de formatação ou regras inteiras.

Controle de programação de formatação condicional

A Range.conditionalFormats propriedade é uma coleção de objetos ConditionalFormat que se aplicam ao intervalo. O ConditionalFormat objeto contém propriedades que definem o formato a ser aplicado com base no ConditionalFormatType.

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

Cada uma das seguintes propriedades de formatação tem uma variante *OrNullObject correspondente. Para obter mais informações sobre esse padrão, consulte os métodos *OrNullObject.

Você pode definir apenas um tipo de formato para um ConditionalFormat objeto. A type propriedade, que é um valor de enumeração ConditionalFormatType , determina o tipo de formato. Defina type quando você adiciona um formato condicional a um intervalo.

Criar regras comuns de formatação condicional

Adicionar formatos condicionais a um intervalo usando conditionalFormats.add. Depois de adicionar um formato condicional, defina as propriedades específicas para esse formato. Os cenários a seguir mostram tipos de regras comuns que você pode adaptar à sua planilha.

Valor da célula

A formatação condicional de valor de célula aplica um formato definidas pelo usuário com base em uma ou duas fórmulas em ConditionalCellValueRule. A operator propriedade é um ConditionalCellValueOperator que define como as expressões resultantes se relacionam com a formatação.

O exemplo a seguir mostra a cor de fonte vermelha aplicada a qualquer valor no intervalo menor que zero.

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 cores

Formatação condicional de escala de cores aplica um gradiente de cor para o intervalo de dados. A criteria propriedade na ColorScaleConditionalFormat define três ConditionalColorScaleCriterion: minimum, maximume, opcionalmente, midpoint. Cada um dos pontos de escala de critério tem três propriedades:

• color – O código de cor HTML para o ponto de extremidade.
• formula – Um número ou uma fórmula que representa o ponto de extremidade. Esse valor será null se type for lowestValue ou highestValue.
• type Como a fórmula deve ser avaliada. highestValue e lowestValue fazem referência a valores no intervalo a ser formatado.

O exemplo a seguir mostra um intervalo a ser colorido de azul para amarelo para vermelho. Observe que minimum e maximum são os valores mais altos e mais baixos, respectivamente e usam null fórmulas. midpoint usa o percentage tipo com uma fórmula de modo que "=50" a célula mais amarela seja o valor médio.

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

A formatação condicional personalizada aplica um formato definido pelo usuário para as células com base em uma fórmula de complexidade arbitrária. O objeto ConditionalFormatRule permite que você defina a fórmula em notações diferentes:

• formula -Anotação padrão.
• formulaLocal - Localizado com base no idioma do usuário.
• formulaR1C1 -Notação estilo R1C1.

O exemplo a seguir colore as fontes de verde para células com valores mais altos do que a célula à esquerda.

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 dados

A barra de formatação condicional de dados adiciona barras de dados nas células. Por padrão, os valores mínimo e máximo no intervalo formam os limites e tamanhos proporcionais das barras de dados. O DataBarConditionalFormat objeto tem várias propriedades para controlar a aparência da barra.

O exemplo a seguir formata o intervalo com barras de dados preenchidas da esquerda para a direita.

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 ícones

A formatação condicional do conjunto de ícones usa os ícones do Excel para realçar células. A criteria propriedade é uma matriz de ConditionalIconCriterion, que define o símbolo a ser inserido e a condição para inserção. Essa matriz é preenchida automaticamente com elementos de critério que têm propriedades padrão. Não é possível substituir propriedades individuais. Em vez disso, substitua todo o objeto de critérios.

O exemplo a seguir mostra um conjunto de ícones de três triângulos aplicado ao intervalo.

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();
});

Critérios predefinidos

A formatação condicional predefinida aplica um formato definido pelo usuário ao intervalo com base em uma regra padrão selecionada. O ConditionalFormatPresetCriterion no ConditionalPresetCriteriaRule define essas regras.

O exemplo a seguir colore a fonte de branco sempre que o valor de uma célula estiver pelo menos um desvio padrão acima da média do intervalo.

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();
});

Comparação de texto

A formatação condicional de texto comparação usa comparações de cadeias como condição. A rule propriedade é um ConditionalTextComparisonRule que define uma cadeia de caracteres para comparar com a célula e um operador para especificar o tipo de comparação.

O exemplo a seguir formata a cor da fonte em vermelho quando o texto de uma célula contiver "Atrasado".

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();
});

Superiores/inferiores

A formatação condicional superiores/inferiores aplica um formato para maiores ou menores valores em um intervalo. As rule propriedade é do tipo ConditionalTopBottomRule, define a condição se baseia no maior ou menor, e se a avaliação é ordenada ou na baseada na porcentagem.

O exemplo a seguir aplica um destaque em verde na maior célula valor do intervalo.

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();
});

Alterar regras de formatação condicional

O ConditionalFormat objeto oferece vários métodos para alterar as regras de formatação condicional depois que seu código as define.

• changeRuleToCellValue
• changeRuleToColorScale
• changeRuleToContainsText
• changeRuleToCustom
• changeRuleToDataBar
• changeRuleToIconSet
• changeRuleToPresetCriteria
• changeRuleToTopBottom

O exemplo a seguir mostra como usar o changeRuleToPresetCriteria método da lista anterior para alterar uma regra de formato condicional existente para o tipo de regra de critérios predefinidos. O intervalo especificado já deve ter uma regra de formato condicional. Se o intervalo não tiver nenhuma regra, os métodos de alteração não aplicarão uma nova.

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();
});

Vários formatos e prioridades

Você pode aplicar vários formatos condicionais em um intervalo. Se os formatos tem elementos conflitantes, como cores de fonte diferentes apenas um formato aplica-se a esse elemento determinado. A ConditionalFormat.priority propriedade define qual formato tem precedência. Prioridade é um número igual ao índice no ConditionalFormatCollection, e você o define ao criar o formato. Um valor menor priority significa maior prioridade.

O exemplo a seguir mostra uma opção de cor da fonte conflitante entre os dois formatos. Os números negativos recebem uma fonte em negrito, mas não uma fonte vermelha, porque a prioridade vai para o formato que lhes dá uma fonte 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 condicionais mutuamente exclusivos

As stopIfTrue propriedade de ConditionalFormat impede que os formatos condicionais de prioridade inferiores sejam aplicados ao intervalo. Quando seu código aplica um formato condicional a stopIfTrue === true um intervalo, nenhum formato condicional subsequente se aplica, mesmo que seus detalhes de formatação não sejam contraditórios.

O exemplo a seguir mostra dois formatos condicionais adicionados a um intervalo. Os números negativos têm uma fonte azul com fundo verde claro, independentemente de a outra condição de formato ser verdadeira.

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();
});

Limpar regras de formatação condicional

Para remover propriedades de formato de uma regra de formato condicional específica, use o método clearFormat do ConditionalRangeFormat objeto. O clearFormat método cria uma regra de formatação sem configurações de formato.

Para remover todas as regras de formatação condicional de um intervalo específico ou de uma planilha inteira, use o método clearAll do ConditionalFormatCollection objeto.

O exemplo a seguir mostra como remover toda a formatação condicional de uma planilha usando o 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();
});

Confira também

• Definir o formato do intervalo usando a API JavaScript do Excel
• Adicionar validação de dados para intervalos do Excel
• Principais conceitos de modelo de objeto do Excel para Suplementos do Office
• Referência de objeto ConditionalFormat
• Adicionar, alterar ou limpar formatações condicionais
• Usar uma fórmula para aplicar formatação condicional no Excel