Adicionar validação de dados para intervalos do Excel

Utilize a API JavaScript do Excel para impor a qualidade dos dados. Aplique regras e confie na IU de validação do Excel para pedidos e alertas de erro. Este artigo mostra como definir tipos de regras, configurar pedidos e alertas de erros e remover ou ajustar a validação. Se precisar de informações sobre a IU de validação incorporada do Excel, veja 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 é apresentado um erro se o utilizador introduzir dados inválidos e definir o texto, título e estilo do alerta, como information, warninge stop. Ver DataValidationErrorAlert.
• prompt — Especifica se é apresentado um pedido quando o utilizador paira o cursor sobre o intervalo e define a mensagem de aviso. 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 só de leitura do tipo de validação, como WholeNumber, Date, TextLength, etc. É definido indiretamente quando 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 para além de qualquer outra validação especificada pelo BasicDataValidation objeto.
• decimal — Requer um número decimal para 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.

O exemplo seguinte cria uma regra de validação. Pontos-chave:

• 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. Esta regra diz que apenas os números inteiros superiores a 0 são válidos.
• O formula1 é um número embutido. Se não souber no momento da codificação qual deve ser o valor, também pode utilizar uma fórmula do Excel como uma cadeia (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();
});

Veja BasicDataValidation para outros operadores binários.

Existem também dois operadores ternários: between e notBetween. Para utilizá-los, 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). Segue-se um exemplo de utilização 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). Segue-se 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

Utilize a list propriedade no DataValidationRule objeto para restringir valores a um conjunto finito. O seguinte exemplo de código demonstra. Pontos-chave:

• 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. Também pode atribuir uma lista delimitada por vírgulas, como "Sue, Ricky, Liz".
• A inCellDropDown propriedade especifica se é apresentado um controlo pendente na célula quando o utilizador o seleciona. Se true, o menu pendente é apresentado com a lista de valores de 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

Utilize a custom propriedade para especificar uma fórmula de validação personalizada. Apresentamos um exemplo a seguir. Pontos-chave:

• Pressupõe que existe uma tabela de duas colunas com colunas Nome do Atleta e Comentários nas colunas A e B da folha de cálculo.
• Para reduzir a verbosidade na coluna Comentários , a regra torna inválidos os dados que incluem o nome do atleta.
• SEARCH(A2,B2) devolve a posição inicial em B2 da cadeia em A2. Se A2 não estiver contido no B2, não devolverá um número.
• ISNUMBER() retorna um booliano. Assim, a formula propriedade diz que os dados válidos para Comentários são dados que não incluem a cadeia 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

Crie um alerta de erro para orientar o utilizador quando forem introduzidos dados inválidos. O exemplo seguinte cria um alerta básico. Pontos-chave:

• 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 utilizador introduzir os dados inválidos.
• As propriedades showAlert padrão para true. Isto significa que o Excel irá criar um alerta genérico (do tipo stop) a menos que crie um alerta personalizado que defina ou defina showAlertfalse 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 pedido informativo que é apresentado quando o utilizador seleciona a célula. Este exemplo informa o utilizador sobre a validação de número positivo antes de introduzir 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 limpar não tem de corresponder com precisão ao intervalo no qual adicionou a validação de dados. Se os dois intervalos não forem uma correspondência exata, apenas as células sobrepostas são limpas.

Observação

Limpar a validação de dados de um intervalo também limpará qualquer validação de dados que o usuário tenha adicionado manualmente ao intervalo.

Próximas etapas

• Combinar validação com eventos: Eventos.
• Adicione formatação condicional para ajudas visuais mais fortes.

Confira também

• Modelo de objeto JavaScript do Excel em Suplementos do Office
• Objeto Application (JavaScript API para Excel)
• Objeto Range (API JavaScript para Excel)