Excel-Like Formulas
Jspreadsheet supports Excel-like formulas, offering excellent compatibility with popular spreadsheet applications such as Excel and Google Sheets. This section details the range of formulas you can utilize, guides you through creating custom formulas, and discusses additional formula-related functionalities to enhance your spreadsheet tasks.
- Formula Auto-update: Automatically adjusts formulas during cell actions such as copy and paste.
- Excel-Style Formulas: Customizable formulas that resemble Excel.
- Cross-Calculations: Calculations across different sheets.
- DOM in Cells: Formulas can output DOM objects to cells.
- Range Calculations: Perform operations with arrays.
The formula engine offers two tiers: Formula Basic, included by default, and Formula Pro, available as an extension. Formula Pro is required for many advanced operations and extended formulas. For additional details about its features and capabilities, please visit Formula Pro.
Documentation
This section details settings, methods, and events associated with spreadsheet calculations in Jspreadsheet. Note that all formula names, including custom ones, should be capitalized for consistency and functionality.
Settings
Configuration options related to formula usage.
| Configuration | Description |
|---|---|
secureFormulas?: boolean |
Enables formula security. Default: true |
editorFormulas?: boolean |
Enables the formula editor. Default: true |
parseFormulas?: boolean |
Enables formula calculations. Default: true |
debugFormulas?: boolean |
Enable formula debug notices. Default: true |
autoIncrement?: boolean |
Enable formula variable increment on cloning or copying. Default: true |
columnNamesInFormulas?: boolean |
Enable calculations using column names. Only column names with more than three characters will be registered. |
Events
Events related to formulas.
| Event | Description |
|---|---|
onbeforeformula?: Function |
Intercepts and parses a formula just before execution. onbeforeformula(worksheet: Object, expression: String, x: Number, y: Number) => String |
onformulachain?: Function |
Gets information about the expressions executed from the formula chain. onformulachain(worksheet: Object, executions: Object) => void |
Methods
Methods related to formulas in the Jspreadsheet context.
| Method | Description |
|---|---|
executeFormula?: Function |
Executes a formula. executeFormula(expression: string, x?: number, y?: number, caching?: boolean, basic?: boolean) => String |
Calculation State
You can queue/release calculations using jspreadsheet.calculations when loading multiple spreadsheets with cross-calculations. Read more
Available Formulas
List of Available Formulas
You can find details of all available formulas here.
Formula Feature Roadmap
Ongoing enhancements and new method introductions are part of our commitment to align Jspreadsheet with other spreadsheet software standards, such as Excel and Google Sheets.
Advanced Usage
Formula Pro Extension
Jspreadsheet offers formulas in two tiers: Basic and Premium. The Basic tier is included with all Jspreadsheet distributions. The Premium tier, available as an extension for Enterprise and Premium plans, includes additional advanced features:
- Matrix Calculations: Complex matrix operations.
- New Operators: '%' for percentages, '@' for formula specifics.
- Range Operations: Row/column range operations (A:A, 1:1).
- Extended Formulas: Provides access to advanced formulas.
- Private Scope: Secure, isolated formula execution.
- Special Properties: 'x', 'y', 'instance' for dynamic formulas.
- Standalone Compatibility: Formula execution in separate apps.
- Formula Picker: Easy formula selection and insertion.
Custom Formulas
Jspreadsheet allows you to create custom Excel-like formulas. We have a dedicated page that explains more about custom formulas.
Examples
Basic Spreadsheet with Formulas
A basic spreadsheet example using formulas, including currency formatting, percentages, and masks.
<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v6/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v6/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.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('YTFjNjMwMGM2OWVlZTYzMzc5MTBjOTQ4MTNmOTg5NzhkODk1MDg4ZTI0ZjljYmFlYTFjNDA4N2IzNjkxMzc5ZGFmYTYxYTgzNzBlMjQ2NTk2ZTgyYWY0NDJiNWJkZDRiYWUyNTI2YzAyOGNhYzIwODhhZmU5MDI3Yzc1NjZhN2MsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azFPRGt4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
toolbar: true,
style: ['background-color:orange; font-weight: bold;'],
worksheets: [{
data: [
[ 'Crayons Crayola only (No Rose Art)', 2, 5.01, 0.01, '=B1*C1*(1-D1)' ],
[ 'Colored Pencils Crayola only', 2, 4.41, 0.02, '=B2*C2*(1-D2)' ],
[ 'Expo Dry-erase Markers Wide', 4, 3.00, 0.1, '=B3*C3*(1-D3)' ],
[ 'Index Cards Unlined', 3, 6.00, 0.03, '=B4*C4*(1-D4)' ],
[ 'Tissues', 10, 1.90, 0.01, '=B5*C5*(1-D5)' ],
[ 'Ziploc Sandwich-size Bags', 5, 1.00, 0.01, '=B6*C6*(1-D6)' ],
[ 'Thin Markers Crayola only', 2, 3.00, 0.02, '=B7*C7*(1-D7)' ],
[ 'Highlighter', 4, 1.20, 0.01, '=B8*C8*(1-D8)' ],
[ 'Total', '=SUM(B1:B8)', '=ROUND(SUM(C1:C8), 2)', '', '=SUM(E1:E8)' ],
],
columns: [
{ type: 'text', title: 'Product', width: '300' },
{ type: 'text', title: 'Qtd', width: '80', mask: '#.##0' },
{ type: 'text', title: 'Price', width: '100px', mask: '$ #.##0,00' },
{ type: 'text', title: 'Discount', mask: '0.00%' },
{
type: 'number',
title: 'Total',
width: '100px',
format: 'US #.##0,00;[Red](#.##0,00)',
},
],
style: {
'A9:E9': 0,
},
columnSorting: false,
worksheetName: 'Calculations',
}]
});
</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 license
jspreadsheet.setLicense('YTFjNjMwMGM2OWVlZTYzMzc5MTBjOTQ4MTNmOTg5NzhkODk1MDg4ZTI0ZjljYmFlYTFjNDA4N2IzNjkxMzc5ZGFmYTYxYTgzNzBlMjQ2NTk2ZTgyYWY0NDJiNWJkZDRiYWUyNTI2YzAyOGNhYzIwODhhZmU5MDI3Yzc1NjZhN2MsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azFPRGt4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default function App() {
// Spreadsheet array of worksheets
const spreadsheet = useRef();
// Global grid style
const styles = ['background-color:orange; font-weight: bold;'];
// Worksheet data
const data = [
['Crayons Crayola only (No Rose Art)', 2, 5.01, 0.01, '=B1*C1*(1-D1)'],
['Colored Pencils Crayola only', 2, 4.41, 0.02, '=B2*C2*(1-D2)'],
['Expo Dry-erase Markers Wide', 4, 3.00, 0.1, '=B3*C3*(1-D3)'],
['Index Cards Unlined', 3, 6.00, 0.03, '=B4*C4*(1-D4)'],
['Tissues', 10, 1.90, 0.01, '=B5*C5*(1-D5)'],
['Ziploc Sandwich-size Bags', 5, 1.00, 0.01, '=B6*C6*(1-D6)'],
['Thin Markers Crayola only', 2, 3.00, 0.02, '=B7*C7*(1-D7)'],
['Highlighter', 4, 1.20, 0.01, '=B8*C8*(1-D8)'],
['Total', '=SUM(B1:B8)', '=ROUND(SUM(C1:C8), 2)', '', '=SUM(E1:E8)'],
]
// Column definitions
const columns = [
{ type: 'text', title: 'Product', width: '300' },
{ type: 'text', title: 'Qtd', width: '80', mask: '#.##0' },
{ type: 'text', title: 'Price', width: '100px', mask: '$ #.##0,00' },
{ type: 'text', title: 'Discount', mask: '0.00%' },
{
type: 'number',
title: 'Total',
width: '100px',
format: 'US #.##0,00;[Red](#.##0,00)',
},
]
// Data grid style
const style = {
'A9:E9': 0,
}
// Render component
return (
<Spreadsheet ref={spreadsheet} style={styles} toolbar={true}>
<Worksheet data={data} columns={columns} style={style} worksheetName="Calculations" columnSorting={false} />
</Spreadsheet>
);
}
<template>
<Spreadsheet ref="spreadsheet" :styles="styles">
<Worksheet :data="data" :columns="columns" :style="style" columnSorting worksheetName="Calculations" />
</Spreadsheet>
</template>
<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import formula from "@jspreadsheet/formula-pro";
// Set license
jspreadsheet.setLicense('YTFjNjMwMGM2OWVlZTYzMzc5MTBjOTQ4MTNmOTg5NzhkODk1MDg4ZTI0ZjljYmFlYTFjNDA4N2IzNjkxMzc5ZGFmYTYxYTgzNzBlMjQ2NTk2ZTgyYWY0NDJiNWJkZDRiYWUyNTI2YzAyOGNhYzIwODhhZmU5MDI3Yzc1NjZhN2MsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azFPRGt4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default {
components: {
Spreadsheet,
Worksheet,
},
data() {
// Global grid style
const styles = ['background-color:orange; font-weight: bold;'];
// Worksheet data
const data = [
[ 'Crayons Crayola only (No Rose Art)', 2, 5.01, 0.01, '=B1*C1*(1-D1)' ],
[ 'Colored Pencils Crayola only', 2, 4.41, 0.02, '=B2*C2*(1-D2)' ],
[ 'Expo Dry-erase Markers Wide', 4, 3.00, 0.1, '=B3*C3*(1-D3)' ],
[ 'Index Cards Unlined', 3, 6.00, 0.03, '=B4*C4*(1-D4)' ],
[ 'Tissues', 10, 1.90, 0.01, '=B5*C5*(1-D5)' ],
[ 'Ziploc Sandwich-size Bags', 5, 1.00, 0.01, '=B6*C6*(1-D6)' ],
[ 'Thin Markers Crayola only', 2, 3.00, 0.02, '=B7*C7*(1-D7)' ],
[ 'Highlighter', 4, 1.20, 0.01, '=B8*C8*(1-D8)' ],
[ 'Total', '=SUM(B1:B8)', '=ROUND(SUM(C1:C8), 2)', '', '=SUM(E1:E8)' ],
]
// Column definitions
const columns = [
{ type: 'text', title: 'Product', width: '300' },
{ type: 'text', title: 'Qtd', width: '80', mask: '#.##0' },
{ type: 'text', title: 'Price', width: '100px', mask: '$ #.##0,00' },
{ type: 'text', title: 'Discount', mask: '0.00%' },
{
type: 'number',
title: 'Total',
width: '100px',
format: 'US #.##0,00;[Red](#.##0,00)',
},
]
// Data grid style
const style = {
'A9:E9': 0,
}
return {
styles,
data,
columns,
style,
};
}
}
</script>
import { Component, ViewChild, ElementRef, AfterViewInit } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import formula from "@jspreadsheet/formula-pro";
import "jspreadsheet/dist/jspreadsheet.css";
import "jsuites/dist/jsuites.css";
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('YTFjNjMwMGM2OWVlZTYzMzc5MTBjOTQ4MTNmOTg5NzhkODk1MDg4ZTI0ZjljYmFlYTFjNDA4N2IzNjkxMzc5ZGFmYTYxYTgzNzBlMjQ2NTk2ZTgyYWY0NDJiNWJkZDRiYWUyNTI2YzAyOGNhYzIwODhhZmU5MDI3Yzc1NjZhN2MsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azFPRGt4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
@Component({
standalone: true,
selector: "app-root",
template: `<div #spreadsheet></div>`
})
export class AppComponent implements AfterViewInit {
@ViewChild("spreadsheet") spreadsheet: ElementRef;
// Worksheets
worksheets: jspreadsheet.worksheetInstance[];
// Create a new data grid
ngAfterViewInit() {
// Create spreadsheet
this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
toolbar: true,
style: ['background-color:orange; font-weight: bold;'],
worksheets: [{
data: [
[ 'Crayons Crayola only (No Rose Art)', 2, 5.01, 0.01, '=B1*C1*(1-D1)' ],
[ 'Colored Pencils Crayola only', 2, 4.41, 0.02, '=B2*C2*(1-D2)' ],
[ 'Expo Dry-erase Markers Wide', 4, 3.00, 0.1, '=B3*C3*(1-D3)' ],
[ 'Index Cards Unlined', 3, 6.00, 0.03, '=B4*C4*(1-D4)' ],
[ 'Tissues', 10, 1.90, 0.01, '=B5*C5*(1-D5)' ],
[ 'Ziploc Sandwich-size Bags', 5, 1.00, 0.01, '=B6*C6*(1-D6)' ],
[ 'Thin Markers Crayola only', 2, 3.00, 0.02, '=B7*C7*(1-D7)' ],
[ 'Highlighter', 4, 1.20, 0.01, '=B8*C8*(1-D8)' ],
[ 'Total', '=SUM(B1:B8)', '=ROUND(SUM(C1:C8), 2)', '', '=SUM(E1:E8)' ],
],
columns: [
{ type: 'text', title: 'Product', width: 300 },
{ type: 'text', title: 'Qtd', width: 80, mask: '#.##0' },
{ type: 'text', title: 'Price', width: 100, mask: '$ #.##0,00' },
{ type: 'text', title: 'Discount', mask: '0.00%' },
{
type: 'number',
title: 'Total',
width: 100,
format: 'US #.##0,00;[Red](#.##0,00)',
},
],
style: {
'A9:E9': 0,
},
columnSorting: false,
worksheetName: 'Calculations',
}]
});
}
}
Cross-Calculations
This example demonstrates formula calculations that span different worksheets and spreadsheets.
<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v6/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v6/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.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('YTFjNjMwMGM2OWVlZTYzMzc5MTBjOTQ4MTNmOTg5NzhkODk1MDg4ZTI0ZjljYmFlYTFjNDA4N2IzNjkxMzc5ZGFmYTYxYTgzNzBlMjQ2NTk2ZTgyYWY0NDJiNWJkZDRiYWUyNTI2YzAyOGNhYzIwODhhZmU5MDI3Yzc1NjZhN2MsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azFPRGt4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [
{
data: [
['Cheese', 10, 6.00, "=B1*C1"],
['Apples', 5, 4.00, "=B2*C2"],
['Carrots', 5, 1.00, "=B3*C3"],
['Oranges', 6, 2.00, "=B4*C4"],
['Reference from the spreadsheet above: ', '=Calculations!C9', '', '=Calculations!E9'],
],
worksheetName: 'Example1',
minDimensions: [5, 5],
defaultColWidth: '50px',
columns: [{
width: '300px'
}],
cells: {
B5: { mask: '#.##0' },
D5: { mask: '#.##0' },
}
},
{
data: [
['20%', "=Example1!D1"],
['20%', "=Example1!D2"],
['20%', "=Example1!D3"],
['20%', "=Example1!D4"],
],
worksheetName: 'Example2',
minDimensions: [5, 5],
},
{
data: [
[ 'Crayons Crayola only (No Rose Art)', 2, 5.01, 0.01, '=B1*C1*(1-D1)' ],
[ 'Colored Pencils Crayola only', 2, 4.41, 0.02, '=B2*C2*(1-D2)' ],
[ 'Expo Dry-erase Markers Wide', 4, 3.00, 0.1, '=B3*C3*(1-D3)' ],
[ 'Index Cards Unlined', 3, 6.00, 0.03, '=B4*C4*(1-D4)' ],
[ 'Tissues', 10, 1.90, 0.01, '=B5*C5*(1-D5)' ],
[ 'Ziploc Sandwich-size Bags', 5, 1.00, 0.01, '=B6*C6*(1-D6)' ],
[ 'Thin Markers Crayola only', 2, 3.00, 0.02, '=B7*C7*(1-D7)' ],
[ 'Highlighter', 4, 1.20, 0.01, '=B8*C8*(1-D8)' ],
[ 'Total', '=SUM(B1:B8)', '=ROUND(SUM(C1:C8), 2)', '', '=SUM(E1:E8)' ],
],
columns: [
{ type: 'text', title: 'Product', width: 300 },
{ type: 'text', title: 'Qtd', width: 80, mask: '#.##0' },
{ type: 'text', title: 'Price', width: 100, mask: '$ #.##0,00' },
{ type: 'text', title: 'Discount', mask: '0.00%' },
{
type: 'number',
title: 'Total',
width: 100,
format: 'US #.##0,00;[Red](#.##0,00)',
},
],
style: {
'A9:E9': 0,
},
columnSorting: false,
worksheetName: 'Calculations',
}
]
});
</script>
</html>
import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import formula from "@jspreadsheet/formula-pro";
// Set license
jspreadsheet.setLicense('YTFjNjMwMGM2OWVlZTYzMzc5MTBjOTQ4MTNmOTg5NzhkODk1MDg4ZTI0ZjljYmFlYTFjNDA4N2IzNjkxMzc5ZGFmYTYxYTgzNzBlMjQ2NTk2ZTgyYWY0NDJiNWJkZDRiYWUyNTI2YzAyOGNhYzIwODhhZmU5MDI3Yzc1NjZhN2MsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azFPRGt4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default function App() {
// Spreadsheet array of worksheets
const spreadsheet = useRef();
// All worksheets in a single variable
const worksheets = [
{
data: [
['Cheese', 10, 6.00, "=B1*C1"],
['Apples', 5, 4.00, "=B2*C2"],
['Carrots', 5, 1.00, "=B3*C3"],
['Oranges', 6, 2.00, "=B4*C4"],
['Reference from the spreadsheet above: ', '=Calculations!C9', '', '=Calculations!E9'],
],
worksheetName: 'Example1',
minDimensions: [5, 5],
defaultColWidth: '50px',
columns: [{
width: '300px'
}],
cells: {
B5: { mask: '#.##0' },
D5: { mask: '#.##0' },
}
},
{
data: [
['20%', "=Example1!D1"],
['20%', "=Example1!D2"],
['20%', "=Example1!D3"],
['20%', "=Example1!D4"],
],
worksheetName: 'Example2',
minDimensions: [5, 5],
},
{
data: [
[ 'Crayons Crayola only (No Rose Art)', 2, 5.01, 0.01, '=B1*C1*(1-D1)' ],
[ 'Colored Pencils Crayola only', 2, 4.41, 0.02, '=B2*C2*(1-D2)' ],
[ 'Expo Dry-erase Markers Wide', 4, 3.00, 0.1, '=B3*C3*(1-D3)' ],
[ 'Index Cards Unlined', 3, 6.00, 0.03, '=B4*C4*(1-D4)' ],
[ 'Tissues', 10, 1.90, 0.01, '=B5*C5*(1-D5)' ],
[ 'Ziploc Sandwich-size Bags', 5, 1.00, 0.01, '=B6*C6*(1-D6)' ],
[ 'Thin Markers Crayola only', 2, 3.00, 0.02, '=B7*C7*(1-D7)' ],
[ 'Highlighter', 4, 1.20, 0.01, '=B8*C8*(1-D8)' ],
[ 'Total', '=SUM(B1:B8)', '=ROUND(SUM(C1:C8), 2)', '', '=SUM(E1:E8)' ],
],
columns: [
{ type: 'text', title: 'Product', width: 300 },
{ type: 'text', title: 'Qtd', width: 80, mask: '#.##0' },
{ type: 'text', title: 'Price', width: 100, mask: '$ #.##0,00' },
{ type: 'text', title: 'Discount', mask: '0.00%' },
{
type: 'number',
title: 'Total',
width: 100,
format: 'US #.##0,00;[Red](#.##0,00)',
},
],
style: {
'A9:E9': 0,
},
columnSorting: false,
worksheetName: 'Calculations',
}
]
// Render component
return (
<Spreadsheet ref={spreadsheet} worksheets={worksheets} />
);
}
<template>
<Spreadsheet ref="spreadsheet" :worksheets="worksheets" />
</template>
<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import formula from "@jspreadsheet/formula-pro";
// Set license
jspreadsheet.setLicense('YTFjNjMwMGM2OWVlZTYzMzc5MTBjOTQ4MTNmOTg5NzhkODk1MDg4ZTI0ZjljYmFlYTFjNDA4N2IzNjkxMzc5ZGFmYTYxYTgzNzBlMjQ2NTk2ZTgyYWY0NDJiNWJkZDRiYWUyNTI2YzAyOGNhYzIwODhhZmU5MDI3Yzc1NjZhN2MsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azFPRGt4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default {
components: {
Spreadsheet,
Worksheet,
},
data() {
// All worksheets in a single variable
const worksheets = [
{
data: [
['Cheese', 10, 6.00, "=B1*C1"],
['Apples', 5, 4.00, "=B2*C2"],
['Carrots', 5, 1.00, "=B3*C3"],
['Oranges', 6, 2.00, "=B4*C4"],
['Reference from the spreadsheet above: ', '=Calculations!C9', '', '=Calculations!E9'],
],
worksheetName: 'Example1',
minDimensions: [5, 5],
defaultColWidth: '50px',
columns: [{
width: '300px'
}],
cells: {
B5: { mask: '#.##0' },
D5: { mask: '#.##0' },
}
},
{
data: [
['20%', "=Example1!D1"],
['20%', "=Example1!D2"],
['20%', "=Example1!D3"],
['20%', "=Example1!D4"],
],
worksheetName: 'Example2',
minDimensions: [5, 5],
},
{
data: [
[ 'Crayons Crayola only (No Rose Art)', 2, 5.01, 0.01, '=B1*C1*(1-D1)' ],
[ 'Colored Pencils Crayola only', 2, 4.41, 0.02, '=B2*C2*(1-D2)' ],
[ 'Expo Dry-erase Markers Wide', 4, 3.00, 0.1, '=B3*C3*(1-D3)' ],
[ 'Index Cards Unlined', 3, 6.00, 0.03, '=B4*C4*(1-D4)' ],
[ 'Tissues', 10, 1.90, 0.01, '=B5*C5*(1-D5)' ],
[ 'Ziploc Sandwich-size Bags', 5, 1.00, 0.01, '=B6*C6*(1-D6)' ],
[ 'Thin Markers Crayola only', 2, 3.00, 0.02, '=B7*C7*(1-D7)' ],
[ 'Highlighter', 4, 1.20, 0.01, '=B8*C8*(1-D8)' ],
[ 'Total', '=SUM(B1:B8)', '=ROUND(SUM(C1:C8), 2)', '', '=SUM(E1:E8)' ],
],
columns: [
{ type: 'text', title: 'Product', width: 300 },
{ type: 'text', title: 'Qtd', width: 80, mask: '#.##0' },
{ type: 'text', title: 'Price', width: 100, mask: '$ #.##0,00' },
{ type: 'text', title: 'Discount', mask: '0.00%' },
{
type: 'number',
title: 'Total',
width: 100,
format: 'US #.##0,00;[Red](#.##0,00)',
},
],
style: {
'A9:E9': 0,
},
columnSorting: false,
worksheetName: 'Calculations',
}
]
return {
worksheets,
};
}
}
</script>
import { Component, ViewChild, ElementRef, AfterViewInit } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import formula from "@jspreadsheet/formula-pro";
import "jspreadsheet/dist/jspreadsheet.css";
import "jsuites/dist/jsuites.css";
// Set license
jspreadsheet.setLicense('YTFjNjMwMGM2OWVlZTYzMzc5MTBjOTQ4MTNmOTg5NzhkODk1MDg4ZTI0ZjljYmFlYTFjNDA4N2IzNjkxMzc5ZGFmYTYxYTgzNzBlMjQ2NTk2ZTgyYWY0NDJiNWJkZDRiYWUyNTI2YzAyOGNhYzIwODhhZmU5MDI3Yzc1NjZhN2MsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azFPRGt4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
@Component({
standalone: true,
selector: "app-root",
template: `<div #spreadsheet></div>`
})
export class AppComponent implements AfterViewInit {
@ViewChild("spreadsheet") spreadsheet: ElementRef;
// Worksheets
worksheets: jspreadsheet.worksheetInstance[];
// Create a new data grid
ngAfterViewInit() {
// Create spreadsheet
this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
worksheets: [
{
data: [
['Cheese', 10, 6.00, "=B1*C1"],
['Apples', 5, 4.00, "=B2*C2"],
['Carrots', 5, 1.00, "=B3*C3"],
['Oranges', 6, 2.00, "=B4*C4"],
['Reference from the spreadsheet above: ', '=Calculations!C9', '', '=Calculations!E9'],
],
worksheetName: 'Example1',
minDimensions: [5, 5],
defaultColWidth: '50px',
columns: [{
width: 300
}],
cells: {
B5: { mask: '#.##0' },
D5: { mask: '#.##0' },
}
},
{
data: [
['20%', "=Example1!D1"],
['20%', "=Example1!D2"],
['20%', "=Example1!D3"],
['20%', "=Example1!D4"],
],
worksheetName: 'Example2',
minDimensions: [5, 5],
},
{
data: [
[ 'Crayons Crayola only (No Rose Art)', 2, 5.01, 0.01, '=B1*C1*(1-D1)' ],
[ 'Colored Pencils Crayola only', 2, 4.41, 0.02, '=B2*C2*(1-D2)' ],
[ 'Expo Dry-erase Markers Wide', 4, 3.00, 0.1, '=B3*C3*(1-D3)' ],
[ 'Index Cards Unlined', 3, 6.00, 0.03, '=B4*C4*(1-D4)' ],
[ 'Tissues', 10, 1.90, 0.01, '=B5*C5*(1-D5)' ],
[ 'Ziploc Sandwich-size Bags', 5, 1.00, 0.01, '=B6*C6*(1-D6)' ],
[ 'Thin Markers Crayola only', 2, 3.00, 0.02, '=B7*C7*(1-D7)' ],
[ 'Highlighter', 4, 1.20, 0.01, '=B8*C8*(1-D8)' ],
[ 'Total', '=SUM(B1:B8)', '=ROUND(SUM(C1:C8), 2)', '', '=SUM(E1:E8)' ],
],
columns: [
{ type: 'text', title: 'Product', width: 300 },
{ type: 'text', title: 'Qtd', width: 80, mask: '#.##0' },
{ type: 'text', title: 'Price', width: 100, mask: '$ #.##0,00' },
{ type: 'text', title: 'Discount', mask: '0.00%' },
{
type: 'number',
title: 'Total',
width: 100,
format: 'US #.##0,00;[Red](#.##0,00)',
},
],
style: {
'A9:E9': 0,
},
columnSorting: false,
worksheetName: 'Calculations',
}
]
});
}
}
Defined Names
This feature is only available with the Formula Pro extension.
<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v6/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v6/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.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('YTFjNjMwMGM2OWVlZTYzMzc5MTBjOTQ4MTNmOTg5NzhkODk1MDg4ZTI0ZjljYmFlYTFjNDA4N2IzNjkxMzc5ZGFmYTYxYTgzNzBlMjQ2NTk2ZTgyYWY0NDJiNWJkZDRiYWUyNTI2YzAyOGNhYzIwODhhZmU5MDI3Yzc1NjZhN2MsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azFPRGt4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [
{
data: [
[ 1, '=SUM(Test*10)' ],
[ 2, '' ],
[ 3, '' ],
[ 4, '' ],
],
minDimensions: [5, 5],
worksheetName: 'Named ranges',
}
],
// In Jspreadsheet, defined names must be uppercase
definedNames: {
'TEST': 'A1:A4',
},
});
</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 license
jspreadsheet.setLicense('YTFjNjMwMGM2OWVlZTYzMzc5MTBjOTQ4MTNmOTg5NzhkODk1MDg4ZTI0ZjljYmFlYTFjNDA4N2IzNjkxMzc5ZGFmYTYxYTgzNzBlMjQ2NTk2ZTgyYWY0NDJiNWJkZDRiYWUyNTI2YzAyOGNhYzIwODhhZmU5MDI3Yzc1NjZhN2MsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azFPRGt4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default function App() {
// Spreadsheet array of worksheets
const spreadsheet = useRef();
// All worksheets in a single variable
const worksheets = [
{
data: [
[ 1, '=SUM(Test*10)' ],
[ 2, '' ],
[ 3, '' ],
[ 4, '' ],
],
minDimensions: [5, 5],
worksheetName: 'Named ranges',
}
];
// In Jspreadsheet, defined names must be uppercase
const definedNames = {
TEST: 'A1:A4',
};
// Render component
return (
<Spreadsheet ref={spreadsheet} worksheets={worksheets} definedNames={definedNames} />
);
}
<template>
<Spreadsheet ref="spreadsheet" :worksheets="worksheets" :definedNames="definedNames" />
</template>
<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
// Set license
jspreadsheet.setLicense('YTFjNjMwMGM2OWVlZTYzMzc5MTBjOTQ4MTNmOTg5NzhkODk1MDg4ZTI0ZjljYmFlYTFjNDA4N2IzNjkxMzc5ZGFmYTYxYTgzNzBlMjQ2NTk2ZTgyYWY0NDJiNWJkZDRiYWUyNTI2YzAyOGNhYzIwODhhZmU5MDI3Yzc1NjZhN2MsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azFPRGt4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default {
components: {
Spreadsheet,
Worksheet,
},
data() {
// All worksheets in a single variable
const worksheets = [
{
data: [
[ 1, '=SUM(Test*10)' ],
[ 2, '' ],
[ 3, '' ],
[ 4, '' ],
],
minDimensions: [5, 5],
worksheetName: 'Named ranges',
}
];
// In Jspreadsheet, defined names must be uppercase
const definedNames = {
TEST: 'A1:A4',
};
return {
worksheets,
definedNames,
};
}
}
</script>
import { Component, ViewChild, ElementRef, AfterViewInit } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import formula from "@jspreadsheet/formula-pro";
import "jspreadsheet/dist/jspreadsheet.css";
import "jsuites/dist/jsuites.css";
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('YTFjNjMwMGM2OWVlZTYzMzc5MTBjOTQ4MTNmOTg5NzhkODk1MDg4ZTI0ZjljYmFlYTFjNDA4N2IzNjkxMzc5ZGFmYTYxYTgzNzBlMjQ2NTk2ZTgyYWY0NDJiNWJkZDRiYWUyNTI2YzAyOGNhYzIwODhhZmU5MDI3Yzc1NjZhN2MsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azFPRGt4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
@Component({
standalone: true,
selector: "app-root",
template: `<div #spreadsheet></div>`
})
export class AppComponent implements AfterViewInit {
@ViewChild("spreadsheet") spreadsheet: ElementRef;
// Worksheets
worksheets: jspreadsheet.worksheetInstance[];
// Create a new data grid
ngAfterViewInit() {
// Create spreadsheet
this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
worksheets: [
{
data: [
[ 1, '=SUM(Test*10)' ],
[ 2, '' ],
[ 3, '' ],
[ 4, '' ],
],
minDimensions: [5, 5],
worksheetName: 'Named ranges',
}
],
// In Jspreadsheet, defined names must be uppercase
definedNames: {
'TEST': 'A1:A4',
}
});
}
}
Changes from Version 11
- Deprecated functions: TABLE, VAL, NULL
- Formula Pro V6: The new formula pro has a different signature.
More Information
The documentation provides additional information about Jspreadsheet calculations. To explore specific features and functionalities, please select one of the options below.