Excel.Interfaces.RangeCollectionLoadOptions interface

Remarks

[ API set: ExcelApi 1.9 ]

Properties

$all

Specifying $all for the load options loads all the scalar properties (such as Range.address) but not the navigational properties (such as Range.format.fill.color).

address

For EACH ITEM in the collection: Specifies the range reference in A1-style. Address value contains the sheet reference (e.g., "Sheet1!A1:B4").

addressLocal

For EACH ITEM in the collection: Represents the range reference for the specified range in the language of the user.

cellCount

For EACH ITEM in the collection: Specifies the number of cells in the range. This API will return -1 if the cell count exceeds 2^31-1 (2,147,483,647).

columnCount

For EACH ITEM in the collection: Specifies the total number of columns in the range.

columnHidden

For EACH ITEM in the collection: Represents if all columns in the current range are hidden. Value is true when all columns in a range are hidden. Value is false when no columns in the range are hidden. Value is null when some columns in a range are hidden and other columns in the same range are not hidden.

columnIndex

For EACH ITEM in the collection: Specifies the column number of the first cell in the range. Zero-indexed.

control

For EACH ITEM in the collection: Accesses the cell control applied to this range. If the range has multiple cell controls, this returns EmptyCellControl.

dataValidation

For EACH ITEM in the collection: Returns a data validation object.

format

For EACH ITEM in the collection: Returns a format object, encapsulating the range's font, fill, borders, alignment, and other properties.

formulas

For EACH ITEM in the collection: Represents the formula in A1-style notation. If a cell has no formula, its value is returned instead.

formulasLocal

For EACH ITEM in the collection: Represents the formula in A1-style notation, in the user's language and number-formatting locale. For example, the English "=SUM(A1, 1.5)" formula would become "=SUMME(A1; 1,5)" in German. If a cell has no formula, its value is returned instead.

formulasR1C1

For EACH ITEM in the collection: Represents the formula in R1C1-style notation. If a cell has no formula, its value is returned instead.

hasSpill

For EACH ITEM in the collection: Represents if all cells have a spill border. Returns true if all cells have a spill border, or false if all cells do not have a spill border. Returns null if there are cells both with and without spill borders within the range.

height

For EACH ITEM in the collection: Returns the distance in points, for 100% zoom, from the top edge of the range to the bottom edge of the range.

hidden

For EACH ITEM in the collection: Represents if all cells in the current range are hidden. Value is true when all cells in a range are hidden. Value is false when no cells in the range are hidden. Value is null when some cells in a range are hidden and other cells in the same range are not hidden.

hyperlink

For EACH ITEM in the collection: Represents the hyperlink for the current range.

isEntireColumn

For EACH ITEM in the collection: Represents if the current range is an entire column.

isEntireRow

For EACH ITEM in the collection: Represents if the current range is an entire row.

left

For EACH ITEM in the collection: Returns the distance in points, for 100% zoom, from the left edge of the worksheet to the left edge of the range.

linkedDataTypeState

For EACH ITEM in the collection: Represents the data type state of each cell.

numberFormat

For EACH ITEM in the collection: Represents Excel's number format code for the given range. For more information about Excel number formatting, see Number format codes.

numberFormatCategories

For EACH ITEM in the collection: Represents the category of number format of each cell.

numberFormatLocal

For EACH ITEM in the collection: Represents Excel's number format code for the given range, based on the language settings of the user. Excel does not perform any language or format coercion when getting or setting the numberFormatLocal property. Any returned text uses the locally-formatted strings based on the language specified in the system settings.

rowCount

For EACH ITEM in the collection: Returns the total number of rows in the range.

rowHidden

For EACH ITEM in the collection: Represents if all rows in the current range are hidden. Value is true when all rows in a range are hidden. Value is false when no rows in the range are hidden. Value is null when some rows in a range are hidden and other rows in the same range are not hidden.

rowIndex

For EACH ITEM in the collection: Returns the row number of the first cell in the range. Zero-indexed.

savedAsArray

For EACH ITEM in the collection: Represents if all the cells would be saved as an array formula. Returns true if all cells would be saved as an array formula, or false if all cells would not be saved as an array formula. Returns null if some cells would be saved as an array formula and some would not be.

style

For EACH ITEM in the collection: Represents the style of the current range. If the styles of the cells are inconsistent, null will be returned. For custom styles, the style name will be returned. For built-in styles, a string representing a value in the BuiltInStyle enum will be returned.

text

For EACH ITEM in the collection: Text values of the specified range. The text value will not depend on the cell width. The number sign (#) substitution that happens in the Excel UI will not affect the text value returned by the API.

top

For EACH ITEM in the collection: Returns the distance in points, for 100% zoom, from the top edge of the worksheet to the top edge of the range.

values

For EACH ITEM in the collection: Represents the raw values of the specified range. The data returned could be a string, number, or boolean. Cells that contain an error will return the error string. If the returned value starts with a plus ("+"), minus ("-"), or equal sign ("="), Excel interprets this value as a formula.

valuesAsJson

For EACH ITEM in the collection: A JSON representation of the values in the cells in this range. Unlike Range.values, Range.valuesAsJson supports all data types which can be in a cell. Examples include formatted number values and web images, in addition to the standard boolean, number, and string values. Data returned from this API always aligns with the en-US locale. To retrieve data in the user's display locale, use Range.valuesAsJsonLocal.

valuesAsJsonLocal

For EACH ITEM in the collection: A JSON representation of the values in the cells in this range. Unlike Range.values, Range.valuesAsJsonLocal supports all data types which can be in a cell. Examples include formatted number values and web images, in addition to the standard boolean, number, and string values. Data returned from this API always aligns with the user's display locale. To retrieve data independent of locale, use Range.valuesAsJson.

valueTypes

For EACH ITEM in the collection: Specifies the type of data in each cell.

width

For EACH ITEM in the collection: Returns the distance in points, for 100% zoom, from the left edge of the range to the right edge of the range.

worksheet

For EACH ITEM in the collection: The worksheet containing the current range.

Property Details

$all

Specifying $all for the load options loads all the scalar properties (such as Range.address) but not the navigational properties (such as Range.format.fill.color).

$all?: boolean;

Property Value

boolean

address

For EACH ITEM in the collection: Specifies the range reference in A1-style. Address value contains the sheet reference (e.g., "Sheet1!A1:B4").

address?: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.1 ]

addressLocal

For EACH ITEM in the collection: Represents the range reference for the specified range in the language of the user.

addressLocal?: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.1 ]

cellCount

For EACH ITEM in the collection: Specifies the number of cells in the range. This API will return -1 if the cell count exceeds 2^31-1 (2,147,483,647).

cellCount?: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.1 ]

columnCount

For EACH ITEM in the collection: Specifies the total number of columns in the range.

columnCount?: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.1 ]

columnHidden

For EACH ITEM in the collection: Represents if all columns in the current range are hidden. Value is true when all columns in a range are hidden. Value is false when no columns in the range are hidden. Value is null when some columns in a range are hidden and other columns in the same range are not hidden.

columnHidden?: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.2 ]

columnIndex

For EACH ITEM in the collection: Specifies the column number of the first cell in the range. Zero-indexed.

columnIndex?: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.1 ]

control

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

For EACH ITEM in the collection: Accesses the cell control applied to this range. If the range has multiple cell controls, this returns EmptyCellControl.

control?: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

dataValidation

For EACH ITEM in the collection: Returns a data validation object.

dataValidation?: Excel.Interfaces.DataValidationLoadOptions;

Property Value

Remarks

[ API set: ExcelApi 1.9 ]

format

For EACH ITEM in the collection: Returns a format object, encapsulating the range's font, fill, borders, alignment, and other properties.

format?: Excel.Interfaces.RangeFormatLoadOptions;

Property Value

Remarks

[ API set: ExcelApi 1.9 ]

formulas

For EACH ITEM in the collection: Represents the formula in A1-style notation. If a cell has no formula, its value is returned instead.

formulas?: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.1 ]

formulasLocal

For EACH ITEM in the collection: Represents the formula in A1-style notation, in the user's language and number-formatting locale. For example, the English "=SUM(A1, 1.5)" formula would become "=SUMME(A1; 1,5)" in German. If a cell has no formula, its value is returned instead.

formulasLocal?: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.1 ]

formulasR1C1

For EACH ITEM in the collection: Represents the formula in R1C1-style notation. If a cell has no formula, its value is returned instead.

formulasR1C1?: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.2 ]

hasSpill

For EACH ITEM in the collection: Represents if all cells have a spill border. Returns true if all cells have a spill border, or false if all cells do not have a spill border. Returns null if there are cells both with and without spill borders within the range.

hasSpill?: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.12 ]

height

For EACH ITEM in the collection: Returns the distance in points, for 100% zoom, from the top edge of the range to the bottom edge of the range.

height?: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.10 ]

hidden

For EACH ITEM in the collection: Represents if all cells in the current range are hidden. Value is true when all cells in a range are hidden. Value is false when no cells in the range are hidden. Value is null when some cells in a range are hidden and other cells in the same range are not hidden.

hidden?: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.2 ]

For EACH ITEM in the collection: Represents the hyperlink for the current range.

hyperlink?: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.7 ]

isEntireColumn

For EACH ITEM in the collection: Represents if the current range is an entire column.

isEntireColumn?: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.7 ]

isEntireRow

For EACH ITEM in the collection: Represents if the current range is an entire row.

isEntireRow?: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.7 ]

left

For EACH ITEM in the collection: Returns the distance in points, for 100% zoom, from the left edge of the worksheet to the left edge of the range.

left?: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.10 ]

linkedDataTypeState

For EACH ITEM in the collection: Represents the data type state of each cell.

linkedDataTypeState?: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.9 ]

numberFormat

For EACH ITEM in the collection: Represents Excel's number format code for the given range. For more information about Excel number formatting, see Number format codes.

numberFormat?: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.1 ]

numberFormatCategories

For EACH ITEM in the collection: Represents the category of number format of each cell.

numberFormatCategories?: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.12 ]

numberFormatLocal

For EACH ITEM in the collection: Represents Excel's number format code for the given range, based on the language settings of the user. Excel does not perform any language or format coercion when getting or setting the numberFormatLocal property. Any returned text uses the locally-formatted strings based on the language specified in the system settings.

numberFormatLocal?: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.7 ]

rowCount

For EACH ITEM in the collection: Returns the total number of rows in the range.

rowCount?: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.1 ]

rowHidden

For EACH ITEM in the collection: Represents if all rows in the current range are hidden. Value is true when all rows in a range are hidden. Value is false when no rows in the range are hidden. Value is null when some rows in a range are hidden and other rows in the same range are not hidden.

rowHidden?: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.2 ]

rowIndex

For EACH ITEM in the collection: Returns the row number of the first cell in the range. Zero-indexed.

rowIndex?: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.1 ]

savedAsArray

For EACH ITEM in the collection: Represents if all the cells would be saved as an array formula. Returns true if all cells would be saved as an array formula, or false if all cells would not be saved as an array formula. Returns null if some cells would be saved as an array formula and some would not be.

savedAsArray?: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.12 ]

style

For EACH ITEM in the collection: Represents the style of the current range. If the styles of the cells are inconsistent, null will be returned. For custom styles, the style name will be returned. For built-in styles, a string representing a value in the BuiltInStyle enum will be returned.

style?: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.7 ]

text

For EACH ITEM in the collection: Text values of the specified range. The text value will not depend on the cell width. The number sign (#) substitution that happens in the Excel UI will not affect the text value returned by the API.

text?: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.1 ]

top

For EACH ITEM in the collection: Returns the distance in points, for 100% zoom, from the top edge of the worksheet to the top edge of the range.

top?: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.10 ]

values

For EACH ITEM in the collection: Represents the raw values of the specified range. The data returned could be a string, number, or boolean. Cells that contain an error will return the error string. If the returned value starts with a plus ("+"), minus ("-"), or equal sign ("="), Excel interprets this value as a formula.

values?: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.1 ]

valuesAsJson

For EACH ITEM in the collection: A JSON representation of the values in the cells in this range. Unlike Range.values, Range.valuesAsJson supports all data types which can be in a cell. Examples include formatted number values and web images, in addition to the standard boolean, number, and string values. Data returned from this API always aligns with the en-US locale. To retrieve data in the user's display locale, use Range.valuesAsJsonLocal.

valuesAsJson?: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.16 ]

valuesAsJsonLocal

For EACH ITEM in the collection: A JSON representation of the values in the cells in this range. Unlike Range.values, Range.valuesAsJsonLocal supports all data types which can be in a cell. Examples include formatted number values and web images, in addition to the standard boolean, number, and string values. Data returned from this API always aligns with the user's display locale. To retrieve data independent of locale, use Range.valuesAsJson.

valuesAsJsonLocal?: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.16 ]

valueTypes

For EACH ITEM in the collection: Specifies the type of data in each cell.

valueTypes?: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.1 ]

width

For EACH ITEM in the collection: Returns the distance in points, for 100% zoom, from the left edge of the range to the right edge of the range.

width?: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.10 ]

worksheet

For EACH ITEM in the collection: The worksheet containing the current range.

worksheet?: Excel.Interfaces.WorksheetLoadOptions;

Property Value

Remarks

[ API set: ExcelApi 1.9 ]