Диапазоны: работа с сеткой в сценариях Office
Ниже приведены простые скрипты, которые можно использовать в собственных книгах. Они формируют стандартные блоки для более крупных решений. Разверните эти скрипты, чтобы расширить решение и решить распространенные проблемы.
Чтение и ведение журнала одной ячейки
Этот пример считывает значение A1 и выводит его на консоль.
function main(workbook: ExcelScript.Workbook) {
// Get the current worksheet.
let selectedSheet = workbook.getActiveWorksheet();
// Get the value of cell A1.
let range = selectedSheet.getRange("A1");
// Print the value of A1.
console.log(range.getValue());
}
Чтение активной ячейки
Этот скрипт регистрирует значение текущей активной ячейки. Если выбрано несколько ячеек, верхняя левая ячейка будет зарегистрирована.
function main(workbook: ExcelScript.Workbook) {
// Get the current active cell in the workbook.
let cell = workbook.getActiveCell();
// Log that cell's value.
console.log(`The current cell's value is ${cell.getValue()}`);
}
Добавление данных в диапазон
Этот скрипт добавляет набор значений на новый лист. Значения начинаются в ячейке A1. Данные, используемые в этом скрипте, являются предопределенными, но могут быть источником из других мест в книге или из нее.
function main(workbook: ExcelScript.Workbook) {
// The getData call could be replaced by input from Power Automate or a fetch call.
const data = getData();
// Create a new worksheet and switch to it.
const newWorksheet = workbook.addWorksheet("DataSheet");
newWorksheet.activate();
// Get a range matching the size of the data.
const dataRange = newWorksheet.getRangeByIndexes(
0,
0,
data.length,
data[0].length);
// Set the data as the values in the range.
dataRange.setValues(data);
}
function getData(): string[][] {
return [["Abbreviation", "State/Province", "Country"],
["AL", "Alabama", "USA"],
["AK", "Alaska", "USA"],
["AZ", "Arizona", "USA"],
["AR", "Arkansas", "USA"],
["CA", "California", "USA"],
["CO", "Colorado", "USA"],
["CT", "Connecticut", "USA"],
["DE", "Delaware", "USA"],
["DC", "District of Columbia", "USA"],
["FL", "Florida", "USA"],
["GA", "Georgia", "USA"],
["HI", "Hawaii", "USA"],
["ID", "Idaho", "USA"],
["IL", "Illinois", "USA"],
["IN", "Indiana", "USA"],
["IA", "Iowa", "USA"],
["KS", "Kansas", "USA"],
["KY", "Kentucky", "USA"],
["LA", "Louisiana", "USA"],
["ME", "Maine", "USA"],
["MD", "Maryland", "USA"],
["MA", "Massachusetts", "USA"],
["MI", "Michigan", "USA"],
["MN", "Minnesota", "USA"],
["MS", "Mississippi", "USA"],
["MO", "Missouri", "USA"],
["MT", "Montana", "USA"],
["NE", "Nebraska", "USA"],
["NV", "Nevada", "USA"],
["NH", "New Hampshire", "USA"],
["NJ", "New Jersey", "USA"],
["NM", "New Mexico", "USA"],
["NY", "New York", "USA"],
["NC", "North Carolina", "USA"],
["ND", "North Dakota", "USA"],
["OH", "Ohio", "USA"],
["OK", "Oklahoma", "USA"],
["OR", "Oregon", "USA"],
["PA", "Pennsylvania", "USA"],
["RI", "Rhode Island", "USA"],
["SC", "South Carolina", "USA"],
["SD", "South Dakota", "USA"],
["TN", "Tennessee", "USA"],
["TX", "Texas", "USA"],
["UT", "Utah", "USA"],
["VT", "Vermont", "USA"],
["VA", "Virginia", "USA"],
["WA", "Washington", "USA"],
["WV", "West Virginia", "USA"],
["WI", "Wisconsin", "USA"],
["WY", "Wyoming", "USA"],
["AB", "Alberta", "CAN"],
["BC", "British Columbia", "CAN"],
["MB", "Manitoba", "CAN"],
["NB", "New Brunswick", "CAN"],
["NL", "Newfoundland and Labrador", "CAN"],
["NT", "Northwest Territory", "CAN"],
["NS", "Nova Scotia", "CAN"],
["NU", "Nunavut Territory", "CAN"],
["ON", "Ontario", "CAN"],
["PE", "Prince Edward Island", "CAN"],
["QC", "Quebec", "CAN"],
["SK", "Saskatchewan", "CAN"],
["YT", "Yukon Territory", "CAN"]];
}
Изменение соседней ячейки
Этот скрипт получает смежные ячейки, используя относительные ссылки. Обратите внимание, что если активная ячейка находится в верхней строке, часть скрипта завершается ошибкой, так как она ссылается на ячейку над выбранной в данный момент.
function main(workbook: ExcelScript.Workbook) {
// Get the currently active cell in the workbook.
let activeCell = workbook.getActiveCell();
console.log(`The active cell's address is: ${activeCell.getAddress()}`);
// Get the cell to the right of the active cell and set its value and color.
let rightCell = activeCell.getOffsetRange(0,1);
rightCell.setValue("Right cell");
console.log(`The right cell's address is: ${rightCell.getAddress()}`);
rightCell.getFormat().getFont().setColor("Magenta");
rightCell.getFormat().getFill().setColor("Cyan");
// Get the cell to the above of the active cell and set its value and color.
// Note that this operation will fail if the active cell is in the top row.
let aboveCell = activeCell.getOffsetRange(-1, 0);
aboveCell.setValue("Above cell");
console.log(`The above cell's address is: ${aboveCell.getAddress()}`);
aboveCell.getFormat().getFont().setColor("White");
aboveCell.getFormat().getFill().setColor("Black");
}
Изменение всех смежных ячеек
Этот скрипт копирует форматирование в активной ячейке в соседние ячейки. Обратите внимание, что этот скрипт работает только в том случае, если активная ячейка не размещена на краю листа.
function main(workbook: ExcelScript.Workbook) {
// Get the active cell.
let activeCell = workbook.getActiveCell();
// Get the cell that's one row above and one column to the left of the active cell.
let cornerCell = activeCell.getOffsetRange(-1,-1);
// Get a range that includes all the cells surrounding the active cell.
let surroundingRange = cornerCell.getResizedRange(2, 2)
// Copy the formatting from the active cell to the new range.
surroundingRange.copyFrom(
activeCell, /* The source range. */
ExcelScript.RangeCopyType.formats /* What to copy. */
);
}
Изменение каждой отдельной ячейки в диапазоне
Этот скрипт выполняет цикл по выбранному в данный момент диапазону. Он очищает текущее форматирование и задает цвет заливки в каждой ячейке случайным цветом.
function main(workbook: ExcelScript.Workbook) {
// Get the currently selected range.
let range = workbook.getSelectedRange();
// Get the size boundaries of the range.
let rows = range.getRowCount();
let cols = range.getColumnCount();
// Clear any existing formatting.
range.clear(ExcelScript.ClearApplyTo.formats);
// Iterate over the range.
for (let row = 0; row < rows; row++) {
for (let col = 0; col < cols; col++) {
// Generate a random hexadecimal color code.
let colorString = `#${Math.random().toString(16).substr(-6)}`;
// Set the color of the current cell to that random hexadecimal code.
range.getCell(row, col).getFormat().getFill().setColor(colorString);
}
}
}
Получение групп ячеек на основе специальных условий
Этот скрипт получает все пустые ячейки в используемом диапазоне текущего листа. Затем он выделяет все эти ячейки желтым фоном.
function main(workbook: ExcelScript.Workbook) {
// Get the current used range.
let range = workbook.getActiveWorksheet().getUsedRange();
// Get all the blank cells.
let blankCells = range.getSpecialCells(ExcelScript.SpecialCellType.blanks);
// Highlight the blank cells with a yellow background.
blankCells.getFormat().getFill().setColor("yellow");
}
Формулы
Диапазоны имеют как значения, так и формулы. Формула — это вычисляемая формула. Значение является результатом этого выражения.
Одна формула
Этот скрипт задает формулу ячейки, а затем показывает, как Excel сохраняет формулу и значение ячейки отдельно.
function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
// Set A1 to 2.
let a1 = selectedSheet.getRange("A1");
a1.setValue(2);
// Set B1 to the formula =(2*A1), which should equal 4.
let b1 = selectedSheet.getRange("B1");
b1.setFormula("=(2*A1)");
// Log the current results for `getFormula` and `getValue` at B1.
console.log(`B1 - Formula: ${b1.getFormula()} | Value: ${b1.getValue()}`);
}
Обработка ошибки, #SPILL! возвращаемой формулой
Этот скрипт транспонирует диапазон "A1:D2" в "A4:B7" с помощью функции TRANSPOSE. Если транспонирование приводит к #SPILL ошибке, он очищает целевой диапазон и снова применяет формулу.
function main(workbook: ExcelScript.Workbook) {
let sheet = workbook.getActiveWorksheet();
// Use the data in A1:D2 for the sample.
let dataAddress = "A1:D2"
let inputRange = sheet.getRange(dataAddress);
// Place the transposed data starting at A4.
let targetStartCell = sheet.getRange("A4");
// Compute the target range.
let targetRange = targetStartCell.getResizedRange(inputRange.getColumnCount() - 1, inputRange.getRowCount() - 1);
// Call the transpose helper function.
targetStartCell.setFormula(`=TRANSPOSE(${dataAddress})`);
// Check if the range update resulted in a spill error.
let checkValue = targetStartCell.getValue() as string;
if (checkValue === '#SPILL!') {
// Clear the target range and call the transpose function again.
console.log("Target range has data that is preventing update. Clearing target range.");
targetRange.clear();
targetStartCell.setFormula(`=TRANSPOSE(${dataAddress})`);
}
// Select the transposed range to highlight it.
targetRange.select();
}
Замените все формулы их результирующих значений
Этот скрипт заменяет каждую ячейку текущего листа, содержащую формулу с результатом этой формулы. Это означает, что после выполнения скрипта не будет никаких формул, только значения.
function main(workbook: ExcelScript.Workbook) {
// Get the ranges with formulas.
let sheet = workbook.getActiveWorksheet();
let usedRange = sheet.getUsedRange();
let formulaCells = usedRange.getSpecialCells(ExcelScript.SpecialCellType.formulas);
// In each formula range: get the current value, clear the contents, and set the value as the old one.
// This removes the formula but keeps the result.
formulaCells.getAreas().forEach((range) => {
let currentValues = range.getValues();
range.clear(ExcelScript.ClearApplyTo.contents);
range.setValues(currentValues);
});
}
Предложить новые примеры
Мы приветствуем предложения по новым образцам. Если есть распространенный сценарий, который поможет другим разработчикам сценариев, сообщите нам в разделе Отзывы в нижней части страницы.
См. также
Office Scripts