Office Script技术咨询：筛选可见行并粘贴至目标工作表最后一行

需求回顾 #

• S1工作表：按两个唯一值分别筛选，将筛选后的可见行复制到ABC_1、ABC_2工作表
• S2工作表：按相同的两个唯一值筛选，将结果追加到ABC_1工作表的最后一行

现有代码的问题 #

你的代码存在几个关键问题：

• 硬编码了固定行范围（如5:9、10:12），没有动态获取筛选后的可见行
• 变量名存在拼写错误（比如prof_Cenr和prof_Cen，I_ABC_DEBT___Output和I_AGED_DEBT___Output）
• 没有正确计算目标工作表的最后一行，无法实现追加粘贴

完整解决方案代码 #

function main(workbook: ExcelScript.Workbook) {
  // 定义筛选条件与目标表的映射（S1的两个条件）
  const s1FilterConfigs = [
    { filterValue: "*ABC DEBT PRO*", targetSheetName: "ABC_1" },
    { filterValue: "*ISPEC DEBT*", targetSheetName: "ABC_2" }
  ];

  // 获取核心工作表对象
  const s1Sheet = workbook.getWorksheet("S1");
  const s2Sheet = workbook.getWorksheet("S2");
  const abc1Sheet = workbook.getWorksheet("ABC_1");

  // 封装筛选并复制可见行到目标表的函数
  const filterAndCopyVisibleRows = (sourceSheet: ExcelScript.Worksheet, filterColumnIndex: number, filterValue: string, targetSheet: ExcelScript.Worksheet, isAppend: boolean = false) => {
    // 确保自动筛选已开启（基于表头行，假设表头在第3行）
    const headerRange = sourceSheet.getRange("3:3");
    if (!sourceSheet.getAutoFilter()) {
      sourceSheet.getAutoFilter().apply(headerRange);
    }

    // 应用筛选条件
    sourceSheet.getAutoFilter().apply(headerRange, filterColumnIndex, {
      filterOn: ExcelScript.FilterOn.custom,
      criterion1: filterValue
    });

    // 获取筛选后的可见行（跳过表头，从第4行开始）
    const dataRange = sourceSheet.getRange("4:" + sourceSheet.getUsedRange().getLastRow().getRowIndex());
    const visibleRows = dataRange.getSpecialCells(ExcelScript.SpecialCellType.visible);

    if (visibleRows) {
      // 计算目标表的起始粘贴行
      let startRow = 1;
      if (isAppend) {
        // 追加模式：找到目标表最后一行的下一行
        const usedRange = targetSheet.getUsedRange(true); // true表示忽略空行
        startRow = usedRange ? usedRange.getLastRow().getRowIndex() + 1 : 1;
      }

      // 复制可见行到目标表
      targetSheet.getRange(`A${startRow}`).copyFrom(visibleRows, ExcelScript.RangeCopyType.all, false, false);
    }

    // 清除筛选条件
    sourceSheet.getAutoFilter().clearCriteria();
  };

  // 处理S1的两个筛选任务
  s1FilterConfigs.forEach(config => {
    const targetSheet = workbook.getWorksheet(config.targetSheetName);
    if (targetSheet) {
      // 假设筛选列是第32列（对应原代码的31，因为索引从0开始）
      filterAndCopyVisibleRows(s1Sheet, 31, config.filterValue, targetSheet);
    }
  });

  // 处理S2的筛选任务：按两个条件筛选后追加到ABC_1
  filterAndCopyVisibleRows(s2Sheet, 31, "*ABC DEBT PRO*", abc1Sheet, true);
  filterAndCopyVisibleRows(s2Sheet, 31, "*ISPEC DEBT*", abc1Sheet, true);
}

关键代码说明 #

1. 动态获取可见行：
   使用getSpecialCells(ExcelScript.SpecialCellType.visible)获取筛选后的非隐藏行，替代硬编码的固定行范围，确保只复制符合条件的数据。

2. 计算目标表最后一行：
   通过targetSheet.getUsedRange(true).getLastRow().getRowIndex() + 1找到目标表已使用区域的最后一行，下一行就是新数据的起始粘贴位置，实现追加效果。

3. 代码复用：
   封装filterAndCopyVisibleRows函数，避免重复编写筛选、复制的逻辑，提高代码可维护性。

4. 筛选列索引：
   注意Office Script中列索引从0开始，原代码中的31对应第32列（AF列），如果你的筛选列不是这个位置，需要自行调整。

内容的提问来源于stack exchange，提问作者user21553220