范围：使用 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!处理从公式返回的错误
此脚本使用 TRANSPOSE 函数将范围“A1：D2”转换为“A4：B7”。 如果转置导致 #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);
});
}
建议新示例
欢迎对新示例提供建议。 如果有有助于其他脚本开发人员的常见方案，请在页面底部的反馈部分中告诉我们。