Rows

In this section, we are going to cover more information about rows management.

Documentation

Methods

The following methods interact with the rows programmatically.
MethodDescription
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 on 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 the number
hideRow Hide a row by number.
hideRow(rowNumber: Number) => void
@param {number} rowNumber - Hide the row by the 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 intercept, validate or cancel a user action.
EventDescription
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 excluded. Return false to cancel the user action.
ondeleterow ondeleterow(worksheet: Object, rowRecords: DOMElements, rowData: Array, cellAttributes: Object)
After a row is excluded.
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 a row height change for one or more rows.


Initial Settings

The following row-related properties are available through the spreadsheet initialization.
PropertyDescription
allowInsertRow: booleanEnable the user to enter new rows. Default: true
allowManualInsertRow: booleanA new row is automatically inserted when the user press the enter key in the last row. Default: true
allowDeleteRow: booleanAllow the user to delete rows. Default: true
rowDrag: booleanAllow the user to change the position of one row by drag and drop. Default: true
rowResize: booleanAllow the user to resize rows. Default: true
defaultRowHeight: numberThe default row height. From version 8: that can have a impact on the scroll calculations.
minSpareRows: numberThe mandatory number of blank rows in the end of the spreadsheet. Default: none.


Available properties

It is possible to initiate the spreadsheet with a custom id, text and height using the following properties.
PropertyDescription
id: numberDefine the record id. That is helpful to syncronize the spreadsheet content with a database.
height: numberDefine the row height in pixels.
index: stringThe row index text. Default: row number



Examples

A basic spreadsheet and a few programmatic methods available.



  1. Click to insert a new blank row at the end of the spreadsheet
  2. Click to insert two new blank rows at the beginning of the spreadsheet
  3. Click to insert a new row with pre-populated values at the end of the spreadsheet
  4. Click to delete the last row
  5. Click to move the first row to the third position
  6. Hide the first row
  7. Show the first row

Source code

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

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

<ol class='example cursor'>
    <li><a onclick="spreadsheet.insertRow()">Click to insert a new blank row at the end of the spreadsheet</a></li>
    <li><a onclick="spreadsheet.insertRow(2, 0, 1);">Click to insert two new blank rows at the beginning of the spreadsheet</a></li>
    <li><a onclick="spreadsheet.insertRow([ '0.99', '1.22', '3.11', '2.21' ]);">Click to insert a new row with pre-populated values at the end of the spreadsheet</a></li>
    <li><a onclick="spreadsheet.deleteRow();">Click to delete the last row</a></li>
    <li><a onclick="spreadsheet.moveRow(0, 2);">Click to move the first row to the third position</a></li>
    <li><a onclick="spreadsheet.hideRow(0);">Hide the first row</a></li>
    <li><a onclick="spreadsheet.showRow(0);">Show the first row</a></li>
</ol>

<script>
var spreadsheet = jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [{
        data: [
            ['US', 'Cheese', 1000 ],
            ['CA', 'Apples', 1200 ],
            ['CA', 'Carrots', 2000 ],
            ['BR', 'Oranges', 3800 ],
        ],
        worksheetName: 'Row management',
    }],
    license: 'MWEzMTE4MGFkNWY5YzQzNjE4NjZiNmE1NThhM2M0Yjc1NmUyNGM2N2YzZjU2NDQ5ZjM1MGFiYWNmOTFkNTkwODFiYmYwNDE1YjhhM2ViNGUyMzM2YjYzY2Q4NTcyMWE4MGQ4YjVjNjI2NWY4NWYyMTBjMWU5M2ZmNTU4OGI1MDQsZXlKdVlXMWxJam9pY0dGMWJDNW9iMlJsYkNJc0ltUmhkR1VpT2pFMk5UZzVOakk0TURBc0ltUnZiV0ZwYmlJNld5SnFjM0J5WldGa2MyaGxaWFF1WTI5dElpd2lZM05pTG1Gd2NDSXNJbXB6YUdWc2JDNXVaWFFpTENKc2IyTmhiR2h2YzNRaVhTd2ljR3hoYmlJNklqSWlMQ0p6WTI5d1pTSTZXeUoyTnlJc0luWTRJaXdpY0dGeWMyVnlJaXdpYzJobFpYUnpJaXdpWm05eWJYTWlMQ0p5Wlc1a1pYSWlMQ0ptYjNKdGRXeGhJbDE5',
});
</script>
</html>