Work with Rows and Columns

SpreadJS allows you to perform various operations on rows and columns as explained below:

Add Rows and Columns

You can add rows and columns using the addRows and addColumns methods. They accept parameters such as the row or column index and the number of rows and columns to add.

The following code example shows how to add rows and columns.

function AddNewRow() {
    // Add one row to Row 2.  
    sheet.addRows(1, 1);
    sheet.setValue(1, 0, "Added row");
};

function AddNewCol() {
    // Add one column to Column 2.  
    sheet.addColumns(1, 1);
    sheet.setValue(0, 1, "Added column");
};

Delete Rows and Columns

You can delete rows and columns using the deleteRows and deleteColumns methods. They accept parameters such as the row or column index and the number of rows/columns to remove.

The following code example shows how to delete rows and columns.

function DeleteRow() {
    // Delete Row 2.
    sheet.deleteRows(1, 1);
}

function DeleteCol() {
    // Delete Column 2.
    sheet.deleteColumns(1, 1);                
}

Set Number of Rows and Columns

You can set the number of rows and columns to be displayed in a sheet using the setRowCount and setColumnCount methods. They accept parameters such as the row or column count and the sheet area.

The following code example shows how to set the number of rows and columns to 3 in a worksheet as shown in the above image.

// Configure Workbook and Worksheet
spread = new GC.Spread.Sheets.Workbook("ss");
sheet = spread.getActiveSheet();
         
// Set the number of rows and columns to 3.
sheet.setRowCount(3, GC.Spread.Sheets.SheetArea.viewport);
sheet.setColumnCount(3, GC.Spread.Sheets.SheetArea.viewport);

When setting the number of rows/columns with setRowCount/setColumnCount methods, if the count parameter is less than the old count then the rows/columns gets deleted irrespective of the fact that the rows/columns contain data, formulas, or anything else.

You can however prevent the rows/columns from being deleted using the UsedRangeType enumeration option.

The following code example shows how to use the UsedRangeType enum in setRowCount method:

sheet.setRowCount(5, GC.Spread.Sheets.SheetArea.viewport, GC.Spread.Sheets.UsedRangeType.span); 

Note: It is only meaningful to guard the rows/columns from deleting when the count of new rows/columns is smaller than the existing ones.

Set Row Height and Column Width

You can adjust the row height and column width in a worksheet using the setRowHeight and setColumnWidth methods. They accept parameters such as the row/column index, value in pixels, and the sheet area.

The following code example shows how to set the row height and column width in a worksheet to 90px and 120px respectively.

// Change the height of the second row.
sheet.setRowHeight(1, 90.0,GC.Spread.Sheets.SheetArea.viewport);
   
//Change the width of the second column.
sheet.setColumnWidth(1, 120.0,GC.Spread.Sheets.SheetArea.viewport);

Select and Deselect Rows and Columns

You can add a cell or a range of cells to a selection using the addSelection method. They accept parameters such as the row and column index of the first cell and the number of rows and columns to add or remove.

Similarly, you can clear the selection from a worksheet using the clearSelection method.

The following code example shows how to add or clear cell selections in a worksheet.

function AddSelection() {
    // Select entire row 4.
    sheet.addSelection(3, -1, 1, -1);
}

function ClearSelection() {
    // Remove the selections in the sheet.
    sheet.clearSelection();
}

Set Top Row and Column

You can choose to display or move the view to a specific row or column in the worksheet using the showRow and showColumn methods. They accept parameters such as the row or column index and the vertical or horizontal position according to the VerticalPosition and HorizontalPosition enumeration options.

The following code example shows how to move the view to a specific row or column in the worksheet.

function ShowRow() {
    // Show Row 10.
    sheet.showRow(9, GC.Spread.Sheets.VerticalPosition.top);
};

function ShowCol() {
    // Show Column J.
    sheet.showColumn(9, GC.Spread.Sheets.HorizontalPosition.left);
};

Get Index of Displayed Cell

You can access information such as the index of the active cell using the following getViewport methods.

The following code example shows how to get cell index according to the position of the row or column in the worksheet.

function GetCellIndex() {
    // Acquire top row (column) index
    var topRow = sheet.getViewportTopRow(1);
    var leftCol = sheet.getViewportLeftColumn(1);
    alert("Index of top row being displayed: " + topRow + "\n" + "Index of first column being displayed: " + leftCol);
}