Jspreadsheet offers the flexibility to customize your spreadsheet with multiple worksheets. You can config JSS to prevent or allow users from manipulating these worksheets by moving, renaming, or deleting them using the context menu. Moreover, you can create programmatic controls and monitor changes made in the spreadsheet through events.
Documentation
Methods
Available methods to interact programmatically with the worksheets.
Property
Description
getWorksheet(object|string)
Get the worksheet position by worksheet object or by the worksheetId. getWorksheet(worksheetIdent: worksheetInstance | String) => Number
getWorksheetName(number)
Get the worksheet name by index. getWorksheetName(position?: Number) => String
openWorksheet(number)
Open the worksheet by index. openWorksheet(position?: Number) => void
createWorksheet(object, number)
Add a new worksheet to the online spreadsheet based on a given configuration. createWorksheet(worksheetOptions: Object, position?: Number) => worksheetInstance
deleteWorksheet(number)
Delete an existing worksheet by index. deleteWorksheet(position?: Number) => void
renameWorksheet(number, string)
Rename an existing worksheet by index. renameWorksheet(position: Number, title: String) => void
moveWorksheet(number, number, boolean)
Move the position of a worksheet. moveWorksheet(from: Number, to: Number, ignoreDomUpdates?: Boolean) => void
getWorksheetActive()
Get the current active worksheet number. getWorksheetActive() => Number
getWorksheetInstance(number)
Get the worksheet instance by index getWorksheetInstance(position: Number) => worksheetInstance
Initial Settings
You can use the following settings to configure the spreadsheet and worksheet management behavior.
Property
Description
Spreadsheet properties
tabs: boolean|object
Show tabs and allow the user to create new worksheets. Default: false.
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
Intercept the creation of worksheets to cancel or define the configuration of the new worksheet.
onbeforecreateworksheet(worksheet: Object, options: Object, worksheetNumber: Number) : mixed
import { Component, ViewChild, ElementRef } from "@angular/core";
import * as jspreadsheet from "jspreadsheet";
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('MjQ3NzY5ZDNhNzM5ODk5MTk2OTE0ZTUyYWNjMTQ5OGYyYTRhMzU5M2E4NmFlZWRlN2U2YjYxYjE5Nzk0NTAxMTVlMjU0YjViYTQ4MzVjMmFlZDViNDA0OGQ2MjAzZDY5MGZkZGRlNGM3OTNhYzIxODliNDg5MjA4MDg4YWVhZTAsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGczTXpnMU15d2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09');
@Component({
selector: "app-root",
template: `<div #spreadsheet></div>
<input type="button" value="Active worksheet" (click)="alert(this.worksheets[0].parent.getWorksheetActive())">
<input type="button" value="Rename 1st worksheet" (click)="this.worksheets[0].parent.renameWorksheet(0, 'Anything')">`
})
export class AppComponent {
@ViewChild("spreadsheet") spreadsheet: ElementRef;
// Worksheets
worksheets: jspreadsheet.worksheetInstance[];
// Create a new data grid
ngOnInit() {
// Create spreadsheet
this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
// Allow create a new tab button
tabs: true,
// Initial worksheet
worksheets: [
{ minDimensions: [6,6] },
{ minDimensions: [6,6] },
],
});
}
}
Worksheet events
Create a new worksheet and explore the related events.
JavaScript example
<html>
<script src="https://jspreadsheet.com/v10/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v10/jspreadsheet.css" type="text/css" />
<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>
<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('MjQ3NzY5ZDNhNzM5ODk5MTk2OTE0ZTUyYWNjMTQ5OGYyYTRhMzU5M2E4NmFlZWRlN2U2YjYxYjE5Nzk0NTAxMTVlMjU0YjViYTQ4MzVjMmFlZDViNDA0OGQ2MjAzZDY5MGZkZGRlNGM3OTNhYzIxODliNDg5MjA4MDg4YWVhZTAsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGczTXpnMU15d2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09');
// Create the spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
// Allow create a new tab button
tabs: true,
// 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' },
],
// Name of the worksheet
worksheetName: 'Albums',
}
],
// Intercept the new worksheet and define the options for the new worksheet
onbeforecreateworksheet: function() {
var options = {
minDimensions: [5,5],
}
return options;
},
// When you open the worksheet
onopenworksheet: function(element, instance, worksheetNumber) {
console.log(element, instance, worksheetNumber);
},
});
</script>
</html>
React example
import React, { useRef } from "react";
import { Spreadsheet, Worksheet } from "@jspreadsheet/react";
const license = 'MjQ3NzY5ZDNhNzM5ODk5MTk2OTE0ZTUyYWNjMTQ5OGYyYTRhMzU5M2E4NmFlZWRlN2U2YjYxYjE5Nzk0NTAxMTVlMjU0YjViYTQ4MzVjMmFlZDViNDA0OGQ2MjAzZDY5MGZkZGRlNGM3OTNhYzIxODliNDg5MjA4MDg4YWVhZTAsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGczTXpnMU15d2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09';
export default function App() {
// Spreadsheet array of worksheets
const spreadsheet = useRef();
// Data
const 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
const columns = [
{ type: 'autonumber', title: 'Id' },
{ type: 'text', width: '350px', title: 'Title' },
{ type: 'text', width: '250px', title: 'Artist' },
]
// Intercept the action to define the default configuration for each new worksheet
onbeforecreateworksheet = () => {
return {
minDimensions: [5,5],
}
}
// When a new worksheet is opened
onopenworksheet = (element, instance, worksheetNumber) => {
console.log(element, instance, worksheetNumber);
}
// Render component
return (
<Spreadsheet ref={spreadsheet} license={license} tabs
onbeforecreateworksheet={onbeforecreateworksheet} onopenworksheet={onopenworksheet}>
<Worksheet data={data} columns={columns} worksheetName={"Albums"} />
</Spreadsheet>
);
}
Vue example
<template>
<Spreadsheet ref="spreadsheet" :license="license" tabs
:onbeforecreateworksheet="onbeforecreateworksheet" :onopenworksheet="onopenworksheet">
<Worksheet :data="data" :columns="columns" worksheetName="Albums" />
</Spreadsheet>
</template>
<script>
import { Spreadsheet, Worksheet } from "@jspreadsheet/vue";
const license = 'MjQ3NzY5ZDNhNzM5ODk5MTk2OTE0ZTUyYWNjMTQ5OGYyYTRhMzU5M2E4NmFlZWRlN2U2YjYxYjE5Nzk0NTAxMTVlMjU0YjViYTQ4MzVjMmFlZDViNDA0OGQ2MjAzZDY5MGZkZGRlNGM3OTNhYzIxODliNDg5MjA4MDg4YWVhZTAsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGczTXpnMU15d2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09';
export default {
components: {
Spreadsheet,
Worksheet,
},
methods: {
// Intercept the action to define the default configuration for each new worksheet
onbeforecreateworksheet() {
return {
minDimensions: [5,5],
}
}
// When a new worksheet is opened
onopenworksheet(element, instance, worksheetNumber) {
console.log(element, instance, worksheetNumber);
}
},
data() {
return {
// License
license: license,
// Data
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
columns: [
{ type: 'autonumber', title: 'Id' },
{ type: 'text', width: '350px', title: 'Title' },
{ type: 'text', width: '250px', title: 'Artist' },
],
};
}
}
</script>
Angular example
import { Component, ViewChild, ElementRef } from "@angular/core";
import * as jspreadsheet from "jspreadsheet";
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('MjQ3NzY5ZDNhNzM5ODk5MTk2OTE0ZTUyYWNjMTQ5OGYyYTRhMzU5M2E4NmFlZWRlN2U2YjYxYjE5Nzk0NTAxMTVlMjU0YjViYTQ4MzVjMmFlZDViNDA0OGQ2MjAzZDY5MGZkZGRlNGM3OTNhYzIxODliNDg5MjA4MDg4YWVhZTAsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGczTXpnMU15d2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09');
@Component({
selector: "app-root",
template: `<div #spreadsheet></div>`
})
export class AppComponent {
@ViewChild("spreadsheet") spreadsheet: ElementRef;
// Worksheets
worksheets: jspreadsheet.worksheetInstance[];
// Create a new data grid
ngOnInit() {
// Create spreadsheet
this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
// Allow create a new tab button
tabs: true,
// 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' },
],
// Name of the worksheet
worksheetName: 'Albums',
}
],
// Intercept the new worksheet and define the options for the new worksheet
onbeforecreateworksheet: function() {
var options = {
minDimensions: [5,5],
}
return options;
},
// When you open the worksheet
onopenworksheet: function(element, instance, worksheetNumber) {
console.log(element, instance, worksheetNumber);
},
});
}
}
Programmatic operations
Create a new worksheet programmatically.
JavaScript example
<html>
<script src="https://jspreadsheet.com/v10/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v10/jspreadsheet.css" type="text/css" />
<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-2"></div>
<input type="button" value="Create a new worksheet"
onclick="spreadsheet[0].createWorksheet({ minDimensions: [5,5] })">
<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('MjQ3NzY5ZDNhNzM5ODk5MTk2OTE0ZTUyYWNjMTQ5OGYyYTRhMzU5M2E4NmFlZWRlN2U2YjYxYjE5Nzk0NTAxMTVlMjU0YjViYTQ4MzVjMmFlZDViNDA0OGQ2MjAzZDY5MGZkZGRlNGM3OTNhYzIxODliNDg5MjA4MDg4YWVhZTAsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGczTXpnMU15d2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09');
// Create the spreadsheet
var spreadsheet = jspreadsheet(document.getElementById('spreadsheet-2'), {
tabs: true,
worksheets: [
{
minDimensions: [5,5],
worksheetName: 'Example2',
}
]
});
</script>
</html>
import { Component, ViewChild, ElementRef } from "@angular/core";
import * as jspreadsheet from "jspreadsheet";
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('MjQ3NzY5ZDNhNzM5ODk5MTk2OTE0ZTUyYWNjMTQ5OGYyYTRhMzU5M2E4NmFlZWRlN2U2YjYxYjE5Nzk0NTAxMTVlMjU0YjViYTQ4MzVjMmFlZDViNDA0OGQ2MjAzZDY5MGZkZGRlNGM3OTNhYzIxODliNDg5MjA4MDg4YWVhZTAsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGczTXpnMU15d2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09');
@Component({
selector: "app-root",
template: `<div #spreadsheet></div>
<input type="button" value="Create a new worksheet"
onclick="this.worksheets[0].createWorksheet({ minDimensions: [5,5] })">`
})
export class AppComponent {
@ViewChild("spreadsheet") spreadsheet: ElementRef;
// Worksheets
worksheets: jspreadsheet.worksheetInstance[];
// Create a new data grid
ngOnInit() {
// Create spreadsheet
this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
tabs: true,
worksheets: [
{
minDimensions: [5,5],
worksheetName: 'Example2',
}
]
});
}
}
Formulas on worksheets
The Pro distribution of Jspreadsheet allows you to execute formulas using variables from any other worksheet. The syntax follows the standard used by other spreadsheet software like Google Sheets or Excel, which involves using the exclamation mark. For example: Products!A2*10.