Custom formulas
Harness the full potential of Jspreadsheet by creating custom formulas that replicate the behavior of traditional spreadsheets. With JSS, you can leverage the power of this feature to return not only strings and numbers but also complex DOM elements that can be used to build rich user interfaces. The methods called by the formulas receive as parameters the coordinates of the cells involved and the worksheet instance, providing a wide range of possibilities for conditional operations and advanced data manipulation.
Requirements This feature is available with the Formula Pro extension.
Special properties
When defining a custom Excel-like formula in Jspreadsheet, the this object provides access to three special properties that contain information about the cell where the formula was invoked and the worksheet instance that contains the cell.
Example
How to use of the cell coordinates and the worksheet instance for conditional operations inside excel-like custom methods.
<html> <script src="https://jspreadsheet.com/v12/jspreadsheet.js"></script> <script src="https://jsuites.net/v6/jsuites.js"></script> <link rel="stylesheet" href="https://jspreadsheet.com/v12/jspreadsheet.css" type="text/css" /> <link rel="stylesheet" href="https://jsuites.net/v6/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> // You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox. // The license is valid for one day, after which the spreadsheet will become read-only. // For a longer trial period, you can create a free account and generate a demo license with an extended expiration date. jspreadsheet.setLicense('NDQwMjcyY2VmNDYwMzgyNjVkNGM5NjI3YTUyNmFlMDVjZGZmNWYyZjNiZDE1YTBiOTU5NWJhZjIyZDRhNjU5NWI1NmFhMWUwYjQyMWJlN2UxZjZiYjA2MzZmYWQyNWUyZDlmMTQ3MjNlNjZlZDM3ZDdkOGEyMzFmYjUzZjNlZGIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpjME1UWTJOak0wTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9'); // 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); // Total let total = 0; // Sum all values in the column from zero to the row number for (let j = 0; j < this.y; j++) { total += parseInt(this.instance.options.data[j][this.x]) || 0; // Formula chain if (typeof(this.instance.records[j][this.x].chain) === 'undefined') { this.instance.records[j][this.x].chain = new Map; } // Keep reference in the formula this.instance.records[j][this.x].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: [8,4], }] }); </script> </html> import React, { useRef } from "react"; import { Spreadsheet, Worksheet } from "@jspreadsheet/react"; import formula from "@jspreadsheet/formula-pro"; import "jsuites/dist/jsuites.css"; import "jspreadsheet/dist/jspreadsheet.css"; // You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox. // The license is valid for one day, after which the spreadsheet will become read-only. // For a longer trial period, you can create a free account and generate a demo license with an extended expiration date. const license = 'NDQwMjcyY2VmNDYwMzgyNjVkNGM5NjI3YTUyNmFlMDVjZGZmNWYyZjNiZDE1YTBiOTU5NWJhZjIyZDRhNjU5NWI1NmFhMWUwYjQyMWJlN2UxZjZiYjA2MzZmYWQyNWUyZDlmMTQ3MjNlNjZlZDM3ZDdkOGEyMzFmYjUzZjNlZGIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpjME1UWTJOak0wTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9'; // 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); // Total let total = 0; // Sum all values in the column from zero to the row number for (let j = 0; j < this.y; j++) { total += parseInt(this.instance.options.data[j][this.x]) || 0; // Formula chain if (typeof(this.instance.records[j][this.x].chain) === 'undefined') { this.instance.records[j][this.x].chain = new Map; } // Keep reference in the formula this.instance.records[j][this.x].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={[8,4]} /> </Spreadsheet> ); } <template> <Spreadsheet ref="spreadsheet" :license="license" :extensions="extensions"> <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 = 'NDQwMjcyY2VmNDYwMzgyNjVkNGM5NjI3YTUyNmFlMDVjZGZmNWYyZjNiZDE1YTBiOTU5NWJhZjIyZDRhNjU5NWI1NmFhMWUwYjQyMWJlN2UxZjZiYjA2MzZmYWQyNWUyZDlmMTQ3MjNlNjZlZDM3ZDdkOGEyMzFmYjUzZjNlZGIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpjME1UWTJOak0wTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9'; // 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); // Total let total = 0; // Sum all values in the column from zero to the row number for (let j = 0; j < this.y; j++) { total += parseInt(this.instance.options.data[j][this.x]) || 0; // Formula chain if (typeof(this.instance.records[j][this.x].chain) === 'undefined') { this.instance.records[j][this.x].chain = new Map; } // Keep reference in the formula this.instance.records[j][this.x].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, extensions, } } } </script> import { Component, ViewChild, ElementRef } from "@angular/core"; import jspreadsheet from "jspreadsheet"; import formula from "@jspreadsheet/formula-pro"; // You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox. // The license is valid for one day, after which the spreadsheet will become read-only. // For a longer trial period, you can create a free account and generate a demo license with an extended expiration date. jspreadsheet.setLicense('NDQwMjcyY2VmNDYwMzgyNjVkNGM5NjI3YTUyNmFlMDVjZGZmNWYyZjNiZDE1YTBiOTU5NWJhZjIyZDRhNjU5NWI1NmFhMWUwYjQyMWJlN2UxZjZiYjA2MzZmYWQyNWUyZDlmMTQ3MjNlNjZlZDM3ZDdkOGEyMzFmYjUzZjNlZGIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpjME1UWTJOak0wTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9'); // Set extensions jspreadsheet.setExtensions({ formula }); // Create a custom javascript method (capital case) const SUMCOL = function (this: any) { // Total let total = 0; // Sum all values in the column up to where the formula was called. for (let j = 0; j < this.y; j++) { total += parseInt(this.instance.options.data[j][this.x]) || 0; // Formula chain if (typeof this.instance.records[j][this.x].chain === 'undefined') { this.instance.records[j][this.x].chain = new Map(); } // Keep reference in the formula this.instance.records[j][this.x].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: [8, 4], }, ], }); } } DOM Elements
The cells accept a DOM element returned from a formula, as below:
<html> <script src="https://jspreadsheet.com/v12/jspreadsheet.js"></script> <script src="https://jsuites.net/v6/jsuites.js"></script> <link rel="stylesheet" href="https://jspreadsheet.com/v12/jspreadsheet.css" type="text/css" /> <link rel="stylesheet" href="https://jsuites.net/v6/jsuites.css" type="text/css" /> <link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" /> <div id="spreadsheet"></div> <script> // You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox. // The license is valid for one day, after which the spreadsheet will become read-only. // For a longer trial period, you can create a free account and generate a demo license with an extended expiration date. jspreadsheet.setLicense('NDQwMjcyY2VmNDYwMzgyNjVkNGM5NjI3YTUyNmFlMDVjZGZmNWYyZjNiZDE1YTBiOTU5NWJhZjIyZDRhNjU5NWI1NmFhMWUwYjQyMWJlN2UxZjZiYjA2MzZmYWQyNWUyZDlmMTQ3MjNlNjZlZDM3ZDdkOGEyMzFmYjUzZjNlZGIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpjME1UWTJOak0wTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9'); // 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"; // You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox. // The license is valid for one day, after which the spreadsheet will become read-only. // For a longer trial period, you can create a free account and generate a demo license with an extended expiration date. const license = 'NDQwMjcyY2VmNDYwMzgyNjVkNGM5NjI3YTUyNmFlMDVjZGZmNWYyZjNiZDE1YTBiOTU5NWJhZjIyZDRhNjU5NWI1NmFhMWUwYjQyMWJlN2UxZjZiYjA2MzZmYWQyNWUyZDlmMTQ3MjNlNjZlZDM3ZDdkOGEyMzFmYjUzZjNlZGIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpjME1UWTJOak0wTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9'; // 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"; // You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox. // The license is valid for one day, after which the spreadsheet will become read-only. // For a longer trial period, you can create a free account and generate a demo license with an extended expiration date. const license = 'NDQwMjcyY2VmNDYwMzgyNjVkNGM5NjI3YTUyNmFlMDVjZGZmNWYyZjNiZDE1YTBiOTU5NWJhZjIyZDRhNjU5NWI1NmFhMWUwYjQyMWJlN2UxZjZiYjA2MzZmYWQyNWUyZDlmMTQ3MjNlNjZlZDM3ZDdkOGEyMzFmYjUzZjNlZGIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpjME1UWTJOak0wTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9'; // 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 { data, columns, license, extensions, } } } </script> import { Component, ViewChild, ElementRef } from "@angular/core"; import jspreadsheet from "jspreadsheet"; import formula from "@jspreadsheet/formula-pro"; // You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox. // The license is valid for one day, after which the spreadsheet will become read-only. // For a longer trial period, you can create a free account and generate a demo license with an extended expiration date. jspreadsheet.setLicense('NDQwMjcyY2VmNDYwMzgyNjVkNGM5NjI3YTUyNmFlMDVjZGZmNWYyZjNiZDE1YTBiOTU5NWJhZjIyZDRhNjU5NWI1NmFhMWUwYjQyMWJlN2UxZjZiYjA2MzZmYWQyNWUyZDlmMTQ3MjNlNjZlZDM3ZDdkOGEyMzFmYjUzZjNlZGIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpjME1UWTJOak0wTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9'); // 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({ 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' }, ] }] }); } }