Agregar validación de datos en rangos de Excel

Use la API de JavaScript de Excel para aplicar la calidad de los datos. Aplique reglas y confíe en la interfaz de usuario de validación de Excel para obtener avisos y alertas de error. En este artículo se muestra cómo definir tipos de reglas, configurar avisos y alertas de error y quitar o ajustar la validación. Si necesita información general sobre la interfaz de usuario de validación integrada de Excel, revise estos artículos.

• Aplicar validación de datos a las celdas
• Más sobre la validación de datos
• Descripción y ejemplos de validación de datos en Excel

Control de programación de la validación de datos

La Range.dataValidation propiedad, que toma un objeto Validación de datos es el punto de entrada para el control programático de la validación de datos en Excel. El objeto tiene cinco propiedades:

• rule : define lo que constituye datos válidos para el intervalo. Consulte DataValidationRule.
• errorAlert : especifica si aparece un error si el usuario escribe datos no válidos y define el texto, el título y el estilo de la alerta, como information, warningy stop. Consulte DataValidationErrorAlert.
• prompt : especifica si aparece un mensaje cuando el usuario mantiene el puntero sobre el intervalo y define el mensaje de aviso. Consulte DataValidationPrompt.
• ignoreBlanks : especifica si la regla de validación de datos se aplica a las celdas en blanco del rango. Valores predeterminados de true.
• type : identificación de solo lectura del tipo de validación, como WholeNumber, Date, TextLength, etc. Se establece indirectamente cuando se establece la rule propiedad .

Nota:

La validación de datos agregada mediante programación se comporta como la agregada manualmente. En particular, ten en cuenta que la validación de datos se activa solo si el usuario introduce directamente un valor en una celda o copia y pega una celda de otro lugar en el libro de trabajo y elige la opción Valores pegar. Si el usuario copia una celda y realiza un simple pegado en un intervalo con validación de datos, la validación no se desencadena.

Creación de reglas de validación

Para agregar la validación de datos a un intervalo, su código debe configurar la propiedad rule del objeto DataValidation en Range.dataValidation. Esto toma un objeto DataValidationRule que tiene siete propiedades opcionales. No puede estar presente más de una de estas propiedades en cualquier objeto DataValidationRule. La propiedad que incluyas determinará el tipo de validación.

Tipos de reglas de validación básicas y de fecha y hora

Las primeros tres propiedades DataValidationRule (es decir, tipos de reglas de validación) toman un objeto BasicDataValidation como su valor.

• wholeNumber : requiere un número entero además de cualquier otra validación especificada por el BasicDataValidation objeto.
• decimal : requiere un número decimal además de cualquier otra validación especificada por el BasicDataValidation objeto.
• textLength : aplica los detalles de validación del BasicDataValidation objeto a la longitud del valor de la celda.

En el ejemplo siguiente se crea una regla de validación. Puntos clave:

• operator es el operador greaterThanbinario . Siempre que utilice un operador binario, el valor que el usuario intenta introducir en la celda es el operando de la izquierda y el valor especificado en formula1 es el operando de la derecha. Esta regla indica que solo los números enteros mayores que 0 son válidos.
• El formula1 es un número codificado de forma rígida. Si no sabe en tiempo de codificación cuál debe ser el valor, también puede usar una fórmula de Excel como una cadena (como "=A3" o "=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();
});

Vea BasicDataValidation para otros operadores binarios.

También hay dos operadores ternarios: between y notBetween. Para usarlos, especifique la propiedad opcional formula2 . Los valores formula1 y formula2 son los operandos del límite de la selección. El valor que el usuario intenta introducir en la celda es el tercer operando (evaluado). A continuación se muestra un ejemplo del uso del operador "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();
});

Las siguientes dos propiedades de reglas toman un objeto DateTimeDataValidation como su valor.

• date
• time

El objeto DateTimeDataValidation está estructurado de manera similar al BasicDataValidation: tiene las propiedades formula1, formula2 y operator y se usa de la misma manera. La diferencia es que no puede usar un número en las propiedades de la fórmula, pero puede introducir una cadena ISO 8606 datetime (o una fórmula de Excel). A continuación se muestra un ejemplo que define valores válidos como fechas de la primera 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();
});

Tipo de regla de validación de lista

Utilice la list propiedad del DataValidationRule objeto para restringir los valores a un conjunto finito. En el ejemplo de código siguiente se muestra. Puntos clave:

• Supón que hay una hoja de trabajo llamada "Nombres" y que los valores en el rango "A1: A3" son nombres.
• La propiedad source especifica la lista de valores válidos. El argumento cadena hace referencia a un rango que contiene los nombres. También puede asignar una lista delimitada por comas, como "Sue, Ricky, Liz".
• La inCellDropDown propiedad especifica si aparece un control desplegable en la celda cuando el usuario lo selecciona. Si truees , la lista desplegable aparece con la 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 regla de validación personalizada

Use la custom propiedad para especificar una fórmula de validación personalizada. A continuación se muestra un ejemplo. Puntos clave:

• Se supone que hay una tabla de dos columnas con las columnas Nombre del atleta y Comentarios en las columnas A y B de la hoja de cálculo.
• Para reducir la verbosidad en la columna Comentarios, la regla hace que los datos que incluyen el nombre del atleta no sean válidos .
• SEARCH(A2,B2) devuelve la posición inicial en B2 de la cadena en A2. Si A2 no está incluido en B2, no devuelve un número.
• ISNUMBER() devuelve un valor booleano. Por lo tanto, la formula propiedad indica que los datos válidos para Comments son datos que no incluyen la cadena Nombre del 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();
});

Crear alertas de error de validación

Cree una alerta de error para guiar al usuario cuando se escriban datos no válidos. En el ejemplo siguiente se crea una alerta básica. Puntos clave:

• La propiedad style determina si el usuario recibe una alerta informativa, una advertencia o una alerta de "detención". En realidad, solo stop impide que el usuario agregue datos no válidos. Los elementos emergentes de warning y information tienen opciones que permiten al usuario escribir los datos no válidos de todos modos.
• La propiedad showAlert toma como valor predeterminado a true. Esto significa que Excel mostrará una alerta genérica (de tipo stop) a menos que cree una alerta personalizada que establezca showAlertfalse o establezca un mensaje, un título y un estilo personalizados. Este código establece un mensaje personalizado y un 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 obtener más información, vea DataValidationErrorAlert.

Crear pedidos de confirmación de la validación

Cree un símbolo del sistema de instrucciones que aparezca cuando el usuario seleccione la celda. En este ejemplo se indica al usuario sobre la validación de números positivos antes de escribir datos.

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 obtener más información, vea DataValidationPrompt.

Quitar la validación de datos de un intervalo

Para quitar la validación de datos de un intervalo, llame al método Range.dataValidation.clear().

myrange.dataValidation.clear();

El intervalo que borre no tiene que coincidir exactamente con el intervalo en el que agregó la validación de datos. Si los dos rangos no coinciden exactamente, solo se borran las celdas superpuestas.

Nota:

Desactivar la validación de datos de un intervalo también desactivará cualquier validación que un usuario haya agregado manualmente al intervalo.

Pasos siguientes

• Combinar la validación con eventos: Eventos.
• Agregue formato condicional para obtener indicaciones visuales más seguras.

Vea también

• Modelo de objetos de JavaScript en Excel para Complementos de Office
• Objeto DataValidation (JavaScript API para Excel)
• Objeto Range (API de JavaScript para Excel)