Handle dynamic arrays and spilling using the Excel JavaScript API
This article provides a code sample that handles dynamic arrays and range spilling using the Excel JavaScript API. For the complete list of properties and methods that the Range
object supports, see Excel.Range class.
Dynamic arrays
Some Excel formulas return Dynamic arrays. These fill the values of multiple cells outside of the formula's original cell. This value overflow is referred to as a "spill". Your add-in can find the range used for a spill with the Range.getSpillingToRange method. There is also a *OrNullObject version, Range.getSpillingToRangeOrNullObject
.
The following sample shows a basic formula that copies the contents of a range into a cell, which spills into neighboring cells. The add-in then logs the range that contains the spill.
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 spilling
Find the cell responsible for spilling into a given cell by using the Range.getSpillParent method. Note that getSpillParent
only works when the range object is a single cell. Calling getSpillParent
on a range with multiple cells will result in an error being thrown (or a null range being returned for Range.getSpillParentOrNullObject
).
See also
意見反應
https://aka.ms/ContentUserFeedback。
即將登場:在 2024 年,我們將逐步淘汰 GitHub 問題作為內容的意見反應機制,並將它取代為新的意見反應系統。 如需詳細資訊,請參閱:提交並檢視相關的意見反應