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

});

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