Format/Parse String as a Number

[Joseph A Whelan]

Hi,

Is there a built in support to parse and format columns as numbers or currency when they have numeric values as string, i.e. within single quotes?

Like, if I have the following Grid:

var grid = new ej.grids.Grid({

  dataSource: data,

  columns: [

    { field: 'ID', headerText: 'ID', textAlign: 'Right' },

    { field: 'Score', headerText: 'Score', type: 'number', format: 'N2' }

  ]

});

grid.appendTo('#Grid');

It works fine when the data is:

var data = [

  { ID: 1, Score: 10.24466 },

  { ID: 2, Score: 14.2 },

  { ID: 3, Score: 16.459 }

];

But it does not display anything when the data is as follows - i.e. Score in single quotes:

var data = [

  { ID: 1, Score: '10.24466' },

  { ID: 2, Score: '14.2' },

  { ID: 3, Score: '16.459' }

];

I wasn't sure if there is a built in support for this, or I just write a custom formatter for this. 'format: N2' does not work for it.

Thanks!

[Pavithra Subramaniyam]

Hi Tera P Keplinger,

Thanks for contacting Syncfusion support.

By default, to apply a number formatting to a column, the values must be a number. So, you need to iterate the data array to convert the string value to a number before setting it to the Grid since there is no built-in support for this.  Or you can use the column “valueAccessor” property to set the format in a custom way. However, the other data actions for this column like filtering, sorting, and grouping will be done based on the original string value only. Please refer to the below code example, documentation, and sample link for more information.

var intl = new ej.base.Internationalization();

 

function numberFormatter(field, data, column) {

  var nParser = intl.getNumberParser({ format: 'N3' });

  return nParser(data[field]);

}

 

var grid = new ej.grids.Grid({

   .  .  .

  columns: [

    {

      field: 'Score',

      headerText: 'Score',

      valueAccessor: numberFormatter,

    },

  ],

});

grid.appendTo('#Grid');

 

Documentation: https://ej2.syncfusion.com/javascript/documentation/grid/columns/columns/#valueaccessor

                            
https://ej2.syncfusion.com/javascript/documentation/common/internationalization/#number-formatting

                            
https://ej2.syncfusion.com/javascript/documentation/common/internationalization/#manipulating-numbers

Sample               : https://stackblitz.com/edit/396mzc?file=index.js

Please get back to us if you need further assistance on this.

Regards,

Pavithra S

[Joseph A Whelan]

Hi Pavithra,

Thank you for your response. This is pretty much what I had tried but there's a difference that I see between 'valueAccessor' attribute, and the 'format' attribute itself for the Column with the filters ON.

  dataSource: [

     { ID: 1, Score: 10.24466 },

     { ID: 2, Score: 14.2 },

     { ID: 3, Score: 16.459 },

   ],

  allowFiltering: true,

  filterSettings: {

    type: 'Excel'

  },

If I use 'format: N2' in a column with numeric data, this is how the filter options look like - they all have 2 digit options.

But when I use the valueAccessor option with the string data as below:

  dataSource: [

    { ID: 1, Score: '10.24466' },

    { ID: 2, Score: '14.2' },

    { ID: 3, Score: '16.459' },

  ],

The grid displays the formatted values but the filter options show the original unformatted values like below:

(Also, 14.2 shows as 14.2 and not 14.20 on grid even with format:N2)

Is there a way to handle this, and have filter values show up as defined by the format (same way as it works with 'format' option of the column) ?

Thanks!

[Pavithra Subramaniyam]

Hi Tera P Keplinger,

Since the “valueAccessor” property is only for displaying the customized cell value, it will not affect the filtering. As we said earlier, the Grid data actions will be done based on the original string values as in the dataSource only. So, if you want to do the number filtering the column value should be a number in Grid dataSource.

Regards,

Pavithra S

[Joseph A Whelan]

Hi Pavithra,

Thank you for your response. I'll check what can be done with the data to recieve this field as a number itself.

Thanks!

[Pavithra Subramaniyam]

Hi Tera P Keplinger,

Welcome! Please get back to us if you need further assistance.

Regards,

Pavithra S