Adicionar validação de dados para intervalos do Excel

Artigo
04/07/2023

A biblioteca JavaScript do Excel fornece APIs para permitir que seu suplemento adicione a validação de dados automáticos a tabelas, colunas, linhas e outros intervalos em uma pasta de trabalho. Para entender os conceitos e a terminologia da validação de dados, consulte os artigos a seguir sobre como os usuários adicionam a validação de dados por meio da interface do usuário do Excel.

Controle de programação de validação de dados

A Range.dataValidation propriedade, que usa um objeto DataValidation, é o ponto de entrada para o controle de programação de validação de dados no Excel. Há cinco propriedades para o objeto DataValidation:

rule — define o que constitui dados válidos para o intervalo. Ver DataValidationRule.
errorAlert — Especifica se um erro aparece se o usuário inserir dados inválidos e definir o texto de alerta, o título e o estilo; por exemplo, information, warninge stop. Ver DataValidationErrorAlert.
prompt — Especifica se um prompt é exibido quando o usuário passa o mouse sobre o intervalo e define a mensagem de prompt. Ver DataValidationPrompt.
ignoreBlanks — especifica se a regra de validação de dados se aplica a células em branco no intervalo. O padrão é true
type — Uma identificação somente leitura do tipo de validação, como WholeNumber, Date, TextLength etc. Ele é definido indiretamente quando você define a rule propriedade.

Observação

A validação de dados adicionada programaticamente funciona exatamente como a validação de dados adicionada manualmente. Em particular, observe que a validação de dados é disparada somente se o usuário inserir diretamente um valor em uma célula ou copiar e colar uma célula de outro local da pasta de trabalho e escolher a opção de colagem Valores. Se o usuário copiar uma célula e fizer uma colagem simples em um intervalo com a validação de dados, a validação não será disparada.

Criar regras de validação

Para adicionar a validação de dados a um intervalo, o código deve configurar a propriedade rule do objeto DataValidation em Range.dataValidation. Isso leva ao objeto DataValidationRule que tem sete propriedades opcionais. Não mais de uma dessas propriedades pode estar presente em qualquer objeto DataValidationRule. A propriedade que você incluir determina o tipo de validação.

Tipos de regras de validação Basic e DateTime

As três primeiras propriedades DataValidationRule (ou seja, tipos de regra de validação) consideram o objeto BasicDataValidation como o seu valor.

wholeNumber — Requer um número inteiro, além de qualquer outra validação especificada pelo BasicDataValidation objeto.
decimal — Requer um número decimal, além de qualquer outra validação especificada pelo BasicDataValidation objeto.
textLength — Aplica os detalhes de validação no BasicDataValidation objeto ao comprimento do valor da célula.

Aqui está um exemplo de como criar uma regra de validação. Observe o seguinte sobre este código.

O operator é o operador greaterThanbinário . Sempre que você usa um operador binário, o valor que o usuário tenta inserir na célula é o operando à esquerda e o valor especificado em formula1 é o operando à direita. Então esta regra diz que apenas números inteiros que são maiores do que 0 são válidos.
O formula1 é um número embutido. Se não souber no momento da codificação qual é o valor, você também poderá usar uma fórmula do Excel (como uma cadeia de caracteres) para o valor. Por exemplo, "= A3" e "SOMA(A4,B5) =" também seriam valores formula1.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    let range = sheet.getRange("B2:C5");

    range.dataValidation.rule = {
            wholeNumber: {
                formula1: 0,
                operator: Excel.DataValidationOperator.greaterThan
            }
        };

    await context.sync();
});

Confira BasicDataValidation para uma lista de outros operadores binários.

Há também dois operadores ternários: between e notBetween. Para usá-los, você deve especificar a propriedade formula2 opcional. Os valoresformula1 e formula2 são os operandos delimitadores. O valor que o usuário tenta inserir na célula é o terceiro operando (calculado). A seguir está um exemplo de como usar o operador "Entre".

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    let range = sheet.getRange("B2:C5");

    range.dataValidation.rule = {
            decimal: {
                formula1: 0,
                formula2: 100,
              operator: Excel.DataValidationOperator.between
            }
        };

    await context.sync();
});

As próximas duas regras de propriedades usam o objeto DateTimeDataValidation como seu valor.

date
time

O objeto DateTimeDataValidation é estruturado da mesma forma que o BasicDataValidation: com as propriedades formula1, formula2 e operator, e é usado da mesma maneira. A diferença é que você não pode usar um número nas propriedades de fórmula, mas você pode inserir uma cadeia ISO 8606 datetime (ou uma fórmula do Excel). Veja a seguir um exemplo que define valores válidos como datas na primeira semana de abril de 2022.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    let range = sheet.getRange("B2:C5");

    range.dataValidation.rule = {
            date: {
                formula1: "2022-04-01",
                formula2: "2022-04-08",
                operator: Excel.DataValidationOperator.between
            }
        };

    await context.sync();
});

Tipos de regra de validação de lista

Use a propriedade list do objeto DataValidationRule para especificar valores que são válidos apenas em uma lista finita. Apresentamos um exemplo a seguir. Observe o seguinte sobre este código.

Ele pressupõe que se trata de uma planilha chamada "Nomes" e que os valores no intervalo "A1: A3" são nomes.
A propriedade source especifica a lista de valores válidos. O argumento de cadeia de caracteres se refere a um intervalo que contém os nomes. Você também pode atribuir uma lista delimitada por vírgula; por exemplo: "Lara, Pedro, Marina".
A propriedade inCellDropDown especifica se um controle de lista suspensa será exibido na célula quando o usuário a selecionar. Se definido como true, em seguida, a lista suspensa é exibida com a lista de valores do source.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    let range = sheet.getRange("B2:C5");   
    let nameSourceRange = context.workbook.worksheets.getItem("Names").getRange("A1:A3");

    range.dataValidation.rule = {
        list: {
            inCellDropDown: true,
            source: "=Names!$A$1:$A$3"
        }
    };

    await context.sync();
})

Tipo de regra de validação personalizada

Use a propriedade custom no objeto DataValidationRule para especificar uma fórmula de validação personalizada. Apresentamos um exemplo a seguir. Observe o seguinte sobre este código.

Ele pressupõe que há uma tabela de duas colunas com as colunas Nome do Atleta e Comentários nas colunas A e B da planilha.
Para reduzir o nível de detalhamento na coluna Comentários, ela torna os dados que incluem o nome do atleta inválidos.
SEARCH(A2,B2) Retorna a posição inicial, na cadeia de caracteres em B2, da cadeia de caracteres em A2. Se A2 não estiver contida em B2, ela não retornará um número. ISNUMBER() retorna um booliano. Portanto, a propriedade formula diz que os dados válidos para a coluna Comentário são os dados que não incluem a cadeia de caracteres da coluna Nome do Atleta.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    let commentsRange = sheet.tables.getItem("AthletesTable").columns.getItem("Comments").getDataBodyRange();

    commentsRange.dataValidation.rule = {
            custom: {
                formula: "=NOT(ISNUMBER(SEARCH(A2,B2)))"
            }
        };

    await context.sync();
});

Criar alertas de erro de validação

Você pode criar um alerta de erro personalizado que aparece quando um usuário tenta inserir dados inválidos em uma célula. Apresentamos um exemplo simples a seguir. Observe o seguinte sobre este código.

A propriedade style determina se o usuário recebe um alerta informativo, um aviso e um alerta "parar". Apenas stop realmente impede que o usuário adicione dados inválidos. Os pop-ups para warning e information têm opções que permitem que o usuário insira os dados inválidos de qualquer maneira.
As propriedades showAlert padrão para true. Isso significa que o Excel fará um pop-up de um alerta genérico (do tipo stop) a menos que você crie um alerta personalizado que define showAlertfalse ou define uma mensagem, título e estilo personalizados. O código define uma mensagem personalizada e o título.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    let range = sheet.getRange("B2:C5");

    range.dataValidation.errorAlert = {
            message: "Sorry, only positive whole numbers are allowed",
            showAlert: true, // The default is 'true'.
              style: Excel.DataValidationAlertStyle.stop,
            title: "Negative or Decimal Number Entered"
        };

    // Set range.dataValidation.rule and optionally .prompt here.

    await context.sync();
});

Para saber mais, confira DataValidationErrorAlert.

Criar solicitações de validação

Você pode criar um prompt instrutivo que é exibido quando um usuário passa o mouse sobre ele ou seleciona uma célula à qual os dados de validação foram aplicados. Apresentamos um exemplo a seguir.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    let range = sheet.getRange("B2:C5");

    range.dataValidation.prompt = {
            message: "Please enter a positive whole number.",
            showPrompt: true, // The default is 'false'.
            title: "Positive Whole Numbers Only."
        };

    // Set range.dataValidation.rule and optionally .errorAlert here.

    await context.sync();
});

Para saber mais, confira DataValidationPrompt.

Remover validação de dados de um intervalo

Para remover a validação de dados de um intervalo, chame o método Range.dataValidation.clear().

myrange.dataValidation.clear()

Não é necessário que o intervalo que você desmarcar seja o mesmo intervalo de um intervalo no qual você adicionou a validação de dados. Caso contrário, apenas as células sobrepostas, se houver, dos dois intervalos são desmarcadas.