行和列可见性示例

这些示例演示如何显示、隐藏和冻结行和列。

隐藏列

此脚本隐藏列“D”、“F”和“J”。

function main(workbook: ExcelScript.Workbook) {
  // Get the current worksheet.
  const sheet = workbook.getActiveWorksheet();

  // Hide columns D, F, and J.
  sheet.getRange("D:D").setColumnHidden(true);
  sheet.getRange("F:F").setColumnHidden(true);
  sheet.getRange("J:J").setColumnHidden(true);
}

显示所有行和列

此脚本获取工作表的已用区域，检查是否存在任何隐藏的行和列，并显示它们。

function main(workbook: ExcelScript.Workbook) {
    // Get the currently selected sheet.
    const selectedSheet = workbook.getActiveWorksheet();

    // Get the entire data range.
    const range = selectedSheet.getUsedRange();

    // If the used range is empty, end the script.
    if (!range) {
      console.log(`No data on this sheet.`)
      return;
    }

    // If no columns are hidden, log message, else show columns.
    if (range.getColumnHidden() == false) {
      console.log(`No columns hidden`);
    } else {
      range.setColumnHidden(false);
    }

    // If no rows are hidden, log message, else, show rows.
    if (range.getRowHidden() == false) {
      console.log(`No rows hidden`);
    } else {
      range.setRowHidden(false);
    }
}

冻结当前所选单元格

此脚本检查当前选择的单元格，并冻结所选单元格，以便这些单元格始终可见。

function main(workbook: ExcelScript.Workbook) {
    // Get the currently selected sheet.
    const selectedSheet = workbook.getActiveWorksheet();

    // Get the current selected range.
    const selectedRange = workbook.getSelectedRange();

    // If no cells are selected, end the script. 
    if (!selectedRange) {
      console.log(`No cells in the worksheet are selected.`);
      return;
    }

    // Log the address of the selected range
    console.log(`Selected range for the worksheet: ${selectedRange.getAddress()}`);

    // Freeze the selected range.
    selectedSheet.getFreezePanes().freezeAt(selectedRange);
}