Change number format according to valueFormat column in kendo Grid

Question

I have created dynamic kendo grid.In grid there is some columns which required formatting. I have one field "valueFormat" in database according that format I want to format other columns.

How to achieve above thing Please help.

link for sample grid

var data = [{
    "Dept": "Dev",
    "CalculateValue": 0 ,
    "AcualValue": 341.917,
    "ValueFormat": "#.###"
   }, 
   {
    "Dept": "Dev",
    "CalculateValue": 0,
    "AcualValue": 5333.083,
    "ValueFormat": "#.###"
   }, 
   {
    "Dept": "Dev",
    "CalculateValue":0 ,
    "AcualValue": 8735.666,
    "ValueFormat": "{0:c2}"
   }, 
   {
    "Dept": "Dev",
    "CalculateValue":0,
    "AcualValue": 126.000,
    "ValueFormat": "{0:c2}"
  }];

var dateFields = [];
generateGrid(data)

function generateGrid(gridData) {
        if ($(".k-header").length > 0) {
            grid = $("#grid").data("kendoGrid");
            grid.destroy();
            $("#grid").empty();
        }

        $("#grid").kendoGrid({
            toolbar: ["excel"],
            excel: {
                allPages: true,
                filterable: true,
                fileName: 'Aggregate Expenditure/Utilization Report_.xlsx'
            },
            dataSource: {

                pageSize: 100,
                schema: {
                    data: function () { return gridData; },
                    total: function () {
                        return gridData.length;
                    }
                }
            },
            sortable: true,
            filterable: true,
            groupable: true,
            pageable: true,
            columns: generateModel(gridData[0]),
            autoBind: true
        });
        $("#grid").data("kendoGrid").refresh();
    }

function generateModel(gridData) {
   var model = {};
   model.id = "ID";
   var fields = {};
   for (var property in gridData) {
     var propType = typeof gridData[property];

    if (propType == "number") {
       fields[property] = {
       type: "number",
       validation: {
          required: true
       }
    };
    } else if (propType == "boolean") {
      fields[property] = {
      type: "boolean",
      validation: {
        required: true
      }
    };
    } else if (propType == "string") {
      var parsedDate = kendo.parseDate(gridData[property]);
       if (parsedDate) {
         fields[property] = {
           type: "date",
           validation: {
             required: true
           }
         };
      dateFields.push(property);
     } else {
        fields[property] = {
          validation: {
             required: true
          }
        };
     }
   } else {
      fields[property] = {
        validation: {
          required: true
        }
      };
   }

  }
   model.fields = fields;

   return model;
}

Answer 1

The Kendo grid's column formatting option won't work for you, because it applies the same formatting to all cells within a column, but you are going for different formatting in every row.

In that case you should specify a custom template for the ActualValue column. Inside that template you can use a formatting function to process your value. Since you are already using Kendo, you can take advantage of kendo.format(), like this:

template: "#: kendo.format(ValueFormat, AcualValue)#"

However, that does not work with all of your data, because some of the format strings are already wrapped in brackets and have an index like "{0:c2}", and others are not - "#.###". So you should create a function that makes sure they are consistent, before you pass them to kendo.format(). I've done this with fixValueFormatString(ValueFormat) below.

Here's a link to a working sample: http://jsbin.com/jejixediga/edit?js,console,output

And here's the code:

var data = [{

  "Dept": "Dev",
  "CalculateValue": 0 ,
  "AcualValue": 341.917,
  "ValueFormat": "#.###"
}, {

  "Dept": "Dev",
  "CalculateValue": 0,
  "AcualValue": 5333.083,
  "ValueFormat": "#.###"
}, {

  "Dept": "Dev",
  "CalculateValue":0 ,
  "AcualValue": 8735.666,
  "ValueFormat": "{0:c2}"
}, {

  "Dept": "Dev",
  "CalculateValue":0,
  "AcualValue": 126.000,
  "ValueFormat": "{0:c2}"
}];

var dateFields = [];

// Make sure all format strings are consistent
function fixValueFormatString(ValueFormat){
  if(!ValueFormat.startsWith("{")){
      ValueFormat = "{0:" + ValueFormat + "}";
  }
  return ValueFormat;
}

generateGrid(data)

  function generateGrid(gridData) {
            if ($(".k-header").length > 0) {
                grid = $("#grid").data("kendoGrid");
                grid.destroy();
                $("#grid").empty();
            }

            $("#grid").kendoGrid({
                toolbar: ["excel"],
                excel: {
                    allPages: true,
                    filterable: true,
                    fileName: 'Aggregate Expenditure/Utilization Report_.xlsx'
                },
                dataSource: {

                    pageSize: 100,
                    schema: {
                        data: function () { return gridData; },
                        total: function () {
                            return gridData.length;
                        }
                    }
                },
                sortable: true,
                filterable: true,
                groupable: true,
                pageable: true,
                autoBind: true,
                columns: [{
                    field: "Dept"
                }, {
                    field: "CalculateValue",
                }, {
                    template: "#: kendo.format(fixValueFormatString(ValueFormat), AcualValue)#",
                    field: "AcualValue"
                }, {
                    field: "ValueFormat"
                }]
            });
            $("#grid").data("kendoGrid").refresh();
        }

function generateModel(gridData) {
  var model = {};
  model.id = "ID";
  var fields = {};
  for (var property in gridData) {
    var propType = typeof gridData[property];

    if (propType == "number") {
      fields[property] = {
        type: "number",
        validation: {
          required: true
        }
      };
    } else if (propType == "boolean") {
      fields[property] = {
        type: "boolean",
        validation: {
          required: true
        }
      };
    } else if (propType == "string") {
      var parsedDate = kendo.parseDate(gridData[property]);
      if (parsedDate) {
        fields[property] = {
          type: "date",
          validation: {
            required: true
          }
        };
        dateFields.push(property);
      } else {
        fields[property] = {
          validation: {
            required: true
          }
        };
      }
    } else {
      fields[property] = {
        validation: {
          required: true
        }
      };
    }

  }
  model.fields = fields;

  return model;
}

UPDATE - ASSIGN TEMPLATE TO DYNAMICALLY GENERATED COLUMNS

In response to OPs request for a solution that works with dynamic columns, here's the revised code (http://jsbin.com/jinowamosa/edit?js,console,output):

var data = [{

  "Dept": "Dev",
  "CalculateValue": 0 ,
  "AcualValue": 341.917,
  "ValueFormat": "#.###"
}, {

  "Dept": "Dev",
  "CalculateValue": 0,
  "AcualValue": 5333.083,
  "ValueFormat": "#.###"
}, {

  "Dept": "Dev",
  "CalculateValue":0 ,
  "AcualValue": 8735.666,
  "ValueFormat": "{0:c2}"
}, {

  "Dept": "Dev",
  "CalculateValue":0,
  "AcualValue": 126.000,
  "ValueFormat": "{0:c2}"
}];

var dateFields = [];

function fixValueFormatString(ValueFormat){
  if(!ValueFormat.startsWith("{")){
      ValueFormat = "{0:" + ValueFormat + "}";
  }
  return ValueFormat;
}

generateGrid(data)

  function generateGrid(gridData) {
            if ($(".k-header").length > 0) {
                grid = $("#grid").data("kendoGrid");
                grid.destroy();
                $("#grid").empty();
            }

            $("#grid").kendoGrid({
                toolbar: ["excel"],
                excel: {
                    allPages: true,
                    filterable: true,
                    fileName: 'Aggregate Expenditure/Utilization Report_.xlsx'
                },
                dataSource: {
                    pageSize: 100,
                    schema: {
                        data: function () { return gridData; },
                        total: function () {
                            return gridData.length;
                        }
                    }
                },
                sortable: true,
                filterable: true,
                groupable: true,
                pageable: true,
                autoBind: true,
                columns: generateColumns(gridData[0])
            });
            $("#grid").data("kendoGrid").refresh();
        }

function generateColumns(gridData) {
  var fields = {};
  var columns = [];

  for (var property in gridData) {
    var propType = typeof gridData[property];

    if (propType == "number") {
      fields[property] = {
        type: "number",
        validation: {
          required: true
        }
      };
    } else if (propType == "boolean") {
      fields[property] = {
        type: "boolean",
        validation: {
          required: true
        }
      };
    } else if (propType == "string") {
      var parsedDate = kendo.parseDate(gridData[property]);
      if (parsedDate) {
        fields[property] = {
          type: "date",
          validation: {
            required: true
          }
        };
        dateFields.push(property);
      } else {
        fields[property] = {
          validation: {
            required: true
          }
        };
      }
    } else {
      fields[property] = {
        validation: {
          required: true
        }
      };
    }

  }

  for (var field in fields) { 
        if(field == 'AcualValue'){
          columns.push({ field: field, template: "#: kendo.format(fixValueFormatString(ValueFormat), AcualValue)#" });
        } else {
          columns.push({ field: field });
        }
    }

  return columns;
}

Hope this helps!