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.

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/v8/jspreadsheet.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v8/jspreadsheet.css" type="text/css" />
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />

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

<ol class='example' style='cursor:pointer;'>
    <li><a id="insertblankbtn">Click to insert a new blank row at the end of the spreadsheet</a></li>
    <li><a id="inserttwobtn">Click to insert two new blank rows at the beginning of the spreadsheet</a></li>
    <li><a id="insertprepopbtn">Click to insert a new row with pre-populated values at the end of the spreadsheet</a></li>
    <li><a id="deletelastbtn">Click to delete the last row</a></li>
    <li><a id="movefirstbtn">Click to move the first row to the third position</a></li>
    <li><a id="hidefirstbtn">Hide the first row</a></li>
    <li><a id="showfirstbtn">Show the first row</a></li>
</ol>

<script>
// Set the JSS spreadsheet license
jspreadsheet.setLicense('YTExMDAzMGIxZTRjNzZlNzE3N2YxNzQ2OTUyOWJiNmY3NGI2YmUzYWU4NTQwMDM4MjkzMmIzOWM5NGNkNjBhZTg3MWZmNjAwMGJkN2ZmZjljZmEyNGJlMDdiMGU5YzJlNjg4NGQ2NzI2ZWNmZjc5OGQwNDliMTMxMDRmZDFhMjIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNeU16TXpNVGcyTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');

// 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("insertblankbtn").onclick = () => spreadsheet[0].insertRow();
document.getElementById("inserttwobtn").onclick = () => spreadsheet[0].insertRow(2, 0, 1);
document.getElementById("insertprepopbtn").onclick = () => spreadsheet[0].insertRow([{ data: ['0.99', '1.22', '3.11', '2.21' ]}]);
document.getElementById("deletelastbtn").onclick = () => spreadsheet[0].deleteRow();
document.getElementById("movefirstbtn").onclick = () => spreadsheet[0].moveRow(0, 2);
document.getElementById("hidefirstbtn").onclick = () => spreadsheet[0].hideRow(0);
document.getElementById("showfirstbtn").onclick = () => spreadsheet[0].showRow(0);
</script>
</html>

Edit this page on GitHub