Working with Cell Content
Locking/Unlocking Cells
You can lock a cell or several cells to protect their content from editing. The locked cells will have a small yellow lock in the bottom right corner.
For this purpose, you need to use the lockCell method and pass three parameters to it:
- row - (number) the row ID
- column - (number) the column ID
- state - (boolean) true to lock a cell, false to unlock it
// locks the cell at the intersection of the 3rd row and 2nd column
$$("ssheet").lockCell(3, 2, true);
You can also lock/unlock several cells at a time with the lockCell method. Call it with different parameters:
- first - (object) the row and column numbers of the first cell in the range
- last - (object) the row and column numbers of the last cell in the range
- state - (boolean) true to lock a cell, false to unlock it
// locks 7 cells in the 2nd row
$$("ssheet").lockCell({ row:2, column:1 }, { row:2, column:7 }, true);
// locks 7 cells in the 2nd column
$$("ssheet").lockCell({ row:1, column:2 }, { row:7, column:2 }, true);
// locks 10 cells in the 1st and 2nd rows
$$("ssheet").lockCell({ row:1, column:1 }, { row:2, column:5 }, true);
In case the cell/cells to lock aren't specified, the method will lock the selected cell.
Changing the default styling of locked cells
You can modify the default styling of locked cells, by disabling yellow locks and applying some background color via CSS:
<style>
.webix_lock:after{
content:" ";
}
.webix_lock {
background-color:#99f29d;
}
</style>
Related sample: Styling locked cells
Checking the state of a cell
You can check, whether a cell is locked, with the help of the isCellLocked method.
It takes two parameters:
- rowId - (number) the row id
- columnId - (number) the column id
and returns true, if the cell is locked and false if it's unlocked.
var isLocked = $$("ssheet").isCellLocked(3, 2);
Adding an Editor into a Cell
It's possible to add an editor into a cell of the sheet. It can include either some custom options or values of a cell range. You can also explicitly specify whether to add an empty option.
Use the setCellEditor to do it. The method expects three parameters:
- rowId - (number) the row id
- columnId - (number) the column id
- editorObject - (object) an object with two properties:
- editor - (string) the editor type (ss_richselect, popup, excel_date, text)
- options - (string,array) (the parameter is used if the editor is of the ss_richselect type) a range of cell references or an array of editor options
- empty - (boolean) specifies whether to add an empty option
$$("ss1").setCellEditor(8,1,{ editor:"ss_richselect", options:["One", "Two", "Three"]});
// or
$$("ss1").setCellEditor(8,2,{ editor:"ss_richselect", options:"B3:B7", empty:true });
Getting the cell editor
You can get the editor set in a cell with the help of the getCellEditor method.
The method takes the ids of cell's row and column as parameters.
$$("ss1").getCellEditor(8,1);
It will return an object with two properties:
- editor - (string) the type of the editor (ss_richselect, popup, excel_date, text)
- options - (string,array) a range of cell references or an array of editor options
{editor:"ss_richselect", options:["One","Two","Three"]}
// or
{editor:"ss_richselect", options:"B3:B7"}
Filtering Cells Values
You can also set a filter inside of a cell. The method setCellFilter will help your with this task.
You need to pass the following parameters to this method:
- rowId - (number) the row id
- columnId - (number) the column id
- options - (string,array) a range of cells references or an array of filter options
// an array of options
$$("ss1").setCellFilter(1,2, ["", "Europe", "Asia", "America"]);
// a range of cells references
$$("ss1").setCellFilter(2,2, "B3:B7");
Sorting Cells Values
SpreadSheet allows you to sort values within a selected or specified range of cells via the sortRange method. You can pass two optional parameters to it:
- range - (string) optional, the range of cells that should be sorted, null to sort the selected range
- dir - (string) optional, the sorting direction: "asc" or "desc" ("asc" by default)
// sorts the specified range in the default ("asc") order
$$("ssheet").sortRange("B2:B4");
// sorts the specifed range in the descending order
$$("ssheet").sortRange("B2:B4", "desc");
// sorts the selected range in the descending order
$$("ssheet").sortRange(null,"desc");
Adding Sparklines into a Cell
You can add a small chart into a cell to display tendencies of data values changing in a range of cells.
To insert a sparkline inside of a cell, use the addSparkline method with the following parameters:
- rowId - (number) the row id
- columnId - (number) the column id
- config - (object) the sparkline configuration that have the properties below:
- type - (string) the type of an added sparkline
- data - (string) the range of cells the values of which will be displayed in the sparkline
- color - (string) the color of a sparkline either in a hex format or as a color name
- negativeColor - (string) the color of a negative value for a Bar sparkline
$$("ssheet").addSparkline(rowId, columnId, config);
Let's insert a blue sparkline of the Line type into the cell E5. The passed parameters will be as follows:
$$("ssheet").addSparkline(5,5,{type:"line", range:"B4:E4", color:"#6666FF"});
Related sample: Adding sparklines
Adding Image in a Cell
You can add an image into a cell to illustrate data in the spreadsheet.
To insert an image into a cell, use the addImage method. You need to pass three parameters to this method:
- rowId - (number) the row id
- columnId - (number) the column id
- url - (string) the URL of an image
$$("ssheet").addImage(2,3, "http://docs.webix.com/media/desktop/image.png");
Adding Comments into Cells
There is a possibility to add a comment into a certain cell of SpreadSheet.
Use the add() method of the comments object. It takes three parameters:
- rowId - (number) the row id
- columnId - (number) the column id
- comment - (string) the text of a comment
// adding a comment into the cell B3
$$("ssheet").comments.add(3,2,"text");
The API of the comments object also makes it possible to get a comment of a particular cell or to delete a comment that is no longer needed:
// getting a comment for the cell B3
$$("ssheet").comments.get(3,2);
// removing a comment from the cell B3
$$("ssheet").comments.remove(3,2);
Using Placeholders
You can specify what data will be displayed in the SpreadSheet cells by using placeholders.
A placeholder is an object with data properties which can be set as SpreadSheet values. To define a placeholder use the setPlaceholder method:
$$("ssheet").setPlaceholder({value:"France", expense:1366, income:842});
To specify a placeholder's property in a cell instead of a value, use the ={{property}} construction. For example, for cells with the "expense" values you should specify placeholders as "={{expense}}".
If you specify a new placeholder for a SpreadSheet, values of all cells where properties of this placeholder are defined will be updated.
$$("ssheet").setPlaceholder({value:"Poland", expense:684, income:781});
If you have not checked yet, be sure to visit site of our main product Webix web control library and page of spreadsheet javascript library product.