One of the most important changes in v8 is worksheet management. Now, there is a spreadsheet component that acts as the container for each new worksheet, which brings several methods, properties and events.
Documentation
Initial Settings
Configure the behavior of the spreadsheet and worksheet management using the worksheet-related settings below.
Property
Description
Spreadsheet properties
tabs: boolean|object
Show tabs and allow the user to create new worksheets. Default: false. It can be used with extended tabs options.
allowDeleteWorksheet: boolean
Add a delete worksheet option to the contextMenu. Default: true
allowRenameWorksheet: boolean
Add the rename worksheet option to the contextMenu. Default: true
allowMoveWorksheet: boolean
Allow worksheet drag and drop options. Default: true
Worksheet properties
worksheetId: string
Worksheet identification. Default: randomNumber
worksheetName: string
Worksheet title. Default: string + integer
Methods
Available methods to interact programmatically with the worksheets.
Property
Description
createWorksheet(object) : object
Add a new worksheet to the online spreadsheet based on a given configuration. createWorksheet(Object configuration) : object
deleteWorksheet(number) : void
Delete worksheet by position. deleteWorksheet(Integer worksheetNumber) : boid
getWorksheet(mixed) : worksheetPosition
Get the worksheet position by worksheet object or by the worksheetId. getWorksheet(Object worksheetInstace | String worksheetId) : number
openWorksheet(number) : void
Open the worksheet with position: worksheetNumber (starts on zero) openWorksheet(Number worksheetPosition) : void
renameWorksheet(number, string)
Change a worksheet title. renameWorksheet(Number worksheetNumber, String worksheetNewTitle) : void
moveWorksheet(number, number)
Update the worksheet position. moveWorksheet(Number fromPosition, Number toPosition) : void
getWorksheetActive()
Get the current active worksheet number. getWorksheetActive() : number
getWorksheetInstance(number)
Get the worksheet instance by number getWorksheetInstance(number) : object
Available events
Available events related to the worksheets.
Event
Description
onopenworksheet
onopenworksheet(Object worksheet, Number worksheetNumber) : void
onbeforecreateworksheet
Before creating a new worksheet, it is possible to overwrite the configuration for the new worksheet or cancel the operation (return false). onbeforecreateworksheet(Object options, Number worksheetNumber) : mixed
oncreateworksheet
oncreateworksheet(Object worksheetInstance, Object worksheetOptions, Number worksheetNumber) : void
onrenameworksheet
onrenameworksheet(Object worksheetInstance, Number worksheetNumber, String newValue, String oldValue) : void;
ondeleteworksheet
ondeleteworksheet(Object oldWorksheetInstance, Number oldWorksheetNumber) : void
onmoveworksheet
onmoveworksheet(Object worksheetInstance, Number newPosition, Number oldPosition) : void
Examples
Active worksheet
How to get the active worksheet number on a JSS spreadsheet.
<html>
<script src="https://jspreadsheet.com/v8/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v8/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<div id="spreadsheet"></div>
<input type="button" value="Get active worksheet" onclick="alert(worksheets[0].parent.getWorksheetActive())">
<input type="button" value="Get active worksheet" onclick="alert(worksheets[0].parent.renameWorksheet(0, 'Anything'))">
<script>
// Set the JSS spreadsheet license
jspreadsheet.setLicense('NDMwMGZhYmZjNDY2MGE0OWRjODczMDRmZGJjMGUzMTc2MjcwNzM5YTU3YTA5NTg0ZmNkNTQxZTI0MjE1NzA2M2EyMGVhYjFlY2U4NjM4NjJhMDZlODRjYTJjNjQwNjVkNWZkZDY4OGY4MWIyZjJiZTM0OTRjYmI5YmEwZGFhM2IsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UY3dNak0zTURjNE1pd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Create the spreadsheet
var worksheets = jspreadsheet(document.getElementById('spreadsheet'), {
// Allow create a new tab button
tabs: true,
// Initial worksheet
worksheets: [
{ minDimensions: [6,6] },
{ minDimensions: [6,6] },
],
});
</script>
</html>
Add new worksheet button
Allow users to add new worksheets to an existing spreadsheet.
Source code
<html>
<script src="https://jspreadsheet.com/v8/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v8/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<div id="spreadsheet"></div>
<script>
// Set the JSS spreadsheet license
jspreadsheet.setLicense('NDMwMGZhYmZjNDY2MGE0OWRjODczMDRmZGJjMGUzMTc2MjcwNzM5YTU3YTA5NTg0ZmNkNTQxZTI0MjE1NzA2M2EyMGVhYjFlY2U4NjM4NjJhMDZlODRjYTJjNjQwNjVkNWZkZDY4OGY4MWIyZjJiZTM0OTRjYmI5YmEwZGFhM2IsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UY3dNak0zTURjNE1pd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Create the spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
// Allow create a new tab button
tabs: true,
// Intercept the new worksheet and define the options for the new worksheet
onbeforecreateworksheet: function() {
var options = {
minDimensions: [5,5],
}
return options;
},
// Run an event as soon the new worksheet is created
onopenworksheet: function(element, instance, worksheetNumber) {
console.log(element, instance, worksheetNumber);
},
// Initial worksheet
worksheets: [
{
data: [
["1","DIVINELY UNINSPIRED TO A HELLISH EXTENT","LEWIS CAPALDI"],
["2","NO 6 COLLABORATIONS PROJECT","ED SHEERAN"],
["3","THE GREATEST SHOWMAN","MOTION PICTURE CAST RECORDING"],
["4","WHEN WE ALL FALL ASLEEP WHERE DO WE GO","BILLIE EILISH"]
],
columns: [
{ type: 'autonumber', title: 'Id' },
{ type: 'text', width: '350px', title: 'Title' },
{ type: 'text', width: '250px', title: 'Artist' },
]
}
]
});
</script>
</html>
Programmatic operations on worksheets
Create a new worksheet programmatically.
Source code
<html>
<script src="https://jspreadsheet.com/v8/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v8/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<div id="spreadsheet-2"></div>
<input type="button" value="Create a new worksheet"
onclick="spreadsheet[0].createWorksheet({ minDimensions: [5,5] })">
<script>
// Set the JSS spreadsheet license
jspreadsheet.setLicense('NDMwMGZhYmZjNDY2MGE0OWRjODczMDRmZGJjMGUzMTc2MjcwNzM5YTU3YTA5NTg0ZmNkNTQxZTI0MjE1NzA2M2EyMGVhYjFlY2U4NjM4NjJhMDZlODRjYTJjNjQwNjVkNWZkZDY4OGY4MWIyZjJiZTM0OTRjYmI5YmEwZGFhM2IsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UY3dNak0zTURjNE1pd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Create the spreadsheet
var spreadsheet = jspreadsheet(document.getElementById('spreadsheet-2'), {
tabs: true,
worksheets: [
{
minDimensions: [5,5],
defaultColWidth: '100px',
worksheetName: 'Example2',
}
]
});
</script>
</html>
Formulas on worksheets Pro feature
With the Pro distribution, it is possible to execute formulas with variables from any other worksheet. The syntax follow the same standard as other spreadsheet software such as Google Sheets or Excel using the exclamation mark, for instance "Products!A2*10".
