当工作表名称包含特殊字符时，TableCollection.add会引发"InvalidArgument"错误 [英] TableCollection.add throws 'InvalidArgument' error when the sheet name has special characters

问题描述

我们构建了一个Excel Task Pane加载项，该加载项主要用于Tables.我们有一些执行TableCollection.add(rangeAddress，true)的代码.当"rangeAddress"中的工作表名称包含特殊字符时，此调用将失败.

We have built a Excel Task Pane add-in that primarily works with Tables. We have some code that executes TableCollection.add(rangeAddress, true). This call fails when the sheet name in the 'rangeAddress' contain special characters.

例如-

ctx.workbook.tables.add(Sheet!A1:E4, true)

工作正常，但是

ctx.workbook.tables.add(Sheet-Name!A1:E4, true)

抛出错误

{
"name": "OfficeExtension.Error",
"code": "InvalidArgument",
"message": "The argument is invalid or missing or has an incorrect format.",
"traceMessages": [],
"debugInfo": {
    "errorLocation": "TableCollection.add"
}

}

请注意，第二个示例地址的工作表名称中带有特殊字符-".可以在工作表名称中支持特殊字符吗?

Please note that the second example address has special character '-' in its sheet name. Is there a way to support special characters in sheet name?

推荐答案

问题本身与API无关.表格也没有.

The issue is not with the APIs... per se. Nor is it with tables.

在Excel中，如果您创建一个跨工作表引用，并且工作表名称中包含非字母数字字符(或类似的字符)，则会看到该公式被写为 ='Some-连字号'！C27 请注意，工作表名称用单引号括起来.

In Excel, if you create a cross-sheet reference, and if the sheet name has non alpha-numeric characters (or something of that sort), you will see that the formula gets written as ='Some-Hyphenated-Sheet'!C27 Note that the sheet name is surrounded with single quotes.

API只是本机Excel功能的一种体现，因此您也需要单引号将工作表名称引起来.那就是:

The API is just a reflection of native Excel functionality, so you would need to single-quote the sheet name as well. That is:

ctx.workbook.tables.add("'Sheet-Name'!A1:E4", true)

这篇关于当工作表名称包含特殊字符时，TableCollection.add会引发"InvalidArgument"错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！