Excel Script宏执行报错：无法读取undefined的getRangeBetweenHeaderAndTotal属性

DevOps 小满

2026-4-21

看起来你遇到的问题根源非常明确——你的宏里写死了固定的工作表和表名称，但切换到其他标签页时，这些硬编码的内容就和当前页的结构不匹配了，导致获取不到对应的表格对象，自然就会抛出“无法读取undefined的xxx属性”的错误。

先给你梳理下核心问题点：

原代码里workbook.getWorksheets()[0]固定取第一个工作表，切换标签页后你需要处理的是当前激活的工作表，而不是固定的第一页
表名Table_INV_1020845是硬编码的，其他标签页的表名肯定对应不同的发票号，所以获取不到这个表，返回undefined，调用getRangeBetweenHeaderAndTotal()就会直接报错
发票号相关的单元格值和列名也写死了1020845，换其他页肯定不适用

下面是修改后的适配版代码，完全支持所有结构相同的标签页，不用再逐个修改：

function main(workbook: ExcelScript.Workbook) {
  // 关键改动1：获取当前激活的工作表，而非固定第一个
  let selectedSheet = workbook.getActiveWorksheet();
  
  // 关键改动2：动态获取当前工作表内的第一个表格（假设每个标签页只有一个目标表）
  let table = selectedSheet.getTables()[0];
  if (!table) {
    throw new Error("当前工作表中未找到表格，请确认结构是否正确");
  }

  // 关键改动3：从表名动态提取发票号（假设表名格式为Table_INV_xxxxxxx）
  let invoiceNumber = table.getName().split("_")[2];
  if (!invoiceNumber) {
    throw new Error("无法从表名提取发票号，请检查表名格式是否为Table_INV_xxxxxxx");
  }

  // 动态生成发票对应的列名
  let invoiceColumn = `^{Invoice #} ${invoiceNumber}`;

  // -------------------------- 以下是原逻辑的动态适配 --------------------------
  // Paste to range A1 on selectedSheet from table cell in row 0 on column Column1
  selectedSheet.getRange("A1").copyFrom(table.getColumns()[0].getRangeBetweenHeaderAndTotal().getRow(0), ExcelScript.RangeCopyType.all, false, false);

  // Paste to table cell in row 0 on column STYLE from table cell in row 1 on column STYLE
  table.getColumn("STYLE").getRangeBetweenHeaderAndTotal().getRow(0).copyFrom(table.getColumn("STYLE").getRangeBetweenHeaderAndTotal().getRow(1), ExcelScript.RangeCopyType.all, false, false);

  // Paste to table cell in row 1 on column STYLE from table cell in row 5 on column STYLE
  table.getColumn("STYLE").getRangeBetweenHeaderAndTotal().getRow(1).copyFrom(table.getColumn("STYLE").getRangeBetweenHeaderAndTotal().getRow(5), ExcelScript.RangeCopyType.all, false, false);

  // Paste to table cell in row 2 on column STYLE from table cell in row 10 on column STYLE
  table.getColumn("STYLE").getRangeBetweenHeaderAndTotal().getRow(2).copyFrom(table.getColumn("STYLE").getRangeBetweenHeaderAndTotal().getRow(10), ExcelScript.RangeCopyType.all, false, false);

  // Paste to table cell in row 3 on column STYLE from table cell in row 20 on column 发票列
  table.getColumn("STYLE").getRangeBetweenHeaderAndTotal().getRow(3).copyFrom(table.getColumn(invoiceColumn).getRangeBetweenHeaderAndTotal().getRow(20), ExcelScript.RangeCopyType.all, false, false);

  // Set range A5 on selectedSheet
  selectedSheet.getRange("A5").setValue("TOTAL");

  // Paste to range B1 on selectedSheet from table cell in row 0 on column Column13
  selectedSheet.getRange("B1").copyFrom(table.getColumn("Column13").getRangeBetweenHeaderAndTotal().getRow(0), ExcelScript.RangeCopyType.all, false, false);

  // Paste to table cell in row 0 on column EA from table cell in row 1 on column Column13
  table.getColumn("EA").getRangeBetweenHeaderAndTotal().getRow(0).copyFrom(table.getColumn("Column13").getRangeBetweenHeaderAndTotal().getRow(1), ExcelScript.RangeCopyType.all, false, false);

  // Paste to table cell in row 1 on column EA from table cell in row 5 on column Column13
  table.getColumn("EA").getRangeBetweenHeaderAndTotal().getRow(1).copyFrom(table.getColumn("Column13").getRangeBetweenHeaderAndTotal().getRow(5), ExcelScript.RangeCopyType.all, false, false);

  // Paste to table cell in row 2 on column EA from table cell in row 10 on column Column13
  table.getColumn("EA").getRangeBetweenHeaderAndTotal().getRow(2).copyFrom(table.getColumn("Column13").getRangeBetweenHeaderAndTotal().getRow(10), ExcelScript.RangeCopyType.all, false, false);

  // Paste to range C1 on selectedSheet from table cell in row 0 on column Column12
  selectedSheet.getRange("C1").copyFrom(table.getColumn("Column12").getRangeBetweenHeaderAndTotal().getRow(0), ExcelScript.RangeCopyType.all, false, false);

  // Paste to table cell in row 0 on column QTY from table cell in row 1 on column Column12
  table.getColumn("QTY").getRangeBetweenHeaderAndTotal().getRow(0).copyFrom(table.getColumn("Column12").getRangeBetweenHeaderAndTotal().getRow(1), ExcelScript.RangeCopyType.all, false, false);

  // Paste to table cell in row 1 on column QTY from table cell in row 5 on column Column12
  table.getColumn("QTY").getRangeBetweenHeaderAndTotal().getRow(1).copyFrom(table.getColumn("Column12").getRangeBetweenHeaderAndTotal().getRow(5), ExcelScript.RangeCopyType.all, false, false);

  // Paste to table cell in row 2 on column QTY from table cell in row 10 on column Column12
  table.getColumn("QTY").getRangeBetweenHeaderAndTotal().getRow(2).copyFrom(table.getColumn("Column12").getRangeBetweenHeaderAndTotal().getRow(10), ExcelScript.RangeCopyType.all, false, false);

  // Set range A1 on selectedSheet
  selectedSheet.getRange("A1").setValue(invoiceColumn);

  // Set range D1:D5 on selectedSheet
  selectedSheet.getRange("D1:D5").setValues([["Invoice #"],["-"],["-"],["-"],[invoiceNumber]]);

  // Paste to table cell in row 3 on column QTY from table cell in row 20 on column EA
  table.getColumn("QTY").getRangeBetweenHeaderAndTotal().getRow(3).copyFrom(table.getColumn("EA").getRangeBetweenHeaderAndTotal().getRow(20), ExcelScript.RangeCopyType.all, false, false);

  // Paste to table cell in row 3 on column EA from table cell in row 20 on column Column5
  table.getColumn("EA").getRangeBetweenHeaderAndTotal().getRow(3).copyFrom(table.getColumn("Column5").getRangeBetweenHeaderAndTotal().getRow(20), ExcelScript.RangeCopyType.all, false, false);

  // Delete range E:O on selectedSheet
  selectedSheet.getRange("E:O").delete(ExcelScript.DeleteShiftDirection.left);

  // Delete range 6:23 on selectedSheet
  selectedSheet.getRange("6:23").delete(ExcelScript.DeleteShiftDirection.up);

  // 统一格式化设置
  const sheetRange = selectedSheet.getRange().getFormat();
  sheetRange.getFont().setSize(12);
  sheetRange.setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
  sheetRange.setIndentLevel(0);
  sheetRange.setVerticalAlignment(ExcelScript.VerticalAlignment.bottom);
  sheetRange.setWrapText(false);
  sheetRange.setTextOrientation(0);
  sheetRange.autofitColumns();

  // 设置目标行的样式和边框
  const targetRow = table.getRangeBetweenHeaderAndTotal().getRow(3).getFormat();
  targetRow.getFont().setBold(true);
  
  const borders = targetRow.getRangeBorders();
  borders.getItem(ExcelScript.BorderIndex.diagonalDown).setStyle(ExcelScript.BorderLineStyle.none);
  borders.getItem(ExcelScript.BorderIndex.diagonalUp).setStyle(ExcelScript.BorderLineStyle.none);
  borders.getItem(ExcelScript.BorderIndex.edgeLeft).setStyle(ExcelScript.BorderLineStyle.continuous);
  borders.getItem(ExcelScript.BorderIndex.edgeLeft).setWeight(ExcelScript.BorderWeight.medium);
  borders.getItem(ExcelScript.BorderIndex.edgeTop).setStyle(ExcelScript.BorderLineStyle.continuous);
  borders.getItem(ExcelScript.BorderIndex.edgeTop).setWeight(ExcelScript.BorderWeight.medium);
  borders.getItem(ExcelScript.BorderIndex.edgeBottom).setStyle(ExcelScript.BorderLineStyle.continuous);
  borders.getItem(ExcelScript.BorderIndex.edgeBottom).setWeight(ExcelScript.BorderWeight.medium);
  borders.getItem(ExcelScript.BorderIndex.edgeRight).setStyle(ExcelScript.BorderLineStyle.continuous);
  borders.getItem(ExcelScript.BorderIndex.edgeRight).setWeight(ExcelScript.BorderWeight.medium);
  borders.getItem(ExcelScript.BorderIndex.insideVertical).setStyle(ExcelScript.BorderLineStyle.none);
  borders.getItem(ExcelScript.BorderIndex.insideHorizontal).setStyle(ExcelScript.BorderLineStyle.none);
}

使用说明

选中你需要处理的标签页
运行这个宏即可，它会自动识别当前页的表格、提取发票号，完成所有你需要的格式整理
如果某个标签页报错，先检查表名是否是Table_INV_xxxxxxx格式，以及表格列名是否和你录制时的结构一致

结合你提供的原表格和期望输出截图，这个代码完全匹配你的需求，能批量处理几百个结构相同的标签页。

备注：内容来源于stack exchange，提问作者lostexcelkid

Excel Script宏执行报错：无法读取undefined的getRangeBetweenHeaderAndTotal属性

使用说明 #

使用说明