SpreadJS 14
Features / Cells / Work with Cells
In This Topic
    Work with Cells
    In This Topic

    SpreadJS allows you to perform various operations on cells as explained below:

    Get and Set Cell Values

    Cell values

    This example gets and sets cell values.

    JavaScript
    Copy Code
    $(document).ready(function ()
    {
        var spread =
        new GC.Spread.Sheets.Workbook(document.getElementById("ss"),{sheetCount:3});
        var sheet = spread.getActiveSheet();
        sheet.getCell(0, 0).formatter("0.00_);(0.00)");
        sheet.getCell(1, 0).formatter("0.00_);(0.00)");
        sheet.getCell(0, 1).formatter("0.00_);(0.00)");
        sheet.getCell(1, 1).formatter("0.00_);(0.00)");
       
        // Set values to Text property
        sheet.getCell(0, 0).text("10");
       
        //Set values by calling SetText method
        sheet.setText(1, 0, "10");
       
        //Set values to Value property.
        sheet.getCell(0, 1).value(10);
       
        //Set values by calling SetValue method.
        sheet.setValue(1, 1, 10);
    
        //Get cell values
        $("#button1").click(function()
         {
           alert("Obtaining cell values by referring to Text property: " +
           sheet.getCell(0, 0).text() + "\n" +
           "Obtaining cell values by calling GetText method: " + sheet.getText(1, 0) + "\n" +
           "Obtaining cell values by referring to Value property: " +
           sheet.getCell(0, 1).value() + "\n" +
           "Obtaining cell values by calling GetValue method: " + sheet.getValue(1, 1));
         });
    
    }); 
    

    Set Active Cell

    This example sets the active cell.

    JavaScript
    Copy Code
    $("#button1").click(function ()
    {  
         // Set cell (3,3) to active
         activeSheet.setActiveCell(3, 3);
     }); 
    

    Set Location of Active Cell

    Active cell

    This example sets the location of active cell.

    JavaScript
    Copy Code
    $("#button1").click(function ()
    {
       // Set cell (3,3) to active
       sheet.setActiveCell(3, 3);
        
       // Display the active cell on top left
       sheet.showCell(3, 3,
        GC.Spread.Sheets.VerticalPosition.top,
        GC.Spread.Sheets.HorizontalPosition.left);
    });
    

    Select Multiple Cells

    Multiple selections in a worksheet

    This example creates multiple selections.

    JavaScript
    Copy Code
    $(document).ready(function ()
    {
        var spread =
        new GC.Spread.Sheets.Workbook(document.getElementById("ss"),{sheetCount:3});
        var sheet = spread.getActiveSheet();
       
        // Allow selection of multiple ranges
        sheet.selectionPolicy(GC.Spread.Sheets.SelectionPolicy.MultiRange);
       
        // Create two different selection ranges.
        sheet.addSelection(0, 0, 2, 2);
        sheet.addSelection(3, 3, 2, 2);
    });
    


    Get Ranges of Selected Cells

    This example gets the selected ranges.

    JavaScript
    Copy Code
    window.onload = function()
    {
       var spread =
       new GC.Spread.Sheets.Workbook(document.getElementById("ss"),{sheetCount:3});
       var activeSheet = spread.getActiveSheet();
       activeSheet.setRowCount(5);
       activeSheet.setColumnCount(5);
       $("#button1").click(function()
       {       
         // Acquiring selection ranges
         var selectedRanges = spread.getActiveSheet().getSelections();
         for(var i = 0; i < selectedRanges.length; i++)
            {
                 console.log("---------------------------");
                 console.log("Using Range class");
                 console.log("-------------------------");
                 console.log("Selected top row index: " + selectedRanges[i].row);
                 console.log("Number of selected rows: " + selectedRanges[i].rowCount);
                 console.log("Selected first column index: " + selectedRanges[i].col);
                 console.log("Number of selected columns: " + selectedRanges[i].colCount);
             }
       });
    }
    

    Set Conditional Formatting for Cells

    This example sets conditional formattinmg styles for cells.

    JavaScript
    Copy Code
    window.onload = function()
    {
      var spread =
      new GC.Spread.Sheets.Workbook(document.getElementById("ss"),{sheetCount:3});
      var activeSheet = spread.getActiveSheet();
    
      // Create respective conditional styles.
      var styleBlue = new GC.Spread.Sheets.Style();
      var stylePink = new GC.Spread.Sheets.Style();
      var styleLime = new GC.Spread.Sheets.Style();
      var styleYellow = new GC.Spread.Sheets.Style();
      var styleEmpty = new GC.Spread.Sheets.Style();
      styleBlue.backColor = "blue";
      styleBlue.foreColor = "white";
      stylePink.backColor = "pink";
      styleLime.backColor = "lime";
      styleYellow.backColor = "yellow";
      styleEmpty.backColor = undefined;
      styleEmpty.foreColor = undefined;
    
      // Set conditional formats to respective cells.
      activeSheet.conditionalFormats.addCellValueRule(
      GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.EqualsTo,
      0, undefined, styleEmpty,
      [new GC.Spread.Sheets.Range(1, 1, 1, 1)]);
      activeSheet.conditionalFormats.addCellValueRule(
      GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.LessThan,
      10,undefined,styleBlue,
      [new GC.Spread.Sheets.Range(1, 1, 1, 1)]);
      activeSheet.conditionalFormats.addCellValueRule(
      GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.Between,
      20,50,stylePink,
      [new GC.Spread.Sheets.Range(2, 1, 1, 1)]);
      activeSheet.conditionalFormats.addCellValueRule(
      GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.Between,
      50,80,styleLime,
      [new GC.Spread.Sheets.Range(3, 1, 1, 1)]);
      activeSheet.conditionalFormats.addCellValueRule(
      GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.GreaterThan,
      80, undefined, styleYellow,
      [new GC.Spread.Sheets.Range(4, 1, 1, 1)]);
      activeSheet.getCell(2, 1).value(25);
      activeSheet.getCell(3, 1).value(77);
      activeSheet.getCell(4, 1).value(88);
    }
    


    Get Cell Position and Size

    This example gets the location and size of specific cells.

    JavaScript
    Copy Code
    spread.getActiveSheet().bind(GC.Spread.Sheets.Events.CellClick, function (e, info)
    {
         if(info.sheetArea ===GC.Spread.Sheets.SheetArea.viewport)
       {
         console.log("Clicked cell index (" + info.row + "," + info.col + ")");
        
         /* Acquire the coordinate information of regular cells which exist at
         the specified index position */
    
         var cellRect = spread.getActiveSheet().getCellRect(info.row, info.col);
         console.log("X coordinate:" + cellRect.x);
         console.log("Y coordinate:" + cellRect.y);
         console.log("Cell width:" + cellRect.width);
         console.log("Cell height:" + cellRect.height);
         }
     });
    

    Get Cell Index from Mouse Click

    You can get the cell index when clicking on a cell.

    JavaScript
    Copy Code
    var spread = new GC.Spread.Sheets.Workbook($("#ss")[0]);
    var activeSheet = spread.getActiveSheet();
    $("#ss").click(function (e)
    {
        
      /* Acquire cell index from mouse-clicked point of regular cells which are
        neither fixed rows/columns nor row/column headers.*/
    
         var offset = $("#ss").offset();
         var x = e.pageX - offset.left;
         var y = e.pageY - offset.top;
         var target = spread.getActiveSheet().hitTest(x, y);
         if(target &&
         (target.rowViewportIndex === 0 || target.rowViewportIndex === 1) &&
         (target.colViewportIndex === 0 || target.colViewportIndex === 1))
         {
             console.log("Row index of mouse-clicked cells: " + target.row);
             console.log("Column index of mouse-clicked cells: " + target.col);
         }
    });