Обработка динамических массивов и разлива с помощью API JavaScript для Excel

В этой статье приведены примеры кода, которые обрабатывают динамические массивы и разлив диапазонов с помощью API JavaScript для Excel. Динамический массив — это функция Excel, которая позволяет формулам автоматически возвращать несколько значений. Понимание того, как работать с разлитыми диапазонами программно, позволяет надстройке эффективно взаимодействовать с этими динамическими результатами.

Полный список свойств и методов, поддерживаемых объектом, см. в Rangeразделе Класс Excel.Range.

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

  • Динамические формулы массива автоматически переносить результаты в соседние ячейки.
  • Используйте getSpillingToRange для поиска всех ячеек, заполненных формулой динамического массива.
  • Используйте getSpillParent для поиска исходной ячейки, содержащей формулу, которая создала разлитое значение.
  • Оба метода имеют *OrNullObject версии, чтобы избежать возникновения ошибок, если не существует разлива.
  • Только диапазоны из одной ячейки могут вызывать getSpillParent. При вызове его в диапазонах с несколькими ячейками возникает ошибка.

Динамические массивы

Динамические формулы массива автоматически заполняют значения в несколько ячеек за пределами исходной ячейки формулы. Это расширение называется "разливание". Общие формулы динамического массива включают FILTER, SORT, UNIQUE, SEQUENCEи простые ссылки на массивы, такие как =A1:D1.

При разливе формулы Excel автоматически заполняет соседние ячейки результатами. Надстройка может программно определить, какие ячейки содержат эти разлитые значения, с помощью метода Range.getSpillingToRange .

Для обработки случаев, когда ячейка может не содержать разлитую формулу, используйте версию Range.getSpillingToRangeOrNullObject*OrNullObject. При этом возвращается разлитый диапазон, если он существует. Если разлитый диапазон не существует, он возвращает объект, свойство которого isNullObject имеет значение true. Затем код оценивает это свойство, чтобы определить, существует ли диапазон разлива.

Получение диапазона разлива из формулы

Следующий пример кода задает формулу, которая возвращает динамический массив, а затем извлекает и регистрирует диапазон, содержащий все разбросаемые значения.

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

    // Set G4 to a formula that returns a dynamic array.
    let targetCell = sheet.getRange("G4");
    targetCell.formulas = [["=A4:D4"]];

    // Get the address of the cells that the dynamic array spilled into.
    let spillRange = targetCell.getSpillingToRange();
    spillRange.load("address");

    // Sync and log the spilled-to range.
    await context.sync();

    // This will log the range as "G4:J4".
    console.log(`Copying the table headers spilled into ${spillRange.address}.`);
});

Поиск источника разлитых значений

При работе с ячейкой, содержащей разлитое значение, можно выполнить трассировку до исходной ячейки формулы с помощью метода Range.getSpillParent . Это полезно, если необходимо определить, какая формула отвечает за заполнение определенной ячейки.

Метод getSpillParent работает только в том случае, если объект входного диапазона является одной ячейкой. Вызов getSpillParent диапазона с несколькими ячейками выдает ошибку. Используйте Range.getSpillParentOrNullObject , чтобы избежать ошибки. Дополнительные сведения о *OrNullObject методах см. в разделе Методы и свойства *OrNullObject.

Получение ячейки формулы из разлитого значения

В следующем примере кода показано, как найти родительскую ячейку разлитого значения. При выборе ячейки, содержащей разлитое значение, и выполнении этого кода возвращается адрес родительского элемента разлива.

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

    // Get a cell that contains a spilled value.
    let spilledCell = sheet.getRange("H4");

    // Get the parent cell whose formula is spilling into `spilledCell`.
    let spillParentRange = spilledCell.getSpillParent();
    spillParentRange.load("address");

    await context.sync();

    // Log the address of the cell containing the formula.
    console.log(`The spill parent of H4 is ${spillParentRange.address}.`);
});

См. также

  • Last updated on