Products

Spreadsheet Rows

This section covers all information about row management.

Documentation

Methods

The following methods interact with the rows programmatically.

Method Description
getHeight Get the row height.
getHeight(rowNumber?: Number) => number
setHeight Set the row height.
setHeight(rowNumber: Number, height: Number) => void
getRow Get the row settings by number.
getRow(rowNumber: Number) => Object
moveRow Change the position of a row.
moveRow(rowNumber: Number, newPositionNumber: Number) => void
insertRow Create one or multiple new rows.
insertRow(mixed: Mixed, rowNumber: Number, insertBefore: Boolean, data: Array) => void
@param {number|array} - number of rows to be added | array with the data to be added (it will create a new single row with this data)
@param {number?} rowNumber - The new row reference position | null refers to the last row in the spreadsheet
@param {boolean} insertBefore - The new row(s) should be included before or after the rowNumber defined
@param {array} data - Array with the new row data
deleteRow Delete one or more rows.
deleteRow(rowNumber: Number, numOfRows: Number) => void
@param {number} rowNumber - Which column should be excluded starting from zero
@param {number} numOfRows - Number of rows to be excluded from the define row reference
showRow Show a row by number.
showRow(rowNumber: Number) => void
@param {number} rowNumber - Show the row by its number
hideRow Hide a row by number.
hideRow(rowNumber: Number) => void
@param {number} rowNumber - Hide the row by its number
getSelectedRows Get the selected rows.
getSelectedRows(asIds: Boolean) => array|string

Events

There are several events related to the row in your spreadsheet. There are a few onbefore events to intercept, validate or cancel a user action.

Event Description
oncreaterow oncreaterow(worksheet: Object, rowNumber: Number, tr: HTMLElement)
After a new row is inserted.
onbeforeinsertrow onbeforeinsertrow(worksheet: Object, rowNumber: Number, numOfRows: Number, insertBefore: Boolean) => Boolean
Before a new row is inserted. Return false to cancel the user action.
oninsertrow oninsertrow(worksheet: Object, rowNumber: Number, numOfRows: Number, historyRecords: Array, insertBefore: Boolean)
After a new row is inserted.
onbeforedeleterow onbeforedeleterow(worksheet: Object, rowNumber: Number, numOfRows: Number)
Before a row is deleted. Return false to cancel the user action.
ondeleterow ondeleterow(worksheet: Object, rowRecords: DOMElements, rowData: Array, cellAttributes: Object)
After a row is deleted.
onmoverow onmoverow(worksheet: Object, origin: Number, destination: Number)
After a row is moved to a new position.
onresizerow onresizerow(worksheet: Object, row: Mixed, height: Mixed, oldHeight: Mixed)
After the height changes for one or more rows.
onchangerowvisibility onchangerowvisibility(worksheet: Object, state: boolean: affected: []]) => void
When the visibility of rows changes.

Initial Settings

The following row-related properties are available through the spreadsheet initialization.

Property Description
allowInsertRow: boolean Enable the user to enter new rows. Default: true
allowManualInsertRow: boolean A new row is automatically inserted when the user presses the enter key in the last row. Default: true
allowDeleteRow: boolean Allow the user to delete rows. Default: true
rowDrag: boolean Allow the user to change the position of one row by dragging and dropping. Default: true
rowResize: boolean Allow the user to resize rows. Default: true
defaultRowHeight: number The default row height. From version 8: this can have an impact on the scroll calculations.
minSpareRows: number The mandatory number of blank rows at the end of the spreadsheet. Default: none.

Available properties

It is possible to initialize the spreadsheet with a custom id, text and height using the following properties:

Property Description
id: number Defines the record id. That is helpful for synchronizing the spreadsheet content with a database.
height: number Defines the row height in pixels.
index: string The row index text. Default: row number

Examples

A basic spreadsheet with a few programmatic methods available.

<html>
<script src="https://jspreadsheet.com/v9/jspreadsheet.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v9/jspreadsheet.css" type="text/css" />
<scrip src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />

<div id="spreadsheet"></div>

<ol class='example' style='cursor:pointer;'>
    <li><a id="button1">Click to insert a new blank row at the end of the spreadsheet</a></li>
    <li><a id="button2">Click to insert two new blank rows at the beginning of the spreadsheet</a></li>
    <li><a id="button3">Click to insert a new row with pre-populated values at the end of the spreadsheet</a></li>
    <li><a id="button4">Click to delete the last row</a></li>
    <li><a id="button5">Click to move the first row to the third position</a></li>
    <li><a id="button6">Hide the first row</a></li>
    <li><a id="button7">Show the first row</a></li>
</ol>

<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('YjA4N2EzZmE0ZmQ5OWRlZGFhZmJmZmYwM2Q0ZTA5YTQ1Nzk3OGM1YzY3MmRiMjNlOGRjM2U0NDZhN2Q1YWIzOGI3NmY0OGYzZjE2YTVkMTA3NjUxZDg0M2ZhN2ZlNGQxZjc5OTYyMDEzN2QzOWQyODNkNWQ4MTgzNmI4YTYwZGMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3pOalF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Create the spreadsheet
let spreadsheet = jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [{
        data: [
            ['US', 'Cheese', 1000 ],
            ['CA', 'Apples', 1200 ],
            ['CA', 'Carrots', 2000 ],
            ['BR', 'Oranges', 3800 ],
        ],
        worksheetName: 'Row management',
    }],
});

document.getElementById("button1").onclick = () => spreadsheet[0].insertRow()
document.getElementById("button2").onclick = () => spreadsheet[0].insertRow(2, 0, 1);
document.getElementById("button3").onclick = () => spreadsheet[0].insertRow([ '0.99', '1.22', '3.11', '2.21' ]);
document.getElementById("button4").onclick = () => spreadsheet[0].deleteRow();
document.getElementById("button5").onclick = () => spreadsheet[0].moveRow(0, 2);
document.getElementById("button6").onclick = () => spreadsheet[0].hideRow(0);
document.getElementById("button7").onclick = () => spreadsheet[0].showRow(0);
</script>
</html>