Ajout de validation des données à des plages Excel

Article
04/07/2023

La bibliothèque JavaScript Excel fournit des API pour autoriser votre complément à ajouter la validation automatique des données aux tableaux, colonnes, lignes et autres plages dans un classeur. Pour comprendre les concepts et la terminologie de la validation des données, consultez les articles suivants sur la façon dont les utilisateurs ajoutent la validation des données via l’interface utilisateur Excel.

Contrôle par programme de validation des données

La propriété Range.dataValidation, qui récupère un objet DataValidation, constitue le point d’entrée pour le contrôle par programmation de la validation des données dans Excel. Il existe cinq propriétés pour l’objet DataValidation :

rule — Définit ce qui constitue des données valides pour la plage. Voir DataValidationRule.
errorAlert : spécifie si une erreur s’affiche si l’utilisateur entre des données non valides et définit le texte, le titre et le style de l’alerte. par exemple, information, warninget stop. Voir DataValidationErrorAlert.
prompt : spécifie si une invite s’affiche lorsque l’utilisateur pointe sur la plage et définit le message d’invite. Voir DataValidationPrompt.
ignoreBlanks : spécifie si la règle de validation des données s’applique aux cellules vides de la plage. La valeur par défaut est true.
type — Identification en lecture seule du type de validation, telle que WholeNumber, Date, TextLength, etc. Elle est définie indirectement lorsque vous définissez la rule propriété .

Remarque

La validation des données ajoutées par programme se comporte comme celle ajoutée manuellement. Notez que la validation des données est déclenchée uniquement si l’utilisateur entre une valeur dans une cellule ou copie directement et colle une cellule à partir d’un autre emplacement dans le classeur en choisissant l’option de collage valeurs. Si l’utilisateur copie une cellule et effectue un simple coller dans une plage avec validation des données, la validation n’est pas déclenchée.

Créer les règles de validation

Pour ajouter la validation des données à une plage, votre code doit définir la propriété rule de l’objet DataValidation dans Range.dataValidation. Cela saisit un objet DataValidationRule contenant les sept propriétés facultatives. Une seule de ces propriétés peut être présente dans un objet DataValidationRule. La propriété que vous incluez détermine le type de validation.

Règles de validation Basic et DateTime

Les trois premières propriétés DataValidationRule (c'est-à-dire les types de règles de validation) prennent un objet BasicDataValidation comme valeur.

wholeNumber — Nécessite un nombre entier en plus de toute autre validation spécifiée par l’objet BasicDataValidation .
decimal — Nécessite un nombre décimal en plus de toute autre validation spécifiée par l’objet BasicDataValidation .
textLength — Applique les détails de validation de l’objet BasicDataValidation à la longueur de la valeur de la cellule.

Voici un exemple de création d’une règle de validation. Notez ce qui suit à propos de ce code.

est operator l’opérateur greaterThanbinaire . Chaque fois que vous utilisez un opérateur binaire, la valeur que l’utilisateur essaie d’entrer dans la cellule est l’opérande gauche et la valeur spécifiée dans formula1 est l’opérande droite. Par conséquent cette règle indique qu’uniquement les nombres entiers supérieurs à 0 sont valides.
Le formula1 est un nombre codé en dur. Lors de la création du code, si vous ne savez pas quelle valeur indiquer, vous pouvez également utiliser une formule Excel (comme chaîne) pour la valeur. Par exemple, « = A3 » et « = SUM(A4,B5) » peuvent également être des valeurs de 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();
});

Voir BasicDataValidation pour obtenir la liste des autres opérateurs binaires.

Il existe également deux opérateurs ternaires : between et notBetween. Pour les utiliser, vous devez spécifier la propriété formula2 facultative. Les valeursformula1 et formula2 sont les opérandes englobantes. La valeur que l’utilisateur essaie d’entrer dans la cellule est la troisième opérande (évaluée). Voici un exemple d’utilisation de l’opérateur « Between ».

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

Les deux propriétés de règle suivantes prennent un objetDateTimeDataValidation comme valeur.

date
time

La structure de l’objet DateTimeDataValidation est similaire à celle de BasicDataValidation : ce dernier a les propriétés formula1, formula2, et operator. Il est aussi utilisé de la même façon. La différence est que vous ne pouvez pas utiliser un nombre dans les propriétés de formule, mais vous pouvez entrer une chaîne 8606 ISO datetime (ou une formule Excel). Voici un exemple qui définit des valeurs valides comme des dates de la première semaine d’avril 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();
});

Type de règle de validation de liste

Utilisez la propriété list dans l’objet DataValidationRule pour spécifier que les seules valeurs valides sont celles d’une liste de remise. Voici un exemple. Notez ce qui suit à propos de ce code.

Il part du principe qu’il existe une feuille de calcul nommée « Noms » et que les valeurs dans la plage « A1:A3 » sont des noms.
La propriété source indique la liste des valeurs valides. L’argument de chaîne fait référence à une plage de cellules contenant les noms. Vous pouvez également affecter une liste délimitée par des virgules ; par exemple : « Sue, Ricky, Florence ».
La propriété inCellDropDown indique si un contrôle de liste déroulante s’affiche dans la cellule lorsque l’utilisateur la sélectionne. Si elle est définie sur true, alors la flèche déroulante s’affiche avec la liste des valeurs 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();
})

Type de règle de validation personnalisée

Utilisez la propriété custom dans l’objet DataValidationRule pour spécifier une formule de validation personnalisée. Voici un exemple. Notez ce qui suit à propos de ce code.

Il part du principe qu’il existe un tableau de deux colonnes avec des colonnes nom athlète et commentaires dans les colonnes A et B de la feuille de calcul.
Pour réduire le niveau de détail dans la colonnecommentaires, il rend les données qui incluent le nom de l’athlète invalides.
SEARCH(A2,B2) renvoie la position de départ, dans la chaîne dans B2, de la chaîne dans A2. Si A2 n’est pas contenue dans B2, elle ne renvoie pas de nombre. ISNUMBER() renvoie une valeur booléenne. La propriété formula indique que les données valides pour la colonne commentaires sont des données qui n’incluent pas la chaîne dans la colonne nom athlète.

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

Créer des alertes d’erreur de validation

Vous pouvez créer une alerte d’erreur personnalisée qui s’affiche lorsqu’un utilisateur tente d’entrer des données non valides dans une cellule. Voici un exemple simple. Notez ce qui suit à propos de ce code.

La propriété style détermine si l’utilisateur reçoit une alerte d’information, un avertissement ou une alerte « Stop ». Seule l'alerte stop empêche l’utilisateur d’ajouter des données non valides. Les fenêtres contextuelles pour warning et information ont des options qui permettent à l’utilisateur d’entrer les données non valides de toute façon.
La propriété showAlert est définie par défaut sur true. Cela signifie qu’Excel affiche une alerte générique (de type stop), sauf si vous créez une alerte personnalisée qui définit ou définit showAlertfalse un message, un titre et un style personnalisés. Ce code définit un message et un titre personnalisés.

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

Pour plus d’informations, voir DataValidationErrorAlert.

Créer des demandes de validation

Vous pouvez créer une invite de commandes instructive qui s’affiche lorsqu’un utilisateur survole ou sélectionne une cellule à laquelle la validation des données a été appliquée. Voici un exemple.

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

Pour plus d’informations, voir DataValidationPrompt.

Supprimer la validation des données d’une plage

Pour supprimer la validation des données d’une plage, appelez la méthode Range.dataValidation.clear().

myrange.dataValidation.clear()

La plage que vous désactivez ne sera pas nécessairement exactement la même plage qu’une plage dans laquelle vous avez ajouté la validation des données. Si ce n’est pas le cas, uniquement les cellules des deux plages qui se chevauchent, le cas échéant, sont effacées.