Edit this page

SpreadsheetRange Object

This article explains the methods of the SpreadsheetRange client-side object and provides examples on its usage.

The following example demonstrates two approaches for acquisition of a SpreadsheetRange reference, which can be used to manipulate the cell(s) state further.


function getRange() {
    var spreadsheet = $find("<%= RadSpreadSheet1.ClientID %>");
    var activeSheet = spreadsheet.get_activeSheet();
    var singleCellRange = activeSheet.get_range("A1")  
    var mutliCellRange = activeSheet.get_range("A1:B3"); // using A1 Notation
}

The following table lists the methods of the client-side SpreadsheetRange object:

Name Parameters Return Type Description
get_enabled none bool Gets the disabled state of the cells in the range.
set_enabled bool none Sets the disabled state of the cells in the range. (see Example 1)
set_filter bool/Object/Array none Enables/disables or sets the filter for a given range. (see Example 2)
hasFilter none bool Returns true if the sheet of the range has filter enabled.
get_isSortable none bool Returns true if a range can be sorted.
get_isFilterable none bool Returns true if a range can be filtered.
merge none none Merges the range cells into a single merged cell. If the range already includes a merged cell, they are merged, too. (see Example 3)
unmerge none none Un-merges any merged cells which are included in the range.
select none none Sets the sheet selection to the range cells.
fillFrom SpreadsheetRange/string none Fills a range with values inferred from a source range. This method employs some heuristics similar to what Excel's auto-filling algorithm does when you select a range of cells and drag the bottom-right handle. The range to be filled is the current object, and you must pass a source range containing data as first argument.
clear none/object none Clears the contents of the range cells.
clearFilter Array/int none Clears the set filters for the given column(s). The indices is relative to the beginning of the range. (see Example 4)
get_borderBottomColor none string Gets the color of the bottom border of the cells.
set_borderBottomColor string none Sets the color of the bottom border of the cells. If the range includes more than a single cell, the setting is applied to all cells. The color may be set to any valid CSS color. (see Example 5)
get_borderBottomSize none int Gets the size of the bottom border of the cells.
set_borderBottomSize int none Sets the size of the bottom border of the cells. If the range includes more than a single cell, the setting is applied to all cells. The method accepts any valid Length value. (see Example 6)
get_borderLeftColor none string Gets the color of the left border of the cells.
set_borderLeftColor string none Sets the color of the left border of the cells. If the range includes more than a single cell, the setting is applied to all cells. The color may be set to any valid CSS color.
get_borderLeftSize none int Gets the size of the left border of the cells.
set_borderLeftSize int none Sets the size of the left border of the cells. If the range includes more than a single cell, the setting is applied to all cells. The method accepts any valid Length value.
get_borderRightColor none string Gets the color of the right border of the cells.
set_borderRightColor string none Sets the color of the right border of the cells. If the range includes more than a single cell, the setting is applied to all cells. The color may be set to any valid CSS color.
get_borderRightSize none int Gets the size of the right border of the cells.
set_borderRightSize int none Sets the size of the right border of the cells. If the range includes more than a single cell, the setting is applied to all cells. The method accepts any valid Length value.
get_borderTopColor none string Gets the color of the top border of the cells.
set_borderTopColor string none Sets the color of the top border of the cells. If the range includes more than a single cell, the setting is applied to all cells. The color may be set to any valid CSS color.
get_borderTopSize none int Gets the size of the top border of the cells.
set_borderTopSize int none Sets the size of the top border of the cells. If the range includes more than a single cell, the setting is applied to all cells. The method accepts any valid Length value.
get_background none string Gets the background color of the cells in the range.
set_background string none Sets the background color of the cells in the range. The background may be set to any valid CSS color. (see Example 7)
get_bold none bool Gets the bold state of the cells in the range.
set_bold bool none Sets the bold state of the cells in the range. (see Example 8)
get_color none string Gets the current text color of the top-left cell of the range.
set_color string none Sets the text color of the range. The color may be set to any valid CSS color.
get_fontFamily none string Gets the font family of the top-left cell of the range.
set_fontFamily string none Sets the font family of the cells in the range.
get_fontSize none int Gets the font size of the top-left cell of the range.
set_fontSize int none Sets the font size (in pixels) of the cells in the range.
get_format none string Gets the format (string) of the top-left cell of the range
set_format string none Sets the format of the cells in the range. (see Example 9)
get_formula none string Gets the formula of the cells
set_formula string none Sets the formula of the cells. The string that the method accepts may optionally start with =. (see Example 10)
get_input none string Gets the value of the cells. If the Range includes more than one cell - returns the top-left cell input value of the range.
set_input string none Sets the value of the cells. This is similar to value, but it parses the argument as if it was entered through the text box. Using the method you can set text, formula, number, boolean or Date. (see Example 11)
get_italic none bool Gets the italic state of the cells in the range.
set_italic bool none Sets the italic state of the cells in the range.
get_selections none array of objects Gets the selections in the range. (see Example 12)
get_textAlign none string Gets the text alignment of the cells in the range. Returns one of the following values: left, center, right and justify.
set_textAlign string none Sets the text alignment of the cells in the range. Accepts one of the following values: left, center, right and justify.
get_underline none bool Gets the underline state of the cells in the range.
set_underline bool none Sets the underline state of the cells in the range.
get_value none string/int/Date Gets the value of the cells.
set_value string/int/Date none Sets the value of the cells.
get_values none array of arrays Gets the values of the range cells.
set_values array of arrays none Sets the values of the range cells. The argument should be an array of arrays which match the dimensions of the range. (see Example 13)
get_validation none object Gets the current validation of the top-left cell of the range.
set_validation object none Sets the validation of the cells. The object that is passed to the method may contain type, comparerType, dataType, from, to, allowNulls, messageTemplate and titleTemplate keys. (see Example 14)
get_verticalAlign none string Gets current vertical text alignment of the top-left cell of the range. Returns one of the following values: top, center and bottom.
set_verticalAlign string none Sets the vertical alignment of the cells in the range. Accepts one of the following values: top, center and bottom.
get_wrap none bool Gets the wrap of the range cells.
set_wrap bool none Sets the wrap of the range cells. (see Example 15)

Example 1: Demonstrates the usage of the set_enabled method

function enableDisableRange() {
    var spreadsheet = $find("<%= RadSpreadsheet1.ClientID %>");
    var activeSheet = spreadsheet.get_activeSheet();
    var range = activeSheet.get_range("A1:B3");
    if (range.get_enabled()) {
        range.set_enabled(false);
    }
    else {
        range.set_enabled(true);
    }
}

Example 2: Demonstrates the usage of the set_filter method

function addFilter() {
    var spreadsheet = $find("<%= RadSpreadsheet1.ClientID %>");
    var activeSheet = spreadsheet.get_activeSheet();
    var range = activeSheet.get_range("A1:B3");
    var filter = new kendo.spreadsheet.DynamicFilter({ type: "today" });
    range.set_filter([{ column: 0, filter: filter }]);
}

Example 3: Demonstrates the usage of the merge method

function mergeRange() {
    var spreadsheet = $find("<%= RadSpreadsheet1.ClientID %>");
    var activeSheet = spreadsheet.get_activeSheet();
    var range = activeSheet.get_range("A1:B3");
    range.merge();
}

Example 4: Demonstrates the usage of the set_filter method

function addFilter() {
    var spreadsheet = $find("<%= RadSpreadsheet1.ClientID %>");
    var activeSheet = spreadsheet.get_activeSheet();
    var range = activeSheet.get_range("A1:B3");
    var filter = new kendo.spreadsheet.DynamicFilter({ type: "today" });
    range.set_filter([{ column: 0, filter: filter }]);
}

Example 5: Demonstrates the usage of the set_borderBottomColor method

function colorRangeBottomBorder() {
    var spreadsheet = $find("<%= RadSpreadsheet1.ClientID %>");
    var activeSheet = spreadsheet.get_activeSheet();
    var range = activeSheet.get_range("A1:B3");
    range.set_borderBottomColor("#0000ff");
}

Example 6: Demonstrates the usage of the set_borderBottomSize method

function setRangeBottomBorderSize() {
    var spreadsheet = $find("<%= RadSpreadsheet1.ClientID %>");
    var activeSheet = spreadsheet.get_activeSheet();
    var range = activeSheet.get_range("A1:B3");
    range.set_borderBottomSize(3);
}

Example 7: Demonstrates the usage of the set_background method

function changeRangeBackground() {
    var spreadsheet = $find("<%= RadSpreadsheet1.ClientID %>");
    var activeSheet = spreadsheet.get_activeSheet();
    var range = activeSheet.get_range("A1:B3");
    range.set_background("#c0c0c0");
}

Example 8: Demonstrates the usage of the set_bold method

function changeRangeFontWeight() {
    var spreadsheet = $find("<%= RadSpreadsheet1.ClientID %>");
    var activeSheet = spreadsheet.get_activeSheet();
    var range = activeSheet.get_range("A1:B3");
    range.set_bold(true);
}

Example 9: Demonstrates the usage of the set_format method

function setRangeFormat() {
    var spreadsheet = $find("<%= RadSpreadsheet1.ClientID %>");
    var activeSheet = spreadsheet.get_activeSheet();
    var range = activeSheet.get_range("A1:B3");
    range.set_format("00.000");
}

Example 10: Demonstrates the usage of the set_formula method

function setRangeFormula() {
    var spreadsheet = $find("<%= RadSpreadsheet1.ClientID %>");
    var activeSheet = spreadsheet.get_activeSheet();
    var rangeA1 = activeSheet.get_range("A1");
    rangeA1.set_input("100");
    var rangeA2 = activeSheet.get_range("A2");
    range2.set_formula("A1*2");
}

Example 11: Demonstrates the usage of the set_input method

function setRangeInput() {
    var spreadsheet = $find("<%= RadSpreadsheet1.ClientID %>");
    var activeSheet = spreadsheet.get_activeSheet();
    var range = activeSheet.get_range("A1");
    range.set_input("100");
}

Example 12: Demonstrates the usage of the get_selections method

function OnClientChange(sender, args) {
    var range = args.get_range();
    var selections = range.get_selections();

    var row = selections[0].bottomRightCell.row;
}

Example 13: Demonstrates the usage of the set_values method

function setRangeValues() {
    var spreadsheet = $find("<%= RadSpreadsheet1.ClientID %>");
    var activeSheet = spreadsheet.get_activeSheet();
    var range = activeSheet.get_range("A1:B3");
    var newValues = [[1, 2], [12, 28], [33, 5]];
    range.set_values(newValues);
}

Example 14: Demonstrates the usage of the set_validation method

function setRangeValidation() {
    var spreadsheet = $find("<%= RadSpreadsheet1.ClientID %>");
    var activeSheet = spreadsheet.get_activeSheet();
    var range = activeSheet.get_range("A1");
    range.set_value(4);
    var validation = {
        from: "1",
        to: "2",
        comparerType: "between",
        dataType: "number",
        messageTemplate: "Number should match the validation."
    };

    range.set_validation(validation);
}

Example 15: Demonstrates the usage of the set_wrap method

function setRangeWrap() {
    var spreadsheet = $find("<%= RadSpreadsheet1.ClientID %>");
    var activeSheet = spreadsheet.get_activeSheet();
    var range = activeSheet.get_range("A1");
    range.set_wrap(true);
}