Excel Like Formatter

This example shows you how to use the SpreadJS built-in format library to format the data in a column using formatting strings similar to those used in Microsoft Excel. This will allow you to design a grid where the columns display the date, currency, or contacts in a specific format, as shown in the sample below.

<p>This example shows you how to use the SpreadJS built-in format library to format the data in a column using formatting strings similar to those used in Microsoft Excel. This will allow you to design a grid where the columns display the date, currency, or contacts in a specific format, as shown in the sample below.</p> <p>You need to add the <strong>format</strong> property and string formatter to the column definition. In this example, the following formatters are used:</p> <ul> <li>Date: <strong>mm/dd/yyyy</strong> renders a typical date format. (notice that for compatibility with other products in the SpreadJS suite, the expected value of the column which will be formatted as Date is not Javascript date milliseconds, but should be OADate)</li> <li>Price: <strong>\$#,##</strong> renders the number in currency format with a comma separator.</li> <li>Sales Contact: <strong>(###) ###-####</strong> renders a number in phone number format.</li> </ul>
<!DOCTYPE html> <html lang="en"> <head> <base href="/dataviewsjs/demos/en/sample/Features/Columns/ExcelLikeFormatter/purejs/" /> <meta charset="utf-8" /> <meta http-equiv="X-UA-Compatible" content="IE=edge" /> <meta name="keywords" content="columns" /> <meta name="description" content="This example shows you how to use the SpreadJS built-in format library to format the data in a column using formatting strings similar to those used in Microsoft Excel. This will allow you to design a grid where the columns display the date, currency, or contacts in a specific format, as shown in the sample below." /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <title>Excel Like Formatter | Features | GrapeCity DataViewsJS JavaScript Demos</title> <link href="/dataviewsjs/demos/node_modules/normalize.css/normalize.css" rel="stylesheet" type="text/css" /> <link href="/dataviewsjs/demos/static/css/base.css" rel="stylesheet" type="text/css" /> <link href="/dataviewsjs/demos/static/dataviews/gc.dataviews.core.min.css" rel="stylesheet" type="text/css" /> <link href="/dataviewsjs/demos/static/dataviews/gc.dataviews.grid.min.css" rel="stylesheet" type="text/css" /> <!-- Google Tag Manager --> <script> (function(w, d, s, l, i) { w[l] = w[l] || []; w[l].push({ 'gtm.start': new Date().getTime(), event: 'gtm.js' }); var f = d.getElementsByTagName(s)[0], j = d.createElement(s), dl = l != 'dataLayer' ? '&l=' + l : ''; j.async = true; j.src = 'https://www.googletagmanager.com/gtm.js?id=' + i + dl; f.parentNode.insertBefore(j, f); })(window, document, 'script', 'dataLayer', 'GTM-WT462SJ'); </script> <!-- End Google Tag Manager --> <script src="/dataviewsjs/demos/static/js/app-polyfills.min.js" type="text/javascript"></script> <script type="text/javascript"> window.process = { env: { NODE_ENV: 'production', USE_NPM: false, USE_CDN: false, SITE_ROOT: '/dataviewsjs/demos', FRAMEWORK: 'purejs', DVJS_LICENSE_KEY: 'E674186349827489#B0LYWQMpFdP3WSHlVaGdjTFtyLCxUQjdFV5MHey9kTyBnY0pkY6tWcr34caJjSPlEVlR6QpJnZEdjSj9kbkRUZChzMsRFS99mN4ZGO72yK7FXe5tmN59Eai5UM7dEVvgUeYN5MqdUTIpWTpBnSTJEWv24YwoFWr9mWEl6UNVmVMxUd5ckUQdlUNV4MDhENaV5KNNWbJtiSwFUYCVUax4GUuBjZDN7T5dWdDpFNyJjSxJkes3mUt94QrdnMK5mZORGeFZ4QyFTMmdWOWdlMmJ4ZJhTS5MmZatyZ7k5c0JXVB3mWVNke7AFRiojITJCLikjNBNjNzcjI0ICSiwSN8AjN9YjMwUTM0IicfJye35XX3JSWUVVOiojIDJCLiEjdgMlSzdXZpZVY4FGRiojIOJyebpjIkJHUiwiI9MDMykDMggDM8ATOxAjMiojI4J7QiwiIt36YukHdpNWZwFmcn9iKiojIz5GRiwiIj9WSgkHdpNUZwFmcHJiOiEmTDJCLlVnc4pjIsZXRiwiI9gDN7IDO9QzM6gTM4cjNiojIklkI1pjIEJCLi4TPn3UZuJ7cPZTW5hUMtN7MJN6aQFnT5gmd9FEZ4EETlFmW63kU8IGdiRGTJZle5UDbZJHTR34T0JzZoZmbalXQwNmM8EndEFEdSVlMiVnckFmQUJTOahmc7AzRHhlU4I4Vs3kN6lHaHR4LuhWULNIe', SJS_LICENSE_KEY: '*.grapecity.com,E613631884219496#B0qRgJHWSJ7NyBlc8BjNMRHW7g7YldTZXFTQuFnW4hVOCplVSlVV09ERlhEZuVTVKlTazE4Q6VGSw2CdWZUWSVmbjVXbrxmWFVWR8ZzQro7U84WMGdlbuVHb73kS5kjUTN4NvFVdLdXWVR4Nox6Z7UUSysEcXJEMsN6bDN4TxMDVwVmWBRzKxhkTzAXTaJmdD3CRFJTd8R4R6M5RklWa6oUaLlXMwR4R8ZUdtRWVxUUaQh6VXNDdEhlZ7FHR6QXTPJTVvkWcyZnbSdHRtZHcYF6TKN4axYGcZNjTDF7TvFTTr24VqZjVHVjcLd7QkRmdNxkI0IyUiwiI5gDOEF4QGVjI0ICSiwiMzkTO9kTOyMTM0IicfJye35XX3JSSGljQiojIDJCLiITMuYHITpEIkFWZyB7UiojIOJyebpjIkJHUiwiI4MDMyEDMgkDM8ATOxAjMiojI4J7QiwiIt36YukHdpNWZwFmcn9iKiojIz5GRiwiIj9WagkHdpNUZwFmcHJiOiEmTDJCLlVnc4pjIsZXRiwiI6kDN9EjM4gDOxMjNzEjNiojIklkIs4XZzxWYmpjIyNHZisnOiwmbBJye0ICRiwiI34TQ72kNBV6YXpXdGxGWxdHcol4MyUGUHJVbQVHRx44Sw84YxRkS4QnZadDNmhWWxV5QxFlTlZEbBJ5N8gUNQlDb7J6Kl36YHVnb4NGN92UMFdlNORFU8VDSaFlQSVlS4EHTrA5Ohh', }, }; </script> <script src="/dataviewsjs/demos/static/dataviews/gc.dataviews.common.min.js" type="text/javascript"></script> <script src="/dataviewsjs/demos/static/dataviews/gc.dataviews.core.min.js" type="text/javascript"></script> <script src="/dataviewsjs/demos/static/dataviews/gc.dataviews.grid.min.js" type="text/javascript"></script> <script src="/dataviewsjs/demos/static/js/license.js" type="text/javascript"></script> </head> <body class="theme-default"> <!-- Google Tag Manager (noscript) --> <noscript ><iframe src="https://www.googletagmanager.com/ns.html?id=GTM-WT462SJ" height="0" width="0" style="display:none;visibility:hidden" ></iframe ></noscript> <!-- End Google Tag Manager (noscript) --> <noscript>You need to enable JavaScript to run this app.</noscript> <div id="grid" class="grid"></div> <script src="data.js" type="text/javascript"></script> <script src="app.js" type="text/javascript"></script> </body> </html>
var cols = [ { id: 'locations', caption: 'Location', dataField: 'locations', width: '*', }, { id: 'date', caption: 'Date', dataField: 'date', format: 'mm/dd/yyyy', width: '*', }, { id: 'price', caption: 'Price', dataField: 'price', format: '$#,##', width: '*', }, { id: 'salesPerson', caption: 'Sales Person', dataField: 'salesPerson', width: '*', }, { id: 'phone', caption: 'Sales Contact', dataField: 'phone', format: '(###) ###-####', width: '*', }, ]; new GC.DataViews.DataView(document.getElementById('grid'), data, cols, new GC.DataViews.GridLayout());
var locations = ['Raleigh', 'Pittsburgh', 'Kirkland', 'Parkersburg', 'Monroeville', 'Pleasantville']; var salespeople = ['Bo', 'Jennifer', 'Shay', 'Gina', 'Sean']; var data = []; for (var i = 0; i < 100; i++) { data.push({ id: i, locations: locations[i % locations.length], date: new Date(2018, i % 12, (i % 28) + 1), price: Math.floor(Math.random() * 100), salesPerson: salespeople[i % salespeople.length], phone: Math.floor(6000000000 + Math.random() * 900000000), }); }