Hinzufügen einer Datenüberprüfung zu Excel-Bereichen

Artikel
04/07/2023

Die Excel-JavaScript-Bibliothek enthält APIs, mit denen Sie es Ihrem Add-In ermöglichen können, automatisch eine Datenüberprüfung zu Tabellen, Spalten, Zeilen und anderen Bereichen in einer Arbeitsmappe hinzuzufügen. Informationen zu den Konzepten und der Terminologie der Datenüberprüfung finden Sie in den folgenden Artikeln darüber, wie Benutzer die Datenüberprüfung über die Excel-Benutzeroberfläche hinzufügen.

Programmgesteuerte Kontrolle der Datenüberprüfung

Die Range.dataValidation-Eigenschaft, die ein DataValidation-Objekt akzeptiert, bildet den Einstiegspunkt für die programmgesteuerte Kontrolle der Datenüberprüfung in Excel. Das DataValidation-Objekt weist fünf Eigenschaften auf:

rule – Definiert, was gültige Daten für den Bereich darstellt. Mehr dazu finden Sie unter DataValidationRule.
errorAlert – Gibt an, ob ein Fehler angezeigt wird, wenn der Benutzer ungültige Daten eingibt, und definiert den Warnungstext, den Titel und den Stil; Beispielsweise information, warningund stop. Mehr dazu finden Sie unter DataValidationErrorAlert.
prompt – Gibt an, ob eine Eingabeaufforderung angezeigt wird, wenn der Benutzer den Mauszeiger über den Bereich bewegt und die Eingabeaufforderungsmeldung definiert. Mehr dazu finden Sie unter DataValidationPrompt.
ignoreBlanks – Gibt an, ob die Datenüberprüfungsregel für leere Zellen im Bereich gilt. Standardwert ist true.
type – Eine schreibgeschützte Identifizierung des Validierungstyps, z. B. WholeNumber, Date, TextLength usw. Sie wird indirekt festgelegt, wenn Sie die rule -Eigenschaft festlegen.

Hinweis

Eine programmgesteuert hinzugefügte Datenüberprüfung verhält sich genauso wie eine manuell hinzugefügte Datenüberprüfung. Beachten Sie insbesondere, dass die Datenüberprüfung nur ausgelöst wird, wenn der Benutzer Daten direkt in eine Zelle eingibt oder eine Zelle von einer anderen Stelle kopiert, in die Arbeitsmappe einfügt und dabei die Einfügeoption Werte verwendet. Wenn der Benutzer eine Zelle kopiert und ein einfaches Einfügen in einen Bereich mit Datenüberprüfung ausführt, wird die Überprüfung nicht ausgelöst.

Erstellen von Überprüfungsregeln

Um einem Bereich Datenüberprüfung hinzuzufügen, muss Ihr Code die rule-Eigenschaft des DataValidation-Objekts in Range.dataValidation festlegen. Diese akzeptiert ein DataValidationRule-Objekt, das über sieben optionale Eigenschaften verfügt. In einem DataValidationRule-Objekt darf zu keiner Zeit mehr als eine dieser Eigenschaften vorhanden sein. Die Eigenschaft, die Sie einschließen, bestimmt den Typ der Überprüfung.

Basic- und DateTime-Überprüfungsregeltypen

Die ersten drei DataValidationRule-Eigenschaften (d. h. Überprüfungsregeltypen) akzeptieren als Wert ein BasicDataValidation-Objekt.

wholeNumber – Erfordert eine ganze Zahl zusätzlich zu jeder anderen Validierung, die BasicDataValidation vom -Objekt angegeben wird.
decimal – Erfordert eine Dezimalzahl zusätzlich zu jeder anderen Validierung, die BasicDataValidation vom -Objekt angegeben wird.
textLength – Wendet die Validierungsdetails im BasicDataValidation -Objekt auf die Länge des Zellwerts an.

Hier sehen Sie ein Beispiel zum Erstellen einer Überprüfungsregel. Beachten Sie die folgenden Aspekte in diesem Code.

ist operator der binäre Operator greaterThan. Wenn Sie einen binären Operator verwenden, ist der Wert, den der Benutzer in die Zelle einzugeben versucht, der linke Operand und der in formula1 angegebene Wert der rechte Operand. Diese Regel besagt also, dass nur ganze Zahlen größer als 0 gültig sind.
Die formula1 ist eine hartcodierte Zahl. Wenn Sie zum Zeitpunkt der Codeerstellung nicht wissen, welchen Wert sie aufweisen soll, können Sie für den Wert auch eine Excel-Formel (als Zeichenfolge) verwenden. Beispielsweise könnten "=A3" und "=SUMME(A4;B5)" ebenfalls Werte von formula1 sein.

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

Eine Liste der anderen binären Operatoren finden Sie unter BasicDataValidation.

Es gibt auch zwei ternäre Operatoren: between und notBetween. Wenn Sie diese verwenden möchten, müssen Sie die optionale Eigenschaft formula2 angeben. Die Werte von formula1 und formula2 sind die begrenzenden Operanden. Der Wert, den der Benutzer in die Zelle einzugeben versucht, ist der dritte (ausgewertete) Operand. Im folgenden Beispiel wird der Operator "Between" verwendet.

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

Die zwei nächsten Regeleigenschaften akzeptieren ein DateTimeDataValidation-Objekt als Wert.

date
time

Das DateTimeDataValidation-Objekt ist ähnlich strukturiert wie das BasicDataValidation-Objekt: es weist die Eigenschaften formula1, formula2 und operator auf und wird in der gleichen Weise verwendet. Der Unterschied besteht darin, dass in den Formeleigenschaften keine Zahl verwendet werden kann, jedoch können Sie eine ISO 8606-Datetime-Zeichenfolge (oder eine Excel-Formel) eingeben. Im folgenden Beispiel werden gültige Werte als Datumsangaben in der ersten Aprilwoche 2022 definiert.

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

Listenüberprüfungs-Regeltyp

Verwenden Sie die list-Eigenschaft im DataValidationRule-Objekt, um anzugeben, dass nur die Werte aus einer endlichen Liste gültige Werte darstellen. Es folgt ein Beispiel. Beachten Sie die folgenden Aspekte in diesem Code.

Es wird davon ausgegangen, dass ein Arbeitsblatt mit dem Namen "Names" vorhanden ist und dass es sich bei den Werten im Bereich "A1:A3" um Namen handelt.
Die source-Eigenschaft gibt die Liste der gültigen Werte an. Das Zeichenfolgenargument verweist auf einen Bereich, der die Namen enthält. Sie können ferner auch eine durch Trennzeichen getrennte Liste zuweisen, beispielsweise "Sue, Ricky, Liz".
Die inCellDropDown-Eigenschaft gibt an, ob ein Dropdown-Steuerelement in der Liste angezeigt wird, wenn der Benutzer sie auswählt. Wenn ihr Wert auf truefestgelegt ist, wird das Dropdown-Steuerelement mit der Liste der Werte aus sourceangezeigt.

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

Benutzerdefinierte Überprüfungsregeltypen

Verwenden Sie die custom-Eigenschaft im DataValidationRule-Objekt, um eine benutzerdefinierte Überprüfungsformel anzugeben. Es folgt ein Beispiel. Beachten Sie die folgenden Aspekte in diesem Code.

Es wird angenommen, dass eine zweispaltige Tabelle mit den Spalten Athlete Name (Name des Sportlers) und Comments (Kommentare) in den Spalten A und B des Arbeitsblatts vorhanden ist.
Um die Ausführlichkeit in der Spalte Comments gering zu halten, macht sie Daten, die den Namen des Sportlers enthalten, ungültig.
SEARCH(A2,B2) gibt die Anfangsposition der Zeichenfolge in A2 in der Zeichenfolge in B2 zurück. Wenn A2 nicht in B2 enthalten ist, wird keine Zahl zurückgegeben. ISNUMBER() gibt einen booleschen Wert zurück. Die formula-Eigenschaft besagt also, dass gültige Daten für die Spalte Comment Daten sind, die die Zeichenfolge in der Spalte Athlete Name nicht enthalten.

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

Erstellen von Warnungen bei Überprüfungsfehlern

Sie können eine benutzerdefinierte Fehlerwarnung erstellen, die angezeigt wird, wenn ein Benutzer versucht, ungültige Daten in eine Zelle einzugeben. Nachfolgend sehen Sie ein einfaches Beispiel. Beachten Sie die folgenden Aspekte in diesem Code.

Die style-Eigenschaft legt fest, ob dem Benutzer eine Informationsbenachrichtigung, eine Warnung oder eine "Stopp"-Benachrichtigung angezeigt wird. Nur stop hindert den Benutzer tatsächlich an der Eingabe ungültiger Daten. Die Popups für warning und information verfügen über Optionen, mit denen der Benutzer die ungültigen Daten trotzdem eingeben kann.
Die showAlert-Eigenschaft ist standardmäßig true. Dies bedeutet, dass Excel eine generische Warnung (vom Typ stop) öffnet, es sei denn, Sie erstellen eine benutzerdefinierte Warnung, die entweder auf false eine benutzerdefinierte Nachricht, einen Titel und eine benutzerdefinierte Formatvorlage festlegt showAlert oder festlegt. Dieser Code legt eine benutzerdefinierte Meldung und einen Titel fest.

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

Weitere Informationen finden Sie unter DataValidationErrorAlert.

Erstellen von Eingabeaufforderungen zur Überprüfung

Sie können eine Eingabeaufforderung mit einer Anweisung erstellen, die angezeigt wird, wenn ein Benutzer auf eine Zelle mit aktiver Datenüberprüfung zeigt oder sie auswählt. Es folgt ein Beispiel.

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

Weitere Informationen finden Sie unter DataValidationPrompt.

Entfernen der Datenüberprüfung aus einem Bereich

Um die Datenüberprüfung aus einem Bereich zu entfernen, rufen Sie die Range.dataValidation.clear()- Methode auf.

myrange.dataValidation.clear()

Es ist nicht erforderlich, dass der Bereich, aus dem Sie die Datenüberprüfung entfernen möchten, exakt mit dem Bereich übereinstimmt, dem Sie die Datenüberprüfung zuvor hinzugefügt haben. Wenn dies nicht der Fall ist, wird die Datenüberprüfung nur in den überlappenden Zellen der zwei Bereiche aufgehoben, falls überhaupt.