Work with cells
Setting cell value
Set values
To set a value for a cell via the API, use the setValue() method. Pass the following parameters to it:
cells- (string) the id(s) of a cell(s) or a range of cellsvalue- (string/number/array) the value to set for a cell/cells
// setting value for one cell
spreadsheet.setValue("A1",5);
// setting the same value for a range of cells
spreadsheet.setValue("A1:D1",5);
// setting the same value for different cells
spreadsheet.setValue("B6,A1:D1",5);
// setting values from an array for cells in a range alternately
spreadsheet.setValue("A1:D1",[1,2,3]);
Note that the method sets the same (repeated) value(s) for the specified cells. If you want to add different values to spreadsheet cells, use the parse() method.
Get values
You can also return the value(s) set in a cell(s) by passing the id(s) of the necessary cell(s) or a range of cells to the getValue() method.
The method returns the value(s) as a string, number, or array:
// returning the value of one cell
var cellValue = spreadsheet.getValue("A2"); // "Ecuador"
// returning the values of the range of cells
var rangeValues = spreadsheet.getValue("A1:A3"); // -> ["Country","Ecuador","Belarus"]
// returning the values of different cells
var values = spreadsheet.getValue("A1,B1,C1:C3");
//-> ["Country", "Product", "Price", 6.68, 3.75]
Validating cells
Starting with v4.3, you can apply data validation to cells by adding drop-down lists of options. To do that, use the setValidation() method:
spreadsheet.setValidation("B10:B15", ["Apple", "Mango", "Avocado"]);
The drop-down list limits the user's choice. It shows the Invalid value message when the user enters an unexpected value in a cell.
The setValidation() method can also remove validation from the specified cells. Check the details.
Inserting a hyperlink into a cell
To insert a hyperlink in a cell, use the insertLink() method. The method can also add text displayed with the hyperlink:
// insert a link in "A2" cell
spreadsheet.insertLink("A2", {
text:"DHX Spreadsheet", href: "https://dhtmlx.com/docs/products/dhtmlxSpreadsheet/"
});
If you need to remove the link from a cell, pass only the cell ID to the method:
// remove a link from "A2" cell
spreadsheet.insertLink("A2");
Styling cells
Set styles
You can apply styles to a cell or a range of cells with the setStyle() method. It takes two parameters:
cells- (string) the id(s) of a cell(s) or a range of cellsstyles- (object/array) styles to apply to cells
// setting style for one cell
spreadsheet.setStyle("A1", {background: "red"});
// setting the same style for a range of cells
spreadsheet.setStyle("A1:D1", {color: "blue"});
// setting the same style for different cells
spreadsheet.setStyle("B6,A1:D1", {color: "blue"});
// setting styles from an array for cells in a range alternately
spreadsheet.setStyle("A1:D1", [{color: "blue"}, {color: "red"}]);
The method sets the same style for the specified cells. If you want to apply different styles to spreadsheet cells, use the parse() method.
Get styles
To get the styles applied to a cell(s), use the getStyle() method. Pass the id(s) of a cell(s) or a range of cells to it:
// getting style of one cell
var style = spreadsheet.getStyle("A1");
// -> {background: "#8DE9E1", color: "#03A9F4"}
// getting styles of a range of cells
var rangeStyles = spreadsheet.getStyle("A1:D1"); // -> see details
// getting styles of different cells
var values = spreadsheet.getStyle("A1,B1,C1:C3");
For multiple cells the method returns an array of objects with styles applied to a cell:
[
{background: "red", border: "solid 1px yellow", color: "blue"},
{background: "red", border: "solid 1px yellow", color: "blue"},
{background: "#C8FAF6", border: "solid 1px yellow", color: "#81C784"},
{background: "#9575CD", border: "solid 1px yellow", color: "#079D8F"}
]
Editing a cell
Enable cell editor
You can add an input into a cell by calling the startEdit() method:
spreadsheet.startEdit();
The method may take two optional parameters:
cell- (string) optional, the id of a cellvalue- (string) optional, the cell value
If the id of a cell isn't passed, the input is added to the currently selected cell.
Disable cell editor
To finish editing a cell, use the endEdit() method, which closes the editor and saves the entered value.
spreadsheet.endEdit();
Locking cells
Lock cells
You can lock a cell or several cells programmatically to make them read-only for users. Use the lock() method for this purpose. The method takes as a parameter the id(s) of the cell(s) or a range of cells to lock.
// locks a cell
spreadsheet.lock("A1");
// locks a range of cells
spreadsheet.lock("A1:C1");
// locks specified cells
spreadsheet.lock("A1,B5,B7,D4:D6");
Related sample: Spreadsheet. Locked Cells
Unlock cells
To unlock the locked cell(s), use the unlock() method. Pass the id(s) of the cell(s) or a range that contains locked cells as a parameter:
// unlocks a cell
spreadsheet.unlock("A1");
// unlocks a range of cells
spreadsheet.unlock("A1:C1");
// unlocks specified cells
spreadsheet.unlock("A1,B5,B7,D4:D6");
Check whether a cell is locked
To check whether a cell or several cells are locked, use the isLocked() method and pass the id(s) of the cell(s) or a range of cells to it:
// checks whether a cell is locked
var cellLocked = spreadsheet.isLocked("A1");
// checks whether several cells are locked
var rangeLocked = spreadsheet.isLocked("A1:C1");
// checks whether scattered cells are locked
var cellsLocked = spreadsheet.isLocked("A1,B5,B7,D4:D6");
The method returns true or false depending on the state of the cell. If several cells are checked at once, the method returns true if there is at least one locked cell among the specified cells.
Merging cells
Merge cells
You can merge two or more cells into one by passing a range of cells you want to merge to the mergeCells() method:
//merge cells A6, A7, and A8
spreadsheet.mergeCells("A6:A8");
//merge cells B2, C2, and D2
spreadsheet.mergeCells("B2:D2");
Split cells
You can also split the merged cells with the mergeCells() method. In addition to the range of cells, pass true as the second parameter to unmerge the specified cells:
//unmerge cells B2, C2, and D2
spreadsheet.mergeCells("B2:D2", true);
Selecting cells
Select cells
Spreadsheet lets you set cell selection with the API of the Selection object.
You can select cell(s) by passing its/their id(s) to the setSelectedCell() method:
// selecting a cell
spreadsheet.selection.setSelectedCell("B5");
// selecting a range of cells
spreadsheet.selection.setSelectedCell("B1:B5");
// selecting scattered cells
spreadsheet.selection.setSelectedCell("B7,B3,D4,D6,E4:E8");
It is also possible to get the id(s) of the selected cell(s) via the getSelectedCell() method:
const selected = spreadsheet.selection.getSelectedCell(); // -> "B7,B3,D4,D6,E4:E8"
Unselect cells
To remove selection from cell(s), pass its/their id(s) to the removeSelectedCell() method:
// sets selection
spreadsheet.selection.setSelectedCell("B7,B3,D4,D6,E4:E8");
// removes selection
spreadsheet.selection.removeSelectedCell("B7,D4,E5:E7");
const selected = spreadsheet.selection.getSelectedCell();
console.log(selected); // -> "B3,D6,E4,E8"
Related sample: Spreadsheet. Remove selection
Setting focus on a cell
The Selection object lets you set focus on a spreadsheet cell and get the id of the focused cell. Use the corresponding methods for this purpose:
// pass the id of the cell to set focus on
spreadsheet.selection.setFocusedCell("D4");
// getting the focused cell
var focused = spreadsheet.selection.getFocusedCell(); // -> "D4"