Excel.Range class

区域表示一个或多个相邻的单元格,例如单元格、行、列、单元格块等。Range represents a set of one or more contiguous cells such as a cell, a row, a column, block of cells, etc.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

Extends

属性

address

表示 A1 样式的区域引用。Represents the range reference in A1-style. Address 值将包含工作表引用 (例如, "Sheet1!A1: B4 ")。Address value will contain the Sheet reference (e.g. "Sheet1!A1:B4"). 只读。Read-only.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

addressLocal

以用户语言表示对指定区域的区域引用。Represents range reference for the specified range in the language of the user. 只读。Read-only.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

cellCount

范围中的单元格数。Number of cells in the range. 如果单元格数超过 2^31-1 (2,147,483,647),此 API 返回 -1。This API will return -1 if the cell count exceeds 2^31-1 (2,147,483,647). 只读。Read-only.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

columnCount

表示区域中的列总数。Represents the total number of columns in the range. 只读。Read-only.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

columnHidden

表示当前 range 的所有列均已隐藏。Represents if all columns of the current range are hidden.

[API 集: ExcelApi 1.2][ API set: ExcelApi 1.2 ]

columnIndex

表示区域中第一个单元格的列编号。Represents the column number of the first cell in the range. 从零开始编制索引。Zero-indexed. 只读。Read-only.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

conditionalFormats

与该范围相交的 ConditionalFormats 的集合。Collection of ConditionalFormats that intersect the range. 只读。Read-only.

[API 集: ExcelApi 1.6][ API set: ExcelApi 1.6 ]

context

与该对象关联的请求上下文。The request context associated with the object. 这会将加载项的进程连接到 Office 主机应用程序的进程。This connects the add-in's process to the Office host application's process.

dataValidation

返回数据有效性对象。Returns a data validation object.

[API 集: ExcelApi 1.8][ API set: ExcelApi 1.8 ]

format

返回一个格式对象,其中封装了区域的字体、填充、边框、对齐方式和其他属性。Returns a format object, encapsulating the range's font, fill, borders, alignment, and other properties. 只读。Read-only.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

formulas

表示采用 A1 表示法的公式。Represents the formula in A1-style notation. 将公式设置为区域时, value 参数可以是单个值 (字符串), 也可以是二维数组。When setting formulas to a range, the value argument can be either a single value (a string) or a two-dimensional array. 如果参数是单个值, 它将应用于区域中的所有单元格。If the argument is a single value, it will be applied to all cells in the range.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

formulasLocal

表示采用 A1 样式表示法的公式,使用用户的语言和数字格式区域设置。Represents the formula in A1-style notation, in the user's language and number-formatting locale. 例如,英语中的公式 "=SUM(A1, 1.5)" 在德语中将变为 "=SUMME(A1; 1,5)"。For example, the English "=SUM(A1, 1.5)" formula would become "=SUMME(A1; 1,5)" in German. 将公式设置为区域时, value 参数可以是单个值 (字符串), 也可以是二维数组。When setting formulas to a range, the value argument can be either a single value (a string) or a two-dimensional array. 如果参数是单个值, 它将应用于区域中的所有单元格。If the argument is a single value, it will be applied to all cells in the range.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

formulasR1C1

表示采用 R1C1 表示法的公式。Represents the formula in R1C1-style notation. 将公式设置为区域时, value 参数可以是单个值 (字符串), 也可以是二维数组。When setting formulas to a range, the value argument can be either a single value (a string) or a two-dimensional array. 如果参数是单个值, 它将应用于区域中的所有单元格。If the argument is a single value, it will be applied to all cells in the range.

[API 集: ExcelApi 1.2][ API set: ExcelApi 1.2 ]

hasSpill

表示所有单元格是否都具有溢出边框。Represents if all cells have a spill border. 如果所有单元格都有溢出边框, 则返回 true; 如果所有单元格都没有溢出边框, 则返回 false。Returns true if all cells have a spill border, or false if all cells do not have a spill border. 如果区域中有和没有溢出边框的单元格, 则返回 null。Returns null if there are cells both with and without spill borders within the range.

[API 集: EXCELAPI BETA (仅限预览)][ API set: ExcelApi BETA (PREVIEW ONLY) ]

height

返回从区域的上边缘到区域的下边缘的 100% 缩放的距离(以磅为单位)。Returns the distance in points, for 100% zoom, from top edge of the range to bottom edge of the range. 只读。Read-only.

[API 集: EXCELAPI BETA (仅限预览)][ API set: ExcelApi BETA (PREVIEW ONLY) ]

hidden

表示当前区域中的所有单元格是否隐藏。Represents if all cells of the current range are hidden. 只读。Read-only.

[API 集: ExcelApi 1.2][ API set: ExcelApi 1.2 ]

hyperlink

表示当前区域的超链接。Represents the hyperlink for the current range.

[API 集: ExcelApi 1.7][ API set: ExcelApi 1.7 ]

isEntireColumn

表示当前区域是否为整列。Represents if the current range is an entire column. 只读。Read-only.

[API 集: ExcelApi 1.7][ API set: ExcelApi 1.7 ]

isEntireRow

表示当前区域是否为整行。Represents if the current range is an entire row. 只读。Read-only.

[API 集: ExcelApi 1.7][ API set: ExcelApi 1.7 ]

left

返回从工作表的左边缘到区域的左边缘的 100% 缩放的距离(以磅为单位)。Returns the distance in points, for 100% zoom, from left edge of the worksheet to left edge of the range. 只读。Read-only.

[API 集: EXCELAPI BETA (仅限预览)][ API set: ExcelApi BETA (PREVIEW ONLY) ]

linkedDataTypeState

表示每个单元格的数据类型状态。Represents the data type state of each cell. 只读。Read-only.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

numberFormat

表示给定范围的 Excel 数字格式代码。Represents Excel's number format code for the given range. 将数字格式设置为一个区域时, value 参数可以是单个值 (string), 也可以是二维数组。When setting number format to a range, the value argument can be either a single value (string) or a two-dimensional array. 如果参数是单个值, 它将应用于区域中的所有单元格。If the argument is a single value, it will be applied to all cells in the range.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

numberFormatLocal

表示 Excel 中的给定区域的数字格式代码,以用户语言的字符串表示。Represents Excel's number format code for the given range as a string in the language of the user. 将数字格式设置为局部区域时, value 参数可以是单个值 (字符串), 也可以是二维数组。When setting number format local to a range, the value argument can be either a single value (string) or a two-dimensional array. 如果参数是单个值, 它将应用于区域中的所有单元格。If the argument is a single value, it will be applied to all cells in the range.

[API 集: ExcelApi 1.7][ API set: ExcelApi 1.7 ]

rowCount

返回区域中的总行数。Returns the total number of rows in the range. 只读。Read-only.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

rowHidden

表示当前 range 的所有行均已隐藏。Represents if all rows of the current range are hidden.

[API 集: ExcelApi 1.2][ API set: ExcelApi 1.2 ]

rowIndex

返回区域中第一个单元格的行编号。Returns the row number of the first cell in the range. 从零开始编制索引。Zero-indexed. 只读。Read-only.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

sort

表示当前 range 的区域排序。Represents the range sort of the current range. 只读。Read-only.

[API 集: ExcelApi 1.2][ API set: ExcelApi 1.2 ]

style

表示当前区域的样式。Represents the style of the current range. 如果单元格的样式不一致, 则将返回 null。If the styles of the cells are inconsistent, null will be returned. 对于自定义样式, 将返回样式名称。For custom styles, the style name will be returned. 对于内置样式, 将返回一个表示 BuiltInStyle 枚举中的值的字符串。For built-in styles, a string representing a value in the BuiltInStyle enum will be returned.

[API 集: ExcelApi 1.7][ API set: ExcelApi 1.7 ]

text

指定区域的文本值。Text values of the specified range. 文本值与单元格宽度无关。The Text value will not depend on the cell width. Excel # UI 中发生的符号替换不会影响 API 返回的文本值。The # sign substitution that happens in Excel UI will not affect the text value returned by the API. 只读。Read-only.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

top

返回从工作表的上边缘到区域的上边缘的 100% 缩放的距离(以磅为单位)。Returns the distance in points, for 100% zoom, from top edge of the worksheet to top edge of the range. 只读。Read-only.

[API 集: EXCELAPI BETA (仅限预览)][ API set: ExcelApi BETA (PREVIEW ONLY) ]

values

表示指定区域的原始值。Represents the raw values of the specified range. 返回的数据可能是字符串、数字,也可能是布尔值。The data returned could be of type string, number, or a boolean. 包含错误的单元格将返回错误字符串。Cells that contain an error will return the error string. 将值设置为一个范围时, value 参数可以是单个值 (string、number 或 boolean), 也可以是一个二维数组。When setting values to a range, the value argument can be either a single value (string, number or boolean) or a two-dimensional array. 如果参数是单个值, 它将应用于区域中的所有单元格。If the argument is a single value, it will be applied to all cells in the range.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

valueTypes

表示每个单元格的数据类型。Represents the type of data of each cell. 只读。Read-only.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

width

返回从区域的左边缘到区域的右边缘的 100% 缩放的距离(以磅为单位)。Returns the distance in points, for 100% zoom, from left edge of the range to right edge of the range. 只读。Read-only.

[API 集: EXCELAPI BETA (仅限预览)][ API set: ExcelApi BETA (PREVIEW ONLY) ]

worksheet

包含当前区域的工作表。The worksheet containing the current range. 只读。Read-only.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

方法

autoFill(destinationRange, autoFillType)

填充区域从当前区域到目标区域。Fills range from the current range to the destination range. 目标区域必须水平或垂直扩展源。The destination range must extend the source either horizontally or vertically. 不支持不连续的区域。Discontiguous ranges are not supported.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

autoFill(destinationRange, autoFillTypeString)

填充区域从当前区域到目标区域。Fills range from the current range to the destination range. 目标区域必须水平或垂直扩展源。The destination range must extend the source either horizontally or vertically. 不支持不连续的区域。Discontiguous ranges are not supported.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

calculate()

计算工作表上的单元格区域。Calculates a range of cells on a worksheet.

[API 集: ExcelApi 1.6][ API set: ExcelApi 1.6 ]

clear(applyTo)

清除区域值、格式、填充、边框等。Clear range values, format, fill, border, etc.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

clear(applyToString)

清除区域值、格式、填充、边框等。Clear range values, format, fill, border, etc.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

convertDataTypeToText()

将具有数据类型的区域单元格转换为文本。Converts the range cells with datatypes into text.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

convertToLinkedDataType(serviceID, languageCulture)

将区域单元格转换为工作表中的链接数据类型。Converts the range cells into linked datatype in the worksheet.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

copyFrom(sourceRange, copyType, skipBlanks, transpose)

将单元格数据或格式从源区域或 RangeAreas 复制到当前区域。Copies cell data or formatting from the source range or RangeAreas to the current range. 目标区域的大小可以与源区域或 RangeAreas 的大小不同。The destination range can be of different size than the source range or RangeAreas. 如果目标小于源, 则将自动扩展目标。The destination will be expanded automatically if it is smaller than the source.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

copyFrom(sourceRange, copyTypeString, skipBlanks, transpose)

将单元格数据或格式从源区域或 RangeAreas 复制到当前区域。Copies cell data or formatting from the source range or RangeAreas to the current range. 目标区域的大小可以与源区域或 RangeAreas 的大小不同。The destination range can be of different size than the source range or RangeAreas. 如果目标小于源, 则将自动扩展目标。The destination will be expanded automatically if it is smaller than the source.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

delete(shift)

删除与区域相关的单元格。Deletes the cells associated with the range.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

delete(shiftString)

删除与区域相关的单元格。Deletes the cells associated with the range.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

find(text, criteria)

根据指定的条件查找给定的字符串。Finds the given string based on the criteria specified. 如果当前范围大于单个单元格, 则搜索将限制为该范围, 否则搜索将覆盖该单元格的所有起始工作表。If the current range is larger than a single cell, then the search will be limited to that range, else the search will cover the entire sheet starting after that cell.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

findOrNullObject(text, criteria)

根据指定的条件查找给定的字符串。Finds the given string based on the criteria specified. 如果当前范围大于单个单元格, 则搜索将限制为该范围, 否则搜索将覆盖该单元格的所有起始工作表。If the current range is larger than a single cell, then the search will be limited to that range, else the search will cover the entire sheet starting after that cell. 如果没有匹配项, 则此函数将返回一个 null 对象。If there are no matches, this function will return a null object.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

flashFill()

对当前区域进行快速填充。快速填充在感知到模式时可自动填充数据,因此该区域必须是单列区域且周围有数据以便查找模式。Does FlashFill to current range.Flash Fill will automatically fills data when it senses a pattern, so the range must be single column range and have data around in order to find pattern.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

getAbsoluteResizedRange(numRows, numColumns)

获取一个 Range 对象,该对象的左上单元格与当前 Range 对象相同,但具有指定的行数和列数。Gets a Range object with the same top-left cell as the current Range object, but with the specified numbers of rows and columns.

[API 集: ExcelApi 1.7][ API set: ExcelApi 1.7 ]

getBoundingRect(anotherRange)

获取包含指定区域的最小 range 对象。Gets the smallest range object that encompasses the given ranges. 例如,“B2:C5”和“D10:E15”的 GetBoundingRect 为“B2:E15”。For example, the GetBoundingRect of "B2:C5" and "D10:E15" is "B2:E15".

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

getCell(row, column)

根据行和列编号获取包含单个单元格的 range 对象。Gets the range object containing the single cell based on row and column numbers. 单元格可以位于其父区域的边界之外, 但前提是它停留在工作表网格中。The cell can be outside the bounds of its parent range, so long as it stays within the worksheet grid. 返回的单元格位于相对于区域左上角的单元格的位置。The returned cell is located relative to the top left cell of the range.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

getCellProperties(cellPropertiesLoadOptions)

返回一个 2D 数组,其中封装了每个单元格的字体、填充、边框、对齐方式和其他属性数据。Returns a 2D array, encapsulating the data for each cell's font, fill, borders, alignment, and other properties.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

getColumn(column)

获取范围中包含的列。Gets a column contained in the range.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

getColumnProperties(columnPropertiesLoadOptions)

返回一个一维数组,其中封装了每个列的字体、填充、边框、对齐方式和其他属性数据。Returns a single-dimensional array, encapsulating the data for each column's font, fill, borders, alignment, and other properties. 对于给定列中每个单元格不一致的属性,将返回 null。For properties that are not consistent across each cell within a given column, null will be returned.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

getColumnsAfter(count)

获取当前范围对象右侧的一定数量的列。Gets a certain number of columns to the right of the current Range object.

[API 集: ExcelApi 1.2][ API set: ExcelApi 1.2 ]

getColumnsBefore(count)

获取当前范围对象左侧的一定数量的列。Gets a certain number of columns to the left of the current Range object.

[API 集: ExcelApi 1.2][ API set: ExcelApi 1.2 ]

getEntireColumn()

获取一个对象, 该对象代表区域的整列 (例如, 如果当前区域表示单元格 "B4: E11", 则它getEntireColumn是表示列 "B:E" 的区域)。Gets an object that represents the entire column of the range (for example, if the current range represents cells "B4:E11", its getEntireColumn is a range that represents columns "B:E").

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

getEntireRow()

获取一个对象, 该对象表示区域的整行 (例如, 如果当前区域表示单元格 "B4: E11", 则它GetEntireRow是表示行 "4:11" 的区域)。Gets an object that represents the entire row of the range (for example, if the current range represents cells "B4:E11", its GetEntireRow is a range that represents rows "4:11").

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

getImage()

将区域呈现为 base64 编码的 png 图像。Renders the range as a base64-encoded png image.

[API 集: ExcelApi 1.7][ API set: ExcelApi 1.7 ]

getIntersection(anotherRange)

获取表示指定区域的矩形交集的 range 对象。Gets the range object that represents the rectangular intersection of the given ranges.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

getIntersectionOrNullObject(anotherRange)

获取表示指定区域的矩形交集的 range 对象。Gets the range object that represents the rectangular intersection of the given ranges. 如果找不到任何交集,则此方法返回空对象。If no intersection is found, will return a null object.

[API 集: ExcelApi 1.4][ API set: ExcelApi 1.4 ]

getLastCell()

获取区域内的最后一个单元格。Gets the last cell within the range. 例如,“B2:D5”的最后一个单元格是“D5”。For example, the last cell of "B2:D5" is "D5".

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

getLastColumn()

获取区域内的最后一列。Gets the last column within the range. 例如,“B2:D5”的最后一列是“D2:D5”。For example, the last column of "B2:D5" is "D2:D5".

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

getLastRow()

获取区域内的最后一行。Gets the last row within the range. 例如,“B2:D5”的最后一行是“B5:D5”。For example, the last row of "B2:D5" is "B5:D5".

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

getOffsetRange(rowOffset, columnOffset)

获取表示与指定区域偏移的区域的对象。返回的区域的尺寸将与此区域一致。如果强制在工作表网格的边界之外生成区域,将引发错误。Gets an object which represents a range that's offset from the specified range. The dimension of the returned range will match this range. If the resulting range is forced outside the bounds of the worksheet grid, an error will be thrown.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

getResizedRange(deltaRows, deltaColumns)

获取与当前范围对象类似的范围对象,但其右下角可通过一定数量的行和列进行展开(或合拢)。Gets a Range object similar to the current Range object, but with its bottom-right corner expanded (or contracted) by some number of rows and columns.

[API 集: ExcelApi 1.2][ API set: ExcelApi 1.2 ]

getRow(row)

获取范围中包含的行。Gets a row contained in the range.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

getRowProperties(rowPropertiesLoadOptions)

返回一个一维数组,其中封装了每个行的字体、填充、边框、对齐方式和其他属性数据。Returns a single-dimensional array, encapsulating the data for each row's font, fill, borders, alignment, and other properties. 对于给定行中每个单元格不一致的属性,将返回 null。For properties that are not consistent across each cell within a given row, null will be returned.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

getRowsAbove(count)

获取当前范围对象上方的一定数量的行。Gets a certain number of rows above the current Range object.

[API 集: ExcelApi 1.2][ API set: ExcelApi 1.2 ]

getRowsBelow(count)

获取当前范围对象下方的一定数量的行。Gets a certain number of rows below the current Range object.

[API 集: ExcelApi 1.2][ API set: ExcelApi 1.2 ]

getSpecialCells(cellType, cellValueType)

获取包含一个或多个矩形区域的 RangeAreas 对象,它表示匹配指定类型和值的所有单元格。Gets the RangeAreas object, comprising one or more rectangular ranges, that represents all the cells that match the specified type and value. 如果找不到特殊单元格, 则将引发 ItemNotFound 错误。If no special cells are found, an ItemNotFound error will be thrown.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

getSpecialCells(cellTypeString, cellValueType)

获取包含一个或多个矩形区域的 RangeAreas 对象,它表示匹配指定类型和值的所有单元格。Gets the RangeAreas object, comprising one or more rectangular ranges, that represents all the cells that match the specified type and value. 如果找不到特殊单元格, 则将引发 ItemNotFound 错误。If no special cells are found, an ItemNotFound error will be thrown.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

getSpecialCellsOrNullObject(cellType, cellValueType)

获取包含一个或多个区域的 RangeAreas 对象,它表示匹配指定类型和值的所有单元格。Gets the RangeAreas object, comprising one or more ranges, that represents all the cells that match the specified type and value. 如果找不到特殊单元格, 则返回 null 对象。If no special cells are found, a null object will be returned.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

getSpecialCellsOrNullObject(cellTypeString, cellValueType)

获取包含一个或多个区域的 RangeAreas 对象,它表示匹配指定类型和值的所有单元格。Gets the RangeAreas object, comprising one or more ranges, that represents all the cells that match the specified type and value. 如果找不到特殊单元格, 则返回 null 对象。If no special cells are found, a null object will be returned.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

getSpillingToRange()

获取 Range 对象,它在调用定位单元格时包含溢出区域。Gets the range object containing the spill range when called on an anchor cell. 如果应用于具有多个单元格的区域,则会失败。Fails if applied to a range with more than one cell. 只读。Read-only.

[API 集: EXCELAPI BETA (仅限预览)][ API set: ExcelApi BETA (PREVIEW ONLY) ]

getSpillingToRangeOrNullObject()

获取 Range 对象,它在调用定位单元格时包含溢出区域。Gets the range object containing the spill range when called on an anchor cell. 只读。Read-only. 如果区域不是定位单元格或溢出区域, 则返回 null 对象。If the range is not an anchor cell or spill range can't be found, a null object will be returned.

[API 集: EXCELAPI BETA (仅限预览)][ API set: ExcelApi BETA (PREVIEW ONLY) ]

getSpillParent()

获取 Range 对象,它包含要将某个单元格溢出到的定位单元格。Gets the range object containing the anchor cell for a cell getting spilled into. 如果应用于具有多个单元格的区域,则会失败。Fails if applied to a range with more than one cell. 只读。Read-only.

[API 集: EXCELAPI BETA (仅限预览)][ API set: ExcelApi BETA (PREVIEW ONLY) ]

getSpillParentOrNullObject()

获取 Range 对象,它包含要将某个单元格溢出到的定位单元格。Gets the range object containing the anchor cell for a cell getting spilled into. 只读。Read-only. 如果不是溢出单元格或多个单元格给出了一个值, 则返回 null 对象。If it is not a spill cell or more than once cells are give, a null object will be returned.

[API 集: EXCELAPI BETA (仅限预览)][ API set: ExcelApi BETA (PREVIEW ONLY) ]

getSurroundingRegion()

返回一个 Range 对象,该对象表示此区域左上单元格的周围区域。Returns a Range object that represents the surrounding region for the top-left cell in this range. 周围区域是由相对于该区域的空白行和空白列的任何组合所限定的区域。A surrounding region is a range bounded by any combination of blank rows and blank columns relative to this range.

[API 集: ExcelApi 1.7][ API set: ExcelApi 1.7 ]

getTables(fullyContained)

获取与区域重叠的限定范围的表格集合。Gets a scoped collection of tables that overlap with the range.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

getUsedRange(valuesOnly)

返回指定 Range 对象的所用区域。如果区域内没有使用单元格,此函数将引发 ItemNotFound 错误。Returns the used range of the given range object. If there are no used cells within the range, this function will throw an ItemNotFound error.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

getUsedRangeOrNullObject(valuesOnly)

返回指定 Range 对象的所用区域。如果区域内没有使用单元格,此函数将返回 NULL 对象。Returns the used range of the given range object. If there are no used cells within the range, this function will return a null object.

[API 集: ExcelApi 1.4][ API set: ExcelApi 1.4 ]

getVisibleView()

表示当前 range 对象的可见行。Represents the visible rows of the current range.

[API 集: ExcelApi 1.3][ API set: ExcelApi 1.3 ]

insert(shift)

将单个单元格或一系列单元格插入到工作表中取代此区域,并移动其他单元格以留出空间。在现在空白的空间返回新的 Range 对象。Inserts a cell or a range of cells into the worksheet in place of this range, and shifts the other cells to make space. Returns a new Range object at the now blank space.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

insert(shiftString)

将单个单元格或一系列单元格插入到工作表中取代此区域,并移动其他单元格以留出空间。在现在空白的空间返回新的 Range 对象。Inserts a cell or a range of cells into the worksheet in place of this range, and shifts the other cells to make space. Returns a new Range object at the now blank space.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

load(option)

将命令加入队列以加载对象的指定属性。Queues up a command to load the specified properties of the object. 在读取属性之前,你必须调用“context.sync()”。You must call "context.sync()" before reading the properties.

load(propertyNames)

将命令加入队列以加载对象的指定属性。Queues up a command to load the specified properties of the object. 在读取属性context.sync()之前, 必须先调用。You must call context.sync() before reading the properties.

load(propertyNamesAndPaths)

将命令加入队列以加载对象的指定属性。Queues up a command to load the specified properties of the object. 在读取属性context.sync()之前, 必须先调用。You must call context.sync() before reading the properties.

merge(across)

在工作表中,将 range 单元格合并到一个区域中。Merge the range cells into one region in the worksheet.

[API 集: ExcelApi 1.2][ API set: ExcelApi 1.2 ]

removeDuplicates(columns, includesHeader)

从列指定的区域中删除重复值。Removes duplicate values from the range specified by the columns.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

replaceAll(text, replacement, criteria)

根据当前区域内指定的条件查找并替换给定的字符串。Finds and replaces the given string based on the criteria specified within the current range.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

select()

在 Excel UI 中选择指定的区域。Selects the specified range in the Excel UI.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

set(properties, options)

同时设置一个对象的多个属性。Sets multiple properties of an object at the same time. 您可以传递具有相应属性的纯对象或相同类型的其他 API 对象。You can pass either a plain object with the appropriate properties, or another API object of the same type.

set(properties)

基于现有加载的对象同时设置该对象的多个属性。Sets multiple properties on the object at the same time, based on an existing loaded object.

setCellProperties(cellPropertiesData)

根据单元格属性的 2D 数组更新区域,它封装了字体、填充、边框、对齐方式等内容。Updates the range based on a 2D array of cell properties , encapsulating things like font, fill, borders, alignment, and so forth.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

setColumnProperties(columnPropertiesData)

根据列属性的一维数组更新区域,它封装了字体、填充、边框、对齐方式等内容。Updates the range based on a single-dimensional array of column properties, encapsulating things like font, fill, borders, alignment, and so forth.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

setDirty()

设置下一次重新计算发生时要重新计算的区域。Set a range to be recalculated when the next recalculation occurs.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

setRowProperties(rowPropertiesData)

根据行属性的一维数组更新区域,它封装了字体、填充、边框、对齐方式等内容。Updates the range based on a single-dimensional array of row properties, encapsulating things like font, fill, borders, alignment, and so forth.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

showCard()

显示活动单元格的卡片(如果该单元格具有富值内容)。Displays the card for an active cell if it has rich value content.

[API 集: ExcelApi 1.7][ API set: ExcelApi 1.7 ]

toJSON()

重写 JavaScript toJSON()方法, 以便在将 API 对象传递到时提供更有用的输出JSON.stringify()Overrides the JavaScript toJSON() method in order to provide more useful output when an API object is passed to JSON.stringify(). (JSON.stringify反过来, 也会调用传递toJSON给它的对象的方法。尽管原始的 Excel Range 对象是 API 对象, 但toJSON方法返回一个简单的 JavaScript 对象 (类型为Excel.Interfaces.RangeData, in turn, calls the toJSON method of the object that is passed to it.) Whereas the original Excel.Range object is an API object, the toJSON method returns a plain JavaScript object (typed as Excel.Interfaces.RangeData), 其中包含原始对象中所有已加载的子属性的浅表副本。) that contains shallow copies of any loaded child properties from the original object.

track()

根据文档中的相应更改来跟踪对象,以便进行自动调整。Track the object for automatic adjustment based on surrounding changes in the document. 此调用是 context.trackedObjects.add(thisObject) 的缩写。This call is a shorthand for context.trackedObjects.add(thisObject). 如果你在“.sync”调用之间和按顺序执行“.run”批处理之外使用此对象,并且在对象上设置属性或调用方法时出现“InvalidObjectPath”错误,则需要在首次创建对象时为跟踪的对象集合添加对象。If you are using this object across ".sync" calls and outside the sequential execution of a ".run" batch, and get an "InvalidObjectPath" error when setting a property or invoking a method on the object, you needed to have added the object to the tracked object collection when the object was first created.

unmerge()

将范围单元格取消合并为各个单元格。Unmerge the range cells into separate cells.

[API 集: ExcelApi 1.2][ API set: ExcelApi 1.2 ]

untrack()

释放与此对象关联的内存(如果先前已跟踪过)。Release the memory associated with this object, if it has previously been tracked. 此调用是 context.trackedObjects.add(thisObject) 的缩写。This call is shorthand for context.trackedObjects.remove(thisObject). 拥有许多跟踪对象会降低主机应用程序的速度,因此请在使用完毕后释放所添加的任何对象。Having many tracked objects slows down the host application, so please remember to free any objects you add, once you're done using them. 在内存释放生效之前,你需要调用“context.sync()”。You will need to call "context.sync()" before the memory release takes effect.

属性详细信息

address

表示 A1 样式的区域引用。Represents the range reference in A1-style. Address 值将包含工作表引用 (例如, "Sheet1!A1: B4 ")。Address value will contain the Sheet reference (e.g. "Sheet1!A1:B4"). 只读。Read-only.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

readonly address: string;

属性值

string

addressLocal

以用户语言表示对指定区域的区域引用。Represents range reference for the specified range in the language of the user. 只读。Read-only.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

readonly addressLocal: string;

属性值

string

cellCount

范围中的单元格数。Number of cells in the range. 如果单元格数超过 2^31-1 (2,147,483,647),此 API 返回 -1。This API will return -1 if the cell count exceeds 2^31-1 (2,147,483,647). 只读。Read-only.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

readonly cellCount: number;

属性值

number

columnCount

表示区域中的列总数。Represents the total number of columns in the range. 只读。Read-only.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

readonly columnCount: number;

属性值

number

columnHidden

表示当前 range 的所有列均已隐藏。Represents if all columns of the current range are hidden.

[API 集: ExcelApi 1.2][ API set: ExcelApi 1.2 ]

columnHidden: boolean;

属性值

boolean

columnIndex

表示区域中第一个单元格的列编号。Represents the column number of the first cell in the range. 从零开始编制索引。Zero-indexed. 只读。Read-only.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

readonly columnIndex: number;

属性值

number

conditionalFormats

与该范围相交的 ConditionalFormats 的集合。Collection of ConditionalFormats that intersect the range. 只读。Read-only.

[API 集: ExcelApi 1.6][ API set: ExcelApi 1.6 ]

readonly conditionalFormats: Excel.ConditionalFormatCollection;

属性值

context

与该对象关联的请求上下文。The request context associated with the object. 这会将加载项的进程连接到 Office 主机应用程序的进程。This connects the add-in's process to the Office host application's process.

context: RequestContext;

属性值

RequestContext

dataValidation

返回数据有效性对象。Returns a data validation object.

[API 集: ExcelApi 1.8][ API set: ExcelApi 1.8 ]

readonly dataValidation: Excel.DataValidation;

属性值

format

返回一个格式对象,其中封装了区域的字体、填充、边框、对齐方式和其他属性。Returns a format object, encapsulating the range's font, fill, borders, alignment, and other properties. 只读。Read-only.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

readonly format: Excel.RangeFormat;

属性值

formulas

表示采用 A1 表示法的公式。Represents the formula in A1-style notation. 将公式设置为区域时, value 参数可以是单个值 (字符串), 也可以是二维数组。When setting formulas to a range, the value argument can be either a single value (a string) or a two-dimensional array. 如果参数是单个值, 它将应用于区域中的所有单元格。If the argument is a single value, it will be applied to all cells in the range.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

formulas: any[][];

属性值

any[][]

formulasLocal

表示采用 A1 样式表示法的公式,使用用户的语言和数字格式区域设置。Represents the formula in A1-style notation, in the user's language and number-formatting locale. 例如,英语中的公式 "=SUM(A1, 1.5)" 在德语中将变为 "=SUMME(A1; 1,5)"。For example, the English "=SUM(A1, 1.5)" formula would become "=SUMME(A1; 1,5)" in German. 将公式设置为区域时, value 参数可以是单个值 (字符串), 也可以是二维数组。When setting formulas to a range, the value argument can be either a single value (a string) or a two-dimensional array. 如果参数是单个值, 它将应用于区域中的所有单元格。If the argument is a single value, it will be applied to all cells in the range.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

formulasLocal: any[][];

属性值

any[][]

formulasR1C1

表示采用 R1C1 表示法的公式。Represents the formula in R1C1-style notation. 将公式设置为区域时, value 参数可以是单个值 (字符串), 也可以是二维数组。When setting formulas to a range, the value argument can be either a single value (a string) or a two-dimensional array. 如果参数是单个值, 它将应用于区域中的所有单元格。If the argument is a single value, it will be applied to all cells in the range.

[API 集: ExcelApi 1.2][ API set: ExcelApi 1.2 ]

formulasR1C1: any[][];

属性值

any[][]

hasSpill

备注

此 API 以预览状态提供给开发者,可能根据我们收到的反馈更改。 请勿在生产环境中使用此 API。

表示所有单元格是否都具有溢出边框。Represents if all cells have a spill border. 如果所有单元格都有溢出边框, 则返回 true; 如果所有单元格都没有溢出边框, 则返回 false。Returns true if all cells have a spill border, or false if all cells do not have a spill border. 如果区域中有和没有溢出边框的单元格, 则返回 null。Returns null if there are cells both with and without spill borders within the range.

[API 集: EXCELAPI BETA (仅限预览)][ API set: ExcelApi BETA (PREVIEW ONLY) ]

readonly hasSpill: boolean;

属性值

boolean

height

备注

此 API 以预览状态提供给开发者,可能根据我们收到的反馈更改。 请勿在生产环境中使用此 API。

返回从区域的上边缘到区域的下边缘的 100% 缩放的距离(以磅为单位)。Returns the distance in points, for 100% zoom, from top edge of the range to bottom edge of the range. 只读。Read-only.

[API 集: EXCELAPI BETA (仅限预览)][ API set: ExcelApi BETA (PREVIEW ONLY) ]

readonly height: number;

属性值

number

hidden

表示当前区域中的所有单元格是否隐藏。Represents if all cells of the current range are hidden. 只读。Read-only.

[API 集: ExcelApi 1.2][ API set: ExcelApi 1.2 ]

readonly hidden: boolean;

属性值

boolean

表示当前区域的超链接。Represents the hyperlink for the current range.

[API 集: ExcelApi 1.7][ API set: ExcelApi 1.7 ]

hyperlink: Excel.RangeHyperlink;

属性值

示例Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Orders");

    let productsRange = sheet.getRange("A3:A5");
    productsRange.load("values");

    await context.sync();

    // Create a hyperlink to a URL 
    // for each product name in the first table.
    for (let i = 0; i < productsRange.values.length; i++) {
        let cellRange = productsRange.getCell(i, 0);
        let cellText = productsRange.values[i][0];

        let hyperlink = {
            textToDisplay: cellText,
            screenTip: "Search Bing for '" + cellText + "'",
            address: "https://www.bing.com?q=" + cellText
        }
        cellRange.hyperlink = hyperlink;
    }

    await context.sync();
});

isEntireColumn

表示当前区域是否为整列。Represents if the current range is an entire column. 只读。Read-only.

[API 集: ExcelApi 1.7][ API set: ExcelApi 1.7 ]

readonly isEntireColumn: boolean;

属性值

boolean

isEntireRow

表示当前区域是否为整行。Represents if the current range is an entire row. 只读。Read-only.

[API 集: ExcelApi 1.7][ API set: ExcelApi 1.7 ]

readonly isEntireRow: boolean;

属性值

boolean

left

备注

此 API 以预览状态提供给开发者,可能根据我们收到的反馈更改。 请勿在生产环境中使用此 API。

返回从工作表的左边缘到区域的左边缘的 100% 缩放的距离(以磅为单位)。Returns the distance in points, for 100% zoom, from left edge of the worksheet to left edge of the range. 只读。Read-only.

[API 集: EXCELAPI BETA (仅限预览)][ API set: ExcelApi BETA (PREVIEW ONLY) ]

readonly left: number;

属性值

number

linkedDataTypeState

表示每个单元格的数据类型状态。Represents the data type state of each cell. 只读。Read-only.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

readonly linkedDataTypeState: Excel.LinkedDataTypeState[][];

属性值

Excel.LinkedDataTypeState[][]

numberFormat

表示给定范围的 Excel 数字格式代码。Represents Excel's number format code for the given range. 将数字格式设置为一个区域时, value 参数可以是单个值 (string), 也可以是二维数组。When setting number format to a range, the value argument can be either a single value (string) or a two-dimensional array. 如果参数是单个值, 它将应用于区域中的所有单元格。If the argument is a single value, it will be applied to all cells in the range.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

numberFormat: any[][];

属性值

any[][]

numberFormatLocal

表示 Excel 中的给定区域的数字格式代码,以用户语言的字符串表示。Represents Excel's number format code for the given range as a string in the language of the user. 将数字格式设置为局部区域时, value 参数可以是单个值 (字符串), 也可以是二维数组。When setting number format local to a range, the value argument can be either a single value (string) or a two-dimensional array. 如果参数是单个值, 它将应用于区域中的所有单元格。If the argument is a single value, it will be applied to all cells in the range.

[API 集: ExcelApi 1.7][ API set: ExcelApi 1.7 ]

numberFormatLocal: any[][];

属性值

any[][]

rowCount

返回区域中的总行数。Returns the total number of rows in the range. 只读。Read-only.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

readonly rowCount: number;

属性值

number

rowHidden

表示当前 range 的所有行均已隐藏。Represents if all rows of the current range are hidden.

[API 集: ExcelApi 1.2][ API set: ExcelApi 1.2 ]

rowHidden: boolean;

属性值

boolean

rowIndex

返回区域中第一个单元格的行编号。Returns the row number of the first cell in the range. 从零开始编制索引。Zero-indexed. 只读。Read-only.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

readonly rowIndex: number;

属性值

number

sort

表示当前 range 的区域排序。Represents the range sort of the current range. 只读。Read-only.

[API 集: ExcelApi 1.2][ API set: ExcelApi 1.2 ]

readonly sort: Excel.RangeSort;

属性值

style

表示当前区域的样式。Represents the style of the current range. 如果单元格的样式不一致, 则将返回 null。If the styles of the cells are inconsistent, null will be returned. 对于自定义样式, 将返回样式名称。For custom styles, the style name will be returned. 对于内置样式, 将返回一个表示 BuiltInStyle 枚举中的值的字符串。For built-in styles, a string representing a value in the BuiltInStyle enum will be returned.

[API 集: ExcelApi 1.7][ API set: ExcelApi 1.7 ]

style: string;

属性值

string

示例Examples

await Excel.run(async (context) => {
    let worksheet = context.workbook.worksheets.getItem("Sample");
    let range = worksheet.getRange("A1:E1");

    // Apply built-in style. 
    // Styles are in the Home tab ribbon.
    range.style = Excel.BuiltInStyle.neutral;
    range.format.horizontalAlignment = "Right";

    await context.sync();
});

text

指定区域的文本值。Text values of the specified range. 文本值与单元格宽度无关。The Text value will not depend on the cell width. Excel # UI 中发生的符号替换不会影响 API 返回的文本值。The # sign substitution that happens in Excel UI will not affect the text value returned by the API. 只读。Read-only.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

readonly text: string[][];

属性值

string[][]

top

备注

此 API 以预览状态提供给开发者,可能根据我们收到的反馈更改。 请勿在生产环境中使用此 API。

返回从工作表的上边缘到区域的上边缘的 100% 缩放的距离(以磅为单位)。Returns the distance in points, for 100% zoom, from top edge of the worksheet to top edge of the range. 只读。Read-only.

[API 集: EXCELAPI BETA (仅限预览)][ API set: ExcelApi BETA (PREVIEW ONLY) ]

readonly top: number;

属性值

number

values

表示指定区域的原始值。Represents the raw values of the specified range. 返回的数据可能是字符串、数字,也可能是布尔值。The data returned could be of type string, number, or a boolean. 包含错误的单元格将返回错误字符串。Cells that contain an error will return the error string. 将值设置为一个范围时, value 参数可以是单个值 (string、number 或 boolean), 也可以是一个二维数组。When setting values to a range, the value argument can be either a single value (string, number or boolean) or a two-dimensional array. 如果参数是单个值, 它将应用于区域中的所有单元格。If the argument is a single value, it will be applied to all cells in the range.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

values: any[][];

属性值

any[][]

valueTypes

表示每个单元格的数据类型。Represents the type of data of each cell. 只读。Read-only.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

readonly valueTypes: Excel.RangeValueType[][];

属性值

Excel.RangeValueType[][]

width

备注

此 API 以预览状态提供给开发者,可能根据我们收到的反馈更改。 请勿在生产环境中使用此 API。

返回从区域的左边缘到区域的右边缘的 100% 缩放的距离(以磅为单位)。Returns the distance in points, for 100% zoom, from left edge of the range to right edge of the range. 只读。Read-only.

[API 集: EXCELAPI BETA (仅限预览)][ API set: ExcelApi BETA (PREVIEW ONLY) ]

readonly width: number;

属性值

number

worksheet

包含当前区域的工作表。The worksheet containing the current range. 只读。Read-only.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

readonly worksheet: Excel.Worksheet;

属性值

方法详细信息

autoFill(destinationRange, autoFillType)

填充区域从当前区域到目标区域。Fills range from the current range to the destination range. 目标区域必须水平或垂直扩展源。The destination range must extend the source either horizontally or vertically. 不支持不连续的区域。Discontiguous ranges are not supported.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

autoFill(destinationRange: Range | string, autoFillType?: Excel.AutoFillType): void;

参数

destinationRange
Range | string

要自动填充的目标区域。The destination range to autofill.

autoFillType
Excel.AutoFillType

自动填充的类型。The type of autofill. 根据当前区域的内容, 指定目标区域的填充方式。Specifies how the destination range is to be filled, based on the contents of the current range. 默认值为 "FillDefault"。Default is "FillDefault".

返回

void

autoFill(destinationRange, autoFillTypeString)

填充区域从当前区域到目标区域。Fills range from the current range to the destination range. 目标区域必须水平或垂直扩展源。The destination range must extend the source either horizontally or vertically. 不支持不连续的区域。Discontiguous ranges are not supported.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

autoFill(destinationRange: Range | string, autoFillTypeString?: "FillDefault" | "FillCopy" | "FillSeries" | "FillFormats" | "FillValues" | "FillDays" | "FillWeekdays" | "FillMonths" | "FillYears" | "LinearTrend" | "GrowthTrend" | "FlashFill"): void;

参数

destinationRange
Range | string

要自动填充的目标区域。The destination range to autofill.

autoFillTypeString
"FillDefault" | "FillCopy" | "FillSeries" | "FillFormats" | "FillValues" | "FillDays" | "FillWeekdays" | "FillMonths" | "FillYears" | "LinearTrend" | "GrowthTrend" | "FlashFill"

自动填充的类型。The type of autofill. 根据当前区域的内容, 指定目标区域的填充方式。Specifies how the destination range is to be filled, based on the contents of the current range. 默认值为 "FillDefault"。Default is "FillDefault".

返回

void

calculate()

计算工作表上的单元格区域。Calculates a range of cells on a worksheet.

[API 集: ExcelApi 1.6][ API set: ExcelApi 1.6 ]

calculate(): void;

返回

void

clear(applyTo)

清除区域值、格式、填充、边框等。Clear range values, format, fill, border, etc.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

clear(applyTo?: Excel.ClearApplyTo): void;

参数

applyTo
Excel.ClearApplyTo

可选。Optional. 确定清除操作的类型。Determines the type of clear action. 有关详细信息, 请参阅 ClearApplyTo。See Excel.ClearApplyTo for details.

返回

void

示例Examples

// Below example clears format and contents of the range. 
Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "D:F";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.clear();
    return ctx.sync(); 
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

clear(applyToString)

清除区域值、格式、填充、边框等。Clear range values, format, fill, border, etc.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

clear(applyToString?: "All" | "Formats" | "Contents" | "Hyperlinks" | "RemoveHyperlinks"): void;

参数

applyToString
"All" | "Formats" | "Contents" | "Hyperlinks" | "RemoveHyperlinks"

可选。Optional. 确定清除操作的类型。Determines the type of clear action. 有关详细信息, 请参阅 ClearApplyTo。See Excel.ClearApplyTo for details.

返回

void

convertDataTypeToText()

将具有数据类型的区域单元格转换为文本。Converts the range cells with datatypes into text.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

convertDataTypeToText(): void;

返回

void

convertToLinkedDataType(serviceID, languageCulture)

将区域单元格转换为工作表中的链接数据类型。Converts the range cells into linked datatype in the worksheet.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

convertToLinkedDataType(serviceID: number, languageCulture: string): void;

参数

serviceID
number

将用于查询数据的服务 ID。The Service ID which will be used to query the data.

languageCulture
string

要为其查询服务的语言区域性。Language Culture to query the service for.

返回

void

copyFrom(sourceRange, copyType, skipBlanks, transpose)

将单元格数据或格式从源区域或 RangeAreas 复制到当前区域。Copies cell data or formatting from the source range or RangeAreas to the current range. 目标区域的大小可以与源区域或 RangeAreas 的大小不同。The destination range can be of different size than the source range or RangeAreas. 如果目标小于源, 则将自动扩展目标。The destination will be expanded automatically if it is smaller than the source.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

copyFrom(sourceRange: Range | RangeAreas | string, copyType?: Excel.RangeCopyType, skipBlanks?: boolean, transpose?: boolean): void;

参数

sourceRange
Range | RangeAreas | string

要从其复制的源区域或 RangeAreas。The source range or RangeAreas to copy from. 当源 RangeAreas 有多个区域时, 它必须在大纲形式中, 可以通过删除矩形区域中的完整行或列来创建。When the source RangeAreas has multiple ranges, it must in the outline form which can be created by removing full rows or columns from a rectangular range.

copyType
Excel.RangeCopyType

要复制的单元格数据的类型或格式。The type of cell data or formatting to copy over. 默认值为 "All"。Default is "All".

skipBlanks
boolean

如果为 True, 则在源区域中跳过空白单元格。True if to skip blank cells in the source range. 默认为 false。Default is false.

transpose
boolean

如此如果要转置目标区域中的单元格。True if to transpose the cells in the destination range. 默认为 false。Default is false.

返回

void

示例Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    sheet.getRange("F2").values = [["Copied Formula"]];
    sheet.getRange("F2").format.autofitColumns();

    // copy a range preserving the formulas
    // note: non-formula values are copied over as is
    sheet.getRange("G2").copyFrom("A1:E1", Excel.RangeCopyType.formulas);
    await context.sync();
});

copyFrom(sourceRange, copyTypeString, skipBlanks, transpose)

将单元格数据或格式从源区域或 RangeAreas 复制到当前区域。Copies cell data or formatting from the source range or RangeAreas to the current range. 目标区域的大小可以与源区域或 RangeAreas 的大小不同。The destination range can be of different size than the source range or RangeAreas. 如果目标小于源, 则将自动扩展目标。The destination will be expanded automatically if it is smaller than the source.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

copyFrom(sourceRange: Range | RangeAreas | string, copyTypeString?: "All" | "Formulas" | "Values" | "Formats", skipBlanks?: boolean, transpose?: boolean): void;

参数

sourceRange
Range | RangeAreas | string

要从其复制的源区域或 RangeAreas。The source range or RangeAreas to copy from. 当源 RangeAreas 有多个区域时, 它必须在大纲形式中, 可以通过删除矩形区域中的完整行或列来创建。When the source RangeAreas has multiple ranges, it must in the outline form which can be created by removing full rows or columns from a rectangular range.

copyTypeString
"All" | "Formulas" | "Values" | "Formats"

要复制的单元格数据的类型或格式。The type of cell data or formatting to copy over. 默认值为 "All"。Default is "All".

skipBlanks
boolean

如果为 True, 则在源区域中跳过空白单元格。True if to skip blank cells in the source range. 默认为 false。Default is false.

transpose
boolean

如此如果要转置目标区域中的单元格。True if to transpose the cells in the destination range. 默认为 false。Default is false.

返回

void

delete(shift)

删除与区域相关的单元格。Deletes the cells associated with the range.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

delete(shift: Excel.DeleteShiftDirection): void;

参数

shift
Excel.DeleteShiftDirection

指定移动单元格的方式。Specifies which way to shift the cells. 有关详细信息, 请参阅 DeleteShiftDirection。See Excel.DeleteShiftDirection for details.

返回

void

delete(shiftString)

删除与区域相关的单元格。Deletes the cells associated with the range.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

delete(shiftString: "Up" | "Left"): void;

参数

shiftString
"Up" | "Left"

指定移动单元格的方式。Specifies which way to shift the cells. 有关详细信息, 请参阅 DeleteShiftDirection。See Excel.DeleteShiftDirection for details.

返回

void

示例Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "D:F";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.delete("Left");
    return ctx.sync(); 
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

find(text, criteria)

根据指定的条件查找给定的字符串。Finds the given string based on the criteria specified. 如果当前范围大于单个单元格, 则搜索将限制为该范围, 否则搜索将覆盖该单元格的所有起始工作表。If the current range is larger than a single cell, then the search will be limited to that range, else the search will cover the entire sheet starting after that cell.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

find(text: string, criteria: Excel.SearchCriteria): Excel.Range;

参数

text
string

要查找的字符串。The string to find.

criteria
Excel.SearchCriteria

其他搜索条件, 包括搜索方向以及搜索是否需要匹配整个单元格或是否区分大小写。Additional search criteria, including the search direction and whether the search needs to match the entire cell or be case sensitive.

返回

与搜索条件匹配的范围。The Range which matched the search criteria.

示例Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const table = sheet.tables.getItem("ExpensesTable");
    const searchRange = table.getRange();

    // NOTE: If no match is found, an ItemNotFound error
    // is thrown when Range.find is evaluated.
    const foundRange = searchRange.find($("#searchText").text(), {
        completeMatch: completeMatch,
        matchCase: matchCase,
        searchDirection: searchDirection
    });
    
    foundRange.load("address");
    await context.sync();


    console.log(foundRange.address);
});

findOrNullObject(text, criteria)

根据指定的条件查找给定的字符串。Finds the given string based on the criteria specified. 如果当前范围大于单个单元格, 则搜索将限制为该范围, 否则搜索将覆盖该单元格的所有起始工作表。If the current range is larger than a single cell, then the search will be limited to that range, else the search will cover the entire sheet starting after that cell. 如果没有匹配项, 则此函数将返回一个 null 对象。If there are no matches, this function will return a null object.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

findOrNullObject(text: string, criteria: Excel.SearchCriteria): Excel.Range;

参数

text
string

要查找的字符串。The string to find.

criteria
Excel.SearchCriteria

其他搜索条件, 包括搜索方向以及搜索是否需要匹配整个单元格或是否区分大小写。Additional search criteria, including the search direction and whether the search needs to match the entire cell or be case sensitive.

返回

与搜索条件匹配的范围。The Range which matched the search criteria.

示例Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const table = sheet.tables.getItem("ExpensesTable");
    const searchRange = table.getRange();
    const foundRange = searchRange.findOrNullObject($("#searchText").text(), {
        completeMatch: completeMatch,
        matchCase: matchCase,
        searchDirection: searchDirection
    });
    
    foundRange.load("address");
    await context.sync();

    if (foundRange.isNullObject) {
        console.log("Text not found");
    } else {
        console.log(foundRange.address);
    }
});

flashFill()

对当前区域进行快速填充。快速填充在感知到模式时可自动填充数据,因此该区域必须是单列区域且周围有数据以便查找模式。Does FlashFill to current range.Flash Fill will automatically fills data when it senses a pattern, so the range must be single column range and have data around in order to find pattern.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

flashFill(): void;

返回

void

getAbsoluteResizedRange(numRows, numColumns)

获取一个 Range 对象,该对象的左上单元格与当前 Range 对象相同,但具有指定的行数和列数。Gets a Range object with the same top-left cell as the current Range object, but with the specified numbers of rows and columns.

[API 集: ExcelApi 1.7][ API set: ExcelApi 1.7 ]

getAbsoluteResizedRange(numRows: number, numColumns: number): Excel.Range;

参数

numRows
number

新范围大小的行数。The number of rows of the new range size.

numColumns
number

新范围大小的列数。The number of columns of the new range size.

返回

getBoundingRect(anotherRange)

获取包含指定区域的最小 range 对象。Gets the smallest range object that encompasses the given ranges. 例如,“B2:C5”和“D10:E15”的 GetBoundingRect 为“B2:E15”。For example, the GetBoundingRect of "B2:C5" and "D10:E15" is "B2:E15".

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

getBoundingRect(anotherRange: Range | string): Excel.Range;

参数

anotherRange
Range | string

range 对象或地址或区域名称。The range object or address or range name.

返回

示例Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "D4:G6";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    var range = range.getBoundingRect("G4:H8");
    range.load('address');
    return ctx.sync().then(function() {
        console.log(range.address); // Prints Sheet1!D4:H8
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getCell(row, column)

根据行和列编号获取包含单个单元格的 range 对象。Gets the range object containing the single cell based on row and column numbers. 单元格可以位于其父区域的边界之外, 但前提是它停留在工作表网格中。The cell can be outside the bounds of its parent range, so long as it stays within the worksheet grid. 返回的单元格位于相对于区域左上角的单元格的位置。The returned cell is located relative to the top left cell of the range.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

getCell(row: number, column: number): Excel.Range;

参数

row
number

要检索的单元格的行号。Row number of the cell to be retrieved. 从零开始编制索引。Zero-indexed.

column
number

要检索的单元格的列号。Column number of the cell to be retrieved. 从零开始编制索引。Zero-indexed.

返回

示例Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "A1:F8";
    var worksheet = ctx.workbook.worksheets.getItem(sheetName);
    var range = worksheet.getRange(rangeAddress);
    var cell = range.cell(0,0);
    cell.load('address');
    return ctx.sync().then(function() {
        console.log(cell.address);
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getCellProperties(cellPropertiesLoadOptions)

返回一个 2D 数组,其中封装了每个单元格的字体、填充、边框、对齐方式和其他属性数据。Returns a 2D array, encapsulating the data for each cell's font, fill, borders, alignment, and other properties.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

getCellProperties(cellPropertiesLoadOptions: CellPropertiesLoadOptions): OfficeExtension.ClientResult<CellProperties[][]>;

参数

cellPropertiesLoadOptions
CellPropertiesLoadOptions

一个代表要加载的单元格属性的对象。An object that represents which cell properties to load.

返回

OfficeExtension.ClientResult<CellProperties[][]>

一个二维数组, 其中每一项代表对应单元格的请求属性。A 2D array where each item represents the requested properties of the corresponding cell.

getColumn(column)

获取范围中包含的列。Gets a column contained in the range.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

getColumn(column: number): Excel.Range;

参数

column
number

要检索的区域的列号。Column number of the range to be retrieved. 从零开始编制索引。Zero-indexed.

返回

示例Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet19";
    var rangeAddress = "A1:F8";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress).getColumn(1);
    range.load('address');
    return ctx.sync().then(function() {
        console.log(range.address); // prints Sheet1!B1:B8
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getColumnProperties(columnPropertiesLoadOptions)

返回一个一维数组,其中封装了每个列的字体、填充、边框、对齐方式和其他属性数据。Returns a single-dimensional array, encapsulating the data for each column's font, fill, borders, alignment, and other properties. 对于给定列中每个单元格不一致的属性,将返回 null。For properties that are not consistent across each cell within a given column, null will be returned.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

getColumnProperties(columnPropertiesLoadOptions: ColumnPropertiesLoadOptions): OfficeExtension.ClientResult<ColumnProperties[]>;

参数

columnPropertiesLoadOptions
ColumnPropertiesLoadOptions

一个代表要加载的列属性的对象。An object that represents which column properties to load.

返回

OfficeExtension.ClientResult<ColumnProperties[]>

一个数组, 其中每个项代表相应列的请求的属性。An array where each item represents the requested properties of the corresponding column.

getColumnsAfter(count)

获取当前范围对象右侧的一定数量的列。Gets a certain number of columns to the right of the current Range object.

[API 集: ExcelApi 1.2][ API set: ExcelApi 1.2 ]

getColumnsAfter(count?: number): Excel.Range;

参数

count
number

可选。生成的范围中要包含的列数。一般来说,使用正数可以在当前范围之外创建一个范围。也可以使用负数在当前范围之内创建一个范围。默认值为 1。Optional. The number of columns to include in the resulting range. In general, use a positive number to create a range outside the current range. You can also use a negative number to create a range within the current range. The default value is 1.

返回

getColumnsBefore(count)

获取当前范围对象左侧的一定数量的列。Gets a certain number of columns to the left of the current Range object.

[API 集: ExcelApi 1.2][ API set: ExcelApi 1.2 ]

getColumnsBefore(count?: number): Excel.Range;

参数

count
number

可选。生成的范围中要包含的列数。一般来说,使用正数可以在当前范围之外创建一个范围。也可以使用负数在当前范围之内创建一个范围。默认值为 1。Optional. The number of columns to include in the resulting range. In general, use a positive number to create a range outside the current range. You can also use a negative number to create a range within the current range. The default value is 1.

返回

getEntireColumn()

获取一个对象, 该对象代表区域的整列 (例如, 如果当前区域表示单元格 "B4: E11", 则它getEntireColumn是表示列 "B:E" 的区域)。Gets an object that represents the entire column of the range (for example, if the current range represents cells "B4:E11", its getEntireColumn is a range that represents columns "B:E").

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

getEntireColumn(): Excel.Range;

返回

示例Examples

// Note: the grid properties of the Range (values, numberFormat, formulas) 
// contains null since the Range in question is unbounded.
Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "D:F";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    var rangeEC = range.getEntireColumn();
    rangeEC.load('address');
    return ctx.sync().then(function() {
        console.log(rangeEC.address);
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getEntireRow()

获取一个对象, 该对象表示区域的整行 (例如, 如果当前区域表示单元格 "B4: E11", 则它GetEntireRow是表示行 "4:11" 的区域)。Gets an object that represents the entire row of the range (for example, if the current range represents cells "B4:E11", its GetEntireRow is a range that represents rows "4:11").

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

getEntireRow(): Excel.Range;

返回

示例Examples

// Gets an object that represents the entire row of the range 
// (for example, if the current range represents cells "B4:E11", 
// its GetEntireRow is a range that represents rows "4:11").
Excel.run(function (ctx) {
    var sheetName = "Sheet1";
    var rangeAddress = "D:F"; 
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    var rangeER = range.getEntireRow();
    rangeER.load('address');
    return ctx.sync().then(function() {
        console.log(rangeER.address);
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getImage()

将区域呈现为 base64 编码的 png 图像。Renders the range as a base64-encoded png image.

[API 集: ExcelApi 1.7][ API set: ExcelApi 1.7 ]

getImage(): OfficeExtension.ClientResult<string>;

返回

OfficeExtension.ClientResult<string>

getIntersection(anotherRange)

获取表示指定区域的矩形交集的 range 对象。Gets the range object that represents the rectangular intersection of the given ranges.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

getIntersection(anotherRange: Range | string): Excel.Range;

参数

anotherRange
Range | string

将用于确定区域交集的 range 对象或区域地址。The range object or range address that will be used to determine the intersection of ranges.

返回

示例Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "A1:F8";
    var range = 
        ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress).getIntersection("D4:G6");
    range.load('address');
    return ctx.sync().then(function() {
        console.log(range.address); // prints Sheet1!D4:F6
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getIntersectionOrNullObject(anotherRange)

获取表示指定区域的矩形交集的 range 对象。Gets the range object that represents the rectangular intersection of the given ranges. 如果找不到任何交集,则此方法返回空对象。If no intersection is found, will return a null object.

[API 集: ExcelApi 1.4][ API set: ExcelApi 1.4 ]

getIntersectionOrNullObject(anotherRange: Range | string): Excel.Range;

参数

anotherRange
Range | string

将用于确定区域交集的 range 对象或区域地址。The range object or range address that will be used to determine the intersection of ranges.

返回

示例Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const salesTable = sheet.tables.getItem("SalesTable");
    const dataRange = salesTable.getDataBodyRange();

    // We want the most recent quarter that has data, so
    // exclude quarters without data and get the last of
    // the remaining columns.
    const usedDataRange = dataRange.getUsedRange(true /* valuesOnly */);
    const currentQuarterRange = usedDataRange.getLastColumn();

    // Asian and European teams have separate contests.
    const asianSalesRange = sheet.getRange("A2:E4");
    const europeanSalesRange = sheet.getRange("A5:E7");

    // The data for each chart is the intersection of the
    // current quarter column and the rows for the continent.
    const asianContestRange = asianSalesRange.getIntersectionOrNullObject(currentQuarterRange);
    const europeanContestRange = europeanSalesRange.getIntersectionOrNullObject(currentQuarterRange);

    // Must sync before you can test the output of *OrNullObject
    // method/property.
    await context.sync();

    if (asianContestRange.isNullObject) {
        // See the declaration of this method for how to
        // test this code path.
        reportMissingData("Asian");
    } else {
        createContinentChart(
            sheet,
            "Asian",
            asianContestRange,
            "A9",
            "F24"
        );
    }

    if (europeanContestRange.isNullObject) {
        // See the declaration of this method for how to
        // test this code path.
        reportMissingData("European");
    } else {
        createContinentChart(
            sheet,
            "European",
            europeanContestRange,
            "A25",
            "F40"
        );
    }

    await context.sync();
});

getLastCell()

获取区域内的最后一个单元格。Gets the last cell within the range. 例如,“B2:D5”的最后一个单元格是“D5”。For example, the last cell of "B2:D5" is "D5".

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

getLastCell(): Excel.Range;

返回

示例Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "A1:F8";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress).getLastCell();
    range.load('address');
    return ctx.sync().then(function() {
        console.log(range.address); // prints Sheet1!F8
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getLastColumn()

获取区域内的最后一列。Gets the last column within the range. 例如,“B2:D5”的最后一列是“D2:D5”。For example, the last column of "B2:D5" is "D2:D5".

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

getLastColumn(): Excel.Range;

返回

示例Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "A1:F8";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress).getLastColumn();
    range.load('address');
    return ctx.sync().then(function() {
        console.log(range.address); // prints Sheet1!F1:F8
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getLastRow()

获取区域内的最后一行。Gets the last row within the range. 例如,“B2:D5”的最后一行是“B5:D5”。For example, the last row of "B2:D5" is "B5:D5".

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

getLastRow(): Excel.Range;

返回

示例Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "A1:F8";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress).getLastRow();
    range.load('address');
    return ctx.sync().then(function() {
        console.log(range.address); // prints Sheet1!A8:F8
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getOffsetRange(rowOffset, columnOffset)

获取表示与指定区域偏移的区域的对象。返回的区域的尺寸将与此区域一致。如果强制在工作表网格的边界之外生成区域,将引发错误。Gets an object which represents a range that's offset from the specified range. The dimension of the returned range will match this range. If the resulting range is forced outside the bounds of the worksheet grid, an error will be thrown.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

getOffsetRange(rowOffset: number, columnOffset: number): Excel.Range;

参数

rowOffset
number

区域偏移的行数(正数、负数或 0)。正数表示向下偏移,负数表示向上偏移。The number of rows (positive, negative, or 0) by which the range is to be offset. Positive values are offset downward, and negative values are offset upward.

columnOffset
number

区域偏移的列数(正数、负数或 0)。正数表示向右偏移,负数表示向左偏移。The number of columns (positive, negative, or 0) by which the range is to be offset. Positive values are offset to the right, and negative values are offset to the left.

返回

示例Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "D4:F6";
    var range = 
        ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress).getOffsetRange(-1,4);
    range.load('address');
    return ctx.sync().then(function() {
        console.log(range.address); // prints Sheet1!H3:K5
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getResizedRange(deltaRows, deltaColumns)

获取与当前范围对象类似的范围对象,但其右下角可通过一定数量的行和列进行展开(或合拢)。Gets a Range object similar to the current Range object, but with its bottom-right corner expanded (or contracted) by some number of rows and columns.

[API 集: ExcelApi 1.2][ API set: ExcelApi 1.2 ]

getResizedRange(deltaRows: number, deltaColumns: number): Excel.Range;

参数

deltaRows
number

相对于当前范围,展开右下角的行数。使用正数可展开范围,使用负数可合拢范围。The number of rows by which to expand the bottom-right corner, relative to the current range. Use a positive number to expand the range, or a negative number to decrease it.

deltaColumns
number

相对于当前范围, 从右下角扩展的列数。The number of columns by which to expand the bottom-right corner, relative to the current range. 使用正数可展开范围,使用负数可合拢范围。Use a positive number to expand the range, or a negative number to decrease it.

返回

getRow(row)

获取范围中包含的行。Gets a row contained in the range.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

getRow(row: number): Excel.Range;

参数

row
number

要检索的区域的行号。Row number of the range to be retrieved. 从零开始编制索引。Zero-indexed.

返回

示例Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "A1:F8";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress).getRow(1);
    range.load('address');
    return ctx.sync().then(function() {
        console.log(range.address); // prints Sheet1!A2:F2
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getRowProperties(rowPropertiesLoadOptions)

返回一个一维数组,其中封装了每个行的字体、填充、边框、对齐方式和其他属性数据。Returns a single-dimensional array, encapsulating the data for each row's font, fill, borders, alignment, and other properties. 对于给定行中每个单元格不一致的属性,将返回 null。For properties that are not consistent across each cell within a given row, null will be returned.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

getRowProperties(rowPropertiesLoadOptions: RowPropertiesLoadOptions): OfficeExtension.ClientResult<RowProperties[]>;

参数

rowPropertiesLoadOptions
RowPropertiesLoadOptions

一个代表要加载的行属性的对象。An object that represents which row properties to load.

返回

OfficeExtension.ClientResult<RowProperties[]>

一个数组, 其中每个项代表相应行的请求属性。An array where each item represents the requested properties of the corresponding row.

getRowsAbove(count)

获取当前范围对象上方的一定数量的行。Gets a certain number of rows above the current Range object.

[API 集: ExcelApi 1.2][ API set: ExcelApi 1.2 ]

getRowsAbove(count?: number): Excel.Range;

参数

count
number

可选。生成的范围中要包含的行数。一般来说,使用正数可以在当前范围之外创建一个范围。也可以使用负数在当前范围之内创建一个范围。默认值为 1。Optional. The number of rows to include in the resulting range. In general, use a positive number to create a range outside the current range. You can also use a negative number to create a range within the current range. The default value is 1.

返回

getRowsBelow(count)

获取当前范围对象下方的一定数量的行。Gets a certain number of rows below the current Range object.

[API 集: ExcelApi 1.2][ API set: ExcelApi 1.2 ]

getRowsBelow(count?: number): Excel.Range;

参数

count
number

可选。生成的范围中要包含的行数。一般来说,使用正数可以在当前范围之外创建一个范围。也可以使用负数在当前范围之内创建一个范围。默认值为 1。Optional. The number of rows to include in the resulting range. In general, use a positive number to create a range outside the current range. You can also use a negative number to create a range within the current range. The default value is 1.

返回

getSpecialCells(cellType, cellValueType)

获取包含一个或多个矩形区域的 RangeAreas 对象,它表示匹配指定类型和值的所有单元格。Gets the RangeAreas object, comprising one or more rectangular ranges, that represents all the cells that match the specified type and value. 如果找不到特殊单元格, 则将引发 ItemNotFound 错误。If no special cells are found, an ItemNotFound error will be thrown.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

getSpecialCells(cellType: Excel.SpecialCellType, cellValueType?: Excel.SpecialCellValueType): Excel.RangeAreas;

参数

cellType
Excel.SpecialCellType

要包含的单元格的类型。The type of cells to include.

cellValueType
Excel.SpecialCellValueType

如果 cellType 为常量或公式, 则此参数用于确定要在结果中包含哪些类型的单元格。If cellType is either Constants or Formulas, this argument is used to determine which types of cells to include in the result. 可以将这些值组合在一起, 以返回多个类型。These values can be combined together to return more than one type. 默认情况下,将选择所有常量或公式,无论类型如何。The default is to select all constants or formulas, no matter what the type.

返回

示例Examples

await Excel.run(async (context) => {

    const sheet = context.workbook.worksheets.getActiveWorksheet();
    const usedRange = sheet.getUsedRange();

    // Find the ranges with either text or logical (boolean)
    // values
    const formulaRanges = usedRange.getSpecialCells("Constants", "LogicalText");
    formulaRanges.format.fill.color = "orange";

    return context.sync();
});

getSpecialCells(cellTypeString, cellValueType)

获取包含一个或多个矩形区域的 RangeAreas 对象,它表示匹配指定类型和值的所有单元格。Gets the RangeAreas object, comprising one or more rectangular ranges, that represents all the cells that match the specified type and value. 如果找不到特殊单元格, 则将引发 ItemNotFound 错误。If no special cells are found, an ItemNotFound error will be thrown.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

getSpecialCells(cellTypeString: "ConditionalFormats" | "DataValidations" | "Blanks" | "Constants" | "Formulas" | "SameConditionalFormat" | "SameDataValidation" | "Visible", cellValueType?: "All" | "Errors" | "ErrorsLogical" | "ErrorsNumbers" | "ErrorsText" | "ErrorsLogicalNumber" | "ErrorsLogicalText" | "ErrorsNumberText" | "Logical" | "LogicalNumbers" | "LogicalText" | "LogicalNumbersText" | "Numbers" | "NumbersText" | "Text"): Excel.RangeAreas;

参数

cellTypeString
"ConditionalFormats" | "DataValidations" | "Blanks" | "Constants" | "Formulas" | "SameConditionalFormat" | "SameDataValidation" | "Visible"

要包含的单元格的类型。The type of cells to include.

cellValueType
"All" | "Errors" | "ErrorsLogical" | "ErrorsNumbers" | "ErrorsText" | "ErrorsLogicalNumber" | "ErrorsLogicalText" | "ErrorsNumberText" | "Logical" | "LogicalNumbers" | "LogicalText" | "LogicalNumbersText" | "Numbers" | "NumbersText" | "Text"

如果 cellType 为常量或公式, 则此参数用于确定要在结果中包含哪些类型的单元格。If cellType is either Constants or Formulas, this argument is used to determine which types of cells to include in the result. 可以将这些值组合在一起, 以返回多个类型。These values can be combined together to return more than one type. 默认情况下,将选择所有常量或公式,无论类型如何。The default is to select all constants or formulas, no matter what the type.

返回

getSpecialCellsOrNullObject(cellType, cellValueType)

获取包含一个或多个区域的 RangeAreas 对象,它表示匹配指定类型和值的所有单元格。Gets the RangeAreas object, comprising one or more ranges, that represents all the cells that match the specified type and value. 如果找不到特殊单元格, 则返回 null 对象。If no special cells are found, a null object will be returned.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

getSpecialCellsOrNullObject(cellType: Excel.SpecialCellType, cellValueType?: Excel.SpecialCellValueType): Excel.RangeAreas;

参数

cellType
Excel.SpecialCellType

要包含的单元格的类型。The type of cells to include.

cellValueType
Excel.SpecialCellValueType

如果 cellType 为常量或公式, 则此参数用于确定要在结果中包含哪些类型的单元格。If cellType is either Constants or Formulas, this argument is used to determine which types of cells to include in the result. 可以将这些值组合在一起, 以返回多个类型。These values can be combined together to return more than one type. 默认情况下,将选择所有常量或公式,无论类型如何。The default is to select all constants or formulas, no matter what the type.

返回

getSpecialCellsOrNullObject(cellTypeString, cellValueType)

获取包含一个或多个区域的 RangeAreas 对象,它表示匹配指定类型和值的所有单元格。Gets the RangeAreas object, comprising one or more ranges, that represents all the cells that match the specified type and value. 如果找不到特殊单元格, 则返回 null 对象。If no special cells are found, a null object will be returned.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

getSpecialCellsOrNullObject(cellTypeString: "ConditionalFormats" | "DataValidations" | "Blanks" | "Constants" | "Formulas" | "SameConditionalFormat" | "SameDataValidation" | "Visible", cellValueType?: "All" | "Errors" | "ErrorsLogical" | "ErrorsNumbers" | "ErrorsText" | "ErrorsLogicalNumber" | "ErrorsLogicalText" | "ErrorsNumberText" | "Logical" | "LogicalNumbers" | "LogicalText" | "LogicalNumbersText" | "Numbers" | "NumbersText" | "Text"): Excel.RangeAreas;

参数

cellTypeString
"ConditionalFormats" | "DataValidations" | "Blanks" | "Constants" | "Formulas" | "SameConditionalFormat" | "SameDataValidation" | "Visible"

要包含的单元格的类型。The type of cells to include.

cellValueType
"All" | "Errors" | "ErrorsLogical" | "ErrorsNumbers" | "ErrorsText" | "ErrorsLogicalNumber" | "ErrorsLogicalText" | "ErrorsNumberText" | "Logical" | "LogicalNumbers" | "LogicalText" | "LogicalNumbersText" | "Numbers" | "NumbersText" | "Text"

如果 cellType 为常量或公式, 则此参数用于确定要在结果中包含哪些类型的单元格。If cellType is either Constants or Formulas, this argument is used to determine which types of cells to include in the result. 可以将这些值组合在一起, 以返回多个类型。These values can be combined together to return more than one type. 默认情况下,将选择所有常量或公式,无论类型如何。The default is to select all constants or formulas, no matter what the type.

返回

getSpillingToRange()

备注

此 API 以预览状态提供给开发者,可能根据我们收到的反馈更改。 请勿在生产环境中使用此 API。

获取 Range 对象,它在调用定位单元格时包含溢出区域。Gets the range object containing the spill range when called on an anchor cell. 如果应用于具有多个单元格的区域,则会失败。Fails if applied to a range with more than one cell. 只读。Read-only.

[API 集: EXCELAPI BETA (仅限预览)][ API set: ExcelApi BETA (PREVIEW ONLY) ]

getSpillingToRange(): Excel.Range;

返回

getSpillingToRangeOrNullObject()

备注

此 API 以预览状态提供给开发者,可能根据我们收到的反馈更改。 请勿在生产环境中使用此 API。

获取 Range 对象,它在调用定位单元格时包含溢出区域。Gets the range object containing the spill range when called on an anchor cell. 只读。Read-only. 如果区域不是定位单元格或溢出区域, 则返回 null 对象。If the range is not an anchor cell or spill range can't be found, a null object will be returned.

[API 集: EXCELAPI BETA (仅限预览)][ API set: ExcelApi BETA (PREVIEW ONLY) ]

getSpillingToRangeOrNullObject(): Excel.Range;

返回

getSpillParent()

备注

此 API 以预览状态提供给开发者,可能根据我们收到的反馈更改。 请勿在生产环境中使用此 API。

获取 Range 对象,它包含要将某个单元格溢出到的定位单元格。Gets the range object containing the anchor cell for a cell getting spilled into. 如果应用于具有多个单元格的区域,则会失败。Fails if applied to a range with more than one cell. 只读。Read-only.

[API 集: EXCELAPI BETA (仅限预览)][ API set: ExcelApi BETA (PREVIEW ONLY) ]

getSpillParent(): Excel.Range;

返回

getSpillParentOrNullObject()

备注

此 API 以预览状态提供给开发者,可能根据我们收到的反馈更改。 请勿在生产环境中使用此 API。

获取 Range 对象,它包含要将某个单元格溢出到的定位单元格。Gets the range object containing the anchor cell for a cell getting spilled into. 只读。Read-only. 如果不是溢出单元格或多个单元格给出了一个值, 则返回 null 对象。If it is not a spill cell or more than once cells are give, a null object will be returned.

[API 集: EXCELAPI BETA (仅限预览)][ API set: ExcelApi BETA (PREVIEW ONLY) ]

getSpillParentOrNullObject(): Excel.Range;

返回

getSurroundingRegion()

返回一个 Range 对象,该对象表示此区域左上单元格的周围区域。Returns a Range object that represents the surrounding region for the top-left cell in this range. 周围区域是由相对于该区域的空白行和空白列的任何组合所限定的区域。A surrounding region is a range bounded by any combination of blank rows and blank columns relative to this range.

[API 集: ExcelApi 1.7][ API set: ExcelApi 1.7 ]

getSurroundingRegion(): Excel.Range;

返回

getTables(fullyContained)

获取与区域重叠的限定范围的表格集合。Gets a scoped collection of tables that overlap with the range.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

getTables(fullyContained?: boolean): Excel.TableScopedCollection;

参数

fullyContained
boolean

如果为 true, 则仅返回完全包含在区域边界内的表。If true, returns only tables that are fully contained within the range bounds. 默认值为 false。The default value is false.

返回

getUsedRange(valuesOnly)

返回指定 Range 对象的所用区域。如果区域内没有使用单元格,此函数将引发 ItemNotFound 错误。Returns the used range of the given range object. If there are no used cells within the range, this function will throw an ItemNotFound error.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

getUsedRange(valuesOnly?: boolean): Excel.Range;

参数

valuesOnly
boolean

仅将有值的单元格视为已使用的单元格。Considers only cells with values as used cells. [Api 集: ExcelApi 1。2][Api set: ExcelApi 1.2]

返回

示例Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "D:F";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    var rangeUR = range.getUsedRange();
    rangeUR.load('address');
    return ctx.sync().then(function() {
        console.log(rangeUR.address);
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});
await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const salesTable = sheet.tables.getItem("SalesTable");
    const dataRange = salesTable.getDataBodyRange();

    // We want the most recent quarter that has data, so
    // exclude quarters without data and get the last of
    // the remaining columns.
    const usedDataRange = dataRange.getUsedRange(true /* valuesOnly */);
    const currentQuarterRange = usedDataRange.getLastColumn();

    // Asian and European teams have separate contests.
    const asianSalesRange = sheet.getRange("A2:E4");
    const europeanSalesRange = sheet.getRange("A5:E7");

    // The data for each chart is the intersection of the
    // current quarter column and the rows for the continent.
    const asianContestRange = asianSalesRange.getIntersectionOrNullObject(currentQuarterRange);
    const europeanContestRange = europeanSalesRange.getIntersectionOrNullObject(currentQuarterRange);

    // Must sync before you can test the output of *OrNullObject
    // method/property.
    await context.sync();

    if (asianContestRange.isNullObject) {
        // See the declaration of this method for how to
        // test this code path.
        reportMissingData("Asian");
    } else {
        createContinentChart(
            sheet,
            "Asian",
            asianContestRange,
            "A9",
            "F24"
        );
    }

    if (europeanContestRange.isNullObject) {
        // See the declaration of this method for how to
        // test this code path.
        reportMissingData("European");
    } else {
        createContinentChart(
            sheet,
            "European",
            europeanContestRange,
            "A25",
            "F40"
        );
    }

    await context.sync();
});

getUsedRangeOrNullObject(valuesOnly)

返回指定 Range 对象的所用区域。如果区域内没有使用单元格,此函数将返回 NULL 对象。Returns the used range of the given range object. If there are no used cells within the range, this function will return a null object.

[API 集: ExcelApi 1.4][ API set: ExcelApi 1.4 ]

getUsedRangeOrNullObject(valuesOnly?: boolean): Excel.Range;

参数

valuesOnly
boolean

仅将有值的单元格视为已使用的单元格。Considers only cells with values as used cells.

返回

示例Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const salesTable = sheet.tables.getItem("SalesTable");
    const dataRange = salesTable.getDataBodyRange();

    // Pass true so only cells with values count as used
    const usedDataRange = dataRange.getUsedRangeOrNullObject(
        true /* valuesOnly */
    );

    //Must sync before reading value returned from *OrNullObject method/property.
    await context.sync();

    if (usedDataRange.isNullObject) {
        console.log("Need Data to Make Chart");
        console.log("To create a meaningful chart, press 'Fill the table' (or add names to the Product column and numbers to some of the other cells). Then press 'Try to create chart' again.");
    } else {
        const chart = sheet.charts.add(
            Excel.ChartType.columnClustered,
            dataRange,
            "Columns"
        );
        chart.setPosition("A15", "F30");
        chart.title.text = "Quarterly sales chart";
        chart.legend.position = "Right";
        chart.legend.format.fill.setSolidColor("white");
        chart.dataLabels.format.font.size = 15;
        chart.dataLabels.format.font.color = "black";
    }

    await context.sync();
});

getVisibleView()

表示当前 range 对象的可见行。Represents the visible rows of the current range.

[API 集: ExcelApi 1.3][ API set: ExcelApi 1.3 ]

getVisibleView(): Excel.RangeView;

返回

insert(shift)

将单个单元格或一系列单元格插入到工作表中取代此区域,并移动其他单元格以留出空间。在现在空白的空间返回新的 Range 对象。Inserts a cell or a range of cells into the worksheet in place of this range, and shifts the other cells to make space. Returns a new Range object at the now blank space.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

insert(shift: Excel.InsertShiftDirection): Excel.Range;

参数

shift
Excel.InsertShiftDirection

指定移动单元格的方式。Specifies which way to shift the cells. 有关详细信息, 请参阅 InsertShiftDirection。See Excel.InsertShiftDirection for details.

返回

示例Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "F5:F10";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.insert();
    return ctx.sync(); 
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

insert(shiftString)

将单个单元格或一系列单元格插入到工作表中取代此区域,并移动其他单元格以留出空间。在现在空白的空间返回新的 Range 对象。Inserts a cell or a range of cells into the worksheet in place of this range, and shifts the other cells to make space. Returns a new Range object at the now blank space.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

insert(shiftString: "Down" | "Right"): Excel.Range;

参数

shiftString
"Down" | "Right"

指定移动单元格的方式。Specifies which way to shift the cells. 有关详细信息, 请参阅 InsertShiftDirection。See Excel.InsertShiftDirection for details.

返回

load(option)

将命令加入队列以加载对象的指定属性。Queues up a command to load the specified properties of the object. 在读取属性之前,你必须调用“context.sync()”。You must call "context.sync()" before reading the properties.

load(option?: Excel.Interfaces.RangeLoadOptions): Excel.Range;

参数

返回

注解

除了此签名之外, 此方法还具有以下签名:In addition to this signature, this method has the following signatures:

load(option?: string | string[]): Excel.Range-Where 选项是一个逗号分隔的字符串或一个字符串数组, 用于指定要加载的属性。load(option?: string | string[]): Excel.Range - Where option is a comma-delimited string or an array of strings that specify the properties to load.

load(option?: { select?: string; expand?: string; }): Excel.Range-Where 选项。选择是一个用逗号分隔的字符串, 用于指定要加载的属性和选项。 expand 是一个用逗号分隔的字符串, 用于指定要加载的导航属性。load(option?: { select?: string; expand?: string; }): Excel.Range - Where option.select is a comma-delimited string that specifies the properties to load, and options.expand is a comma-delimited string that specifies the navigation properties to load.

load(option?: { select?: string; expand?: string; top?: number; skip?: number }): Excel.Range-仅适用于集合类型。load(option?: { select?: string; expand?: string; top?: number; skip?: number }): Excel.Range - Only available on collection types. 它类似于前面的签名。It is similar to the preceding signature. Top 指定结果中可包含的集合项的最大数量。Option.top specifies the maximum number of collection items that can be included in the result. 选项。 skip 指定要跳过且不包含在结果中的项目数。Option.skip specifies the number of items that are to be skipped and not included in the result. 如果指定了 top 选项, 则在跳过指定数目的项目后将启动结果集。If option.top is specified, the result set will start after skipping the specified number of items.

load(propertyNames)

将命令加入队列以加载对象的指定属性。Queues up a command to load the specified properties of the object. 在读取属性context.sync()之前, 必须先调用。You must call context.sync() before reading the properties.

load(propertyNames?: string | string[]): Excel.Range;

参数

propertyNames
string | string[]

一个以逗号分隔的字符串或字符串数组, 用于指定要加载的属性。A comma-delimited string or an array of strings that specify the properties to load.

返回

示例Examples

// Below example uses range address to get the range object.
Excel.run(function (ctx) {
    var sheetName = "Sheet1";
    var rangeAddress = "A1:F8"; 
    var worksheet = ctx.workbook.worksheets.getItem(sheetName);
    var range = worksheet.getRange(rangeAddress);
    range.load('cellCount');
    return ctx.sync().then(function() {
        console.log(range.cellCount);
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});
// Below example uses a named-range to get the range object.
Excel.run(function (ctx) { 
    var rangeName = 'MyRange';
    var range = ctx.workbook.names.getItem(rangeName).range;
    range.load('cellCount');
    return ctx.sync().then(function() {
        console.log(range.cellCount);
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});
// The example below sets number-format, values and formulas on a grid that contains 2x3 grid.
Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "F5:G7";
    var numberFormat = [[null, "d-mmm"], [null, "d-mmm"], [null, null]]
    var values = [["Today", 42147], ["Tomorrow", "5/24"], ["Difference in days", null]];
    var formulas = [[null,null], [null,null], [null,"=G6-G5"]];
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.numberFormat = numberFormat;
    range.values = values;
    range.formulas= formulas;
    range.load('text');
    return ctx.sync().then(function() {
        console.log(range.text);
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

load(propertyNamesAndPaths)

将命令加入队列以加载对象的指定属性。Queues up a command to load the specified properties of the object. 在读取属性context.sync()之前, 必须先调用。You must call context.sync() before reading the properties.

load(propertyNamesAndPaths?: { select?: string; expand?: string; }): Excel.Range;

参数

propertyNamesAndPaths
{ select?: string; expand?: string; }

其中, propertyNamesAndPaths 是用逗号分隔的字符串, 用于指定要加载的属性和 propertyNamesAndPaths。扩展是一个用逗号分隔的字符串, 用于指定要加载的导航属性。Where propertyNamesAndPaths.select is a comma-delimited string that specifies the properties to load, and propertyNamesAndPaths.expand is a comma-delimited string that specifies the navigation properties to load.

返回

merge(across)

在工作表中,将 range 单元格合并到一个区域中。Merge the range cells into one region in the worksheet.

[API 集: ExcelApi 1.2][ API set: ExcelApi 1.2 ]

merge(across?: boolean): void;

参数

across
boolean

可选。Optional. 如果为 True,则将指定区域中每一行的单元格合并为一个单独的合并单元格。Set true to merge cells in each row of the specified range as separate merged cells. 默认值是 false。The default value is false.

返回

void

示例Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "A1:C3";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.merge(true);
    return ctx.sync(); 
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});
Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "A1:C3";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.unmerge();
    return ctx.sync(); 
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

removeDuplicates(columns, includesHeader)

从列指定的区域中删除重复值。Removes duplicate values from the range specified by the columns.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

removeDuplicates(columns: number[], includesHeader: boolean): Excel.RemoveDuplicatesResult;

参数

columns
number[]

区域中可能包含重复项的列。The columns inside the range that may contain duplicates. 至少需要指定一个列。At least one column needs to be specified. 从零开始编制索引。Zero-indexed.

includesHeader
boolean

如果输入数据包含标头, 则为 True。True if the input data contains header. 默认为 false。Default is false.

返回

生成的对象, 其中包含已删除的行数和剩余的唯一行数。The resulting object that contains the number of rows removed and the number of remaining unique rows.

示例Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const range = sheet.getRange("B2:D11");

    const deleteResult = range.removeDuplicates([0],true);    
    deleteResult.load();    
    await context.sync();

    console.log(deleteResult.removed + " entries with duplicate names removed.");
    console.log(deleteResult.uniqueRemaining + " entries with unique names remain in the range.");
});

replaceAll(text, replacement, criteria)

根据当前区域内指定的条件查找并替换给定的字符串。Finds and replaces the given string based on the criteria specified within the current range.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

replaceAll(text: string, replacement: string, criteria: Excel.ReplaceCriteria): OfficeExtension.ClientResult<number>;

参数

text
string

要查找的字符串。String to find.

replacement
string

要将原始替换为的字符串。String to replace the original with.

criteria
Excel.ReplaceCriteria

其他替换条件。Additional Replace Criteria.

返回

OfficeExtension.ClientResult<number>

执行的替换的次数。The number of replacements performed.

select()

在 Excel UI 中选择指定的区域。Selects the specified range in the Excel UI.

[API 集: ExcelApi 1.1][ API set: ExcelApi 1.1 ]

select(): void;

返回

void

示例Examples

Excel.run(function (ctx) {
    var sheetName = "Sheet1";
    var rangeAddress = "F5:F10"; 
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.select();
    return ctx.sync(); 
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

set(properties, options)

同时设置一个对象的多个属性。Sets multiple properties of an object at the same time. 您可以传递具有相应属性的纯对象或相同类型的其他 API 对象。You can pass either a plain object with the appropriate properties, or another API object of the same type.

set(properties: Interfaces.RangeUpdateData, options?: OfficeExtension.UpdateOptions): void;

参数

properties
Interfaces.RangeUpdateData

一个 JavaScript 对象, 其属性的结构 isomorphically 为在其上调用方法的对象的属性。A JavaScript object with properties that are structured isomorphically to the properties of the object on which the method is called.

options
OfficeExtension.UpdateOptions

提供一个选项, 以在 properties 对象尝试设置任何只读属性时禁止显示错误。Provides an option to suppress errors if the properties object tries to set any read-only properties.

返回

void

注解

此方法具有以下附加签名:This method has the following additional signature:

set(properties: Excel.Range): void

示例Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");

    const range = sheet.getRange("B2:E2");
    range.set({
        format: {
            fill: {
                color: "#4472C4"
            },
            font: {
                name: "Verdana",
                color: "white"
            }
        }
    })
    range.format.autofitColumns();
    await context.sync();
});
await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");

    const sourceRange = sheet.getRange("B2:E2");
    sourceRange.load("format/fill/color, format/font/name, format/font/color");
    await context.sync();

    // Set properties based on the loaded and synced 
    // source range.
    const targetRange = sheet.getRange("B7:E7");
    targetRange.set(sourceRange); 
    targetRange.format.autofitColumns();
    await context.sync();
});

set(properties)

基于现有加载的对象同时设置该对象的多个属性。Sets multiple properties on the object at the same time, based on an existing loaded object.

set(properties: Excel.Range): void;

参数

properties
Excel.Range

返回

void

setCellProperties(cellPropertiesData)

根据单元格属性的 2D 数组更新区域,它封装了字体、填充、边框、对齐方式等内容。Updates the range based on a 2D array of cell properties , encapsulating things like font, fill, borders, alignment, and so forth.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

setCellProperties(cellPropertiesData: SettableCellProperties[][]): void;

参数

cellPropertiesData
SettableCellProperties[][]

一个二维数组, 表示要在每个单元格中设置的属性。A 2D array that represents which properties to set in each cell.

返回

void

setColumnProperties(columnPropertiesData)

根据列属性的一维数组更新区域,它封装了字体、填充、边框、对齐方式等内容。Updates the range based on a single-dimensional array of column properties, encapsulating things like font, fill, borders, alignment, and so forth.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

setColumnProperties(columnPropertiesData: SettableColumnProperties[]): void;

参数

columnPropertiesData
SettableColumnProperties[]

一个数组, 表示要在每个列中设置的属性。An array that represents which properties to set in each column.

返回

void

setDirty()

设置下一次重新计算发生时要重新计算的区域。Set a range to be recalculated when the next recalculation occurs.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

setDirty(): void;

返回

void

setRowProperties(rowPropertiesData)

根据行属性的一维数组更新区域,它封装了字体、填充、边框、对齐方式等内容。Updates the range based on a single-dimensional array of row properties, encapsulating things like font, fill, borders, alignment, and so forth.

[API 集: ExcelApi 1.9][ API set: ExcelApi 1.9 ]

setRowProperties(rowPropertiesData: SettableRowProperties[]): void;

参数

rowPropertiesData
SettableRowProperties[]

一个数组, 表示要在每行中设置的属性。An array that represents which properties to set in each row.

返回

void

showCard()

显示活动单元格的卡片(如果该单元格具有富值内容)。Displays the card for an active cell if it has rich value content.

[API 集: ExcelApi 1.7][ API set: ExcelApi 1.7 ]

showCard(): void;

返回

void

toJSON()

重写 JavaScript toJSON()方法, 以便在将 API 对象传递到时提供更有用的输出JSON.stringify()Overrides the JavaScript toJSON() method in order to provide more useful output when an API object is passed to JSON.stringify(). (JSON.stringify反过来, 也会调用传递toJSON给它的对象的方法。尽管原始的 Excel Range 对象是 API 对象, 但toJSON方法返回一个简单的 JavaScript 对象 (类型为Excel.Interfaces.RangeData, in turn, calls the toJSON method of the object that is passed to it.) Whereas the original Excel.Range object is an API object, the toJSON method returns a plain JavaScript object (typed as Excel.Interfaces.RangeData), 其中包含原始对象中所有已加载的子属性的浅表副本。) that contains shallow copies of any loaded child properties from the original object.

toJSON(): Excel.Interfaces.RangeData;

返回

track()

根据文档中的相应更改来跟踪对象,以便进行自动调整。Track the object for automatic adjustment based on surrounding changes in the document. 此调用是 context.trackedObjects.add(thisObject) 的缩写。This call is a shorthand for context.trackedObjects.add(thisObject). 如果你在“.sync”调用之间和按顺序执行“.run”批处理之外使用此对象,并且在对象上设置属性或调用方法时出现“InvalidObjectPath”错误,则需要在首次创建对象时为跟踪的对象集合添加对象。If you are using this object across ".sync" calls and outside the sequential execution of a ".run" batch, and get an "InvalidObjectPath" error when setting a property or invoking a method on the object, you needed to have added the object to the tracked object collection when the object was first created.

track(): Excel.Range;

返回

unmerge()

将范围单元格取消合并为各个单元格。Unmerge the range cells into separate cells.

[API 集: ExcelApi 1.2][ API set: ExcelApi 1.2 ]

unmerge(): void;

返回

void

示例Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "A1:C3";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.unmerge();
    return ctx.sync(); 
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

untrack()

释放与此对象关联的内存(如果先前已跟踪过)。Release the memory associated with this object, if it has previously been tracked. 此调用是 context.trackedObjects.add(thisObject) 的缩写。This call is shorthand for context.trackedObjects.remove(thisObject). 拥有许多跟踪对象会降低主机应用程序的速度,因此请在使用完毕后释放所添加的任何对象。Having many tracked objects slows down the host application, so please remember to free any objects you add, once you're done using them. 在内存释放生效之前,你需要调用“context.sync()”。You will need to call "context.sync()" before the memory release takes effect.

untrack(): Excel.Range;

返回

示例Examples

Excel.run(async (context) => {
    const largeRange = context.workbook.getSelectedRange();
    largeRange.load(["rowCount", "columnCount"]);
    await context.sync();

    for (let i = 0; i < largeRange.rowCount; i++) {
        for (let j = 0; j < largeRange.columnCount; j++) {
            let cell = largeRange.getCell(i, j);
            cell.values = [[i *j]];

            // call untrack() to release the range from memory
            cell.untrack();
        }
    }

    await context.sync();
});