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.

window.onload = function () {
    var spread = new GC.Spread.Sheets.Workbook("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
    document.getElementById("button1").addEventListener("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 Formulas

To set a cell's formula, you can utilize either formula() or setFormula().

Below are two methods for setting the same formula in a single cell.

// Method1
sheet.getCell(1,1).formula("=SUM(A1,C3)");
// Method2
sheet.setFormula(1, 1, '=SUM(A1,C3)');

SpreadJS allows you to apply formulas with relative references to a range of cells, automatically adjusting for each cell's position within the range.

For example, to sum the three cells above across the range (3,0) to (3,3): Obtain the range using getRange(), then set the formula on (3,0).

The formula will automatically offset for each cell in the range, ensuring correct summation.

sheet.getRange(3, 0, 1, 4).formula("SUM(A1:A3)", true);
// (3,0): =SUM(A1:A3) 
// (3,1): =SUM(B1:B3) 
// (3,2): =SUM(C1:C3) 
// (3,3): =SUM(D1:D3)

To check if a cell has a formula using the hasFormula property.

sheet.hasFormula(1, 1);
//Output: true

To get a cell's formula, use the getFormula() method.

sheet.setFormula(1, 1, '=SUM(A1,C3)');

sheet.getFormula(1, 1, GC.Spread.Sheets.SheetArea.viewport);
// Output: 'SUM(A1,C3)'

To clear a formula from a cell or range, you have two primary options:

clear(): Access this method via GC.Spread.Sheets.Worksheet.clear() or GC.Spread.Sheets.CellRange.clear(). This action will clear both the formula and its associated calculated result, resulting in an empty cell.
setFormula(): Call the setFormula() method and pass null specifically for the formula argument. This will remove only the formula, retaining the cell's last computed value.

Note:
Setting a new value in a formula-containing cell requires clearing the formula first. Failure to do so will prevent the new value from taking effect.

sheet.setFormula(1, 1, '=SUM(A1,C3)');

sheet.setValue(1, 1, 7); // Failed

// Method1
sheet.getRange(1, 1, 1, 1).clear(GC.Spread.Sheets.StorageType.data);
// Method2
sheet.clear(1, 1, 1, 1, GC.Spread.Sheets.SheetArea.viewport, GC.Spread.Sheets.StorageType.data);
// Method3
sheet.setFormula(1, 1, null);

sheet.getFormula(1, 1, GC.Spread.Sheets.SheetArea.viewport);
// Output: null

sheet.setValue(1, 1, 7); // Success

SpreadJS offers many additional advanced formula features. Please refer to the full documentation for a complete overview.

Advanced Formula Operations: For complex scenarios like cross-sheet referencing and subtotal formulas, please refer to Set Formulas.
Array Formulas: SpreadJS supports array formulas, though with certain limitations. See Array Formulas for usage guidelines.
Custom Names: To handle lengthy or frequently used formulas, you can create custom names. Create Custom Names offers a comprehensive guide on creating, using, and clearing custom names.
Calculation Modes and Settings: SpreadJS allows configuration of calculation modes (e.g., manual vs. automatic). In manual mode, you'll need to define rules for identifying dirty cells and formulas. Refer to Calculation Mode for these details.
Supported Formulas: A dedicated document lists all supported formulas.

Using Designer

In addition to setting formulas via API calls, users can also configure them directly within the user interface. The following demonstrates this interactive approach.

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.

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.

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