工作簿资源类型Workbook resource type

工作簿是顶级对象,它包含相关 workbook 对象,例如工作表、表、范围等。Workbook is the top level object which contains related workbook objects such as worksheets, tables, ranges, etc.

JSON 表示形式JSON representation

下面是资源的 JSON 表示形式。Here is a JSON representation of the resource

{
  "names": [{"@odata.type": "microsoft.graph.workbookNamedItem"}],
  "tables": [{"@odata.type": "microsoft.graph.workbookTable"}],
  "worksheets": [{"@odata.type": "microsoft.graph.workbookWorksheet"}]
}

属性Properties

None

方法Methods

方法Method 返回类型Return Type 说明Description
Create SessionCreate Session workbookSessionInfoworkbookSessionInfo 创建工作簿会话以启动永久或非永久会话。Create a workbook session to start a persistent or non-persistent session.
Close SessionClose Session None 关闭现有会话。Close an existing session.
Refresh SessionRefresh Session None 刷新现有会话。Refresh an existing session.

关系Relationships

关系Relationship 类型Type 说明Description
namesnames WorkbookNamedItem集合WorkbookNamedItem collection 表示工作簿范围内的已命名项目(称为区域和常量)的集合。只读。Represents a collection of workbook scoped named items (named ranges and constants). Read-only.
表格tables WorkbookTable集合WorkbookTable collection 表示与工作簿关联的表的集合。只读。Represents a collection of tables associated with the workbook. Read-only.
Worksheetsworksheets WorkbookWorksheet集合WorkbookWorksheet collection 表示与工作簿关联的工作表的集合。只读。Represents a collection of worksheets associated with the workbook. Read-only.

函数Functions

Excel 函数使用 JSON 对象调用使用语法 POST /workbook/functions/{function-name} 并在正文中提供函数自变量的工作簿函数。该函数产生 value,所有 error 字符串均返回到函数结果对象中。nullerror 值表示该函数执行成功。Excel functions: Invoke a workbook function using the syntax POST /workbook/functions/{function-name} and providing the function argument(s) in the body using a JSON object. The function's resulting value and any error strings are returned in the function result object. The error value of null indicates successful execution of the function.

受支持函数的完整列表在此处。请参阅特定参数名称和数据类型的函数签名。The complete list of supported functions are listed here. Refer to the function signature for specific parameter names and data types.

重要说明Important notes:

  • 使用 range 对象(而不是范围地址字符串)提供范围输入参数。The range input parameter is supplied using a range object instead of the range address string.
  • 与大部分 API 中使用的从 0 开始编制的索引不同,索引参数是从 1 开始编制索引。The index parameter is 1-indexed unlike the 0-index used in most of the APIs.

示例:vlookupExample: vlookup

在 Excel 电子表格中,vlookup 函数需要使用以下参数:In an Excel spreadsheet, the vlookup function takes the following arguments:

  1. 要查找的值(亦称为“查阅值”)。The value you want to look up, also called the lookup value.
  2. 查阅值所在的区域。The range where the lookup value is located. 请注意,查阅值应始终位于区域中的第一列,这样 VLOOKUP 才能正常运行。Remember that the lookup value should always be in the first column in the range for VLOOKUP to work correctly. 例如,如果查阅值位于单元格 C2,那么区域应从 C 列开始。For example, if your lookup value is in cell C2 then your range should start with C.
  3. 包含返回值的区域的列号。The column number in the range that contains the return value. 例如,如果指定 B2: D11 作为区域,应将 B 计为第一列,将 C 计为第二列,依此类推。For example, if you specify B2: D11 as the range, you should count B as the first column, C as the second, and so on.
  4. (可选)如果想要近似匹配,可指定 TRUE;如果想要返回值的完全匹配,则可指定 FALSE。 Optionally, you can specify TRUE if you want an approximate match or FALSE if you want an exact match of the return value. 如果未指定,默认值始终为 TRUE 或近似匹配。If you don't specify anything, the default value will always be TRUE or approximate match.

在单元格中,vlookup 函数如下所示:Inside a cell, the vlookup function looks like this:

=VLOOKUP(查阅值, 包含查阅值的区域, 包含返回值的区域的列号, 视需要为近似匹配指定 TRUE 或为完全匹配指定 FALSE)=VLOOKUP(lookup value, range containing the lookup value, the column number in the range containing the return value, optionally specify TRUE for approximate match or FALSE for an exact match)

(请参阅 VLOOKUP Excel 函数文档。)(See the documentation for the VLOOKUP Excel function.)

下面的示例展示了如何使用 Excel REST API 调用 vlookup 函数,以及一个或多个输入区域。The example below shows how to call the vlookup function and pass these parameters with the Excel REST API.

请求:Request:

POST https://graph.microsoft.com/beta/me/drive/root:/book1.xlsx:/workbook/functions/vlookup
content-type: Application/Json 
authorization: Bearer {access-token} 
workbook-session-id: {session-id}

{
    "lookupValue": "Temperature",
    "tableArray": { "Address": "Sheet1!E1:G5" },
    "colIndexNum": 2,
    "rangeLookup": false
}

响应:Response:

HTTP code: 200 OK
content-type: application/json;odata.metadata 

{
    "@odata.context": "https://graph.microsoft.com/beta/$metadata#workbookFunctionResult",
    "@odata.type": "#microsoft.graph.workbookFunctionResult",
    "@odata.id": "/users('f6d92604-4b76-4b70-9a4c-93dfbcc054d5')/drive/root/workbook/functions/vlookup()",
    "error": null,
    "value": "28.3"
}

示例:medianExample: median

在 Excel 电子表格中,median 函数需要使用一个或多个输入区域。In an Excel spreadsheet, the median function takes an array of one or more input ranges.

在单元格中,median 函数如以下示例所示:Inside a cell, the median function looks like this example:

=MEDIAN(A2:A6)=MEDIAN(A2:A6)

(请参阅 MEDIAN Excel 函数文档。)(See the documentation for the MEDIAN Excel function.)

下面的示例展示了如何调用 median 函数,以及如何使用 Excel REST API 传递一个或多个输入区域。The example below shows how to call the median function and one or more input ranges with the Excel REST API.

请求:Request:

POST https://graph.microsoft.com/beta/me/drive/root:/book1.xlsx:/workbook/functions/median
content-type: Application/Json 
authorization: Bearer {access-token} 
workbook-session-id: {session-id}

{
"values" :  [
        { "address": "Sheet2!A1:A5" },
        { "address": "Sheet2!B1:B5" },
      ] 
}

响应:Response:

HTTP code: 200 OK
content-type: application/json;odata.metadata 

{
  "@odata.context": "https://graph.microsoft.com/beta/$metadata#workbookFunctionResult",
  "@odata.type": "#microsoft.graph.workbookFunctionResult",
  "@odata.id": "/users('2abcad6a-2fca-4b6e-9577-e358a757d77d')/drive/root/workbook/functions/median()",
  "error": null,
  "value": 30
}