Custom Excel-like Formulas
Jspreadsheet enables developers to create custom Excel-like formulas and methods. These methods can interact with APIs and return specific outputs, such as content or DOM elements for cells, facilitating the creation of dynamic and interactive applications within spreadsheets.
IMPORTANT : All method names should be created using capital letters.
Documentation
Related methods, object and attributes.
Method | Description |
---|---|
worksheet.setTracking | This method, part of the worksheet instance, adds a cell to a monitored list that automatically updates whenever the worksheet's structure changes, such as inserting rows or columns. |
worksheets.records[y][x] | Cell object, this object is created when is in use. |
worksheets.records[y][x].chain | Object that keeps a reference to all cells that need to be updated when a cell changes. |
Formula Scope Object
In Jspreadsheet Pro, invoking a method from a cell provides access to that cell's coordinates via this.x
, this.y
and the worksheet instance using this.instance
. This allows developers to create rich functions to connect to external APIs or perform custom operations within the worksheet.
let COORDS = function() {
// Return the coordinates to the cell
return this.x + ',' + this.y;
}
Declare the Methods
To utilize a custom method in your spreadsheets, declare it using formula.setFormulas
as below.
formula.setFormulas({ COORDS });
Examples
Using DOM elements
Create a custom method to return a DOM element to the cell.
<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v11/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('OTM0ZDRiMjgzOWQwZWEyZjFhMmEyNDJkNDVhZDI1YzhjZjczNDUwMjBiYjM0NGViODhlNjRjNzZlZTI2ZGFiZDE1ZWMxNjQzMjVjZmNmNTJlY2E5OTY2Zjk5M2YwMWI5MDQ1NmI3NGNkMTVkYWY1MWFmNzIyZWEzZmEyMTYyYzQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNek5UUXpOalkzTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
// Create a custom javascript method (capital case)
const COLORIZE = function(v) {
let d = document.createElement('span');
d.style.color = v;
d.innerText = v.toUpperCase();
return d;
}
// Send custom formula to the correct scope
formula.setFormula({ COLORIZE })
// Create spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
data: [
[ 'red', '=COLORIZE(A1)' ],
[ 'green', '=COLORIZE(A2)' ],
[ 'blue', '=COLORIZE(A3)' ],
],
columns: [
{ type: 'text', width:'300' },
{ type: 'text', width:'200' },
]
}]
});
</script>
</html>
import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
// Set your JSS license key (The following key only works for one day)
const license = 'OTM0ZDRiMjgzOWQwZWEyZjFhMmEyNDJkNDVhZDI1YzhjZjczNDUwMjBiYjM0NGViODhlNjRjNzZlZTI2ZGFiZDE1ZWMxNjQzMjVjZmNmNTJlY2E5OTY2Zjk5M2YwMWI5MDQ1NmI3NGNkMTVkYWY1MWFmNzIyZWEzZmEyMTYyYzQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNek5UUXpOalkzTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==';
// Create a custom javascript method (capital case)
const COLORIZE = function(v) {
let d = document.createElement('span');
d.style.color = v;
d.innerText = v.toUpperCase();
return d;
}
// Send custom formula to the correct scope
formula.setFormula({ COLORIZE })
// Extensions
const extensions = { formula };
// Create the component
export default function App() {
// Array with all the data grids
const spreadsheet = useRef();
// Data
const data = [
[ 'red', '=COLORIZE(A1)' ],
[ 'green', '=COLORIZE(A2)' ],
[ 'blue', '=COLORIZE(A3)' ],
]
const columns = [
{ type: 'text', width:'300px' },
{ type: 'text', width:'200px' },
]
// Render data grid component
return (
<Spreadsheet ref={spreadsheet} license={license} extensions={extensions}>
<Worksheet data={data} columns={columns} />
</Spreadsheet>
);
}
<template>
<Spreadsheet ref="spreadsheet" :license="license" :extensions="extensions">
<Worksheet :data="data" :columns="columns" />
</Spreadsheet>
</template>
<script>
import { Spreadsheet, Worksheet } from "@jspreadsheet/vue";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
// Set your JSS license key (The following key only works for one day)
const license = 'OTM0ZDRiMjgzOWQwZWEyZjFhMmEyNDJkNDVhZDI1YzhjZjczNDUwMjBiYjM0NGViODhlNjRjNzZlZTI2ZGFiZDE1ZWMxNjQzMjVjZmNmNTJlY2E5OTY2Zjk5M2YwMWI5MDQ1NmI3NGNkMTVkYWY1MWFmNzIyZWEzZmEyMTYyYzQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNek5UUXpOalkzTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==';
// Create a custom javascript method (capital case)
const COLORIZE = function(v) {
let d = document.createElement('span');
d.style.color = v;
d.innerText = v.toUpperCase();
return d;
}
// Send custom formula to the correct scope
formula.setFormula({ COLORIZE })
// Extensions
const extensions = { formula };
// Create data grid component
export default {
components: {
Spreadsheet,
Worksheet,
},
data() {
// Data
const data = [
[ 'red', '=COLORIZE(A1)' ],
[ 'green', '=COLORIZE(A2)' ],
[ 'blue', '=COLORIZE(A3)' ],
]
const columns = [
{ type: 'text', width:'300px' },
{ type: 'text', width:'200px' },
]
return {
extensions,
data,
columns,
license,
};
}
}
</script>
import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import "jspreadsheet/dist/jspreadsheet.css"
import "jsuites/dist/jsuites.css"
import formula from "@jspreadsheet/formula-pro";
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('OTM0ZDRiMjgzOWQwZWEyZjFhMmEyNDJkNDVhZDI1YzhjZjczNDUwMjBiYjM0NGViODhlNjRjNzZlZTI2ZGFiZDE1ZWMxNjQzMjVjZmNmNTJlY2E5OTY2Zjk5M2YwMWI5MDQ1NmI3NGNkMTVkYWY1MWFmNzIyZWEzZmEyMTYyYzQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNek5UUXpOalkzTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
jspreadsheet.setExtensions({ formula })
// Create a custom javascript method (capital case)
const COLORIZE = (v) => {
let d = document.createElement('span');
d.style.color = v;
d.innerText = v.toUpperCase();
return d;
}
// Send custom formula to the correct scope
formula.setFormula({ COLORIZE })
// Create the data grid component
@Component({
standalone: true,
selector: "app-root",
template: `<div #spreadsheet></div>`,
})
export class AppComponent {
@ViewChild("spreadsheet") spreadsheet: ElementRef;
// Worksheets
worksheets: jspreadsheet.worksheetInstance[];
// Create a new data grid
ngAfterViewInit() {
// Create spreadsheet
this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
worksheets: [{
data: [
[ 'red', '=COLORIZE(A1)' ],
[ 'green', '=COLORIZE(A2)' ],
[ 'blue', '=COLORIZE(A3)' ],
],
columns: [
{ type: 'text', width: 300 },
{ type: 'text', width: 200 },
]
}]
});
}
}
Formula Chain
In this example, a custom formula calculates the sum of all values in the same column above the cell where the formula is applied. Since the formula itself doesn't reference other cells, a manual chain of dependencies is created to ensure that changes in the column values are dynamically updated in the calculation.
<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v11/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" />
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>
<div id="spreadsheet"></div>
<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('OTM0ZDRiMjgzOWQwZWEyZjFhMmEyNDJkNDVhZDI1YzhjZjczNDUwMjBiYjM0NGViODhlNjRjNzZlZTI2ZGFiZDE1ZWMxNjQzMjVjZmNmNTJlY2E5OTY2Zjk5M2YwMWI5MDQ1NmI3NGNkMTVkYWY1MWFmNzIyZWEzZmEyMTYyYzQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNek5UUXpOalkzTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
// Set the formula pro extension
jspreadsheet.setExtensions({ formula });
// Create a custom javascript method (capital case)
const SUMCOL = function () {
// Update formula chain when a new row is added
this.instance.setTracking.call(this, true);
// Get the column data
let data = this.instance.getColumnData(this.x);
// Total
let total = 0;
// Sum all values in the column from zero to the row number
for (let y = 0; y < this.y; y++) {
// Get the cell object
let cell = this.instance.getCellObject(this.x, y);
// Total
total += parseInt(data[y]);
// Formula chain
if (typeof(cell.chain) === 'undefined') {
cell.chain = new Map;
}
// Keep reference in the formula
cell.chain.set(this.instance.records[this.y][this.x], this.instance);
}
return total;
}
// Send custom formula to the correct scope
formula.setFormula({ SUMCOL })
// Create the spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
data: [
[ 'Apple', 931 ],
[ 'Google', 431 ],
[ 'Amazon', 534 ],
[ 'Total', '=SUMCOL()' ],
],
minDimensions: [6,4],
}]
});
</script>
</html>
import React, { useRef } from "react";
import { Spreadsheet, Worksheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
// Set your JSS license key (The following key only works for one day)
const license = 'OTM0ZDRiMjgzOWQwZWEyZjFhMmEyNDJkNDVhZDI1YzhjZjczNDUwMjBiYjM0NGViODhlNjRjNzZlZTI2ZGFiZDE1ZWMxNjQzMjVjZmNmNTJlY2E5OTY2Zjk5M2YwMWI5MDQ1NmI3NGNkMTVkYWY1MWFmNzIyZWEzZmEyMTYyYzQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNek5UUXpOalkzTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==';
// Extensions
const extensions = { formula };
// Create a custom javascript method (capital case)
const SUMCOL = function () {
// Update formula chain when a new row is added
this.instance.setTracking.call(this, true);
// Get the column data
let data = this.instance.getColumnData(this.x);
// Total
let total = 0;
// Sum all values in the column from zero to the row number
for (let y = 0; y < this.y; y++) {
// Get the cell object
let cell = this.instance.getCellObject(this.x, y);
// Total
total += parseInt(data[y]);
// Formula chain
if (typeof(cell.chain) === 'undefined') {
cell.chain = new Map;
}
// Keep reference in the formula
cell.chain.set(this.instance.records[this.y][this.x], this.instance);
}
return total;
}
// Send custom formula to the correct scope
formula.setFormula({ SUMCOL })
// Create the component
export default function App() {
// Array with all the data grids
const spreadsheet = useRef();
// Data
const data = [
[ 'Apple', 931 ],
[ 'Google', 431 ],
[ 'Amazon', 534 ],
[ 'Total', '=SUMCOL()' ],
]
// Render data grid component
return (
<Spreadsheet ref={spreadsheet} license={license} extensions={extensions}>
<Worksheet data={data} minDimensions={[6,4]} />
</Spreadsheet>
);
}
<template>
<Spreadsheet ref="spreadsheet" :license="license">
<Worksheet :data="data" />
</Spreadsheet>
</template>
<script>
import { Spreadsheet, Worksheet } from "@jspreadsheet/vue";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
// Add here your data grid license
const license = 'OTM0ZDRiMjgzOWQwZWEyZjFhMmEyNDJkNDVhZDI1YzhjZjczNDUwMjBiYjM0NGViODhlNjRjNzZlZTI2ZGFiZDE1ZWMxNjQzMjVjZmNmNTJlY2E5OTY2Zjk5M2YwMWI5MDQ1NmI3NGNkMTVkYWY1MWFmNzIyZWEzZmEyMTYyYzQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNek5UUXpOalkzTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==';
// Extensions
const extensions = { formula };
// Create a custom javascript method (capital case)
const SUMCOL = function () {
// Update formula chain when a new row is added
this.instance.setTracking.call(this, true);
// Get the column data
let data = this.instance.getColumnData(this.x);
// Total
let total = 0;
// Sum all values in the column from zero to the row number
for (let y = 0; y < this.y; y++) {
// Get the cell object
let cell = this.instance.getCellObject(this.x, y);
// Total
total += parseInt(data[y]);
// Formula chain
if (typeof(cell.chain) === 'undefined') {
cell.chain = new Map;
}
// Keep reference in the formula
cell.chain.set(this.instance.records[this.y][this.x], this.instance);
}
return total;
}
// Send custom formula to the correct scope
formula.setFormula({ SUMCOL })
export default {
components: {
Spreadsheet,
Worksheet,
},
data() {
// Data
const data = [
[ 'Apple', 931 ],
[ 'Google', 431 ],
[ 'Amazon', 534 ],
[ 'Total', '=SUMCOL()' ],
];
return {
data,
license,
}
}
}
</script>
import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import "jspreadsheet/dist/jspreadsheet.css"
import "jsuites/dist/jsuites.css"
import formula from "@jspreadsheet/formula-pro";
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('OTM0ZDRiMjgzOWQwZWEyZjFhMmEyNDJkNDVhZDI1YzhjZjczNDUwMjBiYjM0NGViODhlNjRjNzZlZTI2ZGFiZDE1ZWMxNjQzMjVjZmNmNTJlY2E5OTY2Zjk5M2YwMWI5MDQ1NmI3NGNkMTVkYWY1MWFmNzIyZWEzZmEyMTYyYzQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNek5UUXpOalkzTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
// Set extensions
jspreadsheet.setExtensions({ formula });
// Create a custom javascript method (capital case)
const SUMCOL = function () {
// Update formula chain when a new row is added
this.instance.setTracking.call(this, true);
// Get the column data
let data = this.instance.getColumnData(this.x);
// Total
let total = 0;
// Sum all values in the column from zero to the row number
for (let y = 0; y < this.y; y++) {
// Get the cell object
let cell = this.instance.getCellObject(this.x, y);
// Total
total += parseInt(data[y]);
// Formula chain
if (typeof(cell.chain) === 'undefined') {
cell.chain = new Map;
}
// Keep reference in the formula
cell.chain.set(this.instance.records[this.y][this.x], this.instance);
}
return total;
}
// Send custom formula to the correct scope
formula.setFormula({ SUMCOL })
@Component({
standalone: true,
selector: "app-root",
template: `<div #spreadsheet></div>`,
})
export class AppComponent {
@ViewChild("spreadsheet") spreadsheet: ElementRef;
// Worksheets
worksheets: jspreadsheet.worksheetInstance[];
// Create a new data grid
ngAfterViewInit() {
// Create spreadsheet
this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
worksheets: [
{
data: [
[ 'Apple', 931 ],
[ 'Google', 431 ],
[ 'Amazon', 534 ],
[ 'Total', '=SUMCOL()' ],
],
minDimensions: [6,4],
}
]
});
}
}