Spreadsheet format

From version 8, three different properties help with column and cell data formatting. Those options bring Jspreadsheet closer to other spreadsheet software such as Excel and Google Spreadsheet and elevate the level of compatibility and flexibility you experience in your online spreadsheet applications. This section covers the different properties, such as: mask, format and locale.

Spreadsheet Tokens

The first section of this chapter will cover the usage of the property mask and format. The mask property only allows the user to enter a specific input defined by spreadsheet-like tokens, where the format property is applied after the user finalizes the edition of a cell. The tokens are compatible with other spreadsheet software and can be used as 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$ #.##,00'
        }]
    }]
});


Intl. Number formatting

Jspreadsheet Pro integrates Intl.NumberFormat natively to extend the number formatting. To enable this feature, you must define the property locale inside the column or cells as the example below. For more information about the available options, please visit the Mozilla website.

Currency formatting

If the style is 'currency', a currency property must be provided. Optionally, currencyDisplay and currencySign control the unit formatting.
// India currency
jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [{
        minDimensions: [10,10],
        columns: [{
            type: 'number',
            // Locale will enable number formating
            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' }
        }]
    }]
});

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' }
        }]
    }]
});

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" }
        }]
    }]
});

Percentage

Percentage can be used as below:
jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [{
        minDimensions: [10,10],
        columns: [{
            { type: "number", locale: 'en-US', options: { style: 'percent' }},
        }]
    }]
});

Examples

The example below implement number formatting using Intl.NumberFormat.

jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [
        {
            minDimensions:[6, 10],
            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", locale: 'en-US', options: { style: 'percent' } },
                { title: "Units Liter US", type: "number", locale: 'en-US', options: { style: 'unit', unit: 'liter', unitDisplay: 'long' } },
            ],
            filters: true,
            defaultColWidth: '120px',
        }
    ],
    toolbar: true,
    license: 'MWEzMTE4MGFkNWY5YzQzNjE4NjZiNmE1NThhM2M0Yjc1NmUyNGM2N2YzZjU2NDQ5ZjM1MGFiYWNmOTFkNTkwODFiYmYwNDE1YjhhM2ViNGUyMzM2YjYzY2Q4NTcyMWE4MGQ4YjVjNjI2NWY4NWYyMTBjMWU5M2ZmNTU4OGI1MDQsZXlKdVlXMWxJam9pY0dGMWJDNW9iMlJsYkNJc0ltUmhkR1VpT2pFMk5UZzVOakk0TURBc0ltUnZiV0ZwYmlJNld5SnFjM0J5WldGa2MyaGxaWFF1WTI5dElpd2lZM05pTG1Gd2NDSXNJbXB6YUdWc2JDNXVaWFFpTENKc2IyTmhiR2h2YzNRaVhTd2ljR3hoYmlJNklqSWlMQ0p6WTI5d1pTSTZXeUoyTnlJc0luWTRJaXdpY0dGeWMyVnlJaXdpYzJobFpYUnpJaXdpWm05eWJYTWlMQ0p5Wlc1a1pYSWlMQ0ptYjNKdGRXeGhJbDE5'
});