Share via

范围：使用 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);
    });
}

建议新示例

欢迎对新示例提供建议。 如果有有助于其他脚本开发人员的常见方案，请在页面底部的反馈部分中告诉我们。

另请参阅