Adicionar validação de dados para intervalos do Excel

Use a API JavaScript do Excel para impor a qualidade dos dados. Aplique regras e confie na interface do usuário de validação do Excel para prompts e alertas de erro. Este artigo mostra como definir tipos de regra, configurar prompts e alertas de erro e remover ou ajustar a validação. Se você precisar de informações sobre a interface do usuário de validação interna do Excel, examine estes artigos.

• Aplicar validação de dados às células
• Validação de dados
• Exemplos e descrição de validação de dados no 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. O objeto tem cinco propriedades:

• rule — Define o que constitui dados válidos para o intervalo. Ver DataValidationRule.
• errorAlert — Especifica se um erro será exibido se o usuário inserir dados inválidos e define o texto, o título e o estilo do alerta, como information, warninge stop. Ver DataValidationErrorAlert.
• prompt — Especifica se um prompt aparece quando o usuário passa o mouse sobre o intervalo e define a mensagem do 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. Ela é definida indiretamente quando você define a rule propriedade.

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 BasicDataValidation qualquer outra validação especificada pelo objeto.
• decimal — Requer um número decimal além de BasicDataValidation qualquer outra validação especificada pelo objeto.
• textLength — Aplica os detalhes de validação no BasicDataValidation objeto ao comprimento do valor da célula.

O exemplo a seguir cria uma regra de validação. Pontos principais:

• 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. Essa regra diz que somente números inteiros maiores que 0 são válidos.
• O formula1 é um número embutido. Se, no momento da codificação, você não souber qual deve ser o valor, também poderá usar uma fórmula do Excel como uma cadeia de caracteres (como "=A3" ou "=SOMA(A4,B5)").

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

Consulte BasicDataValidation para outros operadores binários.

Há também dois operadores ternários: between e notBetween. Para usá-las, especifique a propriedade opcional formula2 . Os valoresformula1 e formula2 são os operandos delimitadores. O valor que o usuário tenta inserir na célula é o terceiro operando (calculado). Veja a seguir um exemplo de uso do 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). 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 list propriedade no objeto para restringir os DataValidationRule valores a um conjunto finito. O exemplo de código a seguir demonstra. Pontos principais:

• 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írgulas, como "Sue, Ricky, Liz".
• A inCellDropDown propriedade especifica se um controle suspenso aparece na célula quando o usuário o seleciona. Se true, o menu suspenso será exibido 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 custom propriedade para especificar uma fórmula de validação personalizada. Apresentamos um exemplo a seguir. Pontos principais:

• 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 detalhamento na coluna Comentários , a regra torna inválidos os dados que incluem o nome do atleta.
• SEARCH(A2,B2) retorna a posição inicial em B2 da cadeia de caracteres em A2. Se A2 não estiver contido em B2, ele não retornará um número.
• ISNUMBER() retorna um booliano. Portanto, a formula propriedade diz que dados válidos para Comentários são dados que não incluem a cadeia de caracteres 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

Criar um alerta de erro para orientar o usuário quando dados inválidos forem inseridos. O exemplo a seguir cria um alerta básico. Pontos principais:

• 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 ao usuário inserir os dados inválidos de qualquer maneira.
• As propriedades showAlert padrão para true. Isso significa que o Excel exibirá um alerta genérico (do tipo stop), a menos que você crie um alerta personalizado que defina showAlert ou false defina 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

Crie um prompt de instrução que seja exibido quando o usuário selecionar a célula. Este exemplo informa ao usuário sobre a validação do número positivo antes de inserir dados.

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

O intervalo que você limpar não precisa corresponder precisamente ao intervalo no qual você adicionou a validação de dados. Se os dois intervalos não forem uma correspondência exata, somente as células sobrepostas serão limpas.

Próximas etapas

• Combine validação com eventos: Eventos.
• Adicione formatação condicional para indicações visuais mais fortes.

Confira também

• Principais conceitos de modelo de objeto do Excel para Suplementos do Office
• Objeto Application (API de JavaScript para Excel)
• Objeto Range (API JavaScript para Excel)