Spreadsheet format
Jspreadsheet providesmask, format, and locale
properties, which help format column and cell data. These properties enable Jspreadsheet to be more compatible with other popular spreadsheets software like Excel and Google Sheets.Documentation
Locale property
Jspreadsheet Pro has a built-in integration of theIntl.NumberFormat
to expand the number formatting capabilities. To activate this feature, please specify the locale property within the column or cells, as shown in the example below. Please refer to the the Mozilla website for additional details about the available options.
Mask and format properties
The initial part of this chapter explains how to use the mask and format properties. Themask
property restricts the user from entering only specific input, defined by spreadsheet-like tokens, while the format
property is applied after the user finishes editing a cell. These tokens are compatible with other spreadsheet software, and you can use them as demonstrated below./** A few valid tokens can be used with mask as below: 0 0.00 0% 0.00% #,##0 #,##0.00 #,##0;(#,##0) #,##0;[Red](#,##0) #,##0.00;(#,##0.00) #,##0.00;[Red](#,##0.00) d-mmm-yy d-mmm dd/mm/yyyy mmm-yy h:mm AM/PM h:mm:ss AM/PM h:mm h:mm:ss m/d/yy h:mm mm:ss [h]:mm:ss */
jspreadsheet(document.getElementById('spreadsheet'), { worksheets: [{ minDimensions: [10,10], columns: [{ type: 'number', // Excel like token to format the currency input mask: 'U$ #.##0,00' }] }] });
export default function App() { // Spreadsheet array of worksheets const spreadsheet = useRef(); // Data const columns = [{ type: 'number', // Excel like token to format the currency input mask: 'U$ #.##0,00' }]; // Render data grid component return ( <Spreadsheet ref={spreadsheet}> <Worksheet columns={columns} minDimensions={[10,10]} /> </Spreadsheet> ); }
Vue example
<template> <Spreadsheet ref="spreadsheet"> <Worksheet :columns="columns" /> </Spreadsheet> </template> <script> export default { components: { Spreadsheet, Worksheet, }, data() { // Data const columns = [{ type: 'number', // Excel like token to format the currency input mask: 'U$ #.##0,00' }]; return { columns }; } } </script>
Angular example
@Component({ selector: "app-root", template: `<div #spreadsheet></div>` }) export class AppComponent { // Create a new data grid ngOnInit() { // Create spreadsheet this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, { worksheets: [{ minDimensions: [10,10], columns: [{ type: 'number', // Excel like token to format the currency input mask: 'U$ #.##0,00' }] }] }); } }
Locale
Currency formatting
You must provide a currency property when using the 'currency' style in Jspreadsheet. Additionally, you can use the optional currencyDisplay and currencySign properties to control how the currency unit is displayed.// India currency jspreadsheet(document.getElementById('spreadsheet'), { worksheets: [{ minDimensions: [10,10], columns: [{ type: 'number', // Locale will enable number formatting locale: 'en-IN', // Options for the number format class. You can find more about he options on the link above options: { style:'currency', currency: 'INR' } }] }] }); // Accounting notation jspreadsheet(document.getElementById('spreadsheet'), { worksheets: [{ minDimensions: [10,10], columns: [{ type: 'number', locale: 'bn', options: { style:'currency', currency: 'USD', currencySign: 'accounting' } }] }] }); // Currency with decimals jspreadsheet(document.getElementById('spreadsheet'), { worksheets: [{ minDimensions: [10,10], columns: [{ type: 'number', locale: 'bn', options: { style: 'currency', currency: 'EUR', maximumFractionDigits: 4, minimumFractionDigits: 1 } }] }] });
// India currency export default function App() { // Spreadsheet array of worksheets const spreadsheet = useRef(); // Columns const columns = [{ type: 'number', // Locale will enable number formatting locale: 'en-IN', // Options for the number format class. You can find more about he options on the link above options: { style:'currency', currency: 'INR' } }]; // Render data grid component return ( <Spreadsheet ref={spreadsheet}> <Worksheet columns={columns} minDimensions={[10,10]} /> </Spreadsheet> ); } // Accounting notation export default function App() { // Spreadsheet array of worksheets const spreadsheet = useRef(); // Columns const columns = [{ type: 'number', locale: 'bn', options: { style:'currency', currency: 'USD', currencySign: 'accounting' } }]; // Render data grid component return ( <Spreadsheet ref={spreadsheet}> <Worksheet columns={columns} minDimensions={[10,10]} /> </Spreadsheet> ); } // Currency with decimals export default function App() { // Spreadsheet array of worksheets const spreadsheet = useRef(); // Columns const columns = [{ type: 'number', locale: 'bn', options: { style: 'currency', currency: 'EUR', maximumFractionDigits: 4, minimumFractionDigits: 1 } }]; // Render data grid component return ( <Spreadsheet ref={spreadsheet}> <Worksheet columns={columns} minDimensions={[10,10]} /> </Spreadsheet> ); }
Vue example
export default { components: { Spreadsheet, Worksheet, }, data() { // Columns const columns = [{ type: 'number', locale: 'bn', options: { style:'currency', currency: 'USD', currencySign: 'accounting' } }]; return { columns }; } }; export default { components: { Spreadsheet, Worksheet, }, data() { // Columns const columns = [{ type: 'number', locale: 'bn', options: { style:'currency', currency: 'USD', currencySign: 'accounting' } }]; return { columns }; } }; export default { components: { Spreadsheet, Worksheet, }, data() { // Columns const columns = [{ type: 'number', locale: 'bn', options: { style: 'currency', currency: 'EUR', maximumFractionDigits: 4, minimumFractionDigits: 1 } }]; return { columns }; } };
Angular example
export class AppComponent { // Create a new data grid ngOnInit() { // Create spreadsheet this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, { worksheets: [{ minDimensions: [10,10], columns: [{ type: 'number', // Locale will enable number formatting locale: 'en-IN', // Options for the number format class. You can find more about he options on the link above options: { style:'currency', currency: 'INR' } }] }] }); } } export class AppComponent { // Create a new data grid ngOnInit() { // Create spreadsheet this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, { worksheets: [{ minDimensions: [10,10], columns: [{ type: 'number', locale: 'bn', options: { style:'currency', currency: 'USD', currencySign: 'accounting' } }] }] }); } } export class AppComponent { // Create a new data grid ngOnInit() { // Create spreadsheet this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, { worksheets: [{ minDimensions: [10,10], columns: [{ type: 'number', locale: 'bn', options: { style: 'currency', currency: 'EUR', maximumFractionDigits: 4, minimumFractionDigits: 1 } }] }] }); } }
Unit formatting
If the style is 'unit,' a unit property must be provided. Optionally, unitDisplay controls the unit formatting.// → '3,500 liters' jspreadsheet(document.getElementById('spreadsheet'), { worksheets: [{ minDimensions: [10,10], columns: [{ type: 'number', locale: 'en-US', options: { style: 'unit', unit: 'liter', unitDisplay: 'long' } }] }] });
export default function App() { // Spreadsheet array of worksheets const spreadsheet = useRef(); // Columns const columns = [{ type: 'number', locale: 'en-US', options: { style: 'unit', unit: 'liter', unitDisplay: 'long' } }] }]; // Render data grid component return ( <Spreadsheet ref={spreadsheet}> <Worksheet columns={columns} minDimensions={[10,10]} /> </Spreadsheet> ); }
Vue example
export default { components: { Spreadsheet, Worksheet, }, data() { // Reference for the array of worksheet instances const spreadsheet = ref(null); // Columns const columns = [{ type: 'number', locale: 'en-US', options: { style: 'unit', unit: 'liter', unitDisplay: 'long' } }] }]; return { spreadsheet, columns }; } }
Angular example
@Component({ selector: "app-root", template: `<div #spreadsheet></div>` }) export class AppComponent { // Create a new data grid ngOnInit() { // Create spreadsheet this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, { worksheets: [{ minDimensions: [10,10], columns: [{ type: 'number', locale: 'en-US', options: { style: 'unit', unit: 'liter', unitDisplay: 'long' } }] }] }); } }
Scientific, engineering or compact notations
Scientific and compact notation are represented by the notation option and can be formatted like this:// Example: 9.9亿 jspreadsheet(document.getElementById('spreadsheet'), { worksheets: [{ minDimensions: [10,10], columns: [{ type: 'number', locale: 'zh-CN', options: { notation: "compact" } }] }] });
export default function App() { // Spreadsheet array of worksheets const spreadsheet = useRef(); // Columns const columns = [{ type: 'number', locale: 'zh-CN', options: { notation: "compact" } }] }]; // Render data grid component return ( <Spreadsheet ref={spreadsheet}> <Worksheet columns={columns} minDimensions={[10,10]} /> </Spreadsheet> ); }
Vue example
export default { components: { Spreadsheet, Worksheet, }, data() { // Reference for the array of worksheet instances const spreadsheet = ref(null); // Columns const columns = [{ type: 'number', locale: 'zh-CN', options: { notation: "compact" } }] }]; return { spreadsheet, columns }; } }
Angular example
@Component({ selector: "app-root", template: `<div #spreadsheet></div>` }) export class AppComponent { // Create a new data grid ngOnInit() { // Create spreadsheet this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, { worksheets: [{ minDimensions: [10,10], columns: [{ type: 'number', locale: 'zh-CN', options: { notation: "compact" } }] }] }); } }
Percentage
Percentage can be used as shown below:jspreadsheet(document.getElementById('spreadsheet'), { worksheets: [{ minDimensions: [10,10], columns: [{ { type: "number", locale: 'en-US', options: { style: 'percent' }}, }] }] });
export default function App() { // Spreadsheet array of worksheets const spreadsheet = useRef(); // Columns const columns = [{ { type: "number", locale: 'en-US', options: { style: 'percent' }}, }]; // Render data grid component return ( <Spreadsheet ref={spreadsheet}> <Worksheet columns={columns} minDimensions={[10,10]} /> </Spreadsheet> ); }
Vue example
export default { components: { Spreadsheet, Worksheet, }, data() { // Reference for the array of worksheet instances const spreadsheet = ref(null); // Columns const columns = [{ { type: "number", locale: 'en-US', options: { style: 'percent' }}, }]; return { spreadsheet, columns }; } }
Angular example
@Component({ selector: "app-root", template: `<div #spreadsheet></div>` }) export class AppComponent { // Create a new data grid ngOnInit() { // Create spreadsheet this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, { worksheets: [{ minDimensions: [10,10], columns: [{ { type: "number", locale: 'en-US', options: { style: 'percent' }}, }] }] }); } }
Examples
Data grid with different currencies
The example below implements number formatting using Intl.NumberFormat or mask.See more examples of the spreadsheet format on jsfiddle
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://jsuites.net/v5/jsuites.css" type="text/css" /> <link rel="stylesheet" href="https://jspreadsheet.com/v10/jspreadsheet.css" type="text/css" /> <link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" /> <div id="spreadsheet"></div> <input type='button' value='Change to AVERAGE' onclick='update()'> <script> // Set your JSS license key (The following key only works for one day) jspreadsheet.setLicense('MTcyMTFlOWJjYzA1ZTNjMzE5ZWUzNWNmY2UwNDY0NGIyZTZmMjM3NTM4YTg2MzA4N2ZhYzU3ZmMzNWFkMDhiMTE3MzJiYjVlOGRlNDUzNDhmMTEyYjY0ZmY4MTM4MDVlMmExODdmZWM1YThlMDczZGMwNDMyMGRlYmY0NWVhNmMsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGczTnpnNU5Td2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09'); // Create a new spreadsheet jspreadsheet(document.getElementById('spreadsheet'), { toolbar: true, worksheets: [ { minDimensions:[6, 10], data: [ [1024,1024,0.24,1024,1024,1024], [1000.456,1000.456,0.4155,1000.456,1000.456,1000.456], ['547','547,98','7,98','547.98','547,98','547.98'], ], columns: [ { title:"Currency INR", type: "number", locale: 'en-IN', options: { style:'currency', currency: 'INR' } }, { title: "Currency BRL", type: "number", locale: 'pt-BR', options: { style: 'currency', currency: 'BRL' } }, { title: "Percent US", type: "number", mask: "0.00%" }, { title: "Units Liter US", type: "number", locale: 'en-US', options: { style: 'unit', unit: 'liter', unitDisplay: 'long' } }, { type: "number", format: '#.##0,00' }, { type: "number", mask: '#,##0' }, ], defaultColWidth: '120px', } ] }); </script> </html>
React example
import React, { useRef } from "react"; import { Spreadsheet, Worksheet } from "@jspreadsheet/react"; import jspreadsheet from "jspreadsheet"; const license = 'MTcyMTFlOWJjYzA1ZTNjMzE5ZWUzNWNmY2UwNDY0NGIyZTZmMjM3NTM4YTg2MzA4N2ZhYzU3ZmMzNWFkMDhiMTE3MzJiYjVlOGRlNDUzNDhmMTEyYjY0ZmY4MTM4MDVlMmExODdmZWM1YThlMDczZGMwNDMyMGRlYmY0NWVhNmMsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGczTnpnNU5Td2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09'; export default function App() { // Spreadsheet array of worksheets const spreadsheet = useRef(); // Data const data = [ [1024,1024,0.24,1024,1024,1024], [1000.456,1000.456,0.4155,1000.456,1000.456,1000.456], ['547','547,98','7,98','547.98','547,98','547.98'], ]; // Columns const columns = [ { title:"Currency INR", type: "number", locale: 'en-IN', options: { style:'currency', currency: 'INR' } }, { title: "Currency BRL", type: "number", locale: 'pt-BR', options: { style: 'currency', currency: 'BRL' } }, { title: "Percent US", type: "number", mask: "0.00%" }, { title: "Units Liter US", type: "number", locale: 'en-US', options: { style: 'unit', unit: 'liter', unitDisplay: 'long' } }, { type: "number", format: '#.##0,00' }, { type: "number", mask: '#,##0' } ]; // Render data grid component return ( <Spreadsheet ref={spreadsheet} license={license} toolbar> <Worksheet data={data} columns={columns} minDimensions={[6,10]} defaultColWidth="120px" /> </Spreadsheet> ); }
Vue example
<template> <Spreadsheet ref="spreadsheet"> <Worksheet :data="data" :columns="columns" :minDimensions="[6,10]" defaultColWidth="120px" /> </Spreadsheet> </template> <script> import { Spreadsheet, Worksheet } from "@jspreadsheet/vue"; const license = 'MTcyMTFlOWJjYzA1ZTNjMzE5ZWUzNWNmY2UwNDY0NGIyZTZmMjM3NTM4YTg2MzA4N2ZhYzU3ZmMzNWFkMDhiMTE3MzJiYjVlOGRlNDUzNDhmMTEyYjY0ZmY4MTM4MDVlMmExODdmZWM1YThlMDczZGMwNDMyMGRlYmY0NWVhNmMsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGczTnpnNU5Td2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09'; export default { components: { Spreadsheet, Worksheet, }, data() { // Data const data = [ [1024,1024,0.24,1024,1024,1024], [1000.456,1000.456,0.4155,1000.456,1000.456,1000.456], ['547','547,98','7,98','547.98','547,98','547.98'], ]; // Columns const columns = [ { title:"Currency INR", type: "number", locale: 'en-IN', options: { style:'currency', currency: 'INR' } }, { title: "Currency BRL", type: "number", locale: 'pt-BR', options: { style: 'currency', currency: 'BRL' } }, { title: "Percent US", type: "number", mask: "0.00%" }, { title: "Units Liter US", type: "number", locale: 'en-US', options: { style: 'unit', unit: 'liter', unitDisplay: 'long' } }, { type: "number", format: '#.##0,00' }, { type: "number", mask: '#,##0' } ]; return { columns, data, license }; } } </script>
Angular example
@Component({ selector: "app-root", template: `<div #spreadsheet></div>` }) export class AppComponent { // Create a new data grid ngOnInit() { // Create spreadsheet this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, { toolbar: true, worksheets: [ { minDimensions:[6, 10], data: [ [1024,1024,0.24,1024,1024,1024], [1000.456,1000.456,0.4155,1000.456,1000.456,1000.456], ['547','547,98','7,98','547.98','547,98','547.98'], ], columns: [ { title:"Currency INR", type: "number", locale: 'en-IN', options: { style:'currency', currency: 'INR' } }, { title: "Currency BRL", type: "number", locale: 'pt-BR', options: { style: 'currency', currency: 'BRL' } }, { title: "Percent US", type: "number", mask: "0.00%" }, { title: "Units Liter US", type: "number", locale: 'en-US', options: { style: 'unit', unit: 'liter', unitDisplay: 'long' } }, { type: "number", format: '#.##0,00' }, { type: "number", mask: '#,##0' }, ], defaultColWidth: '120px', } ] }); } }
Apply a mask programmatically
The example below shows how to change the currency of the data grid dynamically.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://jsuites.net/v5/jsuites.css" type="text/css" /> <link rel="stylesheet" href="https://jspreadsheet.com/v10/jspreadsheet.css" type="text/css" /> <link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" /> <div id="spreadsheet"></div> <input type="button" value="set $ #,##0.00 to A1" onclick="setFormat()" /> <script> // Set your JSS license key (The following key only works for one day) jspreadsheet.setLicense('MTcyMTFlOWJjYzA1ZTNjMzE5ZWUzNWNmY2UwNDY0NGIyZTZmMjM3NTM4YTg2MzA4N2ZhYzU3ZmMzNWFkMDhiMTE3MzJiYjVlOGRlNDUzNDhmMTEyYjY0ZmY4MTM4MDVlMmExODdmZWM1YThlMDczZGMwNDMyMGRlYmY0NWVhNmMsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGczTnpnNU5Td2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09'); var setFormat = function() { table[0].updateProperty(0,0, {mask: '$ #,##0.00' }); } // Create the spreadsheet var table = jspreadsheet(document.getElementById('spreadsheet'), { tabs: true, toolbar: true, worksheets: [{ data: [['101.00']], minDimensions: [8,8], }], }); </script> </html>
React example
import React, { useRef } from "react"; import { Spreadsheet, Worksheet } from "@jspreadsheet/react"; import jspreadsheet from "jspreadsheet"; const license = 'MTcyMTFlOWJjYzA1ZTNjMzE5ZWUzNWNmY2UwNDY0NGIyZTZmMjM3NTM4YTg2MzA4N2ZhYzU3ZmMzNWFkMDhiMTE3MzJiYjVlOGRlNDUzNDhmMTEyYjY0ZmY4MTM4MDVlMmExODdmZWM1YThlMDczZGMwNDMyMGRlYmY0NWVhNmMsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGczTnpnNU5Td2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09'; export default function App() { // Spreadsheet array of worksheets const spreadsheet = useRef(); // Data const data = [['101.00']] // Render data grid component return ( <> <Spreadsheet ref={spreadsheet} license={license}> <Worksheet data={data} minDimensions={[6,10]} /> </Spreadsheet> <input type="button" value="set $ #,##0.00 to A1" onClick={() => spreadsheet.current[0].updateProperty(0,0, { mask: '$ #,##0.00' })} /> </> ); }
Vue example
<template> <Spreadsheet ref="spreadsheet" :license="license"> <Worksheet :data="data" /> </Spreadsheet> <input type="button" value="set $ #,##0.00 to A1" @click="setFormat" /> </template> <script> import { Spreadsheet, Worksheet } from "@jspreadsheet/vue"; const license = 'MTcyMTFlOWJjYzA1ZTNjMzE5ZWUzNWNmY2UwNDY0NGIyZTZmMjM3NTM4YTg2MzA4N2ZhYzU3ZmMzNWFkMDhiMTE3MzJiYjVlOGRlNDUzNDhmMTEyYjY0ZmY4MTM4MDVlMmExODdmZWM1YThlMDczZGMwNDMyMGRlYmY0NWVhNmMsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGczTnpnNU5Td2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09'; export default { components: { Spreadsheet, Worksheet, }, methods: { setFormat() { this.$refs.spreadsheet.current[0].updateProperty(0,0, { mask: '$ #,##0.00' })} }, }, data() { // Data const data = [['101.00']] return { data, license, }; } } </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('MTcyMTFlOWJjYzA1ZTNjMzE5ZWUzNWNmY2UwNDY0NGIyZTZmMjM3NTM4YTg2MzA4N2ZhYzU3ZmMzNWFkMDhiMTE3MzJiYjVlOGRlNDUzNDhmMTEyYjY0ZmY4MTM4MDVlMmExODdmZWM1YThlMDczZGMwNDMyMGRlYmY0NWVhNmMsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGczTnpnNU5Td2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09'); @Component({ selector: "app-root", template: ` <div #spreadsheet></div> <button type="button" (click)="this.worksheets[0].updateProperty(0,0, {mask: '$ #,##0.00' });">set $ #,##0.00 to A1</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, { tabs: true, toolbar: true, worksheets: [{ data: [['101.00']], minDimensions: [8,8], }], }); } }
Custom formatting
Jspreadsheet allows you to integrate custom masking using the methodrender
, as shown below.Format your cells using MomentJS
The example below shows how to mask a cell using MomentJS.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://jsuites.net/v5/jsuites.css" type="text/css" /> <link rel="stylesheet" href="https://jspreadsheet.com/v10/jspreadsheet.css" type="text/css" /> <link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" /> <script src="https://cdn.jsdelivr.net/npm/moment@2.29.4/moment.min.js"></script> <div id="spreadsheet"></div> <script> // Set your JSS license key (The following key only works for one day) jspreadsheet.setLicense('MTcyMTFlOWJjYzA1ZTNjMzE5ZWUzNWNmY2UwNDY0NGIyZTZmMjM3NTM4YTg2MzA4N2ZhYzU3ZmMzNWFkMDhiMTE3MzJiYjVlOGRlNDUzNDhmMTEyYjY0ZmY4MTM4MDVlMmExODdmZWM1YThlMDczZGMwNDMyMGRlYmY0NWVhNmMsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGczTnpnNU5Td2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09'); // Create a new spreadsheet const customRender = function(td, value, x, y, instance, options) { if (td && td.innerText && options.customFormat) { td.innerText = moment(td.innerText).format(options.customFormat); } } // Create the spreadsheet jspreadsheet(document.getElementById('spreadsheet'), { tabs: true, toolbar: true, worksheets: [{ data: [['2022-01-01 12:14:12'],['=TODAY()']], columns: [{ width: 300, customFormat: 'MMMM Do YYYY, h:mm:ss a', render: customRender, align: 'right', }], minDimensions: [6,8], }], }); </script> </html>
React example
import React, { useRef } from "react"; import { Spreadsheet, Worksheet } from "@jspreadsheet/react"; import jspreadsheet from "jspreadsheet"; import moment from "momentjs"; const license = 'MTcyMTFlOWJjYzA1ZTNjMzE5ZWUzNWNmY2UwNDY0NGIyZTZmMjM3NTM4YTg2MzA4N2ZhYzU3ZmMzNWFkMDhiMTE3MzJiYjVlOGRlNDUzNDhmMTEyYjY0ZmY4MTM4MDVlMmExODdmZWM1YThlMDczZGMwNDMyMGRlYmY0NWVhNmMsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGczTnpnNU5Td2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09'; // Create a new spreadsheet const customRender = function(td, value, x, y, instance, options) { if (td && td.innerText && options.customFormat) { td.innerText = moment(td.innerText).format(options.customFormat); } } export default function App() { // Spreadsheet array of worksheets const spreadsheet = useRef(); // Data const data = [['2022-01-01 12:14:12'],['=TODAY()']]; // Columns const columns = [{ width: 300, customFormat: 'MMMM Do YYYY, h:mm:ss a', render: customRender, align: 'right', }] // Render data grid component return ( <Spreadsheet ref={spreadsheet} license={license}> <Worksheet data={data} columns={columns} minDimensions={[6,8]} /> </Spreadsheet> ); }
Vue example
<template> <Spreadsheet ref="spreadsheet" :license="license"> <Worksheet :data="data" :columns="columns" /> </Spreadsheet> </template> <script> import { Spreadsheet, Worksheet } from "@jspreadsheet/vue"; const license = 'MTcyMTFlOWJjYzA1ZTNjMzE5ZWUzNWNmY2UwNDY0NGIyZTZmMjM3NTM4YTg2MzA4N2ZhYzU3ZmMzNWFkMDhiMTE3MzJiYjVlOGRlNDUzNDhmMTEyYjY0ZmY4MTM4MDVlMmExODdmZWM1YThlMDczZGMwNDMyMGRlYmY0NWVhNmMsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGczTnpnNU5Td2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09'; export default { components: { Spreadsheet, Worksheet, }, data() { // Data const data = [['2022-01-01 12:14:12'],['=TODAY()']]; // Columns const columns = [{ width: 300, customFormat: 'MMMM Do YYYY, h:mm:ss a', render: (td, value, x, y, instance, options) => { if (td && td.innerText && options.customFormat) { td.innerText = moment(td.innerText).format(options.customFormat); } }, align: 'right', }]; return { data, columns, license, }; } } </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('MTcyMTFlOWJjYzA1ZTNjMzE5ZWUzNWNmY2UwNDY0NGIyZTZmMjM3NTM4YTg2MzA4N2ZhYzU3ZmMzNWFkMDhiMTE3MzJiYjVlOGRlNDUzNDhmMTEyYjY0ZmY4MTM4MDVlMmExODdmZWM1YThlMDczZGMwNDMyMGRlYmY0NWVhNmMsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGczTnpnNU5Td2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09'); // Create a new spreadsheet const customRender = function(td, value, x, y, instance, options) { if (td && td.innerText && options.customFormat) { td.innerText = moment(td.innerText).format(options.customFormat); } } @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, { tabs: true, toolbar: true, worksheets: [{ data: [['2022-01-01 12:14:12'],['=TODAY()']], columns: [{ width: 300, customFormat: 'MMMM Do YYYY, h:mm:ss a', render: customRender, align: 'right', }], minDimensions: [6,8], }] }); } }