Trabalhar simultaneamente com vários intervalos em suplementos do Excel

Artigo
04/07/2023

A biblioteca de JavaScript do Excel permite que o suplemento realize operações e defina propriedades, em vários intervalos simultaneamente. Os intervalos não precisam ser contíguos. Além de tornar seu código mais simples, essa maneira de definir uma propriedade é executada muito mais rapidamente do que definir a mesma propriedade individualmente para cada um dos intervalos.

RangeAreas

Um conjunto de intervalos (possivelmente descontínuos) é representado por um objeto RangeAreas . Possui propriedades e métodos semelhantes ao tipo Range (muitos com os mesmos nomes ou semelhantes), mas foram feitos ajustes para:

Os tipos de dados para propriedades e o comportamento dos setters e getters.
Os tipos de dados dos parâmetros do método e os comportamentos do método.
Os tipos de dados de forma retornam valores.

Alguns exemplos:

RangeAreas tem uma propriedade address que retorna uma cadeia de caracteres delimitada por vírgula de intervalo de endereços, em vez de apenas um endereço como na propriedadeRange.address.
RangeAreas tem uma propriedade dataValidation que retorna um objeto DataValidation que representa a validação de dados de todos os intervalos emRangeAreas, se for consistente. A propriedade é null se objetos idênticos DataValidation não forem aplicados a todos os intervalos em RangeAreas. Este é um princípio geral, mas não universal, com o RangeAreas objeto : se uma propriedade não tiver valores consistentes em todos os intervalos no RangeAreas, então é null. Veja Ler propriedades de RangeAreas para obter mais informações e algumas exceções.
RangeAreas.cellCount é o número total de células em todos os intervalos no RangeAreas.
RangeAreas.calculate recalcula as células de todos os intervalos no RangeAreas.
RangeAreas.getEntireColumn e RangeAreas.getEntireRow retornar outra RangeAreas objeto que representa todas as colunas (ou linhas) em todos os intervalos no RangeAreas. Por exemplo, se RangeAreas representa "A1: C4" e "F14:L15" em seguida, RangeAreas.getEntireColumn retorna um objeto RangeAreas que representa "A:C" e "F:L".
RangeAreas.copyFrom pode utilizar um Range parâmetro ou um RangeAreas que represente os intervalos de origem da operação de cópia.

Lista completa de membros do intervalo que também estão disponíveis em RangeAreas

Propriedades

Familiarize-se com as Propriedades de leitura do RangeAreas antes de escrever o código que lê as propriedades listadas. Existem sutilezas para o que é retornado.

address
addressLocal
cellCount
conditionalFormats
context
dataValidation
format
isEntireColumn
isEntireRow
style
worksheet

Métodos

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

Métodos e propriedades específicos do RangeArea

O tipo RangeAreas tem alguns métodos e propriedades que não estão no objeto Range. Segue-se uma seleção das mesmas.

areas: O objeto RangeCollection que contém todos os intervalos representados pelo objeto RangeAreas. O objeto RangeCollection também é novidade e é semelhante a outros objetos do conjunto do Excel. É uma propriedade items que é uma matriz de objetos Range que representam os intervalos.
areaCount: O número total de intervalos em RangeAreas.
getOffsetRangeAreas: Funciona como Range.getOffsetRange, exceto pelo fato de que o RangeAreas é retornado e contém os intervalos que são todos os deslocamentos de um dos intervalos do RangeAreas original.

Criar RangeAreas

Você pode criar o objetoRangeAreas de duas maneiras básicas:

Ligue Worksheet.getRanges() e encaminhe-o em uma cadeia de caracteres com endereços de intervalo separado por vírgula. Se algum intervalo que você deseja incluir tiver sido feito em um NamedItem, você poderá incluir o nome, em vez do endereço, cadeia de caracteres.
Chamar Workbook.getSelectedRanges(). Esse método retornará um RangeAreas representando todos os intervalos selecionados na planilha ativa no momento.

Quando você tiver um objeto RangeAreas, você pode criar outros usando os métodos de objeto que retornam RangeAreas como getOffsetRangeAreas e getIntersection.

Observação

É possível adicionar diretamente intervalos adicionais para um objeto RangeAreas. Por exemplo, o conjunto RangeAreas.areas não tem um métodoadd.

Aviso

Tente adicionar ou excluir membros diretamente à matrizRangeAreas.areas.items. Isso levará a um comportamento indesejável no seu código. Por exemplo, é possível enviar um objeto adicional Range para a matriz, mas isso causará erros porque as propriedades e métodos RangeAreas se comportam como se o novo item não estivesse ali. Por exemplo, a propriedade areaCount não inclui intervalos transferidos dessa maneira e o RangeAreas.getItemAt(index) gera um erro se index for maior que areasCount-1. Da mesma forma, excluir um objeto Range na matriz RangeAreas.areas.items obtendo uma referência a ele e chamando seu método Range.delete causa bugs: embora o Rangeobjeto seja excluído, as propriedades e métodos do objeto pai RangeAreas se comportam ou tentam se comportar, como se ele ainda existisse. Por exemplo, se o seu código chamar RangeAreas.calculate, o Office tentará calcular o intervalo, mas haverá erro porque o objeto de intervalo desapareceu.

Definir as propriedades em vários intervalos

Definir uma propriedade em um RangeAreas objeto define a propriedade correspondente em todos os intervalos no conjunto RangeAreas.areas.

A seguir, um exemplo de configuração de uma propriedade em vários intervalos. A função realça os intervalos F3:F5 e 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 exemplo se aplica a cenários nos quais você pode codificar os endereços de intervalo para os quais você passa para getRanges ou facilmente calculá-los no tempo de execução. Alguns dos cenários em que isso pode ser verdadeiro incluem:

O código é executado no contexto de um modelo conhecido.
O código é executado no contexto de dados importados, em que o esquema dos dados é conhecido.

Obter células especiais de vários intervalos

As getSpecialCells e getSpecialCellsOrNullObject métodos no RangeAreas objeto funciona analogamente para métodos de mesmo nome no Range objeto. Esses métodos retornam as células com característica especificada de todos os intervalos no RangeAreas.areas conjunto. Para obter mais detalhes sobre células especiais, consulte Localizar células especiais dentro de um intervalo.

Ao chamar as getSpecialCells ou getSpecialCellsOrNullObject método em um RangeAreas objeto:

Se você passar Excel.SpecialCellType.sameConditionalFormat como o primeiro parâmetro, o método retorna todas as células com a mesma formatação condicional que a célula superior esquerda do primeiro intervalo no RangeAreas.areas conjunto.
Se você passar Excel.SpecialCellType.sameDataValidation como o primeiro parâmetro, o método retorna todas as células com a regra de validação de dados que a célula superior esquerda do primeiro intervalo no RangeAreas.areas conjunto.

Ler propriedades de RangeAreas

A leitura de valores de propriedade RangeAreas requer cuidados, porque uma determinada propriedade pode ter valores diferentes para intervalos diferentes dentro deRangeAreas. A regra geral é que, se um valor consistente puder ser retornado, ele será retornado. Por exemplo, no código seguinte, o código RGB para cor-de-rosa (#FFC0CB) e true será registado na consola porque ambos os intervalos no RangeAreas objeto têm um preenchimento cor-de-rosa e ambos são colunas inteiras.

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

As coisas ficam mais complicadas quando a consistência não é possível. O comportamento das propriedades RangeAreas seguem estes três princípios de três:

Uma propriedade booliana de um RangeAreasretorno de objeto false, a menos que a propriedade seja verdadeira para todos os intervalos de membro.
Propriedades não boolianas, com exceção da propriedade address, retornam null, a menos que a propriedade correspondente em todos os intervalos de membros tenha o mesmo valor.
A propriedade address retorna uma cadeia de caracteres delimitada por vírgulas dos endereços e intervalos dos membros.

Por exemplo, o código a seguir cria um RangeAreas no qual apenas um intervalo é uma coluna inteira e apenas um é preenchido com rosa. O console mostrará null para a cor de preenchimento false para a propriedade isEntireRow e "Planilha1! F3:F5, Planilha1! H:H"(supondo que o nome da planilha seja "Planilha1") para a propriedadeaddress.

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

Compartilhar via