Excel表格隐藏表头后顶部仍有空行，如何移除该行？

我设置表格不显示表头，在tables.add方法中传入false，同时设置showHeaders:false，虽然表头已不可见，但表格顶部仍显示一行空行，截图如下：

我的代码如下：

await Excel.run(async (context) => {
  let sheet = context.workbook.worksheets.getActiveWorksheet();
  let expensesTable = sheet.tables.add("A1:D1", false);
  expensesTable.name = "ExpensesTable";
  expensesTable.showHeaders = false;

  expensesTable.rows.add(null, [
    ["1/1/2017", "The Phone Company", "Communications", "$120"],
    ["1/2/2017", "Northwind Electric Cars", "Transportation", "$142"],
    ["1/5/2017", "Best For You Organics Company", "Groceries", "$27"],
    ["1/10/2017", "Coho Vineyard", "Restaurant", "$33"],
    ["1/11/2017", "Bellows College", "Education", "$350"],
    ["1/15/2017", "Trey Research", "Other", "$135"],
    ["1/15/2017", "Best For You Organics Company", "Groceries", "$97"]
  ]);
  if (Office.context.requirements.isSetSupported("ExcelApi", "1.2")) {
    sheet.getUsedRange().format.autofitColumns();
    sheet.getUsedRange().format.autofitRows();
  }

  sheet.activate();

  await context.sync();
});

问题根源是你初始化表格时指定的范围是A1:D1，即便设置了showHeaders:false，Excel仍会为表头预留这一行的位置，而后续调用rows.add(null, data)是在这个预留行下方添加数据，导致顶部出现空行。

你可以通过两种方式解决：

方式一：填充初始表格行 #

创建表格后，先把第一行数据填充到初始的A1:D1行，再添加剩余数据：

await Excel.run(async (context) => {
  let sheet = context.workbook.worksheets.getActiveWorksheet();
  let expensesTable = sheet.tables.add("A1:D1", false);
  expensesTable.name = "ExpensesTable";
  expensesTable.showHeaders = false;

  // 填充表格的第一行（原预留的空行）
  expensesTable.rows.getItemAt(0).values = [["1/1/2017", "The Phone Company", "Communications", "$120"]];
  // 添加剩余数据行
  expensesTable.rows.add(null, [
    ["1/2/2017", "Northwind Electric Cars", "Transportation", "$142"],
    ["1/5/2017", "Best For You Organics Company", "Groceries", "$27"],
    ["1/10/2017", "Coho Vineyard", "Restaurant", "$33"],
    ["1/11/2017", "Bellows College", "Education", "$350"],
    ["1/15/2017", "Trey Research", "Other", "$135"],
    ["1/15/2017", "Best For You Organics Company", "Groceries", "$97"]
  ]);
  
  if (Office.context.requirements.isSetSupported("ExcelApi", "1.2")) {
    sheet.getUsedRange().format.autofitColumns();
    sheet.getUsedRange().format.autofitRows();
  }

  sheet.activate();
  await context.sync();
});

方式二：用单个单元格初始化表格 #

创建表格时指定单个单元格作为初始范围，这样Excel不会预留表头行，后续添加的数据会直接从该单元格开始：

await Excel.run(async (context) => {
  let sheet = context.workbook.worksheets.getActiveWorksheet();
  // 以单个单元格A1创建表格，hasHeaders设为false
  let expensesTable = sheet.tables.add("A1", false);
  expensesTable.name = "ExpensesTable";
  expensesTable.showHeaders = false;

  // 添加所有数据行
  expensesTable.rows.add(null, [
    ["1/1/2017", "The Phone Company", "Communications", "$120"],
    ["1/2/2017", "Northwind Electric Cars", "Transportation", "$142"],
    ["1/5/2017", "Best For You Organics Company", "Groceries", "$27"],
    ["1/10/2017", "Coho Vineyard", "Restaurant", "$33"],
    ["1/11/2017", "Bellows College", "Education", "$350"],
    ["1/15/2017", "Trey Research", "Other", "$135"],
    ["1/15/2017", "Best For You Organics Company", "Groceries", "$97"]
  ]);
  
  if (Office.context.requirements.isSetSupported("ExcelApi", "1.2")) {
    sheet.getUsedRange().format.autofitColumns();
    sheet.getUsedRange().format.autofitRows();
  }

  sheet.activate();
  await context.sync();
});

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