Work with cells
Setting cell value
Set values
To set value for a cell(s) via the API, make use of the setValue() method. Pass the following parameters to it:
- cells - (string) the id(s) of a cell(s) or a range of cells
- value - (string/number/array) the value to be 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]);
Please note that the method allows setting the same/repeated value(s) for the specified cells. In case you want to add different values into spreadsheet cells, you'd better use the parse() method.
Get values
It is also possible to 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 will return 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 via adding drop-down lists of options into the cells. To do that, use the setValidation() method:
spreadsheet.setValidation("B10:B15", ["Apple", "Mango", "Avocado"]);
The drop-down list will limit the choice of the end user. It will show the Invalid value message when the user completes a cell with an unexpected value.
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 also allows adding a text that will be 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 to the method only ID of the cell:
// remove a link from "A2" cell
spreadsheet.insertLink("A2");
Styling cells
Set styles
You can apply certain styling to a cell or a range of cells via the setStyle() method. It takes two parameters:
- cells - (string) the id(s) of a cell(s) or a range of cells
- style - (object/array) styles that should be applied 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 allows setting the same style for the specified cells. In case you want to apply different styles to spreadsheet cells, you'd better 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 cell
- value - (string) optional, the cell value
If the id of a cell isn't passed, input will be added into the currently selected cell.
Disable cell editor
To finish editing of a cell, use the endEdit() method that will close the editor while saving 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 will return true or false depending on the state of the cell. If several cells are checked at once, the method will return 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 may also split the merged cells via the mergeCells() method. In addition to the range of cells, pass true as a value of the second parameter which will define that the specified cells must be unmerged:
//unmerge cells B2, C2, and D2
spreadsheet.mergeCells("B2:D2", true);
Selecting cells
Select cells
Spreadsheet provides a handy way of setting selection for cells via 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 allows setting focus on a desired spreadsheet cell, as well as getting the id of the cell in focus. 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"