Trabajo con varios intervalos de forma simultánea en los complementos de Excel

Artículo
04/07/2023

La biblioteca de JavaScript de Excel permite al complemento realizar operaciones y establecer propiedades en varios intervalos simultáneamente. Los intervalos no tienen que ser contiguos. Además de hacer que el código sea más sencillo, esta forma de configurar una propiedad es mucho más rápida que establecer la misma propiedad por separado para cada uno de los intervalos.

RangeAreas

Un conjunto de intervalos (posiblemente no contiguos) se representa mediante un objeto RangeAreas . Tiene métodos y propiedades similares al tipo Range (muchos con los mismos nombres o similares), pero se han realizado ajustes en:

Los tipos de datos de propiedades y comportamientos de los captadores y definidores.
Los tipos de datos de los parámetros de método y los comportamientos de método.
Los tipos de datos del método devuelven valores.

Por ejemplo:

RangeAreas tiene una propiedad address que devuelve una cadena delimitada por comas de direcciones de intervalos, en lugar de una sola dirección como con la propiedad Range.address.
RangeAreas tiene una propiedad dataValidation que devuelve un objeto DataValidation que representa la validación de datos de todos los intervalos de RangeAreas, si es coherente. La propiedad es null si no se aplican objetos DataValidation idénticos a todos los intervalos de RangeAreas. Este es un principio general, pero no universal, con el RangeAreas objeto : si una propiedad no tiene valores coherentes en todos los intervalos de RangeAreas, es null. Consulte Read properties of RangeAreas (Leer propiedades de RangeAreas ) para obtener más información y algunas excepciones.
RangeAreas.cellCount obtiene el número total de celdas de todos los intervalos de RangeAreas.
RangeAreas.calculate actualiza las celdas de todos los intervalos de RangeAreas.
RangeAreas.getEntireColumn y RangeAreas.getEntireRow devuelven otro objeto RangeAreas que representa todas las columnas (o filas) en todos los intervalos de RangeAreas. Por ejemplo, si RangeAreas representa "A1: C4" y "F14:L15", RangeAreas.getEntireColumn devuelve un objeto RangeAreas que representa "A:C" y "F:L".
RangeAreas.copyFrom puede tomar un Range parámetro o que RangeAreas represente los intervalos de origen de la operación de copia.

Lista completa de los miembros del intervalo que también están disponibles en RangeAreas

Propiedades

Familiarícese con Leer las propiedades de RangeAreas antes de escribir código que lea las propiedades de la lista. Hay matices con respecto a lo que se devuelve.

address
addressLocal
cellCount
conditionalFormats
context
dataValidation
format
isEntireColumn
isEntireRow
style
worksheet

Métodos

calculate()
clear()
convertDataTypeToText()
convertToLinkedDataType()
copyFrom()
getEntireColumn()
getEntireRow()
getIntersection()
getIntersectionOrNullObject()
getOffsetRange() (denominado getOffsetRangeAreas en el RangeAreas objeto )
getSpecialCells()
getSpecialCellsOrNullObject()
getTables()
getUsedRange() (denominado getUsedRangeAreas en el RangeAreas objeto )
getUsedRangeOrNullObject() (denominado getUsedRangeAreasOrNullObject en el RangeAreas objeto )
load()
set()
setDirty()
toJSON()
track()
untrack()

Métodos y propiedades específicos de RangeArea

El tipo RangeAreas tiene algunas propiedades y métodos que no están en el objeto Range. A continuación se muestra una selección de ellos.

areas: un objeto RangeCollection que contiene todos los intervalos representados por el objeto RangeAreas. El objeto RangeCollection también es nuevo y es similar a otros objetos de la colección de Excel. Tiene una propiedad items que es una matriz de objetos Range que representan los intervalos.
areaCount: el número total de eventos de intervalos de RangeAreas.
getOffsetRangeAreas: funciona igual que Range.getOffsetRange, excepto que se devuelve un RangeAreas y contiene intervalos que se desplazan de uno de los intervalos del RangeAreas original.

Crear RangeAreas

Puede crear un objeto RangeAreas de dos formas básicas:

Llame a Worksheet.getRanges() y pásele una cadena con direcciones de intervalo delimitadas por comas. Si cualquier intervalo que desee incluir se ha realizado en un NamedItem, puede incluir el nombre, en lugar de la dirección, en la cadena.
Llame a Workbook.getSelectedRanges(). Este método devuelve un RangeAreas que representa todos los intervalos seleccionados en la hoja de cálculo activa.

Cuando tenga un objeto RangeAreas, puede crear otros con los métodos en el objeto que devuelve RangeAreas como getOffsetRangeAreas y getIntersection.

Nota:

No puede agregar directamente intervalos adicionales a un objeto RangeAreas. Por ejemplo, la colección de RangeAreas.areas no tiene un método add.

Advertencia

No intente agregar o eliminar miembros de la matriz RangeAreas.areas.items directamente. Esto provocará un comportamiento no deseado en el código. Por ejemplo, es posible insertar un objeto Range adicional en la matriz, pero si lo hace, producirá errores porque los métodos y propiedades de RangeAreas se comportan como si el nuevo elemento no existiese. Por ejemplo, la propiedad areaCount no incluye intervalos insertados de esta forma y se produce un error de RangeAreas.getItemAt(index) si index es mayor que areasCount-1. De forma similar, al eliminar un objeto Range en la matriz RangeAreas.areas.items obteniendo una referencia a él y llamando a su método Range.delete se producen errores: aunque el objeto Rangese elimina, las propiedades y métodos del objeto RangeAreas primario se comportan, o intentan hacerlo, como si siguiese existiendo. Por ejemplo, si el código llama a RangeAreas.calculate, Office intentará calcular el intervalo, pero se producirá un error porque el objeto de intervalo ha desaparecido.

Establecer propiedades en varios rangos

Establecer una propiedad de un objeto RangeAreas establece la propiedad correspondiente en todos los intervalos de la colección de RangeAreas.areas.

Este es un ejemplo del establecimiento de una propiedad en varios intervalos. La función resalta los intervalos F3:F5 y H3:H5.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    let rangeAreas = sheet.getRanges("F3:F5, H3:H5");
    rangeAreas.format.fill.color = "pink";

    await context.sync();
});

Este ejemplo se aplica a los escenarios donde se pueden codificar las direcciones del intervalo que se pasan a getRanges o calcularlas fácilmente en tiempo de ejecución. Algunos de los escenarios en los que esto puede ocurrir son:

El código se ejecuta en el contexto de una plantilla conocida.
El código se ejecuta en el contexto de datos importados donde se conoce el esquema de los datos.

Obtener celdas especiales de varios rangos

Los métodos getSpecialCells y getSpecialCellsOrNullObject en el objeto RangeAreas funcionan de forma análoga a los métodos del mismo nombre en el objeto Range. Estos métodos devuelven las celdas con la característica especificada de todos los rangos de la colección RangeAreas.areas. Para obtener más detalles sobre las celdas especiales, vea Buscar celdas especiales dentro de un rango.

Cuando se llama al método getSpecialCells o getSpecialCellsOrNullObject en un objeto RangeAreas:

Si pasa Excel.SpecialCellType.sameConditionalFormat como primer parámetro, el método devuelve todas las celdas con el mismo formato condicional que la celda superior izquierda del primer rango en la colección RangeAreas.areas.
Si pasa Excel.SpecialCellType.sameDataValidation como primer parámetro, el método devuelve todas las celdas con la misma validación de datos que la celda superior izquierda del primer rango en la colección RangeAreas.areas.

Lectura de propiedades de RangeAreas

Es necesario prestar atención al leer los valores de propiedad de RangeAreas, ya que una propiedad determinada puede tener diferentes valores para diferentes intervalos de RangeAreas. La regla general es que, si un valor coherente puede devolverse, se devolverá. Por ejemplo, en el código siguiente, el código RGB para rosa (#FFC0CB) y true se registrará en la consola porque ambos rangos del RangeAreas objeto tienen un relleno rosa y ambos son columnas completas.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();

    // The ranges are the F column and the H column.
    let rangeAreas = sheet.getRanges("F:F, H:H");  
    rangeAreas.format.fill.color = "pink";

    rangeAreas.load("format/fill/color, isEntireColumn");
    await context.sync();

    console.log(rangeAreas.format.fill.color); // #FFC0CB
    console.log(rangeAreas.isEntireColumn); // true
});

Las cosas se vuelven más complicadas cuando no es posible mantener la coherencia. El comportamiento de las propiedades de RangeAreas sigue estos tres principios:

Una propiedad booleana de un objeto RangeAreas devuelve false a menos que la propiedad tenga un valor true para todos los intervalos del miembro.
Las propiedades no booleanas, con la excepción de la propiedad address, devuelven null a menos que la propiedad correspondiente en todos los intervalos de miembro tengan el mismo valor.
La propiedad address devuelve una cadena delimitada por comas de las direcciones de los intervalos del miembro.

Por ejemplo, el siguiente código crea un RangeAreas donde solo un intervalo es una columna completa y solo uno se rellena con color rosa. La consola mostrará null para el color de relleno, false para la propiedad isEntireRow y "Hoja1!F3:F5, Hoja1!H:H" (suponiendo que el nombre de la hoja es "Hoja1") para la propiedad address.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    let rangeAreas = sheet.getRanges("F3:F5, H:H");

    let pinkColumnRange = sheet.getRange("H:H");
    pinkColumnRange.format.fill.color = "pink";

    rangeAreas.load("format/fill/color, isEntireColumn, address");
    await context.sync();

    console.log(rangeAreas.format.fill.color); // null
    console.log(rangeAreas.isEntireColumn); // false
    console.log(rangeAreas.address); // "Sheet1!F3:F5, Sheet1!H:H"
});

Compartir a través de