Spreadsheet Defined Names
Introduction
In spreadsheet applications, the defined names serves as a method to assign more readable labels to cell references or ranges, enhancing formula readability and maintainability. This concept, common in Excel, is also available in Jspreadsheet, allowing for an Excel-like experience within a JavaScript data grid.
Key Concepts
- Defined Names: Labels that represent references to single cells, cell ranges, groups of cells, or formulas.
- Named Ranges: A specific defined name that refers to a cell range, facilitating easier reference in formulas.
Important Notes:
- Enterprise feature: This feature is only available with the Premium Distribution with the Formulas Pro extension enabled.
- Capital Case: All defined names should be defined with capital letters.
- Scope: All defined names are going to be created on the spreadsheet level scope.
Documentation
Methods
You can use the following methods to read or create new defined names programmatically in your spreadsheet software.
Method | Description |
---|---|
getDefinedNames | Get a defined name. @param {string} index - defined name identification. spreadsheet.getDefinedNames(index: String) => Object |
setDefinedNames | Create a one or multiple defined names. @param {array} - Array with the new defined names. spreadsheet.setDefinedNames(names: Object[]) => void |
Settings
All available properties to define a validation
Property | Description |
---|---|
definedNames: array | An array of defined names |
Example
Basic example with defined names
Here's a simple example of how to use defined names in your calculations within a spreadsheet.
<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />
<script src="https://jsuites.net/v5/jsuites.js"></script>
<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>
<p><input type="button" id="btn1" value="Create and Execute" /></p>
<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('MWQ3NmFiMTljYThmOGUxMDYxMzQ3ZDU3N2Q1NmVlNzliNjMxZmVmMmMyN2NjZjI0ZWRkOGUwNTdhZDRkNWQ1YWQ1M2FiNGEzMzIwYTNhZDZlMDU4NzY3ZGY4NDFlY2QxYmE0Y2ZkNDE4MGQzZjgwZmMyMzk3OGM4NTNlODhiMzYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpFek5URTFNVFUzTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
// Set the extension
jspreadsheet.setExtensions({ formula });
// Create a new spreadsheet
let worksheets = jspreadsheet(document.getElementById('spreadsheet'), {
toolbar: true,
worksheets: [{
data: [
[10, "=SUM(Summary)"],
[20, "1"],
[30, "2"],
[40, "3"],
[50, "4"]
],
minDimensions: [6, 6],
}],
definedNames: {
Summary: 'Sheet1!A1:A6',
}
});
document.getElementById('btn1').addEventListener('click', () => {
// Create the defined name. All defined names are going to be created on the spreadsheet level.
worksheets[0].setDefinedNames([
{ index: 'TOTAL', value: 'Sheet1!B1:B6' }
]);
// Update the value of C1 to process the total
worksheets[0].setValue('C1', '=SUM(TOTAL)');
})
</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)
jspreadsheet.setLicense('MWQ3NmFiMTljYThmOGUxMDYxMzQ3ZDU3N2Q1NmVlNzliNjMxZmVmMmMyN2NjZjI0ZWRkOGUwNTdhZDRkNWQ1YWQ1M2FiNGEzMzIwYTNhZDZlMDU4NzY3ZGY4NDFlY2QxYmE0Y2ZkNDE4MGQzZjgwZmMyMzk3OGM4NTNlODhiMzYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpFek5URTFNVFUzTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default function App() {
// Spreadsheet array of worksheets
const spreadsheet = useRef();
// Data
const data = [
[10, "=SUM(Summary)"],
[20, "1"],
[30, "2"],
[40, "3"],
[50, "4"]
];
// Defined names
const definedNames = {
Summary: 'Sheet1!A1:A6',
}
const handleCreate = function() {
// Create the defined name. All defined names are going to be created on the spreadsheet level.
spreadsheet.current[0].setDefinedNames([
{ index: 'TOTAL', value: 'Sheet1!B1:B6' }
]);
// Update the value of C1 to process the total
spreadsheet.current[0].setValue('C1', '=SUM(TOTAL)');
}
// Render component
return (
<>
<Spreadsheet ref={spreadsheet} definedNames={definedNames}>
<Worksheet data={data} minDimensions={[6,6]} />
</Spreadsheet>
<p><input type={"button"} value="Create and Execute" onClick={handleCreate} /></p>
</>
);
}
<template>
<Spreadsheet ref="spreadsheet" :definedNames="definedNames">
<Worksheet :data="data" />
</Spreadsheet>
<p><input type="button" value="Create and Execute" @click="handleCreate" /></p>
</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 your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('MWQ3NmFiMTljYThmOGUxMDYxMzQ3ZDU3N2Q1NmVlNzliNjMxZmVmMmMyN2NjZjI0ZWRkOGUwNTdhZDRkNWQ1YWQ1M2FiNGEzMzIwYTNhZDZlMDU4NzY3ZGY4NDFlY2QxYmE0Y2ZkNDE4MGQzZjgwZmMyMzk3OGM4NTNlODhiMzYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpFek5URTFNVFUzTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default {
components: {
Spreadsheet,
Worksheet,
},
data() {
// Data
const data = [
[10, "=SUM(Summary)"],
[20, "1"],
[30, "2"],
[40, "3"],
[50, "4"]
];
// Defined names
const definedNames = {
Summary: 'Sheet1!A1:A6',
}
return {
data,
definedNames
};
},
methods: {
handleCreate: function() {
// Create the defined name. All defined names are going to be created on the spreadsheet level.
this.$refs.spreadsheet.current[0].setDefinedNames([
{ index: 'TOTAL', value: 'Sheet1!B1:B6' }
]);
// Update the value of C1 to process the total
this.$refs.spreadsheet.current[0].setValue('C1', '=SUM(TOTAL)');
}
}
}
</script>
import { Component, ViewChild, ElementRef } from "@angular/core";
import * as jspreadsheet from "jspreadsheet";
import formula from "@jspreadsheet/formula-pro";
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('MWQ3NmFiMTljYThmOGUxMDYxMzQ3ZDU3N2Q1NmVlNzliNjMxZmVmMmMyN2NjZjI0ZWRkOGUwNTdhZDRkNWQ1YWQ1M2FiNGEzMzIwYTNhZDZlMDU4NzY3ZGY4NDFlY2QxYmE0Y2ZkNDE4MGQzZjgwZmMyMzk3OGM4NTNlODhiMzYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpFek5URTFNVFUzTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
// Set the extensions
jspreadsheet.setExtensions({ formula });
// Create component
@Component({
selector: "app-root",
template: `
<div #spreadsheet></div>
<button (click)="handleCreate()">Create and Execute</button>
`;
})
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, {
toolbar: true,
worksheets: [{
data: [
[10, "=SUM(Summary)"],
[20, "1"],
[30, "2"],
[40, "3"],
[50, "4"]
],
minDimensions: [6, 6],
}],
definedNames: {
Summary: 'Sheet1!A1:A6',
}
});
}
onButtonClick() {
// Create the defined name. All defined names are going to be created on the spreadsheet level.
this.worksheets[0].setDefinedNames([
{ index: 'TOTAL', value: 'Sheet1!B1:B6' }
]);
// Update the value of C1 to process the total
this.worksheets[0].setValue('C1', '=SUM(TOTAL)');
}
}