Work with Cells

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

Get and Set Cell Values

This example gets and sets cell values.

$(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));
     });

});

Get and Set Cell Default Values

When there is no value in a cell, it appears blank in the SpreadJS. To manage such scenarios, SpreadJS allows you to set a default value of a cell. You can set any value or formula as the default value of a cell. Setting a formula as the default value of a cell works in the same way as a cell formula. The default value can only be set for the viewport area in the worksheet. However, you cannot set a default value for an entire row or column.

Note that when a cell is empty and is already set with a default value, then the following conditions apply:

The recalculation will include the default value or formula for that cell only.
In edit mode, the cell displays the default value, or the value recalculated using the default formula.
On exporting to Excel, the default values are exported as cell values.
Users can change the cell value, but the default value will not be overridden.

You can set or get the default value of a cell using the following methods.

Use defaultValue method of GC.Spread.Sheets.CellRange class.

// Set or get the default value/formula of a cell using defaultValue() method
   activeSheet.getCell(0, 0).defaultValue(20);
   console.log("DefaultValue of cell(0,0) using defaulValue method: " + activeSheet.getCell(0, 0).defaultValue());
   activeSheet.getCell(0, 1).defaultValue("=IF(A1>10, A1*3)");
   console.log("DefaultValue of cell(0,1) using defaulValue method: " + activeSheet.getCell(0, 1).defaultValue());

Use getDefaultValue and setDefaultValue methods of GC.Spread.Sheets.Worksheet class.

// Set or get the default value/formula of a cell using setDefaultValue or getDefaultValue() method
   activeSheet.setDefaultValue(0, 0, 20);
   activeSheet.setDefaultValue(1, 0, 40);
   console.log("DefaultValue of cell(0,0) using getDefaultValue method is: " + activeSheet.getDefaultValue(0, 0));
   activeSheet.setDefaultValue(2, 0, "=SUM(A1,A2)");
   console.log("DefaultValue of cell(2,0) using getDefaultValue method is: " + activeSheet.getDefaultValue(2, 0));

Additionally, you can also use the defaultValue option of CopyToOptions enumeration to copy the default value of a cell.

// Use the CopyToOption enumeration to copy the default cell value/formula.
   activeSheet.getCell(0, 0).defaultValue("2048");
   activeSheet.copyTo(0, 0, 1, 1, 2, 2, GC.Spread.Sheets.CopyToOptions.defaultValue);
   activeSheet.getCell(0, 1).defaultValue("=A1 + 1000");
   activeSheet.copyTo(0, 1, 1, 1, 2, 2, GC.Spread.Sheets.CopyToOptions.defaultValue);

Note: SpreadJS does not support dynamic arrays and array formulas when a formula is set as the default value of a cell. In this scenario, only the top-left value is displayed in the cell.

Using Designer

You can set the default cell value using SpreadJS Designer by selecting the 'Default Value…' option on the Context menu of a cell. This opens the ‘Default Value Dialog’, where you can set the default value for the selected cell. The dialog shows the default value of the cell after it has been set.

The following GIF demonstrates how you can set the default cell value using the designer.

SetDefaultCellValue

Set Active Cell

This example sets the active cell.

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

Set Location of Active Cell

This example sets the location of active cell.

$("#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

This example creates multiple selections.

$(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.

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.

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.

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.

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);
     }
});