Работа с несколькими диапазонами одновременно в надстройках Excel

Вы можете применять операции или задавать свойства в нескольких диапазонах одновременно, даже если они не смежные. Это делает код короче и эффективнее по сравнению с доступом к каждому диапазону отдельно.

Ключевые моменты

• Используйте RangeAreas для чтения или задания одного и того же значения в нескольких отдельных диапазонах в одном вызове.
• Свойство имеет значение , null если только все диапазоны элементов имеют одно и то же значение.
• Задайте свойство один раз для объекта, RangeAreas а не циклически, если для каждого диапазона не требуется разная логика.
• Избегайте больших RangeAreas объектов, состоящих из нескольких отдельных ячеек. Сузьте сначала с помощью getSpecialCells или других фильтров.
• Будьте осторожны с целыми столбцами или строками. Дополнительные сведения см. в разделе Чтение или запись в неограниченный диапазон.

RangeAreas

Объект RangeAreas представляет набор диапазонов, которые могут не касаться. Он использует много элементов с Range, с некоторыми различиями в том, как возвращаются значения.

Примеры:

• address возвращает одну строку с разделителями-запятыми для всех адресов.
• dataValidation возвращает один объект только в том случае, если каждый диапазон имеет одно и то же правило, в противном случае возвращается null.
• cellCount — это общее число ячеек во всех диапазонах.
• calculate Повторно вычисляет все ячейки в наборе.
• getEntireColumn и getEntireRow возвращает новый RangeAreas , охватывающий полные столбцы или строки для каждого элемента.
• copyFromпринимает в Range качестве источника или .RangeAreas

Полный список элементов Range, также доступных в RangeAreas

Свойства

Ознакомьтесь со статьей Чтение свойств RangeAreas перед написанием кода, считывающего любое из перечисленных свойств. Возвращаемое значение зависит от ряда факторов.

• address
• addressLocal
• cellCount
• conditionalFormats
• context
• dataValidation
• format
• isEntireColumn
• isEntireRow
• style
• worksheet

Методы

• calculate()
• clear()
• convertDataTypeToText()
• convertToLinkedDataType()
• copyFrom()
• getEntireColumn()
• getEntireRow()
• getIntersection()
• getIntersectionOrNullObject()
• getOffsetRange() (с именем getOffsetRangeAreas в объекте RangeAreas )
• getSpecialCells()
• getSpecialCellsOrNullObject()
• getTables()
• getUsedRange() (с именем getUsedRangeAreas в объекте RangeAreas )
• getUsedRangeOrNullObject() (с именем getUsedRangeAreasOrNullObject в объекте RangeAreas )
• load()
• set()
• setDirty()
• toJSON()
• track()
• untrack()

Свойства и методы, характерные для объекта RangeArea

Для типа RangeAreas существуют несколько свойств и методов, отсутствующих в объекте Range. Ниже приведен их выбор.

• areas. Объект RangeCollection, содержащий все диапазоны, которые представлены объектом RangeAreas. Объект RangeCollection — еще один новый объект, аналогичный другим объектам коллекции Excel. У него есть свойство items, являющееся массивом объектов Range, которые представляют диапазоны.
• areaCount. Общее количество диапазонов в RangeAreas.
• getOffsetRangeAreas. Действует аналогично методу Range.getOffsetRange, за исключением того, что возвращается объект RangeAreas, содержащий диапазоны, каждый из которых смещен относительно одного из диапазонов в исходном объекте RangeAreas.

Создание RangeAreas

Объект можно создать RangeAreas несколькими способами. В следующем списке приведены некоторые примеры.

• Вызвать метод Worksheet.getRanges() и передать ему строку с адресами диапазона, разделенными запятыми. Если диапазон, который нужно включить, был преобразован NamedItem, в строку можно включить имя вместо адреса.
• Вызов и Range.getSpecialCells() возврат RangeAreas объекта с ячейками определенного типа, например ячейками, содержащими формулы, проверку данных или условное форматирование.
• вызова метода Workbook.getSelectedRanges(); Этот метод возвращает объект RangeAreas, представляющий все диапазоны, выбранные в активном на данный момент листе.

После получения объекта RangeAreas можно создать другие с помощью методов объекта, возвращающих RangeAreas, например getOffsetRangeAreas и getIntersection.

Примечание.

Нельзя напрямую добавить дополнительные диапазоны к объекту RangeAreas. Например, у коллекции в RangeAreas.areas нет метода add.

Предупреждение

Не пытайтесь напрямую добавлять или удалять члены массива RangeAreas.areas.items . Это приведет к нежелательному поведению кода. Например, существует возможность принудительно добавить дополнительный объект Range в массив, но это приведет к ошибкам, поскольку свойства и методы RangeAreas действуют, как будто новый элемент не был добавлен. Например, свойство areaCount не включает диапазоны, принудительно добавленные таким образом, а RangeAreas.getItemAt(index) вызывает ошибку, если index больше, чем areasCount-1. Аналогичным образом, удаление объекта Range в массиве RangeAreas.areas.items путем получения ссылки на него и вызова его метода Range.delete приводит к ошибкам: хотя объект Rangeудален, свойства и методы родительского объекта RangeAreas будут действовать (или пытаться действовать), как будто он еще существует. Например, если код вызывает метод RangeAreas.calculate, Office попытается рассчитать диапазон, но это завершится ошибкой, поскольку объект range отсутствует.

Задание свойств для нескольких диапазонов

Установка свойства для объекта RangeAreas задает соответствующее свойство для всех диапазонов в коллекции RangeAreas.areas.

Ниже приведен пример установки свойства в нескольких диапазонах. Функция выделяет диапазоны F3:F5 и 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();
});

Этот пример применяется к сценариям, в которых можно жестко задать адреса диапазонов, передаваемых в getRanges, или легко рассчитать их во время выполнения. Ниже перечислены некоторые сценарии, в которых это возможно:

• Код выполняется в контексте известного шаблона.
• Код выполняется в контексте импортированных данных, в котором известна схема данных.

Объединить RangeAreas с getSpecialCells

Перед применением RangeAreas форматирования или проверки отфильтруйте только ячейки, соответствующие критерию, например формулы.

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

    // Two discontiguous vertical bands.
    const targets = sheet.getRanges("A1:A100, C1:C100");

    // Narrow to only the formula cells within those bands.
    const formulaCells = targets.getSpecialCells(Excel.SpecialCellType.formulas);
    formulaCells.format.fill.color = "lightYellow";
    await context.sync();
});

Получение специальных ячеек из нескольких диапазонов

Методы getSpecialCells и getSpecialCellsOrNullObject для объекта RangeAreas действуют аналогично методам с теми же названиями для объекта Range. Эти методы возвращают ячейки с указанными характеристиками из всех диапазонов в коллекции RangeAreas.areas. Дополнительные сведения о специальных ячейках см. в разделе Поиск специальных ячеек в диапазоне.

При вызове метода getSpecialCells или getSpecialCellsOrNullObject для объекта RangeAreas:

• Если в качестве первого параметра передается Excel.SpecialCellType.sameConditionalFormat, метод возвращает все ячейки с таким же условным форматированием, как у крайней левой верхней ячейки первого диапазона в коллекции RangeAreas.areas.
• Если в качестве первого параметра передается Excel.SpecialCellType.sameDataValidation, метод возвращает все ячейки с таким же правилом проверки данных, как у крайней левой верхней ячейки первого диапазона в коллекции RangeAreas.areas.

Чтение свойств RangeAreas

Чтение значений свойств RangeAreas требует внимания, так как определенное свойство может иметь разные значения для разных диапазонов в RangeAreas. Общее правило заключается в том, что если соответствующее значение может быть возвращено, оно будет возвращено. Например, в следующем коде rgb-код для розового (#FFC0CB) и true будет заноситься в консоль, так как оба диапазона в RangeAreas объекте имеют розовую заливку и оба являются целыми столбцами.

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

Так как значения свойств могут отличаться, учитывайте эти простые правила.

• Логические свойства имеют значение true только в том случае, если они являются истинными во всех диапазонах, в противном случае они имеют значение false.
• address всегда возвращает строку адресов с разделителями-запятыми.
• Другие свойства имеют одно null и то же значение, если все диапазоны не используют одно и то же значение.

Например, в приведенном ниже коде создается объект RangeAreas, в котором только один диапазон является целым столбцом и только один залит розовым цветом. Консоль отобразит значение null для цвета заливки, false для свойства isEntireRow и "Sheet1!F3:F5, Sheet1!H:H" (при условии, что имя листа — "Sheet1") для свойства 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"
});

См. также

• Основные концепции программирования с помощью API JavaScript для Excel
• Чтение и запись в большом диапазоне с помощью API JavaScript для Excel
• Чтение и запись в неограниченный диапазон с помощью API JavaScript для Excel