Agregar validación de datos en rangos de Excel

Artículo
04/07/2023

La Biblioteca de JavaScript de JavaScript proporciona varias API para habilitar tu complemento para agregar validación automática de datos a tablas, columnas, filas y otros rangos en un libro de trabajo. Para comprender los conceptos y la terminología de la validación de datos, consulte los siguientes artículos sobre cómo los usuarios agregan la validación de datos a través de la interfaz de usuario de 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. Hay cinco propiedades para el objeto DataValidation:

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; por ejemplo, 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.

Crear 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.

Aquí hay un ejemplo de creación de una regla de validación. Tenga en cuenta lo siguiente sobre este código.

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. Así que esta regla dice que solo los números enteros que son mayores que 0 son válidos.
El formula1 es un número codificado de forma rígida. Si no sabes en el momento de la codificación cuál debe ser el valor, también puedes usar una fórmula de Excel (como una cadena) para el valor. Por ejemplo, "=A3" y "=SUMA (A4,B5)" también podrían ser valores 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();
});

Consulte BasicDataValidation para obtener una lista de los otros operadores binarios.

También hay dos operadores ternarios: between y notBetween. Para usar estos, debe especificar 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 los 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 propiedad list en el objeto DataValidationRule para especificar que los únicos valores válidos son los de una lista finita. Esto es un ejemplo. Tenga en cuenta lo siguiente sobre este código.

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; por ejemplo: "Susana, Ricky, Liz".
La propiedad inCellDropDown especifica si aparecerá un control desplegable en la celda cuando el usuario la seleccione. Si está configurado para true, aparece el menú desplegable con la lista de valores del 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

Utilice la propiedad custom en el objeto DataValidationRule para especificar una fórmula de validación personalizada. Esto es un ejemplo. Tenga en cuenta lo siguiente sobre este código.

Asume que hay una tabla de dos columnas con columnas Nombre del atleta y Comentarios en las columnas A y B de la hoja de trabajo.
Para reducir la verbosidad en la columna Comentarios hace que los datos que incluyen el nombre del atleta no sean válidos.
SEARCH(A2,B2) devuelve la posición de inicio, en cadena en B2, de la cadena en A2. Si A2 no está contenido en B2, no devuelve un número. ISNUMBER() devuelve un valor booleano. Entonces la propiedad formula dice que los datos válidos para la columna Comentario son datos que no incluyen la cadena en la columna 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

Puede crear una alerta de error personalizada que aparezca cuando un usuario intente introducir datos no válidos en una celda. A continuación puede ver un ejemplo simple. Tenga en cuenta lo siguiente sobre este código.

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

Puedes crear un mensaje de instrucción que aparece cuando un usuario se desplaza sobre una celda o la selecciona, a la cual se le ha aplicado la validación de datos. A continuación se muestra un ejemplo.

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

No es necesario que el intervalo que desactive sea exactamente el mismo intervalo que el intervalo en que agregó la validación de datos. Si no es así, solo se borran las celdas superpuestas, si las hay, de los dos rangos.