Partager via

Ajout de validation des données à des plages Excel

Utilisez l’API JavaScript Excel pour appliquer la qualité des données. Appliquez des règles et appuyez-vous sur l’interface utilisateur de validation d’Excel pour les invites et les alertes d’erreur. Cet article explique comment définir des types de règles, configurer des invites et des alertes d’erreur, et supprimer ou ajuster la validation. Si vous avez besoin d’informations sur l’interface utilisateur de validation intégrée d’Excel, consultez ces articles.

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. L’objet a cinq propriétés :

  • 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, tels que 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, par exemple 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 des 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.

L’exemple suivant crée une règle de validation. Points clés :

  • 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. Cette règle indique que seuls les nombres entiers supérieurs à 0 sont valides.
  • Le formula1 est un nombre codé en dur. Si vous ne savez pas au moment du codage quelle doit être la valeur, vous pouvez également utiliser une formule Excel en tant que chaîne (telle que « =A3 » ou « =SUM(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();
});

Consultez BasicDataValidation pour d’autres opérateurs binaires.

Il existe également deux opérateurs ternaires : between et notBetween. Pour les utiliser, spécifiez la propriété facultative formula2 . 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 list propriété dans l’objet DataValidationRule pour limiter les valeurs à un jeu fini. L’exemple de code suivant illustre. Points clés :

  • 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 telle que « Sue, Ricky, Liz ».
  • La inCellDropDown propriété spécifie si un contrôle déroulant apparaît dans la cellule lorsque l’utilisateur le sélectionne. Si la valeur est true, la liste 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 custom propriété pour spécifier une formule de validation personnalisée. Voici un exemple. Points clés :

  • Il suppose qu’il existe une table à deux colonnes avec les colonnes Nom de l’athlète et Commentaires dans les colonnes A et B de la feuille de calcul.
  • Pour réduire le détail dans la colonne Commentaires , la règle rend les données qui incluent le nom de l’athlète non valides.
  • SEARCH(A2,B2) retourne la position de départ dans B2 de la chaîne dans A2. Si A2 n’est pas contenu dans B2, il ne retourne pas de nombre.
  • ISNUMBER() renvoie une valeur booléenne. La propriété indique donc que les formula données valides pour Comments sont des données qui n’incluent pas la chaîne Nom de l’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

Créez une alerte d’erreur pour guider l’utilisateur quand des données non valides sont entrées. L’exemple suivant crée une alerte de base. Points clés :

  • 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

Créez une invite d’instructions qui s’affiche lorsque l’utilisateur sélectionne la cellule. Cet exemple indique à l’utilisateur la validation du nombre positif avant d’entrer des données.

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 effacez n’a pas besoin de correspondre précisément à la plage à laquelle vous avez ajouté la validation des données. Si les deux plages ne correspondent pas exactement, seules les cellules qui se chevauchent sont effacées.

Remarque

La désactivation de la validation des données à partir d’une plage efface également une validation des données qu’un utilisateur a ajoutée manuellement à la plage.

Étapes suivantes

Voir aussi

  • Last updated on