SpreadJS 14
Features / Cells / Cell States
In This Topic
    Cell States
    In This Topic

    SpreadJS allows users to configure custom cell styles when the cell state is matched in a specific state like edit, hover, active, select etc. This feature is helpful in creating input form controls, advanced structured forms and other interactive forms within the spreadsheet where you want to specify cell styles according to the current state of the cell.

    Cell States represent how a cell will respond to different actions from the user. Typically, cell state is a part of style and users can create custom named styles to apply various cell features selectively and easily. By implementing cell states, users can create modern user interfaces for spreadsheet based forms and interactive dashboards. Also, users can highlight the active row and column for the selected/active cell and work with applications in real-time. 

    Example - The following screenshot depicts different cell styles when a user hovers over a cell, selects a cell or changes the state of a cell in a spreadsheet. In this example, the background color of the cell changes to pink and the foreground color of the cell changes to red when the cell is in hover state. The cell color changes to yellow when a cell is selected and back color changes to blue and fore color to red when the cell state is dirty.

     Different cell states in the spreadsheet

    Types of Cell States

    The CellStatesType enumeration can be used to work with different cell states in the spreadsheet, as described in the table shared below:

    Enum Type and Value Description
    hover = 0x01 Executes the specified action when mouse is hovered on a cell.
    invalid = 0x02 Sets the cell state to invalid if the conditional evaluation fails during data validation.
    readonly = 0x04 Locks the cell in a protected worksheet.
    edit = 0x08 Executes the specified action when the cell is being edited.
    active = 0x10 Executes the specified action when the cell is in focus.
    selected = 0x20 Executes the specified action when the cell is lying in the selection range of the worksheet.
    dirty = 0x40 Sets a cell state to dirty on changing the cell value or the reference cell value, to easily track any change in the sheet data.

    Priority Ordering

    While configuring different cell styles for various cell states in the worksheet, the priority is followed in the below order :

    edit > hover > active > selected > dirty > invalid > readonly > normal

    where the cell in normal state will have least priority and the cell in edit mode will have the highest priority.

    If cell state styles for two different cell ranges intersect with each other, then the style which was last set by the user will possess more priority.

    Using Code

    Refer to the following example code to apply different styles when users hover over a cell or select a cell in the spreadsheet.

    JavaScript
    Copy Code
    // initializing Spread
    var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 1 });
    // get the activesheet
    var activeSheet = spread.getSheet(0);
    // Adding Data to the spreadsheet
    activeSheet.setValue(0, 1, "Y-2015");
    activeSheet.setValue(0, 2, "Y-2016");
    activeSheet.setValue(0, 3, "Y-2017");
    activeSheet.setValue(0, 4, "Y-2018");
    activeSheet.setValue(0, 5, "Y-2019");
    activeSheet.setValue(1, 0, "Gradlco");
    activeSheet.setValue(2, 0, "Saagiate");
    activeSheet.setValue(3, 0, "Inferno");
    activeSheet.setColumnWidth(0, 120);
    
    // Creating a HoverStyle
    var hoverStyle = new GC.Spread.Sheets.Style();
    hoverStyle.backColor = "pink";
    hoverStyle.foreColor = "red";
    
    // Creating a SelectStyle
    var selectStyle = new GC.Spread.Sheets.Style();
    selectStyle.backColor = "yellow";
    selectStyle.foreColor = "red";
    
    // Creating a DirtyStyle
    var dirtyStyle = new GC.Spread.Sheets.Style();
    dirtyStyle.backColor = 'lightblue';
    dirtyStyle.foreColor = 'red';
    
    // Accessing cell range A1:J10
    var range = new GC.Spread.Sheets.Range(0, 0, 10, 10);
    
    // Applying styles on different CellStatesType
    activeSheet.cellStates.add(range, GC.Spread.Sheets.CellStatesType.dirty, dirtyStyle);
    activeSheet.cellStates.add(range, GC.Spread.Sheets.CellStatesType.selected, selectStyle);
    activeSheet.cellStates.add(range, GC.Spread.Sheets.CellStatesType.hover, hoverStyle);