Spreadsheet format
You can enforce the format of the input for the user using the property mask.Spreadsheet Tokens
The following tokens are available for this purpose./** 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' }] }] });
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
Source code
<html> <script src="https://jspreadsheet.com/v9/jspreadsheet.js"></script> <script src="https://jsuites.net/v4/jsuites.js"></script> <link rel="stylesheet" href="https://jsuites.net/v4/jsuites.css" type="text/css" /> <link rel="stylesheet" href="https://jspreadsheet.com/v9/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(''); // 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>
How to apply format
The example below shows how to change the currency of the data grid dynamically.Source code
<html> <script src="https://jspreadsheet.com/v9/jspreadsheet.js"></script> <script src="https://jsuites.net/v4/jsuites.js"></script> <link rel="stylesheet" href="https://jsuites.net/v4/jsuites.css" type="text/css" /> <link rel="stylesheet" href="https://jspreadsheet.com/v9/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(''); 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>