通过 Microsoft Graph 使用 Excel 工作簿函数Use workbook functions in Excel with Microsoft Graph

可以使用以下语法调用任何工作簿函数:POST /workbook/functions/{function-name}You can invoke any workbook function by using the following syntax: POST /workbook/functions/{function-name}. 使用 JSON 对象提供正文中的函数参数。You provide the function argument(s) in the body using a JSON object. 该函数产生 value,所有 error 字符串均返回到函数结果对象中。The function's resulting value and any error strings are returned in the function result object. nullerror 值表示该函数执行成功。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. lookup_value****(必需)要查找的值。lookup_value (required) The value you want to look up.
  2. table_arraytable_array(必需)查阅值所在的单元格的区域。table_array (required) The range of cells 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. col_index_num****(必需)包含返回值的区域的列号。col_index_num (required) 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. range_lookup****(可选)一个逻辑值,指定希望 VLOOKUP**** 查找近似匹配还是精确的匹配。range_lookup (optional) The logical value that specifies whether you want VLOOKUP to find an approximate or an exact match. 如果想要近似匹配,可指定 TRUE****;如果想要返回值的完全匹配,则可指定 FALSE****。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.)

请求:Request:

下面的示例展示了如何使用 Excel REST API 调用 vlookup 函数和传递这些参数。The following example shows how to call the vlookup function and pass these parameters with the Excel REST API.

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.)

请求Request

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

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
}

另请参阅See also