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

Verwenden Sie die Excel-JavaScript-API, um die Datenqualität zu erzwingen. Wenden Sie Regeln an, und verlassen Sie sich auf die Excel-Validierungsoberfläche für Eingabeaufforderungen und Fehlerwarnungen. In diesem Artikel wird gezeigt, wie Sie Regeltypen definieren, Eingabeaufforderungen und Fehlerwarnungen konfigurieren und die Überprüfung entfernen oder anpassen. Wenn Sie Hintergrundinformationen zur integrierten Überprüfungsbenutzeroberfläche von Excel benötigen, lesen Sie diese Artikel.

• Anwenden der Datenüberprüfung auf Zellen
• Weitere Informationen zur Datenüberprüfung
• Beschreibung und Beispiele für die Datenüberprüfung in Excel

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 Objekt verfügt über fünf Eigenschaften:

• 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 die Formatvorlage wie 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 Validierungsregeln

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.

Im folgenden Beispiel wird eine Validierungsregel erstellt. Wichtige Punkte:

• 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, dass nur ganze Zahlen größer als 0 gültig sind.
• Die formula1 ist eine hartcodierte Zahl. Wenn Sie zur Codierungszeit nicht wissen, was der Wert sein soll, können Sie auch eine Excel-Formel als Zeichenfolge verwenden (z. B. "=A3" oder "=SUMME(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();
});

Weitere binäre Operatoren finden Sie unter BasicDataValidation .

Es gibt auch zwei ternäre Operatoren: between und notBetween. Um diese zu verwenden, geben Sie die optionale formula2 Eigenschaft an. 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 Werte auf eine endliche Menge einzuschränken. Im folgenden Codebeispiel wird veranschaulicht. Wichtige Punkte:

• 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 auch eine durch Trennzeichen getrennte Liste zuweisen, z. B. "Sue, Ricky, Liz".
• Die inCellDropDown -Eigenschaft gibt an, ob ein Dropdown-Steuerelement in der Zelle angezeigt wird, wenn der Benutzer es auswählt. Gibt truean, wird die Dropdownliste 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, um eine benutzerdefinierte Validierungsformel anzugeben. Es folgt ein Beispiel. Wichtige Punkte:

• Es wird davon ausgegangen, dass eine zweispaltige Tabelle mit den Spalten Sportlername und Kommentare in den Spalten A und B des Arbeitsblatts vorhanden ist.
• Um die Ausführlichkeit in der Spalte Kommentare zu reduzieren, macht die Regel Daten, die den Namen des Athleten enthalten, ungültig.
• SEARCH(A2,B2) gibt die Anfangsposition in B2 der Zeichenfolge in A2 zurück. Wenn A2 nicht in B2 enthalten ist, wird keine Zahl zurückgegeben.
• ISNUMBER() gibt einen booleschen Wert zurück. formula Die -Eigenschaft gibt also an, dass gültige Daten für Comments Daten sind, die die Zeichenfolge Sportlername 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

Erstellen Sie eine Fehlerwarnung, um den Benutzer zu informieren, wenn ungültige Daten eingegeben werden. Im folgenden Beispiel wird eine einfache Warnung erstellt. Wichtige Punkte:

• 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

Erstellen Sie eine Eingabeaufforderung, die angezeigt wird, wenn der Benutzer die Zelle auswählt. In diesem Beispiel wird der Benutzer über die Überprüfung positiver Zahlen informiert, bevor er Daten eingibt.

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

Der Bereich, den Sie löschen, muss nicht genau mit dem Bereich übereinstimmen, für den Sie die Datenüberprüfung hinzugefügt haben. Wenn die beiden Bereiche keine genaue Übereinstimmung sind, werden nur überlappende Zellen gelöscht.

Hinweis

Indem Sie die Datenüberprüfung aus einem Bereich entfernen, werden auch alle Datenüberprüfungen, die der Benutzer manuell zu dem Bereich hinzugefügt hat, entfernt.

Nächste Schritte

• Kombinieren Sie Validierung mit Ereignissen: Ereignisse.
• Fügen Sie bedingte Formatierung für stärkere visuelle Hinweise hinzu.

Siehe auch

• JavaScript-Objektmodell für Excel in Office-Add-Ins
• DataValidation-Objekt (JavaScript-API für Excel)
• Range-Objekt (JavaScript-API für Excel)